SQL:在逻辑移动中过滤自循环

Question

我正在处理一个包含物流活动的数据集。在可视化的过程中，我们想要过滤掉从A到A的移动(这种疯狂发生在数据集中)。

假设我有一个数据集，看起来是这样的: Equipment包含正在从仓库搬出和搬到仓库的物品的id，当物品从仓库搬到仓库时是TIME_FROM，当物品被搬到仓库时是TIME_TO。

EQUIPMENT   FROM_MAG   TO_MAG   TIME_FROM   TIME_TO
1           A          B        1           2
1           B          C        2           3
1           C          D        3           4
1           D          D        4           5
1           D          E        5           6 
1           E          F        6           7 
1           F          F        7           8 
1           F          F        8           9     
1           F          G        9           10 

然后我希望我的查询的输出没有D-->D和两个F-->F移动，但具有时间列的逻辑延续：

EQUIPMENT   FROM_MAG   TO_MAG   TIME_FROM   TIME_TO
1           A          B        1           2
1           B          C        2           3
1           C          D        3           5

1           D          E        5           6 
1           E          F        6           9 


1           F          G        9           10 

我尝试使用像这样的查询，但这没有给出我想要的结果。顺便说一下，我正在做SAP HANA。

SELECT
EQUIPMENT,
FROM_MAG,
TO_MAG,
min(TIME_FROM),
max(TIME_TO)

FROM MOVEMENTS

GROUP BY EQUIPMENT,
FROM_MAG,
TO_MAG;

用于SQL的Create语句：

CREATE TABLE IF NOT EXISTS MOVEMENTS(
EQUIPMENT NVARCHAR(1) NOT NULL PRIMARY KEY
,FROM_MAG      NVARCHAR(1) NOT NULL
,TO_MAG        NVARCHAR(1) NOT NULL
,TIME_FROM NVARCHAR(1) NOT NULL
,TIME_TO   NVARCHAR(2) NOT NULL
);
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'A',N'B',N'1',N'2');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'B',N'C',N'2',N'3');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'C',N'D',N'3',N'4');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'D',N'D',N'4',N'5');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'D',N'E',N'5',N'6');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'E',N'F',N'6',N'7');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'F',N'F',N'7',N'8');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'F',N'F',N'8',N'9');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'F',N'G',N'9',N'10');

Answer 1

检查了您的查询，我认为在GROUP BY中不需要FROM_MAG。我在MySql中检查了一下，给出了预期的结果。

SELECT
EQUIPMENT,
FROM_MAG,
TO_MAG,
min(TIME_FROM),
max(TIME_TO)
FROM MOVEMENTS
GROUP BY EQUIPMENT,
TO_MAG

Answer 2

我使用了这个，现在它起作用了：

WITH A AS(
SELECT EQUIPMENT,
     FROM_MAG,
     TO_MAG,
     TIME_FROM,
     TIME_FROM,
     TIME_TO

FROM MOVEMENTS
WHERE FROM_MAG<>TO_MAG
ORDER BY TO_NUMBER(TIME_TO))

SELECT EQUIPMENT,
     FROM_MAG,
     TO_MAG,
     TIME_FROM,
     IFNULL(LEAD(TIME_FROM) OVER(PARTITION BY EQUIPMENT ORDER BY TO_NUMBER(TIME_TO)), 
     TIME_TO) TIME_TO
FROM A;