mysql - 计算当月的账单使用情况

Question

我正在尝试计算当月的存储空间使用情况。表格的外观如下:

stored_on              deleted_on          amount_in_gb    rate_per_gb_per_month
2014-01-01 12:00:00    2014-05-09 00:00:00   20            0.05 
2015-01-01 00:00:00    NULL                  4.2            0.05 
2015-01-01 12:00:00    2015-01-09 00:00:00   7.2            0.05 
2016-01-01 12:00:00    NULL                  100            0.05 

要获取 2015 年 1 月的使用量，它将是:

  $0.05 * 20 * 0        = $0.00 (this item was deleted before the start of the month)
+ $0.05 * 4.2 * 1       = $0.21 (for the second line item, the item is stored for a full month)
+ $0.05 * 7.2 * (~8/31) = $0.09 (stored for about 8 of 31 days in the month)
+ $0.05 * 100 * 0       = $0.00 (this item was added after this month)
---------------------------------
TOTAL                   = $0.30

我将如何在 SQL 中执行上述操作？基本上，给定一个特定的月份，计算该月的使用情况，考虑到 stored_on 值可能在该月开始之前、该月期间或该月之后的事实；与 deleted_on 值相同。

Answer 1

这应该计算一月份的按比例分配的金额:

select sum( rate_per_gb_per_month * amount_in_gb *
        greatest(1 +
                 datediff(least('2015-01-31', coalesce(deleted_on, '2015-01-31')) ,
                          greatest('2015-01-01', stored_on)
                         ), 0
             ) / DAY(LAST_DAY('2015-01-01'))  
      ) as usage_cost

from t;

Here是一个 SQL fiddle 。