GoGoWen opened a new issue, #54577:
URL: https://github.com/apache/doris/issues/54577
1, create table in hive like following
CREATE TABLE `app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt`(
`shadow_sku_id` string COMMENT '影子sku',
`purchaser_control_erp_acct` string COMMENT '采控员ERP帐号',
`item_third_cate_name` string COMMENT '商品三级分类名称',
`sku_freight_coupon_amount` string COMMENT 'SKU免运费券金额',
`is_retract` string COMMENT 'is_retract',
`brandname_full` string COMMENT '品牌全名称',
`vender_name` string COMMENT '商家名称',
`check_account_tm_10min_seg` string COMMENT 'check_account_tm_10min_seg',
`ord_complete_tm` string COMMENT '完成时间',
`sales_man_id` string COMMENT '地勤',
`third_place_cd` string COMMENT 'third_place_cd',
`self_carry_status_cd` string COMMENT '自提状态代码',
`first_place_cd` string COMMENT 'first_place_cd',
`wait_check_account_flag` string COMMENT '待付款订单标识',
`rev_addr_county_name` string COMMENT '收货地址区县名称',
`sale_sub_cha_cd` string COMMENT 'sale_sub_cha_cd',
`shop_id` string COMMENT '店铺编号',
`ord_complete_10min_seg` string COMMENT 'ord_complete_10min_seg',
`item_first_cate_cd` string COMMENT '商品一级分类代码',
`intraday_ord_deal_flag` string COMMENT '订单当天成交标志',
`worldwide_tax_amount` string COMMENT '全球购税费',
`pur_sale_control_separate_flag` string COMMENT
'pur_sale_control_separate_flag',
`oper_dept_id_1` string COMMENT 'oper_dept_id_1',
`rev_addr_town_name` string COMMENT '收货地址乡镇名称',
`dept_name_1` string COMMENT '一级部门名称',
`oper_dept_id_2` string COMMENT 'oper_dept_id_2',
`pur_control_bu_id` string COMMENT 'pur_control_bu_id',
`user_name` string COMMENT 'user_name',
`mid` string COMMENT 'mid',
`dept_name_6` string COMMENT '六级部门名称',
`dept_name_5` string COMMENT '五级部门名称',
`dept_name_4` string COMMENT '四级部门名称',
`oper_dept_id_3` string COMMENT 'oper_dept_id_3',
`intraday_ord_valid_flag` string COMMENT '订单当天有效标志',
`dept_name_3` string COMMENT '三级部门名称',
`oper_dept_id_4` string COMMENT 'oper_dept_id_4',
`dept_name_2` string COMMENT '二级部门名称',
`sale_cha_cd` string COMMENT '销售渠道代码',
`place_saler_dept_id_1` string COMMENT 'place_saler_dept_id_1',
`saler_erp_acct` string COMMENT '销售员ERP帐号',
`after_prefr_amount` string COMMENT 'after_prefr_amount',
`purchaser_erp_acct` string COMMENT '采购员ERP帐号',
`major_supp_brevity_name` string COMMENT '主供应商名称',
`union_coupon_pay_amount` string COMMENT '联合优惠券支付金额',
`place_saler_dept_id_4` string COMMENT 'place_saler_dept_id_4',
`place_saler_dept_id_3` string COMMENT 'place_saler_dept_id_3',
`place_saler_dept_id_2` string COMMENT 'place_saler_dept_id_2',
`paid_presale_deposit_flag` string COMMENT 'paid_presale_deposit_flag',
`cancel_flag` string COMMENT '取消标志',
`out_wh_tm_10min_seg` string COMMENT 'out_wh_tm_10min_seg',
`before_prefr_amount` string COMMENT '优惠前金额',
`brand_cd` string COMMENT '品牌编码',
`item_first_cate_name` string COMMENT '商品一级分类名称',
`ord_cancel_tm` string COMMENT '取消时间',
`item_name` string COMMENT '商品名称',
`sku_offer_amount` string COMMENT '单品优惠金额',
`virtual_ord_flag` string COMMENT '虚拟订单标志',
`jbean_pay_amount` string COMMENT '京豆支付金额',
`work_post_cd` string COMMENT 'work_post_cd',
`user_reg_tm` string COMMENT '用户注册时间',
`intraday_ord_cancel_flag` string COMMENT '订单当天取消标志',
`ord_cancel_tm_1hour_seg` string COMMENT 'ord_cancel_tm_1hour_seg',
`ts` string COMMENT 'ts',
`engy_subsidy_amount` string COMMENT '节能补贴金额',
`rev_addr_county_id` string COMMENT '收货地址区县编号',
`int_pur_ord_flag` string COMMENT 'int_pur_ord_flag',
`ord_deal_tm_10min_seg` string COMMENT 'ord_deal_tm_10min_seg',
`oper_bu_id` string COMMENT 'oper_bu_id',
`ord_deal_tm_1hour_seg` string COMMENT 'ord_deal_tm_1hour_seg',
`valid_flag` string COMMENT '有效标志',
`rev_addr_town_id` string COMMENT '收货地址乡镇编号',
`after_prefr_amount_1` string COMMENT '优惠后金额1--对内口径',
`county_id` string COMMENT 'county_id',
`item_sku_id` string COMMENT 'sku编号',
`check_account_tm` string COMMENT 'check_account_tm',
`afs_ser_fee_amount` string COMMENT '退换无忧服务费',
`ord_status_2_cd` string COMMENT '订单状态2代码',
`subd_num` string COMMENT '分公司编号',
`delv_station_id` string COMMENT '配送站点编号',
`intraday_ord_intr_flag` string COMMENT '订单当天引入标志',
`sale_ord_id` string COMMENT '销售订单编号',
`weight` string COMMENT 'weight',
`gift_cash_amount` string COMMENT 'gift_cash_amount',
`shop_name` string COMMENT '店铺名称',
`volume` string COMMENT 'volume',
`out_wh_tm` string COMMENT '出库时间',
`inflate_discount_amount` string COMMENT '膨胀金优惠金额',
`city_id` string COMMENT 'city_id',
`wholesale_or_retail` string COMMENT '废弃',
`lim_cate_dq_pay_amount` string COMMENT '限品类东券支付金额',
`rev_addr_level_name` string COMMENT 'rev_addr_level_name',
`major_supp_brevity_code` string COMMENT '主供应商简码',
`pop_operator_name` string COMMENT 'POP运营人员名称',
`user_erp` string COMMENT 'user_erp',
`parent_sale_ord_flag` string COMMENT '已拆分父单标志',
`vender_id` string COMMENT '商家编号',
`city_name` string COMMENT 'city_name',
`delv_center_name` string COMMENT '配送中心名称',
`achi_ord_flag` string COMMENT '业绩订单，1是，0否',
`gift_card_ord_flag` string COMMENT 'gift_card_ord_flag',
`out_wh_tm_1hour_seg` string COMMENT 'out_wh_tm_1hour_seg',
`spot_presale_flag` string COMMENT 'spot_presale_flag',
`sale_staf_name` string COMMENT '销售员名称',
`dq_pay_amount` string COMMENT '普通东券支付金额',
`lim_cate_jq_pay_amount` string COMMENT '限品类京券支付金额',
`pop_operator_erp_acct` string COMMENT 'POP运营人员erp账号',
`province_area_name` string COMMENT 'bpin注册地址归属战区',
`intraday_ord_complete_flag` string COMMENT '订单当天完成标志',
`ord_cancel_tm_10min_seg` string COMMENT 'ord_cancel_tm_10min_seg',
`rev_addr_province_name` string COMMENT '收货地址省份名称',
`sku_uuid` string COMMENT 'sku_uuid',
`county_name` string COMMENT 'county_name',
`item_last_cate_name` string COMMENT '商品末级分类名称',
`union_coupon_jd_pay_amount` string COMMENT '自营FBP联合按比例承担优惠劵(京东承担)',
`print_status_cd` string COMMENT '打印状态代码',
`sku_freight_amount` string COMMENT 'SKU运费金额',
`sku_type_new` string COMMENT 'sku类型_新增 (7C供B 0 入仓 1 厂直 2 联营 3 pop 4 联合仓)',
`plus_95_discount_amount` string COMMENT 'plus_95_discount_amount',
`jq_pay_amount` string COMMENT '普通京券支付金额',
`data_type` string COMMENT '商品类型',
`gp_offer_amount` string COMMENT '团购优惠金额',
`yhd_ord_flag` string COMMENT 'yhd_ord_flag',
`item_last_cate_cd` string COMMENT '商品末级分类代码',
`promotion_offer_amount` string COMMENT 'promotion_offer_amount',
`parent_sale_ord_id` string COMMENT '父销售订单编号',
`sku_jd_prc` string COMMENT 'sku_jd_prc',
`area_name` string COMMENT 'bpin注册地址所属大区',
`sale_channel` string COMMENT '销售渠道1 – 掌柜宝渠道; 2 – 分销宝渠道; 3 -主站企业大客户 4-B商城
0 – 其他',
`cfo_sales_channel` string COMMENT 'cfo销售渠道',
`sprt_value_amount` string COMMENT '价保金额',
`main_sku_id` string COMMENT '主sku编号',
`change_new_ord_flag` string COMMENT 'change_new_ord_flag',
`user_type` string COMMENT 'user_type',
`check_account_tm_1hour_seg` string COMMENT 'check_account_tm_1hour_seg',
`ptcj_ord_flag` string COMMENT 'ptcj_ord_flag',
`sale_ord_type_cd` string COMMENT '订单种类代码',
`sale_qtty` string COMMENT '销售数量',
`subd_name` string COMMENT '分公司名称',
`sku_name` string COMMENT 'sku名称',
`id` string COMMENT '自增ID',
`sale_ord_tm` string COMMENT '下单时间',
`bu_id` string COMMENT '事业群代码',
`rev_addr_area_name` string COMMENT 'rev_addr_area_name',
`pay_type_cd` string COMMENT '支付类型代码',
`work_dept_name` string COMMENT 'work_dept_name',
`sys_proc_time` string COMMENT 'sys_proc_time',
`store_name_operation` string COMMENT 'store_name_operation',
`user_log_acct` string COMMENT '用户登录帐号',
`worldwide_sale_ord_flag` string COMMENT 'worldwide_sale_ord_flag',
`upc` string COMMENT 'upc',
`sale_ord_det_id` string COMMENT '销售订单明细编号',
`rev_addr_province_id` string COMMENT '收货地址省份编号',
`province_name` string COMMENT 'province_name',
`ord_flag` string COMMENT '订单标志',
`delv_center_id` string COMMENT '配送中心编号',
`sale_ord_dt` string COMMENT 'sale_ord_dt',
`ord_deal_tm` string COMMENT '成交时间',
`sales_man_name` string COMMENT '客户经理名称',
`ord_complete_1hour_seg` string COMMENT 'ord_complete_1hour_seg',
`ord_status_1_cd` string COMMENT '订单状态1代码',
`delv_station_name` string COMMENT '配送站点名称',
`place_saler_erp_acct` string COMMENT 'place_saler_erp_acct',
`rev_addr_city_name` string COMMENT '收货地址城市名称',
`pay_cate_cd` string COMMENT '支付种类代码 1货到付款 2在线支付',
`vender_direct_delv_flag` string COMMENT '厂直标记',
`user_payable_pay_amount` string COMMENT '用户应付金额',
`jd_card_pay_amount` string COMMENT '京东卡支付金额',
`shelves_tm` string COMMENT 'shelves_tm',
`purchaser_name` string COMMENT '采购员名称',
`store_id` string COMMENT '库房编号',
`dept_id_4` string COMMENT '四级部门代码',
`dept_id_5` string COMMENT '五级部门代码',
`suit_offer_amount` string COMMENT '套装优惠金额',
`dept_id_2` string COMMENT '二级部门代码',
`coupon_pay_amount` string COMMENT 'coupon_pay_amount',
`dept_id_3` string COMMENT '三级部门代码',
`dept_id_6` string COMMENT '六级部门代码',
`dept_id_1` string COMMENT '一级部门代码',
`province_id` string COMMENT 'province_id',
`pay_first_flag` string COMMENT 'pay_first_flag',
`full_minus_offer_amount` string COMMENT '满减优惠金额',
`intraday_ord_out_wh_flag` string COMMENT '订单当天出库标志',
`sku_rebate_amount` string COMMENT 'SKU的返现金额',
`sales_man_erp` string COMMENT '地勤erp',
`oper_erp_acct` string COMMENT '运营人员账号',
`item_second_cate_cd` string COMMENT '商品二级分类代码',
`place_gtm_erp_acct` string COMMENT 'place_gtm_erp_acct',
`area_id` string COMMENT 'area_id',
`delv_ser_fee_amount` string COMMENT '落地配服务费',
`spu_id` string COMMENT '标品编号',
`jd_card_ord_flag` string COMMENT 'jd_card_ord_flag',
`purchaser_control_name` string COMMENT '采控员名称',
`sale_ord_tm_10min_seg` string COMMENT 'sale_ord_tm_10min_seg',
`sale_ord_tm_1hour_seg` string COMMENT 'sale_ord_tm_1hour_seg',
`sale_mode_cd` string COMMENT '销售模式 pop/owner',
`ord_syn_status_cd` string COMMENT 'ord_syn_status_cd',
`delv_way_cd` string COMMENT '配送方式代码',
`rev_addr_city_id` string COMMENT '收货地址城市编号',
`old_after_prefr_amount` string COMMENT 'old_after_prefr_amount',
`bu_name` string COMMENT '事业群名称',
`pop_shop_lim_sku_jq_pay_amount` string COMMENT '店铺限商品京券支付金额',
`kepler_ord_flag` string COMMENT 'kepler_ord_flag',
`item_third_cate_cd` string COMMENT '商品三级分类代码',
`free_goods_sale_qtty` string COMMENT '赠品销售数量',
`pop_shop_jq_pay_amount` string COMMENT 'POP店铺京券支付金额',
`split_status_cd` string COMMENT '拆分状态代码',
`regional_manager_name` string COMMENT 'regional_manager_name',
`pur_control_dept_id_1` string COMMENT 'pur_control_dept_id_1',
`self_ord_flag` string COMMENT 'self_ord_flag',
`pur_control_dept_id_3` string COMMENT 'pur_control_dept_id_3',
`pur_control_dept_id_2` string COMMENT 'pur_control_dept_id_2',
`pur_control_dept_id_4` string COMMENT 'pur_control_dept_id_4',
`worldwide_buy_ord_flag` string COMMENT 'worldwide_buy_ord_flag',
`acct_bal_pay_amount` string COMMENT '账户余额支付金额',
`store_name` string COMMENT '库房名称',
`pop_shop_lim_sku_dq_pay_amount` string COMMENT '店铺限商品东券支付金额',
`rev_addr_area_name_logic` string COMMENT 'rev_addr_area_name_logic',
`total_freight_amount` string COMMENT 'total_freight_amount',
`item_id` string COMMENT '商品编号',
`gtm_dept_id_2` string COMMENT 'gtm_dept_id_2',
`gtm_dept_id_1` string COMMENT 'gtm_dept_id_1',
`gtm_dept_id_4` string COMMENT 'gtm_dept_id_4',
`gtm_dept_id_3` string COMMENT 'gtm_dept_id_3',
`second_place_cd` string COMMENT 'second_place_cd',
`pur_channel_list` string COMMENT 'pur_channel_list',
`item_second_cate_name` string COMMENT '商品二级分类名称',
`union_coupon_pop_pay_amount` string COMMENT '自营FBP联合按比例承担优惠劵(商家承担)',
`pop_shop_dq_pay_amount` string COMMENT 'POP店铺东券支付金额',
`gtm_bu_id` string COMMENT 'gtm_bu_id',
`large_amt_ord_flag` string COMMENT '大额标识',
`place_saler_bu_id` string COMMENT 'place_saler_bu_id',
`diqin_area_id` string COMMENT '区域',
`diqin_area_name` string COMMENT '区域名称',
`ord_flag_dict` string COMMENT '订单标志')
COMMENT '[DTS]传输流自动创建表'
PARTITIONED BY (
`dt` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH SERDEPROPERTIES (
)
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
'hdfs://ns1001/user/mart_sch/app.db/app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt'
TBLPROPERTIES (
'jdbiz.processType'='rt',
'jdhive_ms_idc'='ms-hope',
'jdhive_storage_policy'='htyd:2:1,lfrz:2:0',
'storage_policy_lifecycle'='htyd:2:1#-1,lfrz:2:0#-1',
'mart_name'='mart_mobile',
'transient_lastDdlTime'='1744697143',
'EXTERNAL'='TRUE',
'SENSITIVE_TABLE'='TRUE',
'storage_policy_calc_mode'='partition_create_time',
'jdhive_global_idc'='mysql_hope2',
'row_policy'='')
1.1 we have following partitions ("dt=0-2025-08-11",
"dt=0-2025-08-10",
"dt=0-2025-08-09","dt=0-2025-08-08","dt=0-2025-08-07","dt=0-2025-08-06","dt=0-2025-08-05","dt=0-2025-08-04")
2,
explain SELECT * from( SELECT
"app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt"."sale_ord_det_id",
"app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt"."mid",
SUBSTR("app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt"."dt", 3,10) AS "dt",
ROW_NUMBER() OVER( PARTITION BY
"app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt"."sale_ord_det_id",
"app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt"."dt" ORDER BY
"app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt"."mid" DESC ) AS "rn" FROM
"app"."app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt" ) "a" where
SUBSTR("a"."dt", 1, 10) = SUBSTR(sysdate(0), 1, 10);
3.1, in doris the filter 'SUBSTR("a"."dt", 1, 10) = SUBSTR(sysdate(0), 1,
10)' will not pushdown to hivescannode, cause scan all partition
| ========== PARSED PLAN (time: 3ms) ==========
|
| UnboundResultSink[6] ( )
|
| +--LogicalProject[5] ( distinct=false, projects=[*], excepts=[] )
|
| +--LogicalFilter[4] ( predicates=('SUBSTR('a.dt, 1, 10) =
'SUBSTR('sysdate(0), 1, 10)) )
|
| +--LogicalSubQueryAlias ( qualifier=[a] )
|
| +--LogicalProject[2] ( distinct=false,
projects=['app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt.sale_ord_det_id,
'app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt.mid,
UnboundAlias('SUBSTR('app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt.dt, 3,
10)) AS dt, UnboundAlias('ROW_NUMBER() WindowSpec(PARTITION BY
'app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt.sale_ord_det_id,
'app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt.dt ORDER BY
'app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt.mid desc)) AS rn], excepts=[] )
|
| +--LogicalCheckPolicy ( )
|
| +--UnboundRelation ( id=RelationId#0,
nameParts=app.app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt )
|
|
| ========== ANALYZED PLAN (time: 3ms) ==========
|
| LogicalResultSink[22] ( outputExprs=[sale_ord_det_id#150, mid#28, dt#235,
rn#236] )
|
| +--LogicalProject[20] ( distinct=false, projects=[sale_ord_det_id#150,
mid#28, dt#235, rn#236], excepts=[] )
|
| +--LogicalFilter[18] ( predicates=(substring(dt#235, 1, 10) =
substring(sysdate(0), 1, 10)) )
|
| +--LogicalSubQueryAlias ( qualifier=[a] )
|
| +--LogicalProject[15] ( distinct=false,
projects=[sale_ord_det_id#150, mid#28, substring(dt#234, 3, 10) AS `dt`#235,
row_number() WindowSpec(PARTITION BY sale_ord_det_id#150, dt#234 ORDER BY
mid#28 desc) AS `rn`#236], excepts=[] )
|
| +--LogicalFileScan (
qualified=hive.app.app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt,
output=[shadow_sku_id#0, purchaser_control_erp_acct#1, item_third_cate_name#2,
sku_freight_coupon_amount#3, is_retract#4, brandname_full#5, vender_name#6,
check_account_tm_10min_seg#7, ord_complete_tm#8, sales_man_id#9,
third_place_cd#10, self_carry_status_cd#11, first_place_cd#12,
wait_check_account_flag#13, rev_addr_county_name#14, sale_sub_cha_cd#15,
shop_id#16, ord_complete_10min_seg#17, item_first_cate_cd#18,
intraday_ord_deal_flag#19, worldwide_tax_amount#20,
pur_sale_control_separate_flag#21, oper_dept_id_1#22, rev_addr_town_name#23,
dept_name_1#24, oper_dept_id_2#25, pur_control_bu_id#26, user_name#27, mid#28,
dept_name_6#29, dept_name_5#30, dept_name_4#31, oper_dept_id_3#32,
intraday_ord_valid_flag#33, dept_name_3#34, oper_dept_id_4#35, dept_name_2#36,
sale_cha_cd#37, place_saler_dept_id_1#38, saler_erp_acct#39,
after_prefr_amount#40, purchaser_erp_acct#41, major_supp_bre
vity_name#42, union_coupon_pay_amount#43, place_saler_dept_id_4#44,
place_saler_dept_id_3#45, place_saler_dept_id_2#46,
paid_presale_deposit_flag#47, cancel_flag#48, out_wh_tm_10min_seg#49,
before_prefr_amount#50, brand_cd#51, item_first_cate_name#52, ord_cancel_tm#53,
item_name#54, sku_offer_amount#55, virtual_ord_flag#56, jbean_pay_amount#57,
work_post_cd#58, user_reg_tm#59, intraday_ord_cancel_flag#60,
ord_cancel_tm_1hour_seg#61, ts#62, engy_subsidy_amount#63,
rev_addr_county_id#64, int_pur_ord_flag#65, ord_deal_tm_10min_seg#66,
oper_bu_id#67, ord_deal_tm_1hour_seg#68, valid_flag#69, rev_addr_town_id#70,
after_prefr_amount_1#71, county_id#72, item_sku_id#73, check_account_tm#74,
afs_ser_fee_amount#75, ord_status_2_cd#76, subd_num#77, delv_station_id#78,
intraday_ord_intr_flag#79, sale_ord_id#80, weight#81, gift_cash_amount#82,
shop_name#83, volume#84, out_wh_tm#85, inflate_discount_amount#86, city_id#87,
wholesale_or_retail#88, lim_cate_dq_pay_amount#89, rev_addr_level_name#90, m
ajor_supp_brevity_code#91, pop_operator_name#92, user_erp#93,
parent_sale_ord_flag#94, vender_id#95, city_name#96, delv_center_name#97,
achi_ord_flag#98, gift_card_ord_flag#99, out_wh_tm_1hour_seg#100,
spot_presale_flag#101, sale_staf_name#102, dq_pay_amount#103,
lim_cate_jq_pay_amount#104, pop_operator_erp_acct#105, province_area_name#106,
intraday_ord_complete_flag#107, ord_cancel_tm_10min_seg#108,
rev_addr_province_name#109, sku_uuid#110, county_name#111,
item_last_cate_name#112, union_coupon_jd_pay_amount#113, print_status_cd#114,
sku_freight_amount#115, sku_type_new#116, plus_95_discount_amount#117,
jq_pay_amount#118, data_type#119, gp_offer_amount#120, yhd_ord_flag#121,
item_last_cate_cd#122, promotion_offer_amount#123, parent_sale_ord_id#124,
sku_jd_prc#125, area_name#126, sale_channel#127, cfo_sales_channel#128,
sprt_value_amount#129, main_sku_id#130, change_new_ord_flag#131, user_type#132,
check_account_tm_1hour_seg#133, ptcj_ord_flag#134, sale_ord_type_cd#135,
sale_qtty#13
6, subd_name#137, sku_name#138, id#139, sale_ord_tm#140, bu_id#141,
rev_addr_area_name#142, pay_type_cd#143, work_dept_name#144, sys_proc_time#145,
store_name_operation#146, user_log_acct#147, worldwide_sale_ord_flag#148,
upc#149, sale_ord_det_id#150, rev_addr_province_id#151, province_name#152,
ord_flag#153, delv_center_id#154, sale_ord_dt#155, ord_deal_tm#156,
sales_man_name#157, ord_complete_1hour_seg#158, ord_status_1_cd#159,
delv_station_name#160, place_saler_erp_acct#161, rev_addr_city_name#162,
pay_cate_cd#163, vender_direct_delv_flag#164, user_payable_pay_amount#165,
jd_card_pay_amount#166, shelves_tm#167, purchaser_name#168, store_id#169,
dept_id_4#170, dept_id_5#171, suit_offer_amount#172, dept_id_2#173,
coupon_pay_amount#174, dept_id_3#175, dept_id_6#176, dept_id_1#177,
province_id#178, pay_first_flag#179, full_minus_offer_amount#180,
intraday_ord_out_wh_flag#181, sku_rebate_amount#182, sales_man_erp#183,
oper_erp_acct#184, item_second_cate_cd#185, place_gtm_erp_acct#186,
area_id#187, delv_ser_fee_amount#188, spu_id#189, jd_card_ord_flag#190,
purchaser_control_name#191, sale_ord_tm_10min_seg#192,
sale_ord_tm_1hour_seg#193, sale_mode_cd#194, ord_syn_status_cd#195,
delv_way_cd#196, rev_addr_city_id#197, old_after_prefr_amount#198, bu_name#199,
pop_shop_lim_sku_jq_pay_amount#200, kepler_ord_flag#201,
item_third_cate_cd#202, free_goods_sale_qtty#203, pop_shop_jq_pay_amount#204,
split_status_cd#205, regional_manager_name#206, pur_control_dept_id_1#207,
self_ord_flag#208, pur_control_dept_id_3#209, pur_control_dept_id_2#210,
pur_control_dept_id_4#211, worldwide_buy_ord_flag#212, acct_bal_pay_amount#213,
store_name#214, pop_shop_lim_sku_dq_pay_amount#215,
rev_addr_area_name_logic#216, total_freight_amount#217, item_id#218,
gtm_dept_id_2#219, gtm_dept_id_1#220, gtm_dept_id_4#221, gtm_dept_id_3#222,
second_place_cd#223, pur_channel_list#224, item_second_cate_name#225,
union_coupon_pop_pay_amount#226, pop_shop_dq_pay_amount#227, gtm_bu_id#228,
large_amt_ord_f
lag#229, place_saler_bu_id#230, diqin_area_id#231, diqin_area_name#232,
ord_flag_dict#233, dt#234] ) |
| ========== REWRITTEN PLAN (time: 5ms) ==========
|
| LogicalResultSink[101] ( outputExprs=[sale_ord_det_id#150, mid#28, dt#235,
rn#236] )
|
| +--LogicalProject[99] ( distinct=false, projects=[sale_ord_det_id#150,
mid#28, dt#235, rn#236], excepts=[] )
|
| +--LogicalFilter[97] ( predicates=(substring(dt#235, 1, 10) =
'2025-08-11') )
|
| +--LogicalWindow ( windowExpressions=[row_number()
WindowSpec(PARTITION BY sale_ord_det_id#150, dt#234 ORDER BY mid#28 desc ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `rn`#236], isChecked=true )
|
| +--LogicalProject[92] ( distinct=false,
projects=[sale_ord_det_id#150, mid#28, substring(dt#234, 3, 10) AS `dt`#235,
dt#234], excepts=[] )
|
| +--LogicalFileScan (
qualified=hive.app.app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt,
output=[shadow_sku_id#0, purchaser_control_erp_acct#1, item_third_cate_name#2,
sku_freight_coupon_amount#3, is_retract#4, brandname_full#5, vender_name#6,
check_account_tm_10min_seg#7, ord_complete_tm#8, sales_man_id#9,
third_place_cd#10, self_carry_status_cd#11, first_place_cd#12,
wait_check_account_flag#13, rev_addr_county_name#14, sale_sub_cha_cd#15,
shop_id#16, ord_complete_10min_seg#17, item_first_cate_cd#18,
intraday_ord_deal_flag#19, worldwide_tax_amount#20,
pur_sale_control_separate_flag#21, oper_dept_id_1#22, rev_addr_town_name#23,
dept_name_1#24, oper_dept_id_2#25, pur_control_bu_id#26, user_name#27, mid#28,
dept_name_6#29, dept_name_5#30, dept_name_4#31, oper_dept_id_3#32,
intraday_ord_valid_flag#33, dept_name_3#34, oper_dept_id_4#35, dept_name_2#36,
sale_cha_cd#37, place_saler_dept_id_1#38, saler_erp_acct#39,
after_prefr_amount#40, purchaser_erp_acct#41, major_supp_bre
vity_name#42, union_coupon_pay_amount#43, place_saler_dept_id_4#44,
place_saler_dept_id_3#45, place_saler_dept_id_2#46,
paid_presale_deposit_flag#47, cancel_flag#48, out_wh_tm_10min_seg#49,
before_prefr_amount#50, brand_cd#51, item_first_cate_name#52, ord_cancel_tm#53,
item_name#54, sku_offer_amount#55, virtual_ord_flag#56, jbean_pay_amount#57,
work_post_cd#58, user_reg_tm#59, intraday_ord_cancel_flag#60,
ord_cancel_tm_1hour_seg#61, ts#62, engy_subsidy_amount#63,
rev_addr_county_id#64, int_pur_ord_flag#65, ord_deal_tm_10min_seg#66,
oper_bu_id#67, ord_deal_tm_1hour_seg#68, valid_flag#69, rev_addr_town_id#70,
after_prefr_amount_1#71, county_id#72, item_sku_id#73, check_account_tm#74,
afs_ser_fee_amount#75, ord_status_2_cd#76, subd_num#77, delv_station_id#78,
intraday_ord_intr_flag#79, sale_ord_id#80, weight#81, gift_cash_amount#82,
shop_name#83, volume#84, out_wh_tm#85, inflate_discount_amount#86, city_id#87,
wholesale_or_retail#88, lim_cate_dq_pay_amount#89, rev_addr_level_name#90, m
ajor_supp_brevity_code#91, pop_operator_name#92, user_erp#93,
parent_sale_ord_flag#94, vender_id#95, city_name#96, delv_center_name#97,
achi_ord_flag#98, gift_card_ord_flag#99, out_wh_tm_1hour_seg#100,
spot_presale_flag#101, sale_staf_name#102, dq_pay_amount#103,
lim_cate_jq_pay_amount#104, pop_operator_erp_acct#105, province_area_name#106,
intraday_ord_complete_flag#107, ord_cancel_tm_10min_seg#108,
rev_addr_province_name#109, sku_uuid#110, county_name#111,
item_last_cate_name#112, union_coupon_jd_pay_amount#113, print_status_cd#114,
sku_freight_amount#115, sku_type_new#116, plus_95_discount_amount#117,
jq_pay_amount#118, data_type#119, gp_offer_amount#120, yhd_ord_flag#121,
item_last_cate_cd#122, promotion_offer_amount#123, parent_sale_ord_id#124,
sku_jd_prc#125, area_name#126, sale_channel#127, cfo_sales_channel#128,
sprt_value_amount#129, main_sku_id#130, change_new_ord_flag#131, user_type#132,
check_account_tm_1hour_seg#133, ptcj_ord_flag#134, sale_ord_type_cd#135,
sale_qtty#13
6, subd_name#137, sku_name#138, id#139, sale_ord_tm#140, bu_id#141,
rev_addr_area_name#142, pay_type_cd#143, work_dept_name#144, sys_proc_time#145,
store_name_operation#146, user_log_acct#147, worldwide_sale_ord_flag#148,
upc#149, sale_ord_det_id#150, rev_addr_province_id#151, province_name#152,
ord_flag#153, delv_center_id#154, sale_ord_dt#155, ord_deal_tm#156,
sales_man_name#157, ord_complete_1hour_seg#158, ord_status_1_cd#159,
delv_station_name#160, place_saler_erp_acct#161, rev_addr_city_name#162,
pay_cate_cd#163, vender_direct_delv_flag#164, user_payable_pay_amount#165,
jd_card_pay_amount#166, shelves_tm#167, purchaser_name#168, store_id#169,
dept_id_4#170, dept_id_5#171, suit_offer_amount#172, dept_id_2#173,
coupon_pay_amount#174, dept_id_3#175, dept_id_6#176, dept_id_1#177,
province_id#178, pay_first_flag#179, full_minus_offer_amount#180,
intraday_ord_out_wh_flag#181, sku_rebate_amount#182, sales_man_erp#183,
oper_erp_acct#184, item_second_cate_cd#185, place_gtm_erp_acct#186,
area_id#187, delv_ser_fee_amount#188, spu_id#189, jd_card_ord_flag#190,
purchaser_control_name#191, sale_ord_tm_10min_seg#192,
sale_ord_tm_1hour_seg#193, sale_mode_cd#194, ord_syn_status_cd#195,
delv_way_cd#196, rev_addr_city_id#197, old_after_prefr_amount#198, bu_name#199,
pop_shop_lim_sku_jq_pay_amount#200, kepler_ord_flag#201,
item_third_cate_cd#202, free_goods_sale_qtty#203, pop_shop_jq_pay_amount#204,
split_status_cd#205, regional_manager_name#206, pur_control_dept_id_1#207,
self_ord_flag#208, pur_control_dept_id_3#209, pur_control_dept_id_2#210,
pur_control_dept_id_4#211, worldwide_buy_ord_flag#212, acct_bal_pay_amount#213,
store_name#214, pop_shop_lim_sku_dq_pay_amount#215,
rev_addr_area_name_logic#216, total_freight_amount#217, item_id#218,
gtm_dept_id_2#219, gtm_dept_id_1#220, gtm_dept_id_4#221, gtm_dept_id_3#222,
second_place_cd#223, pur_channel_list#224, item_second_cate_name#225,
union_coupon_pop_pay_amount#226, pop_shop_dq_pay_amount#227, gtm_bu_id#228,
large_amt_ord_f
lag#229, place_saler_bu_id#230, diqin_area_id#231, diqin_area_name#232,
ord_flag_dict#233, dt#234] ) |
|
| ========== OPTIMIZED PLAN (time: 2ms) ==========
|
| PhysicalResultSink[215] ( outputExprs=[sale_ord_det_id#150, mid#28,
dt#235, rn#236] )
|
| +--PhysicalDistribute[212]@4 ( stats=7,268,491,
distributionSpec=DistributionSpecGather )
|
| +--PhysicalProject[209]@4 ( stats=7,268,491,
projects=[sale_ord_det_id#150, mid#28, dt#235, rn#236] )
|
| +--PhysicalFilter[206]@3 ( stats=7,268,491,
predicates=(substring(dt#235, 1, 10) = '2025-08-11') )
|
| +--PhysicalWindow[203]@2 ( windowFrameGroup=(Funcs=[row_number()
WindowSpec(PARTITION BY sale_ord_det_id#150, dt#234 ORDER BY mid#28 desc ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `rn`#236],
PartitionKeys=[sale_ord_det_id#150, dt#234], OrderKeys=[mid#28 desc],
WindowFrame=WindowFrame(ROWS, UNBOUNDED_PRECEDING, CURRENT_ROW)),
requiredProperties=[DistributionSpecHash ( orderedShuffledColumns=[150, 234],
shuffleType=REQUIRE, tableId=-1, selectedIndexId=-1, partitionIds=[],
equivalenceExprIds=[[150], [234]], exprIdToEquivalenceSet={150=0, 234=1} )
Order: ([sale_ord_det_id#150 asc, dt#234 asc, mid#28 desc])], stats=14,536,982
)
|
| +--PhysicalQuickSort[200]@1 ( orderKeys=[sale_ord_det_id#150
asc, dt#234 asc, mid#28 desc], phase=LOCAL_SORT, stats=14,536,982 )
|
| +--PhysicalDistribute[197]@1 ( stats=14,536,982,
distributionSpec=DistributionSpecHash ( orderedShuffledColumns=[150, 234],
shuffleType=EXECUTION_BUCKETED, tableId=-1, selectedIndexId=-1,
partitionIds=[], equivalenceExprIds=[[150], [234]],
exprIdToEquivalenceSet={150=0, 234=1} ) )
|
| +--PhysicalProject[194]@1 ( stats=14,536,982,
projects=[sale_ord_det_id#150, mid#28, substring(dt#234, 3, 10) AS `dt`#235,
dt#234] )
|
| +--PhysicalFileScan (
qualified=hive.app.app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt,
output=[shadow_sku_id#0, purchaser_control_erp_acct#1, item_third_cate_name#2,
sku_freight_coupon_amount#3, is_retract#4, brandname_full#5, vender_name#6,
check_account_tm_10min_seg#7, ord_complete_tm#8, sales_man_id#9,
third_place_cd#10, self_carry_status_cd#11, first_place_cd#12,
wait_check_account_flag#13, rev_addr_county_name#14, sale_sub_cha_cd#15,
shop_id#16, ord_complete_10min_seg#17, item_first_cate_cd#18,
intraday_ord_deal_flag#19, worldwide_tax_amount#20,
pur_sale_control_separate_flag#21, oper_dept_id_1#22, rev_addr_town_name#23,
dept_name_1#24, oper_dept_id_2#25, pur_control_bu_id#26, user_name#27, mid#28,
dept_name_6#29, dept_name_5#30, dept_name_4#31, oper_dept_id_3#32,
intraday_ord_valid_flag#33, dept_name_3#34, oper_dept_id_4#35, dept_name_2#36,
sale_cha_cd#37, place_saler_dept_id_1#38, saler_erp_acct#39,
after_prefr_amount#40, purchaser_erp_acct#41, majo
r_supp_brevity_name#42, union_coupon_pay_amount#43, place_saler_dept_id_4#44,
place_saler_dept_id_3#45, place_saler_dept_id_2#46,
paid_presale_deposit_flag#47, cancel_flag#48, out_wh_tm_10min_seg#49,
before_prefr_amount#50, brand_cd#51, item_first_cate_name#52, ord_cancel_tm#53,
item_name#54, sku_offer_amount#55, virtual_ord_flag#56, jbean_pay_amount#57,
work_post_cd#58, user_reg_tm#59, intraday_ord_cancel_flag#60,
ord_cancel_tm_1hour_seg#61, ts#62, engy_subsidy_amount#63,
rev_addr_county_id#64, int_pur_ord_flag#65, ord_deal_tm_10min_seg#66,
oper_bu_id#67, ord_deal_tm_1hour_seg#68, valid_flag#69, rev_addr_town_id#70,
after_prefr_amount_1#71, county_id#72, item_sku_id#73, check_account_tm#74,
afs_ser_fee_amount#75, ord_status_2_cd#76, subd_num#77, delv_station_id#78,
intraday_ord_intr_flag#79, sale_ord_id#80, weight#81, gift_cash_amount#82,
shop_name#83, volume#84, out_wh_tm#85, inflate_discount_amount#86, city_id#87,
wholesale_or_retail#88, lim_cate_dq_pay_amount#89, rev_addr_level_
name#90, major_supp_brevity_code#91, pop_operator_name#92, user_erp#93,
parent_sale_ord_flag#94, vender_id#95, city_name#96, delv_center_name#97,
achi_ord_flag#98, gift_card_ord_flag#99, out_wh_tm_1hour_seg#100,
spot_presale_flag#101, sale_staf_name#102, dq_pay_amount#103,
lim_cate_jq_pay_amount#104, pop_operator_erp_acct#105, province_area_name#106,
intraday_ord_complete_flag#107, ord_cancel_tm_10min_seg#108,
rev_addr_province_name#109, sku_uuid#110, county_name#111,
item_last_cate_name#112, union_coupon_jd_pay_amount#113, print_status_cd#114,
sku_freight_amount#115, sku_type_new#116, plus_95_discount_amount#117,
jq_pay_amount#118, data_type#119, gp_offer_amount#120, yhd_ord_flag#121,
item_last_cate_cd#122, promotion_offer_amount#123, parent_sale_ord_id#124,
sku_jd_prc#125, area_name#126, sale_channel#127, cfo_sales_channel#128,
sprt_value_amount#129, main_sku_id#130, change_new_ord_flag#131, user_type#132,
check_account_tm_1hour_seg#133, ptcj_ord_flag#134, sale_ord_type_cd#135, sa
le_qtty#136, subd_name#137, sku_name#138, id#139, sale_ord_tm#140, bu_id#141,
rev_addr_area_name#142, pay_type_cd#143, work_dept_name#144, sys_proc_time#145,
store_name_operation#146, user_log_acct#147, worldwide_sale_ord_flag#148,
upc#149, sale_ord_det_id#150, rev_addr_province_id#151, province_name#152,
ord_flag#153, delv_center_id#154, sale_ord_dt#155, ord_deal_tm#156,
sales_man_name#157, ord_complete_1hour_seg#158, ord_status_1_cd#159,
delv_station_name#160, place_saler_erp_acct#161, rev_addr_city_name#162,
pay_cate_cd#163, vender_direct_delv_flag#164, user_payable_pay_amount#165,
jd_card_pay_amount#166, shelves_tm#167, purchaser_name#168, store_id#169,
dept_id_4#170, dept_id_5#171, suit_offer_amount#172, dept_id_2#173,
coupon_pay_amount#174, dept_id_3#175, dept_id_6#176, dept_id_1#177,
province_id#178, pay_first_flag#179, full_minus_offer_amount#180,
intraday_ord_out_wh_flag#181, sku_rebate_amount#182, sales_man_erp#183,
oper_erp_acct#184, item_second_cate_cd#185, place_gtm_erp
_acct#186, area_id#187, delv_ser_fee_amount#188, spu_id#189,
jd_card_ord_flag#190, purchaser_control_name#191, sale_ord_tm_10min_seg#192,
sale_ord_tm_1hour_seg#193, sale_mode_cd#194, ord_syn_status_cd#195,
delv_way_cd#196, rev_addr_city_id#197, old_after_prefr_amount#198, bu_name#199,
pop_shop_lim_sku_jq_pay_amount#200, kepler_ord_flag#201,
item_third_cate_cd#202, free_goods_sale_qtty#203, pop_shop_jq_pay_amount#204,
split_status_cd#205, regional_manager_name#206, pur_control_dept_id_1#207,
self_ord_flag#208, pur_control_dept_id_3#209, pur_control_dept_id_2#210,
pur_control_dept_id_4#211, worldwide_buy_ord_flag#212, acct_bal_pay_amount#213,
store_name#214, pop_shop_lim_sku_dq_pay_amount#215,
rev_addr_area_name_logic#216, total_freight_amount#217, item_id#218,
gtm_dept_id_2#219, gtm_dept_id_1#220, gtm_dept_id_4#221, gtm_dept_id_3#222,
second_place_cd#223, pur_channel_list#224, item_second_cate_name#225,
union_coupon_pop_pay_amount#226, pop_shop_dq_pay_amount#227, gtm_bu_id#228,
large
_amt_ord_flag#229, place_saler_bu_id#230, diqin_area_id#231,
diqin_area_name#232, ord_flag_dict#233, dt#234], stats=14,536,982,
conjuncts=[], selected partitions num=unknown ) |
3.2, in presto, the filter will pushdown
Output[sale_ord_det_id, mid, dt, rn]
│ Layout: [sale_ord_det_id:varchar, mid:varchar, substr:varchar,
row_number:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ dt := substr
│ rn := row_number
└─ RemoteExchange[GATHER]
│ Layout: [mid:varchar, sale_ord_det_id:varchar, row_number:bigint,
substr:varchar]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ Project[]
│ Layout: [mid:varchar, sale_ord_det_id:varchar, row_number:bigint,
substr:varchar]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ substr := substr("dt", BIGINT '3', BIGINT '10')
└─ Window[partition by (<dt>, sale_ord_det_id), order by (mid
DESC_NULLS_LAST)][$hashvalue]
│ Layout: [mid:varchar, sale_ord_det_id:varchar, dt:varchar,
$hashvalue:bigint, row_number:bigint]
│ row_number := row_number() RANGE UNBOUNDED_PRECEDING
CURRENT_ROW
└─ LocalExchange[HASH][$hashvalue] ("sale_ord_det_id", "dt")
│ Layout: [mid:varchar, sale_ord_det_id:varchar, dt:varchar,
$hashvalue:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}
└─ RemoteExchange[REPARTITION][$hashvalue_23]
│ Layout: [mid:varchar, sale_ord_det_id:varchar,
dt:varchar, $hashvalue_23:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}
└─ ScanFilterProject[table =
jdt_edw:app:app_d04_jxt_trade_ord_det_sku_snapshot_achi_rt, filterPredicate =
("@subst
Layout: [mid:varchar, sale_ord_det_id:varchar,
dt:varchar, $hashvalue_24:bigint]
Estimates: {rows: ? (?), cpu: ?, memory: 0B, network:
0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{ro
$hashvalue_24 := combine_hash(combine_hash(bigint
'0', COALESCE("$operator$hash_code"("sale_ord_det_id"), 0)
dt := dt:string:PARTITION_KEY
:: [[0-2025-08-11]]
mid := mid:string:REGULAR
sale_ord_det_id := sale_ord_det_id:string:REGULAR
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
