这里提供一种使用
递归CTE(其实不是真正的“递归”,但它们就是这样被称呼)和
窗口函数的解决方案。您需要至少安装PostgreSQL 8.4才能运行此方法。
SQL Fiddle
PostgreSQL 9.1.9模式设置:
CREATE TABLE viewings (
user_id INTEGER NOT NULL,
datetime TIMESTAMPTZ NOT NULL,
programme TEXT NOT NULL,
PRIMARY KEY (user_id, datetime)
);
INSERT INTO viewings (datetime, user_id, programme) VALUES
('2013-09-01 00:01:18', 1, 'A'),
('2013-09-10 14:03:14', 1, 'B'),
('2013-09-20 17:02:12', 2, 'A'),
('2013-09-21 00:03:22', 2, 'C'),
('2013-09-21 01:23:22', 2, 'M'),
('2013-09-21 03:03:22', 2, 'E'),
('2013-09-21 04:03:22', 2, 'F'),
('2013-09-21 06:03:22', 2, 'G'),
('2013-09-21 09:03:22', 2, 'H'),
('2013-09-03 18:21:09', 3, 'D'),
('2013-09-22 09:03:22', 2, 'H'),
('2013-09-24 19:21:00', 2, 'X'),
('2013-09-24 20:21:00', 2, 'Y'),
('2013-09-24 21:21:00', 2, 'Z');
查询 1:
WITH RECURSIVE consecutive_viewings(user_id, first_dt, last_dt) AS (
WITH lagged_viewings AS (
SELECT user_id, LAG(user_id) OVER w AS prev_user_id,
datetime, LAG(datetime) OVER w AS prev_datetime,
programme
FROM viewings WINDOW w AS (PARTITION BY user_id ORDER BY datetime)
)
SELECT user_id, datetime AS first_dt, datetime AS last_dt
FROM lagged_viewings
WHERE prev_datetime IS NULL OR (prev_datetime + '2 hours'::interval) < datetime
UNION ALL
SELECT lv.user_id, cv.first_dt, lv.datetime AS last_dt
FROM consecutive_viewings cv
INNER JOIN lagged_viewings lv
ON lv.user_id=cv.user_id AND
lv.prev_datetime=cv.last_dt
WHERE (lv.prev_datetime + '2 hours'::interval) >= lv.datetime
)
SELECT user_id, first_dt, MAX(last_dt) AS last_dt
FROM consecutive_viewings
WHERE first_dt != last_dt
GROUP BY user_id, first_dt
ORDER BY user_id, first_dt
结果:
| USER_ID | FIRST_DT | LAST_DT |
|---------|----------------------------------|----------------------------------|
| 2 | September, 21 2013 00:03:22+0000 | September, 21 2013 06:03:22+0000 |
| 2 | September, 24 2013 19:21:00+0000 | September, 24 2013 21:21:00+0000 |
为了理解这个问题,也许从最嵌套的CTE开始更容易。它将按
user_id
和
datetime
对视图进行排序,但同时还会添加一个额外的列,其中包含前一个视图的时间戳,以便稍后可以链接它们。这不是递归CTE(即使在单独的以下查询中也不需要CTE):
查询2:
WITH lagged_viewings AS (
SELECT user_id, LAG(user_id) OVER w AS prev_user_id,
datetime, LAG(datetime) OVER w AS prev_datetime,
programme
FROM viewings WINDOW w AS (PARTITION BY user_id ORDER BY datetime)
)
SELECT * FROM lagged_viewings
结果:
| USER_ID | PREV_USER_ID | DATETIME | PREV_DATETIME | PROGRAMME |
|---------|--------------|----------------------------------|----------------------------------|-----------|
| 1 | (null) | September, 01 2013 00:01:18+0000 | (null) | A |
| 1 | 1 | September, 10 2013 14:03:14+0000 | September, 01 2013 00:01:18+0000 | B |
| 2 | (null) | September, 20 2013 17:02:12+0000 | (null) | A |
| 2 | 2 | September, 21 2013 00:03:22+0000 | September, 20 2013 17:02:12+0000 | C |
| 2 | 2 | September, 21 2013 01:23:22+0000 | September, 21 2013 00:03:22+0000 | M |
| 2 | 2 | September, 21 2013 03:03:22+0000 | September, 21 2013 01:23:22+0000 | E |
| 2 | 2 | September, 21 2013 04:03:22+0000 | September, 21 2013 03:03:22+0000 | F |
| 2 | 2 | September, 21 2013 06:03:22+0000 | September, 21 2013 04:03:22+0000 | G |
| 2 | 2 | September, 21 2013 09:03:22+0000 | September, 21 2013 06:03:22+0000 | H |
| 2 | 2 | September, 22 2013 09:03:22+0000 | September, 21 2013 09:03:22+0000 | H |
| 2 | 2 | September, 24 2013 19:21:00+0000 | September, 22 2013 09:03:22+0000 | X |
| 2 | 2 | September, 24 2013 20:21:00+0000 | September, 24 2013 19:21:00+0000 | Y |
| 2 | 2 | September, 24 2013 21:21:00+0000 | September, 24 2013 20:21:00+0000 | Z |
| 3 | (null) | September, 03 2013 18:21:09+0000 | (null) | D |
这个递归CTE可能有点棘手。 "递归" 基于两个select语句之间的联合。
- 第一个语句是种子迭代(非递归部分):它将找到开始观看链的行(即,如果它是该用户的第一次观看,则前一个日期时间为null,或者前一个日期时间比您的截止间隔更远)。
- 第二个语句链接该观看以形成更长的时间段。一些持续时间会重叠,因为它不知道何时结束。这就是在顶部的整体查询中使用条件的原因,以查找最大值并消除仅具有单个观看的时间段。
查询3:
WITH RECURSIVE consecutive_viewings(user_id, first_dt, last_dt) AS (
WITH lagged_viewings AS (
SELECT user_id, LAG(user_id) OVER w AS prev_user_id,
datetime, LAG(datetime) OVER w AS prev_datetime,
programme
FROM viewings WINDOW w AS (PARTITION BY user_id ORDER BY datetime)
)
SELECT user_id, datetime AS first_dt, datetime AS last_dt
FROM lagged_viewings
WHERE prev_datetime IS NULL OR (prev_datetime + '2 hours'::interval) < datetime
UNION ALL
SELECT lv.user_id, cv.first_dt, lv.datetime AS last_dt
FROM consecutive_viewings cv
INNER JOIN lagged_viewings lv
ON lv.user_id=cv.user_id AND
lv.prev_datetime=cv.last_dt
WHERE (lv.prev_datetime + '2 hours'::interval) >= lv.datetime
)
SELECT * FROM consecutive_viewings
ORDER BY user_id, first_dt, last_dt
结果:
| USER_ID | FIRST_DT | LAST_DT |
|---------|----------------------------------|----------------------------------|
| 1 | September, 01 2013 00:01:18+0000 | September, 01 2013 00:01:18+0000 |
| 1 | September, 10 2013 14:03:14+0000 | September, 10 2013 14:03:14+0000 |
| 2 | September, 20 2013 17:02:12+0000 | September, 20 2013 17:02:12+0000 |
| 2 | September, 21 2013 00:03:22+0000 | September, 21 2013 00:03:22+0000 |
| 2 | September, 21 2013 00:03:22+0000 | September, 21 2013 01:23:22+0000 |
| 2 | September, 21 2013 00:03:22+0000 | September, 21 2013 03:03:22+0000 |
| 2 | September, 21 2013 00:03:22+0000 | September, 21 2013 04:03:22+0000 |
| 2 | September, 21 2013 00:03:22+0000 | September, 21 2013 06:03:22+0000 |
| 2 | September, 21 2013 09:03:22+0000 | September, 21 2013 09:03:22+0000 |
| 2 | September, 22 2013 09:03:22+0000 | September, 22 2013 09:03:22+0000 |
| 2 | September, 24 2013 19:21:00+0000 | September, 24 2013 19:21:00+0000 |
| 2 | September, 24 2013 19:21:00+0000 | September, 24 2013 20:21:00+0000 |
| 2 | September, 24 2013 19:21:00+0000 | September, 24 2013 21:21:00+0000 |
| 3 | September, 03 2013 18:21:09+0000 | September, 03 2013 18:21:09+0000 |