PostgreSQL - how should I use first_value()?

Question

This answer to shows how to produce High/Low/Open/Close values from a ticker: Retrieve aggregates for arbitrary time intervals I am trying to implement a solution based on this (PG 9.2), but am having difficulty in getting the correct value for `first_value()`. So far, I have tried two queries: ``` SELECT cstamp, price, date_trunc('hour',cstamp) AS h, floor(EXTRACT(minute FROM cstamp) / 5) AS m5, min(price) OVER w, max(price) OVER w, first_value(price) OVER w, last_value(price) OVER w FROM trades Where date_trunc('hour',cstamp) = timestamp '2013-03-29 09:00:00' WINDOW w AS ( PARTITION BY date_trunc('hour',cstamp), floor(extract(minute FROM cstamp) / 5) ORDER BY date_trunc('hour',cstamp) ASC, floor(extract(minute FROM cstamp) / 5) ASC ) ORDER BY cstamp; ``` Here's a piece of the result: ``` cstamp price h m5 min max first last "2013-03-29 09:19:14";77.00000;"2013-03-29 09:00:00";3;77.00000;77.00000;77.00000;77.00000 "2013-03-29 09:26:18";77.00000;"2013-03-29 09:00:00";5;77.00000;77.800…

Accepted Answer

SQL Fiddle

All the functions you used act on the window frame, not on the partition. If omitted the frame end is the current row. To make the window frame to be the whole partition declare it in the frame clause (`range...`):

```
SELECT  
    cstamp,
    price,
    date_trunc('hour',cstamp) AS h,
    floor(EXTRACT(minute FROM cstamp) / 5) AS m5,
    min(price) OVER w,
    max(price) OVER w,
    first_value(price) OVER w,
    last_value(price) OVER w
FROM trades
Where date_trunc('hour',cstamp) = timestamp '2013-03-29 09:00:00'
WINDOW w AS (
    PARTITION BY date_trunc('hour',cstamp) , floor(extract(minute FROM cstamp) / 5)
    ORDER BY cstamp
    range between unbounded preceding and unbounded following
    )
ORDER BY cstamp;
```

PostgreSQL - how should I use first_value()?

Solution

Problem

Related problems