MySQL:按计数选择分组数最高的行 [英] MySQL : Selecting the rows with the highest group by count

问题描述

我有一个表，其中记录每分钟用十进制值(10,2)更新.忽视测量错误，我想拥有已插入最多的数字.为此，我尝试了:

 从"weigh_data"分组中按日期(date_time)，sensor1选择"date_time"，max(sensor1)，count(ID) 

这样我就得到了记录数`

  Datetime sensor1 count(ID)2020-03-19 11:49:12 33.22 32020-03-19 11:37:47 33.36 102020-03-20 07:32:02 32.54 4892020-03-20 00:00:43 32.56 8912020-03-20 14:20:51 32.67 52020-03-21 07:54:16 32.50 12020-03-21 00:00:58 32.54 13732020-03-21 01:15:16 32.56 92020-03-22 08:35:12 32.52 22020-03-22 00:00:40 32.54 5752020-03-22 06:50:54 32.58 1 

我真正想要的是每一天计数(ID)最高的

有人可以帮我吗?

解决方案

对于较新的MySQL(8.0和更高版本)，您可以使用

 与cte AS(选择DATE(date_time)，sensor1，RANK()超过(按日期排序(日期时间)或按计数排序(*)DESC)从`weigh_data` GROUP BY DATE(date_time)，sensor1)SELECT * FROM cte WHERE rnk = 1 

如果您只想选择一个(不确定性)联系，则可以使用要测试的DBfiddle.

i have a table with records that are updated every minute with a decimal value (10,2). To ignore measure errors i want to have the number that has been inserted the most. Therefor i tried :

SELECT date_time,max(sensor1),count(ID) FROM `weigh_data` group by day(date_time),sensor1

This way i get the number of records `

Datetime              sensor1    count(ID)
2020-03-19 11:49:12   33.22      3
2020-03-19 11:37:47   33.36      10
2020-03-20 07:32:02   32.54      489
2020-03-20 00:00:43   32.56      891
2020-03-20 14:20:51   32.67      5
2020-03-21 07:54:16   32.50      1
2020-03-21 00:00:58   32.54      1373
2020-03-21 01:15:16   32.56      9
2020-03-22 08:35:12   32.52      2
2020-03-22 00:00:40   32.54      575
2020-03-22 06:50:54   32.58      1

What i actually want is for each day one row wich has the highest count(ID)

Anyone can help me out on this ?

解决方案

With newer MySQL (8.0 and later) you can use the RANK window function to rank the rows according to the count.

Note that this will return all "ties" which means if there are 100 readings of X and 100 readings of Y (and 100 is the max), both X and Y will be returned.

WITH cte AS (
  SELECT 
    DATE(date_time), sensor1,
    RANK() OVER (PARTITION BY DATE(date_time) ORDER BY COUNT(*) DESC) rnk
  FROM `weigh_data` GROUP BY DATE(date_time), sensor1
)
SELECT * FROM cte WHERE rnk=1

If you just want to pick one (non deterministic) of the ties, you can instead use ROW_NUMBER in place of RANK

A DBfiddle to test with.

这篇关于MySQL:按计数选择分组数最高的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！