[GitHub] [incubator-doris] zbtzbtzbt commented on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.

[GitBox]

zbtzbtzbt commented on issue #7008:
URL: 
https://github.com/apache/incubator-doris/issues/7008#issuecomment-968686655


   这里是用来计算每日新客数目
   ```
   select
       dt
       ,bitmap_union_count(if(is_day_new_user=1,buy_user_id,null)) as 
newuser_num -- 新客数
       ,sum(sale_amt) as sale_amt -- 销售额
       ,sum(sale_amt)/bitmap_union_count(buy_user_id) as ARPU -- APRU
       ,sum(sale_amt)/sum(sale_num) as pcs_avg_price -- 件均价
       ,sum(sale_num)/bitmap_union_count(buy_user_id) as user_avg_sale_num -- 
人均销售件数
       ,bitmap_union_count(buy_user_id) as buy_user_cnt -- 购买用户数
       ,sum(sale_num) as sale_num -- 销售件数
       ,bitmap_union_count(sale_main_order_id) as main_order_num -- 订单量
   from grocery_doris_pdt.topic_pdt_pro_coupon_ord_sku_dt_period_v1
   where dt=20211109
       -- 区域筛选
           and net_region_id = 350
       -- 业务模式
   group by dt
   ```
   
   下面这个sql很丑，看样子是根据用户类型分别计算什么指标
   ```
   SELECT IF(GROUPING_ID(c_chain)=1,2,c_chain) AS plat_type,-- 0:MT，1:DP,2:ALL
      bitmap_union_count(IF(user_type=0 AND is_jm_user=1,jm_user_pk,NULL)) AS 
jm_trade_num,
      bitmap_union_count(IF(user_type=1,jm_user_pk,NULL)) AS jm_uv
    FROM dm_aggr_brandka_jmc_user_d
   WHERE partition_date BETWEEN '2021-01-01' and '2021-11-01'
    AND pdc_brand_id = 184707
   GROUP BY GROUPING SETS ((c_chain),());
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org