可以使用asof函数，asof函数可以确定每一行所属的group，示例如下：

Y=[09:00m,10:00m ,11:00m,14:00m,15:00m,16:00m]

select avg(price) from trade group by symbol,date(timestamp),Y[Y.asof(minute(timestamp))]

数据：

n = 1000000
date = take(2019.01.01..2019.01.15,n)
time=(09:30:00.000 + rand(2*60*30*1000, n/2)).sort!() join (13:30:00.000 +rand(2*60*60*1000 , n/2)).sort!()
timestamp= concatDateTime(date,time)
price = 100+cumsum(rand(0.02,n)-0.01)
volume= rand(100,n)
symbol = rand(`6500`6011`0633`6633,n)
trade = table(symbol,timestamp,price,volume).sortBy!(`symbol`timestamp)
Y=[09:00m,10:00m ,11:00m,14:00m,15:00m,16:00m]

解决方案：

方案一：

我们可以把这两个半个小时的数据单独拎出来，与其他数据分开计算， 最后再将所有计算结果用UNION拼接起来。

t = select *
from
(
    (select 
        avg(price),
        y 
      from trade 
      where hour(timestamp) not in (11,13) 
      group by symbol,date(timestamp),Y[Y.asof(minute(timestamp))] as y
      )
union 
    (select 
        avg(price),
        minute(11:00:00.000) as y 
      from trade 
      where hour(timestamp) in (11,13) 
      group by symbol,date(timestamp) 
      )
)
order by symbol,date_timestamp,y

方案二：

利用asof进行时间的归并，将11:00 ~11:30和13:30~14:00 的数据都归并到11：00。

n = 1000000
date = take(2019.01.01..2019.01.15,n)
time=(09:30:00.000 + rand(2*60*30*1000, n/2)).sort!() join (13:30:00.000 +rand(2*60*60*1000 , n/2)).sort!()
timestamp= concatDateTime(date,time)
price = 100+cumsum(rand(0.02,n)-0.01)
volume= rand(100,n)
symbol = rand(`6500`6011`0633`6633,n)
trade = table(symbol,timestamp,price,volume).sortBy!(`symbol`timestamp)
X = [9,10 ,11,14,15,16]
timer(1) b = select avg(price),y from trade group by symbol,date(timestamp),X[X.asof(hour(timestamp))] as y

性能测试：