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

debugcn 发表于 Dev

Midhunraj

LogDateAndTime	批处理日期	标记字母	累加器	预期结果
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）列具有唯一的日期时间条目。该BatchDate列包含整个批次相同的日期时间值。我需要为TagLetter = A的每个实例计算MAX（Totaliser）-MIN（Totaliser）的总和，以便我应该忽略TagLetter = B中的值。在这种情况下，我的ExpectedResult为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，这不是预期的结果。有人可以帮助我取得正确的结果吗？

福帕斯

首先创建连续的组'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

参见演示。
结果：

> 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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。