如何用SQL为各ProductID行写入当日对应值（避免自连接）

需要将每个productid对应指定日期（示例为12.08.2022）的value值，填充到该productid的每一行中，且因数据量极大，需避免自连接操作。

源数据（简化版） #

productid   date         value
1           10.08.2022   4
1           11.08.2022   2        
1           12.08.2022   3
1           13.08.2022   2
1           14.08.2022   1
2           10.08.2022   5
2           11.08.2022   2        
2           12.08.2022   4
2           13.08.2022   1
2           14.08.2022   6

期望输出 #

productid   date         value    value_act_date
1           10.08.2022   4        3
1           11.08.2022   2        3
1           12.08.2022   3        3
1           13.08.2022   2        3
1           14.08.2022   1        3
2           10.08.2022   5        4
2           11.08.2022   2        4
2           12.08.2022   4        4
2           13.08.2022   1        4
2           14.08.2022   6        4

解决方案：窗口函数+条件聚合 #

无需自连接，通过窗口函数对每个productid分组，提取指定日期的value值即可，代码如下：

SELECT 
    productid,
    date,
    value,
    MAX(CASE WHEN date = '12.08.2022' THEN value END) OVER (PARTITION BY productid) AS value_act_date
FROM your_table_name;

核心逻辑说明 #

1. CASE WHEN：筛选当前productid下指定日期的value，其他行返回NULL
2. 窗口聚合：MAX() OVER (PARTITION BY productid)在每个productid分组内，取唯一非空的指定日期值（MAX、MIN、SUM效果一致，因为每个分组仅存在一个指定日期的有效数据）
3. 性能优势：窗口函数仅需扫描一次数据集，避免了自连接带来的大量数据匹配操作，完全适配超大数据量场景

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