sql - 当事件范围超过一个月时按月汇总事件成本(在 rails 3 中)

Question

我有这个事件模型

class Event < ActiveRecord::Base
  attr_accessible :starts_at, :ends_at, :price
end

Event 可以是单个即时事件:

Event.create(:starts_at => Date.today, :ends_at=>nil, :price => 10.0)

它也可以跨越几天或几个月:

Event.create(:starts_at => Date.today, :ends_at => (Date.today + 2.months), :price => 20.0)

我想按月分割事件的成本，这样一个即时事件的成本自然会落入它所属的月份，而跨越多个月的事件的成本应该在这些月份之间按比例分配。

显然这很难使用 SQL 来处理，但也许有人对此有一些建议？

计算此聚合的最有效方法是什么？

更新:

这是 sqlfiddle 上数据的明确定义结构:http://sqlfiddle.com/#!12/a532e/6

我想从这个数据集中得到的是这样的:

( (Date('2013-01-01'), 31.6), (Date('2013-02-01'), 8.4) )

Answer 1

SELECT event_id
      ,date_trunc('month', day)::date AS month
      ,sum(daily) AS price_this_month
FROM  (
   SELECT event_id
         ,generate_series(starts_at
                         ,COALESCE(ends_at, starts_at)
                         ,interval '1 day') AS day
         ,price / COALESCE((ends_at - starts_at) + 1, 1) AS daily
   FROM   events
   ) a
GROUP  BY 1,2
ORDER  BY 1,2;

-> sqlfiddle

我为 PostgreSQL 提供了更新的 sqlfiddle。您的原始版本似乎适用于 MySQL。

技巧是:

• 使用generate_series()每天为每个事件创建一行。
• 使用COALESCE处理 NULL 值，这似乎允许用于 ends_at。
• 通过将价格除以 starts_at 和 ends_at 之间的天数来计算每日 成本+ 1 以逐一修复。在 ends_at IS NULL 的情况下默认为 1。
• 按事件和月份重新汇总。

瞧。

您甚至可以根据每月的天数获得准确的每月费率。 2 月(28 天)比 1 月(31 天)便宜。