SQL优化：如何避免扫描历史明细高效获取长周期用户集

select user_id
  ,product_code
   -- 总订单数
  ,count(order_no) as order_cnt   -- 历史总订单数
  ，count(case when dt>='${1_year_ago_day}' then order_no end) as order_cnt_1year
    -- 近一年订单天数
    ,count(distinct case when dt>='${1_year_ago_day}' then dt end) as order_days_1year    -- 订单天数
from order_info   -- 订单明细表
where dt >= '2017-05-01' and dt <= '${1_day_ago}'  -- 假设公司业务从17年开始的,dt为分区字段
group by user_id,product_code  -- 产品编码

create table lyy_gz.dws_lyy_shangchuang_product_tag_df(
    user_id string comment '用户id'
    ,product_code TINYINT comment '产品码值：码值映射xxxxx'
    ,min_date string comment '最早订单日期'
    ,max_date string comment '最近订单日期'
    ,cnt bigint comment '总订单或者关注次数'
    ,near_1year_cnt bigint comment '近一年订单或者关注次数：总订单cnt不能反映最近情况，故添加该字段'
    ,near_1year_active_days smallint comment '近一年活跃天数'
) comment '用户商创产品订单|关注标识；一分购20年1月开始统计，其他最早数据从2023年6月开始计'
partitioned by (day string comment '分区日期yyy-mm-dd')

insert OVERWRITE  table lyy_gz.dws_lyy_shangchuang_product_tag_df partition(day='${1_day_ago}')
select user_id   -- 用户id
    ,product_code   -- 产品id
    ,min(min_date) as min_date   -- 最早订单日期
    ,max(max_date) as max_date   -- 最近订单日期
    ,sum(cnt) as cnt     -- 订单数量
    ,sum(near_1year_cnt) as near_1year_cnt   -- 近1年累计订单数
    ,sum(near_1year_active_days) as near_1year_active_days  -- 近1年订单天数
from 
(   -- 截止昨日累计 + 当日增量 = 截止当日累计；截止昨日近1年 + 当日增量 - 一年前那天 = 截止当日近1年数据
    -- 截止前日累计数据
    select user_id 
        ,product_code
        ,min_date 
        ,max_date 
        ,cnt 
        ,near_1year_cnt
        ,near_1year_active_days
    from lyy_gz.dws_lyy_shangchuang_product_tag_df
    where day='${2_day_ago}'  -- 昨天的全量
    union all 
    select lyy_user_id as user_id 
            -- 将不同的产品编码统一转化为顺序码值
            ,case channel when 101 then 1
            when 100 then 2
            when 102 then 3
            when 2 then 9
            end as product_code   --  产品编码
        -- created是datetime创建时间，时间上个跟表ds分区保持一致
        ,substr(min(created),1,10) as min_date 
        ,substr(max(created),1,10) as max_date  
        ,count(case when ds='${1dayago}' then out_trade_no end) as cnt -- 订单数量- 当日增量
        -- 加上今天增量-一年前那天的数据=截止今天近1年的数据
        ,count(case when ds='${1dayago}' then out_trade_no end)-count(case when ds='${1yearago}' then out_trade_no end) as near_1year_cnt   
        ,,max(case when day='${1dayago}' then 1 else 0 end) - max(case when day='${1yearago}' then 1 else 0 end) as near_1year_active_days
    from lyy_gz.dwd_third_redeem_order t1 
    where ds in ('${1dayago}','${1yearago}')
        and channel in (   -- 产品编码
            2    -- 移动积分
            ,100   -- 美团一分购
            ,101  -- 京东一分购
            ,102  -- 抖音一分购
        )
        and status=2  -- 产品状态码
) uu  
group by user_id,product_code