GROUP BY ID范围？ [英] GROUP BY ID range?

问题描述

给定一个这样的数据集;

  + ----- + --------- ------------ + -------- + 
 | id |日期|结果| 
 + ----- + --------------------- + -------- + 
 | 121 | 2009-07-11 13:23:24 | -1 | 
 | 122 | 2009-07-11 13:23:24 | -1 | 
 | 123 | 2009-07-11 13:23:24 | -1 | 
 | 124 | 2009-07-11 13:23:24 | -1 | 
 | 125 | 2009-07-11 13:23:24 | -1 | 
 | 126 | 2009-07-11 13:23:24 | -1 | 
 | 127 | 2009-07-11 13:23:24 | -1 | 
 | 128 | 2009-07-11 13:23:24 | -1 | 
 | 129 | 2009-07-11 13:23:24 | -1 | 
 | 130 | 2009-07-11 13:23:24 | -1 | 
 | 131 | 2009-07-11 13:23:24 | -1 | 
 | 132 | 2009-07-11 13:23:24 | -1 | 
 | 133 | 2009-07-11 13:23:24 | -1 | 
 | 134 | 2009-07-11 13:23:24 | -1 | 
 | 135 | 2009-07-11 13:23:24 | -1 | 
 | 136 | 2009-07-11 13:23:24 | -1 | 
 | 137 | 2009-07-11 13:23:24 | -1 | 
 | 138 | 2009-07-11 13:23:24 | 1 | 
 | 139 | 2009-07-11 13:23:24 | 0 | 
 | 140 | 2009-07-11 13:23:24 | -1 | 
 + ----- + --------------------- + -------- + 
  

我会如何按照第5天的记录对结果进行分组。上述结果是实时数据的一部分，表中有超过10万个结果行，并且它在不断增长。基本上我想测量随着时间的变化，所以想要每个X记录取得结果的总和。在真实的数据中，我会一直做到100或1000，但对于上面的数据可能每5分钟一次。

如果我可以按日期排序，我会做类似这个;

  SELECT 
 DATE_FORMAT（日期，'％h％i'）ym，
 COUNT结果）'Total Games'，
 SUM（结果）作为'Score'
 FROM nn_log 
 GROUP BY ym; 
  

我无法弄清楚用数字做类似的事情。订单按日期排序，但我希望将每个x结果的数据分开。假设没有空白行是安全的。

使用您可以执行多个数据的数据完成上述操作，就像是;

  SELECT SUM（result）FROM table LIMIT 0,5; 
 SELECT SUM（result）FROM table LIMIT 5,5; 
 SELECT SUM（result）FROM table LIMIT 10,5; 
  

这显然不是解决更大问题的好方法。我可以只写一个循环，但我想减少查询次数。

解决方案

如何...

p>

  SELECT 
 floor（id / 5）ym，
 COUNT（结果）'Total Games'，
 SUM（result）as'Score'
 FROM nn_log 
 GROUP BY ym; 
  

（我假设id是相关的）

在查询中这是相同的想法，只使用ID来代替当天的组。

Given a data set like this;

+-----+---------------------+--------+
| id  | date                | result |
+-----+---------------------+--------+
| 121 | 2009-07-11 13:23:24 |     -1 | 
| 122 | 2009-07-11 13:23:24 |     -1 | 
| 123 | 2009-07-11 13:23:24 |     -1 | 
| 124 | 2009-07-11 13:23:24 |     -1 | 
| 125 | 2009-07-11 13:23:24 |     -1 | 
| 126 | 2009-07-11 13:23:24 |     -1 | 
| 127 | 2009-07-11 13:23:24 |     -1 | 
| 128 | 2009-07-11 13:23:24 |     -1 | 
| 129 | 2009-07-11 13:23:24 |     -1 | 
| 130 | 2009-07-11 13:23:24 |     -1 | 
| 131 | 2009-07-11 13:23:24 |     -1 | 
| 132 | 2009-07-11 13:23:24 |     -1 | 
| 133 | 2009-07-11 13:23:24 |     -1 | 
| 134 | 2009-07-11 13:23:24 |     -1 | 
| 135 | 2009-07-11 13:23:24 |     -1 | 
| 136 | 2009-07-11 13:23:24 |     -1 | 
| 137 | 2009-07-11 13:23:24 |     -1 | 
| 138 | 2009-07-11 13:23:24 |      1 | 
| 139 | 2009-07-11 13:23:24 |      0 | 
| 140 | 2009-07-11 13:23:24 |     -1 | 
+-----+---------------------+--------+

How would I go about grouping the results by day 5 records at a time. The above results is part of the live data, there is over 100,000 results rows in the table and its growing. Basically I want to measure the change over time, so want to take a SUM of the result every X records. In the real data I'll be doing it ever 100 or 1000 but for the data above perhaps every 5.

If i could sort it by date I would do something like this;

SELECT
   DATE_FORMAT(date, '%h%i') ym,
   COUNT(result) 'Total Games', 
   SUM(result) as 'Score'
FROM nn_log
GROUP BY ym;

I can't figure out a way of doing something similar with numbers. The order is sorted by the date but I hope to split the data up every x results. It's safe to assume there are no blank rows.

Doing it above with the data you could do multiple selects like;

SELECT SUM(result) FROM table LIMIT 0,5;
SELECT SUM(result) FROM table LIMIT 5,5;
SELECT SUM(result) FROM table LIMIT 10,5;

Thats obviously not a very good way to scale up to a bigger problem. I could just write a loop but I'd like to reduce the number of queries.

解决方案

How about...

SELECT
   floor(id / 5) ym,
   COUNT(result) 'Total Games', 
   SUM(result) as 'Score'
FROM nn_log
GROUP BY ym;

(I'm assuming that the id is correlative)

This is the same idea in your query, only using the ID to group instead of the day.

这篇关于GROUP BY ID范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！