下面是一个查询的长sql ，记录于此：
with base_v0 as (
select
a.geo,
a.subgeo,
a.country,
a.product_group,
a.brand,
a.product_name,
a.product as sm_number,
d.item_short_description as sm_description,
a.bucket,
a.source_code,
a.site_id,
b.factory,
a.pdp_init,
a.pdp_final,
a.version,
a.update_timestamp,
a.seller,
a.demand_type,
a.color,
a.mram,
a.mrom,
a.design_category,
a.subbrand,
a.batch_number,
a.xcvr,
a.pss_version,
e.id as npi_product_id,
e.npi_product_name
from
(((ludp_pdp a
join (
select
dim_product_mapping.id,
dim_product_mapping.npi_product_name,
dim_product_mapping.product_name_in_sci_pdp
from
dim_product_mapping
where
((dim_product_mapping.product_name_in_sci_pdp)::text != ''::text
and npi_product_name ='BERLIN' )
group by
dim_product_mapping.id,
dim_product_mapping.npi_product_name,
dim_product_mapping.product_name_in_sci_pdp) e on
(((a.product_name)::text = (e.product_name_in_sci_pdp)::text)))
left join (
select
distinct dim_factory_mapping.factory,
dim_factory_mapping.site_id
from
dim_factory_mapping) b on
(((a.site_id)::text = (b.site_id)::text)))
left join ludp_windchill_product_item d on
(((a.product)::text = (d.item_number)::text)))
where
1 = 1
and a.version ='20241111_Update' ) ,
base_v1 as (
select
a.geo,
a.subgeo,
a.country,
a.product_group,
a.brand,
a.product_name,
a.sm_number,
a.sm_description,
a.bucket,
a.source_code,
a.site_id,
a.factory,
a.pdp_init,
a.pdp_final,
a.version,
a.update_timestamp,
a.seller,
a.demand_type,
a.color,
a.mram,
a.mrom,
a.design_category,
a.subbrand,
a.batch_number,
a.xcvr,
a.pss_version,
c.npi_product_id,
c.npi_product,
c.npi_country,
c.npi_region,
c.npi_customer,
c.npi_color
from
(base_v0 a
join (
select
distinct datahub_customer_mapping_pdp.npi_product_id,
datahub_customer_mapping_pdp.npi_product,
datahub_customer_mapping_pdp.npi_country,
datahub_customer_mapping_pdp.npi_region,
datahub_customer_mapping_pdp.npi_customer,
datahub_customer_mapping_pdp.npi_color,
datahub_customer_mapping_pdp.pdp_product_name,
datahub_customer_mapping_pdp.pdp_country_code,
datahub_customer_mapping_pdp.pdp_color,
datahub_customer_mapping_pdp.pdp_sm_number
from
datahub_customer_mapping_pdp ) c on
((((a.product_name)::text = (c.pdp_product_name)::text)
and ((a.country)::text = (c.pdp_country_code)::text)
and ((a.color)::text = (c.pdp_color)::text)
and ((a.sm_number)::text = (c.pdp_sm_number)::text)
and ((a.npi_product_id)::text = (c.npi_product_id)::text)))) ),
filter_v1 as (
select
base_v1.npi_product_id,
base_v1.npi_product,
base_v1.country
from
base_v1
where
(base_v1.npi_product_id is not null)
group by
base_v1.npi_product_id,
base_v1.npi_product,
base_v1.country ),
data_set_d as (
select
a.id,
a.npi_product_name,
a.product_name_in_sci_pdp,
b.npi_product_id,
b.npi_region,
b.country,
b.country_code
from
(dim_product_mapping a
left join (
select
dim_mapping_region_country_product.npi_product_id,
dim_mapping_region_country_product.npi_region,
dim_mapping_region_country_product.country,
dim_mapping_region_country_product.country_code,
row_number() over (partition by dim_mapping_region_country_product.npi_product_id,
dim_mapping_region_country_product.npi_region
order by
dim_mapping_region_country_product.country_code) as order_index
from
dim_mapping_region_country_product) b on
((((a.id)::text = (b.npi_product_id)::text)
and ((b.order_index = 1)
or ((b.order_index > 1)
and (b.country_code is not null)))))) ),
data_set_e as (
select
a.id,
a.npi_product_name,
a.product_name_in_sci_pdp,
a.npi_product_id,
a.npi_region,
coalesce(a.country,
b.country) as country,
coalesce(a.country_code,
b.country_code) as country_code
from
(data_set_d a
left join dim_mapping_region_country b on
((((a.npi_region)::text = (b.region)::text)
and (a.npi_region is not null)
and (a.country_code is null)))) ),
data_set_f as (
select
distinct data_set_e.id,
data_set_e.npi_product_name,
data_set_e.product_name_in_sci_pdp
from
data_set_e
where
((data_set_e.npi_product_id is null)
and (data_set_e.npi_region is null)
and (data_set_e.country_code is null)) ),
data_set_a_sub_b as (
select
a.geo,
a.subgeo,
a.country,
a.product_group,
a.brand,
a.product_name,
a.sm_number,
a.sm_description,
a.bucket,
a.source_code,
a.site_id,
a.factory,
a.pdp_init,
a.pdp_final,
a.version,
a.update_timestamp,
a.seller,
a.demand_type,
a.color,
a.mram,
a.mrom,
a.design_category,
a.subbrand,
a.batch_number,
a.xcvr,
a.pss_version,
a.npi_product_id,
a.npi_product_name
from
(base_v0 a
left join base_v1 b on
((((a.npi_product_id)::text = (b.npi_product_id)::text)
and ((a.product_name)::text = (b.product_name)::text)
and ((a.sm_number)::text = (b.sm_number)::text)
and ((a.country)::text = (b.country)::text)
and ((a.color)::text = (b.color)::text))))
where
(b.npi_product_id is null) ),
data_set_g as (
select
a.geo,
a.subgeo,
a.country,
a.product_group,
a.brand,
a.product_name,
a.sm_number,
a.sm_description,
a.bucket,
a.source_code,
a.site_id,
a.factory,
a.pdp_init,
a.pdp_final,
a.version,
a.update_timestamp,
a.seller,
a.demand_type,
a.color,
a.mram,
a.mrom,
a.design_category,
a.subbrand,
a.batch_number,
a.xcvr,
a.pss_version,
a.npi_product_id,
a.npi_product_name
from
(data_set_a_sub_b a
join data_set_f f on
((((a.npi_product_id)::text = (f.id)::text)
and ((a.product_name)::text = (f.product_name_in_sci_pdp)::text)))) ) ,
data_set_h as (
select
a.geo,
a.subgeo,
a.country,
a.product_group,
a.brand,
a.product_name,
a.sm_number,
a.sm_description,
a.bucket,
a.source_code,
a.site_id,
a.factory,
a.pdp_init,
a.pdp_final,
a.version,
a.update_timestamp,
a.seller,
a.demand_type,
a.color,
a.mram,
a.mrom,
a.design_category,
a.subbrand,
a.batch_number,
a.xcvr,
a.pss_version,
a.npi_product_id,
a.npi_product_name as npi_product,
coalesce(b.country,
c.country) as npi_country,
coalesce(b.region,
c.region) as npi_region,
a.seller as npi_customer,
null::text as npi_color
from
((data_set_g a
left join dim_mapping_region_country b on
((((a.country)::text = (b.country_code)::text)
and ((a.npi_product_name)::text = (b.product)::text))))
left join (
select
dim_mapping_region_country.id,
dim_mapping_region_country.country_code,
dim_mapping_region_country.sub_geo,
dim_mapping_region_country.geo,
dim_mapping_region_country.region,
dim_mapping_region_country.product,
dim_mapping_region_country.create_time,
dim_mapping_region_country.update_time,
dim_mapping_region_country.creator,
dim_mapping_region_country.update_by,
dim_mapping_region_country.country,
dim_mapping_region_country.sub_geo_desc,
dim_mapping_region_country.geo_desc
from
dim_mapping_region_country
where
((dim_mapping_region_country.product)::text = 'ALL'::text)) c on
(((a.country)::text = (c.country_code)::text))) ) ,
data_set_a_sub_h_sub_c as (
select
a.geo,
a.subgeo,
a.country,
a.product_group,
a.brand,
a.product_name,
a.sm_number,
a.sm_description,
a.bucket,
a.source_code,
a.site_id,
a.factory,
a.pdp_init,
a.pdp_final,
a.version,
a.update_timestamp,
a.seller,
a.demand_type,
a.color,
a.mram,
a.mrom,
a.design_category,
a.subbrand,
a.batch_number,
a.xcvr,
a.pss_version,
a.npi_product_id,
a.npi_product_name
from
((
select
a_1.geo,
a_1.subgeo,
a_1.country,
a_1.product_group,
a_1.brand,
a_1.product_name,
a_1.sm_number,
a_1.sm_description,
a_1.bucket,
a_1.source_code,
a_1.site_id,
a_1.factory,
a_1.pdp_init,
a_1.pdp_final,
a_1.version,
a_1.update_timestamp,
a_1.seller,
a_1.demand_type,
a_1.color,
a_1.mram,
a_1.mrom,
a_1.design_category,
a_1.subbrand,
a_1.batch_number,
a_1.xcvr,
a_1.pss_version,
a_1.npi_product_id,
a_1.npi_product_name
from
(base_v0 a_1
left join data_set_h h on
((((a_1.npi_product_id)::text = (h.npi_product_id)::text)
and ((a_1.product_name)::text = (h.product_name)::text)
and ((a_1.sm_number)::text = (h.sm_number)::text)
and ((a_1.country)::text = (h.country)::text)
and ((a_1.color)::text = (h.color)::text))))
where
(h.npi_product_id is null)) a
left join filter_v1 c on
((((a.npi_product_id)::text = (c.npi_product_id)::text)
and ((a.country)::text = (c.country)::text))))
where
(c.npi_product_id is null) ),
data_set_e_sub_f as (
select
e.id,
e.npi_product_name,
e.product_name_in_sci_pdp,
e.npi_product_id,
e.npi_region,
e.country,
e.country_code
from
(data_set_e e
left join data_set_f f on
((((e.id)::text = (f.id)::text)
and ((e.npi_product_name)::text = (f.npi_product_name)::text)
and ((e.product_name_in_sci_pdp)::text = (f.product_name_in_sci_pdp)::text))))
where
(f.id is null) ),
data_set_k as (
select
a.geo,
a.subgeo,
a.country,
a.product_group,
a.brand,
a.product_name,
a.sm_number,
a.sm_description,
a.bucket,
a.source_code,
a.site_id,
a.factory,
a.pdp_init,
a.pdp_final,
a.version,
a.update_timestamp,
a.seller,
a.demand_type,
a.color,
a.mram,
a.mrom,
a.design_category,
a.subbrand,
a.batch_number,
a.xcvr,
a.pss_version,
a.npi_product_id,
a.npi_product_name as npi_product,
b.country as npi_country,
b.npi_region,
a.seller as npi_customer,
null::text as npi_color
from
(data_set_a_sub_h_sub_c a
join data_set_e_sub_f b on
((((a.npi_product_id)::text = (b.id)::text)
and ((a.product_name)::text = (b.product_name_in_sci_pdp)::text)
and ((a.country)::text = (b.country_code)::text)))) ) ,
v_smart_pss_pdp_customer as (
select
base_v1.geo,
base_v1.subgeo,
base_v1.country,
base_v1.product_group,
base_v1.brand,
base_v1.product_name,
base_v1.sm_number,
base_v1.sm_description,
base_v1.bucket,
base_v1.source_code,
base_v1.site_id,
base_v1.factory,
base_v1.pdp_init,
base_v1.pdp_final,
base_v1.version,
base_v1.update_timestamp,
base_v1.seller,
base_v1.demand_type,
base_v1.color,
base_v1.mram,
base_v1.mrom,
base_v1.design_category,
base_v1.subbrand,
base_v1.batch_number,
base_v1.xcvr,
base_v1.pss_version,
base_v1.npi_product_id,
base_v1.npi_product,
base_v1.npi_country,
base_v1.npi_region,
base_v1.npi_customer,
base_v1.npi_color
from
base_v1
where
(base_v1.npi_product_id is not null)
union all
select
data_set_h.geo,
data_set_h.subgeo,
data_set_h.country,
data_set_h.product_group,
data_set_h.brand,
data_set_h.product_name,
data_set_h.sm_number,
data_set_h.sm_description,
data_set_h.bucket,
data_set_h.source_code,
data_set_h.site_id,
data_set_h.factory,
data_set_h.pdp_init,
data_set_h.pdp_final,
data_set_h.version,
data_set_h.update_timestamp,
data_set_h.seller,
data_set_h.demand_type,
data_set_h.color,
data_set_h.mram,
data_set_h.mrom,
data_set_h.design_category,
data_set_h.subbrand,
data_set_h.batch_number,
data_set_h.xcvr,
data_set_h.pss_version,
data_set_h.npi_product_id,
data_set_h.npi_product,
data_set_h.npi_country,
data_set_h.npi_region,
data_set_h.npi_customer,
data_set_h.npi_color
from
data_set_h
where
(data_set_h.npi_product_id is not null)
union all
select
data_set_k.geo,
data_set_k.subgeo,
data_set_k.country,
data_set_k.product_group,
data_set_k.brand,
data_set_k.product_name,
data_set_k.sm_number,
data_set_k.sm_description,
data_set_k.bucket,
data_set_k.source_code,
data_set_k.site_id,
data_set_k.factory,
data_set_k.pdp_init,
data_set_k.pdp_final,
data_set_k.version,
data_set_k.update_timestamp,
data_set_k.seller,
data_set_k.demand_type,
data_set_k.color,
data_set_k.mram,
data_set_k.mrom,
data_set_k.design_category,
data_set_k.subbrand,
data_set_k.batch_number,
data_set_k.xcvr,
data_set_k.pss_version,
data_set_k.npi_product_id,
data_set_k.npi_product,
data_set_k.npi_country,
data_set_k.npi_region,
data_set_k.npi_customer,
data_set_k.npi_color
from
data_set_k
where
(data_set_k.npi_product_id is not null) ) ,
v_smart_pss_pdp_customer_app as(
select
*
from
v_smart_pss_pdp_customer
where
1 = 1 ) ,
mon_day as(
select
*
from
dim_pss_calendar as dpc
where
dpc.day_of_week = 1 ) ,
full_and_monday as (
select
dpc.date,
md.date as mon,
md.year,
md.quarter,
md.month,
md.week,
extract(day
from
md.date) as day
from
dim_pss_calendar as dpc
left join mon_day as md on
dpc.year = md.year
and dpc.week_start = md.week_start
and dpc.week = md.week
order by
date ) ,
sep as(
select
fad.year,
fad.quarter,
fad.month,
fad.week,
fad.mon as date,
fad.day ,
factory,
npi_product,
npi_region ,
pdp_final
from
v_smart_pss_pdp_customer_app as spp
left join full_and_monday as fad on
fad.date::date = bucket::date
order by
spp.factory,
bucket ) ,
tem as(
select
md.date,
tr.year,
tr.quarter,
tr.month,
tr.week,
day ,
factory,
npi_product,
npi_region ,
sum(pdp_final) as pdpFinal
from
sep as tr
left join mon_day as md on
md.year = tr.year
and md.week = tr.week
group by
factory,
npi_product,
npi_region ,
md.date,
tr.year,
tr.quarter,
tr.month,
tr.week,
day ) ,
t_complex as(
select
factory,npi_product as npi_product_name,
npi_region,
''::text as country,
''::text as npi_customer ,
json_agg(json_build_object('date',
date,
'year',
year,
'quarter',
quarter,
'month',
month,
'week',
week,
'day',
day,
'value',
pdpFinal)) as complex
from
tem
group by
factory,
npi_product,
npi_region
order by
factory,
npi_product,
npi_region )
select
tc.factory as factory,
case
when dfl.type = 'EMS' then true
else false
end as ems,
npi_product_name,
npi_region,
country,
npi_customer,
complex
from
t_complex as tc
left join dim_factory_list as dfl on
tc.factory = dfl.factory
order by
dfl.sort;
扫一扫
1683
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
