在PostgreSQL中按月和年对查询结果进行分组，并获得有效的月总和

maxleb 发表于 Dev

Maxleb

SELECT date_trunc('month', txn_date) AS txn_month, sum(amount) as monthly_sum
    FROM yourtable
GROUP BY txn_month

有没有办法使没有结果的月份显示在查询中？

假设我有：

id      transDate     Product Qty
1234    04/12/2019    ABCD    2     
1245    04/05/2019    ABCD    1
1231    02/07/2019    ABCD    6

我还需要在第三个月返回0值

MonthYear  totalQty
02/2019    6
03/2019    0
04/2019    3

谢谢，

----更新-

这是从当前日期开始持续24个月的最终查询。年和月准备好任何图表。

感谢@a_horse_with_no_name

SELECT 
    --ONLY USE THE NEXT LINE IF YOU NEED TO HAVE THE ID IN YOUR RESULT
    CASE WHEN t."ItemId" IS NULL THEN 10607 ELSE t."ItemId" END AS "ItemId",
    TO_CHAR(y."transactionDate", 'yyyy-mm-dd') AS txn_month,
    TO_CHAR(y."transactionDate", 'yyyy') AS "Year",
    TO_CHAR(y."transactionDate", 'Mon') AS "Month",
    -coalesce(SUM(t."transactionQty"),0) AS "TotalSold"
FROM generate_series(
    TO_CHAR(CURRENT_DATE - INTERVAL '24 month', 'yyyy-mm-01')::date , 
    TO_CHAR(CURRENT_DATE, 'yyyy-mm-01')::date, 
    INTERVAL '1 month') as y("transactionDate")
  LEFT JOIN "ItemTransactions" AS t 
         ON date_trunc('month', t."transactionDate") = y."transactionDate"
        AND t."ItemTransactionTypeId" = 1
        AND t."ItemId" = 10607
GROUP BY txn_month, "Year", "Month", t."ItemId"
ORDER BY txn_month ASC;

正常输出

ItemId  txn_month   Year    Month   TotalSold
10607   2018-03-01  2018    Mar     2
10607   2018-04-01  2018    Apr     0
10607   2018-05-01  2018    May     8
10607   2018-06-01  2018    Jun     12
10607   2018-07-01  2018    Jul     6
10607   2018-08-01  2018    Aug     4
10607   2018-09-01  2018    Sep     6
10607   2018-10-01  2018    Oct     8
10607   2018-11-01  2018    Nov     4
10607   2018-12-01  2018    Dec     0
10607   2019-01-01  2019    Jan     2
10607   2019-02-01  2019    Feb     3
10607   2019-03-01  2019    Mar     4
10607   2019-04-01  2019    Apr     1
10607   2019-05-01  2019    May     4
10607   2019-06-01  2019    Jun     3
10607   2019-07-01  2019    Jul     5
10607   2019-08-01  2019    Aug     6
10607   2019-09-01  2019    Sep     6
10607   2019-10-01  2019    Oct     6
10607   2019-11-01  2019    Nov     3
10607   2019-12-01  2019    Dec     0
10607   2020-01-01  2020    Jan     4
10607   2020-02-01  2020    Feb     2
10607   2020-03-01  2020    Mar     0

a_horse_with_no_name

左加入一个月列表：

SELECT t.txn_month, 
       coalesce(sum(yt.amount),0) as monthly_sum
FROM generate_series(date '2019-02-01', date '2019-04-01', interval '1 month') as t(txn_month)
  left join yourtable yt on date_trunc('month', yt.transdate) = t.txn_month
GROUP BY t.txn_month

在线示例

在实际查询中，您需要将条件从WHERE子句移动到JOIN条件。将它们放入WHERE子句会将外部联接重新转换为内部联接：

SELECT t."ItemId",
       y."transactionDate" AS txn_month,
       -coalesce(SUM(t."transactionQty"),0) AS "TotalSold"
FROM generate_series(date '2018-01-01', date '2020-04-01', INTERVAL '1 month') as y("transactionDate")
  LEFT JOIN "ItemTransactions" AS t 
         ON date_trunc('month', t."transactionDate") = y."transactionDate"
        AND t."ItemTransactionTypeId" = 1
        AND t."ItemId" = 10606
-- this WHERE clause isn't really needed because of the date values provided to generate_series()
WHERE AND y."transactionDate" >= NOW() - INTERVAL '2 year'
GROUP BY txn_month, t."ItemId"
ORDER BY txn_month DESC;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。