如何按locId扁平化数据表并取各属性列的最新非空值？

针对你遇到的问题——将每个locId的多条记录合并为一行，各属性列取最新（date最晚）的非空值，以下提供几种可行的SQL实现方案，避免使用MAX()这类依赖值大小的聚合函数：

方案1：使用LAST_VALUE窗口函数（支持IGNORE NULLS的数据库：PostgreSQL 13+/Oracle/SQL Server 2022+） #

利用LAST_VALUE结合IGNORE NULLS，直接按date排序后取每个locId下各列最后一个非空值，同时取最大date作为结果的日期：

SELECT DISTINCT
  locId,
  LAST_VALUE(col1) OVER (
    PARTITION BY locId 
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) IGNORE NULLS AS col1,
  LAST_VALUE(col2) OVER (
    PARTITION BY locId 
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) IGNORE NULLS AS col2,
  MAX(date) OVER (PARTITION BY locId) AS date
FROM location_data;

原理说明： #

• PARTITION BY locId：按locId分组处理每个位置的记录
• ORDER BY date：确保按时间从旧到新排序
• LAST_VALUE(...) IGNORE NULLS：跳过空值，取分组内最后一个（即最新的）非空属性值
• DISTINCT：去重，因为窗口函数会为每一行生成结果，最终每个locId只保留一行

方案2：使用ROW_NUMBER()子查询（兼容多数数据库：MySQL 8+/PostgreSQL/Oracle等） #

如果你的数据库不支持IGNORE NULLS，可以通过子查询给每个非空属性记录排序，取每个locId下最新的那条：

WITH col1_latest AS (
  -- 找到每个locId最新的非空col1记录
  SELECT locId, col1
  FROM (
    SELECT 
      locId, col1, date,
      ROW_NUMBER() OVER (PARTITION BY locId ORDER BY date DESC) AS rn
    FROM location_data
    WHERE col1 IS NOT NULL
  ) t
  WHERE rn = 1
),
col2_latest AS (
  -- 找到每个locId最新的非空col2记录
  SELECT locId, col2
  FROM (
    SELECT 
      locId, col2, date,
      ROW_NUMBER() OVER (PARTITION BY locId ORDER BY date DESC) AS rn
    FROM location_data
    WHERE col2 IS NOT NULL
  ) t
  WHERE rn = 1
),
max_date AS (
  -- 找到每个locId的最新日期
  SELECT locId, MAX(date) AS date
  FROM location_data
  GROUP BY locId
)
-- 合并结果，处理属性全为空的情况（用COALESCE兜底）
SELECT 
  m.locId,
  COALESCE(c1.col1, (SELECT col1 FROM location_data WHERE locId = m.locId AND col1 IS NOT NULL ORDER BY date DESC LIMIT 1)) AS col1,
  COALESCE(c2.col2, (SELECT col2 FROM location_data WHERE locId = m.locId AND col2 IS NOT NULL ORDER BY date DESC LIMIT 1)) AS col2,
  m.date
FROM max_date m
LEFT JOIN col1_latest c1 ON m.locId = c1.locId
LEFT JOIN col2_latest c2 ON m.locId = c2.locId;

原理说明： #

1. 分别为col1、col2生成子查询，筛选出每个locId下非空且日期最新的记录（通过ROW_NUMBER()倒序排序后取第一条）
2. 单独计算每个locId的最新日期
3. 最后通过LEFT JOIN合并所有结果，用COALESCE处理某属性全为空的极端情况（若该locId下某属性无任何非空值，可返回NULL或自定义默认值）

方案3：单子查询聚合（更简洁的兼容写法） #

通过在子查询中为每个属性生成排序序号，再聚合取对应最新值：

SELECT
  locId,
  MAX(CASE WHEN rn_col1 = 1 THEN col1 END) AS col1,
  MAX(CASE WHEN rn_col2 = 1 THEN col2 END) AS col2,
  MAX(date) AS date
FROM (
  SELECT
    locId,
    col1,
    col2,
    date,
    -- 为col1的非空记录按date倒序排号，空值排最后
    ROW_NUMBER() OVER (
      PARTITION BY locId 
      ORDER BY CASE WHEN col1 IS NOT NULL THEN date ELSE 0 END DESC
    ) AS rn_col1,
    -- 为col2的非空记录按date倒序排号，空值排最后
    ROW_NUMBER() OVER (
      PARTITION BY locId 
      ORDER BY CASE WHEN col2 IS NOT NULL THEN date ELSE 0 END DESC
    ) AS rn_col2
  FROM location_data
) t
GROUP BY locId;

原理说明： #

• 子查询中，CASE WHEN colX IS NOT NULL THEN date ELSE 0 END确保空值的排序优先级最低，非空值按日期倒序排列
• ROW_NUMBER()为每个locId下的记录生成序号，最新的非空属性记录序号为1
• 外层通过MAX(CASE WHEN rn_colX=1 THEN colX END)提取对应最新非空值，MAX(date)取最新日期

以上三种方案均能处理你示例中的场景，最终得到每个locId一行，col1为a、col2为c、date为2024的结果。

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