sql - 基于多个字段对集合中的记录进行排名

Question

╔════════════════════╦═══════════╦══════════╦═══════╦══════╗
║      ENDTIME       ║ TRAILERID ║ POSITION ║ STORE ║ STOP ║
╠════════════════════╬═══════════╬══════════╬═══════╬══════╣
║ 11/7/2017 15:15:44 ║       145 ║ L1       ║ BOS   ║    1 ║
║ 11/7/2017 15:16:57 ║       145 ║ L2       ║ NY    ║    3 ║
║ 11/7/2017 15:17:24 ║       145 ║ R1       ║ NY    ║    2 ║
║ 11/7/2017 15:17:57 ║       145 ║ R2       ║ NY    ║    1 ║
║ 11/7/2017 15:18:26 ║       145 ║ L3       ║ CT    ║    2 ║
║ 11/7/2017 15:34:24 ║       145 ║ R3       ║ CT    ║    1 ║
║ 11/7/2017 16:02:01 ║       999 ║ L1       ║ NJ    ║    2 ║
║ 11/7/2017 16:22:33 ║       999 ║ R1       ║ NJ    ║    1 ║
║ 11/7/2017 16:23:14 ║       999 ║ L2       ║ WA    ║    3 ║
║ 11/7/2017 16:27:16 ║       999 ║ R2       ║ WA    ║    2 ║
║ 11/7/2017 16:27:46 ║       999 ║ L3       ║ WA    ║    1 ║
╚════════════════════╩═══════════╩══════════╩═══════╩══════╝

我正在尝试将停止字段更改为如下所示

╔════════════════════╦═══════════╦══════════╦═══════╦══════╗
║      ENDTIME       ║ TRAILERID ║ POSITION ║ STORE ║ STOP ║
╠════════════════════╬═══════════╬══════════╬═══════╬══════╣
║ 11/7/2017 15:15:44 ║       145 ║ L1       ║ BOS   ║    3 ║
║ 11/7/2017 15:16:57 ║       145 ║ L2       ║ NY    ║    2 ║
║ 11/7/2017 15:17:24 ║       145 ║ R1       ║ NY    ║    2 ║
║ 11/7/2017 15:17:57 ║       145 ║ R2       ║ NY    ║    2 ║
║ 11/7/2017 15:18:26 ║       145 ║ L3       ║ CT    ║    1 ║
║ 11/7/2017 15:34:24 ║       145 ║ R3       ║ CT    ║    1 ║
║ 11/7/2017 16:02:01 ║       999 ║ L1       ║ NJ    ║    2 ║
║ 11/7/2017 16:22:33 ║       999 ║ R1       ║ NJ    ║    2 ║
║ 11/7/2017 16:23:14 ║       999 ║ L2       ║ WA    ║    1 ║
║ 11/7/2017 16:27:16 ║       999 ║ R2       ║ WA    ║    1 ║
║ 11/7/2017 16:27:46 ║       999 ║ L3       ║ WA    ║    1 ║
╚════════════════════╩═══════════╩══════════╩═══════╩══════╝

我的尝试是使用 row_number 和 rank，但我无法获得正确的结果。正在给我第一个表格结果。

SELECT ps.ENDTIME, ps.TRUCKID as TRAILERID, ps.CONTAINERPOSITION as POSITION,ps.STORE,RANK() OVER( PARTITION by ps.STORE  ORDER BY ps.ENDTIME DESC)AS STOP 
FROM TRAILERDATA ps
ORDER BY ps.ENDTIME ASC

最重要的是，即使 ENDTIME 的顺序不正确，也要按排名相同的商店组进行标记。

Answer 1

示例数据

DECLARE @T TABLE 
    (EndTime datetime2(0), TrailerID int, Position varchar(50), Store varchar(50));
INSERT INTO @T (EndTime, TrailerID, Position, Store) VALUES
('2017-11-07 15:15:44',145,'L1','BOS'),
('2017-11-07 15:16:57',145,'L2','NY'),
('2017-11-07 15:17:24',145,'R1','NY'),
('2017-11-07 15:17:57',145,'R2','NY'),
('2017-11-07 15:18:26',145,'L3','CT'),
('2017-11-07 15:34:24',145,'R3','CT'),
('2017-11-07 16:02:01',999,'L1','NJ'),
('2017-11-07 16:22:33',999,'R1','NJ'),
('2017-11-07 16:23:14',999,'L2','WA'),
('2017-11-07 16:27:16',999,'R2','WA'),
('2017-11-07 16:27:46',999,'L3','WA');

查询

WITH
CTE
AS
(
    SELECT
        EndTime, TrailerID, Position, Store
        ,MAX(EndTime) OVER (PARTITION BY TrailerID, Store) AS SortingTime
    FROM @T
)
SELECT
    EndTime, TrailerID, Position, Store
    ,SortingTime
    ,DENSE_RANK() OVER (PARTITION BY TrailerID ORDER BY SortingTime DESC) AS Stop
FROM CTE
ORDER BY TrailerID, Stop DESC, EndTime;

结果

+---------------------+-----------+----------+-------+---------------------+------+
|       EndTime       | TrailerID | Position | Store |     SortingTime     | Stop |
+---------------------+-----------+----------+-------+---------------------+------+
| 2017-11-07 15:15:44 |       145 | L1       | BOS   | 2017-11-07 15:15:44 |    3 |
| 2017-11-07 15:16:57 |       145 | L2       | NY    | 2017-11-07 15:17:57 |    2 |
| 2017-11-07 15:17:24 |       145 | R1       | NY    | 2017-11-07 15:17:57 |    2 |
| 2017-11-07 15:17:57 |       145 | R2       | NY    | 2017-11-07 15:17:57 |    2 |
| 2017-11-07 15:18:26 |       145 | L3       | CT    | 2017-11-07 15:34:24 |    1 |
| 2017-11-07 15:34:24 |       145 | R3       | CT    | 2017-11-07 15:34:24 |    1 |
| 2017-11-07 16:02:01 |       999 | L1       | NJ    | 2017-11-07 16:22:33 |    2 |
| 2017-11-07 16:22:33 |       999 | R1       | NJ    | 2017-11-07 16:22:33 |    2 |
| 2017-11-07 16:23:14 |       999 | L2       | WA    | 2017-11-07 16:27:46 |    1 |
| 2017-11-07 16:27:16 |       999 | R2       | WA    | 2017-11-07 16:27:46 |    1 |
| 2017-11-07 16:27:46 |       999 | L3       | WA    | 2017-11-07 16:27:46 |    1 |
+---------------------+-----------+----------+-------+---------------------+------+

在结果中，您可以看到 SortingTime 如何计算您需要的 DENSE_RANK。本质上，SortingTime 定义了按 TrailerID 和 Store 的分组，还提供了一种方便的排序方式。

---

上面的查询依赖于 EndTime 对于不同的 Stores 是不同的。更具体地说，MAX 时间不同。如果您有两个具有相同最大 EndTime 的 Stores，则查询将为它们分配相同的 Stop。

如果可能，则将Store 添加到ORDER BY:

WITH
CTE
AS
(
    SELECT
        EndTime, TrailerID, Position, Store
        ,MAX(EndTime) OVER (PARTITION BY TrailerID, Store) AS SortingTime
    FROM @T
)
SELECT
    EndTime, TrailerID, Position, Store
    ,SortingTime
    ,DENSE_RANK() OVER (PARTITION BY TrailerID ORDER BY SortingTime DESC, Store) AS Stop
FROM CTE
ORDER BY TrailerID, Stop DESC, EndTime;