hive 7天内连续3天

select
    store_id
    ,date_diff
    ,count(*)
from (
    SELECT
        store_id
        ,date_sub(order_bill_date,rk) date_diff
    from (
        SELECT 
            store_id
            ,order_bill_date
            ,rank() over(partition by store_id order by order_bill_date) rk
        FROM (
            SELECT store_id,substr(order_bill_date,1,10) as order_bill_date
            from xxx
            WHERE dt = ‘2021-08-08‘
            and store_id is not null
            --AND 时间范围
            group by store_id,substr(order_bill_date,1,10)
        ) as ori
    ) as t1
) as t2
group by store_id,date_diff
having count(*) >= 7



SELECT id,
       count(*)
FROM(
    SELECT *,
          date_sub(substr(日期,1,10),cum)AS 结果
   FROM (
        SELECT *,
             rank() over(PARTITION BY id
                               ORDER BY 日期) AS cum
        FROM (
            SELECT DISTINCT substr(order_bill_date,1,10) AS 日期 ,store_id AS id
            FROM xxx
            WHERE dt = ‘2021-08-08‘ 
        ) AS a
    ) AS b
) AS c
GROUP BY id,结果
HAVING count(*)>=7