后12个月后计数

Question

我正在计算加入多个表后的最后12个月计数，我的预期输出可以正常工作，但这不是我想要的吗？我想添加另一列名称为“当前月”，所以基本想法是，如果我看到了一个月的报告 May ，那么它将从去年的5月到今年开始4月和5月作为当月，总计13列计数。我的直觉说窗口查询将帮助我解决这个问题，但是我现在确定如何做到这一点。

select
c.name,
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'January'  THEN 1  END) as "January",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'February' THEN 1  END) as "February",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'March' THEN 1  END) as "March",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'April' THEN 1  END) as "April",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'May' THEN 1  END) as "May",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'June' THEN 1  END) as "June",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'July' THEN 1  END) as "July",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'August' THEN 1  END) as "August",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'September' THEN 1  END) as "September",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'October' THEN 1  END) as "October",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'November' THEN 1  END) as "November",
  SUM(case when RTRIM(TO_CHAR(mor.sent_at , 'Month')) = 'December' THEN 1  END) as "December"
from analytics_outbox mo 
inner join analytics_outbox_recipient mor on mor.analytics_outbox_id = mo.id
inner join customer c on c.id = mo.customer_id
group by c.name

当前输出：

name                              |january|february|march |april  |may    |june|july|august|september|october|november|december|
----------------------------------+-------+--------+------+-------+-------+----+----+------+---------+-------+--------+--------+
ABC                               |       |        |     1|      2|       |    |    |      |         |       |        |        |
DEF                               |     11|      24|    34|     32|     19|    |    |      |         |       |        |        |
GEH                               |      9|       3|     7|     18|     22|    |    |      |         |       |        |        |
IJK                               |       |        |      |      1|       |    |    |      |         |       |        |        |

Answer 1

动态结果列名仅使用动态SQL才有可能。

这应该有效地完成工作，保存动态列名：

SELECT c.name
     , to_char(t.mon, 'Month YYYY') AS report_month
     , count(*) FILTER (WHERE mor.sent_at >= t.mon - interval '12 mon' AND mor.sent_at <  t.mon - interval '11 mon') AS mon1
     , count(*) FILTER (WHERE mor.sent_at >= t.mon - interval '11 mon' AND mor.sent_at <  t.mon - interval '10 mon') AS mon2
     , count(*) FILTER (WHERE mor.sent_at >= t.mon - interval '10 mon' AND mor.sent_at <  t.mon - interval '09 mon') AS mon3
     -- etc.
FROM   analytics_outbox           mo
JOIN   analytics_outbox_recipient mor ON mor.analytics_outbox_id = mo.id
JOIN   customer                   c   ON c.id = mo.customer_id
    , (SELECT date_trunc('month', now())) AS t(mon)    -- add once for ease of use
GROUP  BY 1;

这将未更改的值从send_at与常数值（计算一次）进行比较，该值比在比较之前通过多个函数运行每个值便宜。

由于缺少输入而无法解析的时区和TIMESTAMP timestamp timestamp 可能的转角案例问题。