计算SQL中的连续数值 [英] Count Consecutive Numeric values in SQL

问题描述

有关此方案的问题已经很多，但是我无法在方案中复制答案.

There are already many existing questions about this scenario, however I am unable to replicate the answers in my scenario.

我有以下示例数据集:

ID Number  | Values 
754321       0
754321       0
754321       0
754321       0
754321       1
754321       0
754321       1
754321       0
754321       2
754321       0
754329       3
754329       4
754329       5
754329       6
754329       7
754329       8
754329       9 

我希望输出ID Number的SQL查询的值连续显示"0"的次数.因此，对于上表，我希望获得如下输出:

I want the SQL query that outputs the ID Number with the number of times the value of "0" appears consecutively. So, for the above table I would like to get the output as follows:

ID Number  Count of Consecutive 0 Values
754321     4 

推荐答案

如果运行的MySQL版本不支持窗口函数，则可以使用变量来实现此功能:

If you're running a version of MySQL that doesn't support window functions, you can implement this functionality using variables:

SELECT `ID Number`, MAX(cnt) AS `Max Consecutive 0 Values`
FROM (SELECT `ID Number`, SUM(`Values` = 0) AS cnt
      FROM (SELECT `ID Number`, `Values`,
                   @cnz:= CASE WHEN `Values` != 0 THEN @cnz + 1
                               ELSE @cnz
                          END AS cnz
            FROM data
            CROSS JOIN (SELECT @cnz := 0) init
            ORDER BY date
            ) c
      GROUP BY `ID Number`, cnz) s
GROUP BY `ID Number`

输出

ID Number   Max Consecutive 0 Values
754321      4
754329      0

SQLFiddle上的演示

这篇关于计算SQL中的连续数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！