math - 给定超过一个月的月度数据，如何计算 MySQL 中的月平均值？

Question

我有跨越多个月的数据，我希望能够获取每天的平均值并将其分成适当的月份。例如，假设一个数据点是 2/9/2010 - 3/8/2010，金额为 1500。那么，查询应该返回 2010 年 2 月的 1071.4 和 3 月的 428.6。我希望有一个 MySQL 语句能够代替我的 PHP 逻辑进行计算。谢谢。

编辑(添加表格定义):开始(日期时间)，结束(日期时间)，使用

编辑 2:这是一些虚拟数据

DROP TABLE IF EXISTS `dummy_data`;
CREATE TABLE `dummy_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `data` double(15,4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `dummy_data`
-- ----------------------------
BEGIN;
INSERT INTO `dummy_data` VALUES ('1', '2010-01-01', '2010-02-02', '200.0000'), ('2', '2010-02-03', '2010-02-25', '250.0000'), ('3', '2010-02-26', '2010-03-08', '300.0000'), ('4', '2010-03-09', '2010-04-12', '210.0000'), ('5', '2010-04-13', '2010-05-10', '260.0000'), ('6', '2010-05-11', '2010-06-15', '310.0000'), ('7', '2010-06-16', '2010-07-20', '320.0000');
COMMIT;

Answer 1

您应该选择 SUM 并按 MONTH(date) 函数分组，如下所示:

SELECT SUM(value), MONTH(date)
FROM TABLE
GROUP BY MONTH(date)

编辑:糟糕，我误读了问题，现在修改我的答案!

您需要使用一些更复杂的 TSQL 来获取月份中的天数，找到平均值并设置到每个字段中，然后以月度格式呈现它

更新创建一个函数，为您提供范围内的天数，例如这个，我根据 Michael Baria 创建的函数修改

CREATE FUNCTION [dbo].[GetDays](@StartDate DATETIME, @EndDate DATETIME)
RETURNS @MonthList TABLE(DayValue tinyint NOT NULL, MonthValue tinyint NOT NULL, YearValue int NOT NULL)
AS
BEGIN
--Variable used to hold each new date value
DECLARE @DateValue DATETIME

--Start with the starting date in the range
SET @DateValue=@StartDate

--Load output table with the month part of each new date
WHILE @DateValue <= @EndDate
BEGIN
    INSERT INTO @MonthList(DayValue, MonthValue,YearValue)
    SELECT DAY(@DateValue), MONTH(@DateValue), YEAR(@DateValue)

    --Move to the next day           
    SET @DateValue=DATEADD(dd,1,@DateValue)
END

--Return results
RETURN
END

GO

将您的表加入此函数，然后根据天数求和

SELECT SUM(data/DATEDIFF(dd,startDate,endDate)), M.MonthValue
FROM TABLE
JOIN (SELECT * FROM [dbo].[GetDays] (startDate,endDate)) M 

GROUP BY M.MonthValue

如果我得到一些示例数据，我可以稍微清理一下