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 demo_data (
AB DATE,
BIS DATE,
MyHash INT,
flag INT
);
INSERT INTO demo_data (AB, BIS, myHash, flag) VALUES
('2024-01-01', '2024-01-15', 111, 0),
('2024-01-16', '2024-03-01', 111, 0),
('2024-03-02', '2024-03-31', 222, 0),
('2024-04-01', '2024-07-30', 222, 0),
('2024-07-31', '2024-07-31', 222, 1),
('2024-08-01', '2024-08-02', 222, 0),
('2024-01-01', '2024-07-31', 543, 0),
('2024-11-03', '2024-11-15', 653, 0),
('2024-01-01', '2024-06-30', 22435, 0),
('2024-07-01', '2024-10-31', 22435, 1),
('2024-11-01', '2024-11-02', 22435, 0),
('2024-11-03', '2024-11-15', 22435, 0);
CREATE TABLE
INSERT 0 12
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY myHash ORDER BY myhash) AS rn,
LAG(flag, 1 , 0) OVER (ORDER BY myhash) AS lag_flag
FROM demo_data
)
SELECT ab, bis, myhash, flag,
SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (ORDER BY myhash)
+ SUM(lag_flag) OVER (ORDER BY myhash, ab, bis) AS grp
FROM CTE
ORDER BY myhash
| ab | bis | myhash | flag | grp |
|---|---|---|---|---|
| 2024-01-01 | 2024-01-15 | 111 | 0 | 1 |
| 2024-01-16 | 2024-03-01 | 111 | 0 | 1 |
| 2024-03-02 | 2024-03-31 | 222 | 0 | 2 |
| 2024-04-01 | 2024-07-30 | 222 | 0 | 2 |
| 2024-07-31 | 2024-07-31 | 222 | 1 | 2 |
| 2024-08-01 | 2024-08-02 | 222 | 0 | 3 |
| 2024-01-01 | 2024-07-31 | 543 | 0 | 4 |
| 2024-11-03 | 2024-11-15 | 653 | 0 | 5 |
| 2024-01-01 | 2024-06-30 | 22435 | 0 | 6 |
| 2024-07-01 | 2024-10-31 | 22435 | 1 | 6 |
| 2024-11-01 | 2024-11-02 | 22435 | 0 | 7 |
| 2024-11-03 | 2024-11-15 | 22435 | 0 | 7 |
SELECT 12