You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2021/11/04 09:59:41 UTC
[GitHub] [incubator-doris] zbtzbtzbt opened a new issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
zbtzbtzbt opened a new issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues.
### Version
master
### What's Wrong?
简单的说就是在bitmap_union_count中加了if判断后，rollup就命中不了了，导致查询很慢；不加可以命中。
### What You Expected?
在bitmap_union_count里使用IF也可以命中rollup（详情见下面的 step 3.1）
### How to Reproduce?
复现流程如下：
- step1：建表、建rollup
```
CREATE TABLE table_1
(
k1 int NULL,
k2 int NULL,
k3 int NULL,
v1 bitmap BITMAP_UNION NULL
)
AGGREGATE KEY(k1,k2,k3)
DISTRIBUTED BY HASH(k1) BUCKETS 10
rollup (rollup_1(k1,k2,v1))
PROPERTIES("replication_num" = "1");
```
- step2：插入数据
```
insert into table_1 select 1 as k1, 2 as k2, 3 as k3, to_bitmap(10) as v1;
insert into table_1 select 2 as k1, 2 as k2, 3 as k3, to_bitmap(10) as v1;
```
- step3.1：bitmap_union_count里使用IF，则无法命中rollup
```
EXPLAIN
SELECT k1,
bitmap_union_count(IF(k2=0,v1,NULL)) AS v1_num
FROM table_1
GROUP BY k1;
-- result:
PREAGGREGATION: OFF.
rollup: table_1
```
- step3.2：bitmap_union_count里不使用IF，则可以命中rollup
```
EXPLAIN
SELECT k1,
bitmap_union_count(v1) AS v1_num
FROM table_1
GROUP BY k1;
-- result:
PREAGGREGATION: ON
rollup: rollup_1
```
### Anything Else?
无
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] zbtzbtzbt commented on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
zbtzbtzbt commented on issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008#issuecomment-964914799
如果只支持让这种特殊的用法（bitmap_union_count（if xx） ）能命中rollup，应该改哪块代码呢？
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] zbtzbtzbt edited a comment on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
zbtzbtzbt edited a comment on issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008#issuecomment-968689508
还有另一个问题也反馈一下，sum(case when)这种写法，也属于聚合函数里面用表达式
当存在隐式转换时，也可能不会命中rollup，比如从int转化为bigint就命中不了了
猜测转换的原因是怕数据溢出吧
```
explain
select dt,
sum(arrived_waybill_cnt) as arrived_waybill_cnt
, sum(case when delivery_type_second_level_code=2 then arrived_waybill_cnt end) as arrived_waybill_cnt_jiameng
from bi_peisong.app_rider_model_ctl_sa_view_day
where dt > 20211022 and dt<= 20211109
and delivery_type_second_level_code in (2,4)
group by dt
```
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008#issuecomment-964852704
Now the selector cannot parse the detail column and aggregate column required by the query from expr
So it cannot know the k2 is required detail column and the v1 is required bitmap agg column.
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008#issuecomment-968694069
> 还有另一个问题也反馈一下，sum(case when)这种写法，也属于聚合函数里面用表达式 当存在隐式转换时，也可能不会命中rollup，比如从int转化为bigint就命中不了了 猜测转换的原因是怕数据溢出吧
>
> ```
> explain
> select dt,
> sum(arrived_waybill_cnt) as arrived_waybill_cnt
> , sum(case when delivery_type_second_level_code=2 then arrived_waybill_cnt end) as arrived_waybill_cnt_jiameng
> from bi_peisong.app_rider_model_ctl_sa_view_day
> where dt > 20211022 and dt<= 20211109
> and delivery_type_second_level_code in (2,4)
> group by dt
> ```
是这样的，目前物化视图选择器不支持 sum(expr) 其中 expr 不是一个列的情况。
最主要的原因是，无法判断 expr 中的列物化视图是否满足要求。
比如 bitmap_union_count(if(is_day_new_user=1,buy_user_id,null))
其中需要检测，is_day_new_user， buy_user_id 是否能正确的从物化视图中读取出。
由于 expr 千变万化，需要有表达式等价这类框架支持才能判断。顾目前都不支持聚合函数中出现表达式的物化视图选择。
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] zbtzbtzbt commented on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] zbtzbtzbt removed a comment on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
zbtzbtzbt removed a comment on issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008#issuecomment-964914799
如果只支持让这种特殊的用法（bitmap_union_count（if xx） ）能命中rollup，应该改哪块代码呢？
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008#issuecomment-968676882
我有一个偏业务的小问题，什么类型的业务查询会有这种
bitmap_union_count(IF(k2=0,v1,NULL)) AS v1_num 需求呢？
能举个具体的业务例子嘛？
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] zbtzbtzbt commented on issue #7008: [Enhance] use predicate if in bitmap_union_count() can not hit rollup.
Posted by GitBox <gi...@apache.org>.
zbtzbtzbt commented on issue #7008:
URL: https://github.com/apache/incubator-doris/issues/7008#issuecomment-968689508
还有另一个问题也反馈一下，sum(case when)这种写法
当存在隐式转换时，也可能不会命中rollup，比如从int转化为bigint就命中不了了
猜测转换的原因是怕数据溢出吧
```
explain
select dt,
sum(arrived_waybill_cnt) as arrived_waybill_cnt
, sum(case when delivery_type_second_level_code=2 then arrived_waybill_cnt end) as arrived_waybill_cnt_jiameng
from bi_peisong.app_rider_model_ctl_sa_view_day
where dt > 20211022 and dt<= 20211109
and delivery_type_second_level_code in (2,4)
group by dt
```
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org