mysql - 扩展行集的平均计算

Question

我有一个包含以下列的表格:

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | bigint(20)       | NO   | PRI | NULL    | auto_increment |
| company_id       | mediumint(9)     | NO   |     | NULL    |                |
| time_stamp       | datetime         | YES  |     | NULL    |                |
| legal_date       | datetime         | YES  |     | NULL    |                |
| shamsi_date      | varchar(12)      | YES  |     | NULL    |                |
| legal_final      | decimal(10,2)    | YES  |     | NULL    |                |
| natural_sell_no  | int(10) unsigned | YES  |     | NULL    |                |
| natural_sell_vol | bigint(20)       | YES  |     | NULL    |                |
| natural_buy_no   | int(10) unsigned | YES  |     | NULL    |                |
| natural_buy_vol  | bigint(20)       | YES  |     | NULL    |                |
| legal_sell_no    | int(10) unsigned | YES  |     | NULL    |                |
| legal_sell_vol   | bigint(20)       | YES  |     | NULL    |                |
| legal_buy_no     | int(10) unsigned | YES  |     | NULL    |                |
| legal_buy_vol    | bigint(20)       | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

一些数据:

+----+------------+---------------------+-------------+-------------+-----------------+
| id | company_id | time_stamp          | shamsi_date | legal_final | natural_sell_no |
+----+------------+---------------------+-------------+-------------+-----------------+
|  1 |          1 | 2019-11-29 20:08:05 | 13980609    |    10499.00 |             232 |
|  2 |          2 | 2019-11-29 20:08:05 | 13980609    |    13876.00 |             156 |
|  3 |          3 | 2019-11-29 20:08:05 | 13980609    |     5147.00 |             491 |
|  4 |          4 | 2019-11-29 20:08:05 | 13980609    |     7050.00 |             586 |
|  5 |          5 | 2019-11-29 20:08:05 | 13980609    |     4282.00 |             831 |
|  6 |          5 | 2019-11-29 20:08:05 | 13980609    |     4282.00 |               0 |
|  7 |          6 | 2019-11-29 20:08:05 | 13980609    |    55386.00 |             297 |
|  8 |          7 | 2019-11-29 20:08:05 | 13980609    |    50713.00 |             115 |
|  9 |          8 | 2019-11-29 20:08:05 | 13980609    |     6151.00 |             224 |
| 10 |          9 | 2019-11-29 20:08:05 | 13980609    |    25397.00 |             266 |
| 11 |         10 | 2019-11-29 20:08:05 | 13980609    |    15007.00 |             623 |
| 12 |         11 | 2019-11-29 20:08:05 | 13980609    |    17779.00 |             233 |
| 13 |         14 | 2019-11-29 20:08:05 | 13980609    |    13044.00 |             225 |
| 14 |         15 | 2019-11-29 20:08:05 | 13980609    |    12216.00 |               3 |
| 15 |         16 | 2019-11-29 20:08:05 | 13980609    |    68967.00 |              61 |
| 16 |         19 | 2019-11-29 20:08:05 | 13980609    |    16974.00 |             124 |
| 17 |         21 | 2019-11-29 20:08:05 | 13980609    |     7665.00 |             333 |
| 18 |         22 | 2019-11-29 20:08:05 | 13980609    |     8444.00 |             259 |
| 19 |         23 | 2019-11-29 20:08:05 | 13980609    |    11427.00 |             171 |
| 20 |         26 | 2019-11-29 20:08:05 | 13980609    |     3872.00 |             130 |
| 21 |         27 | 2019-11-29 20:08:05 | 13980609    |     3452.00 |             488 |
| 22 |         28 | 2019-11-29 20:08:05 | 13980609    |    20752.00 |              71 |
| 23 |         29 | 2019-11-29 20:08:05 | 13980609    |     5503.00 |             503 |
| 24 |         30 | 2019-11-29 20:08:05 | 13980609    |     8638.00 |             184 |
| 25 |         31 | 2019-11-29 20:08:05 | 13980609    |    23162.00 |             178 |
| 26 |         33 | 2019-11-29 20:08:05 | 13980609    |    41389.00 |             111 |
| 27 |         34 | 2019-11-29 20:08:05 | 13980609    |    43090.00 |             148 |
| 28 |         35 | 2019-11-29 20:08:05 | 13980609    |    19552.00 |             287 |
| 29 |         36 | 2019-11-29 20:08:05 | 13980609    |    17761.00 |             255 |
| 30 |         37 | 2019-11-29 20:08:05 | 13980609    |     7550.00 |               6 |
+----+------------+---------------------+-------------+-------------+-----------------+

我使用以下查询来计算按 shamsi_date 排序的 company_id 的最后 30 行的平均值:

select company_id, avg(c.natural_buy_vol + c.legal_buy_vol) from 
(
 select company_id, natural_buy_vol, legal_buy_vol 
 from legal_daily
 where company_id = 1
 group by shamsi_date 
 order by shamsi_date desc
 limit 30
) as c;

输出为:

1   1523659.9667

这在技术上是正确的。

我的问题是:如何对所有 company_id 执行此类计算？即所需的输出将是:

1   1523659.9667
2   <some average>
3   <some other average>
..  .....

MySQL 版本是:

Server version:         8.0.14 MySQL Community Server - GPL

Answer 1

如果您想要固定行集的平均值，您可以使用 row_number 并按行集范围的整数除法进行分组

  SET @your_range = 10;
  SET @row_number = 0; 
  select  num div @your_range  my_range , avg(natural_buy_vol + legal_buy_vol)
  from (

      select (@row_number:=@row_number + 1) AS num
        , company_id, c.natural_buy_vol,  c.legal_buy_vol 
      from     (
         select company_id, natural_buy_vol, legal_buy_vol 
         from legal_daily
         where company_id = 1
         group by shamsi_date 
         order by shamsi_date desc
      )  c
  ) t 
  group by  my_range