(1)统计gitlab每个开发一年内每个月提交代码数据：
-- commit 次数统计排名 select author_id,b.email,b.name,count(1) as sum_cnt , sum(case when to_char(a.created_at,'yyyy-mm')='2020-01' then 1 end) as "1mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-02' then 1 end) as "2mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-03' then 1 end) as "3mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-04' then 1 end) as "4mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-05' then 1 end) as "5mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-06' then 1 end) as "6mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-07' then 1 end) as "7mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-08' then 1 end) as "8mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-09' then 1 end) as "9mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-10' then 1 end) as "10mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-11' then 1 end) as "11mon", sum(case when to_char(a.created_at,'yyyy-mm')='2020-12' then 1 end) as "12mon" from events a left join users b on a.author_id=b.id where a.created_at>='2020-01-01' and b.state='active' and a.action=5 group by 1,2,3 order by 4 desc
(1)统计gitlab每个开发一年内24小时提交代码数据：
-- 时间活跃区间 排名
select author_id,b.email,b.name,count(1) as sum_cnt,
coalesce(sum(case when extract(hour from a.created_at) = '01' then 1 end),0) as "1h",
coalesce(sum(case when extract(hour from a.created_at) = '02' then 1 end),0) as "2h",
coalesce(sum(case when extract(hour from a.created_at) = '03' then 1 end),0) as "3h",
coalesce(sum(case when extract(hour from a.created_at) = '04' then 1 end),0) as "4h",
coalesce(sum(case when extract(hour from a.created_at) = '05' then 1 end),0) as "5h",
coalesce(sum(case when extract(hour from a.created_at) = '06' then 1 end),0) as "6h",
coalesce(sum(case when extract(hour from a.created_at) = '07' then 1 end),0) as "7h",
coalesce(sum(case when extract(hour from a.created_at) = '08' then 1 end),0) as "8h",
coalesce(sum(case when extract(hour from a.created_at) = '09' then 1 end),0) as "9h",
coalesce(sum(case when extract(hour from a.created_at) = '10' then 1 end),0) as "10h",
coalesce(sum(case when extract(hour from a.created_at) = '11' then 1 end),0) as "11h",
coalesce(sum(case when extract(hour from a.created_at) = '12' then 1 end),0) as "12h",
coalesce(sum(case when extract(hour from a.created_at) = '13' then 1 end),0) as "13h",
coalesce(sum(case when extract(hour from a.created_at) = '14' then 1 end),0) as "14h",
coalesce(sum(case when extract(hour from a.created_at) = '15' then 1 end),0) as "15h",
coalesce(sum(case when extract(hour from a.created_at) = '16' then 1 end),0) as "16h",
coalesce(sum(case when extract(hour from a.created_at) = '17' then 1 end),0) as "17h",
coalesce(sum(case when extract(hour from a.created_at) = '18' then 1 end),0) as "18h",
coalesce(sum(case when extract(hour from a.created_at) = '19' then 1 end),0) as "19h",
coalesce(sum(case when extract(hour from a.created_at) = '20' then 1 end),0) as "20h",
coalesce(sum(case when extract(hour from a.created_at) = '21' then 1 end),0) as "21h",
coalesce(sum(case when extract(hour from a.created_at) = '22' then 1 end),0) as "22h",
coalesce(sum(case when extract(hour from a.created_at) = '23' then 1 end),0) as "23h",
coalesce(sum(case when extract(hour from a.created_at) = '24' then 1 end),0) as "24h"
from events a left join users b on a.author_id=b.id
where a.created_at>='2020-01-01' and b.state='active' and a.action=5
group by 1,2,3 order by 4 desc
(3)gitlab统计每个开发提交次数最多小时是每天的哪个小时
-- 每个开发提交最多小时的是每天的几点 select ww.name,ww.email,ww.h_time,ww.cnt from (select wa.email,wa.name,wa.h_time,wa.cnt, ROW_NUMBER() OVER( partition by wa.email,wa.name ORDER BY cnt desc ) as rnk from (select b.email,b.name,extract(hour from a.created_at) as h_time,count(1) as cnt from events a left join users b on a.author_id=b.id where a.created_at>='2020-01-01' and b.state='active' and a.action=5 group by 1,2,3)wa ) ww where ww.rnk=1