时间戳

首页 > 解决方案 > SQL - 以日期间隔聚合数据

sql - SQL - 以日期间隔聚合数据

问题描述

我在加入两个代表用户状态更改的日期间隔表时遇到问题。两个表中的 ID 相同。

表_A

ID  StatusA    FromA              ToA
1   Active     01/01/2020 10:00   01/01/2020 11:30
1   NonActive  02/01/2020 09:00   03/01/2020 11:00
2   Active     01/01/2020 10:00   01/01/2020 11:30
3   Active     02/01/2020 09:30   02/01/2020 15:50

表_B

ID  StatusB    FromB               ToB
1   Chatting   01/01/2020 10:02   01/01/2020 10:15
1   Calling    01/01/2020 10:10   01/01/2020 10:20
2   Awaiting   02/01/2020 10:00   02/01/2020 10:15
2   Calling    02/01/2020 10:16   02/01/2020 10:20
3   Awating    02/01/2020 09:30   02/01/2020 15:50
1   Awating    02/01/2020 09:00   03/01/2020 11:00

我想要获得的是这样的表格：

ID  StatusA    FromA              ToA               StatusB   FromB              ToB
1   Active     01/01/2020 10:00   01/01/2020 11:30  Chatting  01/01/2020 10:02   01/01/2020 10:15
1   Active     01/01/2020 10:00   01/01/2020 11:30  Calling   01/01/2020 10:10   01/01/2020 10:20
2   Active     01/01/2020 10:00   01/01/2020 11:30  Awaiting  02/01/2020 10:00   02/01/2020 10:15
2   Active     01/01/2020 10:00   01/01/2020 11:30  Calling   02/01/2020 10:16   02/01/2020 10:20

我在一开始写的查询：

SELECT Table_A.ID, 
Table_A.StatusA AS "Primary_Status", 
Table_A.FromA, 
Table_A.ToA,
Table_B.StatusB AS "Secondary_Status",
Table_B.FromB,
Table_B.ToB,
FROM TableA 
INNER JOIN Table_B ON Table_A.ID = Table_B.ID
WHERE ((Table_B.startTime BETWEEN Table_A.FromA AND Table_A.ToA)
or (Table_B.FromB <= Table_A.FromA AND Table_B.ToB <= Table_A.ToA)
or (Table_B.FromB <= Table_A.FromA AND Table_B.ToB >= Table_A.ToA)
or (Table_B.FromB BETWEEN Table_A.FromA AND Table_A.ToA AND Table_B.ToB >= Table_A.ToA) 
);

提前致谢！

编辑：每个 ID 唯一标识一个用户。因此，Table_A 中 ID = 1 的用户与 Table_B 中 ID = 1 的用户相同

EDIT_02：感谢 Gordon Linoff 解决方案，我现在有了

SELECT Table_A.ID, 
Table_A.StatusA AS "Primary_Status", 
Table_A.FromA, 
Table_A.ToA,
Table_B.StatusB AS "Secondary_Status",
Table_B.FromB,
Table_B.ToB,
FROM TableA 
INNER JOIN Table_B ON (Table_A.ID = Table_B.ID AND Table_B.FromB >= Table_A.FromA AND Table_B.ToB <= Table_A.ToA)

此查询正在运行，但我可能会遇到以下情况

b.FromB <= a.FromA && b.ToB <= a.ToA
b.FromB >= a.FromA && b.ToB >= a.ToA
b.FromB <= a.FromA && b.ToB >= a.ToA

标签： sqlsql-serverazure-sql-database

解决方案

You seem to just want a join with the right conditions on the time columns:

SELECT a.ID, a.StatusA AS "Primary_Status", a.FromA, a.ToA,
       b.StatusB AS "Secondary_Status", b.FromB, b.ToB,
FROM TableA a JOIN
     Table_B b
     ON a.ID = b.ID AND
        b.FromB >= a.FromA AND
        b.ToB <= b.FromB;

推荐阅读