复杂BI报表SQL

发布于：2025-08-30 ⋅ 阅读:(19) ⋅ 点赞:(0)

复杂SQL

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

-- 西宁硅料三期
with b as (
select
    (row_number() OVER(PARTITION BY t1.tool ORDER BY t1.tool ) - 1) AS help_topic_id
from
    ((
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool) t1
join (
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool) t2)),
tmp as (
select
    a.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 (
select
    tmp.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 (
select
    tmp_2.id AS id,
    count(distinct tmp_2.exp_person) AS person_cnt
from
    tmp_2
group by
    tmp_2.id
),
final as (
select
    t1.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)))
)
select
    REPLACE(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
    ((
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool) t1
join (
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool) t2)
),
tmp as (
select
    a.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 (
select
    tmp.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 (
select
    tmp_2.id AS id,
    count(distinct tmp_2.exp_person) AS person_cnt
from
    tmp_2
group by
    tmp_2.id
),
final as (
select
    t1.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)))
)
select
    REPLACE(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
    ((
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool) t1
join (
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool
union all
    select
        1 AS tool) t2)
),
tmp as (
select
    a.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
from
    xdap_app_339816648452079617.view_438304971734122496 a
left join xdap_app_339816648452079617.view_453948802894659584 b on
    replace(replace(a.widget_453960078530183168, '["', ''), '"]', '') = b.id
left join xdap_app_339816648452079617.glcl_gx gg on
    replace(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 (
select
    tmp.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 (
select
    tmp_2.id AS id,
    count(distinct tmp_2.exp_person) AS person_cnt
from
    tmp_2
group by
    tmp_2.id
),
final as (
select
    t1.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
from
    tmp_2 t1
left join tmp_3 t2 on
    t1.id = t2.id
)
select
	REPLACE(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
from
    final t1
left join xdap_app_339816648452079617.xdap_users t2 on
    t1.exp_person = t2.id

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布