如何按name分组获取每日最新count的最后两个值并横向展示？

给定一张定期更新的表（load_id字段递增），需要实现以下查询需求：

• 针对name列的每个取值
• 获取count列的最后两个值（“最后”指每日max(load_id)对应的记录）
• 将这两个值并排展示

输入示例

name,day,count,load_id
John,10,9,14
John,9,5,12
John,9,7,13
John,8,5,12
John,8,2,11
Paul,9,0,12
Paul,9,1,13
Paul,7,1,11
Paul,7,9,10
Mark,7,10,11
Mark,7,11,10
Ned,7,0,10

步骤1 - 提取每日最新记录
先筛选出每个用户每天load_id最大的记录，得到如下结果：

name,day,count
John,10,9
John,9,7
John,8,5
Paul,9,1
Paul,7,1
Mark,7,10
Ned,7,0

预期输出

name,last_day_last_count,prev_day_last_count
John,9,7
Paul,1,1
Mark,10,NULL
Ned,0,NULL

你尝试的SQL代码

WITH RankedData AS (
  SELECT
    name,
    day,
    count,
    load_id,
    ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC) AS rn
  FROM test
)

SELECT *
FROM RankedData
WHERE rn = 1
ORDER BY name, day DESC

完整解决方案 #

你的第一步已经正确筛选出了每日最新记录，接下来只需在此基础上对每个用户的记录按日期降序排序，取前两个count值横向展示即可：

WITH DailyLatest AS (
  -- 先获取每个用户每日的最新记录（load_id最大的那条）
  SELECT
    name,
    day,
    count,
    -- 对每个用户的记录按日期从新到旧排序
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY day DESC) AS user_day_rank
  FROM (
    SELECT
      name,
      day,
      count,
      ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC) AS rn
    FROM test
  ) t
  WHERE rn = 1
)
SELECT
  name,
  -- 取最近一天的count值
  MAX(CASE WHEN user_day_rank = 1 THEN count END) AS last_day_last_count,
  -- 取前一天的count值，没有则返回NULL
  MAX(CASE WHEN user_day_rank = 2 THEN count END) AS prev_day_last_count
FROM DailyLatest
GROUP BY name
ORDER BY name;

代码说明 #

1. DailyLatest 公共表表达式：内层先通过窗口函数筛选每个用户每天的最新记录，外层再对每个用户的记录按日期降序标记排名。
2. 最终聚合查询：用条件聚合把排名第1和第2的count值分别提取出来，实现并排展示；如果用户只有一条记录，第二个字段自然返回NULL，完全符合预期输出要求。

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