SQL使用Max分组返回多值 如何获取单用户最新日期记录

你的现有写法没有实现用户维度的最新记录取数，核心错误是GROUP BY粒度过细：你把除tracked_date_id外的所有返回字段都放进了分组规则，只要同一个user_id下存在体重、账单日期等字段取值不同的记录，就会拆分出多行。此时你写的max(tracked_date_id)只是每个细分小分组的最大日期，而非每个用户全局最新的称重日期，自然会出现单用户返回多行的问题。

用窗口函数ROW_NUMBER()先给每个用户的称重记录按日期倒序打标，只取排位为1的最新记录关联其他维度表即可，代码兼容你当前使用的BigQuery语法：

WITH user_latest_weight AS (
    SELECT 
        tracked_date_id AS weigh_in_date,
        member_id,
        weight_in_lbs,
        ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY tracked_date_id DESC) AS rn
    FROM `datalake.star_schema.fact_weight_tracking`
)
SELECT 
    w.weigh_in_date, 
    dm.user_id, 
    dp.description, 
    fm.next_bill_date_id, 
    w.weight_in_lbs, 
    dm.goal_weight
FROM `datalake.star_schema.fact_billing` fm
INNER JOIN `datalake.star_schema.dim_member` dm
    ON dm.member_id = fm.member_id
INNER JOIN `datalake.star_schema.dim_product` dp
    ON dp.product_id = fm.product_id
INNER JOIN user_latest_weight w 
    ON w.member_id = dm.member_id 
    AND w.rn = 1 -- 仅关联每个用户最新的1条称重记录
WHERE
    dm.life_time_flag = "True" 
    AND dm.is_fran_flag = "False" 
    AND dm.classic_country = "US" 
    AND dp.description LIKE '%Lifetime%'
    AND CAST(fm.next_bill_date_id AS STRING) LIKE '%20221001%'

• 如果存在同一用户同一tracked_date_id下有多条称重记录的场景，可在窗口函数的ORDER BY后补充称重记录创建时间、自增主键等二级排序字段，避免随机返回同天的其中一条记录；如果需要保留同天所有记录，可将ROW_NUMBER()替换为RANK()
• 原SQL中tracked_date_id未指定所属表别名，多表join时容易触发字段歧义报错，修正后的代码统一加了表别名规避这类问题
• 你当前用CAST(fm.next_bill_date_id AS STRING) LIKE '%20221001%'做日期过滤的写法性能很差，如果next_bill_date_id是YYYYMMDD格式的数值/日期类型，建议直接写等值判断fm.next_bill_date_id = 20221001，能避免全表扫描，提升查询速度

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