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.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |
begin;
create table order_history(id serial,pickup_date date, return_date date);
insert into order_history(pickup_date,return_date) values('2020-03-01', '2020-03-12'),
('2020-03-01', '2020-03-22'),
('2020-03-11', '2020-03-22'),
('2020-02-11', '2020-03-22'),
('2020-01-01', '2020-01-22'),
('2020-01-01', '2020-04-22');
commit;
6 rows affected
alter table order_history add column date_ranges daterange;
with a(m_begin, m_end, pickup_date) as
(select date_trunc('month', pickup_date)::date,
(date_trunc('month', pickup_date) + interval '1 month - 1 day')::date,
pickup_date from order_history)
update order_history set date_ranges =
daterange(a.m_begin, a.m_end,'[]') from a
where a.pickup_date = order_history.pickup_date;
6 rows affected
select *, return_date - pickup_date as total from order_history order by id;
| id | pickup_date | return_date | date_ranges | total |
|---|---|---|---|---|
| 1 | 2020-03-01 | 2020-03-12 | [2020-03-01,2020-04-01) | 11 |
| 2 | 2020-03-01 | 2020-03-22 | [2020-03-01,2020-04-01) | 21 |
| 3 | 2020-03-11 | 2020-03-22 | [2020-03-01,2020-04-01) | 11 |
| 4 | 2020-02-11 | 2020-03-22 | [2020-02-01,2020-03-01) | 40 |
| 5 | 2020-01-01 | 2020-01-22 | [2020-01-01,2020-02-01) | 21 |
| 6 | 2020-01-01 | 2020-04-22 | [2020-01-01,2020-02-01) | 112 |
--if pickup_date and return_date in the same month then return_date - pickup_date
--else aggreagte based pickup_date.
--for example. id=4, total is 40. 40=22+18.
--therefore should split to march-2020 22 feb-2020:18.
--finally do the aggregate per month.
--id=6. total = 112. 112 = 22+ 31 + 29 + 30
--therefore toal should split: jan2020: 30, feb2020:29, march2020: 31, 2020apr:22.