starrocks count distinct 明细模型查询假死状态

jackw · 2022年07月6日 11:07

【详述】 count( DISTINCT BRAND_CODE, member_id)
但select count( DISTINCT BRAND_CODE, member_id) ads.ads_user_tag 执行可以
【背景】
set global parallel_fragment_exec_instance_num=64

OK
时间: 0.052s

– 假死ＳＱＬ
select count( DISTINCT member_id) from ads.ads_user_tag_20220616
WHERE sex IN ( 0, 1 ,2)
AND brand_code = ‘TS’
AND cdp_is_del = ‘N’

select count( DISTINCT BRAND_CODE, member_id) from ads.ads_user_tag_20220616
WHERE sex IN ( 0, 1 ,2)
AND brand_code = ‘TS’
AND cdp_is_del = ‘N’

OK
时间: 0.303s
－－表模型
CREATE TABLE ads_user_tag_20220616 (
BRAND_CODE varchar(65533) NULL COMMENT “”,
MEMBER_ID varchar(65533) NULL COMMENT “”,
LOGONNAME varchar(65533) NULL COMMENT “”,
MOBILE varchar(65533) NULL COMMENT “”,
IS_BIND_MOBILE tinyint(4) NULL COMMENT “”,
NAME varchar(65533) NULL COMMENT “”,
IS_NAME tinyint(4) NULL COMMENT “”,
SEX tinyint(4) NULL COMMENT “”,
BIRTHDAY datetime NULL COMMENT “”,
BIRTHDAY_MONTH varchar(65533) NULL COMMENT “”,
IS_BIRTHDAY_MONTH tinyint(4) NULL COMMENT “”,
IS_BIRTHDAY_NEXT tinyint(4) NULL COMMENT “”,
IS_BIRTHDAY_DAY tinyint(4) NULL COMMENT “”,
AGE int(11) NULL COMMENT “”,
AGE_RANGE varchar(65533) NULL COMMENT “”,
CONSTELLATION varchar(65533) NULL COMMENT “”,
ID_CARD_NO varchar(65533) NULL COMMENT “”,
HAS_ID_CARD tinyint(4) NULL COMMENT “”,
FOLLOW_STATUS varchar(65533) NULL COMMENT “”,
FIRST_FOLLOW_TIME bigint(20) NULL COMMENT “”,
LATEST_CANCEL_TIME bigint(20) NULL COMMENT “”,
FOLLOW_STORE varchar(65533) NULL COMMENT “”,
FOLLOWER_CITY varchar(65533) NULL COMMENT “”,
FOLLOWER_CITY_LEVEL varchar(65533) NULL COMMENT “”,
FOLLOW_SOURCE int(11) NULL COMMENT “”,
FOLLOW_CHANNEL_WX int(11) NULL COMMENT “”,
FOLLOW_CHANNEL_APP int(11) NULL COMMENT “”,
REGISTER_STORE varchar(65533) NULL COMMENT “”,
REGISTER_STORE_NO varchar(65533) NULL COMMENT “”,
ORG_CODE_LONG varchar(65533) NULL COMMENT “”,
REGISTER_TIME datetime NULL COMMENT “”,
REGISTER_DATE datetime NULL COMMENT “”,
REGISTER_CITY varchar(65533) NULL COMMENT “”,
REGISTER_CITY_LEVEL varchar(65533) NULL COMMENT “”,
REGISTER_SOURCE varchar(65533) NULL COMMENT “”,
REGISTER_DAYS int(11) NULL COMMENT “”,
PROVINCE varchar(65533) NULL COMMENT “”,
CITY varchar(65533) NULL COMMENT “”,
DISTRICT varchar(65533) NULL COMMENT “”,
LEVEL tinyint(4) NULL COMMENT “”,
IS_PAID_NOW tinyint(4) NULL COMMENT “”,
CLASS varchar(65533) NULL COMMENT “”,
TYPE int(11) NULL COMMENT “”,
sal_last_time datetime NULL COMMENT “”,
follow_qty int(11) NULL COMMENT “”,
follow_cancel_qty int(11) NULL COMMENT “”,
evaluation_qty int(11) NULL COMMENT “”,
is_evaluation tinyint(4) NULL COMMENT “”,
sal_qty decimal128(38, 9) NULL COMMENT “”,
sal_amt decimal128(38, 9) NULL COMMENT “”,
sal_return_qty bigint(20) NULL COMMENT “”,
sal_return_amt decimal128(38, 9) NULL COMMENT “”,
sal_avg_amt decimal128(38, 9) NULL COMMENT “”,
coupon_be_sent_qty int(11) NULL COMMENT “”,
coupon_to_get_qty int(11) NULL COMMENT “”,
coupon_expire_qty int(11) NULL COMMENT “”,
coupon_available_qty int(11) NULL COMMENT “”,
coupon_be_used_qty int(11) NULL COMMENT “”,
coupon_not_enabled_qty int(11) NULL COMMENT “”,
last_visit_time datetime NULL COMMENT “”,
point_acc_amt decimal128(38, 9) NULL COMMENT “”,
point_valid_amt decimal128(38, 9) NULL COMMENT “”,
point_no_effect_amt bigint(20) NULL COMMENT “”,
point_soon_expire_amt decimal128(38, 9) NULL COMMENT “”,
point_expired_amt bigint(20) NULL COMMENT “”,
point_used_amount_amt decimal128(38, 9) NULL COMMENT “”,
point_cash_qty bigint(20) NULL COMMENT “”,
point_cash_amt decimal128(38, 9) NULL COMMENT “”,
point_mall_qty bigint(20) NULL COMMENT “”,
point_mall_amt decimal128(38, 9) NULL COMMENT “”,
point_activity_qty bigint(20) NULL COMMENT “”,
point_activity_amt decimal128(38, 9) NULL COMMENT “”,
activity_signup_qty int(11) NULL COMMENT “”,
activity_lottery_qty int(11) NULL COMMENT “”,
activity_break_qty int(11) NULL COMMENT “”,
lottery_qty int(11) NULL COMMENT “”,
lottery_gift_qty int(11) NULL COMMENT “”,
presale_writeoff_qty int(11) NULL COMMENT “”,
presale_take_qty int(11) NULL COMMENT “”,
presale_break_qty int(11) NULL COMMENT “”,
is_e_sports_enthusiasts tinyint(4) NULL COMMENT “”,
is_runner tinyint(4) NULL COMMENT “”,
is_basketball_lovers tinyint(4) NULL COMMENT “”,
is_sneaker tinyint(4) NULL COMMENT “”,
has_receive_weixin_card int(11) NULL COMMENT “”,
consume_period int(11) NULL COMMENT “”,
consume_period2 int(11) NULL COMMENT “”,
mem_has_baby int(11) NULL COMMENT “”,
member_rfm varchar(65533) NULL COMMENT “”,
card_type varchar(65533) NULL COMMENT “”,
activty_type_none varchar(65533) NULL COMMENT “”,
activty_type_sign_up varchar(65533) NULL COMMENT “”,
activty_type_purchase_limit varchar(65533) NULL COMMENT “”,
activty_type_lottery varchar(65533) NULL COMMENT “”,
arpa varchar(65533) NULL COMMENT “”,
coupon_sensitive varchar(65533) NULL COMMENT “”,
coupon_sensitivity_one_price varchar(65533) NULL COMMENT “”,
coupon_sensitivity_across varchar(65533) NULL COMMENT “”,
coupon_sensitivity_buy_gift varchar(65533) NULL COMMENT “”,
coupon_sensitivity_adjust_price varchar(65533) NULL COMMENT “”,
coupon_sensitivity_presentpoint varchar(65533) NULL COMMENT “”,
coupon_sensitivity_reduction varchar(65533) NULL COMMENT “”,
coupon_sensitivity_discount varchar(65533) NULL COMMENT “”,
coupon_sensitivity_integral varchar(65533) NULL COMMENT “”,
member_acct_super_high varchar(65533) NULL COMMENT “”,
member_acct_high varchar(65533) NULL COMMENT “”,
member_acct_low varchar(65533) NULL COMMENT “”,
member_acct_medium varchar(65533) NULL COMMENT “”,
member_acct_negative varchar(65533) NULL COMMENT “”,
buy_product_enjoy_1 varchar(65533) NULL COMMENT “”,
buy_product_enjoy_4 varchar(65533) NULL COMMENT “”,
buy_product_enjoy_3 varchar(65533) NULL COMMENT “”,
buy_time_hour_0_2 varchar(65533) NULL COMMENT “”,
buy_time_hour_20_22 varchar(65533) NULL COMMENT “”,
buy_time_hour_2_4 varchar(65533) NULL COMMENT “”,
buy_time_hour_18_20 varchar(65533) NULL COMMENT “”,
buy_time_hour_16_18 varchar(65533) NULL COMMENT “”,
buy_time_hour_8_10 varchar(65533) NULL COMMENT “”,
buy_time_hour_12_14 varchar(65533) NULL COMMENT “”,
buy_time_hour_22_24 varchar(65533) NULL COMMENT “”,
buy_time_hour_14_16 varchar(65533) NULL COMMENT “”,
buy_time_hour_10_12 varchar(65533) NULL COMMENT “”,
buy_week_7 varchar(65533) NULL COMMENT “”,
buy_week_6 varchar(65533) NULL COMMENT “”,
buy_week_1 varchar(65533) NULL COMMENT “”,
buy_week_2 varchar(65533) NULL COMMENT “”,
buy_week_3 varchar(65533) NULL COMMENT “”,
buy_week_5 varchar(65533) NULL COMMENT “”,
buy_week_4 varchar(65533) NULL COMMENT “”,
consumer_cross_city varchar(65533) NULL COMMENT “”,
continue_month varchar(65533) NULL COMMENT “”,
member_lifecycle varchar(65533) NULL COMMENT “”,
loyal_channel varchar(65533) NULL COMMENT “”,
loyal_brand_code varchar(65533) NULL COMMENT “”,
city_often_level_1 varchar(65533) NULL COMMENT “”,
city_often_level_new1 varchar(65533) NULL COMMENT “”,
city_often_level_5 varchar(65533) NULL COMMENT “”,
city_often_level_2 varchar(65533) NULL COMMENT “”,
city_often_level_3 varchar(65533) NULL COMMENT “”,
city_often_level_4 varchar(65533) NULL COMMENT “”,
presale_activity varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_cv01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_as01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_ac01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_nk01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_pu01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_ak01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_ad01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_ao01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_rb01 varchar(65533) NULL COMMENT “”,
presale_pbrand_enjoy_other varchar(65533) NULL COMMENT “”,
pur_brand_enjoy_ao01 varchar(65533) NULL COMMENT “”,
pur_brand_enjoy_pu01 varchar(65533) NULL COMMENT “”,
pur_brand_enjoy_nk01 varchar(65533) NULL COMMENT “”,
pur_brand_enjoy_other varchar(65533) NULL COMMENT “”,
pur_brand_enjoy_as01 varchar(65533) NULL COMMENT “”,
pur_brand_enjoy_ad01 varchar(65533) NULL COMMENT “”,
pur_brand_enjoy_ac01 varchar(65533) NULL COMMENT “”,
order_channel_app varchar(65533) NULL COMMENT “”,
order_channel_jd varchar(65533) NULL COMMENT “”,
order_channel_dy varchar(65533) NULL COMMENT “”,
order_channel_offline varchar(65533) NULL COMMENT “”,
order_channel_tm varchar(65533) NULL COMMENT “”,
order_channel_sport varchar(65533) NULL COMMENT “”,
order_channel_appsc varchar(65533) NULL COMMENT “”,
discount_enjoy varchar(65533) NULL COMMENT “”,
categories_3 varchar(65533) NULL COMMENT “”,
categories_2 varchar(65533) NULL COMMENT “”,
categories_1 varchar(65533) NULL COMMENT “”,
pur_price_enjoy_super_high varchar(65533) NULL COMMENT “”,
pur_price_enjoy_medium varchar(65533) NULL COMMENT “”,
pur_price_enjoy_low varchar(65533) NULL COMMENT “”,
pur_price_enjoy_high varchar(65533) NULL COMMENT “”,
pur_psex_often_7 varchar(65533) NULL COMMENT “”,
pur_psex_often_6 varchar(65533) NULL COMMENT “”,
pur_psex_often_12 varchar(65533) NULL COMMENT “”,
pur_psex_often_3 varchar(65533) NULL COMMENT “”,
pur_psex_often_5 varchar(65533) NULL COMMENT “”,
pur_psex_often_1 varchar(65533) NULL COMMENT “”,
pur_psex_often_9 varchar(65533) NULL COMMENT “”,
pur_psex_often_2 varchar(65533) NULL COMMENT “”,
signup_method_none varchar(65533) NULL COMMENT “”,
signup_method_cash varchar(65533) NULL COMMENT “”,
signup_method_point_cash varchar(65533) NULL COMMENT “”,
signup_method_point varchar(65533) NULL COMMENT “”,
signup_method_free varchar(65533) NULL COMMENT “”,
pre_pbrand_enjoy_ao01 varchar(65533) NULL COMMENT “”,
pre_pbrand_enjoy_ac01 varchar(65533) NULL COMMENT “”,
pre_pbrand_enjoy_nk01 varchar(65533) NULL COMMENT “”,
pre_pbrand_enjoy_ad01 varchar(65533) NULL COMMENT “”,
pre_pbrand_enjoy_none varchar(65533) NULL COMMENT “”,
visit_title_enjoy_lmfw_xlfs varchar(65533) NULL COMMENT “”,
visit_title_enjoy_lmfw_jfsc varchar(65533) NULL COMMENT “”,
visit_title_enjoy_lmfw_grzx varchar(65533) NULL COMMENT “”,
member_activity_enjoy_1 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_25 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_9 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_4 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_23 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_11 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_14 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_24 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_19 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_3 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_6 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_5 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_15 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_12 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_7 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_10 varchar(65533) NULL COMMENT “”,
member_activity_enjoy_13 varchar(65533) NULL COMMENT “”,
member_lifecycle_crm varchar(65533) NULL COMMENT “”,
member_lifecycle_crm_new_register varchar(65533) NULL COMMENT “”,
member_lifecycle_crm_sleep varchar(65533) NULL COMMENT “”,
member_lifecycle_crm_lost varchar(65533) NULL COMMENT “”,
event_brand_enjoy_ao01 varchar(65533) NULL COMMENT “”,
event_brand_enjoy_ad01 varchar(65533) NULL COMMENT “”,
event_brand_enjoy_nk01 varchar(65533) NULL COMMENT “”,
event_brand_enjoy_other varchar(65533) NULL COMMENT “”,
event_cancelunpay_brand_nk01 varchar(65533) NULL COMMENT “”,
event_cancelunpay_brand_ad01 varchar(65533) NULL COMMENT “”,
event_cancelunpay_brand_last_nk01 varchar(65533) NULL COMMENT “”,
event_cancelunpay_brand_last_ad01 varchar(65533) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_3 varchar(65533) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_5 varchar(65533) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_11 varchar(65533) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_2 varchar(65533) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_7 varchar(65533) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_10 varchar(65533) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_8 varchar(65533) NULL COMMENT “”,
event_price_enjoy_super_high varchar(65533) NULL COMMENT “”,
event_price_enjoy_high varchar(65533) NULL COMMENT “”,
event_price_enjoy_medium varchar(65533) NULL COMMENT “”,
event_price_enjoy_low varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_ao01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_cv01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_tb01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_ad01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_nk01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_pu01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_as01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_ak01 varchar(65533) NULL COMMENT “”,
shopingcar_brand_last_other varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_9 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_10 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_11 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_4 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_8 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_5 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_14 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_2 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_7 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_12 varchar(65533) NULL COMMENT “”,
shoppingcar_colour_enjoy_3 varchar(65533) NULL COMMENT “”,
shoppingcar_brand_enjoy_ak01 varchar(65533) NULL COMMENT “”,
shoppingcar_brand_enjoy_other varchar(65533) NULL COMMENT “”,
shoppingcar_brand_enjoy_as01 varchar(65533) NULL COMMENT “”,
shoppingcar_brand_enjoy_nk01 varchar(65533) NULL COMMENT “”,
shoppingcar_brand_enjoy_ad01 varchar(65533) NULL COMMENT “”,
wechat_visit_hour_0_2 bigint(20) NULL COMMENT “”,
wechat_visit_hour_2_4 bigint(20) NULL COMMENT “”,
wechat_visit_hour_4_6 bigint(20) NULL COMMENT “”,
wechat_visit_hour_6_8 bigint(20) NULL COMMENT “”,
wechat_visit_hour_8_10 bigint(20) NULL COMMENT “”,
wechat_visit_hour_10_12 bigint(20) NULL COMMENT “”,
wechat_visit_hour_12_14 bigint(20) NULL COMMENT “”,
wechat_visit_hour_14_16 bigint(20) NULL COMMENT “”,
wechat_visit_hour_16_18 bigint(20) NULL COMMENT “”,
wechat_visit_hour_18_20 bigint(20) NULL COMMENT “”,
wechat_visit_hour_20_22 bigint(20) NULL COMMENT “”,
wechat_visit_hour_22_24 bigint(20) NULL COMMENT “”,
wechat_visit_week_1 bigint(20) NULL COMMENT “”,
wechat_visit_week_2 bigint(20) NULL COMMENT “”,
wechat_visit_week_3 bigint(20) NULL COMMENT “”,
wechat_visit_week_4 bigint(20) NULL COMMENT “”,
wechat_visit_week_5 bigint(20) NULL COMMENT “”,
wechat_visit_week_6 bigint(20) NULL COMMENT “”,
wechat_visit_week_7 bigint(20) NULL COMMENT “”,
cdp_create_time datetime NULL COMMENT “”,
cdp_update_time datetime NULL COMMENT “”,
etl_update_time datetime NULL COMMENT “”,
cdp_ver int(11) NULL COMMENT “”,
cdp_is_del varchar(65533) NULL COMMENT “”,
loyal_user_status varchar(65533) NULL COMMENT “”,
service_satisfaction varchar(65533) NULL COMMENT “”,
is_presale_writeoff int(11) NULL COMMENT “”,
IS_BIRTHDAY_7 varchar(65533) NULL COMMENT “”,
IS_LEVEL_EXPIRATION_7 varchar(65533) NULL COMMENT “”,
IS_COUPON_EXPIRATION_7 varchar(65533) NULL COMMENT “”,
IS_NOSAL_LAST_TIME_30 varchar(65533) NULL COMMENT “”,
IS_NOSAL_LAST_TIME_60 varchar(65533) NULL COMMENT “”,
LEVEL_EXPIRATION_DATE datetime NULL COMMENT “”,
COUPON_EXPIRATION_LAST_DATE datetime NULL COMMENT “”,
SAL_LAST_STORE varchar(65533) NULL COMMENT “”,
SAL_LAST_YEAR_QTY bigint(20) NULL COMMENT “”,
EVALUATION_AVG_SCORE decimal128(38, 9) NULL COMMENT “”,
EVALUATION_SCORE3_QTY int(11) NULL COMMENT “”,
store_no varchar(65533) NULL COMMENT “”,
city_often varchar(65533) NULL COMMENT “”,
member_lifecycle_new_register int(11) NULL COMMENT “”,
member_lifecycle_low int(11) NULL COMMENT “”,
member_lifecycle_sleep int(11) NULL COMMENT “”,
member_lifecycle_mid int(11) NULL COMMENT “”,
member_lifecycle_lost int(11) NULL COMMENT “”,
member_lifecycle_high int(11) NULL COMMENT “”,
pur_brand_enjoy_AK01 int(11) NULL COMMENT “”,
pur_brand_enjoy_CV01 int(11) NULL COMMENT “”,
pur_brand_enjoy_VN01 int(11) NULL COMMENT “”,
pur_brand_enjoy_OT01 int(11) NULL COMMENT “”,
pur_brand_enjoy_TB01 int(11) NULL COMMENT “”,
pur_brand_enjoy_SH01 int(11) NULL COMMENT “”,
pur_brand_enjoy_NF01 int(11) NULL COMMENT “”,
pur_brand_enjoy_RB01 int(11) NULL COMMENT “”,
sensitivity_high int(11) NULL COMMENT “”,
sensitivity_lower int(11) NULL COMMENT “”,
sensitivity_medium int(11) NULL COMMENT “”,
pre_pbrand_enjoy_AS01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_AK01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_PU01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_CV01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_VN01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_OT01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_TB01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_SH01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_NF01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_RB01 int(11) NULL COMMENT “”,
pre_pbrand_enjoy_other int(11) NULL COMMENT “”,
presale_activity_first_take int(11) NULL COMMENT “”,
presale_activity_general_take int(11) NULL COMMENT “”,
presale_activity_hight_take int(11) NULL COMMENT “”,
presale_pbrand_enjoy_VN01 int(11) NULL COMMENT “”,
presale_pbrand_enjoy_OT01 int(11) NULL COMMENT “”,
presale_pbrand_enjoy_TB01 int(11) NULL COMMENT “”,
presale_pbrand_enjoy_SH01 int(11) NULL COMMENT “”,
presale_pbrand_enjoy_NF01 int(11) NULL COMMENT “”,
coupon_sensitive_high int(11) NULL COMMENT “”,
coupon_sensitive_medium int(11) NULL COMMENT “”,
coupon_sensitive_low int(11) NULL COMMENT “”,
coupon_used_get int(11) NULL COMMENT “”,
coupon_used_get_used int(11) NULL COMMENT “”,
coupon_used_not int(11) NULL COMMENT “”,
arpa_pct_lower int(11) NULL COMMENT “”,
arpa_pct_med int(11) NULL COMMENT “”,
arpa_pct_high int(11) NULL COMMENT “”,
continue_month_12 int(11) NULL COMMENT “”,
continue_month_11 int(11) NULL COMMENT “”,
continue_month_10 int(11) NULL COMMENT “”,
continue_month_9 int(11) NULL COMMENT “”,
continue_month_8 int(11) NULL COMMENT “”,
continue_month_7 int(11) NULL COMMENT “”,
continue_month_6 int(11) NULL COMMENT “”,
continue_month_5 int(11) NULL COMMENT “”,
continue_month_4 int(11) NULL COMMENT “”,
continue_month_3 int(11) NULL COMMENT “”,
continue_month_2 int(11) NULL COMMENT “”,
loyal_pbrand_NK01 int(11) NULL COMMENT “”,
loyal_pbrand_AD01 int(11) NULL COMMENT “”,
loyal_pbrand_AO01 int(11) NULL COMMENT “”,
loyal_pbrand_AS01 int(11) NULL COMMENT “”,
loyal_pbrand_AK01 int(11) NULL COMMENT “”,
loyal_pbrand_PU01 int(11) NULL COMMENT “”,
loyal_pbrand_CV01 int(11) NULL COMMENT “”,
loyal_pbrand_VN01 int(11) NULL COMMENT “”,
loyal_pbrand_OT01 int(11) NULL COMMENT “”,
loyal_pbrand_TB01 int(11) NULL COMMENT “”,
loyal_pbrand_SH01 int(11) NULL COMMENT “”,
loyal_pbrand_NF01 int(11) NULL COMMENT “”,
loyal_pbrand_RB01 int(11) NULL COMMENT “”,
loyal_pbrand_AC01 int(11) NULL COMMENT “”,
loyal_pbrand_other int(11) NULL COMMENT “”,
discount_enjoy_none int(11) NULL COMMENT “”,
discount_enjoy_normal int(11) NULL COMMENT “”,
discount_enjoy_more int(11) NULL COMMENT “”,
city_often_level_18 int(11) NULL COMMENT “”,
buy_time_hour_4_6 int(11) NULL COMMENT “”,
buy_time_hour_6_8 int(11) NULL COMMENT “”,
pur_psex_often_4 int(11) NULL COMMENT “”,
pur_psex_often_8 int(11) NULL COMMENT “”,
pur_psex_often_10 int(11) NULL COMMENT “”,
pur_psex_often_11 int(11) NULL COMMENT “”,
pur_psex_often_13 int(11) NULL COMMENT “”,
pur_psex_often_14 int(11) NULL COMMENT “”,
pur_psex_often_15 int(11) NULL COMMENT “”,
loyal_channel_jd int(11) NULL COMMENT “”,
loyal_channel_app int(11) NULL COMMENT “”,
loyal_channel_sport int(11) NULL COMMENT “”,
loyal_channel_tm int(11) NULL COMMENT “”,
loyal_channel_offline int(11) NULL COMMENT “”,
consumer_cross_city_1 int(11) NULL COMMENT “”,
consumer_cross_city_2 int(11) NULL COMMENT “”,
consumer_cross_city_3 int(11) NULL COMMENT “”,
consumer_cross_city_4 int(11) NULL COMMENT “”,
consumer_cross_city_5 int(11) NULL COMMENT “”,
consumer_cross_city_6_plus int(11) NULL COMMENT “”,
buy_product_enjoy_2 int(11) NULL COMMENT “”,
card_type_normal int(11) NULL COMMENT “”,
card_type_golden int(11) NULL COMMENT “”,
card_type_black int(11) NULL COMMENT “”,
series_type_no_qd int(11) NULL COMMENT “”,
series_enjoy_qd_qd int(11) NULL COMMENT “”,
series_enjoy_qd_none int(11) NULL COMMENT “”,
series_type_no_yz int(11) NULL COMMENT “”,
series_enjoy_yz_yz int(11) NULL COMMENT “”,
series_enjoy_yz_none int(11) NULL COMMENT “”,
series_type_no_nba int(11) NULL COMMENT “”,
series_enjoy_nba_nba int(11) NULL COMMENT “”,
series_enjoy_nba_none int(11) NULL COMMENT “”,
visit_title_enjoy_lmfw_xprl int(11) NULL COMMENT “”,
visit_title_enjoy_none int(11) NULL COMMENT “”,
visit_title_enjoy_lmfw_yhq int(11) NULL COMMENT “”,
visit_title_enjoy_lmfw_hdzx int(11) NULL COMMENT “”,
visit_title_enjoy_lmfw_qyzx int(11) NULL COMMENT “”,
loyal_channel_appsc int(11) NULL COMMENT “”,
member_activity_enjoy_2 int(11) NULL COMMENT “”,
member_activity_enjoy_8 int(11) NULL COMMENT “”,
member_activity_enjoy_16 int(11) NULL COMMENT “”,
member_activity_enjoy_17 int(11) NULL COMMENT “”,
member_activity_enjoy_18 int(11) NULL COMMENT “”,
member_activity_enjoy_20 int(11) NULL COMMENT “”,
member_activity_enjoy_21 int(11) NULL COMMENT “”,
member_activity_enjoy_22 int(11) NULL COMMENT “”,
member_activity_enjoy_26 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_AO01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_PU01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_CV01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_VN01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_OT01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_TB01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_SH01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_NF01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_RB01 int(11) NULL COMMENT “”,
shoppingcar_brand_enjoy_AC01 int(11) NULL COMMENT “”,
shopingcar_brand_last_VN01 int(11) NULL COMMENT “”,
shopingcar_brand_last_OT01 int(11) NULL COMMENT “”,
shopingcar_brand_last_SH01 int(11) NULL COMMENT “”,
shopingcar_brand_last_NF01 int(11) NULL COMMENT “”,
shopingcar_brand_last_RB01 int(11) NULL COMMENT “”,
shopingcar_brand_last_AC01 int(11) NULL COMMENT “”,
shoppingcar_colour_enjoy_1 int(11) NULL COMMENT “”,
shoppingcar_colour_enjoy_6 int(11) NULL COMMENT “”,
shoppingcar_colour_enjoy_13 int(11) NULL COMMENT “”,
event_brand_enjoy_AS01 int(11) NULL COMMENT “”,
event_brand_enjoy_AK01 int(11) NULL COMMENT “”,
event_brand_enjoy_PU01 int(11) NULL COMMENT “”,
event_brand_enjoy_CV01 int(11) NULL COMMENT “”,
event_brand_enjoy_VN01 int(11) NULL COMMENT “”,
event_brand_enjoy_OT01 int(11) NULL COMMENT “”,
event_brand_enjoy_TB01 int(11) NULL COMMENT “”,
event_brand_enjoy_SH01 int(11) NULL COMMENT “”,
event_brand_enjoy_NF01 int(11) NULL COMMENT “”,
event_brand_enjoy_RB01 int(11) NULL COMMENT “”,
event_brand_enjoy_AC01 int(11) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_1 int(11) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_4 int(11) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_6 int(11) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_9 int(11) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_12 int(11) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_13 int(11) NULL COMMENT “”,
event_cancelunpay_colour_enjoy_14 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_AO01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_AS01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_AK01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_PU01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_CV01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_VN01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_OT01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_TB01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_SH01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_NF01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_RB01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_AC01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_last_other int(11) NULL COMMENT “”,
event_cancelunpay_brand_AO01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_AS01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_AK01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_PU01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_CV01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_VN01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_OT01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_TB01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_SH01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_NF01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_RB01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_AC01 int(11) NULL COMMENT “”,
event_cancelunpay_brand_other int(11) NULL COMMENT “”,
user_id int(11) NOT NULL COMMENT “用户id”,
INDEX BITMAP_IS_SEX (sex) USING BITMAP COMMENT ‘’
) ENGINE=OLAP
DUPLICATE KEY(BRAND_CODE, MEMBER_ID)
COMMENT “OLAP”
DISTRIBUTED BY HASH(BRAND_CODE, MEMBER_ID) BUCKETS 60
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

