获取每个组的前1行

我有一个表，我想得到每个组的最新条目。这是表：

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

  • If such function doesn't exist, is there any way I can achieve the output I want?

  • Or at the first place, could this be caused by unnormalized database? I'm thinking, since what I'm looking for is just one row, should that status also be located in the parent table?

Please see the parent table for more information:

Current Documents Table

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

父表是否应该像这样，以便我可以轻松访问其状态？

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

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

883141 次浏览
小开
最佳答案 
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

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

至于标准化与否，这取决于你是否想：

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

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

小开 
SELECT * FROM
DocumentStatusLogs JOIN (
SELECT DocumentID, MAX(DateCreated) DateCreated
FROM DocumentStatusLogs
GROUP BY DocumentID
) max_date USING (DocumentID, DateCreated)

什么数据库服务器？此代码不适用于所有服务器。

关于你问题的后半部分，我认为将状态作为列包含是合理的。您可以将DocumentStatusLogs保留为日志，但仍将最新信息存储在主表中。

顺便说一句，如果您在Documents表中已经有DateCreated列，您可以使用它加入DocumentStatusLogs（只要DateCreated在DocumentStatusLogs中是唯一的）。

编辑：MsSQL不支持USING，因此将其更改为：

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated
小开

我刚刚学习了如何使用cross apply。以下是在这种情况下如何使用它：

 select d.DocumentID, ds.Status, ds.DateCreated
from Documents as d
cross apply
(select top 1 Status, DateCreated
from DocumentStatusLogs
where DocumentID = d.DocumentId
order by DateCreated desc) as ds
小开

在你想避免使用row_count（）的情况下，你也可以使用左连接：

select ds.DocumentID, ds.Status, ds.DateCreated
from DocumentStatusLogs ds
left join DocumentStatusLogs filter
ON ds.DocumentID = filter.DocumentID
-- Match any row that has another row that was created after it.
AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched
where filter.DocumentID is null

对于示例模式，您还可以使用“not in subQuery”，它通常编译为与左连接相同的输出： 

select ds.DocumentID, ds.Status, ds.DateCreated
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
SELECT filter.ID
FROM DocumentStatusLogs filter
WHERE ds.DocumentID = filter.DocumentID
AND ds.DateCreated < filter.DateCreated)

请注意，如果表没有至少一个单列唯一键/约束/索引（在本例中为主键“Id”），则子查询模式将不起作用。

这两个查询往往比row_count（）查询（由查询分析器测量）更“昂贵”。但是，您可能会遇到它们更快地返回结果或启用其他优化的情况。

小开

我的代码从每组中选择前1名

select a.* from #DocumentStatusLogs a where
datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where
a.documentid = b.documentid
order by datecreated desc
)
小开

如果你担心性能，你也可以使用MAX（）：

SELECT *
FROM DocumentStatusLogs D
WHERE DateCreated = (SELECT MAX(DateCreated) FROM DocumentStatusLogs WHERE ID = D.ID)

ROW_NUMBER（）需要对SELECT语句中的所有行进行排序，而MAX不需要。应该大大加快您的查询速度。

小开

这是一个相当古老的话题，但我认为我应该投入我的两分钱，因为公认的答案对我来说并不是特别有效。我在一个大型数据集上尝试了gbn的解决方案，发现它非常慢（在SQLServer 2012中的500万plus记录上>45秒）。从执行计划来看，很明显问题是它需要一个SORT操作，这会显着减慢速度。

这是我从实体框架中提取的一个替代方案，它不需要SORT操作并执行非聚集索引搜索。这将上述记录集上的执行时间减少到<2秒。

SELECT
[Limit1].[DocumentID] AS [DocumentID],
[Limit1].[Status] AS [Status],
[Limit1].[DateCreated] AS [DateCreated]
FROM   (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
FROM (SELECT
[Extent2].[ID] AS [ID],
[Extent2].[DocumentID] AS [DocumentID],
[Extent2].[Status] AS [Status],
[Extent2].[DateCreated] AS [DateCreated]
FROM [dbo].[DocumentStatusLogs] AS [Extent2]
WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
)  AS [Project2]
ORDER BY [Project2].[ID] DESC) AS [Limit1]

