Postgresql - How to get value from last record of each month (without index), PostgreSQL

Postgresql - How to get value from last record of each month (without ...

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

create table table_name (
  year     int     not null,
  month    int     not null,
  week     int     not null,
  category text    not null,
  value    numeric
);

insert into table_name
values (2017, 1, 1, 'A',  1),
       (2017, 1, 1, 'B',  2),
       (2017, 1, 1, 'C',  3),
       (2017, 1, 2, 'A',  4),
       (2017, 1, 2, 'B',  5),
       (2017, 1, 2, 'C',  6),
       (2017, 1, 3, 'A',  7),
       (2017, 1, 3, 'B',  8),
       (2017, 1, 3, 'C',  9),
       (2017, 1, 4, 'A', 10),
       (2017, 1, 4, 'B', 11),
       (2017, 1, 4, 'C', 12),
       (2017, 2, 5, 'A',  1),
       (2017, 2, 5, 'B',  2),
       (2017, 2, 5, 'C',  3),
       (2017, 2, 6, 'A',  4),
       (2017, 2, 6, 'B',  5),
       (2017, 2, 6, 'C',  6),
       (2017, 2, 7, 'A',  7),
       (2017, 2, 7, 'B',  8),
       (2017, 2, 7, 'C',  9),
       (2017, 2, 8, 'A', 10),
       (2017, 2, 8, 'B', 11),
       (2017, 2, 8, 'C', 12);

select   year, month, category, avg(value) avg_val,
         (array_agg(value order by week desc))[1] max_val_of_month
from     table_name
group by year, month, category;

insert into table_name
select     y, m, extract(week from make_date(y, m, 4) + interval '1 week' * w), c, random() * 10
from       generate_series(1970, 2016) y
cross join generate_series(1, 12) m
cross join generate_series(0, 3) w
cross join (values ('A', 'B', 'C')) c(c);

-- create index idx_table_name_year_month_category_week_desc on table_name(year, month, category, week desc);

-- row_number() variant:

explain analyze
select year, month, category, avg_val, max_val_of_month
from   (select year, month, category, value max_val_of_month,
               avg(value) over (partition by year, month, category) avg_val,
               row_number() over (partition by year, month, category order by week desc) rn
        from   table_name) w
where  rn = 1;

-- distinct on variant:

explain analyze
select   distinct on (year, month, category) year, month, category,
         avg(value) over (partition by year, month, category) avg_val,
         value max_val_of_month
from     table_name
order by year, month, category, week desc;

-- lateral variant:

explain analyze
select     year, month, category,
           avg(value) avg_val,
           max_val_of_month
from       table_name t
cross join lateral (select   value max_val_of_month
                    from     table_name
                    where    (year, month, category) = (t.year, t.month, t.category)
                    order by week desc
                    limit    1) m
group by   year, month, category, max_val_of_month;

-- array_agg variant:

explain analyze
select   year, month, category, avg(value) avg_val,
         (array_agg(value order by week desc))[1] max_val_of_month
from     table_name
group by year, month, category;

-- custom first_agg variant:

create function first_agg_sfunc(anyelement, anyelement)
  returns anyelement
  language sql
  immutable
  strict
  as 'SELECT $1';

create aggregate first_agg (
  sfunc    = first_agg_sfunc,
  basetype = anyelement,
  stype    = anyelement
);

explain analyze
select   year, month, category, avg(value) avg_val,
         first_agg(value order by week desc) max_val_of_month
from     table_name
group by year, month, category;


absolute service time: 1,74 sec

	year	month	category	avg_val	max_val_of_month
1	2017	1	A	5,5000	10
2	2017	1	B	6,5000	11
3	2017	1	C	7,5000	12
4	2017	2	A	5,5000	10
5	2017	2	B	6,5000	11
6	2017	2	C	7,5000	12

	QUERY PLAN
1	Subquery Scan on w (cost=197.64..247.36 rows=7 width=104) (actual time=16.971..19.068 rows=570 loops=1)
2	Filter: (w.rn = 1)
3	Rows Removed by Filter: 1710
4	-> WindowAgg (cost=197.64..230.79 rows=1326 width=116) (actual time=16.941..18.775 rows=2280 loops=1)
5	-> Sort (cost=197.64..200.95 rows=1326 width=108) (actual time=16.924..17.149 rows=2280 loops=1)
6	Sort Key: table_name.year, table_name.month, table_name.category, table_name.week DESC
7	Sort Method: quicksort Memory: 275kB
8	-> WindowAgg (cost=99.03..128.87 rows=1326 width=108) (actual time=11.166..14.925 rows=2280 loops=1)
9	-> Sort (cost=99.03..102.35 rows=1326 width=76) (actual time=11.113..11.381 rows=2280 loops=1)
10	Sort Key: table_name.year, table_name.month, table_name.category
11	Sort Method: quicksort Memory: 275kB
12	-> Seq Scan on table_name (cost=0.00..30.26 rows=1326 width=76) (actual time=0.023..0.698 rows=2280 loops=1)
13	Planning time: 0.322 ms
14	Execution time: 19.399 ms

	QUERY PLAN
1	Unique (cost=197.64..210.90 rows=200 width=108) (actual time=7.647..8.330 rows=570 loops=1)
2	-> Sort (cost=197.64..200.95 rows=1326 width=108) (actual time=7.646..7.846 rows=2280 loops=1)
3	Sort Key: year, month, category, week DESC
4	Sort Method: quicksort Memory: 275kB
5	-> WindowAgg (cost=99.03..128.87 rows=1326 width=108) (actual time=1.998..5.478 rows=2280 loops=1)
6	-> Sort (cost=99.03..102.35 rows=1326 width=76) (actual time=1.975..2.223 rows=2280 loops=1)
7	Sort Key: year, month, category
8	Sort Method: quicksort Memory: 275kB
9	-> Seq Scan on table_name (cost=0.00..30.26 rows=1326 width=76) (actual time=0.033..0.539 rows=2280 loops=1)
10	Planning time: 0.209 ms
11	Execution time: 8.773 ms

	QUERY PLAN
1	GroupAggregate (cost=53457.27..53479.66 rows=200 width=104) (actual time=1061.504..1063.351 rows=570 loops=1)
2	Group Key: t.year, t.month, t.category, table_name.value
3	-> Sort (cost=53457.27..53460.59 rows=1326 width=104) (actual time=1061.479..1061.717 rows=2280 loops=1)
4	Sort Key: t.year, t.month, t.category, table_name.value
5	Sort Method: quicksort Memory: 275kB
6	-> Nested Loop (cost=40.21..53388.50 rows=1326 width=104) (actual time=0.470..1057.167 rows=2280 loops=1)
7	-> Seq Scan on table_name t (cost=0.00..30.26 rows=1326 width=72) (actual time=0.021..0.668 rows=2280 loops=1)
8	-> Limit (cost=40.21..40.22 rows=1 width=36) (actual time=0.461..0.461 rows=1 loops=2280)
9	-> Sort (cost=40.21..40.22 rows=1 width=36) (actual time=0.458..0.458 rows=1 loops=2280)
10	Sort Key: table_name.week DESC
11	Sort Method: top-N heapsort Memory: 25kB
12	-> Seq Scan on table_name (cost=0.00..40.20 rows=1 width=36) (actual time=0.062..0.434 rows=4 loops=2280)
13	Filter: ((year = t.year) AND (month = t.month) AND (category = t.category))
14	Rows Removed by Filter: 2276
15	Planning time: 0.345 ms
16	Execution time: 1063.663 ms

	QUERY PLAN
1	GroupAggregate (cost=99.03..121.92 rows=200 width=104) (actual time=1.930..6.287 rows=570 loops=1)
2	Group Key: year, month, category
3	-> Sort (cost=99.03..102.35 rows=1326 width=76) (actual time=1.902..2.140 rows=2280 loops=1)
4	Sort Key: year, month, category
5	Sort Method: quicksort Memory: 275kB
6	-> Seq Scan on table_name (cost=0.00..30.26 rows=1326 width=76) (actual time=0.018..0.571 rows=2280 loops=1)
7	Planning time: 0.162 ms
8	Execution time: 6.510 ms

	QUERY PLAN
1	GroupAggregate (cost=99.03..449.61 rows=200 width=104) (actual time=3.110..15.467 rows=570 loops=1)
2	Group Key: year, month, category
3	-> Sort (cost=99.03..102.35 rows=1326 width=76) (actual time=2.923..3.386 rows=2280 loops=1)
4	Sort Key: year, month, category
5	Sort Method: quicksort Memory: 275kB
6	-> Seq Scan on table_name (cost=0.00..30.26 rows=1326 width=76) (actual time=0.027..0.899 rows=2280 loops=1)
7	Planning time: 0.161 ms
8	Execution time: 15.732 ms