【业务影响】
导致业务迁移复杂性，极大影响业务
【StarRocks版本】例如：2.3 、2.16
【集群规模】例如：4fe（3 follower+1observer）+4be（fe与be混部）
【机器信息】64C/128G/千兆
【附件】

fe.warn.log/be.warn.log/相应截图
慢查询：
- Profile信息

PLAN FRAGMENT 0
OUTPUT EXPRS:487: count
PARTITION: UNPARTITIONED

RESULT SINK

3:AGGREGATE (update finalize)
| output: multi_distinct_count(2: MEMBER_ID)
| group by:
|
2:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 02
UNPARTITIONED

1:Project
| <slot 2> : 2: MEMBER_ID
|
0:OlapScanNode
TABLE: ads_user_tag_20220616
PREAGGREGATION: ON
PREDICATES: 8: SEX IN (0, 1, 2), DictExpr(488: BRAND_CODE,[ = ‘TS’]), DictExpr(489: cdp_is_del,[ = ‘N’])
partitions=1/1
rollup: ads_user_tag_20220616
tabletRatio=60/60
tabletList=147026,147030,147034,147038,147042,147046,147050,147054,147058,147062 …
cardinality=4197637
avgRowSize=36.000168
numNodes=0

– dump-file
dump_file (31.2 KB)

