MYSQL查询从表中获取每月消费数据

Question

我在mysql中有一个表'consumption'，大约有500万条记录，如:

month from  |   month to         |       consumption
2012-12-20     2013-01-10                  200
2013-01-11     2013-02-13                  345

有没有一种方法可以得到每个月的消费，比如:1月份(2013-01-01至2013-01-31)的消耗量=…， 2月= ....该值可以是估计值，不必完美。

我想用每天的平均消耗量乘以不同日期范围内每月的天数，但不知道如何处理。

更新:

@Karolis使用原始excel公式，我得到的估计消费值高于使用sql脚本计算的值。据我所知，sql脚本和excel公式都在做同样的计算。你能帮我找出为什么会发生这种情况，并使sql脚本消耗值与使用excel获得的值相同吗?

原始表:

            id  month_from  month_to    consumption 
            121 2009-12-30  2009-01-28  1251            <-First period
            121 2010-01-29  2010-02-24  915             <-Second period
            993 xxxx-xx-xx  xxxx-xx-xx  xxx 
            121 2010-02-25  2010-03-25  741 
            121 2010-03-26  2010-04-28  1508    

我使用了你给的脚本，做了一些修改，并添加了一个group by id和order by id，我使用的脚本是:

SELECT 
    m.month, id, 
    SUM(
        -- partial consumption = date subrange / date range * consumption
        (
            DATEDIFF(
                IF(c.date_to > m.last_day, m.last_day, c.date_to),
                IF(c.date_from < m.first_day, m.first_day, c.date_from)
            ) + 1
        ) / (DATEDIFF(c.date_to, c.date_from) + 1) * c.consumption
    ) consumption
FROM
    consumption c
    JOIN (
        -- series of months
        SELECT DISTINCT 
            DATE_FORMAT(date_from, '%Y %M') month,
            DATE_FORMAT(date_from, '%Y-%m-01') first_day,
            LAST_DAY(date_from) last_day
        FROM consumption
        GROUP BY date_from -- redundant, but for speed purposes
    ) m ON 
        -- condition indicating a date range belongs to a particular 
        -- month (fully or partially)
        c.date_from <= m.last_day AND c.date_to >= m.first_day
   GROUP BY m.month, id
   ORDER BY m.month, id

Excel公式:

if((idInCurrentLine = idInNextLine), ((((month_to - start_date) + 1  )*consumptionPerDayForFirstPeriod/day ) + (start_date - month_from) *  consumptionPerDayForsecondPeriod/day), "")
consumptionPerDayForFirstPeriod = consumptionFortheFirstPeriod/((month_to - month_from)+ 1)
consumptionPerDayForSecondPeriod = consumptinoFortheSecondPeriod/((month_to - month_from)+ 1)

在给出的例子中

idInCurrentLine = 121, idInNextLine = 121

使用这两个我计算估计的消耗，结果是:

估计消耗:(正如您所看到的，两种情况下的估计值存在差异，使用excel的估计值高于sql。

             Month           Using Excel    Using mysql script
             2009 january    1313.4         1269.3  
             2009 Febuary    950.47         915.5   
             2009 March      935.78         904..9  
             xxxx            xxxx           xxxxx   
             xxxx            xxxx           xxxxx 

Answer 1

SELECT
    m.month,
    SUM(
        -- partial consumption = date subrange / date range * consumption
        (
            DATEDIFF(
                IF(c.month_to > m.last_day, m.last_day, c.month_to),
                IF(c.month_from < m.first_day, m.first_day, c.month_from)
            ) + 1
        ) / (DATEDIFF(c.month_to, c.month_from) + 1) * c.consumption
    ) consumption
FROM
    consumption c
    JOIN (
        -- series of months
        SELECT DISTINCT 
            DATE_FORMAT(month_from, '%Y %M') month,
            DATE_FORMAT(month_from, '%Y-%m-01') first_day,
            LAST_DAY(month_from) last_day
        FROM consumption
        GROUP BY month_from -- redundant, but for speed purposes
    ) m ON 
        -- condition indicating that a date range belongs
        -- to a particular month (fully or partially)
        c.month_from <= m.last_day AND c.month_to >= m.first_day
GROUP BY m.first_day
ORDER BY m.first_day

Answer 2

Datediff是你的朋友-试试consumption * 30 / Datediff(day, monthfrom, monthto)

Answer 3

简而言之，您可以使用BETWEEN:

SELECT consumption from [table] where [input date] is between [monthFrom] and [monthTo]

输入日期为范围内的区域