PostgreSQL 如何获得前一个月和一周的价值

发布于05月31日

现在我每个月的平均值

SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)

但是SQL查询需要调整，以便

有可能吗？

对于每周

SELECT EXTRACT(WEEK FROM date_time) AS week, EXTRACT(YEAR FROM date_time) AS year, avg("total") FROM my_table GROUP BY EXTRACT(WEEK FROM date_time), EXTRACT(YEAR FROM date_time)

推荐答案

是的，有可能:

SELECT t1.month, t2.year, t1.tot - t2.tot FROM
(
SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") AS tot
FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)
) t1
join (
SELECT EXTRACT(MONTH FROM date_time) AS month, EXTRACT(YEAR FROM date_time) AS year, avg("total") AS tot
FROM my_table GROUP BY EXTRACT(MONTH FROM date_time), EXTRACT(YEAR FROM date_time)
) t2
on ((t1.year = t2.year) and (t1.month = t2.month + 1)) or
   ((t1.year = t2.year + 1) and (t1.month = 1) and (t2.month = 12))

我已将您的select转换为两个子 Select ，分别命名为t1和t2，并按照左连接的标准将它们连接起来.

请注意，第一个月当前没有一对，如果您仍然需要它，那么您可以使用left join和coalesce来确保即使是未配对的项目也有一个"对"，并且tot的NULL默认为0.

进一步注意，您可以将此子查询转换为view以提高可读性.