通过基于pgsql的timescaleDB的time_bucket函数实现自定义聚合粒度

1、自己写的不完全满足要求的实现方式

with tb_tmp as (
        select 
                *, 
                 //计算该时间距离第一天有多少天
                ((extract(epoch from create_time) /3600/24)::integer) as ct_i
        from test.test_salary
)

select 
        min(a.create_time) as create_time,
        sum(a.salary)
from (
        select 
                *,
                 //移动数据使得3条数据为一组
                 (ct_i - (select min(ct_i) % 3  from tb_tmp))/3   as circle
        from  tb_tmp
) a
group by a.circle
order by create_time;

with tb_tmp as (
        select 
                *, 
                extract (week from age(create_time, '1970-01-01')) as ct_i
        from test.test_salary
)

select 
        min(a.create_time) as create_time,
        sum(a.salary)
from (
        select 
                *,
                 (ct_i - (select min(ct_i::integer) % 3  from tb_tmp))/3   as circle
        from  tb_tmp
) a
group by a.circle
order by create_time;

select extract (year from timestamp age('2070-02-01', '1970-01-01'));

select age('2070-02-01'::date)

//年
        select 
                *, 
                extract (year from age(create_time, '1970-01-01')) as ct_i
        from test.test_salary;
//月
        select 
                *, 
                (extract (year from age(create_time, '1970-01-01')) - 1) * 12 + extract(month from create_time) -1 as ct_i
        from test_salary ;
       
       
//周
        select 
                *, 
                ((extract(epoch from create_time) /3600/24/7)::integer) as ct_i
        from test_salary;
       
//季度
        select 
                *, 
                extract (year from age(create_time, '1970-01-01')) * 4 + extract(quarter from create_time) as ct_i
        from test_salary;
        
 //日       
        select 
                *, 
                ((extract(epoch from create_time) /3600/24)::integer) as ct_i
        from test_salary;

2、第二种不完全实现方法，主要通过generate_series方法生成序列加上关联业务表实现自定义分组聚合

with ranges as (
  select tt.ss, tt.ee from (
    select 
        the_time as ss, 
        lead(t.the_time, 1) over (order by t.the_time) as ee from (
        select * from generate_series
                ( '2020-01-01 00:00:00'::timestamp
                , '2024-01-01 00:00:00'::timestamp
                , '600 second'::interval) as the_time
        ) t
  ) tt
)
select r.ss,r.ee,department , sum(salary)
from ranges r
left join test.test_salary
t on t.create_time >= r.ss and t.create_time < r.ee
group by r.ss,r.ee,t.department 
order by r.ss,r.ee,t.department ;

3、通过time_bucket函数实现随意自定义的分组聚合，如n年，n季度，n月，n周，n天，n小时，n分钟，n秒，以及更复杂的每天的几点到几点，每周的周几到周几，每月的几号到本月或下月的几号

1）、按3天聚合，并且从指定的时间开始，默认是按照自然年，自然月，自然周的起始点开始的，如果不需要指定开始时间去掉第三个参数即可

select time_bucket('3 day', create_time, '2020-01-05'::timestamp) as tb,sum(salary) as salary from test.test_salary 
where create_time >= '2020-01-05' and create_time  <='2020-03-01'
group by tb
order by tb asc

2）、实现每天9点到18点分组聚合

select department , create_time , time_bucket('1 day', create_time) + '9 hour'  as tb,time_bucket('1 day', create_time) + '18 hour'  as tb,  salary from test.test_salary 
where create_time  < '2020-01-10' and extract(epoch from create_time::timestamp::time) >= 32400
and extract(epoch from create_time::timestamp::time) < 64800
order by create_time;

3）、实现每周2到周五分组聚合

select  time_bucket('1 week', create_time) + '1 day' as tb,time_bucket('1 week', create_time) + '4 day' as tb2 , sum(salary) 
from test.test_salary 
where create_time >= '2020-01-01' and create_time  <='2020-05-01' and extract(dow from create_time::timestamp) >= 2 and extract(dow from create_time::timestamp) <=5
group by tb,tb2
order by tb,tb2  asc

4）、实现本月2号到18号分组聚合

select tb1,sum(salary) from 
        (select time_bucket('1 month', create_time)+ '1 day'  tb1, create_time, salary 
        from test.test_salary
        where create_time  < '2021-01-01' and date_part('day',create_time) >= 2  and date_part('day',create_time) <=18  order by create_time) t
group by tb1;

5）、实现本月18号到下月10分组聚合

select tb1,sum(salary) from (
        (select time_bucket('1 month', create_time) + '17 day'  tb1, create_time, salary 
        from test.test_salary
        where create_time  < '2021-01-01' and date_part('day',create_time) >= 18 order by create_time)
        
        union all
        
        (select time_bucket('1 month', create_time) + '-1 month 17 day'   tb1, create_time, salary 
        from test.test_salary
        where create_time  < '2021-01-01' and  date_part('day', create_time) <= 10)
        order by tb1, create_time asc
) t
group by tb1;

4、在我们使用time_bucket方法进行分组聚合获取数据时，我们会发现个问题，可能由于咱们的业务表中的数据并不是每个周期都有数据，比如咱们按月分组，但表中的数据没有2023-02的数据，那查询结果就直接没有2023-02这条数据，但有时我们的业务又需要补齐这条没有的数据，就2023-02的数据虽然是0但必须得有。这个时候另外一个方法就派上用场了。

time_bucket_gapfill：该方法可以补齐没有的数据，但它也有它的局限性，

第一：如果使用该方法，那么时间字段比如有where条件，也就是必须明确数据范围，也很好理解，如果明确，它也不知道该补齐哪些数据；

第二：该方法如果指定第三个参数，也就是指定从哪个时间开始分组的话不生效，比如咱们指定从02-02开始2天一个分组，那么02-02和02-03应该是一个组，02-04和02-05一个组，但这个方法还是会让02-01和02-02一个组不满足业务需求；

第三：该方法不支持增加一个时间间隔(interval)，也就是上面sql中用到的time_bucket('1 month', create_time) + '-1 month 17 day'中的 + '-1 month 17 day'。