如何在Databricks SQL中不分组使用max()函数获取最新数据？

问题场景 #

有如下数据表datatableX，需要针对每个Account，仅保留Date_ingested最新的完整记录（比如Account 3000只保留Group X的那条）：

错误原因分析 #

1. 分组SQL返回两条记录的原因：
   你之前的SQL把Group和Values也加入了group by，但这两个字段在同一个Account下有不同值，SQL会将它们拆分为两个独立分组，max(Date_ingested)只是每个分组内的最大值，因此返回两条记录，不符合“按Account取最新”的需求。

   -- 错误SQL：Group和Values导致分组拆分
   Select Group, Account, Values, max(Date_ingested) from datatableX 
   group by Group, Account, Values
   

2. 不分组报错的原因：
   直接使用max(Date_ingested)但不分组时，SQL要求所有非聚合字段要么在group by中，要么用聚合函数包裹（比如first()），否则无法确定返回哪一条的非聚合字段值，因此抛出AnalysisException。

正确解决方案 #

方案1：窗口函数（推荐）

用ROW_NUMBER()窗口函数为每个Account的记录按日期倒序编号，取编号为1的记录（最新的那条）：

WITH ranked_records AS (
    SELECT 
        `Group`,  -- Group是SQL关键字，需用反引号包裹
        Account,
        `Values`, -- Values也是关键字，同理处理
        Date_ingested,
        ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Date_ingested DESC) AS record_rank
    FROM datatableX
)
SELECT `Group`, Account, `Values`, Date_ingested
FROM ranked_records
WHERE record_rank = 1;

• PARTITION BY Account：按Account拆分数据，每个Account单独处理
• ORDER BY Date_ingested DESC：在每个Account组内，按入库日期从新到旧排序
• ROW_NUMBER()：给每条记录分配唯一编号，最新的记录编号为1

如果同一个Account存在多条同一天入库的记录，且需要保留所有这些记录，可以把ROW_NUMBER()替换为RANK()或者DENSE_RANK()。

方案2：聚合函数关联

先通过分组找到每个Account的最新入库日期，再关联原表获取对应完整记录：

SELECT d.`Group`, d.Account, d.`Values`, d.Date_ingested
FROM datatableX d
INNER JOIN (
    -- 先获取每个Account的最新日期
    SELECT Account, MAX(Date_ingested) AS latest_date
    FROM datatableX
    GROUP BY Account
) latest_dates 
    ON d.Account = latest_dates.Account 
    AND d.Date_ingested = latest_dates.latest_date;

这个方法也能实现需求，但窗口函数写法更简洁，尤其当需要保留多个非聚合字段时。

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