sql - 使用时间戳差异将页面浏览量分组到 session 中

Question

我有一个包含 3 个字段的表:user_id、页面和时间戳，如下所示:

user_id     page        timestamp
1234567     home.all    2018-03-01 00:10
7541231     task.now    2018-03-01 03:51
7541231     home.all    2018-03-01 03:53
4544731     talk.wow    2018-03-01 04:56
4544731     task.now    2018-03-01 05:01
4544731     home.all    2018-03-01 05:02
4544731     bla.home    2018-03-01 05:26
4544731     home.all    2018-03-01 06:40

时间戳是具有给定 ID 的用户在网站上加载给定页面的时间。每次观察都是一次网页浏览命中。

我需要为每个观察分配一个 session ID。每个 session 的每个 session ID 都应该是唯一的，其中一个 session 是一组页面浏览，其中彼此最接近的时间戳的时间差不超过 3600 秒，发生在 相同的 user_id .

结果应该是这样的:

user_id     page        timestamp           session_id
1234567     home.all    2018-03-01 00:10    1234567-2018030100100010
7541231     task.now    2018-03-01 03:51    7541231-2018030103510353
7541231     home.all    2018-03-01 03:53    7541231-2018030103510353
4544731     talk.wow    2018-03-01 04:56    4544731-2018030104560526
4544731     task.now    2018-03-01 05:01    4544731-2018030104560526
4544731     home.all    2018-03-01 05:02    4544731-2018030104560526
4544731     bla.home    2018-03-01 05:26    4544731-2018030104560526
4544731     home.all    2018-03-01 06:40    4544731-2018030106400640

请问您可以提出任何查询吗？

Answer 1

如果您可以确保 user_id 和 timestamp 这对是唯一的，则以下内容可能对您有用。

WITH cte AS
(
SELECT h1.user_id,
       h1.page,
       h1.timestamp,
       coalesce(h1.timestamp - h2.timestamp <= INTERVAL '3600 SECONDS', false) shares_session_with_previous,
       coalesce(h4.timestamp - h1.timestamp <= INTERVAL '3600 SECONDS', false) shares_session_with_next
       FROM hit h1
            LEFT JOIN hit h2
                      ON h2.user_id = h1.user_id
                         AND h2.timestamp = (SELECT max(h3.timestamp)
                                                    FROM hit h3
                                                    WHERE h3.user_id = h1.user_id
                                                          AND h3.timestamp < h1.timestamp)
            LEFT JOIN hit h4
                      ON h4.user_id = h1.user_id
                         AND h4.timestamp = (SELECT min(h5.timestamp)
                                                    FROM hit h5
                                                    WHERE h5.user_id = h1.user_id
                                                          AND h5.timestamp > h1.timestamp)
)
SELECT c1.user_id,
       c1.page,
       c1.timestamp,
       concat((SELECT concat(c2.user_id, '-', to_char(max(c2.timestamp), 'YYYYMMDDHH24MI'))
                      FROM cte c2
                      WHERE c2.user_id = c1.user_id
                            AND c2.timestamp <= c1.timestamp
                            AND NOT c2.shares_session_with_previous
                      GROUP BY c2.user_id),
              (SELECT to_char(min(c2.timestamp), 'HH24MI')
                      FROM cte c2
                      WHERE c2.user_id = c1.user_id
                            AND c2.timestamp >= c1.timestamp
                            AND NOT c2.shares_session_with_next)) session_id
       FROM cte c1
       ORDER BY c1.timestamp;

核心部分是CTE。对于每一行，将连接具有最旧时间戳的行和具有最旧时间戳的行。检查较旧或最新的时间戳与行的时间戳之间的间隔是否小于或等于 3600 秒。检查的结果存储在标志 shares_session_with_previous 和 shares_session_with_next 中。

然后使用标志来获取 session 的开始和结束。 begin 是早于或等于当前时间戳的最年轻时间戳，其中 shares_session_with_previous 为 false。 end 是小于或等于当前时间戳的最旧时间戳，其中 shares_session_with_next 为 false。

session 开始和结束的相应值被连接起来以提供您的 session ID。

SQL Fiddle