sql - 需要获取日期时间范围之间的用户数
问题描述
我需要创建一个报告，显示在用户提供的时间范围内登录的用户数。这对我来说似乎有点复杂，因为我是新手。我们需要计算在所选时间范围内的几个小时之间登录的用户
id,startdate, enddate
1 01012018:14:01:10 01012018:15:30:40
1 01012018:16:11:50 01012018:16:30:45
2 01012018:09:41:50 01012018:16:30:45
1 01012018:09:41:50 01012018:10:30:45
2 01012018:19:41:50 01012018:21:30:45
3 01012018:09:31:10 01012018:21:20:45
预期输出：
startdate enddate hours total users logged
01012018 01012018 09-10
01012018 01012018 10-11
01012018 01012018 11-12
01012018 01012018 13-14
01012018 01012018 14-15 01
01012018 01012018 15-16 01
01012018 01012018 16-17 01
01012018 01012018 17-18 01
01012018 01012018 18-19 01
01012018 01012018 19-20 01
01012018 01012018 20-21 01
01012018 01012018 21-22 01
01012018 01012018 22-23
01012018 01012018 23-00
解决方案
您似乎将日期和时间存储为文本。这意味着您不能使用 Postgres 的内置日期函数来进行比较。而且您以一种难以使用的格式存储它们，“DDMMYYYY”或可能的“MMDDYYYY”是模棱两可的，需要对其进行解析才能对其进行排序。除非您有充分的理由，否则请坚持ISO 8601日期。一切都理解它，它很容易分类。
你的桌子应该看起来像这样。
create table account_logins (
id serial primary key,
account integer not null references accounts(id),
start_at timestamp not null,
end_at timestamp not null
);
每个条目都有自己的 id，只是很好的做法。该帐户被声明为适当的外键。它使用的timestamp类型意味着我们可以使用所有 Postgres 日期函数。最后，我为它们命名start_at，end_at因为它们是时间戳，而不是日期。foo_at是许多系统用于时间戳的命名约定。
这里有足够的数据来测试它。
id | account | start_at | end_at
----+---------+---------------------+---------------------
1 | 1 | 2018-01-01 14:01:10 | 2018-01-01 15:30:40
2 | 1 | 2018-01-01 16:11:50 | 2018-01-01 16:30:45
3 | 2 | 2018-01-01 16:13:45 | 2018-01-01 16:25:11
我们想要的是这样的东西。
hour | num_logins
------+-----------
... | 0
13:00 | 0
14:00 | 1
15:00 | 1
16:00 | 2
17:00 | 0
... | 0
现在我们的模式已经更好了，我们可以开始构建查询了。首先，我们需要上午 9 点到午夜之间的所有时间。我们可以用generate_series.
select hour
from generate_series(
'20180101 09:00'::timestamp,
'20180101 23:00'::timestamp,
'1 hour'
) as series(hour);
这给了我们一个工作时间列表。
hour
---------------------
2018-01-01 09:00:00
2018-01-01 10:00:00
2018-01-01 11:00:00
2018-01-01 12:00:00
2018-01-01 13:00:00
2018-01-01 14:00:00
2018-01-01 15:00:00
2018-01-01 16:00:00
2018-01-01 17:00:00
2018-01-01 18:00:00
2018-01-01 19:00:00
2018-01-01 20:00:00
2018-01-01 21:00:00
2018-01-01 22:00:00
2018-01-01 23:00:00
as series(hour)为生成的表命名，series，以及列，hour。这将使其更容易在短期内参考。
account_logins现在我们通过检查哪些登录在hour...hour+1范围内来加入这一系列小时。
select hour, id
from generate_series(
'20180101 09:00'::timestamp,
'20180101 23:00'::timestamp,
'1 hour'
) as series(hour)
left outer join account_logins
on start_at < hour+'1 hour' and hour < end_at;
left outer join确保series选择 中的每个小时，即使该小时没有登录。请参阅SQL 连接的可视化表示，以更好地理解各种连接。
这给了我们这个。
hour | id
---------------------+----
2018-01-01 09:00:00 |
2018-01-01 10:00:00 |
2018-01-01 11:00:00 |
2018-01-01 12:00:00 |
2018-01-01 13:00:00 |
2018-01-01 14:00:00 | 1
2018-01-01 15:00:00 | 1
2018-01-01 16:00:00 | 2
2018-01-01 16:00:00 | 3
2018-01-01 17:00:00 |
2018-01-01 18:00:00 |
2018-01-01 19:00:00 |
2018-01-01 20:00:00 |
2018-01-01 21:00:00 |
2018-01-01 22:00:00 |
2018-01-01 23:00:00 |
我们可以看到第一次登录在它跨越的两个小时内都被计算在内，并且 16:00 有两次登录。
最后一步是将小时分组在一起，group by hour计算每小时的登录次数count(id)，并确保它们以正确的顺序出现，order by hour。
select hour, count(id) as "total users logged"
from generate_series(
'20180101 09:00'::timestamp,
'20180101 23:00'::timestamp,
'1 hour'
) as series(hour)
left outer join account_logins
on start_at < hour+'1 hour' and hour < end_at
group by hour
order by hour;
你有它。
hour | total users logged
---------------------+--------------------
2018-01-01 09:00:00 | 0
2018-01-01 10:00:00 | 0
2018-01-01 11:00:00 | 0
2018-01-01 12:00:00 | 0
2018-01-01 13:00:00 | 0
2018-01-01 14:00:00 | 1
2018-01-01 15:00:00 | 1
2018-01-01 16:00:00 | 2
2018-01-01 17:00:00 | 0
2018-01-01 18:00:00 | 0
2018-01-01 19:00:00 | 0
2018-01-01 20:00:00 | 0
2018-01-01 21:00:00 | 0
2018-01-01 22:00:00 | 0
2018-01-01 23:00:00 | 0
您可以使用各种日期函数来获得所需的格式，但我建议保持查询简单和通用。相反，让任何消耗和显示此数据的内容对其进行格式化。将格式与功能分开。