sql - 计算限制为 7 的用户的访问次数

Question

我正在尝试计算用户在应用程序上进行的连续访问。我使用 rank 函数来确定每个用户维护的条纹。但是，我的要求是条纹不应超过 7。

例如，如果用户连续 9 天访问该应用程序。他将有 2 种不同的条纹：一种为 7，另一种为 2。

使用 MaxCompute。它类似于 MySQL。

我有下表名为visitors_data：

user_id visit_date
murtaza 01-01-2021
john    01-01-2021
murtaza 02-01-2021
murtaza 03-01-2021
murtaza 04-01-2021
john    01-01-2021
murtaza 05-01-2021
murtaza 06-01-2021
john    02-01-2021
john    03-01-2021
murtaza 07-01-2021
murtaza 08-01-2021
murtaza 09-01-2021
john    20-01-2021
john    21-01-2021

输出应如下所示：

user_id streak
murtaza 7
murtaza 2
john    3
john    2

我能够通过以下查询获得条纹，但我无法将条纹限制为 7。

WITH groups AS (
    SELECT  user_id,
            RANK() OVER (ORDER BY user_id, visit_date) AS RANK,
            visit_date,
            DATEADD(visit_date, -RANK() OVER (ORDER BY user_id, visit_date), 'dd') AS date_group
    FROM visitors_data
    ORDER BY user_id, visit_date)

SELECT 
    user_id,
    COUNT(*) AS streak
FROM  groups 
GROUP BY 
    user_id,
    date_group
HAVING COUNT(*)>1
ORDER BY COUNT(*);

标签： sqlrank

Answer 1

我的想法与 forpas 类似：

SELECT user_id, COUNT(*) streak
FROM 
(
   SELECT 
      user_id, streak, 
      FLOOR((ROW_NUMBER() OVER (PARTITION BY user_id, streak ORDER BY visit_date)-1)/7) substreak
   FROM 
   (
      SELECT 
         user_id, visit_date,
         SUM(runtot) OVER (PARTITION BY user_id ORDER BY visit_date) streak
      FROM (
         SELECT 
            user_id, visit_date, 
            CASE WHEN DATE_ADD(visit_date, INTERVAL -1 DAY) = LAG(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date) THEN 0 ELSE 1 END as runtot
         FROM visitors_data
         GROUP BY user_id, visit_date
      ) x
   ) y
) z
GROUP BY user_id, streak, substreak

作为对其工作原理的解释；计算连续记录的运行的一个常用技巧是使用 LAG 来检查之前的记录，如果只有一天的差异，则输入 0，否则输入 1。这意味着连续运行的第一条记录是 1，其余为 0，因此该列最终看起来像​1,0,0,0,1,0... SUM OVER ORDER BY 以“运行总计”方式对其进行求和。这实际上意味着它会形成一个计数器，每次遇到运行开始时都会打勾，因此运行 4 天后有间隙，然后运行 ​​3 天看起来像 1,1,1,1,2,2,2等等，它形成了一个“条纹ID号”。

如果然后将其输入到按条纹 ID 编号分区的行编号中，它会建立一个递增计数器，每次条纹 ID 更改时都会重新启动。如果我们将其减去 1，使其从 0 而不是 1 运行，那么我们可以将其除以 7，以获得 9 长条纹的“子条纹 ID”，即 0,0,0,0,0,0,0 ,1,1（依此类推。连续 25 个将有 7 个零、7 个一、7 个二和 4 个三）

然后剩下的就是按用户、连拍 ID、substreakID 分组并计算结果

在最后一组和计数之前，数据如下所示：

这应该让您了解这一切是如何运作的