基于上一日期分数变化筛选行的SQL Presto实现问询

基础数据表 #

目标结果 #

实现方案 #

无需拆分表做连接，直接用Presto支持的LAG()窗口函数就能实现需求，该函数可获取同一分组内上一行的指定字段值，对比当前行与上一行的分数变化即可。

SQL代码 #

WITH ranked_scores AS (
    SELECT 
        date,
        customer_id,
        score1,
        score2,
        -- 提取同一客户下上一条记录的score1
        LAG(score1) OVER (PARTITION BY customer_id ORDER BY date) AS prev_score1,
        -- 提取同一客户下上一条记录的score2
        LAG(score2) OVER (PARTITION BY customer_id ORDER BY date) AS prev_score2
    FROM your_table_name
)
SELECT date, customer_id, score1, score2
FROM ranked_scores
-- 筛选分数变化的记录，同时排除每个客户的第一条记录（无前置数据）
WHERE (score1 != prev_score1 OR score2 != prev_score2)
  AND prev_score1 IS NOT NULL;

代码说明 #

1. LAG()窗口函数：PARTITION BY customer_id限定仅在同一客户的记录范围内查找上一行，ORDER BY date保证按日期顺序获取前置记录。
2. 筛选条件：score1 != prev_score1 OR score2 != prev_score2判断当前行分数是否与上一行存在差异；prev_score1 IS NOT NULL过滤掉每个客户的第一条记录（该记录没有前置对比数据）。

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