如何从事务表中获取最新账单日期对应的ID、产品及营收组合

原SQL问题分析 #

你当前的写法将Id、product、product_category、Revenue四个字段全部作为分组维度，只要这四个字段的取值存在差异就会生成独立分组，因此会返回全量维度组合的查询结果，无法实现仅保留最新账单日期对应条目的需求。

调整方案 #

方案1：窗口函数法（适配MySQL8.0+、PostgreSQL、Hive、Spark SQL等所有支持窗口函数的主流数据库，推荐使用）

SELECT Id, product, product_category, Revenue, Billing_date
FROM (
    SELECT 
        Id, product, product_category, Revenue, Billing_date,
        -- 按需要的分组维度分区，账单日期倒序排序后最新记录排名为1
        ROW_NUMBER() OVER (PARTITION BY Id, product, product_category, Revenue ORDER BY Billing_date DESC) AS rn
    FROM Table1
) t
WHERE rn = 1;

如果不需要将Revenue作为分组维度，仅需要保留每个Id+product+product_category组合对应的最新记录，删除PARTITION BY中的Revenue字段即可。
如果同一分组下存在多条相同最新日期的记录需要全部保留，将ROW_NUMBER()替换为RANK()即可。

方案2：关联子查询法（适配不支持窗口函数的低版本数据库，比如MySQL5.x）

SELECT a.Id, a.product, a.product_category, a.Revenue, a.Billing_date
FROM Table1 a
INNER JOIN (
    SELECT Id, product, product_category, Revenue, MAX(Billing_date) AS max_date
    FROM Table1
    GROUP BY Id, product, product_category, Revenue
) b 
ON a.Id = b.Id 
AND a.product = b.product 
AND a.product_category = b.product_category 
AND a.Revenue = b.Revenue 
AND a.Billing_date = b.max_date;

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