Postgresql - How to get value from last record of each month, PostgreSQL

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

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: 0,59 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=319.24..404.74 rows=11 width=104) (actual time=11.150..13.475 rows=570 loops=1)
2	Filter: (w.rn = 1)
3	Rows Removed by Filter: 1710
4	-> WindowAgg (cost=319.24..376.24 rows=2280 width=116) (actual time=11.130..13.155 rows=2280 loops=1)
5	-> Sort (cost=319.24..324.94 rows=2280 width=108) (actual time=11.110..11.362 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=0.28..192.08 rows=2280 width=108) (actual time=0.117..9.162 rows=2280 loops=1)
9	-> Index Scan using idx_table_name_year_month_category_week_desc on table_name (cost=0.28..146.48 rows=2280 width=76) (actual time=0.067..4.106 rows=2280 loops=1)
10	Planning time: 0.467 ms
11	Execution time: 13.816 ms

	QUERY PLAN
1	Unique (cost=319.24..342.04 rows=228 width=108) (actual time=8.958..9.695 rows=570 loops=1)
2	-> Sort (cost=319.24..324.94 rows=2280 width=108) (actual time=8.956..9.153 rows=2280 loops=1)
3	Sort Key: year, month, category, week DESC
4	Sort Method: quicksort Memory: 275kB
5	-> WindowAgg (cost=0.28..192.08 rows=2280 width=108) (actual time=0.054..7.306 rows=2280 loops=1)
6	-> Index Scan using idx_table_name_year_month_category_week_desc on table_name (cost=0.28..146.48 rows=2280 width=76) (actual time=0.023..2.636 rows=2280 loops=1)
7	Planning time: 0.237 ms
8	Execution time: 9.933 ms

	QUERY PLAN
1	GroupAggregate (cost=19142.26..19179.31 rows=228 width=104) (actual time=16.565..18.261 rows=570 loops=1)
2	Group Key: t.year, t.month, t.category, table_name.value
3	-> Sort (cost=19142.26..19147.96 rows=2280 width=104) (actual time=16.541..16.744 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=0.28..19015.10 rows=2280 width=104) (actual time=0.036..13.678 rows=2280 loops=1)
7	-> Seq Scan on table_name t (cost=0.00..39.80 rows=2280 width=72) (actual time=0.020..0.560 rows=2280 loops=1)
8	-> Limit (cost=0.28..8.30 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=2280)
9	-> Index Scan using idx_table_name_year_month_category_week_desc on table_name (cost=0.28..8.30 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=2280)
10	Index Cond: ((year = t.year) AND (month = t.month) AND (category = t.category))
11	Planning time: 0.397 ms
12	Execution time: 18.547 ms

	QUERY PLAN
1	GroupAggregate (cost=0.28..178.40 rows=228 width=104) (actual time=0.050..9.144 rows=570 loops=1)
2	Group Key: year, month, category
3	-> Index Scan using idx_table_name_year_month_category_week_desc on table_name (cost=0.28..146.48 rows=2280 width=76) (actual time=0.013..2.534 rows=2280 loops=1)
4	Planning time: 0.213 ms
5	Execution time: 9.290 ms

	QUERY PLAN
1	GroupAggregate (cost=0.28..742.13 rows=228 width=104) (actual time=0.111..12.297 rows=570 loops=1)
2	Group Key: year, month, category
3	-> Index Scan using idx_table_name_year_month_category_week_desc on table_name (cost=0.28..146.48 rows=2280 width=76) (actual time=0.012..2.167 rows=2280 loops=1)
4	Planning time: 0.159 ms
5	Execution time: 12.400 ms