时间戳

首页 > 解决方案 > PostgreSQL - 获取按日期时间列分组的表中 N 个间隔的项目计数

sql - PostgreSQL - 获取按日期时间列分组的表中 N 个间隔的项目计数

问题描述

我有一个用户表，其中有以下字段。

| id | created_at | username |

我想过滤这个表，以便我可以获得在日期时间范围内创建的用户数，分成 N 个间隔。例如，对于created_at介于两者之间2019-01-01T00:00:00并2019-01-02T00:00:00分成 2 个间隔的用户，我会得到类似的东西。

_______________________________
|       dt            | count |
-------------------------------
| 2019-01-01T00:00:00 |   6   |
| 2019-01-01T12:00:00 |   7   |
-------------------------------

是否可以一击完成？我目前正在使用我的 Django ORM 创建 N 个日期范围，然后进行 N 个查询，这不是很有效。

标签： sqldatabasepostgresql

解决方案

生成您想要的时间，然后使用left join和聚合：

select gs.ts, count(u.id)
from generate_series('2019-01-01T00:00:00'::timestamp,
                     '2019-01-01T12:00:00'::timestamp,
                     interval '12 hour'
                    ) gs(ts) left join
     users u
     on u.created_at >= gs.ts and
        u.created_at < gs.ts + interval '12 hour'
group by 1
order by 1;

编辑：

如果要指定行数，可以使用类似的东西：

from generate_series(1, 10, 1) as gs(n) cross join lateral
     (values ('2019-01-01T00:00:00'::timestamp + (gs.n - 1) * interval '12 hour')
     ) v(ts) left join
     users u
     on u.created_at >= v.ts and
        u.created_at < v.ts + interval '12 hour'

推荐阅读