Oracle SQL修改需求：按规则基于历史数据生成未来量预测结果

根据需求，我们需要基于原日期的拆分行数，逐行将日期按每周（7天）往前偏移，并统计对应偏移日期的实际数据量，同时计算偏移日期加180天的未来日期。以下是修改后的SQL：

WITH data_set AS (
    -- 获取原日期分组、原计数及需要拆分的行数
    SELECT 
        d.crt_dt AS original_crt_dt,
        COUNT(*) AS original_cnt,
        FLOOR(COUNT(*) / 6) AS split_rows
    FROM t_dat d
    WHERE d.crt_dt <= SYSDATE 
      AND d.crt_dt >= TRUNC(SYSDATE) - 180
    GROUP BY d.crt_dt
    HAVING TRUNC(d.crt_dt) + 180 >= TRUNC(SYSDATE) - 9 
       AND COUNT(*) > 10
),
split_levels AS (
    -- 生成每个原日期对应的拆分层级（1、2...N）
    SELECT 
        original_crt_dt,
        LEVEL AS lvl
    FROM data_set
    CONNECT BY LEVEL <= split_rows
       AND PRIOR original_crt_dt = original_crt_dt
       AND PRIOR SYS_GUID() IS NOT NULL  -- 避免层级生成时的笛卡尔积问题
),
adjusted_dates AS (
    -- 计算每个层级对应的偏移日期和未来日期
    SELECT 
        original_crt_dt - 7 * lvl AS crt_dt,
        (original_crt_dt - 7 * lvl) + 180 AS future_dt
    FROM split_levels
)
-- 统计偏移日期的实际数据量并输出结果
SELECT 
    ad.crt_dt,
    ad.future_dt,
    COUNT(d.crt_dt) AS approx_cnt
FROM adjusted_dates ad
LEFT JOIN t_dat d ON TRUNC(d.crt_dt) = TRUNC(ad.crt_dt)
GROUP BY ad.crt_dt, ad.future_dt
ORDER BY ad.crt_dt DESC;

关键修改说明： #

1. 拆分层级生成：使用CONNECT BY生成对应拆分行数的层级，通过PRIOR SYS_GUID()避免同一原日期生成层级时的笛卡尔积问题。
2. 日期偏移计算：根据层级数lvl，将原日期往前偏移7*lvl天，得到目标统计日期。
3. 实际计数统计：关联原表T_DAT，统计偏移后日期的实际数据量，替代原SQL中直接复用的原计数。
4. 未来日期计算：基于偏移后的日期加180天，而非原日期。

执行上述SQL后，将得到预期输出：

CRT_DT      FUTURE_DT  APPROX_CNT
25-FEB-23   24-AUG-23   7
18-FEB-23   17-AUG-23   10

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