mysql - 计算不连续的值

Question

所以我有以下结构:

+------+---------------+---------------+----+
| guid | current_level | current_value | pk |
+------+---------------+---------------+----+
| a    |           100 |            12 |  1 |
| a    |           200 |            12 |  2 |
| a    |           200 |            12 |  3 |
| a    |           200 |            12 |  4 |
| a    |           200 |            12 |  6 |
| a    |           300 |            14 |  7 |
| a    |           300 |            12 |  9 |
| a    |           300 |            12 | 10 |
| a    |           300 |            14 | 12 |
| b    |           100 |            10 |  5 |
| b    |           100 |            10 |  8 |
| b    |           200 |            12 | 11 |
| b    |           200 |            12 | 13 |
+------+---------------+---------------+----+

我想计算 a 和 b 非连续达到 200 级的次数(还有 300 次，但目前只有 200 次)即我期待的结果:

+------+-------+-------+
| guid | level | times |
+------+-------+-------+
| a    |   200 |     1 |
| b    |   200 |     1 |
+------+-------+-------+

(我不能只做独特的，因为 200 秒的单独条纹应该单独计算)

当我执行以下操作时:

set @id = "none";
set @lev = 10; -- arbitary non zero starting level

SELECT  guid, current_level , if(@id <> guid, @lev := 10, 0) AS useless, case when @id <> guid then @id := guid else 0 end AS useless2
         , (case when (current_level = 200 AND current_level <> @lev) then 1 else 0 end) as TIMES
        , if(current_level = 200 AND current_level <> @lev, @lev := current_level, 0) AS useless3

 FROM (SELECT * FROM sensor_logs order by guid) as T

我得到:

+------+---------------+---------+----------+----------+----------+
| guid | current_level | useless | useless2 | TIMES    | useless3 |
+------+---------------+---------+----------+----------+----------+
| a    |           100 |      10 | a        |        0 |        0 |
| a    |           200 |       0 | 0        |        1 |      200 |
| a    |           200 |       0 | 0        |        0 |        0 |
| a    |           200 |       0 | 0        |        0 |        0 |
| a    |           200 |       0 | 0        |        0 |        0 |
| a    |           300 |       0 | 0        |        0 |        0 |
| a    |           300 |       0 | 0        |        0 |        0 |
| a    |           300 |       0 | 0        |        0 |        0 |
| a    |           300 |       0 | 0        |        0 |        0 |
| b    |           100 |      10 | b        |        0 |        0 |
| b    |           100 |       0 | 0        |        0 |        0 |
| b    |           200 |       0 | 0        |        1 |      200 |
| b    |           200 |       0 | 0        |        0 |        0 |
+------+---------------+---------+----------+----------+----------+

所以现在对 TIMES 列求和并按 guid 分组应该可以解决问题，即:

set @id = "none";
set @lev = 10; -- arbitary non zero starting level

SELECT  guid, current_level , if(@id <> guid, @lev := 10, 0) AS useless, case when @id <> guid then @id := guid else 0 end AS useless2
         , sum(case when (current_level = 200 AND current_level <> @lev) then 1 else 0 end) as TIMES
        , if(current_level = 200 AND current_level <> @lev, @lev := current_level, 0) AS useless3

 FROM (SELECT * FROM sensor_logs order by guid) as T
 GROUP BY guid

但我得到以下信息:

+------+---------------+---------+----------+----------+----------+
| guid | current_level | useless | useless2 | TIMES    | useless3 |
+------+---------------+---------+----------+----------+----------+
| a    |           100 |      10 | a        |        4 |        0 |
| b    |           100 |      10 | b        |        2 |        0 |
+------+---------------+---------+----------+----------+----------+

我不明白为什么对具有两个 1(每个 guid 一个)的列求和会得到 4 和 2。

是我做错了什么吗？还是更多与查询(和求和函数)执行方式的底层机制有关？

Answer 1

你的第一个查询你最好这样写:

SELECT  guid, current_level , if(@id <> guid, @lev := 10, 0) AS useless, case when @id <> guid then @id := guid else 0 end AS useless2
         , (case when (current_level = 200 AND current_level <> @lev) then 1 else 0 end) as TIMES
        , if(current_level = 200 AND current_level <> @lev, @lev := current_level, 0) AS useless3

 FROM sensor_logs
 , (SELECT @id := 'none', @lev := 10) var_init_subquery
 ORDER BY guid

不仅在需要时显式地进行排序更干净，而不是在子查询中进行，在子查询中进行排序也可能导致糟糕的执行计划(这意味着在临时表的情况下性能不佳)。

对于您的最终结果，您不应直接应用 GROUP BY 等。 SELECT(以及您的变量和计算)在 GROUP BY 之后进行评估。要在计算后进行分组，请将查询放在子查询中:

SELECT guid, SUM(times) FROM (
    SELECT  guid, current_level , if(@id <> guid, @lev := 10, 0) AS useless, case when @id <> guid then @id := guid else 0 end AS useless2
             , (case when (current_level = 200 AND current_level <> @lev) then 1 else 0 end) as TIMES
            , if(current_level = 200 AND current_level <> @lev, @lev := current_level, 0) AS useless3

     FROM sensor_logs
     , (SELECT @id := 'none', @lev := 10) var_init_subquery
     ORDER BY guid
) sq
GROUP BY guid