mysql移动平均N行

Question

我有一个简单的 MySQL 表，如下所示，用于计算汽车的 MPG。

+-------------+-------+---------+
| DATE        | MILES | GALLONS |
+-------------+-------+---------+
| JAN 25 1993 |  20.0 |    3.00 |
| FEB 07 1993 |  55.2 |    7.22 |
| MAR 11 1993 |  44.1 |    6.28 |
+-------------+-------+---------+

我可以使用 select 语句轻松计算汽车的每加仑英里数 (MPG)，但由于 MPG 在每次加注时变化很大(即您每次加注的汽油量不同)，我也想计算“移动平均线”。因此，对于任何行，MPG 是该行的 MILES/GALLON，而 MOVINGMPG 是最后 N 行的 SUM(MILES)/SUM(GALLONS)。如果此时存在少于 N 行，则仅 SUM(MILES)/SUM(GALLONS) 到该点。

是否有一个 SELECT 语句可以通过将 N 代入 select 语句来获取带有 MPG 和 MOVINGMPG 的行？

Answer 1

是的，可以使用单个 SQL 语句返回指定的结果集。

不幸的是，MySQL 不支持分析函数，这使得语句相当简单。即使 MySQL 没有支持它们的语法，也可以使用 MySQL 用户变量模拟一些分析函数。

实现指定结果集(使用单个 SQL 语句)的方法之一是使用 JOIN 操作，对每一行使用唯一的升序整数值(rownum，由查询派生并在查询中分配)。

例如:

SELECT q.rownum          AS rownum
     , q.date            AS latest_date
     , q.miles/q.gallons AS latest_mpg
     , COUNT(1)               AS cnt_rows
     , MIN(r.date)            AS earliest_date
     , SUM(r.miles)                AS rtot_miles
     , SUM(r.gallons)              AS rtot_gallons
     , SUM(r.miles)/SUM(r.gallons) AS rtot_mpg
  FROM ( SELECT @s_rownum := @s_rownum + 1 AS rownum
              , s.date
              , s.miles
              , s.gallons
           FROM mytable s
           JOIN (SELECT @s_rownum := 0) c
          ORDER BY s.date
       ) q
  JOIN ( SELECT @t_rownum := @t_rownum + 1 AS rownum
              , t.date                  
              , t.miles
              , t.gallons
           FROM mytable t
           JOIN (SELECT @t_rownum := 0) d
          ORDER BY t.date
       ) r
    ON r.rownum <= q.rownum
   AND r.rownum > q.rownum - 2
 GROUP BY q.rownum

要指定每个汇总行中包含多少行的所需“n”值在 GROUP BY 子句之前的谓词中指定。在此示例中，每个运行总计行中最多“2”行。

如果您指定值 1，您将(基本上)得到返回的原始表。

要消除任何“不完整”的运行总计行(由少于“n”行组成)，需要再次指定“n”的值，添加:

HAVING COUNT(1) >= 2

sqlfiddle 演示:http://sqlfiddle.com/#!2/52420/2

跟进:

问: 我想了解您的 SQL 语句。您的解决方案是否为数据库中的每一行选择了二十行？换句话说，如果我有 1000 行，您的语句会执行 20000 次选择吗？ (我担心性能)...

答:您对性能的关注是正确的。

要回答您的问题，不，这不会对 1,000 行执行 20,000 次选择。

性能下降来自两个(本质上相同的)内联 View (别名为 q 和 r)。 MySQL 对这些(基本上)所做的是创建临时 MyISAM 表(MySQL 称它们为“派生表”)，这些表基本上是 mytable 的副本，带有一个额外的列，每行分配一个唯一的整数值来自1 到行数。

一旦创建并填充了两个“派生”表，MySQL 就会运行外部查询，使用这两个“派生”表作为行源。 q 中的每一行与 r 中最多 n 行匹配，以计算“运行总计”英里数和加仑数。

为了获得更好的性能，您可以使用表中已有的列，而不是让查询分配唯一的整数值。例如，如果 date 列是唯一的，那么您可以计算特定天数的“运行总计”。

SELECT q.date                      AS latest_date
     , SUM(q.miles)/SUM(q.gallons) AS latest_mpg
     , COUNT(1)                    AS cnt_rows
     , MIN(r.date)                 AS earliest_date
     , SUM(r.miles)                AS rtot_miles
     , SUM(r.gallons)              AS rtot_gallons
     , SUM(r.miles)/SUM(r.gallons) AS rtot_mpg
  FROM mytable q
  JOIN mytable r
    ON r.date <= q.date
   AND r.date > q.date + INTERVAL -30 DAY
 GROUP BY q.date

(为了提高性能，您可能希望使用 date 定义适当的索引作为索引中的前导列。)

---

对于第一个查询，包含任何谓词(在内联 View 定义查询中)以减少返回的行数(例如，仅返回过去一年的日期值)将减少要处理的行数，并且也可能会提高性能。

---

同样，对于您关于为 1,000 行运行 20,000 次选择的问题...嵌套循环操作是获得相同结果集的另一种方法。对于大量行，这会表现出较慢的性能。 (另一方面，当只返回几行时，这种方法可能相当有效:

SELECT q.date                 AS latest_date
     , q.miles/q.gallons      AS latest_mpg
     , ( SELECT SUM(r.miles)/SUM(r.gallons)
           FROM mytable r
          WHERE r.date <= q.date
            AND r.date >= q.date + INTERVAL -90 DAY
       ) AS rtot_mpg
  FROM mytable q
 ORDER BY q.date