oracle之with as 使用

项目中一个聚合查询，真正进行查询之前预先构造了一个临时表，之后便可多次使用它做进一步的分析和处理
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