Search code examples
sqlalgorithmpostgresqlpostgresql-9.1

better performance for this sql algorithm?

I'm looking for alternative SQL code

My table has 4 columns : Id, Zip code, timestamp and user value.

Row Example :2299898;"40";"2011-03-28 00:45:00+02";1.9

I want to get the result set for one day for each zip code where the time equals or is before a timestamp. And obviously the user data for each line.

My query so far:

SELECT DISTINCT
    FIRST_VALUE(timestamp) OVER (
        PARTITION BY zipcode, DATE(timestamp)
        ORDER BY timestamp DESC
    ) AS timestamp,
    zipcode,
    FIRST_VALUE(userdata) OVER (
        PARTITION BY zipcode, DATE(timestamp)
        ORDER BY timestamp DESC
    ) AS userdata
Solution

  • You do not need the window functions:

    select distinct on (zipcode, timestamp::date)
    timestamp,
    zipcode,
    userdata
from t
order by zipcode, timestamp::date desc, timestamp desc

    Check distinct on