SQL实现滑动窗口功能

场景1：假设用户访问记录表 records 有 3 个字段，uid、url（用户访问url）、datetime（访问 url 的时间），问找出 url 在 5 分钟内被访问次数大于 10 次的用户，以及访问次数大于 10 次的开始时间点。

思路：该问题类似于 flink 中的滑动窗口问题，解决方法是从某一条记录开始，往后在 5 分钟内的条数超过 10 条即可，使用 inner join 即可完成

SELECT a.uid,
       a.url,
       a.datetime
FROM records a
         JOIN
     records b ON a.uid = b.uid
         AND UNIX_TIMESTAMP(b.datetime) - UNIX_TIMESTAMP(a.datetime) >= 0
         AND UNIX_TIMESTAMP(b.datetime) - UNIX_TIMESTAMP(a.datetime) < 300
GROUP BY a.uid,
         a.url,
         a.datetime
HAVING count(1) >= 10;

场景2：在场景1 的条件下，这 10 次的跨度必须大于 3 分钟（意思是如果用户在1分钟内就访问了10，这样不满足要求，这 10 次必须分布在 3 个 1 分钟里面）。

WITH user_minute_count AS (
    SELECT uid,
           url,
           CONCAT(SUBSTRING_INDEX(datetime, ':', 2), ':00') AS date_minute,
           COUNT(1) view_count
    FROM test.records
    GROUP BY uid, url, SUBSTRING_INDEX(datetime, ':', 2)
)
SELECT a.uid,
       a.url,
       a.date_minute
FROM user_minute_count a
         JOIN
     user_minute_count b ON a.uid = b.uid
         AND UNIX_TIMESTAMP(b.date_minute) - UNIX_TIMESTAMP(a.date_minute) >= 0
         AND UNIX_TIMESTAMP(b.date_minute) - UNIX_TIMESTAMP(a.date_minute) < 300
GROUP BY a.uid,
         a.url,
         a.date_minute
HAVING SUM(b.view_count) >= 10
   AND COUNT(1) > 3;