如何返回GROUP BY查询中每个GROUP的最后一个值

Question

我需要获取GROUP BY查询的每个GROUP的最后一条记录(CLOSE字段(。

我有以下数据结构

minute;symbol;timeframe;open;high;low;close;displayed
2021-02-22 00:00:00;EURUSD;M0;1.21103;1.21103;1.21103;1.21103;1
2021-02-22 00:00:00;EURUSD;M0;1.21103;1.21108;1.21103;1.21108;1
2021-02-22 00:02:00;EURUSD;M0;1.21127;1.21127;1.21127;1.21127;1
2021-02-22 00:02:00;EURUSD;M0;1.21127;1.21133;1.21127;1.21133;1
2021-02-22 00:03:00;EURUSD;M0;1.21125;1.21125;1.21125;1.21125;1
2021-02-22 00:03:00;EURUSD;M0;1.21125;1.21125;1.21109;1.21109;1

我尝试了下面的代码，但它似乎从整个数据集返回了最新的CLOSE

SELECT minute, id, time, date, open, 
(SELECT close FROM graphs GROUP BY minute ORDER BY minute DESC LIMIT 1) AS close, 
MIN(low) AS low, 
MAX(high) AS high
FROM graphs
WHERE 
symbol = 'EURUSD' AND 
date >= '1420070400' AND 
date <= '1640995199' AND 
timeframe = 'M0' AND 
showed = '1'
GROUP BY minute

有什么建议吗？？

Answer 1

使用row_number()。我不确定确切的逻辑是什么，因为查询引用的time不在示例数据中，而是类似于：

select g.*
from (select g.*,
row_number() over (partition by minute , symbol order by time desc) as seqnum
from graphs g
where g.symbol = 'EURUSD' AND 
g.date >= '1420070400' AND 
g.date <= '1640995199' AND 
g.timeframe = 'M0' AND 
g.showed = '1'
) g
where seqnu = 1;