Create a stock chart using postgreSQL

Question

I am trying to make one of those stock charts using postgreSQL that will look like the following.

My data would look something like this:

stock_data    
stock_price   trade_datetime
5.1         | 1/1/2000 1:00 PM
6.2         | 1/1/2000 2:00 PM
5.0         | 1/2/2000 1:00 PM
3.4         | 1/2/2000 2:00 PM
4.8         | 1/2/2000 3:00 PM
7.0         | 1/3/2000 2:30 PM
5.9         | 1/3/2000 5:55 PM

Desired result

MIN | MAX | AVG | close | date
5.1 | 6.2 | 5.65| 6.2   | 1/1/2000
3.4 | 5.0 | 4.4 | 4.8   | 1/2/2000
5.9 | 7.0 | 6.45| 5.9   | 1/3/2000

I am thinking I probably need to use windowed functions, but I just can't seem to get this one right.

Andrew Whitaker · Accepted Answer · 2019-03-01 19:57:49Z

You can do this by using the expected aggregate functions and then joining to a derived table that uses the LAST_VALUE window function:

SELECT
    MIN(stock_price) AS "MIN"
    , MAX(stock_price) AS "MAX"
    , AVG(stock_price) AS "AVG"
    , MAX(closing.closing_price) AS "close"
    , trade_datetime::date AS "date"
FROM
    stock_data
    INNER JOIN LATERAL (
      SELECT
        LAST_VALUE(stock_price) OVER (PARTITION BY trade_datetime::date) AS closing_price
      FROM
        stock_data AS closing_data
      WHERE closing_data.trade_datetime::date = stock_data.trade_datetime::date
    ) AS closing ON true
GROUP BY
    trade_datetime::date
ORDER BY
    trade_datetime::date ASC

Yields:

| MIN | MAX | AVG                | close | date                     |
| --- | --- | ------------------ | ----- | ------------------------ |
| 5.1 | 6.2 | 5.6500000000000000 | 6.2   | 2000-01-01T00:00:00.000Z |
| 3.4 | 5.0 | 4.4000000000000000 | 4.8   | 2000-01-02T00:00:00.000Z |
| 5.9 | 7.0 | 6.4500000000000000 | 5.9   | 2000-01-03T00:00:00.000Z |

DB Fiddle

Collectives™ on Stack Overflow

Create a stock chart using postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related