Postgres 10

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 illness (illness text, created_at timestamp);

INSERT INTO illness VALUES
   ('Cervix', '2018-01-03 15:45:40'),
   ('Cervix', '2018-01-03 15:45:40'),
   ('Cervix', '2018-01-03 15:45:40'),
   ('Cervix', '2018-01-03 15:45:40'),
   ('Cervix', '2018-01-03 15:45:40'),
   ('Lung',   '2018-01-03 17:50:32'),
   ('Lung',   '2018-02-03 17:50:32'),
   ('Lung',   '2018-02-03 17:50:32'),
   ('Lung',   '2018-02-03 17:50:32'),
   ('Cervix', '2018-02-03 17:50:32'),
   ('Cervix', '2017-01-03 15:45:40'), -- Lung each for the month of Jan - tie!
   ('Lung',   '2017-01-03 17:50:32'),
   ('Lung',   '2017-02-03 17:50:32'),
   ('Lung',   '2017-02-03 17:50:32'),
   ('Lung',   '2017-02-03 17:50:32'),
   ('Cervix', '2017-02-03 17:50:32');
16 rows affected

-- For a given (single!) month:
SELECT tumour_count, illness
FROM (
   SELECT count(*) AS tumour_count, illness
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- given month: 2007-01
   AND    created_at  <  '2017-02-01'
   GROUP  BY illness
   ) sub
WHERE rnk = 1;


tumour_count
illness


1
Cervix

1
Lung


-- For any given period of time:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, illness
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
ORDER  BY mon, illness;


month
tumour_count
illness


2017-01
1
Cervix

2017-01
1
Lung

2017-02
3
Lung

2018-01
5
Cervix

2018-02
3
Lung


-- Get 1 row per month:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, string_agg(illness, ' | ')
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
GROUP  BY mon, tumour_count
ORDER  BY mon;


month
tumour_count
string_agg


2017-01
1
Cervix | Lung

2017-02
3
Lung

2018-01
5
Cervix

2018-02
3
Lung

month	tumour_count	illness
2017-01	1	Cervix
2017-01	1	Lung
2017-02	3	Lung
2018-01	5	Cervix
2018-02	3	Lung