sum - 计算 MySQL8 中重复条目组的最大值和最小值之和

Question

日志日期和时间
批号
标签信
累加器
预期结果


10-11-2020 09:06:14
10-11-2020 08:29:55
一种
6319
31

10-11-2020 09:06:24
10-11-2020 08:29:55
一种
6337
31

10-11-2020 09:08:14
10-11-2020 08:29:55
乙
6355
31

10-11-2020 09:08:24
10-11-2020 08:29:55
乙
6372
31

10-11-2020 09:08:34
10-11-2020 08:29:55
乙
6378
31

10-11-2020 09:08:44
10-11-2020 08:29:55
一种
6383
31

10-11-2020 09:09:14
10-11-2020 08:29:55
一种
6388
31

10-11-2020 09:09:24
10-11-2020 08:29:55
一种
6396
31

10-11-2020 09:09:34
10-11-2020 08:29:55
乙
6409
31

10-11-2020 09:09:44
10-11-2020 08:29:55
乙
6426
31

10-11-2020 09:10:24
10-11-2020 08:29:55
乙
6442
31


上表有 LogDateAndTime(Primary_Key) 具有唯一日期时间条目的列。 批号 列在整个批次中保存相同的日期时间值。我需要为 TagLetter=A 的每个实例计算 MAX(Totaliser)-MIN(Totaliser) 的总和，以便我应该忽略 TagLetter=B 中的值。在这种情况下，我的预期结果将是 SUM[(6337-6319)+(6396-6383)]= 31 .我尝试了以下查询，但没有得到预期的结果。

SELECT SUM(
           CASE 
             WHEN TagLetter='A' THEN MAX(Totaliser)-MIN(Totaliser) 
             ELSE 0.0 
           END
          ) OVER (PARTITION BY BatchDate) AS ExpectedResult

在这种情况下，它正在计算 6396-6319= 77 这不是预期的结果。有人可以帮我得到正确的结果吗？

Answer 1

首先创建连续的组'A' s 带窗函数 LAG()和 SUM()然后在这些组上聚合:

WITH cte AS (
  SELECT DISTINCT SUM(MAX(Totaliser) - MIN(Totaliser)) OVER () ExpectedResult
  FROM (
    SELECT *, SUM(flag) OVER (ORDER BY LogDateAndTime) grp
    FROM (
      SELECT *, LAG(TagLetter, 1, '') OVER (ORDER BY LogDateAndTime) <> 'A' flag
      FROM tablename 
    ) t
    WHERE TagLetter = 'A'
  ) t
  GROUP BY grp
)
SELECT t.*, c.ExpectedResult
FROM tablename t CROSS JOIN cte c

或者，如果您想要每个 BatchDate 的结果:

WITH cte AS (
  SELECT DISTINCT BatchDate,
         SUM(MAX(Totaliser) - MIN(Totaliser)) OVER () ExpectedResult
  FROM (
    SELECT *, SUM(flag) OVER (PARTITION BY BatchDate ORDER BY LogDateAndTime) grp
    FROM (
      SELECT *, LAG(TagLetter, 1, '') OVER (PARTITION BY BatchDate ORDER BY LogDateAndTime) <> 'A' flag
      FROM tablename 
    ) t
    WHERE TagLetter = 'A'
  ) t
  GROUP BY BatchDate, grp
)
SELECT t.*, c.ExpectedResult
FROM tablename t LEFT JOIN cte c
ON c.BatchDate = t.BatchDate

见 demo .
结果:

> LogDateAndTime      | BatchDate           | TagLetter | Totaliser | ExpectedResult
> :------------------ | :------------------ | :-------- | --------: | -------------:
> 10-11-2020 09:06:14 | 10-11-2020 08:29:55 | A         |      6319 |             31
> 10-11-2020 09:06:24 | 10-11-2020 08:29:55 | A         |      6337 |             31
> 10-11-2020 09:08:14 | 10-11-2020 08:29:55 | B         |      6355 |             31
> 10-11-2020 09:08:24 | 10-11-2020 08:29:55 | B         |      6372 |             31
> 10-11-2020 09:08:34 | 10-11-2020 08:29:55 | B         |      6378 |             31
> 10-11-2020 09:08:44 | 10-11-2020 08:29:55 | A         |      6383 |             31
> 10-11-2020 09:09:14 | 10-11-2020 08:29:55 | A         |      6388 |             31
> 10-11-2020 09:09:24 | 10-11-2020 08:29:55 | A         |      6396 |             31
> 10-11-2020 09:09:34 | 10-11-2020 08:29:55 | B         |      6409 |             31
> 10-11-2020 09:09:44 | 10-11-2020 08:29:55 | B         |      6426 |             31
> 10-11-2020 09:10:24 | 10-11-2020 08:29:55 | B         |      6442 |             31