我正在尝试在SQLite中创建累积移动平均线。回顾一下，在累积移动平均值（CMA）中，数据以有序基准流的形式到达，我希望获得直到当前基准点为止的所有数据的平均值。我的桌子看起来像：Continent,Date,Measure,ValueAntarctica,03/01/2019 12:00:00 AM,Passengers,346158South America,03/01/2019 12:00:00 AM,Ships,6483South America,03/01/2019 12:00:00 AM,Flights,19Antarctica,02/01/2019 12:00:00 AM,Passengers,172163South America,02/01/2019 12:00:00 AM,Cargo Ships,1319Antarctica,01/01/2019 12:00:00 AM,Passengers,56810诸如1或2之类的先前解决方案描述了每月或每周的移动平均值。但是，尽管我可以每月保持这个平均值，但我正在尝试建立一个累积平均值。我尝试这样做：SELECT T1.Date, AVG(T2.VALUE) from my_table AS T1 INNER JOIN my_table AS T2 ON datetime(T1.Date, '-1 Month') <= datetime(T2.Date) AND datetime(T1.Date, '+1 Month') >= datetime(T2.Date) GROUP BY T1.date;但是当我使用sqlite时，datetime操作会产生错误：sqlite does not have operation datetime.我什至尝试了简单的命令：SELECT AVG(VALUE) FROM my_table GROUP BY MEASURE, DATE, CONTINENT，但是按移动平均线进行了分组，但这并不能解决我的问题。我要做什么：Continent,Date,Measure,Value,AverageAntarctica,03/01/2019 12:00:00 AM,Passengers,346158,114487South America,03/01/2019 12:00:00 AM,Ships,6483,0South America,03/01/2019 12:00:00 AM,Flights,19,0Antarctica,02/01/2019 12:00:00 AM,Passengers,172163,56810South America,02/01/2019 12:00:00 AM,Cargo Ships,1319,0Antarctica,01/01/2019 12:00:00 AM,Passengers,56810,0Average列是与欧洲大陆的总穿越次数的连续月平均值，以及之前所有月份的穿越平均值。因此，要计算第一行的平均值（即，3月之前的所有月份中穿越南极洲的全部乘客的月均运行平均值），您需要采用February 156,891 + 15,272 = 172,163和，并将其四舍五入到最接近的整数January 56,810。有没有更简单的方法可以做到这一点？ (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 首先，修复您的时间戳，使其采用可以排序的格式，例如sqlite date and time functions支持的ISO-8601。代替03/01/2019 12:00:00 AM，使用2019-03-01 00:00:00（如果您不关心时间，只关心日期，则使用2019-03-01）。这使您的CSV数据看起来像：Continent,Date,Measure,ValueAntarctica,2019-03-01 00:00:00,Passengers,346158South America,2019-03-01 00:00:00,Ships,6483South America,2019-03-01 00:00:00,Flights,19Antarctica,2019-02-01 00:00:00,Passengers,172163South America,2019-02-01 00:00:00,Cargo Ships,1319Antarctica,2019-01-01 00:00:00,Passengers,56810然后，您可以使用窗口函数（在Sqlite 3.25中引入）轻松计算前几个月的累计平均值：SELECT continent, date, measure, value, cast(round(ifnull(avg(value) OVER (PARTITION BY continent, measure ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 0) AS INTEGER) AS AverageFROM crossingsORDER BY date DESC, continent, measure DESC;这使Continent Date Measure Value Average---------- ------------------- ---------- ---------- ----------Antarctica 2019-03-01 00:00:00 Passengers 346158 114487South Amer 2019-03-01 00:00:00 Ships 6483 0South Amer 2019-03-01 00:00:00 Flights 19 0Antarctica 2019-02-01 00:00:00 Passengers 172163 56810South Amer 2019-02-01 00:00:00 Cargo Ship 1319 0Antarctica 2019-01-01 00:00:00 Passengers 56810 0如果停留在没有窗口功能支持的较旧版本上，则可以使用相关子查询来计算累积平均值：SELECT continent, date, measure, value, ifnull((SELECT cast(round(avg(c2.value), 0) AS INTEGER) FROM crossings AS c2 WHERE c2.continent = c.continent AND c2.measure = c.measure AND c2.date < c.date), 0) AS AverageFROM crossings AS cORDER BY date DESC, continent, measure DESC;两种版本都将受益于(continent, measure, date)上的索引。 (adsbygoogle = window.adsbygoogle || []).push({});
