sql - 通过一次选择计算每天的访问量

Question

我的 PostgreSQL 中有这个表:

CREATE TABLE visits(
id BIGSERIAL NOT NULL PRIMARY KEY,
timeslot TSRANGE NOT NULL,
user_id INTEGER NOT NULL REFERENCES users(id),
CONSTRAINT overlapping_timeslots EXCLUDE USING GIST (
    user_id WITH =,
    timeslot WITH &&
));

有了这些数据:

|   id  |   timeslot                                |   user_id |
|   1   |   10.02.2014 10:00 - 10.02.2014 17:00     |   2       |
|   2   |   10.02.2014 18:00 - 10.02.2014 19:00     |   2       |    
|   3   |   11.02.2014 01:00 - 11.02.2014 02:00     |   2       |
|   4   |   10.02.2014 12:00 - 11.02.2014 17:00     |   2       |
|   5   |   11.02.2014 12:00 - 11.02.2014 12:30     |   2       |

我需要知道每天有多少用户访问我的商店。如果用户一天光顾商店两次，则应计为两次。

在上面的例子中它应该是。

Users at 10.02 = 3 (ID: 1,2,4)
Users at 11.02 = 3 (ID: 3,4,5)

Answer 1

假设“每天”缺乏定义的任意时间段:

SELECT  day, count(*) AS visits, array_agg(id) AS ids
FROM    generate_series ('2014-02-10'::date
                       , '2014-02-12'::date
                       , interval '1 day') AS d(day)
JOIN    visits ON tsrange(day::timestamp
                        , day::timestamp + interval '1 day') && timeslot
GROUP   BY 1;

&& is the "overlap" operator for range types .

使用 LEFT JOIN 在结果中包含访问次数为 0 的天数。

-> SQLfiddle demo.