并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
parallel_fragment_exec_instance_num 64
cbo是否开启：show variables like ‘%cbo%’;

cbo_cte_reuse	false
cbo_enable_low_cardinality_optimize	true
cbo_max_reorder_node_use_dp	10
cbo_max_reorder_node_use_exhaustive	4
cbo_use_correlated_join_estimate	true

be节点cpu和内存使用率截图

image792×321 34.6 KB

image1098×501 46.8 KB

许秀不许秀 · 2022年07月6日 11:16

执行一下 explain select count( DISTINCT member_id) from ads.ads_user_tag_20220616
WHERE sex IN ( 0, 1 ,2)
AND brand_code = ‘TS’
AND cdp_is_del = ‘N’;

jackw · 2022年07月6日 11:32

只能执行这个

PLAN FRAGMENT 0
OUTPUT EXPRS:487: count
PARTITION: UNPARTITIONED

RESULT SINK

3:AGGREGATE (update finalize)
| output: multi_distinct_count(2: MEMBER_ID)
| group by:
|
2:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 02
UNPARTITIONED

1:Project
| <slot 2> : 2: MEMBER_ID
|
0:OlapScanNode
TABLE: ads_user_tag_20220616
PREAGGREGATION: ON
PREDICATES: 8: SEX IN (0, 1, 2), DictExpr(488: BRAND_CODE,[ = ‘TS’]), DictExpr(489: cdp_is_del,[ = ‘N’])
partitions=1/1
rollup: ads_user_tag_20220616
tabletRatio=60/60
tabletList=147026,147030,147034,147038,147042,147046,147050,147054,147058,147062 …
cardinality=4197637
avgRowSize=36.000168
numNodes=0

