oracle之with as 使用

布满荆棘的人生 2023-06-30 05:24 25阅读 0赞

项目中一个聚合查询，真正进行查询之前预先构造了一个临时表，之后便可多次使用它做进一步的分析和处理  
WITH Clause方法的优点  
增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。

WITH p_sum AS (
    	SELECT
    		report_type,
    		'xx' T,
    		self_agency trade_mode,
    		system_inoutside,
    		SUM (number_vol_toe) number_ton_sum,
    		SUM (sales_amount_usd) amount_usd_sum,
    		0.0 number_mm3_sum,
    		arrival_month ymonth
    	FROM
    		oil_bigdata.dwd_gas_tradestat_export
    	WHERE
    		arrival_month = '201908'
    	GROUP BY
    		arrival_month,
    		variety,
    		report_type,
    		self_agency,
    		system_inoutside
    	UNION ALL
    		SELECT
    			report_type,
    			'xx' T,
    			self_agency trade_mode,
    			system_inoutside,
    			SUM (purchase_netton) number_ton_sum,
    			SUM (amount_usd) amount_usd_sum,
    			SUM (cif_number_volume) number_mm3_sum,
    			arrival_month ymonth
    		FROM
    			oil_bigdata.dwd_gas_tradestat_import
    		WHERE
    			arrival_month = '201908'
    		GROUP BY
    			arrival_month,
    			variety,
    			report_type,
    			self_agency,
    			system_inoutside
    		UNION ALL
    			SELECT
    				report_type,
    				'xx' T,
    				self_agency trade_mode,
    				system_inoutside,
    				SUM (sales_netton) number_ton_sum,
    				SUM (sales_amount_rmb) amount_usd_sum,
    				0.0 number_mm3_sum,
    				year_month ymonth
    			FROM
    				oil_bigdata.dwd_gas_tradestat_trade
    			WHERE
    				year_month = '201908'
    			GROUP BY
    				year_month,
    				variety,
    				report_type,
    				self_agency,
    				system_inoutside
    			UNION ALL
    				SELECT
    					report_type,
    					'xx' T,
    					self_agency trade_mode,
    					system_inoutside,
    					CASE
    				WHEN variety = 'a1' THEN
    					SUM (number_vol_toe)
    				WHEN variety = 'a2' THEN
    					SUM (number_netton)
    				END AS number_ton_sum,
    				SUM (amount_usd) amount_usd_sum,
    				0.0 number_mm3_sum,
    				arrival_month ymonth
    			FROM
    				oil_bigdata.dwd_gas_tradestat_transit
    			WHERE
    				arrival_month = '201908'
    			GROUP BY
    				arrival_month,
    				variety,
    				report_type,
    				self_agency,
    				system_inoutside
    			UNION ALL
    				SELECT
    					report_type,
    					'xx' T,
    					self_agency trade_mode,
    					system_inoutside,
    					CASE
    				WHEN variety = 'a1' THEN
    					SUM (number_vol_toe)
    				WHEN variety = 'a2' THEN
    					SUM (number_netton)
    				END AS number_ton_sum,
    				SUM (amount_usd) amount_usd_sum,
    				0.0 number_mm3_sum,
    				delivery_month ymonth
    			FROM
    				oil_bigdata.dwd_gas_tradestat_transit
    			WHERE
    				delivery_month = '201908'
    			GROUP BY
    				delivery_month,
    				variety,
    				report_type,
    				self_agency,
    				system_inoutside
    ) SELECT
    	TRIM (report_type),
    	TRIM (T),
    	TRIM (trade_mode),
    	TRIM (system_inoutside),
    	number_ton_sum,
    	amount_usd_sum,
    	CASE
    WHEN number_mm3_sum = 0.0 THEN
    	NULL
    ELSE
    	number_mm3_sum
    END AS number_mm3_sum,
     TRIM (ymonth)
    FROM
    	p_sum