如何在Clickhouse中根据日期和时间段选择数据 [英] HOW to SELECT data basing on both a period of date and a period of time in clickhouse
问题描述
我想同时按 yyyymmdd
(日期)和 hhmmss
(时间)过滤某些数据,但Clickhouse不支持 time
类型.因此,我选择 datetime
进行组合.但是如何做这样的事情:
I want to filter some data by both yyyymmdd
(date) and hhmmss
(time), but clickhouse don't support time
type. So I choose datetime
to combine them. But how to do such things:
这是 dolphindb
的代码(它支持 second
类型来表示 hhmmss
.
This is code of dolphindb
(which supports second
type to represent hhmmss
.
select avg(ofr + bid) / 2.0 as avg_price
from taq
where
date between 2007.08.05 : 2007.08.07,
time between 09:30:00 : 16:00:00
group by symbol, date
这是 clickhouse
的代码,但是是逻辑上有问题的代码.
This is code of clickhouse
, but a logical problematic code.
SELECT avg(ofr + bid) / 2.0 AS avg_price
FROM taq
WHERE
time BETWEEN '2007-08-05 09:30:00' AND '2007-08-07 16:00:00'
GROUP BY symbol, toYYYYMMDD(time)
;
如何像 dolphindb
代码一样在sql中表达它?
how to express it in sql just like the dolphindb
code?
推荐答案
假设您只想在正常交易时间内平均交易价格(不包括非盘后交易),那么可以采取以下解决方案:
Assume that you just want to average the trading price in normal trading hours, excluding after hour trading, then a possible solution:
SELECT avg(ofr + bid) / 2.0 AS avg_price
FROM taq
WHERE
toYYYYMMDD(time) BETWEEN 20070805 AND 20070807 AND
toYYYYMMDDhhmmss(time)%1000000 BETWEEN 93000 and 160000
GROUP BY symbol, toYYYYMMDD(time)
这会在指定的日期和时间内过滤 taq 表.
This filters the taq table within specified date and time.
这篇关于如何在Clickhouse中根据日期和时间段选择数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!