选择组成每天最大聚合值的个别行。

huangapple
go评论96阅读模式
英文:

SELECT the individual rows that make up the aggregate MAX per day

问题

我的 dbfiddle

我有一个表格，每隔5分钟记录多个设备的用户数量。每个设备在每个时间戳下都有自己的行。正如我在我的示例中展示的那样，我创建了一个查询，用于对特定日期的每个create_time的所有行求和。然后，我添加了一个查询来查找最大的总和。接着，我添加了一个查询来查找具有最大总和的create_time。最后，我将它们全部组合在一起，以获取构成具有最大总和的create_time的个别行。很好！

虽然它适用于特定日期，但我只是无法弄清楚如何处理多个日期。我以为我可以在那里加入一些按日期(create_time)分组的内容，但显然我还没有掌握带有having的分组。从这一点上来说，我不能说我尝试了其他任何东西，因为我只是无法理解它。

任何帮助将不胜感激！

编辑：
我在示例的底部添加了一个查询，显示了我希望得到的结果。对于“最近3天”的演示来说还好，但选项可以回溯到12个月，我不认为使用365个UNION是正确的方法 选择组成每天最大聚合值的个别行。

英文:

My dbfiddle

I have a table that stores user counts every 5 minutes for multiple devices. Each device has its own row per timestamp. As I show in my fiddle, I created a query to sum all of the rows per create_time for a particular day. I then added to the query to find the max sum. I then added to find the create_time of the group that has that max sum. I then combined them all together to get the individual rows that make up the group for the create_time that had the max sum. Great!

While it works for a specific day, I just can't figure out how to do it for multiple days. I thought I could throw some group by date(create_time)'s in there but I clearly am not grasping group by with having. From this point, I can't say I've tried anything else because I just can't wrap my brain around it.

Any help would be much appreciated!

EDIT:
I have added a query to the bottom of the fiddle that shows what I want my results to be. It's fine for a demo of "the last 3 days" but the options can go back 12 months and I don't think 365 UNIONS would be the way to go 选择组成每天最大聚合值的个别行。

答案1

得分: 1

使用FIRST_VALUE()窗口函数来获取每天users总和最大的create_time对应的值：

  1. WITH cte AS (
  2.   SELECT DISTINCT FIRST_VALUE(create_time) OVER (PARTITION BY DATE(create_time) ORDER BY SUM(users) DESC) create_time 
  3.   FROM test 
  4.   GROUP BY create_time
  5. )
  6. SELECT id, create_time AS stat_time, users
  7. FROM test
  8. WHERE create_time IN (SELECT create_time FROM cte);

查看demo。<br/>

英文:

Use FIRST_VALUE() window function to get for each day the value of each day's create_time with the greatest sum of users:

  1. WITH cte AS (
  2.   SELECT DISTINCT FIRST_VALUE(create_time) OVER (PARTITION BY DATE(create_time) ORDER BY SUM(users) DESC) create_time 
  3.   FROM test 
  4.   GROUP BY create_time
  5. )
  6. SELECT id, create_time AS stat_time, users
  7. FROM test
  8. WHERE create_time IN (SELECT create_time FROM cte);

See the demo.<br/>

答案2

得分: 0

这段代码的中文翻译如下：

这是你正在寻找的吗？此代码仅按日期对用户求和。

  1. select date(create_time) as date_create,
  2. sum(users) as ut
  3. from test
  4. group by date_create;
英文:

is this what you're looking for? this code groups the users sums by date only

  1. select date(create_time) as date_create,
  2. sum(users) as ut
  3. from test
  4. group by date_create;

答案3

得分: 0

我不认为我完全理解你的问题。从我理解的部分来看，你可以使用存储过程和游标在数据库中为每个日期运行查询。

  1. CREATE PROCEDURE AggregateMaxPerDay()
  2. BEGIN
  3.   DECLARE done INT DEFAULT FALSE;
  4.   DECLARE uniqueDate DATE;
  5.   DECLARE cur CURSOR FOR SELECT DISTINCT DATE(create_time) FROM test;
  6.   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  7.   OPEN cur;
  8.  read_loop: LOOP
  9.     FETCH cur INTO uniqueDate;
  10.     IF done THEN
  11.       LEAVE read_loop;
  12.     END IF;
  13.     SET @query = CONCAT('SELECT DISTINCT id, create_time AS stat_time, users
  14.                         FROM test
  15.                         WHERE create_time = (
  16.                           SELECT create_time FROM (
  17.                             SELECT create_time, SUM(users) AS ut
  18.                             FROM test
  19.                             WHERE DATE(create_time) = "', uniqueDate, '"
  20.                             GROUP BY create_time
  21.                           ) t2
  22.                           WHERE t2.ut = (
  23.                             SELECT MAX(t.ut) AS ut
  24.                             FROM (
  25.                               SELECT create_time, SUM(users) AS ut
  26.                               FROM test
  27.                               WHERE DATE(create_time) = "', uniqueDate, '"
  28.                               GROUP BY create_time
  29.                             ) t
  30.                           )
  31.                         );');
  32.     PREPARE stmt FROM @query;
  33.     EXECUTE stmt;
  34.     DEALLOCATE PREPARE stmt;
  35.   END LOOP;
  36.  CLOSE cur;
  37. END;

然后，你可以调用 CALL AggregateMaxPerDay(); 来执行这个存储过程。

英文:

I don't think that I completely understand your question. From what I understand you can run your query for each date in the database by using a procedure and cursors.

  1. CREATE PROCEDURE AggregateMaxPerDay()
  2. BEGIN
  3.   DECLARE done INT DEFAULT FALSE;
  4.   DECLARE uniqueDate DATE;
  5.   DECLARE cur CURSOR FOR SELECT DISTINCT DATE(create_time) FROM test;
  6.   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  7.   OPEN cur;
  8.  read_loop: LOOP
  9. FETCH cur INTO uniqueDate;
  10. IF done THEN
  11.   LEAVE read_loop;
  12. END IF;
  13. SET @query = CONCAT(&#39;SELECT DISTINCT id, create_time AS stat_time, users
  14.                     FROM test
  15.                     WHERE create_time = (
  16.                       SELECT create_time FROM (
  17.                         SELECT create_time, SUM(users) AS ut
  18.                         FROM test
  19.                         WHERE DATE(create_time) = &quot;&#39;, uniqueDate, &#39;&quot;
  20.                         GROUP BY create_time
  21.                       ) t2
  22.                       WHERE t2.ut = (
  23.                         SELECT MAX(t.ut) AS ut
  24.                         FROM (
  25.                           SELECT create_time, SUM(users) AS ut
  26.                           FROM test
  27.                           WHERE DATE(create_time) = &quot;&#39;, uniqueDate, &#39;&quot;
  28.                           GROUP BY create_time
  29.                         ) t
  30.                       )
  31.                     );&#39;);
  32. PREPARE stmt FROM @query;
  33. EXECUTE stmt;
  34. DEALLOCATE PREPARE stmt;
  35. END LOOP;
  36.  CLOSE cur;
  37. END;
  40. CALL AggregateMaxPerDay();

huangapple
  • 本文由 发表于 2023年5月25日 02:51:19
  • 转载请务必保留本文链接：https://go.coder-hub.com/76326597.html
  • common-table-expression
  • group-by
  • mysql
  • sql
  • window-functions
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定