agg 在CTE中， 没法用hint 绑定
SELECT
stats_date
FROM
(
WITH fusion_order AS (
SELECT
/*+ STREAM_AGG() /
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
/+ STREAM_AGG() /
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
/+ STREAM_AGG() */
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;
这个单独执行执行计划确实用了stream agg , 但是如果使用SPM 进行bind 就无法生效