首页 文章

如何获取组中的第一个和最后一个项以及聚合结果？

提问于
浏览 次
0

我有一个股票市场价格数据表 . 我想将刻度分为开，高，低，近，分钟 .

注意：时间戳是int hhmmssmmm所以/ 100000给我一分钟分组（hhmm）

我可以做一个简单的小组并获得高低：

select (min(time_stamp)/100000) as [time], max(price) as [high], min(price) as [low] from [20160104] 
where symbol = 'AAPL' and price_field = 0
group by (time_stamp/100000) order by [time]

然后我可以在min（id）上进行内部联接以获得该组的第一个价格：

select a.time_stamp / 100000 as bar_time, a.price as bar_open, b.bar_high, b.bar_low
FROM [20160104] as a
join
(
    select max(price) as bar_high, min(price) as bar_low, min(id) as first_id, max(id) as last_id
    from [20160104] 
    where symbol = 'AAPL' and price_field = 0
    group by (time_stamp / 100000)
) as b 
on b.first_id = a.id

我也可以用max（id）做同样的事情来获得组中的最后价格 .

但是如何以最小（价格）和最大价格（价格）获得同一行的第一个和最后一个价格？

sql sql-server

2 回答

  • 1

    您可以将两个联接放在一个语句中，如下所示 . （为清楚起见，我将内部选择移到了第一位，因为两个连接都依赖于它的结果 . ）

    select a.time_stamp / 100000 as bar_time, a.price as bar_open,
    b.bar_high, b.bar_low, c.price as bar_close
FROM 
(
    select max(price) as bar_high, min(price) as bar_low, min(id) as first_id, max(id) as last_id
    from [20160104] 
    where symbol = 'AAPL' and price_field = 0
    group by (time_stamp / 100000)
) as b
join [20160104] as a on b.first_id = a.id
join [20160104] as c on b.last_id = c.id
    回复于
  • 0

    您可以使用具有max和min函数的over（）来获取同一行中的最大值和最小值

    {

    select (min(time_stamp)/100000) as [time], max(price) OVER( PARTITION BY (time_stamp/100000) order by [time]) as [high], min(price) OVER( PARTITION BY (time_stamp/100000) order by [time]) as [low] from [20160104] where symbol = 'AAPL' and price_field = 0;

    }

    You can get more information about over function here.

    回复于

相关问题