感谢提出的方法https://juejin.cn/post/6995003591807221767
场景:
要根据数据查出每月的统计数据。
思路:
1、创建一个临时表,里面存放月份
select from_unixtime(unix_timestamp( date_trunc('month',date_trunc('year',now())) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 1 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 2 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 3 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 4 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 5 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 6 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 7 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 8 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 9 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 10 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 11 month)) ),'yyyyMM') as tm
注:这个算是比较复杂的一种写法,每个月是计算出来的。也可以直接写1、2、3月,但是做关联的时候就要多加一个今年的判断条件
简单写法:
SELECT * from(
select 1 as mon
union all
select 2 as mon
union all
select 3 as mon
union all
select 4 as mon
union all
select 5 as mon
union all
select 6 as mon
union all
select 7 as mon
union all
select 8 as mon
union all
select 9 as mon
union all
select 10 as mon
union all
select 11 as mon
union all
select 12 as mon
) a
left join
(select *
from c
where YEAR(tm)=YEAR (NOW())
GROUP BY mon) b on b.mon=a.mon
2、然后将数据与临时表做乘积,将每一条数据都分成12份。
select
*
from
(
select from_unixtime(unix_timestamp( date_trunc('month',now()) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 1 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 2 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 3 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 4 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 5 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 6 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 7 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 8 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 9 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 10 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 11 month)) ),'yyyyMM') as tm
) a
left join cntrct b on 1=1
3、最后再将数据根据月份左关联起来
select
*
from
(
select from_unixtime(unix_timestamp( date_trunc('month',now()) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 1 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 2 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 3 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 4 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 5 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 6 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 7 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 8 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 9 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 10 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 11 month)) ),'yyyyMM') as tm
) a
left join ods_pro.t_ods_cm_cntrct b on 1=1
left join (
select cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM') tm,sum(cast (mny as DOUBLE )) mny from cntrct_stts
where typ = '3'
group by cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM')
) c on b.id = c.cntrct_id and a.tm = c.tm
4、将多列转换成一行
select
b.id,
ifnull(max(case when a.tm like '%01' then c.mny end),0) Jan,
ifnull(max(case when a.tm like '%02' then c.mny end),0) Feb,
ifnull(max(case when a.tm like '%03' then c.mny end),0) Mar,
ifnull(max(case when a.tm like '%04' then c.mny end),0) Apr,
ifnull(max(case when a.tm like '%05' then c.mny end),0) May,
ifnull(max(case when a.tm like '%06' then c.mny end),0) Jun,
ifnull(max(case when a.tm like '%07' then c.mny end),0) Jul,
ifnull(max(case when a.tm like '%08' then c.mny end),0) Aug,
ifnull(max(case when a.tm like '%09' then c.mny end),0) Sep,
ifnull(max(case when a.tm like '%10' then c.mny end),0) Oct,
ifnull(max(case when a.tm like '%11' then c.mny end),0) Nov,
ifnull(max(case when a.tm like '%12' then c.mny end),0) `Dec`
from
(
select from_unixtime(unix_timestamp( date_trunc('month',now()) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 1 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 2 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 3 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 4 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 5 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 6 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 7 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 8 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 9 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 10 month)) ),'yyyyMM') as tm
union all
select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 11 month)) ),'yyyyMM') as tm
) a
left join ods_pro.t_ods_cm_cntrct b on 1=1
left join (
select cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM') tm,sum(cast (mny as DOUBLE )) mny from cntrct_stts
where typ = '3'
group by cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM')
) c on b.id = c.cntrct_id and a.tm = c.tm
group by b.id