如何获取相同user_id分组下各列对应的最新非NULL值

对每个user_id返回单行数据，每列取该user_id对应的最新非NULL值。

原始样例数据表 #

期望查询结果 #

现有实现问题 #

现有写法仅过滤出每个user_id的最新整行数据，若最新行的某列为NULL，无法自动填充更早行的非NULL值，得到的错误结果如下：

可以用窗口函数FIRST_VALUE实现，按时间倒序排序后取每个列第一个非NULL值，再去重即可，兼容BigQuery、Spark SQL、PostgreSQL 11+等主流SQL引擎。

正确SQL代码 #

WITH
  foo AS (
  SELECT
    10001 user_id,
    'one' a,
    2 b,
    NULL c,
    TIMESTAMP('2014-09-27 12:30:01') t
  UNION ALL
  SELECT
    10002 user_id,
    'seven' a,
    8 b,
    9 c,
    TIMESTAMP('2020-09-27 12:30:02') t
  UNION ALL
  SELECT
    10001 user_id,
    'four' a,
    NULL b,
    6 c,
    TIMESTAMP('2014-09-27 12:30:03') t
    )
SELECT DISTINCT
  user_id,
  FIRST_VALUE(a) OVER(PARTITION BY user_id ORDER BY CASE WHEN a IS NOT NULL THEN t END DESC NULLS LAST) AS a,
  FIRST_VALUE(b) OVER(PARTITION BY user_id ORDER BY CASE WHEN b IS NOT NULL THEN t END DESC NULLS LAST) AS b,
  FIRST_VALUE(c) OVER(PARTITION BY user_id ORDER BY CASE WHEN c IS NOT NULL THEN t END DESC NULLS LAST) AS c
FROM foo
ORDER BY user_id;

逻辑说明 #

• 每个字段单独处理，PARTITION BY user_id按用户分组
• 排序逻辑：仅当字段非NULL时按时间倒序排列，NULL值排到最后
• FIRST_VALUE取分组内排序后的第一个值，也就是该字段最新的非NULL值
• 最后加DISTINCT去重，每个用户只返回一行结果
  如果所用SQL引擎不支持上述写法，也可以用分组聚合的方式兼容：

WITH
  foo AS (
  SELECT
    10001 user_id,
    'one' a,
    2 b,
    NULL c,
    TIMESTAMP('2014-09-27 12:30:01') t
  UNION ALL
  SELECT
    10002 user_id,
    'seven' a,
    8 b,
    9 c,
    TIMESTAMP('2020-09-27 12:30:02') t
  UNION ALL
  SELECT
    10001 user_id,
    'four' a,
    NULL b,
    6 c,
    TIMESTAMP('2014-09-27 12:30:03') t
    ),
-- 给每个字段的非NULL值按时间倒序编号
ranked AS (
  SELECT 
    user_id,
    a,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY CASE WHEN a IS NOT NULL THEN t END DESC NULLS LAST) rn_a,
    b,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY CASE WHEN b IS NOT NULL THEN t END DESC NULLS LAST) rn_b,
    c,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY CASE WHEN c IS NOT NULL THEN t END DESC NULLS LAST) rn_c
  FROM foo
)
SELECT 
  user_id,
  MAX(CASE WHEN rn_a = 1 THEN a END) a,
  MAX(CASE WHEN rn_b = 1 THEN b END) b,
  MAX(CASE WHEN rn_c = 1 THEN c END) c
FROM ranked
GROUP BY user_id
ORDER BY user_id;

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