SQL销售额连续3天超过100的商品

表: order_detail
字段：order_id sku_id create_date price sku_num

需求：商品销售额连续三天超过100的商品

-- 方法一
select distinct sku_id from (
    select sku_id,create_date,flag
    ,lead(flag,1,9999) over(partition by sku_id order by create_date) aflag
    ,lead(flag,2,9999) over(partition by sku_id order by create_date) atflag

     from (
        select sku_id,create_date,tonum,if(tonum>=100,1,0) flag from ( 
            select sku_id,create_date,sum(price*sku_num) tonum from  order_detailorder_detail
            group by sku_id,create_date
        ) a where flag = 1
    ) b 
) c where flag = aflag and aflag = atflag 
;


-- 方法二 
select distinct sku_id from (
    select sku_id,afdate from (
        select sku_id,create_date,rn,date_sub(create_date,rn) afdate from(
        
            select sku_id,create_date,tonum, row_number() over(partition by sku_id order by create_date) rn from ( 
                select sku_id,create_date,sum(price*sku_num) tonum from  order_detailorder_detail
                group by sku_id,create_date having sum(price*sku_num) >= 100
            ) a 
        )  b
    ) c group by sku_id,afdate having count(*) >= 3
) d