By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE mytable(
IDA INTEGER NOT NULL
,DATETIMEA timestamp NOT NULL
);
INSERT INTO mytable(IDA,DATETIMEA) VALUES (1,'2020-03-16 13:15:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (2,'2020-03-17 15:25:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (3,'2020-03-18 17:10:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (5,'2020-03-19 11:44:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (5,'2020-03-20 12:55:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (5,'2020-03-21 19:35:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (7,'2020-03-22 10:13:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (8,'2020-03-22 15:25:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (8,'2020-03-28 12:12:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (9,'2020-03-29 17:55:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (10,'2020-03-30 11:54:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (12,'2020-03-30 15:35:00');
INSERT INTO mytable(IDA,DATETIMEA) VALUES (12,'2020-03-31 13:19:00');
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
SELECT t.DATETIMEA::date date,
COUNT(t.*) "day",
(SELECT COUNT(w.*) FROM mytable w WHERE w.DATETIMEA::date BETWEEN t.DATETIMEA::date - 7 and t.DATETIMEA::date) week,
(SELECT COUNT(m.*) FROM mytable m WHERE m.DATETIMEA::date BETWEEN t.DATETIMEA::date - 30 and t.DATETIMEA::date) "month"
FROM mytable t
GROUP BY t.DATETIMEA::date
ORDER BY t.DATETIMEA::date
ERROR: subquery uses ungrouped column "t.datetimea" from outer query LINE 3: ...*) FROM mytable w WHERE w.DATETIMEA::date BETWEEN t.DATETIME... ^
select
datetimea::date date,
count(*) "day",
sum(count(*)) over(
order by datetimea::date
range between '7 day' preceding and current row
) l7,
sum(count(*)) over(
order by datetimea::date
range between '30 day' preceding and current row
) l30
from mytable
group by datetimea::date
order by datetimea::date
| date | day | l7 | l30 |
|---|---|---|---|
| 2020-03-16 | 1 | 1 | 1 |
| 2020-03-17 | 1 | 2 | 2 |
| 2020-03-18 | 1 | 3 | 3 |
| 2020-03-19 | 1 | 4 | 4 |
| 2020-03-20 | 1 | 5 | 5 |
| 2020-03-21 | 1 | 6 | 6 |
| 2020-03-22 | 2 | 8 | 8 |
| 2020-03-28 | 1 | 4 | 9 |
| 2020-03-29 | 1 | 4 | 10 |
| 2020-03-30 | 2 | 4 | 12 |
| 2020-03-31 | 1 | 5 | 13 |
SELECT 11