在BigQuery SQL中获取用户最新非“No”变更记录的方法咨询

方法1：窗口函数筛选（推荐） #

利用窗口函数给每个用户的非"No"变更记录按日期倒序排名，取排名第一的内容关联到最新日期数据即可。假设你的表名为user_daily_revenue，包含user_id、date、revenue、changed_status字段，SQL代码如下：

-- 先筛选每个用户最后一次有效变更记录
WITH last_change AS (
    SELECT 
        user_id,
        changed_status,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
    FROM user_daily_revenue
    WHERE changed_status != 'No'
)
-- 关联到最新日期的用户数据，新增最后变更列
SELECT 
    t.*,
    COALESCE(lc.changed_status, '无有效变更') AS last_effective_change
FROM user_daily_revenue t
LEFT JOIN last_change lc 
    ON t.user_id = lc.user_id AND lc.rn = 1
WHERE t.date = (SELECT MAX(date) FROM user_daily_revenue);

这里用COALESCE处理了从未有过有效变更的用户，你可以把'无有效变更'改成你需要的默认值。

方法2：子查询+聚合函数 #

如果不习惯窗口函数，也可以用嵌套子查询实现：

SELECT 
    t.*,
    COALESCE(
        (SELECT changed_status 
         FROM user_daily_revenue 
         WHERE user_id = t.user_id 
           AND changed_status != 'No'
           AND date = (SELECT MAX(date) 
                       FROM user_daily_revenue 
                       WHERE user_id = t.user_id 
                         AND changed_status != 'No')),
        '无有效变更'
    ) AS last_effective_change
FROM user_daily_revenue t
WHERE t.date = (SELECT MAX(date) FROM user_daily_revenue);

逻辑是先找到每个用户最后一次非"No"变更的日期，再匹配对应的变更内容。

方法3：BI工具操作思路（Tableau/Power BI等） #

如果是在可视化工具里处理：

• 新建计算字段最后变更日期：MAX(IF changed_status != 'No' THEN date END) OVER (PARTITION BY user_id)
• 再建计算字段最后变更内容：MAX(IF date = [最后变更日期] THEN changed_status END) OVER (PARTITION BY user_id)
• 最后只展示最新日期的用户数据，把最后变更内容字段拖进表格即可

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