CODEWITHSUNDEEP
postgresqlwindow-functions
Gaurav Shah
Gaurav Shah

Reputation: 5279

preceding nth row without self-joins

I have a large query that results in following:

Date      Cost
------------------
6-Oct    24.05
5-Oct    34.56
4-Oct    24.76
3-Oct    12.89
2-Oct    11.34
1-Oct    21.76
30-Sept  32.89
29-Sept  11.34

I am trying to have the following using windows

Date      Cost   Previous_week
-------------------------------
6-Oct    24.05   32.89
5-Oct    34.56   11.34
4-Oct    24.76   NULL
3-Oct    12.89   NULL
2-Oct    11.34   NULL
1-Oct    21.76   NULL
30-Sept  32.89   NULL
29-Sept  11.34   NULL

Seems that window functions can be used only for aggregations, Is there another way that I can get 7th preceding row ?

I don't want to do self-joins.

Upvotes: 0

Views: 92

Answers (1)

Gaurav Shah
Gaurav Shah

Reputation: 5279

I should have spend little more time reading docs.

select date, cost
nth_value(cost, 7)
over(order by date desc
rows between unbounded preceding and unbounded following)
as previous_week

http://docs.aws.amazon.com/redshift/latest/dg/r_Examples_of_NTH_WF.html

Upvotes: 1

Related Questions