SQL 窗口函数过滤

Question

Blockquote

鉴于这些要求:

费率查询表:

effective_start_date | effective_end_date  | amount | rate_type
----------------------------------------------------------------
2016-01-16 00:00:00  | 2016-02-15 00:00:00 |   0.10 | rate1
2016-01-16 00:00:00  | 2016-02-15 00:00:00 |    156 | rate2 (annual)
2016-02-16 00:00:00  | 2016-03-15 00:00:00 |   0.15 | rate1

输入/源表:

datetime            | person | qty | x  | vatable
-------------------------------------------------
2016-01-15 00:00:00 |     p1 |  10 | x1 | Y
2016-01-16 00:00:00 |     p1 |  10 | x1 | Y
2016-01-16 00:00:00 |     p1 |  11 | x2 | N
2016-01-16 01:00:00 |     p1 |  9  | x1 | Y
2016-01-16 02:00:00 |     p2 |  10 | x1 | Y
2016-02-15 00:00:00 |     p1 |  8  | x1 | Y
2016-02-15 00:00:00 |     p1 |  8  | x2 | Y
2016-02-16 00:00:00 |     p1 |  8  | x1 | Y
2016-02-16 00:00:00 |     p1 |  7  | x2 | Y

如果使用rate2:
monthly_qty = param 每个月的数量总和(1 月 16 日至 31 日，2 月 1 日至 15 日)
calculated_rate = rate amount/12/monthly_qty
计算费用 = 计算费用 * 数量
vatable_qty = 数量总和，其中 vatable = Yvat = calculated_rate * vatable_qty * 0.12

if param = 1 月 16 日 - 2 月 15 日(期间以有效的开始和结束日期为准)

预期的输出表:

datetime            | person | qty | monthly_qty | calc_rate | calc_fee       | vat_qty | vat
--------------------------------------------------------------------------------------------------
2016-01-16 00:00:00 |     p1 | 21  | 40          | 156/12/40 | calc_rate * 21 | 10      | calc_rate * 10 * 0.12
2016-01-16 01:00:00 |     p1 | 9   | 40          | 156/12/40 | calc_rate * 9  | 9       | calc_rate * 9 * 0.12
2016-01-16 02:00:00 |     p2 | 10  | 40          | 156/12/40 | calc_rate * 10 | 10      | calc_rate * 10 * 0.12
2016-02-15 00:00:00 |     p1 | 16  | 16          | 156/12/16 | calc_rate * 16 | 16      | calc_rate * 16 * 0.12

(1) 我得到了错误的 total_qty_per_month (monthly_qty) - 对于 1 月，它包括 2016-01-15 的总数，实际:50，预期:40 (10 + 11 + 9 + 10 只)

编辑:这是我更新的 SQL:(我正在使用 View )

select it.datetime, it.person, 'rate2'::varchar as rate_used,
  sum(it.qty) as qty, 
  rl.amount, rl.effective_start_date, rl.effective_end_date,
  sum(sum(it.qty)) over w_month as total_qty_per_month,
  rl.amount / 12 / sum(sum(it.qty)) over w_month as calculated_rate,
  ((rl.amount / 12 / sum(sum(it.qty)) over w_month) * sum(qty)) as calculated_fee,
  sum(case when it.vatable = 'Y' then it.qty else 0 end) as vatable_qty
  from input_table it
  inner join rate_lookup_table rl on it.datetime between rl.effective_start_date and rl.effective_end_date
  where it.person_type='PT1' and rl.rate_type = 'rate2' 
  group by it.datetime, it.person, rl.amount, rl.effective_start_date, rl.effective_end_date
  window w_month as (partition by date_trunc('month', it.datetime))
  order by it.datetime

我需要根据rate lookup table的effective_start_date和effective_end_date获取窗口函数的总和。

Answer 1

据我所知，您可以使用 between 和 window function 的连接来做到这一点计算每月总和:

select it.datetime, it.person, it.qty, rl.amount, rl.rate_type, 
       rl.amount / 12 / sum(it.qty) over (partition by date_trunc('month', it.datetime)) as calculated_rate       
from rate_lookup_table rl
  join input_table it on it.datetime between rl.effective_start_date and effective_end_date
where rl.rate_type = 'rate2';

date_trunc('month', it.datetime) 将日期“规范化”为月初，因此同一个月的所有值都相同。因此，窗口函数将汇总同一个月的所有数量。

根据您的示例数据，这将返回:

datetime            | person | qty | amount | rate_type | calculated_rate
--------------------+--------+-----+--------+-----------+----------------
2016-01-16 00:00:00 | p1     |  10 |    156 | rate2     |            0.45
2016-01-16 01:00:00 | p1     |   9 |    156 | rate2     |            0.45
2016-01-16 02:00:00 | p2     |  10 |    156 | rate2     |            0.45
2016-02-15 00:00:00 | p1     |   8 |    156 | rate2     |            1.63

calculated_fee 和 vat 可以从 calculated_rate 中推导出来。要不重复表达式，您可以使用派生表:

select *, 
       calculated_rate * qty as calculated_fee,
       calculated_rate * qty * 0.12 as vat
from (
  select it.datetime, it.person, it.qty, rl.amount, rl.rate_type, 
         rl.amount / 12 / sum(it.qty) over (partition by date_trunc('month', it.datetime)) as calculated_rate       
  from rate_lookup_table rl
    join input_table it on it.datetime between rl.effective_start_date and effective_end_date
  where rl.rate_type = 'rate2'
) t;