问题描述
我有一个包含很多行的表格,其中有几个与全局相关的标识符,但在我当前的任务中,只有 3 列是重要的:
I have a table that contains A LOT of rows, with several identifiers that are relevant in the big picture, but in my current task, only 3 columns are important:
SiteIdentifier | SysTm | Signalet
SiteIdentifier 是一个数字,SysTm 是 DateTime,Signalet 是一个文本字段.防爆数据:
SiteIdentifier is a number, SysTm is DateTime and Signalet is a text field. Ex data:
587451 | 2021-03-01 00:00:00 | Left 587451 | 2021-03-04 07:12:17 | Joined 214537 | 2021-03-05 02:13:03 | Left 587451 | 2021-03-04 12:12:12 | Left 214537 | 2021-03-05 07:13:00 | Joined 587451 | 2021-03-08 01:04:07 | Joined
这是我想要实现的目标:我想创建一个可以显示的查询:
Here's what I want to achieve: I want to create a query that can display:
SiteIdentifier | SysTm of last Signalet Left | SysTm of last Signalet Joined | DATEDIFF(hour, ... between last Left and Joined
在示例数据行的情况下,结果将是:获取每个 SiteIdentifier 的每个实例很重要
In the case of the lines of example data, a result would be: It is important that I get EVERY instance of EVERY SiteIdentifier
587451 | 2021-03-01 00:00:00 | 2021-03-04 07:12:17 | 79 214537 | 2021-03-05 02:13:03 | 2021-03-05 07:13:00 | 5 587451 | 2021-03-04 12:12:12 | 2021-03-08 01:04:07 | 84
(每个 SiteIdentifier 可以表示 NUMEROUS 次)
这些行都包含在一张表中,这让我很困惑...
These rows are all contained in one table, which is what is tripping me up...
我之前问过这个问题,并得到了这个查询:
I asked this question earlier, and was given this query:
SELECT SiteIdentifier, MAX(CASE WHEN Signalet = 'Left' THEN SysTM END) as left_tm, MAX(CASE WHEN Signalet = 'Joined' THEN SysTM END) as Joined_tm, DATAEDIFF(hour, MAX(CASE WHEN Signalet = 'Left' THEN SysTM END), MAX(CASE WHEN Signalet = 'Joined' THEN SysTM END) ) as time_diff FROM Table WHERE Signalet IN ( 'Left', 'Joined') GROUP BY SiteIdentifier ORDER BY SiteIdentifier
这个查询给了我最近的行"EACH SiteIdentifier,但不是所有结果.我试图得到的结果是,正如我上面写的,一个 SiteIdentifier 可以表示多次.
This query gave me the most recent "row" of EACH SiteIdentifier, but not all results. I am trying to get the result to be, as I wrote above, a SiteIdentifier can be represented multiple times.
推荐答案
如果您在 SiteIdentifier 中为每个 Signalet Left"添加行号和Joined",然后在匹配的行上加入你会得到想要的结果.
If you add a row number across the SiteIdentifier for each of Signalet "Left" and "Joined", and then join on the matching row you get the desired results.
注意:添加了笨重的第二个左连接来处理第一行不是左"记录的情况.
Note: Added a clunky second left join to handle the case when the first row isn't a 'left' record.
declare @Test table (SiteIdentifier int, SysTm datetime2(0), Signalet varchar(21)); insert into @Test (SiteIdentifier, SysTm, Signalet) values (587451, '2021-03-01 00:00:00', 'Left'), (587451, '2021-03-04 07:12:17', 'Joined'), (214537, '2021-03-05 02:13:03', 'Left'), (587451, '2021-03-04 12:12:12', 'Left'), (214537, '2021-03-05 07:13:00', 'Joined'), (587451, '2021-03-08 01:04:07', 'Joined'); with cte as ( select * , row_number() over (partition by SiteIdentifier, Signalet order by SysTm) rn from @Test ) select C1.SiteIdentifier, C1.SysTm, coalesce(C2.SysTm, C3.SysTm), datediff(hour, C1.SysTm, coalesce(C2.SysTm, C3.SysTm)) from cte C1 left join cte C2 on C2.SiteIdentifier = C1.SiteIdentifier and C2.Signalet = 'Joined' and C2.rn = C1.rn and C2.SysTm > C1.SysTm left join cte C3 on C3.SiteIdentifier = C1.SiteIdentifier and C3.Signalet = 'Joined' and C3.rn = C1.rn + 1 and C3.SysTm > C1.SysTm and C2.rn is null where C1.Signalet = 'Left' order by C1.SysTm asc;
返回:
站点标识符 | SysTm(左) | SysTm(已加入) | 差异 |
---|---|---|---|
587451 | 2021-03-01 00:00:00 | 2021-03-04 07:12:17 | 79 |
587451 | 2021-03-04 12:12:12 | 2021-03-08 01:04:07 | 85 |
214537 | 2021-03-05 02:13:03 | 2021-03-05 07:13:00 | 5 |
请注意,如果您像我对您的问题所做的那样添加 DDL+DML,您会更容易为人们提供帮助.
Note if you add DDL+DML as I have done to your questions you make it much easier for people to assist.