为表中的字段From和To放置在可逆列中的表建立查询 [英] Building a query for a table whose fields From and To are placed in reversible columns

问题描述

我有一个这样的表:

Journey  HHMM   Chkpt1  Chkpt2
41   1600   AAA BBB 
41   1601   AAA BBB
41   1602   AAA BBB
41   1603   CCC DDD
41   1603   BBB CCC
41   1604   DDD EEE

点Chkpt1和Chkpt2定义了一条道路的路段. 在这种情况下，经过41段路程的车辆经过了以下细分市场:AAA-BBB，BBB-CCC，CCC-DDD，DDD-EEE.

The points Chkpt1 and Chkpt2 define a segment from a road. In this case, the vehicle that did journey 41 passed by these segments: AAA-BBB, BBB-CCC, CCC-DDD, DDD-EEE.

我的问题: 我需要获得旅程中的第一点和最后一点，以及它们各自的时间. 在这种情况下，答案是AAA(1600)和EEE(1604).

My question: I need to get the first and last points from the journey, and their respective times. In this case, the answer is AAA (1600) and EEE (1604).

要获得此答案，需要考虑一些要点:

There are some points to be considered to get this answer:

1)每分钟跟踪一次轨迹.这样可以生成具有相同检查点的多行.

1) the trajectory is tracked at each minute. This can generate multiple lines with the same checkpoints.

2)跟踪每个段.如果车辆在同一分钟内从路段移至另一路段，则同一分钟可以插入多条线-并且由于某些实施原因，它们可能未按时间顺序显示.

2) every segment is tracked. If the vehicle moves from a segment to other during a same minute, more than a line can be inserted at the same minute - and, for some reasons of implementation, they may appear not in chronological order.

3)最棘手的点-车辆不一定从Chkpt1移到Chkpt2.它可能正在从Chkpt2移到Chkpt1.问题是如何推导实际方向(此表上没有方向"列，因此不能更改该表).

3) the trickiest point - the vehicle in not necessarily moving from Chkpt1 to Chkpt2. It may be moving from Chkpt2 to Chkpt1. The question is how to deduce the real direction (there´s not a Direction column on this table and the table must not be changed).

例如:

Journey HHMM    Chkpt1  Chkpt2 
42  1700    YYY ZZZ 
42  1701    YYY ZZZ 
42  1702    WWW XXX 
42  1702    XXX YYY 
42  1702    VVV WWW 
42  1703    UUU VVV 

在这种情况下，车辆从ZZZ行驶到UUU，答案是ZZZ(1700)/UUU(1703).

In this case, the vehicle is moving from ZZZ to UUU and the answer is ZZZ (1700) / UUU (1703).

在每个段中，它从Chkpt2到Chkpt1. 在同一旅程中，必须沿相同方向跟踪所有线路.

In each segment, it comes from Chkpt2 to Chkpt1. During a same journey, all the lines must be tracked with the same direction.

对于旅程41，所有移动都是从Chkpt1到Chkpt2.我们得到了比较Chkpt2(1602)和Chkpt1(1603)的轨迹，因此我们看到车辆从AAA-BBB移到BBB-CCC，依此类推.

For journey 41, all movements are from Chkpt1 to Chkpt2. We get the trajectory comparing Chkpt2 (1602) to Chkpt1 (1603), so we see the vehicle moved from AAA-BBB to BBB-CCC, and so on.

对于旅程42，所有运动都是从Chkpt2到Chkpt1.我们得到了比较Chkpt1(1700)和Chkpt2(1702)的轨迹，因此我们看到车辆从ZZZ-YYY移到YYY-XXX，依此类推.

For journey 42, all movements are from Chkpt2 to Chkpt1. We get the trajectory comparing Chkpt1 (1700) to Chkpt2 (1702), so we see the vehicle moved from ZZZ-YYY to YYY-XXX, and so on.

所需的结果将是:

Journey ChkptStart  Time1   ChkptEnd    Time2 
41  AAA 1600    EEE 1604 
42  ZZZ 1700    UUU 1703 

嗯，我对这种复杂的查询没有足够的SQL经验.

Well, I don´t have enough experience with SQL for a complex query as this.

有人可以帮我解决这个问题吗?

Can anyone give me a help about this problem ?

推荐答案

是的，它有点笨拙(尽管不如我最初想象的那么糟糕):

Yup, it's a doozy (although not as bad as I first thought):

WITH Deduplicated AS (SELECT id, checkpoint1, checkpoint2, MIN(hhmm) as startTime, MAX(hhmm) as endTime
                      FROM Journey
                      GROUP BY id, checkpoint1, checkpoint2),
     Path (id, originPoint, originStartTime, originEndTime, checkpoint2, startTime, endTime, lev)
          AS (SELECT id, checkpoint1, startTime, endTime, checkpoint2, startTime, endTime, 0
              FROM Deduplicated
              WHERE NOT EXISTS (SELECT 1
                                FROM Journey b
                                WHERE b.id = Deduplicated.id
                                      AND b.checkpoint2 = Deduplicated.checkpoint1)
              UNION ALL
              SELECT Path.id, Path.originPoint, Path.originStartTime, Path.originEndTime,
                     Deduplicated.checkpoint2, Deduplicated.startTime, Deduplicated.endTime, lev + 1
              FROM Path
              JOIN Deduplicated
                ON Deduplicated.id = Path.id
                   AND Deduplicated.checkpoint1 = Path.checkpoint2)
SELECT id, 
       CASE WHEN originStartTime > startTime 
                 OR originEndTime > endTime
            THEN checkPoint2
            ELSE originPoint END AS checkpointStart, 
       LEAST(originStartTime, startTime) AS time1,
       CASE WHEN originStartTime > startTime 
                 OR originEndTime > endTime
            THEN originPoint
            ELSE checkPoint2 END AS checkpointEnd, 
       GREATEST(originEndTime, endTime) AS endTime
FROM (SELECT Path.*, MAX(lev) OVER(PARTITION BY id) AS lim
      FROM Path) Filtered
WHERE lev = lim

小提琴演示

通过具有多个开始时间"段的轻度病理情况.本质上，最好的方法是忽略直到完成递归图(成功)之后的时间，然后检查时间戳记的方向是否与递归方向匹配.

Passes the mild pathological case where there are multiple "starting time" segments. Essentially, the best way is to ignore the time until after completing the recursive graph (which succeeds), then check to see if the direction of the timestamps matches the recursive direction.

这篇关于为表中的字段From和To放置在可逆列中的表建立查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！