我有一张桌子,结构如下:
(
id SERIAL PRIMARY KEY,
user_id integer NOT NULL REFERENCES user(id) ON UPDATE CASCADE,
status text NOT NULL,
created_at timestamp with time zone NOT NULL,
updated_at timestamp with time zone NOT NULL
)
示例数据:
"id","user_id","status","created_at","updated_at"
416,38,"ONLINE","2018-08-07 14:40:51.813+00","2018-08-07 14:40:51.813+00"
417,39,"ONLINE","2018-08-07 14:45:00.717+00","2018-08-07 14:45:00.717+00"
418,38,"OFFLINE","2018-08-07 15:43:22.678+00","2018-08-07 15:43:22.678+00"
419,38,"ONLINE","2018-08-07 16:21:30.725+00","2018-08-07 16:21:30.725+00"
420,38,"OFFLINE","2018-08-07 16:49:10.3+00","2018-08-07 16:49:10.3+00"
421,38,"ONLINE","2018-08-08 11:37:53.639+00","2018-08-08 11:37:53.639+00"
422,38,"OFFLINE","2018-08-08 12:29:08.234+00","2018-08-08 12:29:08.234+00"
423,39,"ONLINE","2018-08-14 15:22:00.539+00","2018-08-14 15:22:00.539+00"
424,39,"OFFLINE","2018-08-14 15:22:02.092+00","2018-08-14 15:22:02.092+00"
当我的应用程序上的用户联机时,将插入状态为
ONLINE
的新行。当它们脱机时,将插入状态为OFFLINE
的行。创建其他条目是为了记录不同的事件,但对于此查询,只有OFFLINE
和ONLINE
是重要的。我想制作一个图表,显示一段时间内(如5分钟)在一个日期范围内在线的用户总数。如果一个用户在这段时间的任何时候都在线,那么他们应该被计算在内。
例子:
datetime, count
2019-05-22T12:00:00+0000, 53
2019-05-22T12:05:00+0000, 47
2019-05-22T12:10:00+0000, 49
2019-05-22T12:15:00+0000, 55
2019-05-22T12:20:00+0000, 59
2019-05-22T12:25:00+0000, 56
通过获取日期范围内的所有状态行,然后手动处理,我可以为单个用户生成类似的图表,但是这种方法不会扩展到所有用户。
我相信这样的事情可以用窗口函数来完成,但我不确定从哪里开始
最佳答案
因为你的问题很模糊,没有人能百分之百地帮助你。好吧,你可以通过“with”子句和窗口函数的组合来实现你想要的。有了“With”子句,你可以很容易地把大问题分解成小问题。也许以下查询(不查看任何性能)可能有帮助,您可以用表替换public.tbl_test:
with temp_online as (
select
*
from public.tbl_test
where public.tbl_test.status ilike 'online'
order by created_at
),
temp_offline as (
select
*
from public.tbl_test
where public.tbl_test.status ilike 'offline'
order by created_at
),
temp_change as (
select
* ,
(
select temp_offline.created_at from temp_offline where temp_offline.created_at > temp_online.created_at and temp_offline.user_id = temp_online.user_id order by created_at asc limit 1
) as go_offline
from temp_online
),
temp_result as
(
select *,
go_offline - created_at as online_duration
from temp_change
),
temp_series as
(
SELECT (generate_series || ' minute')::interval + '2019-05-22 00:00:00'::timestamp as temp_date
FROM generate_series(0, 1440,5)
)
select
temp_series.temp_date,
(select count(*) from temp_result where temp_result.created_at <= temp_series.temp_date and temp_result.go_offline >= temp_series.temp_date) as count_users
from
temp_series
关于sql - 从行之间的差异获取总计,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56256810/