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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE mytable(
Individual_ID TEXT NOT NULL
,Treatment TEXT NOT NULL
,Date_Treatment_Received DATE NOT NULL
);
CREATE TABLE
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2010-01-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2010-02-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2010-04-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2011-05-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2011-06-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2011-08-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2012-09-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('A','B','2012-10-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2010-01-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2010-02-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2010-04-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2011-05-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2011-06-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2011-08-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2012-09-01');
INSERT INTO mytable(Individual_ID,Treatment,Date_Treatment_Received) VALUES ('B','C','2012-10-01');
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
INSERT 0 1
INSERT 0 1
INSERT 0 1
select individual_id, treatment,
min(date_treatment_received) min_date, max(date_treatment_received) max_date
from (
select t.*,
sum(
case when treatment = lag_treatment
and date_treatment_received < lag_date_treatment_received + interval '1' year
then 0 else 1 end
) over(partition by individual_id order by date_treatment_received) grp
from (
select t.*,
lag(date_treatment_received) over(partition by individual_id order by date_treatment_received) lag_date_treatment_received,
lag(treatment) over(partition by individual_id order by date_treatment_received) lag_treatment
from mytable t
) t
) t
group by individual_id, treatment, grp
order by individual_id, treatment, min_date
| individual_id | treatment | min_date | max_date |
|---|---|---|---|
| A | B | 2010-01-01 | 2010-04-01 |
| A | B | 2011-05-01 | 2011-08-01 |
| A | B | 2012-09-01 | 2012-10-01 |
| B | C | 2010-01-01 | 2010-04-01 |
| B | C | 2011-05-01 | 2011-08-01 |
| B | C | 2012-09-01 | 2012-10-01 |
SELECT 6