SQL:
SELECT stats_date FROM ( WITH fusion_order AS ( SELECT stats_date, union_id, union_name, zone_id, zone_name, org_id, org_name, ( CASE WHEN site_id = 1 THEN ‘one’ WHEN site_id = 5 THEN ‘two’ END ) AS org_type, ( CASE WHEN shipment_type = 8 THEN ‘on-1’ WHEN shipment_type = 10 THEN ‘on-2’ WHEN ( ( shipment_type IN(7, 9) AND ( order_from = ‘Shop_Lite’ OR order_type = ‘attr’ ) ) OR shipment_type = 1 ) THEN ‘x1’ WHEN ( order_from != ‘Shop_Lite’ AND shipment_type = 7 AND order_type = ‘natural’ ) THEN ‘x1’ WHEN ( order_from != ‘Shop_Lite’ AND shipment_type = 9 AND order_type = ‘natural’ ) THEN ‘x3’ END ) AS shipment_type, SUM(valid_ord_cnt) AS ord_cnt FROM nrdc_bi.ads_o2ofusion_ord_org_d GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 ), fusion_order_org AS ( SELECT stats_date, union_id, union_name, zone_id, zone_name, org_id, org_name, site_id, SUM(valid_ord_cnt) AS ord_cnt FROM nrdc_bi.ads_o2ofusion_ord_org_d GROUP BY 1, 2, 3, 4, 5, 6, 7, 8 ), retail_order AS ( SELECT t1.stats_date, t1.union_id, t1.union_name, t1.zone_id, t1.zone_name, t1.org_id, t1.org_name, ( CASE WHEN t1.site_id = 1 THEN ‘x1’ WHEN t1.site_id = 5 THEN ‘x1’ END ) AS org_type, ‘x’ AS shipment_type, SUM(t2.ord_cnt - t1.ord_cnt) AS ord_cnt FROM fusion_order_org t1 INNER JOIN nrdc_bi.ads_retail_ord_store_d t2 ON t1.stats_date = t2.stats_date AND t1.org_id = t2.org_id GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 ) SELECT * FROM fusion_order UNION ALL SELECT * FROM retail_order ) sql_model_virtual_table_new_27082_12067 where stats_date like ‘%zghe%’ limit 1;
执行计划：
±-----------------------------------------±-----------±----------±------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | estRows | task | access object | operator info
±-----------------------------------------±-----------±----------±------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Limit_34 | 1.00 | root | | offset:0, count:1
| └─Union_35 | 1.00 | root | |
| ├─Limit_39 | 0.80 | root | | offset:0, count:1
| │ └─Selection_40 | 0.80 | root | | like(cast(nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, var_string(10)), “%z%”, 92)
| │ └─CTEFullScan_50 | 1.00 | root | CTE:fusion_order | data:CTE_0
| └─Limit_54 | 0.80 | root | | offset:0, count:1
| └─Selection_55 | 0.80 | root | | like(cast(nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, var_string(10)), “%z%”, 92)
| └─CTEFullScan_88 | 1.00 | root | CTE:retail_order | data:CTE_2
| CTE_0 | 1.00 | root | | Non-Recursive CTE
| └─Projection_41(Seed Part) | 9954.85 | root | | nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.union_id, nrdc_bi.ads_o2ofusion_ord_org_d.union_name, nrdc_bi.ads_o2ofusion_ord_org_d.zone_id, nrdc_bi.ads_o2ofusion_ord_org_d.zone_name, nrdc_bi.ads_o2ofusion_ord_org_d.org_id, nrdc_bi.
| └─HashAgg_46 | 9954.85 | root | | group by:Column#115, Column#116, nrdc_bi.ads_o2ofusion_ord_org_d.org_id, nrdc_bi.ads_o2ofusion_ord_org_d.org_name, nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.union_id, nrdc_bi.ads_o2ofusion_ord_org_d.union_name, nrdc_bi.ads_o2ofu
| └─TableReader_47 | 9954.85 | root | | data:HashAgg_42
| └─HashAgg_42 | 9954.85 | cop[tikv] | | group by:case(eq(cast(nrdc_bi.ads_o2ofusion_ord_org_d.shipment_type, double BINARY), 8), “美团闪购”, eq(cast(nrdc_bi.ads_o2ofusion_ord_org_d.shipment_type, double BINARY), 10), “京东到家”, or(and(or(eq(cast(nrdc_bi.ads_o2ofusion_ord_org_d.shipment_type, double B
| └─TableFullScan_45 | 1070944.00 | cop[tikv] | table:ads_o2ofusion_ord_org_d | keep order:false, stats:pseudo
| CTE_2 | 1.00 | root | | Non-Recursive CTE
| └─Projection_58(Seed Part) | 1.00 | root | | nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.union_id, nrdc_bi.ads_o2ofusion_ord_org_d.union_name, nrdc_bi.ads_o2ofusion_ord_org_d.zone_id, nrdc_bi.ads_o2ofusion_ord_org_d.zone_name, nrdc_bi.ads_o2ofusion_ord_org_d.org_id, nrdc_bi.
| └─HashAgg_59 | 1.00 | root | | group by:Column#152, Column#153, Column#154, Column#155, Column#156, Column#157, Column#158, Column#159, funcs:sum(Column#143)->Column#80, funcs:firstrow(Column#144)->nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, funcs:firstrow(Column#145)->nrdc_bi.ads_o2ofusion_o
| └─Projection_87 | 0.80 | root | | minus(cast(nrdc_bi.ads_retail_ord_store_d.ord_cnt, decimal(20,0) BINARY), Column#65)->Column#143, nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.union_id, nrdc_bi.ads_o2ofusion_ord_org_d.union_name, nrdc_bi.ads_o2ofusion_ord_org_d.zo
| └─IndexJoin_63 | 0.80 | root | | inner join, inner:IndexLookUp_62, outer key:nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.org_id, inner key:nrdc_bi.ads_retail_ord_store_d.stats_date, nrdc_bi.ads_retail_ord_store_d.org_id, equal cond:eq(nrdc_bi.ads_o2ofusion_ord_or
| ├─Selection_74(Build) | 0.80 | root | | not(isnull(nrdc_bi.ads_o2ofusion_ord_org_d.org_id)), not(isnull(nrdc_bi.ads_o2ofusion_ord_org_d.stats_date))
| │ └─CTEFullScan_84 | 1.00 | root | CTE:t1 | data:CTE_1
| └─IndexLookUp_62(Probe) | 1.00 | root | |
| ├─IndexRangeScan_60(Build) | 1.00 | cop[tikv] | table:t2, index:uk_date_ord(stats_date, org_id) | range: decided by [eq(nrdc_bi.ads_retail_ord_store_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.stats_date) eq(nrdc_bi.ads_retail_ord_store_d.org_id, nrdc_bi.ads_o2ofusion_ord_org_d.org_id)], keep order:false, stats:pseudo
| └─TableRowIDScan_61(Probe) | 1.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo
| CTE_1 | 1.00 | root | | Non-Recursive CTE
| └─Projection_75(Seed Part) | 9954.85 | root | | nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.union_id, nrdc_bi.ads_o2ofusion_ord_org_d.union_name, nrdc_bi.ads_o2ofusion_ord_org_d.zone_id, nrdc_bi.ads_o2ofusion_ord_org_d.zone_name, nrdc_bi.ads_o2ofusion_ord_org_d.org_id, nrdc_bi.
| └─HashAgg_80 | 9954.85 | root | | group by:nrdc_bi.ads_o2ofusion_ord_org_d.org_id, nrdc_bi.ads_o2ofusion_ord_org_d.org_name, nrdc_bi.ads_o2ofusion_ord_org_d.site_id, nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.union_id, nrdc_bi.ads_o2ofusion_ord_org_d.union_name,
| └─TableReader_81 | 9954.85 | root | | data:HashAgg_76
| └─HashAgg_76 | 9954.85 | cop[tikv] | | group by:nrdc_bi.ads_o2ofusion_ord_org_d.org_id, nrdc_bi.ads_o2ofusion_ord_org_d.org_name, nrdc_bi.ads_o2ofusion_ord_org_d.site_id, nrdc_bi.ads_o2ofusion_ord_org_d.stats_date, nrdc_bi.ads_o2ofusion_ord_org_d.union_id, nrdc_bi.ads_o2ofusion_ord_org_d.union_name,
| └─TableFullScan_79 | 1070944.00 | cop[tikv] | table:ads_o2ofusion_ord_org_d | keep order:false, stats:pseudo
±-----------------------------------------±-----------±----------±------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
主要有CTE， SQL binding 不生效
~
~
~