CODEWITHSUNDEEP
postgresqlsyntaxlead
Konstantinos Vilaras
Konstantinos Vilaras

Reputation: 45

Using lead() in Postgres with multiple columns

I have the following query:

select

calendar,
column1,
column2,
.
.
.
columnN,
count(some_value)/30 as some_value

from some_table
group by 1,2,...,N

Assuming my calendar is a date_trunc on 'month' and I want to use the lead() function to get the value from next year (12 months after the current value), how should my lead() syntax be?

I tried

lead(count(some_value)/30,12) OVER (PARTITION BY column1, column2, ..., columnN ORDER BY calendar ASC)

but it does not work as expected. What I have tested is selecting a single column and it does work. If I select multiple columns in does not.

sample output:

calendar column1 column2 count lead
01-01-23 Type A a 1 30
01-01-23 Type B a 3 34
01-02-23 Type A b 2 25
01-02-23 Type B a 5 51
01-03-23 Type A b 4 45
01-03-23 Type B b 10 37
01-04-23 Type B b 6
01-05-23 Type A b 15
01-06-23 Type B a 14
01-07-23 Type B b 4
01-08-23 Type A b 5
01-08-23 Type B a 2
01-09-23 Type A b 32
01-09-23 Type B b 32
01-10-23 Type B a 41
01-11-23 Type B b 44
01-12-23 Type B a 32
01-01-24 Type A a 30
01-01-24 Type B a 34
01-02-24 Type A b 25
01-02-24 Type B a 51
01-03-24 Type A b 45
01-03-24 Type B b 37

The idea is to retrieve for a specific combination of calendar date, type from column 1 and category from column 2 the count value from the same month from the next calendar year

Upvotes: 0

Views: 89

Answers (0)

Related Questions