现在我假设一些在最初的问题中没有完全指定的东西，但是如果你的表设计是这样的，你的ID列是一个自动递增ID，并且DateCreated在每次插入时都设置为当前日期，那么即使没有运行我上面的查询，你实际上也可以从在ID上订购而不是在DateCreated上订购中获得gbn解决方案的相当大的性能提升（大约一半的执行时间），因为这将提供一个相同的排序顺序，而且它是一个更快的排序。

小开

我在这里对各种建议做了一些时间安排，结果实际上取决于所涉及的表的大小，但最一致的解决方案是使用交叉应用。这些测试是针对SQL服务器2008-R2运行的，使用一个有6500条记录的表和另一个有1.37亿条记录的（相同的模式）。被查询的列是表主键的一部分，表宽度非常小（大约30字节）。泰晤士报由SQL服务器从实际执行计划中报告。

Query                                  Time for 6500 (ms)    Time for 137M(ms)


CROSS APPLY                                    17.9                17.9
SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4
DENSE_RANK() OVER PARTITION                     6.6               907.1

我认为真正令人惊讶的是，无论涉及多少行，交叉应用的时间是多么一致。

小开

从上面验证克林特令人敬畏的正确答案：

下面两个查询之间的性能很有趣。52%是第一个。48%是第二个。使用DISTINCT而不是ORDER BY的性能提高了4%。但是ORDER BY具有按多列排序的优势。

IF (OBJECT_ID('tempdb..#DocumentStatusLogs') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END


CREATE TABLE #DocumentStatusLogs (
[ID] int NOT NULL,
[DocumentID] int NOT NULL,
[Status] varchar(20),
[DateCreated] datetime
)


INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, 'S1', '7/29/2011 1:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, 'S2', '7/30/2011 2:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, 'S1', '8/02/2011 3:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, 'S1', '7/28/2011 4:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, 'S2', '7/30/2011 5:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, 'S3', '8/01/2011 6:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, 'S1', '8/02/2011 7:00:00')

备选案文1：

    SELECT
