MySQL-在相关子查询中限制聚合数据时，嵌套子查询的替代方法 [英] MySQL - alternatives to nested subqueries when limiting aggregate data in a correlated subquery

问题描述

我有一个看起来像这样的表:

I have a table that looks something like this:

DataTable
+------------+------------+------------+
| Date       | DailyData1 | DailyData2 |
+------------+------------+------------+
| 2012-01-23 |     146.30 |     212.45 |
| 2012-01-20 |     554.62 |     539.11 |
| 2012-01-19 |     710.69 |     536.35 |
+------------+------------+------------+

我正在尝试创建一个视图(称为AggregateView)，该视图将针对每个日期和每个数据列显示一些不同的聚合.例如，select * from AggregateView where Date = '2012-01-23'可能会给出:

I'm trying to create a view (call it AggregateView) that will, for each date and for each data column, show a few different aggregates. For example, select * from AggregateView where Date = '2012-01-23' might give:

+------------+--------------+----------------+--------------+----------------+
| Date       | Data1_MTDAvg | Data1_20DayAvg | Data2_MTDAvg | Data2_20DayAvg |
+------------+--------------+----------------+--------------+----------------+
| 2012-01-23 |       697.71 |         566.34 |       601.37 |         192.13 |
+------------+--------------+----------------+--------------+----------------+

其中，Data1_MTDAvg在1月23日之前的1月中的每个日期显示avg(DailyData1)，而Data1_20DayAvg在表中的前20个日期中显示相同的内容.我不是SQL忍者，但我一直认为最好的方法是通过子查询. MTD平均值很容易:

where Data1_MTDAvg shows avg(DailyData1) for each date in January prior to Jan 23, and Data1_20DayAvg shows the same but for the prior 20 dates in the table. I'm no SQL ninja, but I was thinking that the best way to do this would be via subqueries. The MTD average is easy:

select t1.Date, (select avg(t2.DailyData1) 
                 from DataTable t2 
                 where t2.Date <= t1.Date 
                   and month(t2.Date) = month(t1.Date) 
                   and year(t2.Date) = year(t1.Date)) Data1_MTDAvg 
from DataTable t1;

但是由于需要限制返回结果的数量，我无法达到20天的平均水平.请注意，表中的日期是不规则的，因此我不能使用日期间隔.我需要表中的最后20条记录，而不仅仅是过去20天的所有记录.我发现的唯一解决方案是使用嵌套子查询首先限制所选记录，然后取平均值.

But I'm getting hung up on the 20-day average due to the need to limit the number of results returned. Note that the dates in the table are irregular, so I can't use a date interval; I need the last twenty records in the table, rather than just all records over the last twenty days. The only solution I've found is to use a nested subquery to first limit the records selected, and then take the average.

单独，子查询适用于各个硬编码日期:

Alone, the subquery works for individual hardcoded dates:

select avg(t2.DailyData1) Data1_20DayAvg 
from (select DailyData1 
      from DataTable 
      where Date <= '2012-01-23' 
      order by Date desc 
      limit 0,20) t2;

但是尝试将其嵌入到更大的查询中会炸掉:

But trying to embed this as part of the greater query blows up:

select t1.Date, (select avg(t2.DailyData1) Data1_20DayAvg 
                 from (select DailyData1 
                       from DataTable 
                       where Date <= t1.Date 
                       order by Date desc 
                       limit 0,20) t2) 
from DataTable t1;
ERROR 1054 (42S22): Unknown column 't1.Date' in 'where clause'

通过搜索，我得到的印象是您不能将相关子查询用作from子句的一部分，我认为这就是问题所在.另一个问题是我不确定MySQL是否会在子查询中接受包含from子句的视图定义.为了解决这两个问题，是否有一种方法可以限制我的聚合选择中的数据而不求助于子查询?

From searching around I get the impression that you can't use correlated subqueries as part of a from clause, which I think is where the problem is here. The other issue is that I'm not sure if MySQL will accept a view definition containing a from clause in a subquery. Is there a way to limit the data in my aggregate selection without resorting to subqueries, in order to work around these two issues?

推荐答案

否，您不能在FROM子句中使用经过校正的子查询.但是您可以在ON条件下使用它们:

No, you can't use correalted subqueries in the FROM clause. But you can use them in the ON conditions:

SELECT AVG(d.DailyData1) Data1_20DayAvg 
       --- other aggregate stuff on d (Datatable)
FROM 
      ( SELECT '2012-01-23' AS DateChecked
      ) AS dd
  JOIN
      DataTable AS d
    ON
      d.Date <= dd.DateChecked
    AND
      d.Date >= COALESCE( 
      ( SELECT DailyData1 
        FROM DataTable AS last20 
        WHERE Date <= dd.DateChecked 
          AND (other conditions for last20)
        ORDER BY Date DESC 
        LIMIT 1 OFFSET 19
      ), '1001-01-01'   )
WHERE (other conditions for d Datatable)

类似，许多日期:

SELECT dd.DateChecked 
     , AVG(d.DailyData1) Data1_20DayAvg 
       --- other aggregate stuff on d (Datatable)
FROM 
      ( SELECT DISTINCT Date AS DateChecked
        FROM DataTable 
      ) AS dd
  JOIN
      DataTable AS d
    ON
      d.Date <= dd.DateChecked
    AND
      d.Date >= COALESCE( 
      ( SELECT DailyData1 
        FROM DataTable AS last20
        WHERE Date <= dd.DateChecked 
          AND (other conditions for last20)
        ORDER BY Date DESC 
        LIMIT 1 OFFSET 19
      ), '1001-01-01'   )
WHERE (other conditions for d Datatable)
GROUP BY 
      dd.DateChecked 

两个查询均假设Datatable.Date具有UNIQUE约束.

Both queries assume that Datatable.Date has a UNIQUE constraint.

这篇关于MySQL-在相关子查询中限制聚合数据时，嵌套子查询的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！