复杂SQL

一行多个人员，平均瓜分总产量。

-- 西宁硅料三期
with b as (
select(row_number() OVER(PARTITION BY t1.tool ORDER BY t1.tool ) - 1) AS help_topic_id
from((select1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool) t1
join (select1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool) t2)),
tmp as (
selecta.id AS id,a.widget_456031860032536576 AS date_time,gg.gx AS gx,b.widget_453948803158900736 AS gy,a.widget_456031860032536580 AS dj,a.widget_456031860032536584 AS weight,regexp_replace(if((a.widget_456031860032536582 = '[]'), concat_ws(',', a.user_laowu1, a.user_laowu2, a.lwname3, a.lwname4, a.lwname5), concat_ws(',', a.widget_456031860032536582, a.user_laowu1, a.user_laowu2, a.lwname3, a.lwname4, a.lwname5)), '\\[|\\]|"', '') AS arr_person,a.creation_date AS tp,a.widget_456031860032536583 AS pc
from((xdap_app_339816648452079617.view_456031859625689088 a
left join xdap_app_339816648452079617.view_453948802894659584 b on((replace(replace(a.widget_456031860032536579, '["', ''), '"]', '') = b.id)))
left join xdap_app_339816648452079617.glcl_gx gg on((replace(replace(a.widget_456031860032536578, '["', ''), '"]', '') = gg.id)))
WHERE a.widget_456031860032536576 >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01 08:30:00'), INTERVAL 1 MONTH) and a.widget_456031860032536576 < DATE_FORMAT(NOW(), '%Y-%m-01 08:30:00')
),
tmp_2 as (
selecttmp.id AS id,tmp.date_time AS date_time,tmp.gx AS gx,tmp.gy AS gy,tmp.dj AS dj,tmp.weight AS weight,tmp.arr_person AS arr_person,substring_index(substring_index(tmp.arr_person, ',',(b.help_topic_id + 1)), ',',-(1)) AS exp_person,tmp.tp AS tp,tmp.pc as pc
from(tmp
join b)
where(b.help_topic_id < ((length(tmp.arr_person) - length(replace(tmp.arr_person, ',', ''))) + 1))
),
tmp_3 as (
selecttmp_2.id AS id,count(distinct tmp_2.exp_person) AS person_cnt
fromtmp_2
group bytmp_2.id
),
final as (
selectt1.id AS id,t1.date_time AS date_time,t1.gx AS gx,t1.gy AS gy,t1.dj AS dj,t1.weight AS weight,t1.arr_person AS arr_person,t1.exp_person AS exp_person,t2.person_cnt AS person_cnt,TRUNCATE(t1.weight / t2.person_cnt, 2) AS yield,t1.tp AS tp,t1.pc AS pc
from(tmp_2 t1
left join tmp_3 t2 on((t1.id = t2.id)))
)
selectREPLACE(UUID() , '-', '') as bi_id,'西宁' as bi_base,'三期' as bi_period,'硅料' as bi_type,t1.gx AS bi_gx,t1.gy AS bi_gy,t1.dj AS bi_dj,t2.user_number AS bi_gh,ifnull(t2.username, t1.exp_person) AS bi_xm,t1.yield AS bi_yield,t1.tp AS date_time,CONCAT('批次: ',t1.pc) AS bi_remark,t1.date_time AS bi_datetime,DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m') as bi_month
from(final t1
left join xdap_app_339816648452079617.xdap_users t2 on((t1.exp_person = t2.id)))

西宁硅料二期

with b as (
select(row_number() OVER(PARTITION BY t1.tool ORDER BY t1.tool)-1) AS help_topic_id
from((select1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool) t1
join (select1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool) t2)
),
tmp as (
selecta.id AS id,a.widget_446956575546933248 AS date_time,gg.gx AS gx,b.widget_453948803158900736 AS gy,a.widget_453973982488559617 AS dj,a.widget_446956575546933254 AS weight,regexp_replace(if(((a.widget_446956575546933252 is null) or (a.widget_446956575546933252 = '[]')), concat_ws(',', a.widget_555350966648438784, a.widget_555350966648438785, a.widget_596761283412361216, a.widget_596761283412361217, a.widget_596761283412361218), concat_ws(',', a.widget_446956575546933252, a.widget_555350966648438784, a.widget_555350966648438785, a.widget_596761283412361216, a.widget_596761283412361217, a.widget_596761283412361218)), '\\[|\\]|"', '') AS arr_person,a.creation_date AS tp,a.widget_446956575546933253 AS pc
from((xdap_app_339816648452079617.view_446956575110725632 a
left join xdap_app_339816648452079617.view_453948802894659584 b on((replace(replace(a.widget_453973982488559616, '["', ''), '"]', '') = b.id)))
left join xdap_app_339816648452079617.glcl_gx gg on((replace(replace(a.widget_454284003264954368, '["', ''), '"]', '') = gg.id)))
WHERE a.widget_446956575546933248 >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01 08:30:00'), INTERVAL 1 MONTH) and a.widget_446956575546933248 < DATE_FORMAT(NOW(), '%Y-%m-01 08:30:00')
),
tmp_2 as (
selecttmp.id AS id,tmp.date_time AS date_time,tmp.gx AS gx,tmp.gy AS gy,tmp.dj AS dj,tmp.weight AS weight,tmp.arr_person AS arr_person,substring_index(substring_index(tmp.arr_person, ',',(b.help_topic_id + 1)), ',',-(1)) AS exp_person,tmp.tp AS tp,tmp.pc AS pc
from(tmp
join b)
where(b.help_topic_id < ((length(tmp.arr_person) - length(replace(tmp.arr_person, ',', ''))) + 1))
),
tmp_3 as (
selecttmp_2.id AS id,count(distinct tmp_2.exp_person) AS person_cnt
fromtmp_2
group bytmp_2.id
),
final as (
selectt1.id AS id,t1.date_time AS date_time,t1.gx AS gx,t1.gy AS gy,t1.dj AS dj,t1.arr_person AS arr_person,t1.exp_person AS exp_person,t2.person_cnt AS person_cnt,TRUNCATE(t1.weight / t2.person_cnt, 2) AS yield,t1.tp AS tp,t1.pc AS pc
from(tmp_2 t1
left join tmp_3 t2 on((t1.id = t2.id)))
)
selectREPLACE(UUID() , '-', '') as bi_id,'西宁' as bi_base,'二期' as bi_period,'硅料' as bi_type,t1.gx AS bi_gx,t1.gy AS bi_gy,t1.dj AS bi_dj,t2.user_number AS bi_gh,ifnull(t2.username, t1.exp_person) AS bi_xm,t1.yield AS bi_yield,t1.tp AS date_time,CONCAT('批次: ', t1.pc) AS bi_remark,t1.date_time AS bi_datetime,DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m') as bi_month
from(final t1
left join xdap_app_339816648452079617.xdap_users t2 on((t1.exp_person = t2.id)))

