sqlite的第一和最后一个功能

I want to get the first and last by date of each group. I couldn't find documentation for it..

我想要得到每组的第一个和最后一个日期。我找不到证明文件。

Basically the table is as:

这个表格基本上是这样的:

select start, --timestamp
       first(open) partition by start order by asc, 
       max(high), 
       min(low), 
       last(close) partition by start order by asc
from candles_USDT_BTC
group by round(start/1800); --group by 15 min

Of course the first and last function does not exists, just for you guys to get the idea...

当然，第一个和最后一个函数并不存在，只是为了让你们明白……

2 个解决方案

#1

You can use additional joins:

您可以使用其他连接:

select cub.s, cub.max_high, cub.min_low, f.open, l.close
from (select round(start/1800) as s, max(high) as max_high, min(low) as min_low,
             min(start) as first_start,
             max(start) as last_start
      from candles_USDT_BTC
      group by round(start/1800)
     ) cub join
     candles_USDT_BTC f
     on f.start = cub.first_start join
     candles_USDT_BTC l
     on l.start = cub.last_start;

#2

You can use correlated subqueries to look up the row with the smallest/largest value in the group:

您可以使用相关子查询查找组中值最小/最大的行:

SELECT start,
       (SELECT open
        FROM candles_USDT_BTC AS c2
        WHERE round(c2.start / 1800) = round(candles_USDT_BTC.start / 1800)
        ORDER BY start ASC
        LIMIT 1),
       max(high),
       min(low),
       (SELECT close
        FROM candles_USDT_BTC AS c2
        WHERE round(c2.start / 1800) = round(candles_USDT_BTC.start / 1800)
        ORDER BY start DESC
        LIMIT 1)
FROM candles_USDT_BTC
GROUP BY round(start / 1800);

#1