我有一张看起来像这样的表:
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'
可能会给出:
+------------+--------------+----------------+--------------+----------------+
| Date | Data1_MTDAvg | Data1_20DayAvg | Data2_MTDAvg | Data2_20DayAvg |
+------------+--------------+----------------+--------------+----------------+
| 2012-01-23 | 697.71 | 566.34 | 601.37 | 192.13 |
+------------+--------------+----------------+--------------+----------------+
where Data1_MTDAvg
shows avg(DailyData1)
对于 1 月 23 日之前的 1 月每个日期,以及Data1_20DayAvg
显示相同的内容,但针对表中的前 20 个日期。我不是 SQL 忍者,但我认为最好的方法是通过子查询。 MTD 平均值很简单:
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 天的所有记录。我找到的唯一解决方案是使用嵌套子查询首先限制所选记录,然后取平均值。
单独的子查询适用于单独的硬编码日期:
select avg(t2.DailyData1) Data1_20DayAvg
from (select DailyData1
from DataTable
where Date <= '2012-01-23'
order by Date desc
limit 0,20) t2;
但尝试将其嵌入作为更大查询的一部分会失败:
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
子查询中的子句。有没有办法限制我的聚合选择中的数据而不诉诸子查询,以解决这两个问题?