文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何在单个查询中生成访问跟踪报告

问如何在单个查询中生成访问跟踪报告
EN

Stack Overflow用户
提问于 2019-04-10 05:46:14
回答 1查看 38关注 0票数 1

我正在尝试生成一个基于三个不同表的报告，这些表存储了系统进程的一种轨迹。Source_Base表存储她的进程尝试，以及上次调用的自动机的成功或失败结果。Source_Robot表存储名为的每个机器人的轨迹。最后，如果某个机器人中发生了异常，则会将其存储在Robot_Exception表中。

下面我将试着展示这些表格：

代码语言：javascript
复制
Source_Base (Showing only a portion of the real data)
----------------------------------------------------------------------
AccesID     | OwnerAccessID | StartTime     | EndTime       | Success 
----------------------------------------------------------------------
1248683283  |  0            | 09:10:23.237  | 09:10:26.727  | 0
1248683284  |  10013553     | 09:10:23.233  | 09:12:05.703  | 1
1248683285  |  0            | 14:24:32.037  | 14:24:39.313  | 0
1248683286  |  10013555     | 14:24:32.030  | 14:25:42.387  | 1
1248683287  |  0            | 14:44:47.747  | 14:44:50.973  | 1
1248683288  |  1248683287   | 14:44:47.747  | 14:44:51.697  | 1


Source_Robot 
-----------------------------------------------------------------------------------
AccesID     | OwnerAccessID | RobotID | StartTime    | EndTime      |   Success 
-----------------------------------------------------------------------------------
10013551    |   1248683283  | 11      | 09:10:27.587 | 09:11:17.605 |   0
10013552    |   10013551    | 12      | 09:11:18.186 | 09:11:18.219 |   0
10013553    |   10013552    | 13      | 09:11:18.323 | 09:12:05.003 |   1
10013554    |   1248683285  | 15      | 14:24:41.763 | 14:25:36.313 |   0
10013555    |   10013554    | 14      | 14:25:37.393 | 14:25:39.967 |   1


Robot_Exception
-----------------------------------------------------------------------
ExceptionID | OwnerAccessID | RobotID   | StartTime     | EndTime                   
-----------------------------------------------------------------------
999993      |   10013554    |   15      | 14:24:41.763  | 14:25:36.313  
999992      |   10013552    |   12      | 09:11:18.189  | 09:11:18.217  
999991      |   10013551    |   11      | 09:10:27.593  | 09:11:17.603

现在我希望有这样的报告：

代码语言：javascript
复制
--------------------------------------------------------------------------------------
AccesID     |   OwnerAccessID   |   RobotID     | Success | Exception | Duration
--------------------------------------------------------------------------------------
1248683283  |   0               |   NULL        | 0       | NULL      | (datediff)
10013551    |   1248683283      |   11          | 0       | 999991    | (datediff)
10013552    |   10013551        |   12          | 0       | 999992    | (datediff)
10013553    |   10013552        |   13          | 1       | NULL      | (datediff)
1248683284  |   10013553        |   NULL        | 1       | NULL      | (datediff)
1248683285  |   0               |   NULL        | 0       | NULL      | (datediff)
10013554    |   1248683285      |   15          | 0       | 999993    | (datediff)
10013555    |   10013554        |   14          | 1       | NULL      | (datediff)
1248683286  |   10013555        |   NULL        | 1       | NULL      | (datediff)
1248683288  |   1248683287      |   NULL        | 1       | NULL      | (datediff)
1248683287  |   0               |   NULL        | 1       | NULL      | (datediff)

我试图用CTE解决这个问题，但是，一些值没有出现在报告中，我确信我的上一个查询是错误的。或者使用CTE不是正确的方式。

代码语言：javascript
复制
WITH Source_Base_CTE (AccesID, OwnerAccessID, RobotID, StartTime, EndTime, Duration, Success)
AS
(SELECT 
        AccesID,
        OwnerAccessID,
        0 as RobotID,
        StartTime,
        EndTime,
        datediff (second, StartTime, EndTime) as Duration,
        Success
    FROM 
        Source_Base 
    ),
Source_Robot_CTE (AccesID, OwnerAccessID, RobotID, StartTime, EndTime, Duration, Success)
AS
(SELECT 
        AccesID,
        OwnerAccessID,
        RobotID,
        StartTime,
        EndTime,
        datediff (second, StartTime, EndTime) as Duration,
        Success
    FROM 
        Source_Robot 
    )
