求教一句关于连续天数的问题

Question

CREATE TABLE prm_tbl(zoneno NUMBER, last_days NUMBER, min_days NUMBER, min_trans NUMBER);
INSERT INTO prm_tbl VALUES(1, 10, 4,6);
INSERT INTO prm_tbl VALUES(2, 5,3,6);
CREATE TABLE acc_tbl(d_date DATE, merch NUMBER, cnt NUMBER);
INSERT INTO acc_tbl VALUES(trunc(SYSDATE) -1, 1, 6);
INSERT INTO acc_tbl VALUES(trunc(SYSDATE) -2, 1, 3);
CREATE TABLE merch_list(merch NUMBER, zoneno NUMBER);
INSERT INTO merch_list VALUES(1, 1);
INSERT INTO merch_list VALUES(2,2);
COMMIT;WITH zone_merch_dates(zoneno,merch, date_id, min_days, min_trans ) AS (
  SELECT pt.zoneno, ml.merch, date_id, pt.min_days, pt.min_trans
  from prm_tbl pt, merch_list ml,
  (SELECT zones.zoneno, dates.date_id, row_number() over(partition by zones.zoneno order by dates.date_id) rn FROM
  (SELECT DISTINCT zoneno FROM prm_tbl) zones,
  (SELECT trunc(SYSDATE) - LEVEL date_id FROM dual CONNECT BY LEVEL <= (SELECT MAX(last_days) FROM prm_tbl)) dates
  ) zone_date_v
  WHERE zone_date_v.zoneno=pt.zoneno
  AND zone_date_v.rn <= pt.last_days
  AND pt.zoneno=ml.zoneno
),
all_data as(
SELECT zmd.zoneno, zmd.merch, zmd.date_id,zmd.min_days, zmd.min_trans, nvl(A.cnt,0) tran_cnt 
FROM zone_merch_dates zmd LEFT OUTER JOIN acc_tbl A 
ON zmd.date_id=A.d_date 
AND zmd.merch=A.merch
)
select distinct zoneno, merch from (
SELECT zoneno, merch, date_id, min_days,
sum((case when tran_cnt <= min_trans then 1 else 0 end)) over(partition by zoneno, merch order by date_id rows 4 preceding ) last_4 
FROM all_data
ORDER BY zoneno, merch
)
where last_4 >= min_days;
请给分，花了我一个多小时。
缺失的时间，用笛卡尔积造数据。再通过左连接得到一个完整的zoneno, merch, 交易笔数的虚拟表
连续4天的问题，用sum() over(rows 4 preceding)这样的分析函数解决。

Answer 1

不好意思，2楼的代码有bug。
请看新代码update prm_tbl set last_days=3, min_days=3 where zoneno=1;
insert into acc_tbl values(trunc(sysdate) -3, 1, 7);
COMMIT;
with zone_merch_dates(zoneno, merch, date_id, last_days, min_days, min_trans ) as (
  SELECT pt.zoneno, ml.merch, date_id, pt.last_days,pt.min_days, pt.min_trans
  from prm_tbl pt, merch_list ml,
  (SELECT zones.zoneno, dates.date_id, row_number() over(partition by zones.zoneno order by dates.date_id desc) rn FROM
  (SELECT DISTINCT zoneno FROM prm_tbl) zones,
  (SELECT trunc(SYSDATE) - LEVEL date_id FROM dual CONNECT BY LEVEL <= (SELECT MAX(last_days) FROM prm_tbl)) dates
  ) zone_date_v
  WHERE zone_date_v.zoneno=pt.zoneno
  AND zone_date_v.rn <= pt.last_days
  AND pt.zoneno=ml.zoneno
),
all_data as(
select zmd.zoneno, zmd.merch, zmd.date_id,zmd.last_days,zmd.min_days, zmd.min_trans, nvl(a.cnt,0) tran_cnt,
sum((case when nvl(a.cnt,0) <= zmd.min_trans then 1 else 0 end)) over(partition by zmd.zoneno, zmd.merch) cumulative_tran_flag
FROM zone_merch_dates zmd LEFT OUTER JOIN acc_tbl A 
ON zmd.date_id=A.d_date 
AND zmd.merch=A.merch
)
select  distinct zoneno, merch from all_data 
where cumulative_tran_flag >= min_days;

Answer 2

3楼的代码没办法做到检测连续。请看新代码：with all_data as(  
select ml.zoneno, ml.merch, date_id, nvl(tran_cnt,0) tran_cnt, row_number() over(partition by ml.zoneno, ml.merch order by date_id desc) rn from  
(select acc.merch, dates.date_id, nvl(acc.cnt,0) tran_cnt  from acc_tbl acc partition by (merch) right outer join (select trunc(sysdate) - level date_id from dual connect by level <= (select max(last_days) from prm_tbl)) dates  
on acc.d_date=dates.date_id) merch_dates partition by (merch, date_id) right outer join merch_list ml on merch_dates.merch=ml.merch  
),  
filtered_data as(  
select ad.*, pt.min_days,pt.min_trans, 
(case when ad.tran_cnt <= pt.min_trans then 1 else 0 end) ind,
sum((case when ad.tran_cnt <= pt.min_trans then 1 else 0 end)) over(partition by ad.zoneno, ad.merch) sum_ind
from all_data ad, prm_tbl pt  
where ad.zoneno=pt.zoneno  
and ad.rn <= pt.last_days  
),
--select * from filtered_data;
sparse_data as(
select zoneno, merch,rn, nvl(lag(rn, 1) over(partition by zoneno, merch order by date_id desc),0) pre_rn
from filtered_data
where ind=1
)
select distinct zoneno, merch from sparse_data
where rn - pre_rn =1;