pqsql 怎么生成间隔类数据集合

最新推荐文章于 2024-11-28 16:21:41 发布

原创最新推荐文章于 2024-11-28 16:21:41 发布 · 597 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#sql

pgsql 专栏收录该内容

2 篇文章

订阅专栏

在日常场景中，经常会有间隔类的统计场景，如充值区间、等级区间。当原始数据缺省时，往往需要程序再次补充。那么pqsql协议的数据库或者数仓有么有好的解决方案呢

前提知识点

集合返回函数:generate_series

此函数主要用来升级区间数据的，结果是返回区间集合。如：generate_series(start, stop)

select * from generate_series(2,50)

-- 返回结果
generate_series
-----------------
               2
               3
               ...
               49
               50
(49 rows)

可复用临时公共查询:cte

支持cte的查询可以服用查询结果，也就是带with关键字的子查询

with
tmp_query_name [(column1,...)] as (
业务query，如：
select 
    user_id
from
    user_map
where
    reg_date = 20241025
)[,tmp_query_name2 as (...)]
select
    count(1)
    count(tmp_query_name.user_id)
from
    tmp_query_name 
left join login_log on 
    tmp_query_name.user_id = login_log.user_id

在CTE范围内，后面定义的CTE可以引用前面定义的的表，实现复用。也可以实现sql规范中的以小集合牵动大集合（以小击大，四两千斤），手动定义最优查询路径

使用场景

generate_series(start, stop)

生成start到stop的步长1的集合

generate_series(start, stop,step)

生成start到stop的步长为step值的集合，注意step可以为负数

generate_series(start, stop,step interval)

生成start到stop的步长为step值的集合，注意step时间区间

select 
    *
from
generate_series('2025-10-01 00:00'::timestamp,
'2025-10-24 12:00'::timestamp,
'10 hours'
)

此时start和stop类型为TIMESTAMP或TIMESTAMP WITH TIME ZONE

每5分钟一次的登录统计

先用CTE生成时间间隔，防止缺省情况，然后常规操作即可

with
time_range(ts) as (
select 
    *
from
generate_series('2025-10-24 00:00'::timestamp,now(),'5 minutes')
)
select
    ts,
    count(1) as times,
    count(distinct user_id)
from
    time_range left join user_login_log ull 
    on ull.login_ts >= time_range.ts and ull.login_ts < time_range.ts + interval '5 minutes'
where login_date = 20251024
group by ts
order by ts

至此非缺省的时间间隔生成完毕