Oracle SQL查询：如何将周五数据填充至周六、周日日期行

源表示例数据：

已尝试的SQL代码：

With CTE as
(SELECT 
    MIN(date'2023-01-06' + level - 1) min_d,
    MAX(date'2023-01-09' + level - 1) max_d
FROM   dual
CONNECT BY LEVEL <= (date'2023-01-01' - date'2023-12-31' + 1)
)
SELECT 
    SALES_DATE,
    NVL(PRODUCT_ID, LAST_VALUE(PRODUCT_ID) IGNORE NULLS OVER (ORDER BY SALES_DATE, PRODUCT_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS PRODUCT_ID    
   FROM (
     SELECT 
        min_d + level - 1 as d
      FROM CTE
    CONNECT BY min_d + level - 1 <= max_d
  ) dt LEFT JOIN Source_Table ST ON (dt.d = ST.SALES_DATE)
GROUP BY PRODUCT_ID, d
ORDER BY SALES_DATE, PRODUCT_ID

当前输出结果：

期望输出结果：

问题分析 #

原代码存在两处核心问题：

1. 日期生成逻辑错误：CTE中计算min_d和max_d的CONNECT BY条件完全不符合需求，导致生成的日期范围混乱；
2. 数据关联方式错误：LEFT JOIN仅能关联到当天的Product ID，周末无数据时，LAST_VALUE窗口函数只能取到历史第一个ID，无法复制周五的所有Product ID到周末行。

正确实现方案 #

核心思路是：先生成目标连续日期范围，为每个日期确定对应的基准填充日期（周六取前一天周五，周日取前两天周五，其他日期取自身），最后将基准日期与源表关联，获取所有Product ID。

WITH date_range AS (
    -- 生成需要覆盖的连续日期范围，可根据需求调整起止日期
    SELECT DATE '2023-01-06' + LEVEL - 1 AS sales_date
    FROM dual
    CONNECT BY LEVEL <= DATE '2023-01-09' - DATE '2023-01-06' + 1
),
date_with_base AS (
    -- 为每个日期分配对应的基准填充日期
    SELECT 
        sales_date,
        CASE 
            -- 用英文标识判断周六周日，避免NLS_DATE_LANGUAGE影响
            WHEN TO_CHAR(sales_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'SAT' THEN sales_date - 1
            WHEN TO_CHAR(sales_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'SUN' THEN sales_date - 2
            ELSE sales_date
        END AS base_date
    FROM date_range
)
-- 关联源表，获取基准日期对应的所有Product ID
SELECT 
    dwb.sales_date,
    st.product_id
FROM date_with_base dwb
JOIN source_table st ON dwb.base_date = st.sales_date
ORDER BY dwb.sales_date, st.product_id;

代码说明 #

1. date_range：生成从起始到结束的连续日期，CONNECT BY LEVEL的条件通过结束日期减起始日期加1计算总天数；
2. date_with_base：通过TO_CHAR判断日期是周六还是周日，分别映射到前一天或前两天的周五，工作日直接使用自身日期作为基准；
3. 最终关联：将每个日期的基准日期与源表关联，自动获取基准日期的所有Product ID，实现周末日期完全复制周五的Product ID列表。

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