数据库oracle

有一张表，有店名，数据日期，日销售额，商店编号
查询连续**连续**三天或者三天以上销售额超过50000的店名
比如A店1号的销售额5万2号3万3号6万4号7万，这是不符合记录的

create table sale_info
(
dt date, ---销售日期
sale_num float, ---销售量
shop_code varchar(200) ---商店编号
)

select shop_code, rdt,count(1)
from (select b.shop_code, b.dt, b.sale_num, b.flg, b.flg - d.rn as rdt
from (select b.shop_code,
b.dt,
a.sale_num,
case
when a.sale_num > 50000 then
b.dt
else
null
end flg
from (select shop_code, dt, sum(sale_num) sale_num
from sale_info
group by shop_code, dt) a, ---先按照日期和店分组下
(select trunc(sysdate) - level + 1 as dt,
'001' shop_code
from dual
connect by level <= 23) b --连续日期，如果需要更长，可以自己设置
where b.dt = a.dt(+) --左外连接
and b.shop_code = a.shop_code(+)
order by b.shop_code, b.dt) b,
(select c.*,
row_number() over(partition by c.shop_code order by c.shop_code, c.dt) rn
from (select shop_code, dt, sum(sale_num) sale_num
from sale_info
group by shop_code, dt) c ---先按照日期和店分组下，再按照日期进行排序
where c.sale_num > 50000) d
where b.dt = d.dt(+) --左外连接
and b.shop_code = d.shop_code(+))
group by shop_code, rdt
having count(1)>=3 ---存在大于等于3的就是了

用分析函数lag或lead
--测试表，简便起见，省略了店名
create table t_sales
(storeid number(10)
,dt date
,amount number(10,2)
);

--数据，数据中出现连续3天超过50000的应该是1号店3月3号（sysdate-2），2号店3月5号（sysdate）2次
insert into t_sales values(1,sysdate,50001);
insert into t_sales values(1,sysdate-1,49999);
insert into t_sales values(1,sysdate-2,60000);
insert into t_sales values(1,sysdate-3,70000);
insert into t_sales values(1,sysdate-4,80000);

insert into t_sales values(2,sysdate,50000);
insert into t_sales values(2,sysdate-1,60000);
insert into t_sales values(2,sysdate-2,50000);
insert into t_sales values(2,sysdate-3,30000);
insert into t_sales values(2,sysdate-3,70000);

--取数
select * from (
select storeid,dt,amount amt_day0
,lag(amount,1) over(partition by storeid order by dt) amt_day1
,lag(amount,2) over(partition by storeid order by dt) amt_day2
from t_sales
) where amt_day0>=50000 and amt_day1>=50000 and amt_day2>=50000
order by dt,storeid;

--结果

原理就是把今天，昨天，前天的销售额取出来，每个都大于50000就表示连续3天大于50000。这类取数在做绩效考核的时候经常会用到，欢迎关注我的博客，嘿嘿