Oracle SQL -如何根据客户的开始和结束位置排列我的列表？

Question

我有下表，我想知道哪些顾客去旅行，他/她的start_location是另一位顾客的end_location，他/她比他/她早5分钟。

例如，这就是我所拥有的：

DT                    Customer_Name       Start_location   End_location    Trip_fare
2019-11-01 08:17:42   Jane                  A                 B            $10
2019-11-01 08:18:02   Mary                  C                 A            $7
2019-11-01 08:18:04   Tom                   B                 D            $12
2019-11-01 08:20:11   Harry                 E                 C            $20
2019-11-01 08:21:22   Alex                  D                 A            $5
2019-11-01 08:24:30   Sally                 C                 B            $8

这就是我想要的：

DT                    Customer_Name    Start_location   End_location   
2019-11-01 08:17:42   Jane              A                 B
2019-11-01 08:18:04   Tom               B                 D  (cause Tom's start_location = B = Jane's end_location and the time difference between the 2 trips is within 5 minutes)
2019-11-01 08:21:22   Alex              D                 A
2019-11-01 08:20:11   Harry             E                 C 
2019-11-01 08:24:30   Sally             C                 B

在这里，玛丽已经被从名单中删除，作为她的start_location = 'C'，这不是简的end_location，她比她早5分钟旅行了<=。

我为这个“混乱”的问题道歉。如果您需要进一步澄清，请让我知道！

非常感谢你的帮助！

Answer 1

我有下表，我想知道哪些顾客去旅行，他/她的start_location是另一位顾客的end_location，他/她比他/她早5分钟。

你对这个问题的描述暗示着not exists

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.end_loc = t.start_loc and
                        t2.dt < t.dt and
                        t2.dt >= t.dt - interval '5' minute
                 );

然而，这消除了汤姆，亚历克斯和萨利。从你对这个问题的描述来看，我认为这是正确的。

这里是db<>fiddle。

Answer 2

由于您的查询与来自同一表的客户有关，因此需要一个自联接。也就是说，你用自己的方式加入这个桌子。

SELECT ... FROM mytable JOIN mytable ...

要区分表的一个“实例”和另一个实例，需要别名：

SELECT ... FROM mytable t1 JOIN mytable t2 ...

你需要加入的条件，这就是你的两个客户之间的联系。在您的示例中，这是非常简单的：

SELECT tcust.name  AS name, 
       tother.name AS other_name
  FROM mytable tcust 
  JOIN mytable tother
    ON tcust.start_loc = tother.end_loc
   AND tcust.dt       >= tother.dt - INTERVAL '5' MINUTE;

但是，这个查询得到的结果略有不同。你能找出原因吗？

CREATE TABLE mytable (
  dt DATE, name VARCHAR2(30 CHAR), start_loc VARCHAR2(5 CHAR), 
  end_loc VARCHAR2(5 CHAR), fare NUMBER);

INSERT INTO mytable VALUES (TIMESTAMP '2019-11-01 08:17:42', 'Jane',  'A', 'B', 10);
INSERT INTO mytable VALUES (TIMESTAMP '2019-11-01 08:18:02', 'Mary',  'C', 'A', 7);
INSERT INTO mytable VALUES (TIMESTAMP '2019-11-01 08:18:04', 'Tom',   'B', 'D', 12);
INSERT INTO mytable VALUES (TIMESTAMP '2019-11-01 08:20:11', 'Harry', 'E', 'C', 20);
INSERT INTO mytable VALUES (TIMESTAMP '2019-11-01 08:21:22', 'Alex',  'D', 'A',  5);
INSERT INTO mytable VALUES (TIMESTAMP '2019-11-01 08:24:30', 'Sally', 'C', 'B',  8);

结果：

NAME   OTHER_NAME
Tom    Jane 
Jane   Mary 
Alex   Tom  
Mary   Harry
Sally  Harry
Jane   Alex

在这个问题中解释了5分钟的减法。