计算MySQL中的连续出现

发布于01月06日

关于MySQL中的窗口，我有一个简短的问题

SELECT 
Client,
User,
Date,
Flag,
lag(Date) over (partition by Client,User order by Date asc) as last_date,
lag(Flag) over (partition by Client,User order by Date  asc) as last_flag,
case when Flag = 1 and last_flag = 1 then 1 else 0 end as consecutive
FROM db.tbl

此查询返回类似以下内容的内容.我正在try 计算每个用户的标志列为1的连续次数，如果他们有111most recently00111次，那么我们应该取最后三次出现1来确定他们具有连续的标志3次.

我需要提取连续旗帜的开始和结束日期.

我该如何着手做这件事，有人能帮我吗:)

如果我们使用11110000111的例子，那么我们应该只提取111，从而提取该客户的3个最近日期.因此，在下面的代码中，我们需要将10.01.2023作为第一个日期，将24.01.2023作为最后一个日期.连续计数应为3

输出:

推荐答案

使用聚合和字符串函数:

WITH cte AS (
  SELECT Client, User,
         GROUP_CONCAT(CASE WHEN Flag THEN Date END ORDER BY Date) AS dates,
         CHAR_LENGTH(SUBSTRING_INDEX(GROUP_CONCAT(Flag ORDER BY Date SEPARATOR ''), '0', '-1')) AS consecutive
  FROM tablename
  GROUP BY Client, User
)
SELECT Client, User,
       NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(dates, ',', -consecutive), ',', 1), '') AS first_date,
       CASE WHEN consecutive > 0 THEN SUBSTRING_INDEX(dates, ',', -1) END AS last_date,
       consecutive 
FROM cte;

另一种使用窗口函数和条件聚合的解决方案:

WITH 
  cte1 AS (SELECT *, SUM(NOT Flag) OVER (PARTITION BY Client, User ORDER BY Date) AS grp FROM tablename),
  cte2 AS (SELECT *, MAX(grp) OVER (PARTITION BY Client, User) AS max_grp FROM cte1)
SELECT Client, User,
       MIN(CASE WHEN Flag THEN Date END) AS first_date,
       MAX(CASE WHEN Flag THEN Date END) AS last_date,
       SUM(Flag) AS consecutive
FROM cte2 
WHERE grp = max_grp
GROUP BY Client, User;

See the demo.