一个复杂的sql 查询

「爱情、让人受尽委屈。」 2023-01-15 15:25 121阅读 0赞

四级联，sql 拆分为三个两级级联

### 1. ###

四级联，sql 查询  
productGroup->product->region->customer

WITH prod_group AS (
    	SELECT
    		dict_code AS product_group 
    	FROM
    		sys_dict 
    	WHERE
    		parent_id = ( SELECT ID FROM sys_dict WHERE dict_code = '40f44f426e874c989fce836ebfaea451' ) 
    		AND dict_code != 'c8ea8e97547841278af5f229a05d30da' 
    		AND is_delete = 0 
    	),
    	prod AS ( 
    	SELECT npi_product_name AS product FROM dim_product_mapping GROUP BY npi_product_name 
    	),
    	prodgroup_prod AS ( 
    	SELECT * FROM prod_group, prod UNION ALL SELECT 'c8ea8e97547841278af5f229a05d30da' AS product_group, 'All' AS product 
    	)
    	,
    	half_region AS (
    	SELECT
    		prod.npi_product_name AS product,
    		region.region 
    	FROM
    		dim_product_mapping AS prod
    		LEFT JOIN dim_mapping_region_country AS region ON region.product = prod.npi_product_name 
    	GROUP BY
    		prod.npi_product_name,
    		region.region 
    	ORDER BY
    		prod.npi_product_name,
    		region.region 
    	)
    	, product_s as(
    		select product FROM half_region  GROUP BY product ORDER BY product)
    	-- region of product in All
    	, region_s as(
    	select region FROM dim_mapping_region_country where product='All' GROUP BY region ORDER BY region)
    	, prod_reg as (
    	select * from product_s,region_s
    	)
    	,product_region AS ( 
    	SELECT * FROM half_region 
    	UNION SELECT * FROM prod_reg 
    	UNION ALL 
    	SELECT 'All' AS product, region FROM dim_mapping_region_country GROUP BY region ORDER BY region
    	)
    	,r_tem AS (
    	SELECT
    		reg.region,
    		cus.npi_customer AS customer 
    	FROM
    		dim_mapping_region_country AS reg
    		LEFT JOIN dim_mapping_country_customer AS cus ON cus.npi_country = reg.country 
    	WHERE
    		reg.region IS NOT NULL 
    	GROUP BY
    		reg.region,
    		cus.npi_customer UNION ALL
    	SELECT
    		npi_region AS region,
    		pss_npi_customer AS customer 
    	FROM
    		datahub_customer_mapping_business_case 
    	WHERE
    		npi_region IS NOT NULL 
    	GROUP BY
    		npi_region,
    		pss_npi_customer UNION ALL
    	SELECT
    		npi_region AS region,
    		npi_customer AS customer 
    	FROM
    		datahub_customer_mapping_pdp 
    	WHERE
    		npi_region IS NOT NULL 
    	GROUP BY
    		npi_region,
    		npi_customer 
    	),
    	region_customer AS ( SELECT region, customer FROM r_tem GROUP BY region, customer ORDER BY region, customer )
    	,
    	tem AS (
    	SELECT
    		pgp.product_group,
    		pgp.product,
    		pr.region
    		,
    		rc.customer 
    	FROM
    		prodgroup_prod AS pgp
    		LEFT JOIN product_region AS pr ON pr.product = pgp.product
    		LEFT JOIN region_customer AS rc ON rc.region = pr.region 
    	WHERE
    		pgp.product_group IS NOT NULL 
    	GROUP BY
    		pgp.product_group,
    		pgp.product,
    		pr.region
    		,rc.customer 
    	ORDER BY
    		pgp.product_group,
    		pgp.product,
    		pr.region
    		,rc.customer 
    	) SELECT
    	concat_ws ( '---', dict_name, product_group) AS "dict_name---dict_code",
    	product,
    	region
    	,
    	customer 
    FROM
    	tem
    	LEFT JOIN sys_dict AS sys ON sys.dict_code = tem.product_group

### 2.productGroup->product ###

-- productGroup/product (200)
    WITH prod_group AS (
    	SELECT
    		dict_code AS product_group 
    	FROM
    		sys_dict 
    	WHERE
    		parent_id = ( SELECT ID FROM sys_dict WHERE dict_code = '40f44f426e874c989fce836ebfaea451' ) 
    		AND dict_code != 'c8ea8e97547841278af5f229a05d30da' 
    		AND is_delete = 0 
    	),
    	prod AS ( 
    	SELECT npi_product_name AS product FROM dim_product_mapping GROUP BY npi_product_name 
    	)
    	,
    	prodgroup_prod AS ( 
    	SELECT * FROM prod_group, prod UNION ALL SELECT 'c8ea8e97547841278af5f229a05d30da' AS product_group, 'All' AS product 
    	)
    	SELECT
    	concat_ws ( '---', dict_name, product_group) AS "dict_name---dict_code",
    	product
    FROM
    	prodgroup_prod as tem
    	LEFT JOIN sys_dict AS sys ON sys.dict_code = tem.product_group

