with ads_inventory_now as(
select
mat_products_1_category_name,
"在库" as inventory_type ,
snpsht_date,
last_reciv_date,
case when
DATEDIFF(snpsht_date,last_reciv_date) <=30 then "0~1月"
when DATEDIFF(snpsht_date,last_reciv_date) >30 and DATEDIFF(snpsht_date,last_reciv_date)<=90 then "1月~3月"
when DATEDIFF(snpsht_date,last_reciv_date) >90 and DATEDIFF(snpsht_date,last_reciv_date)<=180 then "3月~6月"
when DATEDIFF(snpsht_date,last_reciv_date) >180 and DATEDIFF(snpsht_date,last_reciv_date)<=365 then "6月~1年"
when DATEDIFF(snpsht_date,last_reciv_date) >365 and DATEDIFF(snpsht_date,last_reciv_date)<=730 then "1年~2年"
when DATEDIFF(snpsht_date,last_reciv_date) >730 and DATEDIFF(snpsht_date,last_reciv_date)<=1095 then "2年~3年"
else "其他" end dt_dis,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
product_category_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
pattern_vulgo_name,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
SUBSTRING(module_size_code, 1, LOCATE('×',module_size_code, LOCATE('×', module_size_code) + 1) - 1) module_size_code,
module_levl_code,
watt_per_pic_amt,
module_pattern_code,
inventory_age_qty,
case when '${单位}' = 'WPCS' then (umlme_pc_qty+bwesb_pc_qty)/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*(umlme_pc_qty+bwesb_pc_qty)/1000000
else (umlme_pc_qty+bwesb_pc_qty) end transit,
case when '${单位}' = 'WPCS' then full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*full_pc_qty/1000000
else full_pc_qty end full_num,
full_amt,
material_cost_amt,
case when '${单位}' = 'WPCS' then last_year_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*last_year_full_pc_qty/1000000
else last_year_full_pc_qty end last_year_full_pc_qty,
last_year_full_amt,
last_year_dead_amt,
last_year_inventory_turnover_days_qty,
last_year_material_cost_amt,
case when '${单位}' = 'WPCS' then last_half_year_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*last_half_year_full_pc_qty/1000000
else last_half_year_full_pc_qty end last_half_year_full_pc_qty,
last_half_year_full_amt,
last_half_year_dead_amt,
last_half_year_inventory_turnover_days_qty,
last_half_year_material_cost_amt,
case when '${单位}' = 'WPCS' then before_last_m_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*before_last_m_full_pc_qty/1000000
else before_last_m_full_pc_qty end before_last_m_full_pc_qty,
before_last_m_full_amt,
before_last_m_dead_amt,
before_last_m_inventory_turnover_days_qty,
before_last_m_material_cost_amt,
case when '${单位}' = 'WPCS' then last_m_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*last_m_full_pc_qty/1000000
else last_m_full_pc_qty end last_m_full_pc_qty,
last_m_full_amt,
last_m_dead_amt,
last_m_inventory_turnover_days_qty,
last_m_material_cost_amt,
case when '${单位}' = 'WPCS' then this_m_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*this_m_full_pc_qty/1000000
else this_m_full_pc_qty end this_m_full_pc_qty,
this_m_full_amt,
this_m_dead_amt,
this_m_inventory_turnover_days_qty,
this_m_material_cost_amt,
case when '${单位}' = 'WPCS' then last_7_d_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*last_7_d_full_pc_qty/1000000
else last_7_d_full_pc_qty end last_7_d_full_pc_qty,
last_7_d_full_amt,
last_7_d_dead_amt,
last_7_d_inventory_turnover_days_qty,
last_7_d_material_cost_amt,
case when '${单位}' = 'WPCS' then last_2_w_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*last_2_w_full_pc_qty/1000000
else last_2_w_full_pc_qty end last_2_w_full_pc_qty,
last_2_w_full_amt,
last_2_w_dead_amt,
last_2_w_inventory_turnover_days_qty,
last_2_w_material_cost_amt,
case when '${单位}' = 'WPCS' then last_3_w_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*last_3_w_full_pc_qty/1000000
else last_3_w_full_pc_qty end last_3_w_full_pc_qty,
last_3_w_full_amt,
last_3_w_dead_amt,
last_3_w_inventory_turnover_days_qty,
last_3_w_material_cost_amt,
case when '${单位}' = 'WPCS' then last_4_w_full_pc_qty/10000
when '${单位}' = 'MW' then ifnull(watt_per_pic_amt,0)*last_4_w_full_pc_qty/1000000
else last_4_w_full_pc_qty end last_4_w_full_pc_qty,
last_4_w_full_amt,
last_4_w_dead_amt,
last_4_w_inventory_turnover_days_qty,
last_4_w_material_cost_amt
from
dws_full_inventory_rgalfudy
where
snpsht_date between date_add(CURDATE(),interval -6 day) and CURDATE()
and mat_products_1_category_name in ("组件成品","电池片")
union all
select
mat_group_category_name,
"在途至客户" as inventory_type ,
dt,
"",
0,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
"",
base_name,
product_category_name,
"主流" as main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
pattern_vulgo_name,
is_oem_flag,
is_rd_flag,
"在途至客户",
"正常订单（在途）",
SUBSTRING(replace(module_size_code,"*","×"), 1, LOCATE('×',replace(module_size_code,"*","×"), LOCATE('×', replace(module_size_code,"*","×")) + 1) - 1) module_size_code,
module_levl_code,
ifnull(in_transit_inventory_watt_qty/in_transit_inventory_pc_qty,""),
module_pattern_code,
0,
0 ,
case when '${单位}' = 'WPCS' then in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then ifnull(in_transit_inventory_watt_qty,0)/1000000
else in_transit_inventory_pc_qty end full_num,
in_transit_inventory_amt,
0,
case when '${单位}' = 'PCS' then last_year_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_year_in_transit_inventory_watt_qty/1000000
else last_year_in_transit_inventory_pc_qty/10000 end last_year_in_transit_inventory_pc_qty,
last_year_in_transit_inventory_amt,
0 ,
0,
0,
case when '${单位}' = 'PCS' then last_half_year_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_half_year_in_transit_inventory_watt_qty/1000000
else last_half_year_in_transit_inventory_pc_qty/10000 end last_half_year_in_transit_inventory_pc_qty,
last_half_year_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then before_last_m_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then before_last_m_in_transit_inventory_watt_qty/1000000
else before_last_m_in_transit_inventory_pc_qty/10000 end before_last_m_in_transit_inventory_pc_qty,
before_last_m_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_m_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_m_in_transit_inventory_watt_qty/1000000
else last_m_in_transit_inventory_pc_qty/10000 end last_m_in_transit_inventory_pc_qty,
last_m_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then this_m_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then this_m_in_transit_inventory_watt_qty/1000000
else this_m_in_transit_inventory_pc_qty/10000 end this_m_in_transit_inventory_pc_qty,
this_m_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_7_d_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_7_d_in_transit_inventory_watt_qty/1000000
else last_7_d_in_transit_inventory_pc_qty/10000 end last_7_d_in_transit_inventory_pc_qty,
last_7_d_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_2_w_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_2_w_in_transit_inventory_watt_qty/1000000
else last_2_w_in_transit_inventory_pc_qty/10000 end last_2_w_in_transit_inventory_pc_qty,
last_2_w_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_3_w_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_3_w_in_transit_inventory_watt_qty/1000000
else last_3_w_in_transit_inventory_pc_qty/10000 end last_3_w_in_transit_inventory_pc_qty,
last_3_w_in_transit_inventory_amt,
0,
0,
0,
case when '${单位}' = 'PCS' then last_4_w_in_transit_inventory_pc_qty/10000
when '${单位}' = 'MW' then last_4_w_in_transit_inventory_watt_qty/1000000
else last_4_w_in_transit_inventory_pc_qty/10000 end last_4_w_in_transit_inventory_pc_qty,
last_4_w_in_transit_inventory_amt,
0,
0,
0
from
aiko_dm_isc.dwd_in_transit_inventory_rgalfudy
where
dt between date_add(CURDATE(),interval -6 day) and CURDATE()
and mat_group_category_name in ("组件成品")
union all
select
mat_group_category_name,
"在制" as inventory_type ,
dt,
"",
0,
faty_code,
0,
mat_code,
"",
"",
case when faty_code = 2000 then "义乌七厂"
when faty_code = 3000 then "天津"
when faty_code = 1000 then "佛山"
when faty_code = 8020 then "滁州"
when faty_code = 5000 then "珠海"
when faty_code = 8010 then "济南" else "其他" end base_name,
"在产在售",
"主流" ,
"常规" ,
"在制",
"在制",
"在制",
utility_distributed_name,
module_type_name,
module_type_code,
pattern_vulgo_name,
is_oem_flag,
is_rd_flag,
"在制",
"在制",
"在制",
"在制",
"",
module_pattern_code,
0,
0 ,
case when '${单位}' = 'WPCS' then work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else work_in_progress_pc_qty end full_num,
work_in_progress_amt,
case when '${库存类型}' = "在制" then raw_material_cost_amt else 0 end raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_year_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_year_work_in_progress_pc_qty/10000 end last_year_work_in_progress_pc_qty,
last_year_work_in_progress_amt,
0 ,
0,
case when '${库存类型}' = "在制" then last_year_raw_material_cost_amt else 0 end last_year_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_half_year_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_half_year_work_in_progress_pc_qty/10000 end last_half_year_work_in_progress_pc_qty,
last_half_year_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_half_year_raw_material_cost_amt else 0 end last_half_year_raw_material_cost_amt,
case when '${单位}' = 'PCS' then before_last_m_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else before_last_m_work_in_progress_pc_qty/10000 end before_last_m_work_in_progress_pc_qty,
before_last_m_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then before_last_m_raw_material_cost_amt else 0 end before_last_m_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_m_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_m_work_in_progress_pc_qty/10000 end last_m_work_in_progress_pc_qty,
last_m_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_m_raw_material_cost_amt else 0 end last_m_raw_material_cost_amt,
case when '${单位}' = 'PCS' then this_m_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else this_m_work_in_progress_pc_qty/10000 end this_m_work_in_progress_pc_qty,
this_m_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then this_m_raw_material_cost_amt else 0 end this_m_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_7_d_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_7_d_work_in_progress_pc_qty/10000 end last_7_d_work_in_progress_pc_qty,
last_7_d_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_7_d_raw_material_cost_amt else 0 end last_7_d_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_2_w_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_2_w_work_in_progress_pc_qty/10000 end last_2_w_work_in_progress_pc_qty,
last_2_w_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_2_w_raw_material_cost_amt else 0 end last_2_w_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_3_w_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_3_w_work_in_progress_pc_qty/10000 end last_3_w_work_in_progress_pc_qty,
last_3_w_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_3_w_raw_material_cost_amt else 0 end last_3_w_raw_material_cost_amt,
case when '${单位}' = 'PCS' then last_4_w_work_in_progress_pc_qty/10000
when '${单位}' = 'MW' then 0
else last_4_w_work_in_progress_pc_qty/10000 end last_4_w_work_in_progress_pc_qty,
last_4_w_work_in_progress_amt,
0,
0,
case when '${库存类型}' = "在制" then last_4_w_raw_material_cost_amt else 0 end last_4_w_raw_material_cost_amt
from
aiko_dm_isc.dws_work_in_progress_rgalfudy
where
dt between date_add(CURDATE(),interval -6 day) and CURDATE()
and mat_group_category_name = "组件成品"
),
aiko_wk as
(select aiko_week,CAST(SUBSTRING(aiko_week,3,4) as int) num from aiko_dm_qms.dim_date_dimension
where date_all between STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') and CURRENT_DATE()
group by
aiko_week
order by
num desc
limit 4),
ads_inventory_trend as (
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近七天" trend_lable,
DATE_FORMAT(snpsht_date, '%m/%d') trend_tm,
cast(concat(200,DATE_FORMAT(snpsht_date, '%m%d')) as int) trend_num,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
full_num,
full_amt,
material_cost_amt
from ads_inventory_now
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 offset 3),
121,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
last_4_w_full_pc_qty,
last_4_w_full_amt,
last_4_w_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 offset 2),
122,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
last_3_w_full_pc_qty,
last_3_w_full_amt,
last_3_w_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 offset 1),
123,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
last_2_w_full_pc_qty,
last_2_w_full_amt,
last_2_w_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近四周" trend_lable,
(select aiko_week from aiko_wk order by num desc limit 1 ),
124,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
last_7_d_full_pc_qty,
last_7_d_full_amt,
last_7_d_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近三月" trend_lable,
CONCAT(MONTH(CURRENT_DATE()),"月"),
4,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
this_m_full_pc_qty,
this_m_full_amt,
this_m_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近三月" trend_lable,
CONCAT(MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)),"月"),
3,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
last_m_full_pc_qty,
last_m_full_amt,
last_m_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近三月" trend_lable,
CONCAT(MONTH(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)),"月"),
2,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
before_last_m_full_pc_qty,
before_last_m_full_amt,
before_last_m_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近半年" trend_lable,
CONCAT(YEAR(CURRENT_DATE()), "上半年"),
1,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
last_half_year_full_pc_qty,
last_half_year_full_amt,
last_half_year_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
union all
select
mat_products_1_category_name,
last_reciv_date,
dt_dis,
inventory_type ,
"近一年" trend_lable,
CONCAT(YEAR(CURRENT_DATE())-1, "年"),
0,
product_category_name,
pattern_vulgo_name,
faty_code,
warhs_lgort_code,
mat_code,
long_desc_txt,
mat_batch_code,
base_name,
main_product_type_name,
is_regular_flag,
home_or_abroad_txt,
region_dept_name,
area_name,
utility_distributed_name,
module_type_name,
module_type_code,
module_size_code,
module_levl_code,
module_pattern_code,
watt_per_pic_amt,
is_oem_flag,
is_rd_flag,
is_shipped_flag,
is_shipp_domestic_overseas_flag,
inventory_age_qty,
transit,
last_year_full_pc_qty,
last_year_full_amt,
last_year_material_cost_amt
from ads_inventory_now
where snpsht_date = DATE_ADD(CURRENT_DATE(),interval -0 day)
)
select
mat_products_1_category_name as "资材类型",
last_reciv_date as "上次收货时间",
dt_dis as "库龄分布",
inventory_type as "在库/在途/在制",
product_category_name as "主推/库存",
pattern_vulgo_name as "版型俗称",
faty_code as "工厂编码",
warhs_lgort_code as "库位编码",
mat_code as "物料编码",
long_desc_txt as "物料描述",
mat_batch_code as "批次号",
base_name as "基地",
trend_lable as "时间趋势",
trend_tm as "趋势时间",
trend_num as "趋势排序",
main_product_type_name as "主流/非主流",
is_regular_flag as "常规/非常规",
case when is_regular_flag = "常规" then 25
when is_regular_flag = "非常规" then 8
else 0 end "组件周转目标",
home_or_abroad_txt as "海内外",
region_dept_name as "地域部",
area_name as "区域",
utility_distributed_name as "集中式/分布式",
module_type_name as "组件类型（单双玻）",
module_type_code as "组件型号",
module_size_code as "组件尺寸",
module_levl_code as "组件等级",
module_pattern_code as "组件版型",
watt_per_pic_amt as "组件功率",
is_oem_flag as "是否代工",
is_rd_flag as "是否研发",
is_shipped_flag as "是否可发库存",
is_shipp_domestic_overseas_flag as "是否可发海外",
inventory_age_qty as "库龄",
transit as "在途（单位）",
full_num as "全库存(单位)",
full_amt as "全库存(金额)",
material_cost_amt as "物料成本",
sum(case when mat_products_1_category_name = "组件成品" then full_amt else 0 end ) over (partition by inventory_type,trend_tm) as "单位时间总金额",
sum(material_cost_amt) over (partition by trend_tm) as "单位时间总成本",
sum(case when mat_products_1_category_name = "组件成品" then full_num else 0 end ) over (partition by inventory_type,trend_tm) as "单位时间总数量"
from
ads_inventory_trend