gpt4 book ai didi

mysql - 如何获取每组中的最大值和最小值以及它们的时间?

转载 作者:行者123 更新时间:2023-11-29 01:35:24 29 4
gpt4 key购买 nike

假设我有一个包含 Id、NumericValue 和 UTCTimestamp 列的表 MyTable。我正在尝试按时间戳的小时对表 MyTable 的结果进行分组,并返回每个组的最大数值及其相关时间戳以及每个组的最小数值及其相关时间戳值。

现在,我可以通过以下查询来解决问题的第一部分:

SELECT 
HOUR(t.UTCTimestamp) AS `Hour`,
t.NumericValue AS MaximumValue,
t.UTCTimestamp AS MaximumValueTime
FROM MyTable t
INNER JOIN (
SELECT HOUR(t2.UTCTimestamp) AS `Hour`, MAX(t2.NumericValue) AS NumericValue
FROM MyTable t2
GROUP BY HOUR(t2.UTCTimestamp)
) maxNumericValue ON HOUR(t.UTCTimestamp) = maxNumericValue.`Hour` AND t.NumericValue = maxNumericValue.NumericValue
GROUP BY HOUR(t.UTCTimestamp);

灵感来自 this answer .

Here's an MVCE .

如何显示每个组的最小值以及与之关联的时间戳?

最佳答案

从 MySQL 8.0 开始,您可以使用 ROW_NUMBER:

WITH cte AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY HOUR(UTCTimestamp)
ORDER BY UTCTimestamp ASC) AS rn
,ROW_NUMBER() OVER(PARTITION BY HOUR(UTCTimestamp)
ORDER BY UTCTimestamp DESC) AS rn2
FROM MyTable
)
SELECT HOUR(c1.UTCTimestamp),
c1.ID, c1.NumericValue, c1.UTCTimestamp, -- min row
c2.ID, c2.NumericValue, c2.UTCTimestamp -- max row
FROM cte c1
JOIN cte c2
ON HOUR(c1.UTCTimestamp) = HOUR(c2.UTCTimestamp)
AND c1.rn=1
AND c2.rn2=1
ORDER BY HOUR(c1.UTCTimestamp) ASC;

DBFiddle Demo

关于mysql - 如何获取每组中的最大值和最小值以及它们的时间?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50160739/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com