CODEWITHSUNDEEP
sqlpostgresql
Sketch0482
Sketch0482

Reputation: 35

Manipulating dates in PostgresSQL

I'm trying to convert a list days, that I'm querying , to weeks in the query listed below

Please advise. Thank you in advice for your help.

Upvotes: 0

Views: 55

Answers (1)

GMB
GMB

Reputation: 222432

You can first aggregate by date_trunc('week', ...) in a subquery, and then compute the delta between adjacent rows in the outer query:

select 
    anchor, 
    average,
    100.000 * (1 - lead(average) over (order by anchor) / average) as delta
from (
    select 
        date_trunc('week', day) anchor,
        round(avg(value)) average
    from mytable
    group by date_trunc('week', day)
) t
order by anchor

Upvotes: 1

Related Questions