mysql - 计算每日成员(member)收入

Question

我有一个成员资格表，其中包含列 ID、Start_date、End_date、持续时间、值

ID | Start_date | End_date | Duration | Value |
1  | 2012-08-12 |2012-09-12|    30    |   10  |
2  | 2011-05-22 |2013-05-22|   720    |  2000 |

等等

我想把它变成两列，一列有日期，一年中的每一天，另一列有当天所有成员(member)值(value)/持续时间的总和。

我最终需要将其转化为每月值(value)，让我清楚地了解由于运行成员(member)资格而导致的 future 预期收入。

现在我做了类似的事情

select 
sum(if("2012-01-01" between start_date and end_date, total_value/duration, null)) as "2012-01-01",
sum(if("2012-01-02" between start_date and end_date, total_value/duration, null)) as "2012-01-02",
[...]
sum(if("2013-12-31" between start_date and end_date, total_value/duration, null)) as "2013-12-31"

from MembershipsTable

/* 0 行受影响，1 行找到。 1 个查询的持续时间:3,666 秒。 */

但我不知道如何轻松地总结它们以给出每月的值。我可以再次创建列的总和，但不想输入小说文本

对于当前格式来说，运行时间不是问题

我需要形状的输出

Month    | Sum    |
Jan 2012 |4500    |
Feb 2012 |4215,91 |

总和是该期间内所有成员(member)资格的总和，计算公式为每日价格*成员(member)资格当月的天数。

因此，如果成员(member)资格于 11 月 12 日开始，于 12 月 11 日结束，持续时间为 30，值(value)为 300，我想将 300/30*daysInNov 添加到 11 月份，12 月份相同，11 月份为 +190， 12 月 +110我需要以这种方式计算所有成员资格的总和。

有人知道吗？

Answer 1

这是一个有点丑陋的黑客，但我相信如果我正确理解您的需求，类似下面的东西会起作用。

首先，创建一个名为month_days 的表，其中包含所有月份及其开始日期和结束日期。您可以将其用作实用程序表来连接并计算每月总计。

month_days
start_date | last_date 
2012-01-01 | 2012-01-31
2012-02-01 | 2012-02-29

然后，执行连接和计算，如下所示:

select format(month_days.start_date, 'MMM yyyy') AS [Month],
       sum(case when (memberships.start_date > month_days.start_date AND memberships.end_date < month_days.end_date)
              then datediff(day, memberships.end_date, memberships.start_date) * (value/duration)
            when (memberships.start_date between month_days.start_date and month_days.end_date)
              then (datediff(day, month_days.end_date, memberships.start_date) + 1) * (value/duration)
            when (memberships.end_date between month_days.start_date and month_days.end_date)
              then datediff(day, memberships.end_date, month_days.start_date) * (value/duration)
            else (datediff(day, month_days.end_date, month_days.start_date) + 1) * (value/duration)
        end) total_value
from memberships
inner join month_days
on memberships.start_date < month_days.end_date
and memberships.end_date > month_days.start_date
group by month_days.start_date
order by month_days.start_date

有很多方法可以创建 Month_days 表来实现类似的效果。

您可能还可以编写一个存储过程来迭代每个记录的月份，用每月总和填充临时表(或表变量)，然后返回临时表的内容。