本教程重点介绍了一些常见场景下的 SQL 编写案例，通过优化前后性能对比或正确编写方法介绍，说明 DolphinDB SQL 脚本的使用技巧，案例分四类：条件过滤相关案例、分布式表相关案例、分组计算相关案例及元编程相关案例，共计22个应用实例，案例中用到的测试数据为基于真实数据结构模拟的2000只股票快照数据。
由于篇幅有限，此处选取了1个案例进行展示，点击推送底部 阅读原文 查看完整案例内容 👇👇
计算股票组合的价值
场景：进行指数套利交易回测时，计算给定股票组合的价值。
当数据量极大时，一般数据分析系统进行回测时，对系统内存及速度的要求极高。以下案例，展现了使用 DolphinDB SQL 语言可极为简洁地进行此类计算。
为了简化起见，假定某个指数仅由两只股票组成：AAPL 与 FB。模拟数据如下：
syms = take(`AAPL, 6) join take(`FB, 5)time = 2019.02.27T09:45:01.000000000 + [146, 278, 412, 445, 496, 789, 212, 556, 598, 712, 989]prices = 173.27 173.26 173.24 173.25 173.26 173.27 161.51 161.50 161.49 161.50 161.51quotes = table(take(syms, 100000) as Symbol, take(time, 100000) as Time, take(prices, 100000) as Price)weights = dict(`AAPL`FB, 0.6 0.4)ETF = select Symbol, Time, Price*weights[Symbol] as weightedPrice from quotes
优化前：
首先，需要将原始数据表的3列（时间，股票代码，价格）转换为同等长度但是宽度为指数成分股数量加1的数据表，然后向前补充空值（forward fill NULLs），进而计算每行的指数成分股对指数价格的贡献之和。示例如下：
timer { colAAPL = array(DOUBLE, ETF.Time.size()) colFB = array(DOUBLE, ETF.Time.size()) for(i in 0:ETF.Time.size()) { if(ETF.Symbol[i] == `AAPL) { colAAPL[i] = ETF.weightedPrice[i] colFB[i] = NULL } if(ETF.Symbol[i] == `FB) { colAAPL[i] = NULL colFB[i] = ETF.weightedPrice[i] } } ETF_TMP1 = table(ETF.Time, ETF.Symbol, colAAPL, colFB) ETF_TMP2 = select last(colAAPL) as colAAPL, last(colFB) as colFB from ETF_TMP1 group by time, Symbol ETF_TMP3 = ETF_TMP2.ffill() t1 = select Time, rowSum(colAAPL, colFB) as rowSum from ETF_TMP3}
以上代码块耗时 713 ms。
优化后：
使用 pivot by 子句根据时间、股票代码对于数据表重新排序，将时间作为行，股票代码作为列，然后使用ffill 函数填充 NULL 元素，使用 avg 函数计算均值，最后 rowSum 函数计算每个时间点的股票价值之和，仅需以下一行代码，即可实现上述所有步骤。示例如下：
timer t2 = select rowSum(ffill(last(weightedPrice))) from ETF pivot by Time, Symbol
查询耗时 23 ms。
each(eqObj, t1.values(), t2.values()) //true
与优化前写法相比，优化后写法查询性能提升约 30 倍。
此例中，仅以两只股票举例说明，当股票数量更多时，使用循环遍历的方式更为繁琐，而且性能极低。
pivot by 是 DolphinDB SQL 独有的功能，是对标准SQL语句的拓展，可以将表中两列或多列的内容按照两个维度重新排列，亦可配合数据转换函数使用。不仅编程简洁，而且无需产生中间过程数据表，有效避免了内存不足的问题，极大地提升了计算速度。
以下是与此场景类似的另外一个案例，属于物联网典型场景。
场景：假设一个物联网场景中存在三个测点进行实时数据采集，期望针对每个测点分别计算一分钟均值，再对同一分钟的三个测点均值求和。
首先，产生模拟数据，示例如下：
N = 10000t = table(take(`id1`id2`id3, N) as id, rand(2021.01.01T00:00:00.000 + 100000 * (1..10000), N) as time, rand(10.0, N) as value)
使用 bar 函数对时间做一分钟聚合，并使用 pivot by 子句根据分钟、测点对数据表重新排序，将分钟作为行，测点作为列，然后使用 ffill 函数填充 NULL 元素，使用 avg 函数计算均值，然后再使用 rowSum 函数计算每个时间点的测点值之和。最后使用 group by 子句结合 interval 函数对于缺失值进行填充。
timePeriod = 2021.01.01T00:00:00.000 : 2021.01.01T01:00:00.000timer result = select sum(rowSum) as v from ( select rowSum(ffill(avg(value))) from t where id in `id1`id2`id3, time between timePeriod pivot by bar(time, 60000) as minute, id) group by interval(minute, 1m, "prev") as minute
查询耗时 12 ms。
DolphinDB SQL 案例教程 完整目录
1 测试环境说明
2 条件过滤相关案例
2.3.1 过滤条件与序列无关
2.3.2 过滤条件与序列有关
2.1 where 条件子句使用 in 关键字
2.2 分组数据过滤
2.3 where 条件子句使用逗号或 and
3 分布式表相关案例
3.1 分区剪枝
3.2 group by 并行查询
3.3 分组查询使用 map 关键字
4 分组计算相关案例
4.1 查询最新的 N 条记录
4.2 计算滑动 VWAP
4.3 计算累积 VWAP
4.4 计算 N 股 VWAP
4.5 分段统计股票价格变化率
4.6 计算不同连续区间的最值
4.7 不同聚合方式计算指标
4.8 计算股票收益波动率
4.9 计算股票组合的价值
4.10 根据成交量切分时间窗口
4.11 股票因子归整
4.12 根据交易额统计单子类型
5 元编程相关案例
5.1 动态生成 SQL 语句案例 1
5.2 动态生成 SQL 语句案例 2
点击推送底部 阅读原文 查看完整案例内容 👇👇
关于我们
DolphinDB是一款专为海量时序数据设计的数据库产品，将编程语言、数据库和分布式计算从底层进行一体化设计，开创性地解决了快速开发、高速运行和简单部署三者难以兼顾的难题，为海量结构化数据的快速存储、检索、 分析及计算量身订做一站式解决方案。
DolphinDB目前被广泛应用于金融市场全域（低频、中频和高频）的数据存储、数据清洗、因子分析、策略回测、实时计算等场景中。在物联网领域，尤其是化工、电力、能源、水务等行业，DolphinDB通过联合各行业头部集成商，将DolphinDB集成到行业解决方案中，实现海量数据的存储和实时计算，为用户提供性能优异的整体解决方案。
Explore More
▼