### 3.product->region ###

-- product/region(934)
    
    	with half_region AS (
    	SELECT
    		prod.npi_product_name AS product,
    		region.region 
    	FROM
    		dim_product_mapping AS prod
    		LEFT JOIN dim_mapping_region_country AS region ON region.product = prod.npi_product_name 
    	GROUP BY
    		prod.npi_product_name,
    		region.region 
    	ORDER BY
    		prod.npi_product_name,
    		region.region 
    	)
    	, product_s as(
    		select product FROM half_region  GROUP BY product ORDER BY product)
    	-- region of product in All
    	, region_s as(
    	select region FROM dim_mapping_region_country where product='All' GROUP BY region ORDER BY region)
    	, prod_reg as (
    	select * from product_s,region_s
    	)
    	,product_region AS ( 
    	SELECT * FROM half_region 
    	UNION SELECT * FROM prod_reg 
    	UNION ALL 
    	SELECT 'All' AS product, region FROM dim_mapping_region_country GROUP BY region ORDER BY region
    	)
    	select * from product_region group by product, region ORDER BY product, region

### 4.region->customer ###

-- region/customer(34)
    	with r_tem AS (
    	SELECT
    		reg.region,
    		cus.npi_customer AS customer 
    	FROM
    		dim_mapping_region_country AS reg
    		LEFT JOIN dim_mapping_country_customer AS cus ON cus.npi_country = reg.country 
    	WHERE
    		reg.region IS NOT NULL 
    	GROUP BY
    		reg.region,
    		cus.npi_customer UNION ALL
    	SELECT
    		npi_region AS region,
    		pss_npi_customer AS customer 
    	FROM
    		datahub_customer_mapping_business_case 
    	WHERE
    		npi_region IS NOT NULL 
    	GROUP BY
    		npi_region,
    		pss_npi_customer UNION ALL
    	SELECT
    		npi_region AS region,
    		npi_customer AS customer 
    	FROM
    		datahub_customer_mapping_pdp 
    	WHERE
    		npi_region IS NOT NULL 
    	GROUP BY
    		npi_region,
    		npi_customer 
    	)
    	SELECT region, customer FROM r_tem GROUP BY region, customer ORDER BY region, customer

### 5.product/region/customer ###

还可以拆分为一个三级级联

with half_region AS (
    	SELECT
    		prod.npi_product_name AS product,
    		region.region 
    	FROM
    		dim_product_mapping AS prod
    		LEFT JOIN dim_mapping_region_country AS region ON region.product = prod.npi_product_name 
    	GROUP BY
    		prod.npi_product_name,
    		region.region 
    	ORDER BY
    		prod.npi_product_name,
    		region.region 
    	)
    	, product_s as(
    		select product FROM half_region  GROUP BY product ORDER BY product)
    	-- region of product in All
    	, region_s as(
    	select region FROM dim_mapping_region_country where product='All' GROUP BY region ORDER BY region)
    	, prod_reg as (
    	select * from product_s,region_s
    	)
    	,product_region AS ( 
    	SELECT * FROM half_region 
    	UNION SELECT * FROM prod_reg 
    	UNION ALL 
    	SELECT 'All' AS product, region FROM dim_mapping_region_country GROUP BY region ORDER BY region
    	)
    	,r_tem AS (
    	SELECT
    		reg.region,
    		cus.npi_customer AS customer 
    	FROM
    		dim_mapping_region_country AS reg
    		LEFT JOIN dim_mapping_country_customer AS cus ON cus.npi_country = reg.country 
    	WHERE
    		reg.region IS NOT NULL 
    	GROUP BY
    		reg.region,
    		cus.npi_customer UNION ALL
    	SELECT
    		npi_region AS region,
    		pss_npi_customer AS customer 
    	FROM
    		datahub_customer_mapping_business_case 
    	WHERE
    		npi_region IS NOT NULL 
    	GROUP BY
    		npi_region,
    		pss_npi_customer UNION ALL
    	SELECT
    		npi_region AS region,
    		npi_customer AS customer 
    	FROM
    		datahub_customer_mapping_pdp 
    	WHERE
    		npi_region IS NOT NULL 
    	GROUP BY
    		npi_region,
    		npi_customer 
    	),
    	region_customer AS ( SELECT region, customer FROM r_tem GROUP BY region, customer ORDER BY region, customer )
    -- ,
    -- tem AS (
    	SELECT
    -- pgp.product_group,
    		pr.product,
    		pr.region
    		,
    		rc.customer 
    	FROM
    		product_region AS pr 
    		LEFT JOIN region_customer AS rc ON rc.region = pr.region 
    	WHERE
    		pr.product IS NOT NULL 
    	GROUP BY
    -- pgp.product_group,
    		pr.product,
    		pr.region
    		,rc.customer 
    	ORDER BY
    -- pgp.product_group,
    		pr.product,
    		pr.region
    		,rc.customer