获取每组的前 1 行

Question

我有一张表，我想获取每个组的最新条目。这是表格：

DocumentStatusLogs 表

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

该表将按 DocumentID 分组并按 DateCreated 降序排序。对于每个 DocumentID ，我想获得最新状态。

我的首选输出：

 | DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |

• 是否有任何聚合函数可以仅从每个组中获取顶部？见伪代码 GetOnlyTheTop 如下：

   SELECT
    DocumentID,
    GetOnlyTheTop(Status),
    GetOnlyTheTop(DateCreated)
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ORDER BY DateCreated DESC

• 如果这样的功能不存在，有什么办法可以实现我想要的输出？

• 或者首先，这可能是由非规范化数据库引起的吗？我在想，既然我要找的只是一行，那 status 也应该位于父表中吗？

有关详细信息，请参阅父表：

当前 Documents 表

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

父表应该是这样的，以便我可以轻松访问它的状态吗？

 | DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

更新 我刚刚学会了如何使用“应用”，它可以更容易地解决这些问题。

原文由 kazinix 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

如果您希望每天有 2 个条目，那么这将任意选择一个。要获取一天的两个条目，请改用 DENSE_RANK

至于是否标准化，这取决于您是否要：

• 在2个地方保持状态
• 保留状态历史
• …

就目前而言，您保留状态历史记录。如果您也想要父表中的最新状态（这是非规范化），您需要一个触发器来维护父表中的“状态”。或删除此状态历史记录表。

原文由 gbn 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

一些数据库引擎*开始支持 QUALIFY 子句，该子句允许过滤窗口函数的结果（接受的答案使用）。

所以接受的答案可以变成

SELECT *, ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
FROM DocumentStatusLogs
QUALIFY rn = 1

请参阅这篇文章以获得深入的解释： https ://jrandrews.net/the-joy-of-qualify

您可以使用此工具查看哪个数据库支持此子句： https ://www.jooq.org/translate/ 当目标方言不支持时，可以选择转换限定子句。

*Teradata、BigQuery、H2、雪花……

原文由 MasterKiller 发布，翻译遵循 CC BY-SA 4.0 许可协议