如何按分组取Max(Date)并仅统计该周期内的Items数量？

输入示例 #

Name    Item    Date
Box 1   ItemA   7/31/2023
Box 1   ItemB   7/31/2023
Box 1   ItemC   7/31/2023
Box 1   ItemA   6/30/2023
Box 2   ItemA   12/31/2022
Box 2   ItemB   12/31/2022
Box 2   ItemC   12/31/2022
Box 2   ItemD   12/31/2022
Box 2   ItemA   3/31/2023
Box 2   ItemB   3/31/2023
Box 2   ItemC   3/31/2023
Box 2   ItemD   3/31/2023
Box 2   ItemE   3/31/2023
Box 2   ItemF   3/31/2023

期望输出 #

Name    Max_Date    #Items
Box 1   7/31/2023   3
Box 2   3/31/2023   6

现有问题 #

现有代码能获取每个箱子的最近日期，但统计的是该箱子关联的所有物品总数，而非最近日期的物品数量，输出结果如下：

Name    Max_Date    #Items
Box 1   7/31/2023   4
Box 2   3/31/2023   10

现有代码 #

Select Distinct t.[Name], r.LastUpdate, r.itemCount
From (
        Select [Name], Max(Date) as LastUpdate,Count([Item]) as itemCount
        From Table
        Group by [Name]
    ) r
INNER JOIN Table t
ON t.[Name] = r.[Name] AND t.[Date] = r.LastUpdate

解决方案 #

问题出在子查询中，Count([Item])是按Name分组后统计的所有物品总数，而非对应最大日期的物品数量。不需要两次内连接，以下两种方法可以解决：

方法一：先取最大日期，再关联统计 #

SELECT 
    t.[Name],
    r.LastUpdate AS Max_Date,
    COUNT(t.[Item]) AS #Items
FROM (
    -- 先获取每个箱子的最近日期
    SELECT [Name], MAX(Date) AS LastUpdate
    FROM Table
    GROUP BY [Name]
) r
INNER JOIN Table t 
    ON t.[Name] = r.[Name] AND t.[Date] = r.LastUpdate
-- 按箱子和最近日期分组，统计该日期的物品数
GROUP BY t.[Name], r.LastUpdate

方法二：使用窗口函数标记最近日期 #

SELECT 
    [Name],
    Date AS Max_Date,
    COUNT(Item) AS #Items
FROM (
    -- 给每个箱子的所有行标记出该箱子的最近日期
    SELECT 
        *,
        MAX(Date) OVER (PARTITION BY [Name]) AS LatestDate
    FROM Table
) sub
-- 筛选出属于最近日期的行
WHERE Date = LatestDate
GROUP BY [Name], Date

内容的提问来源于stack exchange，提问作者AlanJ