[Extent1].[ID],
[Extent1].[DocumentID],
[Extent1].[Status],
[Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
OUTER APPLY (
SELECT TOP 1
[Extent2].[ID],
[Extent2].[DocumentID],
[Extent2].[Status],
[Extent2].[DateCreated]
FROM #DocumentStatusLogs AS [Extent2]
WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

备选案文2：

SELECT
[Limit1].[DocumentID] AS [ID],
[Limit1].[DocumentID] AS [DocumentID],
[Limit1].[Status] AS [Status],
[Limit1].[DateCreated] AS [DateCreated]
FROM (
SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #DocumentStatusLogs AS [Extent1]
) AS [Distinct1]
OUTER APPLY  (
SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
FROM (
SELECT
[Extent2].[ID] AS [ID],
[Extent2].[DocumentID] AS [DocumentID],
[Extent2].[Status] AS [Status],
[Extent2].[DateCreated] AS [DateCreated]
FROM #DocumentStatusLogs AS [Extent2]
WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
)  AS [Project2]
ORDER BY [Project2].[ID] DESC
) AS [Limit1]

在MicrosoftSQLServer Management Studio中：突出显示并运行第一个块后，突出显示选项1和选项2，右键单击->[显示估计执行计划]。然后运行整个事情以查看结果。

选项1结果：

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

选项2结果：

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

备注：

当我希望一个连接是1-to-（1）时，我倾向于使用APPLY。

如果我希望连接是1对多或多对多，我将使用JOIN。

我避免使用CTEROW_NUMBER（），除非我需要做一些高级的事情，并且可以接受窗口性能损失。

我还避免在WHERE或ON子句中使用EXISTS/IN子查询，因为我经历过这会导致一些糟糕的执行计划。但是里程数各不相同。在需要的地方和时间查看执行计划和配置文件性能！

小开

试试这个：

SELECT [DocumentID]
,[tmpRez].value('/x[2]', 'varchar(20)') AS [Status]
,[tmpRez].value('/x[3]', 'datetime') AS [DateCreated]
FROM (
SELECT [DocumentID]
,cast('<x>' + max(cast([ID] AS VARCHAR(10)) + '</x><x>' + [Status] + '</x><x>' + cast([DateCreated] AS VARCHAR(20))) + '</x>' AS XML) AS [tmpRez]
FROM DocumentStatusLogs
GROUP BY DocumentID
) AS [tmpQry]
小开 
SELECT o.*
FROM `DocumentStatusLogs` o
LEFT JOIN `DocumentStatusLogs` b
ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
WHERE b.DocumentID is NULL ;

如果您只想按DateCreated返回最近的文档顺序，它将按DocumentID仅返回前1个文档

小开

这是这个话题上最容易找到的问题之一，所以我想给它一个现代的答案（既供我参考，也帮助其他人）。通过使用first_value和over，您可以简短地处理上述查询：

Select distinct DocumentID
, first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
, first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

First_value可以被认为是在使用over子句时完成Select Top 1的一种方式。Over允许在选择列表中分组，因此而不是编写嵌套子查询（像许多现有答案一样），这以更可读的方式完成。希望这有帮助。

小开

我知道这是一个旧的线程，但TOP 1 WITH TIES解决方案非常好，可能有助于阅读解决方案。

select top 1 with ties
DocumentID
,Status
,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

select top 1 with ties子句告诉SQLServer您要返回每个组的第一行。但是SQLServer如何知道如何对数据进行分组？这就是order by row_number() over (partition by DocumentID order by DateCreated desc的用武之地。partition by之后的列/列定义了SQLServer如何对数据进行分组。在每个组中，行将根据order by列进行排序。排序后，每个组中的顶部行将在查询中返回。

有关TOP子句的更多信息，请参阅这里。

小开

此解决方案可用于获取每个分区的前N个最近行（在示例中，N在WHERE语句中为1，分区为doc_id）：

SELECT T.doc_id, T.status, T.date_created FROM
(
SELECT a.*, ROW_NUMBER() OVER (PARTITION BY doc_id ORDER BY date_created DESC) AS rnk FROM doc a
) T
WHERE T.rnk = 1;
小开

这里有3种不同的方法来解决这个问题，以及每个查询的最佳索引选择（请自己尝试索引，看看逻辑读取、运行时间、执行计划。我根据我的经验提供了关于此类查询的建议，但没有针对这个特定问题执行）。

办法1：使用ROW_NUMBER（）。如果行存储索引无法提高性能，您可以尝试非聚集/聚集列存储索引，因为对于具有聚合和分组的查询以及始终按不同列排序的表，列存储索引通常是最佳选择。

;WITH CTE AS
(
SELECT   *,
RN = ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
FROM     DocumentStatusLogs
)
SELECT  ID
,DocumentID
,Status
,DateCreated
FROM    CTE
WHERE   RN = 1;

办法2：使用FIRST_VALUE。如果行存储索引无法提高性能，您可以尝试非聚集/聚集列存储索引，因为对于具有聚合和分组的查询以及始终按不同列排序的表，列存储索引通常是最佳选择。

SELECT  DISTINCT
ID      = FIRST_VALUE(ID) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
,DocumentID
,Status     = FIRST_VALUE(Status) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
,DateCreated    = FIRST_VALUE(DateCreated) OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC)
FROM    DocumentStatusLogs;

办法3：使用CROSS APPLY。在DocumentStatusLogs表上创建涵盖查询中使用的列的行存储索引应该足以覆盖查询，而无需列存储索引。

SELECT  DISTINCT
ID      = CA.ID
,DocumentID = D.DocumentID
,Status     = CA.Status
,DateCreated    = CA.DateCreated
FROM    DocumentStatusLogs D
CROSS APPLY (
SELECT  TOP 1 I.*
FROM    DocumentStatusLogs I
WHERE   I.DocumentID = D.DocumentID
ORDER   BY I.DateCreated DESC
) CA;
小开 
SELECT documentid,
status,
datecreated
FROM   documentstatuslogs dlogs
WHERE  status = (SELECT status
FROM   documentstatuslogs
WHERE  documentid = dlogs.documentid
ORDER  BY datecreated DESC
LIMIT  1)
小开

CROSS APPLY是我用于解决方案的方法，因为它对我和我的客户需求都有效。根据我所读到的，如果他们的数据库大幅增长，应该会提供最佳的整体性能。

小开

我相信这可以像这样完成。这可能需要一些调整，但你可以从组中选择最大值。

这些答案太过分了。

SELECT
d.DocumentID,
MAX(d.Status),
MAX(d1.DateCreated)
FROM DocumentStatusLogs d, DocumentStatusLogs d1
USING DocumentID
GROUP BY 1
ORDER BY 3 DESC
小开

一些数据库引擎*开始支持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、Snowflake…


提交答案