如何为SQL中重复的字段组合选取最优填充值？

原SQL语句：

select
   date,
   session_id,
   article_id,
   category
from
   table 

当前查询结果：

需求：
按date、session_id、article_id的组合保留唯一行，category字段取最优填充值：优先选取非空且非空字符串的有效值；若组内仅存在空字符串或null，则保留null。示例结果应保留一行animals和一行sports。

解决方案 #

方案1：分组聚合（通用SQL） #

通过分组筛选有效category值，利用聚合函数提取有效值：

select
    date,
    session_id,
    article_id,
    max(case when category is not null and category <> '' then category end) as category
from
    table
group by
    date, session_id, article_id

逻辑说明：

• 用case表达式过滤出非空且非空字符串的category值
• max函数自动忽略null，组内存在有效值则返回该值；无有效值时返回null，符合需求

方案2：窗口函数去重（适用于PostgreSQL、MySQL 8+等支持窗口函数的数据库） #

通过窗口函数优先选取有效值，再去重：

select distinct
    date,
    session_id,
    article_id,
    first_value(case when category is not null and category <> '' then category end ignore nulls) over (
        partition by date, session_id, article_id 
        order by case when category is not null and category <> '' then 0 else 1 end
    ) as category
from
    table

逻辑说明：

• partition by按目标组合分组
• order by将有效值排在组内前列
• first_value(ignore nulls)跳过null，直接取组内第一个有效值；无有效值时返回null
• distinct去除重复行

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