如何获得每个组的最大值和最小值以及它们的时间？

pprl5pva 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(375)

假设我有一个表mytable，其中包含id、numericvalue和utctimestamp列。我试图将表mytable的结果按时间戳的小时分组，并返回每个组与其关联的时间戳对应的最大numericvalue以及每个组与其关联的时间戳对应的最小numericvalue。
现在，我可以通过以下查询实现问题的第一部分：

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);

这个答案启发了我们。
这是mvce。
如何显示每个组的最小值以及与之相关联的时间戳？

sql mysql mysql-5.6 greatest-n-per-group

来源：https://stackoverflow.com/questions/50160739/how-to-get-the-maximum-and-minimum-values-in-each-group-as-well-as-their-times

3条答案

按热度按时间

pxq42qpu1#

可以两次联接到mytable（并且只能使用一个聚合子查询）

SELECT bounds.`Hour`
    , minT.NumericValue AS MinValue
    , minT.UTCTimestamp AS MinTime
    , maxT.NumericValue AS MaximumValue
    , maxT.UTCTimestamp AS MaximumValueTime
FROM (
    SELECT HOUR(t2.UTCTimestamp) AS `Hour`
         , MAX(t2.NumericValue) AS maxValue
         , MIN(t2.NumericValue) AS minValue
    FROM MyTable t2
    GROUP BY HOUR(t2.UTCTimestamp)
) bounds 
LEFT JOIN MyTable minT ON bounds.`Hour` = HOUR(minT.UTCTimestamp)
   AND bounds.minValue = minT.NumericValue
LEFT JOIN MyTable maxT ON bounds.`Hour` = HOUR(maxT.UTCTimestamp)
   AND bounds.maxValue = maxT.NumericValue
;

赞(0）回复(0）举报 2021-06-21

ukqbszuj2#

采用相同的技术，但至少：

select a.*, b.MinimumValueTime from (
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))a
join
(
SELECT 
    HOUR(t.UTCTimestamp) AS `Hour`, 
    t.NumericValue AS MinimumValue,
    t.UTCTimestamp AS MinimumValueTime
FROM MyTable t
INNER JOIN (
    SELECT HOUR(t2.UTCTimestamp) AS `Hour`, MIN(t2.NumericValue) AS NumericValue
    FROM MyTable t2
    GROUP BY HOUR(t2.UTCTimestamp)
) minNumericValue ON HOUR(t.UTCTimestamp) = minNumericValue.`Hour` AND t.NumericValue = minNumericValue.NumericValue
GROUP BY HOUR(t.UTCTimestamp))b on a.hour=b.hour

赞(0）回复(0）举报 2021-06-21

6bc51xsx3#

从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演示

赞(0）回复(0）举报 2021-06-21

我来回答

如何获得每个组的最大值和最小值以及它们的时间？

3条答案

相关问题

热门标签

最新问答