许秀不许秀 · 2022年07月6日 13:55

set new_planner_agg_stage = 4;
然后再执行以下

许秀不许秀 · 2022年07月6日 13:55

你这个慢是因为没有统计信息，只走了一阶段聚合

jackw · 2022年07月6日 14:55

对这样，可以执行完
set global parallel_fragment_exec_instance_num=64

OK
时间: 0.053s

set new_planner_agg_stage = 4

OK
时间: 0.048s

select count( DISTINCT member_id) from ads.ads_user_tag_20220616
WHERE sex IN ( 0, 1 ,2)
AND brand_code = ‘TS’
AND cdp_is_del = ‘N’

OK
时间: 2.843s

jackw · 2022年07月6日 14:56

统计信息会修复一下吗？感觉怪怪的

许秀不许秀 · 2022年07月7日 05:59

你是哪个版本。
执行 analyze full table ads.ads_user_tag_20220616; 刻意重新收集统计信息

jackw · 2022年07月7日 15:05

2.3 rc
重新收集统计信息后，执行多次也是一样

许秀不许秀 · 2022年07月8日 02:54

怎么获取query_dump文件执行以下query dump，我们分析以下

jackw · 2022年07月19日 06:05

dump_file (2) (32.7 KB)

jackw · 2022年07月19日 06:06

query dump 如上