PostgreSQL 13
假設像下面這樣的簡化表plans，可以假設每個月至少有 1 行，有時同一天有多行：
id |
first_published_at |
|---|---|
| 12345678910 | 2022-10-01 03:58:55.118 |
| abcd1234efg | 2022-10-03 03:42:55.118 |
| jhsdf894hld | 2022-10-03 17:34:55.118 |
| aslb83nfys5 | 2022-09-12 08:17:55.118 |
我的簡化查詢：
SELECT TO_CHAR(plans.first_published_at, 'YYYY-MM') AS publication_date, COUNT(*)
FROM plans
WHERE plans.first_published_at IS NOT NULL
GROUP BY TO_CHAR(plans.first_published_at, 'YYYY-MM');
這給了我以下結果：
publication_date |
count |
|---|---|
| 2022-10 | 3 |
| 2022-09 | 1 |
但是我在 10 月份需要的結果是4.
對于每個月，計數應該是當前月份和之前所有月份的匯總。我將不勝感激有關如何解決此問題的任何見解。
uj5u.com熱心網友回復：
我會將您的查詢用作 CTE 并運行使用累積sum作為視窗函式的選擇。
with t as
(
SELECT TO_CHAR(plans.first_published_at, 'YYYY-MM') AS publication_date,
COUNT(*) AS cnt
FROM plans
WHERE plans.first_published_at IS NOT NULL
GROUP BY publication_date
)
select publication_date,
sum(cnt) over (order by publication_date) as "count"
from t
order by publication_date desc;
DB fiddle上的演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/522212.html
下一篇：R中的日期計算