计算每天的会员收入

Question

我有一个包含ID、Start_date、End_date、Duration、Value列的membership表

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 |

诸若此类

我想将其转换为两列，一列包含日期，一列包含一年中的每一天，另一列包含该天所有成员资格价值/持续时间的总和。

我最终需要把它转换成每月的价值，让我清楚地知道，由于运营会员资格，未来会有什么收入。

现在我做了一些像这样的事情

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次查询时长:3666秒。*/

但我不明白如何轻松地将它们相加，以获得每月的价值。我可以再次创建列的总和，但不希望键入文本的小说

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

我需要一个形状的输出

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

其中总和是该期间内所有会员的总和，按每天的价格计算*该会员在该月内的天数。

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

有谁知道吗？

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表，以达到类似的效果。

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