条件聚合查询

Question

我有一张这样的桌子：

OrderID | PhaseID | Timestamp
1       | 1       | 1/1
1       | 2       | 1/2
1       | 3       | 1/3
1       | 2       | 1/4
1       | 4       | 1/5

我试图让一个查询返回每个orderphase组合的最新时间戳，而不是后面跟一个较小的phaseid。如下所示：

OrderID | PhaseID | MaxTimestampWithoutBeingFollowedByLesserPhaseID
1       | 1       | 1/1
1       | 2       | 1/4
1       | 3       | NULL
1       | 4       | 1/5

我一直在转来转去，并提出了一个条件聚合查询的问题。

有没有人能搞清楚这个查询或者给我一些建议？

Answer 1

With a as (
Select OrderID, PhaseID, MaxTimestamp=max([Timestamp])
From orderphase op
Where not exists(select 1 from orderphase where PhaseID < op.PhaseID 
    and [Timstamp]> op.[Timestamp])
Group by OrderID, PhaseID
)
Select distinct o.Orderid, o.PhaseID, MaxTimestamp=a.MaxTimestamp
From orderphase o
Left join a on a.OrderID = o.OrderID and a.PhaseID=o.PhaseID

编辑参考a.MaxTimestamp

Answer 2

1. 为每个订单按时间戳排序阶段。
2. 根据排名将每一行与其后续行联接。
3. 标记PhaseID后跟较小PhaseID的行最后一个结果集有条件地挑选最大时间戳使用MAX(CASE ...)以忽略标记为后跟较小PhaseID的行。

下面是一个示例实现：

;
WITH ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY [Timestamp])
  FROM atable
),
marked AS (
  SELECT
    r1.OrderID,
    r1.PhaseID,
    r1.[Timestamp],
    IsFollowedByLesserPhaseID = CASE WHEN r2.PhaseID IS NULL THEN 0 ELSE 1 END
  FROM ranked r1
    LEFT JOIN ranked r2 ON r1.OrderID = r2.OrderID
                       AND r1.rnk     = r2.rnk - 1
                       AND r1.PhaseID > r2.PhaseID
)
SELECT
  OrderID,
  PhaseID,
  MaxTimestampWithoutBeingFollowedByLesserPhaseID = MAX(
    CASE IsFollowedByLesserPhaseID WHEN 0 THEN [Timestamp] END
  )
FROM marked
GROUP BY
  OrderID,
  PhaseID