mysql - SQL:按顺序计算访问次数

Question

我有下表(访问):

id(int) | fb_id(varchar)|   flipbook(varchar) | 
----       ----------         ---------     
1           1123            november 2014
2           1124            november 2014
3           1123            december 2014
4           1124            december 2014
5           1123            december 2014
6           1123            january 2015
7           1126            january 2015
8           1125            february 2015
9           1123            february 2015
10          1124            march 2015
11          1125            march 2015
11          1123            march 2015

查询运行后，我想得到如下结果:

sequence  count
  5         1  (1 user visited 5 flipbooks in a row: 1123)
  2         2  (2 users visited 2 flipbooks in a row: 1124, 1125)
  1         1  (1 user visited only 1 flipbook: 1126)

有什么想法可以实现吗？

Answer 1

fb_id=1124 仅“连续”访问了 1 个活页簿。除非 id=4 行应该是“december 2014”而不是“december 2015”。

是的，这可以在 MySQL 中使用用户定义的变量来完成。 MySQL 引用手册警告说，在同一语句中使用用户定义变量的行为是未定义的。

sequence   count  info
--------  ------  ---------------------------------------------
       5       1  (1 user visited 5 flipbooks in a row: 1123
       2       1  (1 user visited 2 flipbooks in a row: 1125
       1       2  (2 users visited only 1 flipbook: 1124,1126

该结果集由以下 SQL 查询生成:

SELECT d.seq AS `sequence`
     , COUNT(1) AS `count`
     , CONCAT('('
             ,COUNT(1)
             ,' user'
             ,IF(COUNT(1)>1,'s','')
             ,' visited'
             ,IF(d.seq>1,'',' only')
             ,' '
             ,d.seq
             ,' flipbook'
             ,IF(d.seq>1,'s in a row: ',': ')
             ,GROUP_CONCAT(d.fb_id ORDER BY d.fb_id)
       ) AS `info`
  FROM ( SELECT c.fb_id
              , MAX(c.cnt) AS seq
           FROM ( SELECT @cnt := IF(@prev_fb_id = v.fb_id AND PERIOD_DIFF(v.yyyymm,@prev_yyyymm)=1, @cnt + 1, 1) AS cnt
                       , @prev_yyyymm := v.yyyymm AS yyyymm
                       , @prev_fb_id  := v.fb_id AS fb_id
                    FROM ( SELECT @prev_fb_id := NULL
                                , @prev_yyyymm := NULL
                                , @cnt := 0
                         ) i
                   CROSS
                    JOIN ( SELECT t.fb_id
                                , DATE_FORMAT(STR_TO_DATE(CONCAT('01 ',t.flipbook),'%d %M %Y'),'%Y%m') AS yyyymm
                             FROM t
                            GROUP BY t.fb_id, yyyymm 
                            ORDER BY t.fb_id, yyyymm
                          ) v  
                ) c
          GROUP BY c.fb_id
       ) d
 GROUP BY d.seq
 ORDER BY d.seq DESC

跟进

源表的表名在最内层的内联 View 中进入查询，别名为v。 (在上面的例子中，表名是t。

要了解这是如何工作的，您可以只运行最里面的内联 View 的查询，看看它返回什么。重要的工作是将 flipbook 列重新格式化为 YYYYMM 格式，并对行进行排序。 (稍后我们将使用 PERIOD_DIFF 函数来计算 flipbook 值之间的月数。)

内联 View i 只是用来初始化我们将要使用的用户定义变量。我们在最里面的内联 View 查询中执行此操作，以便在外部查询中引用这些变量之前完成。它本质上等同于在查询运行之前立即运行单独的 SET 语句。 (我们不希望变量的任何遗留值影响我们的结果。)

一旦 v 和 i View 被具体化(作为派生表)，内联 View 中别名为 v 的查询就可以运行了。 (FROM 子句中的 View 查询本质上类似于表。)

这个查询是神奇的地方。我们正在使用用户定义的变量来保留“前一”行的值，因此我们可以将其与当前行进行比较。如果当前行是同一用户的，并且恰好在上一行之后一个月，我们将序列计数递增 1，否则，我们将其设置为 1。

一旦该查询完成，我们就有了一个派生表，我们可以将其用作另一个查询的行源。在此查询中，我们要为每个用户找到该序列计数器的“最大值”。这将为我们提供每个用户的最长序列。

使用该集合，最外层的查询几乎是微不足道的...按降序排列最长的序列，并折叠行以计算具有相同最大序列值的用户数。

---

要获得序列中 fb_id 的最高访问次数，我们可以累加该最内层 View 查询中的访问次数。 COUNT(1) 或 SUM(1) 将为我们提供每个月的访问次数。

这可以提供给下一个查询。我们可以进行与累积连续月份相同的检查。我们将累计总访问次数，而不是递增 1。

必须修改下一个查询。我们不能只用 MAX() 包裹 tot，因为我们不能保证总访问量来自相同的最长序列。我们可能在 5 个月的序列中有 6 次访问，但同一用户可能在 3 个月内访问了 8 次。所以我们放弃了 MAX() 函数，而是使用排序(从最高到最低)。我们将为 fb_id 保留第一行的值，并将其他值设置为 NULL。然后，在最外层的查询中，我们可以使用 MAX() 聚合，它会忽略 NULL，并返回具有相同 sequence 的所有用户中最高的总访问量值(value)。

我们可以得到这样的结果:

sequence   count  highest_tot
--------  ------  -----------
       5       1            6
       2       1            2
       1       2            1

来自这样的查询:

SELECT d.seq AS `sequence`
     , COUNT(1) AS `count`
     , MAX(FLOOR(d.tot)) AS `highest_tot`
  FROM ( SELECT IF(@c_fb_id=c.fb_id,NULL,c.cnt) AS seq
              , IF(@c_fb_id=c.fb_id,NULL,c.tot) AS tot
              , @c_fb_id := c.fb_id AS fb_id
           FROM ( SELECT @cnt := IF(@prev_fb_id = v.fb_id AND PERIOD_DIFF(v.yyyymm,@prev_yyyymm)=1, @cnt + 1, 1) AS cnt
                       , @tot := IF(@prev_fb_id = v.fb_id AND PERIOD_DIFF(v.yyyymm,@prev_yyyymm)=1, @tot + v.tot, v.tot) AS tot
                       , @prev_yyyymm := v.yyyymm AS yyyymm
                       , @prev_fb_id  := v.fb_id AS fb_id
                    FROM ( SELECT @prev_fb_id := NULL
                                , @prev_yyyymm := NULL
                                , @cnt := 0
                                , @tot := 0
                                , @c_fb_id := NULL
                         ) i
                   CROSS
                    JOIN ( SELECT t.fb_id
                                , DATE_FORMAT(STR_TO_DATE(CONCAT('01 ',t.flipbook),'%d %M %Y'),'%Y%m') AS yyyymm
                                , SUM(1) AS tot
                             FROM t
                            GROUP BY t.fb_id, yyyymm 
                            ORDER BY t.fb_id, yyyymm
                          ) v  
                ) c
          ORDER BY c.fb_id DESC, c.cnt DESC, c.tot DESC
       ) d
 WHERE d.seq IS NOT NULL
 GROUP BY d.seq
 ORDER BY d.seq DESC