一，构建下表
构建上表的核心思路是：按照行与列进行分组
--- 按15天分组统计新增
with conn as( -- 人员会在部门之间调动,这里找到最新的 user与dept的关系
select user_id ,dep_id from (
select
user_id ,dep_id ,modify_time,
row_number() over(partition by user_id order by modify_time desc) as rk
from `mp-auth`.rbac_user_dep final where `_sign` = 1 and is_delete=0
)f where rk = 1
),zuser_org as (
select username,
mobile,
uid,
deptname_1, -- xx组
zdept1.name as deptname_2, -- xx部门
zdept2.name as deptname_3 -- xx中心
from (
select
case when zuser.username='oulingling' then '欧玲玲'
when zuser.username='梁瑶' then '梁美英'
when zuser.username='朱林' then '朱桃林'
when zuser.username='周亦婷' then '周婷'
when zuser.username='zhangcheng' then '张承'
when zuser.username='黄勇华1' then '黄勇华'
else zuser.username end as username,
zuser.mobile as mobile,
zuser.id as uid,
zdept.name as deptname_1,
zdept.id as id,
zdept.pid as pid,
zdept.dep_level
from
(select id,real_name as username,mobile,is_delete from `mp-auth`.rbac_user final where `_sign` = 1 and is_delete=0 and status != 0) zuser,conn,
(select id,name,pid,dep_level from `mp-auth`.rbac_department final where `_sign` = 1) zdept
where zuser.id=conn.user_id and conn.dep_id=zdept.id
)f
left join (select id,name,pid,user_count from `mp-auth`.rbac_department final where `_sign` = 1) zdept1 on f.pid=zdept1.id
left join (select id,name,pid from `mp-auth`.rbac_department final where `_sign` = 1) zdept2 on zdept1.pid=zdept2.id
),zuser as (
select uid,username,mobile,deptname_1 as deptname from zuser_org where deptname_1 like '%部'
union all
select uid,username,mobile,deptname_2 as deptname from zuser_org where deptname_2 like '%部'
),T as(
select
toStartOfMonth(now()) - INTERVAL 3 Month as f0_start_day,
toStartOfMonth(now()) - INTERVAL 3 Month + INTERVAL 14 Day as f0_mid_day,
date_sub(DAY, 1, toStartOfMonth(date_add(MONTH, -2, now()))) as f0_end_day,
toStartOfMonth(now()) - INTERVAL 2 Month as f1_start_day,
toStartOfMonth(now()) - INTERVAL 2 Month + INTERVAL 14 Day as f1_mid_day,
date_sub(DAY, 1, toStartOfMonth(date_add(MONTH, -1, now()))) as f1_end_day,
toStartOfMonth(now()) - INTERVAL 1 Month as f2_start_day,
toStartOfMonth(now()) - INTERVAL 1 Month + INTERVAL 14 Day as f2_mid_day,
date_sub(DAY, 1, toStartOfMonth(date_add(MONTH, 0, now()))) as f2_end_day,
toStartOfMonth(now()) as f3_start_day,
yesterday() as f3_end_day
),T1 as (
select class,fcreate_date,f0_start_day,f0_mid_day,f0_end_day,f1_start_day,f1_mid_day,
f1_end_day,f2_start_day,f2_mid_day,f2_end_day,f3_start_day,f3_end_day,salseman,
zur.deptname
from (
select fname,
case when c1_fdata_value like '%车联网卡%' then '车联网卡'
when c1_fdata_value like '%机器人%' then '机器人'
when c1_fdata_value like '%新能源%' then '新能源'
when c1_fdata_value like '%智能支付%' then '智能支付'
when c1_fdata_value like '%智能三表%' then '智能三表'
when c1_fdata_value like '%共享领域%' then '共享领域'
when c1_fdata_value like '%智慧农业%' then '智慧农业'
when c1_fdata_value like '%智能消防%' then '智能消防'
when c1_fdata_value like '%智能医疗%' then '智能医疗'
when c1_fdata_value like '%智慧教育%' then '智慧教育'
when c1_fdata_value like '%工业物联网%' then '工业物联网'
when c2_fdata_value like '%车联网卡%' then '车联网卡'
when c2_fdata_value like '%机器人%' then '机器人'
when c2_fdata_value like '%新能源%' then '新能源'
when c2_fdata_value like '%智能支付%' then '智能支付'
when c2_fdata_value like '%智能三表%' then '智能三表'
when c2_fdata_value like '%共享领域%' then '共享领域'
when c2_fdata_value like '%智慧农业%' then '智慧农业'
when c2_fdata_value like '%智能消防%' then '智能消防'
when c2_fdata_value like '%智能医疗%' then '智能医疗'
when c2_fdata_value like '%智慧教育%' then '智慧教育'
when c2_fdata_value like '%工业物联网%' then '工业物联网'
when c3_fdata_value like '%车联网卡%' then '车联网卡'
when c3_fdata_value like '%机器人%' then '机器人'
when c3_fdata_value like '%新能源%' then '新能源'
when c3_fdata_value like '%智能支付%' then '智能支付'
when c3_fdata_value like '%智能三表%' then '智能三表'
when c3_fdata_value like '%共享领域%' then '共享领域'
when c3_fdata_value like '%智慧农业%' then '智慧农业'
when c3_fdata_value like '%智能消防%' then '智能消防'
when c3_fdata_value like '%智能医疗%' then '智能医疗'
when c3_fdata_value like '%智慧教育%' then '智慧教育'
when c3_fdata_value like '%工业物联网%' then '工业物联网'
else '其他类' end as class,
fcreate_date,
f0_start_day,
f0_mid_day,
f0_end_day,
f1_start_day,
f1_mid_day,
f1_end_day,
f2_start_day,
f2_mid_day,
f2_end_day,
f3_start_day,
f3_end_day,
case when f_qfes_text11='核算管理部-黄欣' then '黄欣'
when f_qfes_text11='物联网采购部2-李荣荣' then '李荣荣'
when f_qfes_text11='核算组-应收（周妍）' then '周妍'
when f_qfes_text11='核算管理部-陈泽婷' then '陈泽婷'
else f_qfes_text11 end as salseman
from test_kingdee_bridge.v_kingdee_t_bd_customer230504 c,T t
)f left join zuser zur on f.salseman=zur.username
where 1=1 and zur.deptname='重客一部'
),R as(
select class,cdate,count(1) as cnt from(
select class,
case when fcreate_date between f0_start_day and f0_mid_day then concat(toString(f0_start_day),' ~ ',toString(f0_mid_day))
when fcreate_date between f0_mid_day and f0_end_day then concat(toString(f0_mid_day),' ~ ',toString(f0_end_day))
when fcreate_date between f1_start_day and f1_mid_day then concat(toString(f1_start_day),' ~ ',toString(f1_mid_day))
when fcreate_date between f1_mid_day and f1_end_day then concat(toString(f1_mid_day),' ~ ',toString(f1_end_day))
when fcreate_date between f2_start_day and f2_mid_day then concat(toString(f2_start_day),' ~ ',toString(f2_mid_day))
when fcreate_date between f2_mid_day and f2_end_day then concat(toString(f2_mid_day),' ~ ',toString(f2_end_day))
when fcreate_date between f3_start_day and f3_end_day then concat(toString(f3_start_day),' ~ ','昨天')
else '其他日期'
end as cdate
from T1
)f group by class,cdate -- 按照表的行与列进行分组
),Rt as (
select cdate,
sum(v1) as "机器人",
sum(v2) as "新能源",
sum(v3) as "智能支付",
sum(v4) as "智能三表",
sum(v5) as "共享领域",
sum(v6) as "智慧农业",
sum(v7) as "智能消防",
sum(v8) as "智能医疗",
sum(v9) as "智慧教育",
sum(v10) as "工业物联网",
(sum(v1)+sum(v2)+sum(v3)+sum(v4)+sum(v5)+sum(v6)+sum(v7)+sum(v8)+sum(v9)+sum(v10)) as "总和"
from(
select cdate,
if(class='机器人',cnt,0) as v1,
if(class='新能源',cnt,0) as v2,
if(class='智能支付',cnt,0) as v3,
if(class='智能三表',cnt,0) as v4,
if(class='共享领域',cnt,0) as v5,
if(class='智慧农业',cnt,0) as v6,
if(class='智能消防',cnt,0) as v7,
if(class='智能医疗',cnt,0) as v8,
if(class='智慧教育',cnt,0) as v9,
if(class='工业物联网',cnt,0) as v10
from R where cdate !='其他日期'
)k group by cdate order by cdate
)
select * from Rt
union all
select '总和' as cdae,sum("机器人"),sum("新能源"),sum("智能支付"),sum("智能三表"),sum("共享领域"),sum("智慧农业"),sum("智能消防"),sum("智能医疗"),sum("智慧教育"),sum("工业物联网"),sum("总和") from Rt
二，转换下表
A
===转换=->B
核心思路是：将下一行日期拉取到上一行，构成一个范围 [a,b]，left join一个连续的时间维表，使得数据膨胀，最后在用范围去做行过滤
--- tst测试
with a as (
select cast('2023-01-01' as date) as dates
union all
select cast('2023-02-01' as date) as dates
union all
select cast('2023-03-01' as date) as dates
union all
select cast('2023-04-01' as date) as dates
union all
select cast('2023-05-01' as date) as dates
union all
select cast('2023-06-01' as date) as dates
union all
select cast('2023-07-01' as date) as dates
union all
select cast('2023-08-01' as date) as dates
union all
select cast('2023-09-01' as date) as dates
union all
select cast('2023-10-01' as date) as dates
union all
select cast('2023-11-01' as date) as dates
),b as (
select '宝马' as name, cast('2023-01-01' as date) as cdate,13 as cnt
union all
select '宝马' as name, cast('2023-04-01' as date) as cdate,17 as cnt
union all
select '宝马' as name, cast('2023-09-01' as date) as cdate,51 as cnt
)
select name,cnt,stime,K.etime,a.dates
from
(
select `name`,cnt,cdate as stime,
case when max(cdate)over(partition by `name` order by cdate rows between 1 following and 1 following)=cast('1970-01-01' as date) then cast('2023-12-01' as date)
else cast(max(cdate)over(partition by `name` order by cdate rows between 1 following and 1 following) as date)
end as etime
from b
)K
left join a on 1=1
where a.dates>=stime and a.dates<etime --and ifnull(b.t1='1970-01-01',cast(now() as date),b.t1)<=a.dates
order by `name`,a.dates
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
