我的表结构:
trade_dt, security_id
2020.01.11 00001
2020.01.11 00002
2020.01.11 00003
2020.01.12 00002
2020.01.12 00003
2020.01.12 00004
···
请问有什么办法可以快速统计每期新入的股票数量?
没有足够的数据
(゚∀゚ )
暂时没有任何数据
Davis 关注了问题 · 1月13日
我的表结构:
trade_dt, security_id
2020.01.11 00001
2020.01.11 00002
2020.01.11 00003
2020.01.12 00002
2020.01.12 00003
2020.01.12 00004
···
请问有什么办法可以快速统计每期新入的股票数量?
关注 3 回答 1
Davis 赞了问题 · 1月13日
我的表结构:
trade_dt, security_id
2020.01.11 00001
2020.01.11 00002
2020.01.11 00003
2020.01.12 00002
2020.01.12 00003
2020.01.12 00004
···
请问有什么办法可以快速统计每期新入的股票数量?
我的表结构: {代码...} 请问有什么办法可以快速统计每期新入的股票数量?
关注 3 回答 1
Davis 赞了文章 · 1月11日
DolphinDB
DolphinDB 是以 C++ 编写的一款分析型的高性能分布式时序数据库,使用高吞吐低延迟的列式内存引擎,集成了功能强大的编程语言和高容量高速度的流数据分析系统,可在数据库中进行复杂的编程和运算,显著减少数据迁移所耗费的时间。
DolphinDB 通过内存引擎、数据本地化、细粒度数据分区和并行计算实现高速的分布式计算,内置流水线、 Map Reduce 和迭代计算等多种计算框架,使用内嵌的分布式文件系统自动管理分区数据及其副本,为分布式计算提供负载均衡和容错能力。
DolphinDB database 支持类标准 SQL 的语法,提供类似于 Python 的脚本语言对数据进行操作,也提供其它常用编程语言的 API,在金融领域中的历史数据分析建模与实时流数据处理,以及物联网领域中的海量传感器数据处理与实时分析等场景中表现出色。
TimescaleDB
TimescaleDB 是目前市面上唯一的开源且完全支持 SQL 的时序数据库。它在 PostgreSQL 数据库的基础上进行开发,本质上是一个 PostgreSQL 的插件。
TimescaleDB 完全支持 SQL 且拥有 PostgreSQL 的丰富生态、并针对时间序列数据的快速插入和复杂查询进行了优化,支持自动分片,支持时间空间维度自动分区,支持多个 SERVER、多个 CHUNK 的并行查询,内部写优化(批量提交、内存索引、事务支持、数据倒灌)。
然而,目前 TimescaleDB 仍不支持水平扩展(集群),即不能动态增加新的数据结点以写入数据(Write clustering for multi-node Timescale deployments is under active development. github.com/timescale/t…),只支持通过 PostgreSQL 的流复制(streaming replication)实现的只读集群(read-only clustering)。
在本报告中,我们对 TimescaleDB 和 DolphinDB,在时间序列数据集上进行了性能对比测试。测试涵盖了CSV数据文件的导入导出、磁盘空间占用、查询性能等三方面。在我们进行的所有测试中,DolphinDB 均表现得更出色,主要结论如下:
10多倍
,大数据集的情况下导入性能是其100多倍
,而且在导入过程中可以观察到随着导入时间的增加,TimescaleDB 的导入速率不断下降,而 DolphinDB 保持稳定。3 倍
左右。1/6
,大数据集下占用空间仅仅是 TimescaleDB 的 1/17
。50多倍
;在15个测试样例中性能为 TimescaleDB 10 ~ 50 倍
; 在10个测试样例中性能是 TimescaleDB 的数倍;仅有2个测试样例性能低于TimescaleDB。TimescaleDB 目前仍未支持能够写入数据的集群,因此我们使用单机进行测试。单机的配置如下。
主机:DELL OptiPlex 7060
CPU :Intel Core i7-8700(6 核 12 线程 3.20 GHz)
内存:32 GB (8GB × 4, 2666 MHz)
硬盘:2T HDD (222 MB/s 读取;210 MB/s 写入)
OS:Ubuntu 16.04 LTS
DolphinDB 的测试版本为 Linux v0.89 (2019.01.31),最大内存设置为28GB
。测试时使用的 PostgreSQL 版本为 Ubuntu 10.6-1 on x86_64, TimescaleDB 插件的版本为 v1.1.1。根据 TimescaleDB 官方指南推荐的性能调优方法,结合测试机器的实际硬件配置,我们在 pgtune.leopard.in.ua/ 网站上生成了配置文件,同时参考了 wiki.postgresql.org/wiki/Tuning… 这一官方配置指南作了优化,主要将 shared_buffers
和 effective_cache_size
设置为 16GB
,并根据12线程 CPU 设置了 parallel workers
,由于仅使用一块机械硬盘,我们将 effective_io_concurrency
设置为 1,具体修改的配置详见附录中 postgresql_test.conf
文件。
本报告测试了小数据量级(4.2 GB) 和 大数据量级(270 GB) 下 DolphinDB 和 TimescaleDB 的表现情况:
在小数据量级的测试中我们预先将硬盘中的分区数据表全部加载到内存中,即在 DolphinDB 中使用 loadTable(memoryMode=true),在 PostgresQL 中使用 pg_prewarm 插件将其加载至 shared_buffers。
在大数据量级的测试中我们不预先加载硬盘分区表,查询测试的时间包含磁盘 I/O 的时间,为保证测试公平,每次启动程序测试前均通过 Linux 系统命令 sync; echo 1,2,3 | tee /proc/sys/vm/drop_caches
分别清除系统的页面缓存、目录项缓存和硬盘缓存。
以下是两个数据集的表结构和分区方法:
4.2 GB 设备传感器记录小数据集(CSV 格式,3 千万条)
我们从 TimescaleDB 官方给出的样例数据集中选择了 devices_big
作为小数据集来测试,数据集包含 3000 个设备在 10000 个时间间隔(2016.11.15 - 2016.11.19)上的 传感器时间
, 设备 ID
, 电池
, 内存
, CPU
等时序统计信息。
来源:docs.timescale.com/v1.1/tutori…
下载地址:timescaledata.blob.core.windows.net/datasets/de…
数据集共 3
千万 条数据(4.2 GB
CSV),压缩包内包含一张设备信息表和一张设备传感器信息记录表,表结构以及分区方式如下:
device_info 表
readings 表
数据集中device_id
这一字段有 3000 个不同的值,这些值在 readings 表的记录中反复出现,用 text 类型不仅占用大量空间而且查询效率较低,但是在 TimescaleDB 中我们难以对这一字段采用 enum 类型,而 DolphinDB 的 Symbol 类型简单高效地解决了存储空间和查询效率这两大问题。
同样,对于bssid
和ssid
这两个字段表示设备连接的 WiFi 信息,在实际中因为数据的不确定性,虽然有大量的重复值,但并不适合使用 enum 类型。
我们在 DolphinDB database 中的分区方案是将time
作为分区的第一个维度,按天分为 4 个区,分区边界为[2016.11.15 00:00:00, 2016.11.16 00:00:00, 2016.11.17 00:00:00, 2016.11.18 00:00:00, 2016.11.19 00:00:00]
;再将device_id
作为分区的第二个维度,每天一共分 10 个区,最后每个分区所包含的原始数据大小约为100 MB
。
我们尝试了在 TimescaleDB 中将device_id
作为分区的第二个维度,但经测试90%查询样例的性能反而不如仅由时间维度进行分区,因此我们选择仅按照时间维度和按天分为4个区,该维度和 DolphinDB 的分区方式相同,而device_id
这一维度以官方推荐的建立索引的方式(参考docs.timescale.com/v1.0/using-…)来加快查询速度,如下所示。
create index on readings (device_id, time desc);
create index on readings (ssid, time desc);
复制代码
270 GB 股票交易大数据集(CSV 格式,23 个 CSV,65 亿条)
我们将纽约证券交易所(NYSE)提供的 2007.08.01 - 2007.08.31 一个月的股市 Level 1 报价数据作为大数据集进行测试,数据集包含 8000 多支股票在一个月内的交易时间、股票代码、买入价、卖出价、买入量、卖出量
等报价信息。
数据集中共有 65 亿(6,561,693,704)条报价记录,一个 CSV 中保存一个交易日的记录,该月共 23 个交易日,未压缩的 CSV 文件共计 270 GB。 来源:www.nyse.com/market-data…。
taq 表
我们按date(日期)
,symbol(股票代码)
进行分区,每天再根据 symbol 分为 100 个分区,每个分区大概 120 MB 左右。
从 CSV 文件导入数据
DolphinDB database 使用以下脚本导入:
timer {
for (fp in fps) {
loadTextEx(db, `taq, `date`symbol, fp, ,schema)
print now() + ": 已导入 " + fp
}
}
复制代码
4.2 GB 设备传感器记录小数据集共3千万条数据导入用时 20 秒
, 平均速率 1,500,000 条/秒
270 GB 股票交易大数据集共 6,561,693,704 条数据(TAQ20070801 - TAQ20070831
23 个文件),导入用时 38 分钟
在 TimescaleDB 的导入中,由于 timescaledb-parallel-copy
工具不支持 CSV 首行为列名称,我们先用 tail -n +2
跳过 CSV 首行,再将文件流写入其标准输入。
for f in /data/TAQ/csv/*.csv ; do
tail -n +2 $f | timescaledb-parallel-copy
--workers 12
--reporting-period 1s
--copy-options "CSV"
--connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable"
--db-name test
--table taq
--batch-size 200000
echo "文件 $f 导入完成"
done
复制代码
4.2 GB 设备传感器记录小数据集共3千万条数据导入用时 5 分钟 45 秒
, 平均速率 87,000 条/秒
270 GB 股票交易大数据集仅 TAQ20070801, TAQ20070802, TAQ20070803, TAQ20070806, TAQ20070807
五个文件(总大小 70 GB
)所包含的 16.7 亿
条数据导入用时 24 小时
,导入速率 19400 条/秒
,预计将数据全部 270 GB
数据导入需要 92 小时
。
导入性能如下表所示:
结果显示, DolphinDB 的导入速率远大于 TimescaleDB 的导入速率,数据量大时差距更加明显,而且在导入过程中可以观察到随着导入时间的增加,TimescaleDB 的导入速率不断下降,而 DolphinDB 保持稳定。
另,TimescaleDB 在导入小数据集后仍需花费 2 min 左右的时间建立索引。
导出数据为 CSV 文件
在 DolphinDB 中使用 saveText((select * from readings), '/data/devices/readings_dump.csv')
进行数据导出。
在 TimescaleDB 中使用 time psql -d test -c "COPY (SELECT * FROM readings) TO /data/devices/devices_dump.csv DELIMITER ',' CSV"
进行数据导出。
小数据集的导出性能如下表所示:
导入数据后对 TimescaleDB 和 DolphinDB 数据库占用空间的分析如下表所示:
DolphinDB 的空间利用率远大于 TimescaleDB,而且 TimescaleDB 中数据库占用的存储空间甚至大于原始 CSV 数据文件的大小,这主要有以下几方面的原因:
SELECT create_hypertable('readings', 'time', chunk_time_interval => interval '1 day')
将原始数据表转化为 hypertable 抽象表来为不同的数据分区提供统一的查询、操作接口,其底层使用 hyperchunk 来存储数据,经分析发现 hyperchunk 中对时序数据字段的索引共计 0.8 GB,对 device_id, ssid 两个字段建立的索引共计 2.3 GB。我们一共对比了以下八种类别的查询:
4.2 GB 设备传感器记录小数据集查询测试
对于小数据集的测试,我们先将数据表全部加载至内存中。
DolphinDB 使用 loadTable(memoryMode=true)
加载至内存。
TimescaleDB 使用 select pg_prewarm('_hyper_2_41_chunk')
加载至 shared_buffers。
查询性能如下表所示。查询脚本见附录。
对于抽样查询,TimescaleDB 中有 tablesample 子句对数据表进行抽样,参数是采样的比例,但只有两种抽样方式(system, bernoulli),system 方式按数据块进行取样,性能较好,但采样选中的块内的所有行都会被选中,随机性较差。bernoulli 对全表进行取样,但速度较慢。这两种取样方式不支持按某一个字段进行取样;而 DolphinDB 不支持全表取样,只支持按分区取样,由于实现方式不同,我们不进行性能对比。
对于插值查询,TimescaleDB (PostgreSQL) 无内置插值查询支持,需要上百行代码来实现,见 wiki.postgresql.org/wiki/Linear… ;而 DolphinDB 支持 4 种插值方式,ffill 向后取非空值填充、bfill 向前去非空值填充、lfill 线性插值、nullFill 指定值填充。
对于对比查询,TimescaleDB 的对比查询功能由 PostgresQL 内置的 tablefunc 插件所提供的 crosstab() 函数实现,但是从样例查询中可以看出该函数有很大的局限性:
第一,它需要用户手动硬编码第二个维度(行)中所有可能的取值和对应的数据类型,无法根据数据动态生成,非常繁琐,因此不能对动态数据或取值多的字段使用。
第二,它只能根据 text 的类型维度进行整理,或者由其它类型的维度事先转换为 text 类型。数据量大时该转换操作效率低下且浪费空间。
而 DolphinDB 原生支持 pivot by 语句,只需指定分类的两个维度即可自动整理。
对于关联查询,双时间连接(asof join)对于时间序列数据分析非常方便。DolphinDB 原生支持 asof join 而 PostgresQL 暂不支持 github.com/timescale/t…。
使用 count(*) 查询总记录数时,TimescaleDB 会对全表进行扫描,效率极低。
270 GB 股票交易大数据集查询测试
在大数据量级的测试中我们不预先加载硬盘分区表至内存,查询测试的时间包含磁盘 I/O 的时间,为保证测试公平,每次启动程序测试前均通过 Linux 系统命令sync; echo 1,2,3 | tee /proc/sys/vm/drop_caches
清除系统的页面缓存、目录项缓存和硬盘缓存,启动程序后依次执行所有测试样例一遍。
查询性能如下表所示。查询脚本见附录。
device_info:devices.csv
readings:readings.csv
TAQ:TAQ.csv
安装、配置、启动脚本:test_dolphindb.sh
配置文件:dolphindb.cfg
小数据集测试完整脚本:test_dolphindb_small.txt
大数据集测试完整脚本:test_dolphindb_big.txt
安装、配置、启动脚本:test_timescaledb.sh
小数据集测试完整脚本:test_timescaledb_small.sql
大数据集测试完整脚本:test_timescaledb_big.sql
PostgreSQL修改配置:postgresql_test.conf
PostgreSQL完整配置:postgresql.conf
PostgreSQL权限配置:pg_hba.conf
股票代码的所有可能值:symbols.txt
创建Symbol枚举类型的SQL语句:make_symbol_enum.sql
生成Symbol枚举类型的脚本:make_symbol_enum.coffee
作者:DolphinDB
链接:https://juejin.cn/post/691408...
来源:掘金
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
DolphinDB 是以 C++ 编写的一款分析型的高性能分布式时序数据库,使用高吞吐低延迟的列式内存引擎,集成了功能强大的编程语言和高容量高速度的流数据分析系统,可在数据库中进行复杂的编程和运算,显著减少数据迁移所耗费的时间。
赞 4 收藏 0 评论 0
Davis 赞了回答 · 1月10日
通过http接口连接的比如web集群管理器20分钟会话断开, socket连接不会关闭,服务端可以通过函数closeSession来关闭指定会话。
通过http接口连接的比如web集群管理器20分钟会话断开, socket连接不会关闭,服务端可以通过函数closeSession来关闭指定会话。
关注 2 回答 1
Davis 赞了问题 · 1月10日
想问下,默认配置下,客户端连接到dolphindb的数据库连接会被服务器关闭吗?如果会,大概是多久啊?还是说一直不会关闭?
想问下,默认配置下,客户端连接到dolphindb的数据库连接会被服务器关闭吗?如果会,大概是多久啊?还是说一直不会关闭?
关注 2 回答 1
Davis 赞了回答 · 1月10日
交易回测在DolphinDB中的实现,可以参考:https://zhuanlan.zhihu.com/p/...
上述案例可以通过下面的函数实现:
defg maxDrawDown(price){
return max(1.0 - price\price.cummax())
}
tb = table(rand(1 2 3 , 10) as id, rand(1.5, 10) as price)
select maxDrawDown(price) as group_max_drawdown from tb group by id
交易回测在DolphinDB中的实现,可以参考:[链接]上述案例可以通过下面的函数实现: {代码...}
关注 2 回答 1
Davis 赞了问题 · 1月10日
DolphinDB有许多的内置函数,请问有没有适合计算金融中最大回撤的函数?
如下图所示:
DolphinDB有许多的内置函数,请问有没有适合计算金融中最大回撤的函数?如下图所示:
关注 2 回答 1
Davis 赞了回答 · 1月10日
可以通过:z.keys()
获取键。
可以通过:z.values()
获取值。
可以通过:z.keys()获取键。可以通过:z.values()获取值。
关注 2 回答 1
Davis 赞了问题 · 1月10日
我通过以下语句创建了一个字典,请问如何获取它的键和值?
x=1 2 3
y=4.5 7.8 4.3
z=dict(x,y);
我通过以下语句创建了一个字典,请问如何获取它的键和值? {代码...}
关注 2 回答 1
Davis 赞了回答 · 1月10日
DolphinDB中的setRetentionPolicy(dbHanle, retentionHours, [retentionDimension])函数可以设置数据保留时间,数据库会保留最近retiontionHours小时的数据。
示例如下:
db=database("dfs://db1",VALUE,2019.06.01..date(now()))
retentionHour=7*24
setRetentionPolicy(db,retentionHour,0);
DolphinDB中的setRetentionPolicy(dbHanle, retentionHours, [retentionDimension])函数可以设置数据保留时间,数据库会保留最近retiontionHours小时的数据。示例如下: {代码...}
关注 2 回答 1
查看全部 个人动态 →
(゚∀゚ )
暂时没有
(゚∀゚ )
暂时没有
注册于 2020-04-06
个人主页被 1.6k 人浏览
推荐关注