postgresql 获取一些列的第一个值，在postgres中[已关闭]

amrnrhlw 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(1)|浏览(129)

- 已关闭**。此问题为opinion-based。当前不接受答案。
- 想要改进此问题吗？**请更新此问题，以便editing this post可以用事实和引文来回答。

3天前关闭。
Improve this question
我有一个表，其中的行如下所示：

ts,ticker,m1,m5,m15,m30,h1,h2,h4,d1,high,vwap,low
2020-12-03 00:00:00.000000,DOGEUSDT,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.003366,0.0033595364,0.003356
2020-12-03 00:01:00.000000,DOGEUSDT,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.003371,0.0033696603,0.003365
2020-12-03 00:02:00.000000,DOGEUSDT,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.003376,0.0033727777,0.00337
2020-12-03 00:03:00.000000,DOGEUSDT,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.003376,0.0033747195,0.003373

查询始终相同：对于特定的股票代码，返回某个时间范围内的聚合数据。
因此，在聚合数据中，时间戳应该是第一条记录（按时间排序），而自动收报机（在所有返回行中都相同）也应该来自第一行。
我已经这样做了：

SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,

这一条似乎也起作用了：

SELECT
    min(ts) as ts,
    min(ticker) as ticker,

现在，这个也可以：

SELECT
    min(ts) as ts,
    min(ticker) as ticker,
FROM ...
GROUP BY ts, ticker
LIMIT 1

推荐的方法是什么？为什么？

postgresql

来源：https://stackoverflow.com/questions/75193938/get-the-first-value-of-some-columns-in-postgres

1条答案

按热度按时间

nfzehxib1#

上面编写的所有查询返回不同的数据。要更清楚地了解这一点：

CREATE TABLE table5 (
    ts timestamp NULL,
    ticker text NULL,
    m1 float8 NULL
);

INSERT INTO table5 (ts, ticker, m1) VALUES('2020-12-03 00:01:00.000', 'c1', 0.00336);
INSERT INTO table5 (ts, ticker, m1) VALUES('2020-12-03 00:03:00.000', 'a1', 0.00337);
INSERT INTO table5 (ts, ticker, m1) VALUES('2020-12-03 00:02:00.000', 'b1', 0.00336);
INSERT INTO table5 (ts, ticker, m1) VALUES('2020-12-03 00:00:00.000', 'e1', 0.00337);

/*  Query 1 
This query return only first row without sorting by date 
*/
SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker
from table5 

Return: 
ts                     |ticker|
-----------------------+------+
2020-12-03 00:01:00.000|c1    |

/*  Query 2 
even if you add "order by ts" to this query, it still won't return you the first values of sorted date. This is return only minimum value of column ts, and same time min value of ticker column 
*/
SELECT
    min(ts) as ts,
    min(ticker) as ticker
from table5 

Return: 
ts                     |ticker|
-----------------------+------+
2020-12-03 00:00:00.000|a1    |

/*  Query 3 
this is to same as query 2, but using gropping columns 
*/
SELECT
    min(ts) as ts,
    min(ticker) as ticker
FROM table5 
GROUP BY ts, ticker
LIMIT 1

Return: 
ts                     |ticker|
-----------------------+------+
2020-12-03 00:00:00.000|e1    |

如果需要按日期对行进行排序，并且之后只返回前几个值，则可以使用以下查询

select 
    ts, 
    ticker, 
    m1 
from table5 
order by ts 
limit 1;

如果你需要使用agregate函数，那么试试这个查询：

select 
    first_value(ts) over (order by ts) as ts, 
    first_value(ticker) over (order by ts) as ticker, 
    first_value(m1) over (order by ts) as m1 
from table5 
limit 1;

这些查询返回相同的数据：

ts                     |ticker|m1     |
-----------------------+------+-------+
2020-12-03 00:00:00.000|e1    |0.00337|

赞(0）回复(0）举报 2023-01-25

我来回答

postgresql 获取一些列的第一个值，在postgres中[已关闭]

1条答案

相关问题

热门标签

最新问答