python - 如何从 mysql 中具有日期数据的表中估计每个月的列值？

Question

我在 mysql 中有一个 mysql 表“data_table”，其中包含大约 500 万条记录，例如:

  ------------------------------------------------------------------
    serial  |   month from  |   month to         |       consumption

    123         2012-01-01     2013-01-10                  200
    111         2012-12-28     2013-01-29                  324
    123         2013-01-11     2013-05-13                  1675
    111         2013-01-30     2013-02-16                  200
    391         2012-12-28     2013-02-27                  113
    123         2013-05-14     2013-05-28                  234
    123         2013-05-29     2013-06-05                  53
    123         2013-08-01     2013-09-26                  783 
  -------------------------------------------------------------------

如何找到每个月的消费估算值，例如:1 月(2013-01-01 至 2013-01-31)的消费 = ...，2 月 = .... 对于每个系列。

结果应该是这样的:2013 年 1 月的估计值

Serial     Month        year     Estimated_Consumption
 123       January      2013              472.5

像这样，代码应该能够列出所有月份、年份和相应序列号的估计值。

序列号 123 的 1 月份估计背后的逻辑是:

Total number of days in January = 31, 
Consumption for the first 10 days = 200
Consumption for the next 21 days = number of days for January in (2013-01-11, 2013-05-13)
* consumption per day for the period (2013-01-11, 2013-05-13)
Adding both these I get the consumption for January =  472.5

Answer 1

首先感谢大家的回复。我想出了如何做到这一点。为此，我使用了一个日历表，如以下链接所述:http://www.brianshowalter.com/calendar_tables

此外，我在数据表中添加了另一列，即每天的消耗量表。例如:

    premise         date_from       date_to     consumption     consumption per day
    111             12/13/2012      1/22/2013       200         4.88
    222             12/17/2012      1/12/2013       300         11.11
    111             1/23/2013       4/20/2013       200         2.27
    222             1/13/2013       1/20/2013       300         37.5
    111             4/23/2013       5/10/2013       200         11.11

查询是:

SELECT id,
        date_from as bill_start_date,
        theYear as Year,
        MONTHNAME(STR_TO_DATE(theMonth, '%m')) as month,  # use theMonth for displaying the month as a number
        DaysOnBill,
        TotalDaysInTheMonth,
        sum(perDayConsumption *  DaysOnBill) as EstimatedConsumption 
    FROM
    ( 
     SELECT
        id,
        date_from,
        theYear,
        theMonth,  # use theMonth for displaying the month as a number
        COUNT(*) AS DaysOnBill,
        TotalDaysInTheMonth, 
        perDayConsumption
    FROM
        (
        SELECT
            c.id,
            c.date_from as date_from,
            ct.dt,
            y AS theYear,
            month AS theMonth,
            DAY(LAST_DAY(ct.dt)) as TotalDaysInTheMonth,
            perDayConsumption
        FROM
            consumption AS c
            INNER JOIN
            calendar_table AS ct
                ON ct.dt >= c.date_from
                    AND ct.dt<= c.date_to
        ) AS allDates
    GROUP BY
        id,
        date_from,
        theYear,
        theMonth ) AS estimates
GROUP BY
        id,
        theYear,
        theMonth;

这里的日历表是​​一个单独的表，其中列出了月份、年份和日期等。