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?.
select version();
| version |
|---|
| PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table entry (username text, entry_data jsonb);
insert into entry values ('Aaron','{"value":1234,"entryDate":"07/25/2022"}');
insert into entry values ('Aaron','{"value":6463,"entryDate":"07/26/2022"}');
insert into entry values ('Aaron','{"value":8375,"entryDate":"07/27/2022"}');
insert into entry values ('Aaron','{"value":2734,"entryDate":"07/28/2022"}');
insert into entry values ('Aaron','{"value":4563,"entryDate":"07/29/2022"}');
insert into entry values ('Aaron','{"value":7374,"entryDate":"07/30/2022"}');
insert into entry values ('Aaron','{"value":8923,"entryDate":"07/31/2022"}');
insert into entry values ('Aaron','{"value":6737,"entryDate":"08/01/2022"}');
insert into entry values ('Aaron','{"value":1374,"entryDate":"08/02/2022"}');
insert into entry values ('Aaron','{"value":1834,"entryDate":"08/03/2022"}');
insert into entry values ('Aaron','{"value":3646,"entryDate":"08/04/2022"}');
insert into entry values ('Aaron','{"value":7834,"entryDate":"08/05/2022"}');
insert into entry values ('Aaron','{"value":2473,"entryDate":"08/06/2022"}');
insert into entry values ('Aaron','{"value":4673,"entryDate":"08/07/2022"}');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select *, ceil(row_number() over (order by 0)::decimal/7)::integer rn from entry;
| username | entry_data | rn |
|---|---|---|
| Aaron | {"value": 1234, "entryDate": "07/25/2022"} | 1 |
| Aaron | {"value": 6463, "entryDate": "07/26/2022"} | 1 |
| Aaron | {"value": 8375, "entryDate": "07/27/2022"} | 1 |
| Aaron | {"value": 2734, "entryDate": "07/28/2022"} | 1 |
| Aaron | {"value": 4563, "entryDate": "07/29/2022"} | 1 |
| Aaron | {"value": 7374, "entryDate": "07/30/2022"} | 1 |
| Aaron | {"value": 8923, "entryDate": "07/31/2022"} | 1 |
| Aaron | {"value": 6737, "entryDate": "08/01/2022"} | 2 |
| Aaron | {"value": 1374, "entryDate": "08/02/2022"} | 2 |
| Aaron | {"value": 1834, "entryDate": "08/03/2022"} | 2 |
| Aaron | {"value": 3646, "entryDate": "08/04/2022"} | 2 |
| Aaron | {"value": 7834, "entryDate": "08/05/2022"} | 2 |
| Aaron | {"value": 2473, "entryDate": "08/06/2022"} | 2 |
| Aaron | {"value": 4673, "entryDate": "08/07/2022"} | 2 |
select e.username, e.rn week_no,
(select sum(to_number(entry_data->>'value','9999999')) from entry) total_value,
sum(to_number(entry_data->>'value','9999999')) total_two_weeks
from
(select *, ceil(row_number() over (order by 0)::decimal/7)::integer rn
from entry) e
group by
e.username, total_value, e.rn;
| username | week_no | total_value | total_two_weeks |
|---|---|---|---|
| Aaron | 2 | 68237 | 28571 |
| Aaron | 1 | 68237 | 39666 |
with data as
(select *, ceil(row_number() over (order by 0)::decimal/7)::integer rn
from entry)
select username,sum(to_number(entry_data->>'value','9999999')) total_value,
sum (case when rn = 1 then
to_number(entry_data->>'value','9999999')
else 0
end) week_1_total,
sum (case when rn = 2 then
to_number(entry_data->>'value','9999999')
else 0
end) week_2_total,
sum (case when rn = 2 then
to_number(entry_data->>'value','9999999')
else 0
end) -
sum (case when rn = 1 then
to_number(entry_data->>'value','9999999')
else 0
end) WoWDifference
from data
group by
username;
| username | total_value | week_1_total | week_2_total | wowdifference |
|---|---|---|---|---|
| Aaron | 68237 | 39666 | 28571 | -11095 |
insert into entry values ('Aaron','{"value":6737,"entryDate":"08/08/2022"}');
insert into entry values ('Aaron','{"value":2374,"entryDate":"08/09/2022"}');
insert into entry values ('Aaron','{"value":1334,"entryDate":"08/10/2022"}');
insert into entry values ('Aaron','{"value":3516,"entryDate":"08/11/2022"}');
insert into entry values ('Aaron','{"value":6824,"entryDate":"08/12/2022"}');
insert into entry values ('Aaron','{"value":1433,"entryDate":"08/13/2022"}');
insert into entry values ('Aaron','{"value":3623,"entryDate":"08/14/2022"}');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
select e.username, e.rn week_no,
(select sum(to_number(entry_data->>'value','9999999')) from entry) total_value,
sum(to_number(entry_data->>'value','9999999')) total_weekly
from
(select *, ceil(row_number() over (order by 0)::decimal/7)::integer rn
from entry) e
group by
e.username, total_value, e.rn
order by e.rn;
| username | week_no | total_value | total_weekly |
|---|---|---|---|
| Aaron | 1 | 94078 | 39666 |
| Aaron | 2 | 94078 | 28571 |
| Aaron | 3 | 94078 | 25841 |
with data as (
select e.username, e.rn week_no,
(select sum(to_number(entry_data->>$$value$$,$$9999999$$)) from entry) total_value,
sum(to_number(entry_data->>$$value$$,$$9999999$$)) total_weekly
from
(select *, ceil(row_number() over (partition by username order by 0)::decimal/7)::integer rn
from entry) e
group by
e.username, total_value, e.rn)
select username,
week_no,
total_value,
total_weekly
from data
union all
select username,
(select max(week_no) from data) +
row_number() over (partition by username order by 0),
total_value,
total_weekly - coalesce(lag(total_weekly) over (order by week_no), 0)
from data
;
| username | week_no | total_value | total_weekly |
|---|---|---|---|
| Aaron | 2 | 94078 | 28571 |
| Aaron | 3 | 94078 | 25841 |
| Aaron | 1 | 94078 | 39666 |
| Aaron | 4 | 94078 | 39666 |
| Aaron | 5 | 94078 | -11095 |
| Aaron | 6 | 94078 | -2730 |
SELECT *
FROM crosstab(
'SELECT username, total_value,
week_no, total_weekly
FROM (
with data as (
select e.username, e.rn week_no,
(select sum(to_number(entry_data->>$$value$$,$$9999999$$))
from entry) total_value,
sum(to_number(entry_data->>$$value$$,$$9999999$$)) total_weekly
from
(select *,
ceil(row_number() over
(partition by username order by 0)::decimal/7)::integer rn
from entry) e
group by
e.username, total_value, e.rn
)
select username,
week_no,
total_value,
total_weekly
from data
union all
select username,
(select max(week_no) from data d
where d.username = d1.username) +
row_number() over (partition by username order by 0),
total_value,
total_weekly - coalesce(lag(total_weekly) over (order by week_no), 0)
from data d1) x
ORDER BY 1'
,$$VALUES (1), (2), (3), (4), (5), (6)$$
) AS ct
("username" text,
"total_value" int,
| username | total_value | Week_1 | Week_2 | Week_3 | Weekdiff_1 | Weekdiff_2 | Weekdiff_3 |
|---|---|---|---|---|---|---|---|
| Aaron | 94078 | 39666 | 28571 | 25841 | 39666 | -11095 | -2730 |
with data as (
select e.username, e.rn week_no,
(select sum(to_number(entry_data->>'value','9999999')) from entry) total_value,
sum(to_number(entry_data->>'value','9999999')) total_weekly
from
(select *, ceil(row_number() over (order by 0)::decimal/7)::integer rn
from entry) e
group by
e.username, total_value, e.rn)
select username,
total_value,
sum(case when week_no = 1
then total_weekly
else 0
end) week_1_total,
sum(case when week_no = 2
then total_weekly
else 0
end) week_2_total,
sum(case when week_no = 3
then total_weekly
else 0
end) week_3_total,
sum(case when week_no = 1
then total_weekly
else 0
end) wow_diff_1,
sum(case when week_no = 2
then total_weekly
else 0
end) -
sum(case when week_no = 1
then total_weekly
else 0
end) wow_diff_2,
sum(case when week_no = 3
| username | total_value | week_1_total | week_2_total | week_3_total | wow_diff_1 | wow_diff_2 | wow_diff_3 |
|---|---|---|---|---|---|---|---|
| Aaron | 94078 | 39666 | 28571 | 25841 | 39666 | -11095 | -2730 |