SELECT * FROM Source_Base_CTE WHERE StartTime > '00:00:00.00'
UNION ALL
SELECT * FROM Source_Robot_CTE WHERE StartTime > '00:00:00.00' AND OwnerAccessID in (SELECT AccesID FROM Source_Base_CTE) 
ORDER BY StartTime ASC

结果：

代码语言：javascript
复制
AccesID     OwnerAccessID   RobotID StartTime       EndTime         Duration    Success

1248683284  10013551        0       09:10:23.233    09:12:05.703    102         1
1248683283  0               0       09:10:23.237    09:10:26.727    3           0
10013551    1248683283      12      09:10:27.587    09:11:17.603    50          0
1248683286  10013554        0       14:24:32.030    14:25:42.387    70          1
1248683285  0               0       14:24:32.037    14:24:39.313    7           0
10013553    1248683285      15      14:24:41.763    14:25:36.313    55          0
1248683288  1248683287      0       14:44:47.747    14:44:51.697    4           1
1248683287  0               0       14:44:47.747    14:44:50.973    3           1
1248683289  0               0       16:23:35.930    16:23:38.857    3           0
1248683290  10013557        0       16:23:35.930    16:24:34.197    59          1
10013555    1248683289      12      16:23:39.727    16:24:17.860    38          0

如果有人能给我一个方法，我将不胜感激。

sql
sql-server
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-04-10 08:42:17

运行：https://www.db-fiddle.com/f/iDtVPvCnXV6E92CzyWNjWK/0

在那里按run以查看11行的结果。

SQL：

代码语言：javascript
复制
with source as (
  select AccesID, OwnerAccessID, null RobotID, StartTime, EndTime, Success from Source_Base union
  select AccesID, OwnerAccessID,      RobotID, StartTime, EndTime, Success from Source_Robot
)
select AccesID,OwnerAccessID, RobotID, Success,
  (select ExceptionID from Robot_Exception e where e.OwnerAccessID=source.AccesID) Exception,
  round(extract(epoch from EndTime)-extract(epoch from StartTime)) duration,
  starttime
from source
order by StartTime;

使用您的示例表：

代码语言：javascript
复制
create table source_base ( accesid numeric, owneraccessid numeric, starttime timestamp, endtime timestamp, success numeric );
insert into source_base values 
(1248683283, 0,          '2019-04-10 09:10:23.237', '2019-04-10 09:10:26.727', 0),
(1248683284, 10013553,   '2019-04-10 09:10:23.233', '2019-04-10 09:12:05.703', 1),
(1248683285, 0,          '2019-04-10 14:24:32.037', '2019-04-10 14:24:39.313', 0),
(1248683286, 10013555,   '2019-04-10 14:24:32.030', '2019-04-10 14:25:42.387', 1),
(1248683287, 0,          '2019-04-10 14:44:47.747', '2019-04-10 14:44:50.973', 1),
(1248683288, 1248683287, '2019-04-10 14:44:47.747', '2019-04-10 14:44:51.697', 1);

create table source_robot ( accesid numeric, owneraccessid numeric, robotid numeric, starttime timestamp, endtime timestamp, success numeric );
insert into source_robot values
(10013551, 1248683283, 11, '2019-04-10 09:10:27.587', '2019-04-10 09:11:17.605', 0),
(10013552, 10013551,   12, '2019-04-10 09:11:18.186', '2019-04-10 09:11:18.219', 0),
(10013553, 10013552,   13, '2019-04-10 09:11:18.323', '2019-04-10 09:12:05.003', 1),
(10013554, 1248683285, 15, '2019-04-10 14:24:41.763', '2019-04-10 14:25:36.313', 0),
(10013555, 10013554,   14, '2019-04-10 14:25:37.393', '2019-04-10 14:25:39.967', 1);

create table robot_exception (exceptionid numeric, owneraccessid numeric, robotid numeric, starttime timestamp, endtime timestamp);
insert into robot_exception values
(999993, 10013554, 15, '2019-04-10 14:24:41.763', '2019-04-10 14:25:36.313'),
(999992, 10013552, 12, '2019-04-10 09:11:18.189', '2019-04-10 09:11:18.217'),
(999991, 10013551, 11, '2019-04-10 09:10:27.593', '2019-04-10 09:11:17.603');
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/55601805

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档