postgresql - Postgres如何从关系表组中获取上个月的总和？

Question

我有两张桌子

coins       summary_coins 
--------    -------------
id | name    id| date                |  get_count | coin_id
===|====    ==============================================
 1 |lira     1 | 2020-02-16 16:55:50 |  20        | 1
 2 |A        1 | 2020-03-16 16:55:50 |  12        | 1
 3 |B        1 | 2020-03-16 16:55:50 |  20        | 1

我的预期结果

name get_count   previous_month_count  
Lira 32          20

通过以下查询，我可以获得当前月份的汇总总和

SELECT Coins.id AS "Coins__id", 
Coins.name AS "Coins__name",  
(SUM(SummaryCoins.get_count)) AS "get_count", 
(SUM(SummaryCoins.get_count)) AS "previous_month_count" 
FROM coins Coins 
INNER JOIN summary_coins SummaryCoins 
ON Coins.id = (SummaryCoins.coin_id) 
WHERE (SummaryCoins.user_id = 1 AND SummaryCoins.date > '2021-04-01' AND SummaryCoins.date < '2021-05-31') 
GROUP BY Coins.id

我怎样才能得到上个月的总和？

标签： postgresql

Answer 1

一种方法是扩展 WHERE 子句中的日期范围以包括两个月（或实际上是所有三个月），然后在 SELECT 列表中使用过滤聚合。

SELECT c.id AS "Coins__id", 
       c.name AS "Coins__name",  
       SUM(sc.get_count) filter (where sc.date >= '2021-03-01' and sc.date < '2021-04-01') AS get_count,
       SUM(sc.get_count) filter (where sc.date >= '2021-04-01' and sc.date < '2021-05-01') AS previous_month_count
FROM coins c
  JOIN summary_coins sc ON c.id = sc.coin_id
WHERE sc.date >= '2021-03-01'  --<< note this includes the "previous" month
  AND sc.date < '2020-05-01'
GROUP BY c.id

在处理时间戳值（尽管列名“日期”具有误导性）时，建议使用运算符在所需范围结束后<的一天使用，以确保包含最后一天的所有值。