基于Oracle中的历史数据预测未来卷的SQL查询

我编写了一个查询，将计数拆分为多行。这只是一个样本数据，所以我已经分为2行，如在输出中提到的。现在的要求是获得第一行前7天的计数，第二行前14天的计数，第三行前21天的计数，等等，基于拆分计数的数量。未来日期为crtd日期后180天。如果你需要更多的细节，请告诉我。先谢谢你了

样本数据

CREATE TABLE T_DAT  (   "CRT_DT" DATE );

Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('25-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('18-FEB-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR'));
Insert into T_DAT (CRT_DT) values (to_date('04-MAR-23','DD-MON-RR')); commit;

要修改的查询以获得下面的预期输出

with data_set as 
     ( select d.crt_dt as crt_dt,
    trunc(d.crt_dt) + 180 as future_dt,
    count(*) approx_cnt
    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),
     vol as (select level  from dual connect by level <= (select approx_cnt/6 from data_set))
     select crt_dt, future_dt , approx_cnt 
      from data_set d ,vol;

**OUTPUT** 

CRT_DT       FUTURE_DT  APPROX_CNT
04-MAR-23   31-AUG-23   15
04-MAR-23   31-AUG-23   15  

 EXPECTED OUTPUT 

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

希望这能帮上忙。我已经添加了注解来解释我正在做的事情。

WITH 
--first determine from which date to begin and till which date to end.
min_max_fetch AS (
    SELECT
        MIN(td.crt_dt) AS min_crt,
        MAX(td.crt_dt) AS max_crt
    FROM
        t_dat td
    WHERE
            td.crt_dt <= sysdate
        AND td.crt_dt >= ( trunc(sysdate) - 180 )
)
--now fetch the date range for every row based on above
, weekly_fetch AS (
    SELECT
        mf.max_crt - ( level * 7 )         AS from_date,
        mf.max_crt - ( ( level - 1 ) * 7 ) AS to_date
    FROM
        min_max_fetch mf
    CONNECT BY
        mf.max_crt - ( level * 7 ) >= mf.min_crt
)
--now fetch count of data that lies in the above date range
SELECT
    wf.from_date,
    wf.from_date + 180 AS future_dt,
    (
        SELECT
            COUNT(*)
        FROM
            t_dat td
        WHERE
            td.crt_dt BETWEEN wf.from_date AND wf.to_date - 1
    )                  AS approx_cnt
FROM
    weekly_fetch wf
ORDER BY
    wf.from_date DESC;

基于Oracle中的历史数据预测未来卷的SQL查询

1条答案

相关问题

热门标签

最新问答