AWS Redshift(PostgreSQL)按规则前向填充列空值技术问询

数据集 #

create schema m;
create table m.parent_child_lvl_1(customer_id,date,order_type,order_id,sub_id)
as values
 (108384372,'18/09/2023'::date,'sub_parent_first_order',5068371361861,407284605)
,(108384372, '13/11/2023', 'sub_order', 5134167539781, null)
,(108384372, '8/01/2024', 'sub_order', 5214687526981, null)
,(108384372, '4/03/2024', 'sub_order', 5283166126149, null)
,(108384372, '18/06/2024', 'sub_parent_order', 5421811138629, 500649255)
,(108384372, '12/08/2024', 'sub_order', 5508433641541, null)
,(108384372, '12/08/2024', 'sub_order', 5508433641541, null);

需求 #

• 按customer_id分组、date排序
• 将sub_id列的空值用前一个非空值填充，直到遇到下一个非空值为止
• 数据集共1500万行，需保证执行效率

尝试过的方法 #

试过lead()、lag()、lastvalue()、coalesce()函数，但这些只能填充非空值后的第一个空值，无法满足连续填充需求。

当前代码问题 #

以下代码仅对部分记录有效，部分空值未被正确填充：

SELECT
  first_value(sub_id) OVER (
    PARTITION BY customer_id, value_partition 
    ORDER BY customer_id, date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS parent_sub_id,
  customer_id,
  order_id
FROM (
  SELECT
customer_id,
order_id,
sub_id,
date,
    SUM(CASE WHEN sub_id IS NULL THEN 0 ELSE 1 END) OVER (
      ORDER BY customer_id, date 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS value_partition
from m.parent_child_lvl_1 
where customer_id in ('227330109','90872199','102694972')
and
order_type in (
'sub_parent_first_order',
'sub_parent_order',
'sub_order'
)
  ORDER BY date ASC
) AS q
ORDER BY customer_id, date
 ;

补充说明：实际返回结果（A列）与预期结果（B列）不符，部分空值未被正确填充。

问题核心：子查询中的SUM()窗口函数未按customer_id分区，导致不同用户的分区标识被错误累加，分组逻辑失效。

正确解决方案 #

高效版SQL（适用于PostgreSQL 11+） #

利用last_value配合IGNORE NULLS直接实现连续填充，性能最优：

SELECT
  customer_id,
  date,
  order_type,
  order_id,
  last_value(sub_id) OVER (
    PARTITION BY customer_id 
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    IGNORE NULLS
  ) AS parent_sub_id
FROM m.parent_child_lvl_1
WHERE 
  customer_id IN ('227330109','90872199','102694972')
  AND order_type IN ('sub_parent_first_order','sub_parent_order','sub_order')
ORDER BY customer_id, date;

兼容低版本PostgreSQL方案 #

如果版本低于11不支持IGNORE NULLS，修正分区累加逻辑即可：

SELECT
  customer_id,
  date,
  order_type,
  order_id,
  first_value(sub_id) OVER (
    PARTITION BY customer_id, value_partition 
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS parent_sub_id
FROM (
  SELECT
    customer_id,
    date,
    order_type,
    order_id,
    sub_id,
    -- 按用户分区累加，每个非空sub_id触发分区标识递增
    SUM(CASE WHEN sub_id IS NOT NULL THEN 1 ELSE 0 END) OVER (
      PARTITION BY customer_id 
      ORDER BY date 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS value_partition
  FROM m.parent_child_lvl_1
  WHERE 
    customer_id IN ('227330109','90872199','102694972')
    AND order_type IN ('sub_parent_first_order','sub_parent_order','sub_order')
) AS q
ORDER BY customer_id, date;

性能优化建议 #

1. 建立复合索引：CREATE INDEX idx_parent_child_cust_date ON m.parent_child_lvl_1(customer_id, date);，加速分组与排序
2. 覆盖索引优化：如果仅查询指定字段，建立覆盖索引减少磁盘IO：CREATE INDEX idx_parent_child_cover ON m.parent_child_lvl_1(customer_id, date) INCLUDE (order_type, order_id, sub_id);
3. 分批处理：若一次性处理1500万行压力大，可按customer_id范围分批执行查询

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