代码之家 › 专栏 › 技术社区 › woodbine

Postgresql重叠日期范围和求和结果

date-range join date postgresql

woodbine · 技术社区 · 7 年前

以下是我的合同表示例:

| buyer_id | supplier_id | start_date | end_date   | contract_value  |
| buyer_a  | supplier_a  | 2015-01-01 | 2017-01-01 | 240000          |
| buyer_a  | supplier_a  | 2016-01-01 | 2016-06-01 | 6000            |
| buyer_a  | supplier_b  | 2015-01-01 | 2015-12-31 | 100000          |
| buyer_a  | supplier_b  | 2017-01-01 | 2017-12-31 | 100000          |

以下是我的支出表示例:

| buyer_id | supplier_id | month      | trans_value    |
| buyer_a  | supplier_a  | 2015-01-01 | 1230.12        |
| buyer_a  | supplier_a  | 2015-02-01 | 1735.98        |
| buyer_a  | supplier_a  | 2015-03-01 | 2242.02        |

据我所知,链接这些表的最佳方法是将我的contracts表汇总到视图中,使其看起来像这样。。。

| buyer_id | supplier_id | month      | value |
| buyer_a  | supplier_a  | 2015-01-01 | 10000 |
| buyer_a  | supplier_a  | 2015-02-01 | 10000 |
| buyer_a  | supplier_a  | 2015-03-01 | 10000 |
| buyer_a  | supplier_a  | 2015-04-01 | 10000 |
| buyer_a  | supplier_a  | 2015-05-01 | 10000 |
| buyer_a  | supplier_a  | 2015-06-01 | 10000 |
| buyer_a  | supplier_a  | 2015-07-01 | 10000 |

只要每个月的价值是合同的总份额,就很容易将交易链接到唯一的三列上 buyer_id supplier_id 和 month ,然后我可以确定任何超支。

另外,我无法控制这些数据是如何发布的,所以我无法从源头上改进数据。

编辑:我希望能够创建这样的输出,然后我可以将其放入图表中以显示超支:

| buyer_id | supplier_id | month      | monthly_con_val | trans_value |
| buyer_a  | supplier_a  | 2015-01-01 | 10000           | 34000       |
| buyer_a  | supplier_a  | 2015-02-01 | 10000           | 10000       |
| buyer_a  | supplier_a  | 2015-03-01 | 50000           | 8000        |
| buyer_a  | supplier_a  | 2015-04-01 | 50000           | 14000       |
| buyer_a  | supplier_a  | 2015-05-01 | 50000           | 4000        |
| buyer_a  | supplier_a  | 2015-06-01 | 10000           | 3000        |
| buyer_a  | supplier_a  | 2015-07-01 | 10000           | 3000        |

1 回复 | 直到 7 年前

Abelisto 7 年前

类似于

with
  -- Sample data
  contracts(bs_id, start_date, end_date, contract_value) as (values
    (1, '2015-01-01'::date, '2017-01-01'::date, 240000),
    (1, '2016-01-01'::date, '2016-06-01'::date, 6000)),
  spending(bs_id, month, trans_value) as (values
    (1, '2015-01-01'::date, 1230.12),
    (1, '2015-02-01'::date, 1735.98),
    (1, '2016-05-01'::date, 5689.01)),
  -- End of sample data
  contracts_monthly as (
    select
      bs_id,
      month::date,
      sum(
        contract_value / (
          (extract(year from end_date)*12 + extract(month from end_date)) - 
          (extract(year from start_date)*12 + extract(month from start_date)))) as monthly_con_val
    from contracts, generate_series(start_date, end_date, interval '1 month') as month
    group by bs_id, month
    order by bs_id, month)
select
  *
from
  contracts_monthly left join spending using (bs_id, month);

为了使示例更加紧凑,我合并了列 buyer_id | supplier_id bs_id .

About generate_series() function