西宁硅料一期

with b as (
select(row_number() OVER(PARTITION BY t1.tool ORDER BY t1.tool) - 1) AS help_topic_id
from((select1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool) t1
join (select1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool
union allselect1 AS tool) t2)
),
tmp as (
selecta.id AS id,a.widget_438304971969003520 AS date_time,gg.gx AS gx,b.widget_453948803158900736 AS gy,a.widget_453888051886686208 AS dj,a.widget_445894624578371584 AS weight,regexp_replace(if((a.widget_443337882443186176 = '[]'),regexp_replace(concat_ws(',', a.widget_535134057806168064, a.widget_535135291673935872, a.widget_538021294705213440, a.widget_538021294705213441, a.widget_538021294705213442), '\\[|\\]|"', ''),concat_ws(',', a.widget_443337882443186176, a.widget_535134057806168064, a.widget_535135291673935872, a.widget_538021294705213440, a.widget_538021294705213441, a.widget_538021294705213442)), '\\[|\\]|"', '') AS arr_person,a.widget_445891041954889728 AS pc,a.creation_date AS tp
fromxdap_app_339816648452079617.view_438304971734122496 a
left join xdap_app_339816648452079617.view_453948802894659584 b onreplace(replace(a.widget_453960078530183168, '["', ''), '"]', '') = b.id
left join xdap_app_339816648452079617.glcl_gx gg onreplace(replace(a.widget_454231553468268544, '["', ''), '"]', '') = gg.id
WHERE a.widget_438304971969003520 >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01 08:30:00'), INTERVAL 1 MONTH) and a.widget_438304971969003520 < DATE_FORMAT(NOW(), '%Y-%m-01 08:30:00')
),
tmp_2 as (
selecttmp.id AS id,tmp.date_time AS date_time,tmp.gx AS gx,tmp.gy AS gy,tmp.dj AS dj,tmp.weight AS weight,tmp.arr_person AS arr_person,substring_index(substring_index(tmp.arr_person, ',',(b.help_topic_id + 1)), ',',-(1)) AS exp_person,tmp.pc AS pc,tmp.tp AS tp
from
(tmp
join b)
where(b.help_topic_id < ((length(tmp.arr_person) - length(replace(tmp.arr_person, ',', ''))) + 1))
),
tmp_3 as (
selecttmp_2.id AS id,count(distinct tmp_2.exp_person) AS person_cnt
fromtmp_2
group bytmp_2.id
),
final as (
selectt1.id AS id,t1.date_time AS date_time,t1.gx AS gx,t1.gy AS gy,t1.dj AS dj,t1.weight AS weight,t1.arr_person AS arr_person,t1.exp_person AS exp_person,t2.person_cnt AS person_cnt,TRUNCATE(t1.weight / t2.person_cnt, 2) AS yield,t1.pc as pc,t1.tp AS tp
fromtmp_2 t1
left join tmp_3 t2 ont1.id = t2.id
)
selectREPLACE(UUID() , '-', '') as bi_id,'西宁' as bi_base,'一期' as bi_period,'硅料' as bi_type,t1.date_time AS bi_datetime,t1.gx AS bi_gx,t1.gy AS bi_gy,t1.dj AS bi_dj,t1.weight AS weight,t2.user_number AS bi_gh,ifnull(t2.username, t1.exp_person) AS bi_xm,t1.yield AS bi_yield,CONCAT('批次: ',t1.pc) AS bi_remark,t1.tp AS date_time,DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m') as bi_month
fromfinal t1
left join xdap_app_339816648452079617.xdap_users t2 ont1.exp_person = t2.id