sql - 如何根据 SQL 中的任意每月周期日期范围聚合值？

Question

给定一个表:

# SELECT * FROM payments ORDER BY payment_date DESC;
 id | payment_type_id | payment_date | amount  
----+-----------------+--------------+---------
  4 |               1 | 2019-11-18   |  300.00
  3 |               1 | 2019-11-17   | 1000.00
  2 |               1 | 2019-11-16   |  250.00
  1 |               1 | 2019-11-15   |  300.00
 14 |               1 | 2019-10-18   |  130.00
 13 |               1 | 2019-10-18   |  100.00
 15 |               1 | 2019-09-18   | 1300.00
 16 |               1 | 2019-09-17   | 1300.00
 17 |               1 | 2019-09-01   |  400.00
 18 |               1 | 2019-08-25   |  400.00
(10 rows)

如何根据任意日期范围对金额列求和，而不仅仅是日期截断？

以从某个月的 15 日开始到下个月的 14 日结束的日期范围为例，我希望看到的输出是:

 payment_type_id | payment_date | amount  
-----------------+--------------+---------
               1 | 2019-11-15   | 1850.00
               1 | 2019-10-15   |  230.00
               1 | 2019-09-15   | 2600.00
               1 | 2019-08-15   |  800.00

这可以在 SQL 中完成，还是用代码更好地处理？传统上，我会在代码中执行此操作，但希望扩展我对 SQL 的了解(在这个阶段，这并不多!)

Answer 1

Click demo:db<>fiddle

您可以结合使用 CASE 子句和 date_trunc() 函数:

SELECT 
    payment_type_id,
    CASE 
        WHEN date_part('day', payment_date) < 15 THEN 
            date_trunc('month', payment_date) + interval '-1month 14 days'
        ELSE  date_trunc('month', payment_date) + interval '14 days'
    END AS payment_date,
    SUM(amount) AS amount
FROM
    payments
GROUP BY 1,2

1. date_part('day', ...) 给出当前日期
2. CASE 子句用于划分 15 号之前和之后的日期。
3. date_trunc('month', ...) 将一个月中的所有日期转换为本月的第一天

因此，如果 date 在当月的 15 号之前，它应该被分组到上个月的 15 号(这是 +interval '-1month 14 days' 计算的结果:+14，因为 date_trunc() 会截断到每月的第一天:1 + 14 = 15)。否则分组到当月的 15 号。

在计算出这些payment_days之后，您可以使用它们进行简单的分组。