使用postgreSQL创建股票图表

Question

我正在尝试使用postgreSQL制作一个股票图表，如下所示。

​

​

我的数据应该是这样的：

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

期望的结果

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

我在想，我可能需要使用窗口函数，但我似乎就是不能正确使用这个函数。

Answer 1

为此，您可以使用预期的聚合函数，然后连接到使用LAST_VALUE窗口函数的派生表：

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

收益率：

| 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