数据平台/DAI
由jliang创建，最终由rydeng 被浏览 5787 用户
什么是DAI
DAI (Data for AI) 是BigQuant研发的高性能分布式数据平台
DAI 快速入门
导入dai
DAI已经预装在BigQuant的 AIStudio 策略开发IDE中，无需额外安装。
import dai
分区表默认不能全表扫描，需要对表的字段（如instrument，date）做过滤；如需读取全表数据，则需设置full_db_scan参数为True。
import dai
dai.query("SELECT * FROM cn_stock_instruments WHERE date = '2020-06-04'")
指定股票代码
如下是一个是用DAI读取平台数据的示例：
从中国A股市场（cn_stock_bar1d）数据集中筛选出2020年1月1日至2021年1月1日期间，每支股票的“开盘价除以收盘价”和“交易量”数据：
import dai
df = dai.query("SELECT date, open/close, volume FROM cn_stock_bar1d WHERE date BETWEEN '2020-01-01' AND '2021-01-01' ").df()
dai.query 是DAI读取平台数据的接口，可以对平台所有数据做读取和计算SELECT date, open, close, volume FROM cn_stock_bar1d WHERE date BETWEEN '2023-01-01' AND '2023-05-01' AND instrument = '000001.SZ' 是 SQL 数据访问语言，SQL是使用简单也是使用最广泛的数据操作语言，DAI会解析SQL并调用C++执行引擎，充分利用现代 CPU/GPU 特性实现高性能计算
SELECT 读取/选择数据date, open/close, volume 需要的数据列
date 日期，open/close 表示计算开盘价除以收盘价，通过表达式和SQL算子可以实现对数据的衍生计算，支持使用python函数自定义算子volume 交易量FROM 用于指定应从哪个表格或者多个表读取数据cn_stock_bar1d 是 后复权日行情数据 表名WHERE date BETWEEN '2023-01-01' AND '2023-05-01' AND instrument = '000001.SZ' 按条件过滤数据，WHERE之后是条件
date BETWEEN '2023-01-01' AND '2023-05-01' 日期在 2023-01-01 和 2023-05-01 间AND 并且instrument = '000001.SZ' 股票代码为 000001.SZ
.SZ 是深交所的股票代码后缀，.SH 为上交所，平台所有数据定义保持一致从中国A股市场（cn_stock_bar1d）数据集中筛选出2020年1月1日至2021年1月1日的数据，用每支股票的开盘价除以收盘价作为股票日收益率：
import dai
df = dai.query("SELECT date, open/close AS daily_returns FROM cn_stock_bar1d WHERE date BETWEEN '2023-01-01' AND '2023-05-01' ").df()
DAI核心概念
DAI提供多种数据格式的访问和计算支持，包括SQL、DataFrame、Arrow等。
其中SQL：
全兼容和支持SQL标准
性能优化，支持CPU和GPU
专为量化因子和指标计算开发和优化的函数
双计算引擎
BigDB：DAI的高性能向量化计算引擎，主要用于大规模数据计算场景
BigStream：DAI的低延迟实时数据流计算引擎，有高度的并行性和快速的数据处理能力。可用于高频因子实时计算交易、高频交易、市场实时监控等
QuantChat 辅助生成查询和计算SQL，通过自然语言就可以实现因子构建
DataSource
DataSource是DAI组织数据的基本单元，类似于数据库中的table。
DataSource使用列式存储、支持数据分区和索引。基于高性能的数据表示，可以实现TB/s的数据从磁盘到内存高吞吐。
View是一种特殊的数据表，类似于数据库中的view。
View可以用于联合多个DataSource创建新的数据表，比如BigQuant平台的因子表，其实是一个view，底层由成百上千个不同的DataSource联合组成。通过专门的性能优化，View已能实现高性能的JOIN和裁剪，也能支持无限层次嵌套。
数据字典和文档
BigQuant平台数据字典入口：BigQuant数据字典和文档
数据表规范
BigQuant表命名由小写字母、数据和下划线构成，并且只能以字母开始，一般使用复数形式。
表名主要由如下几部分构成，i.e. cn_stock_indexes：
国家、地区或者市场的代码，常用代码如下（更多见术语部分）：
cn：中国/A股
us：美国/美股
hk：中国香港地区/港股
uk：英国/英国股票
jp：日本/日本股票
de：德国/德国股票
fr：法国/法国股票
sg：新加坡/新加坡股票
binance：币安交易所
okex：OKEX交易所
交易标的，常见如下（更多见术语部分）
stock：股票
bond：债券
cbond：可转债
future：期货
option：期权
fund：基金
forex：外汇
crypto：数字货币
数据类别：命名的第三部分表示数据类别，多个单词见用下划线(_)连接，示例如下 （更多见术语部分）：
bar1d：日线行情数据
bar1m：分钟行情数据
income_sheet：利润表
financial_pit_ttm：滚动十二期的PIT财务数据
dividend：分红信息
basic_info：基础信息
index_bar1d：指数日线行情数据
index_weights：指数权重
industry_components：行业成分
以下为数据表通用字段，它们在大部分表中都有一致的定义：
日期和时间
类型：datetime
instrument
类型：字符串 (categorical)
SQL入门教程
SQL，全称为结构化查询语言(Structured Query Language)，是用于管理关系数据库的标准语言。它可以用来查询、更新和操作数据库。SQL对于数据分析师和数据科学家来说是一项重要的技能，因为它让你能够从大型数据库中提取、过滤和分析数据。
本教程将指导你了解SQL的基础知识。我们将假设你已经有了Python和pandas的基础知识。
SQL基础
%%sql
在aistudio的notebook单元格中键入%%sql便能够使用sql通过dai查询数据，如下：
%%sql
from cn_stock_bar1d limit 10;
如果要给sql中的所有数据字典添加过滤区间，比如只查询2023-06-01之后的数据，可以如下操作：
%%sql {"date": ["2023-06-01", "2024-01-01"]}
from cn_stock_bar1d limit 10;
如果要将查询到的数据在python程序中继续处理，比如转成dataframe的格式，可以如下操作：
# import pandas as pd
# 去除行数限制/列数限制
# pd.set_option('display.max_rows', 200)
# pd.set_option('display.max_columns', 200)
result = _.df()
result
注：_是一个特殊变量，用来存储最近一个代码单元格的输出结果，也支持使用 __(两个下划线) 来访问倒数第二个单元格的输出，___(三个下划线) 来访问倒数第三个单元格的输出。
数据库和表
SQL数据库是一个或多个相关的表的集合。表是行（称为记录）和列（称为字段）的二维表示。每一行通常表示一个实体（如一个人、一个产品等），每一列代表实体的一个属性（如名字、年龄等）。
SQL语句
SQL语句是你告诉数据库想要做什么的方式。比如，你想要从数据平台中获取后复权日行情数据表的所有记录，你可以使用SELECT语句：
%%sql
SELECT * FROM cn_stock_bar1d;
*代表所有列，cn_stock_bar1d是表的名字，是后复权日行情数据。每条SQL语句都以分号结束。
SELECT语句
SELECT语句用于从数据库中选取数据。语法为：
SELECT column1, column2, ...
FROM table_name;
例如，从数据平台的cn_stock_bar1d表中选择date、instrument以及close列：
%%sql
SELECT date, instrument, close FROM cn_stock_bar1d;
WHERE子句
WHERE子句用于过滤记录，即通过condition筛选我们想要的数据。语法为：
SELECT column1, column2, ...
FROM table_name
WHERE condition;
例如，从日线表格中抽取特定日期的收盘价:
%%sql
SELECT date, instrument, close FROM cn_stock_bar1d WHERE date = '2023-02-08';
ORDER BY子句
ORDER BY子句用于对结果集按照一列或多列进行排序。语法为：
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
例如，从日线表中选择特定日期, 并查看换手率最高的三只股票:
%%sql
set full_db_scan=true;
SELECT instrument, name, turn FROM cn_stock_bar1d WHERE date = '2023-02-08' ORDER BY turn DESC LIMIT 3;
注：全表扫描时（一般是没有对date字段做过滤），需要在查询前执行set full_db_scan=true;以设置支持全表扫描。
聚合函数和分组
SQL也支持对数据进行聚合和分组，比如计算平均值、总和等。
SQL提供了一些内置的聚合函数，如COUNT()、SUM()、AVG()、MAX()、MIN()、LAG()还有统计学中的相关系数函数CORR()等。
例如，我们来求000001.SZ股票在指定一段日期内成交量和收盘价的相关系数：
%%sql
SELECT CORR(volume, close) as corr_vol_close
FROM cn_stock_bar1d
WHERE date between '2023-02-08' and '2023-02-10'
and instrument = '000001.SZ';
GROUP BY子句
GROUP BY子句用于将结果集按照一列或多列进行分组。语法为：
SELECT column1, function(column2)
FROM table_name
GROUP BY column1;
例如, 求得每日截面上股票平均价格水平:
%%sql
SELECT date, AVG(close) FROM cn_stock_bar1d GROUP BY date;
再如，按股票分组求每只股票下的成交量和收盘价的相关系数：
%%sql
SELECT instrument, CORR(volume, close) corr_vol_close FROM cn_stock_bar1d GROUP BY instrument;
窗口函数在SQL中提供了一种处理数据的强大方法，它可以在一行的上下文中对一组行进行计算。窗口由输入行的集合组成，它是根据窗口函数的使用情况定义的。
窗口函数的基本语法如下：
<窗口函数> (<列名>) OVER ([PARTITION BY <列名>]
[ORDER BY <列名>]
[ROWS BETWEEN <开始行> AND <结束行>])
PARTITION BY子句
PARTITION BY子句将输入行划分为若干个组或分区。窗口函数将独立于每个分区进行计算。
例如，计算每只股票五日区间收益率:
%%sql
SELECT date, instrument,
close / LAG(close, 5) OVER (PARTITION BY instrument ORDER BY date) as return_5
FROM cn_stock_bar1d
WHERE date > '2023-01-01';
剖析: 将数据按照变量instrument进行分组PARTITION BY instrument，有多少只股票就会拆分出多少组，并且对拆分出来的组按照日期进行排序ORDER BY date，进而在每组中计算五日区间收益率close / LAG(close, 5)，最后将所有组拼接成大表，和没被拆分的表格一样，区别在于多了一列return_5。
ORDER BY子句
ORDER BY子句定义了窗口内的行排序。这对于像RANK()这样的窗口函数尤其有用，它会根据排序顺序对行进行排名。
例如，按照date形成一个截面数据的换手率排行：
%%sql
SELECT date, instrument,
RANK() OVER (PARTITION BY date ORDER BY turn DESC) as rank_turn
FROM cn_stock_bar1d
WHERE date > '2023-01-01';
剖析: 首先按照日期进行分区，每张表都是特定时期下的截面数据，进一步按照股票的换手率进行降序排序，并给每张表格添加一列rank_turn，最后将所有截面数据拼接成总表。
ROWS子句
ROWS子句定义了窗口的物理限制，即窗口函数计算的行的范围。它可以定义为在当前行之前和之后的一定数量的行，或者是从分区的起始到当前行，以及从当前行到分区的结束。
ROWS子句的语法如下：
ROWS UNBOUNDED PRECEDING：从分区的第一行到当前行。ROWS N PRECEDING：从当前行的前N行到当前行。ROWS CURRENT ROW：只有当前行。ROWS N FOLLOWING：从当前行到后N行。ROWS UNBOUNDED FOLLOWING：从当前行到分区的最后一行。ROWS BETWEEN A AND B：定义一个从A到B的范围，其中A和B可以是上述任何选项。例如，计算每支股票的5日移动平均线以及对应的收盘价:
%%sql
SELECT date, instrument, close,
(AVG(close) OVER (PARTITION BY instrument ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)) ma5
FROM cn_stock_bar1d
WHERE date > '2023-01-01';
剖析: 首先按照股票代码进行分区，每个分区都保存了单只股票的时间序列数据，再对每个分区按照date排序， 取前4期的数据到当期数据的一个平均值。
注意: 聚合函数和窗口同时出现时，聚合函数和窗口共同形成一个字段AVG(close) OVER (PARTITION BY instrument ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)该字段如果是浮点数, 依旧可以进行加减乘除运算。
dai的SQL扩展功能
dai提供了许多非常方便的SQL扩展功能。这些功能可以极大地简化SQL查询的编写，提高查询的效率和灵活性。
USING
传统的联表查询使用JOIN ON的方式是比较繁琐的，dai对于JOIN列相等的情况可以使用USING的语法简化，老的方式：
%%sql
SELECT *
FROM cn_stock_bar1d a
JOIN cn_stock_valuation b
ON a.instrument = b.instrument and a.date = b.date
WHERE a.date > '2023-01-01';
新的方式：
%%sql
SELECT *
FROM cn_stock_bar1d
JOIN cn_stock_valuation USING (instrument, date)
WHERE date > '2023-01-01';
EXCLUDE/REPLACE
EXCLUDE在传统的SQL查询中，我们需要明确指定所请求的列。SELECT *可以让SQL返回所有相关的列，这在构建查询的时候非常灵活。但是相对该表全部字段数量来说，我们需要取得字段数量巨大，这时将字段逐个输入就显得浪费时间，所以我们只需要在总表的基础上排除个别字段即可。在dai中，我们只需要指定要排除的列就可以了。
例如, 在上述例子中，我不想取出收盘价数据：
%%sql
WITH t AS (
SELECT date, instrument, close,
(AVG(close) OVER (PARTITION BY instrument ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)) ma5
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
SELECT * EXCLUDE (close) FROM t;
这里有一个WITH语句，其目的是将取出的表赋值给一个中间变量t，最后取出表中除去收盘价的所有数据。
REPLACE有时，我们希望使用表中的所有列，除了几个需要做一些小调整的列。在dai中，我们可以使用REPLACE轻松地对少数列进行更改。
例如, 求出当天股票交易的总价值(粗略计算，价格乘以当天交易量)，并将其替换close字段的值：
%%sql
WITH t AS (
SELECT date, instrument, close, volume
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
SELECT * REPLACE (close * volume AS close) FROM t;
GROUP/ORDER BY ALL
GROUP BY ALL
在SQL中，我们经常需要在SELECT子句和GROUP BY子句中指定列。理论上这增加了SQL的灵活性，但实际上很少有价值。dai现在提供GROUP BY ALL ：即GROUP BY所有在SELECT子句中没有被聚合函数包裹的列。
%%sql
WITH t AS (
SELECT date, instrument, close, volume
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
SELECT date, instrument, AVG(close) FROM t GROUP BY ALL
上述GROUP BY ALL语句等价于GROUP BY date, instrument。
ORDER BY ALL
%%sql
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
ORDER BY ALL
这里的ORDER BY ALL等价于ORDER BY date, instrument, close。这在构建汇总时特别有用，因为许多其他客户端工具会自动按照这种方式对结果进行排序。dai还支持ORDER BY ALL DESC以逆序排序每一列，以及指定NULLS FIRST或NULLS LAST的选项。
在 WHERE / GROUP BY / HAVING中使用列别名
在许多SQL方言中，除了在那个语句的ORDER BY子句中，无法在其他地方使用在SELECT子句中定义的别名。在dai中，SELECT子句中的非聚合别名可以立即在WHERE和GROUP BY子句中使用，聚合别名可以在HAVING子句中使用，即使在同一查询深度也不需要子查询。
例如, 以单个标的000001.SZ为例, 找出价格上穿布林带上限（20日均线加上一倍标准差）的时点：
%%sql
WITH t AS (
/*计算布林带上轨*/
SELECT date, instrument, close,
(AVG(close) OVER (ORDER BY date ROWS 19 PRECEDING)) + (STDDEV(close) OVER (ORDER BY date ROWS 19 PRECEDING)) up_line
FROM cn_stock_bar1d
WHERE date > '2023-01-01' AND instrument = '000001.SZ'
/*筛选突破布林带上轨的时点, 卖出资产的时点*/
SELECT date, close - up_line delta FROM t WHERE delta > 0;
这里为收盘价和布林带上轨的差值取了别名delta。delta > 0代表收盘价突破了布林带上轨，进一步在where子句中过滤取出delta > 0的时点。
再如，筛选出2023年以来平均换手率低于0.3的股票：
%%sql
SELECT instrument, AVG(turn) avg_turn
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
GROUP BY instrument
HAVING avg_turn < 0.3;
这里给平均换手率指标取了别名avg_turn，HAVING子句中用别名做了判断。
字符串切片
在dai中，你可以使用方括号语法切片字符串，而不是使用笨重的SUBSTRING函数。
%%sql
SELECT 'bigquant'[:-3];
简单的列表和结构创建
dai提供了嵌套类型，以允许比纯关系模型更灵活的数据结构，同时保持高性能。为了尽可能容易使用它们，创建一个LIST（数组）或一个STRUCT（对象）使用比其他SQL系统更简单的语法。数据类型会自动推断。
%%sql
SELECT
['1', '2', '3', '4'] as num_list,
{corp: 'bigquant', vision: 'Democratize AI to empower investors'} as bigquant
方括号语法也可以用来切片一个LIST。同样，注意这里是从1开始索引的，以保持SQL的兼容性。
%%sql
SELECT
num_list[2:2] as one_number
FROM (SELECT ['1', '2', '3', '4'] as num_list);
结构点符号
使用方便的点符号来访问dai STRUCT列中特定键的值。如果键包含空格，可以使用双引号。
%%sql
SELECT
bigquant.corp,
bigquant."corp vision"
FROM (SELECT {corp: 'bigquant', 'corp vision': 'Democratize AI to empower investors'} as bigquant)
SELECT语句中如果最后存在逗号的话，一般来讲会报错, 而dai会忽略掉这种错误。
%%sql
SELECT date, instrument,
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
GROUP BY date, instrument,
自动增量重复列名
当你构建一个连接相似表的查询时，你会经常遇到重复的列名。如果查询是最后的结果，dai将简单地返回重复的列名，而不进行修改。然而，如果查询用于创建表，或者嵌在子查询或公共表表达式（CTE）中（在其他数据库中，重复的列是被禁止的！），dai将自动为重复的列分配新的名称，以便更容易地制定查询。
例如, 我想提取表cn_stock_limit_price和表cn_stock_bar1d中的日期：
%%sql
SELECT a.date, b.date FROM cn_stock_bar1d a
JOIN cn_stock_limit_price b
ON a.date = b.date AND a.instrument = b.instrument
WHERE b.date > '2023-01-01';
可重复使用的列别名
在 select 语句中使用增量计算表达式时，传统的SQL方言要求您为每个列编写完整的表达式，或者围绕计算的每一步创建一个公共表表达式（CTE）。现在，任何列别名都可以在同一 select 语句中的后续列中重复使用。不仅如此，这些别名还可以在 where 和 order by 子句中使用。
旧方式1：重复自己
%%sql
SELECT
date,
instrument,
AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 4 PRECEDING) AS avg_amount_5,
AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 19 PRECEDING) AS avg_amount_20,
AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 4 PRECEDING)
/ AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 19 PRECEDING) AS 'avg_amount_5/avg_amount_20',
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
ORDER BY instrument, date
旧方式2：使用CTEs
%%sql
WITH t AS
(SELECT
date,
instrument,
AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 4 PRECEDING) AS avg_amount_5,
AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 19 PRECEDING) AS avg_amount_20,
FROM cn_stock_bar1d WHERE date > '2023-01-01')
SELECT
date,
instrument,
avg_amount_5,
avg_amount_20,
avg_amount_5 / avg_amount_20 AS 'avg_amount_5/avg_amount_20',
FROM t
ORDER BY instrument, date
%%sql
SELECT
date,
instrument,
AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 4 PRECEDING) AS avg_amount_5,
AVG(amount) OVER (PARTITION BY instrument ORDER BY date ROWS 19 PRECEDING) AS avg_amount_20,
avg_amount_5 / avg_amount_20 AS 'avg_amount_5/avg_amount_20',
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
ORDER BY instrument, date
如果使用dai预定义的宏能够更加简化sql
%%sql
SELECT
date,
instrument,
M_AVG(amount, 5) AS avg_amount_5,
M_AVG(amount, 20) AS avg_amount_20,
avg_amount_5 / avg_amount_20 AS 'avg_amount_5/avg_amount_20',
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
ORDER BY instrument, date
如果只是把avg_amount_5和avg_amount_20作为中间变量，最后输出 avg_amount_5/avg_amount_20 因子，则可以在avg_amount_5 和 avg_amount_20 前添加下划线（_），如下：
%%sql
SELECT
date,
instrument,
M_AVG(amount, 5) AS _avg_amount_5,
M_AVG(amount, 20) AS _avg_amount_20,
_avg_amount_5 / _avg_amount_20 AS 'avg_amount_5/avg_amount_20',
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
ORDER BY instrument, date
FROM table SELECT
传统查询语句遵循先选特征再写表名, 这也是为什么SELECT在前，FROM在后。但构建查询时，你需要知道的第一件事就是你的数据来自哪里:
%%sql
FROM cn_stock_bar1d SELECT * WHERE date > '2023-01-01'
不仅如此， SELECT 语句可以完全移除，dai将假定应该选择所有的列。现在查看一张表就像这样简单：
%%sql
FROM cn_stock_bar1d WHERE date > '2023-01-01'
dai为标量函数提供了函数链功能：使用点操作符将函数链接在一起，链中的先前表达式作为随后函数的第一个参数。
%%sql
SELECT
('Democratize AI to empower investors')
.UPPER()
.string_split(' ')
.list_aggr('string_agg','.')
.concat('.') AS cool;
上方函数链代码与下方代码表达的意思相同：
%%sql
SELECT
concat(
list_aggr(
string_split(
UPPER('Democratize AI to empower investors'),
' '),
'string_agg','.'),
'.') AS oof;
与python/pandas对比
虽然SQL和python语言两者的应用领域和设计目标有所不同，但在某些地方，它们的语法还是存在相通之处。同时我们也给出在数据分析方面很常用的pandas的一些例子。
Python
Python使用相同的操作符进行算术和比较操作。例如，+、-、*、/用于算术运算，而<、>、=、!=用于比较运算。
x = 10
y = 20
print(x + y) # 输出30
print(x > y) # 输出False
SQL也是：
SELECT 10 + 20; -- 输出30
SELECT 10 > 20; -- 输出false
Pandas
Pandas也是：
df = pd.DataFrame({'x': [10], 'y': [20]})
print(df['x'] + df['y']) # 输出30
print(df['x'] > df['y']) # 输出False
Python
Python 提供了条件语句，例如 if、else。
x = 10
if x > 5:
print("x is greater than 5")
else:
print("x is not greater than 5")
SQL 提供了 CASE、WHEN、THEN、ELSE 条件语句。
%%sql
CREATE TABLE students AS (SELECT 10 as age);
SELECT
WHEN age > 5 THEN 'Age is greater than 5'
ELSE 'Age is not greater than 5'
FROM students;
Pandas
Pandas 提供了 where 条件语句。
df = pd.DataFrame({'age': [10]})
df['age'].where(df['age'] > 5, 'Age is not greater than 5', 'Age is greater than 5')
创建和使用表
Python
Python中，我们可以创建一个新的变量并给它赋值，然后在后续的代码中引用它。
students = [("Alice", 20), ("Bob", 22)]
在SQL中，你可以使用CREATE TABLE语句来定义并命名一个新的表，然后在后续的查询中引用它：
%%sql
CREATE TABLE students AS SELECT * FROM (VALUES ('Alice', 20), ('Bob', 22)) students(name, age);
Pandas
在Pandas中，DataFrame可以视作一个表，我们可以创建一个新的DataFrame并给它赋值，然后在后续的代码中引用它。
import pandas as pd
df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [20, 22]})
使用临时表
Python
Python中，我们可以创建一个新的变量并给它赋值，然后在后续的代码中引用它。
student_ages = [("Alice", 20), ("Bob", 22)]
old_students = [student for student in student_ages if student[1] > 21]
在SQL中，WITH语句（也称为公共表表达式，或CTE）允许你定义一个临时的命名结果集，然后在后续的查询中引用它：
%%sql
WITH student_ages AS (
SELECT * FROM (VALUES ('Alice', 20), ('Bob', 22)) students(name, age)
SELECT * FROM student_ages WHERE age > 21;
Pandas
在Pandas中，我们也可以创建一个新的DataFrame并给它赋值，然后在后续的代码中引用它。
import pandas as pd
df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [20, 22]})
old_students = df[df['age'] > 21]
定义和使用函数
Python
在Python中，我们使用 def 关键字定义函数：
def square(x):
return x * x
在SQL中，我们可以使用 CREATE MACRO 语句定义函数：
%%sql
CREATE MACRO square(x) AS x * x;
SELECT square(2);
Pandas
在Pandas中，我们可以定义Python函数，然后在DataFrame上应用它：
import pandas as pd
def square(x):
return x * x
df = pd.DataFrame({'x': [2]})
df['x_squared'] = df['x'].apply(square)
以上就是dai SQL、Python和Pandas语法在一些方面的相通之处。
用DAI代替Python表达式
在进行量化投资的过程中，你可能会需要对大量的交易数据进行分析，比如计算某只股票的历史收益率、波动、流动性等因子。当你使用代码编写策略时，你就需要使用 DAI 来进行数据查询和分析。本文给出了常见的可视化程序中Python表达式的DAI实现。
例如：分别用可视化编程和代码编程提取当日收盘价并求对数值。
可视化编程
对s做裁剪，裁剪区间为[lower,upper],如果lower，则设置为lower，如果upper，则设置为upper
clip(s, lower, upper)
clip(close, 10, 20)
计算s的第q个百分位数, q属于[0, 1]。则 all_quantile(s, 0)得到s的最小值，all_quantile(s, 1)对应s的最大值。注意，这里计算的是全部s数据的百分位(而不是按天或者按股票处理的)
all_quantile(s, q)
all_quantile_cont(close, 0.01)
按等宽做离散化，映射从0开始。bins可以是正整数，表示bins的数量；list，表示splits
all_wbins(s, bins)
all_wbins(close, 5)
按等频做离散化，映射从0开始。bins可以是正整数，表示bins的数量；list，表示每个bin里的数据比例
all_cbins(s, bins)
all_cbins(close, 5)
移动x值的函数
shift
m_lead(close,1)
今天的x值减去d天以前的x值
delta(x, d)
m_lead(close,1)
在过去长度为d天，x和y的相关性
correlation(x, y, d)
m_corr(close, volume, 20)
在过去长度为d天，x和y的协方差
covariance(x, y, d)
m_covar_pop(close, volume, 20)
返回y与x的滚动回归所得到的result_type序列
ols(result_type, y, x, d)
m_regr_slope(close, volume, 20)
过去 d 天 x 的和
sum(x, d)
m_sum( volume, 5)
过去 d 天 x 的乘积
product(x, d)
m_product( close/m_lag(close,1)-1, 1)
过去 d 天 x 的标准差
std(x,d)
m_stddev(close, 21)
过去 d 天 x 的均值
mean(x, d)
m_avg(close, 5)
过去 d 天 x 的均值（去除NaN值）
nanmean(x, d)
m_nanavg(close, 5)
过去 d 天 x 的方差（去除NaN值）
nanvar(x, d)
m_nanvar(close, 5)
过去 d 天 x 的标准差（去除NaN值）
nanstd(x, d)
m_nanstd(close, 5)
过去 d 天 x 的方差
var(x, d)
m_var_pop(close, 5)
过去 d 天 x 的偏度
skew(x, d)
m_skewness(close, 20)
过去 d 天 x 的峰度
kurt(x, d)
m_kurtosis(close, 20)
过去 d 天的加权平均，权重是1,…,d-1,d
decay_linear(x, d)
m_decay_linear(close, 10)
时间序列函数， d 天内的最小值
ts_min(x, d)
m_min(low, 20)
时间序列函数， d 天内的最大值
ts_max(x, d)
m_max(low, 20)
时间序列函数， d 天内的最大值发生在哪一天
ts_argmax(x, d)
20 - m_imax(high, 20)
时间序列函数， d 天内的最小值发生在哪一天
ts_argmin(x, d)
20 - m_imin(high, 20)
时间序列函数， 当天的值在d天的内的排名百分比
ts_rank(x, d)
m_rolling_rank(close, 20)
x在当天的百分比排名
pct_rank_by(date, volume)
按日期分组后的数据处理函数
scale(x, a=1)
close/sum(abs(amount)) over (PARTITION BY date )
同时按日期和key做分组，求平均
group_mean(key, x)
avg(amount) over( PARTITION BY date, key)
同时按日期和key做分组，求和
group_sum(key, x)
sum(amount) over( PARTITION BY date, key)
同时按日期和key做分组，求当前值在分组内的排名百分比
group_rank(key, x)
percent_rank() over( PARTITION BY date,key order by amount asc)
timeperiod周期的简单移动平均值
ta_sma(x, timeperiod)
m_ta_sma(close,20)
timeperiod周期的指数移动平均值
ta_ema(x, timeperiod)
m_ta_ema(close,20)
timeperiod周期的加权移动平均值
ta_wma(x, timeperiod)
m_ta_wma(close,20)
timeperiod周期的动量指标
ta_mom(x, timeperiod)
m_ta_mom(close, 10)
timeperiod周期的变动率指标
ta_roc(x, timeperiod)
m_ta_roc(close, 10)
timeperiod周期的相对强弱指标
ta_rsi(x, timeperiod)
m_ta_rsi(close, 14)
timeperiod周期的三重指数平滑平均线
ta_trix(x, timeperiod)
m_ta_trix(close, 15)
timeperiod周期的威廉指标
ta_willr(x, timeperiod)
m_ta_willr(close, low, close,14)
timeperiod周期的均幅指标
ta_atr(high, low, close, timeperiod)
m_avg(greatest(high - low, abs(high - pre_close), abs(low - pre_close)), 14)
timeperiod周期的平均趋向指数
ta_adxr(high, low, close, timeperiod)
m_ta_adxr(high, low, close,4)
timeperiod周期的顺势指标
ta_cci(high, low, close, timeperiod)
m_ta_cci(high, low, close,14)
timeperiod周期的回归斜率
ta_beta(x, y, timeperiod)
m_ta_beta(close, volume, 5)
timeperiod多空指数,空头市场
ta_bbi(close, 'long', timeperiod_1=3, timeperiod_2=6, timeperiod_3=12, timeperiod_4=24)
(m_avg(close,3) + m_avg(close, 6) + m_avg(close,12) + m_avg(close, 24)) / 4
SQL使用示例
上述DAI示例代码为sql代码，可直接运行
%%sql
select
cn_stock_bar1d
where date >= '2015-01-01'
输出结果：
DAI使用示例
"%%sql" 是 Jupyter Notebook 的一种命令，用于在 Jupyter Notebook 中运行 SQL 语句。使用DAI时，需将%%sql 去掉。因此，使用DAI时，需将%%sql 去掉。
import dai
df = dai.query("select
instrument,
date,
if(close>m_lag(close,1), 1, 0) as kline_color
cn_stock_bar1d
where date >= '2015-01-01'").df()
print(df)
输出结果：
进阶 SQL
SQL常用语句
select
SELECT语句的主要功能是查询，从数据库中检索符合条件的行。
%%sql
/*单纯的从表cn_stock_bar1d中取出所有字段*/
SELECT * FROM cn_stock_bar1d;
/*从表cn_stock_bar1d中取出2023年9月13日的收盘价*/
SELECT close FROM cn_stock_bar1d WHERE date >= '2005-01-04';
/*统计A股换手率的截面均值*/
SELECT date, AVG(turn) FROM cn_stock_bar1d GROUP BY date;
/*筛选出2023年9月13日换手率最低的三只股票*/
SELECT instrument, turn FROM cn_stock_bar1d WHERE date = '2023-09-13' ORDER BY turn LIMIT 3;
/*将日线表和估值表按照日期和股票代码合并进而观察每只股票的市值*/
SELECT * FROM cn_stock_bar1d JOIN cn_stock_valuation USING (date, instrument) WHERE date > '2021-01-01';
SELECT语句的规范顺序如下（缩进了不太常见的子句）：
SELECT select_list
FROM tables
USING SAMPLE sample_expr
WHERE condition
GROUP BY groups
HAVING group_filter
WINDOW window_expr
QUALIFY qualify_filter
ORDER BY order_expr
LIMIT n
SELECT语句可以带有WITH子句作为前缀。with 用于指定公用表表达式（CTE）。常规（非递归）公用表表达式本质上是仅限于特定查询的视图。CTE可以相互引用，并且可以嵌套。
注：由于SELECT语句非常复杂，我们将其分解成几个部分放到下一节select语法详解进行讲解。
create table
CREATE TABLE语句的主要功能是在当下目录中创建一个表。
%%sql
/*创建一个名为bigquant_factor_1的表, 其中包含两个字段i和j, 两个字段都只能存放整数*/
CREATE TABLE bigquant_factor_1(i INTEGER, j INTEGER);
/*创建一个名为bigquant_factor_2的表, 其中包含两个字段i和j, i只能存放整数, j只能存放字符串, i表的主键, 如果插入的数据存在两个同样的i值, 则会报错*/
CREATE TABLE bigquant_factor_2(id INTEGER PRIMARY KEY, j VARCHAR);
/*创建一个名为bigquant_factor_3的表, 其中包含两个字段id和j, id只能存放整数, j能存放字符串, 将这两个字段同时设为主键*/
CREATE TABLE bigquant_factor_3(id INTEGER, j VARCHAR, PRIMARY KEY(id, j));
/*创建一个名为bigquant_factor_4的表, 其中包含四个字段, i只能存放整数, decimalnr能存放小于10的浮点数, date存放的是DATE类型的数据, time 存放的是TIMESTAMP数据, 其中DATE存放的期限比后者长*/
CREATE TABLE bigquant_factor_4(i INTEGER NOT NULL, decimalnr DOUBLE CHECK(decimalnr<10), date DATE UNIQUE, time TIMESTAMP);
/*创建一个名为bigquant_factor_5的表, 其中用SELECT语句建立了包含一个样本的表, 进一步将这个表赋给bigquant_factor_5*/
CREATE TABLE bigquant_factor_5 AS SELECT 42 AS i, 84 AS j;
/*创建一个名为bigquant_factor_6的表, 读取csv中的表并将表赋给bigquant_factor_6*/
CREATE TABLE bigquant_factor_6 AS SELECT * FROM read_csv_auto ('path/file.csv');
CREATE OR REPLACE
CREATE OR REPLACE 语法允许创建新表或由新表覆盖现有表。这是删除现有表然后创建新表的简写。
%%sql
/*创建一个表bigquant_factor_1, 表中包含i和j两个变量, 如果之前存在bigquant_factor_1这张表, 则覆盖掉之前的表*/
CREATE OR REPLACE TABLE bigquant_factor_1(i INTEGER, j INTEGER);
IF NOT EXISTS
仅当表尚不存在时，IF NOT EXISTS 语法才会继续创建表。如果该表已存在，则不会执行任何操作，现有表将保留在数据库中。
%%sql
/*创建表bigquant_factor_1, 如果之前没有表bigquant_factor_1, 则创建一个包含i和j两个字段的表, 并赋给bigquant_factor_1*/
CREATE TABLE IF NOT EXISTS bigquant_factor_1(i INTEGER, j INTEGER);
create macro
CREATE MACRO语句可以在目录中创建标量宏或表宏（函数）。
宏是一种批量处理的称谓，一种语法替换的规则或模式，用于说明某一特定输入（通常是字符串）如何根据预定义的规则转换成对应的输出（通常也是字符串)。宏可能只是单个SELECT语句 ，但它具有接受参数的好处。
对于标量宏，CREATE MACRO后面跟着宏的名称, 以及一组在括号中的可选参数。接着关键字AS后跟着宏具体的文本。根据设计，标量宏可以只返回单个值。而对于表宏，语法跟标量宏是类似的，除了关键字AS被替换成了AS TABLE。表宏可以返回任意大小和格式的表。
%%sql
/*创建一种加法运算符*/
CREATE MACRO add(a, b) AS a + b;
/*创建一种新的条件语句语法, 如果满足条件a, 则输出值b, 否则是c*/
CREATE MACRO ifelse(a, b, c) AS CASE WHEN a THEN b ELSE c END;
/*定义一个函数one(), 该函数能直接调用出后面SELECT语句抽出的表格*/
CREATE MACRO one() AS (SELECT 1);
/*创建一个带参数b的函数, 函数作用于一个名为cte的表格, 从表格中取出名为a的列, 再对a列的值加上b*/
CREATE MACRO plus_one(b) AS (WITH cte AS (SELECT 1 AS a) SELECT cte.a + b FROM cte);
/*创建一个均值函数*/
CREATE FUNCTION main.myavg(x) AS SUM(x) / COUNT(x);
/*创建一个包含默认参数的函数, 以下函数中参数b默认为5*/
CREATE MACRO add_default(a, b := 5) AS a + b;
/*创建一个数组添加函数, 传入参数l和e, l是一个数组结构数据, 该函数实现了将e添加到l的尾端这一功能*/
CREATE MACRO arr_append(l, e) AS list_concat(l, list_value(e));
/*自定义一个不需要传参的函数, 调用这个函数会返回一张表*/
CREATE MACRO static_table() AS TABLE SELECT 'Hello' as column1, 'World' as column2;
/*定义一个作用于动态表的带参数的函数, 以下函数实现的功能是: 传入的参数直接输出成一张两个字段的表*/
CREATE MACRO dynamic_table(col1_value,col2_value) AS TABLE SELECT col1_value as column1, col2_value as column2;
/*定义一个作用于动态表的带参数的函数*/
CREATE OR REPLACE MACRO dynamic_table(col1_value,col2_value) AS TABLE
SELECT col1_value as column1, col2_value as column2
UNION ALL
SELECT 'Hello' as col1_value, 456 as col2_value;
宏允许您为表达式组合创建快捷方式。
%%sql
/*以下这个代码会报错, 因为b没有被定义*/
CREATE MACRO add(a) AS a + b;
/*定义加法运算函数*/
CREATE MACRO add(a,b) AS a + b;
/*字符串和数值格式不匹配, 所以这里会报错*/
SELECT add('hello', 3);
/*这里是成功的*/
SELECT add(1, 2);
宏可以具有默认参数。
%%sql
CREATE MACRO add_default(a, b := 5) AS a + b;
/*结果返回42*/
SELECT add_default(37);
/*结果会报错, 只能填入一个参数, 没有指定的情况下无法修改默认参数*/
SELECT add_default(40, 2);
/*结果返回42*/
SELECT add_default(40, b:=2);
/*结果报错, 默认参数和非默认参数之间不能被改变传参位置*/
SELECT add_default(b:=2, 40);
/*默认参数之间是可以改变传参位置的*/
CREATE MACRO triple_add(a, b := 5, c := 10) AS a + b + c;
SELECT triple_add(40, c := 1, b := 1);
当使用宏时，它们被展开（即替换为原始表达式），并且扩展表达式中的参数将替换为提供的参数。具体步骤如下：
%%sql
/*使用宏*/
SELECT add(40, 2);
/*宏展开成原始表达式*/
SELECT a + b;
/*然后将宏的参数替换原始表达式中的a和b*/
SELECT 40 + 2;
pivot
PIVOT语句的主要功能是将列中的不同值分隔到它们自己的列中。这些新列中的值是通过在与每个非重复值匹配的行子集进行聚合函数计算得到的。
PIVOT_WIDER是PIVOT的另一种写法，二者可相互替换使用。
① 简化的pivot语法
简化的PIVOT语法可以总结如下：
PIVOT [dataset]
ON [column(s)]
USING [value(s)]
GROUP BY [row(s)]
ON，USING和GROUP BY子句都是可选的，但不能全部省略。
首先, 抽取399330.SZ、000300.SH、000852.SH、000852.SH的部分指数成分股000001.SZ、000002.SZ、002061.SZ、002063.SZ、002068.SZ:
%%sql
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
SELECT * FROM bigquant_table;
使用以下PIVOT语句为每年创建一个单独的列，并且计算每天总成交量。
ON子句指定哪一列或者哪几列需要拆分为单独的列。它等效于excel中数据透视表的列参数；
USING子句确定如何聚合拆分为单独列的值。这等效于excel何种数据透视表的值参数；
如果没有USING子句，则默认使用SUM(*)进行聚合。
%%sql
/*统计每个指数每天的总成交量*/
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
/*之后每次访问bigquant_table都必须加上上述创建表格的语句*/
PIVOT bigquant_table ON date USING SUM(volume);
在上面的示例中，SUM聚合函数总是对单个值进行操作。如果我们只想更改数据的显示方向而不进行聚合计算，则可以使用FIRST聚合函数。在这个示例中，虽然我们是透视的数值，但是使用FIRST函数也可以非常方便地透视文本列。
此查询生成的结果与上述结果相同：
%%sql
/*统计每个指数每天的总成交量*/
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
PIVOT bigquant_table ON date USING FIRST(volume);
PIVOT ON，USING和GROUP BY
默认情况下，PIVOT语句保留所有没在ON或者USING子句中指定的列。
若要仅包含某些列并进一步聚合，可以在GROUP BY子句中指定列。这相当于在excel数据透视表中的行参数。
在下面的示例中，index_code列不再包含在输出中，数据将聚合到instrument级别。
%%sql
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
PIVOT bigquant_table ON instrument USING SUM(volume) GROUP BY date;
在ON子句中使用IN表达式做过滤
若要仅为ON子句中列中的特定值创建单独的列，可以使用IN表达式。例如我们想要日期为2023-09-12、2023-09-13每只股票的成交量：
%%sql
/*取出特定日期列*/
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
PIVOT bigquant_table ON date IN ('2023-09-12', '2023-09-13') USING SUM(volume) GROUP BY instrument;
可以在ON和GROUP BY子句中指定多个列，并且可以在USING子句中包含多个聚合表达式。
ON多个列和ON多个表达式
可以将多个列透视到它们自己的列中。我们会在每个ON子句列中找到不同的值，并为这些值的所有组合创建一个新列（笛卡尔积）。
在下面的示例中，instrument和index_code的所有唯一组合都会组成一个新列。某些组合在基础数据中可能不存在，那么这些列会用NULL值进行填充。
%%sql
/*以时间为列, 统计每支股票的成交量*/
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
PIVOT bigquant_table ON instrument, index_code USING SUM(volume) GROUP BY date;
若仅要透视基础数据中存在的值组合，可以在ON子句中使用表达式。
在下面的例子中，instrument和index_code通过下划线连接在一起，生成的每个连接都会作为一列。当然也可以使用其它任何非聚合的表达式进行连接：
%%sql
/*用'-'连起来*/
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
PIVOT bigquant_table ON instrument || '-' || index_code USING FIRST(volume) GROUP BY date;
USING多个表达式
可以为USING子句中的每个表达式设置一个别名。该别名会被添加到生成的列名之后，并用下划线（_）连接。这使得在 USING 子句中包含多个表达式时列名会更加清晰。
在此示例中，计算每年volume列的SUM和MAX被拆分成了单独的列：
%%sql
/*统计指数最大值和均值*/
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
PIVOT bigquant_table ON instrument USING MAX(volume) max, AVG(volume) avg GROUP BY date;
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
PIVOT bigquant_table ON date USING SUM(volume) GROUP BY instrument, index_code;
在SELECT语句中使用PIVOT
PIVOT语句可以作为CTE或者子查询被包含在SELECT语句中。这样PIVOT就可以与其他SQL逻辑一起使用，并且一个查询中也可以使用多个PIVOT语句。
在CTE中，可以用PIVOT关键字替代SELECT。
%%sql
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
WITH pivot_alias AS (
PIVOT bigquant_table ON instrument USING MAX(volume) max, AVG(volume) avg GROUP BY date
SELECT * FROM pivot_alias;
PIVOT 可以在子查询中使用，并且必须括在括号中。请注意，此操作与标准SQL透视不同，如后续示例所示。
%%sql
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
SELECT
FROM (
PIVOT bigquant_table ON instrument USING MAX(volume) max, AVG(volume) avg GROUP BY date
) pivot_alias;
使用多个Pivots
每个PIVOT都可以被当作SELECT，也可以使用JOIN或者一些其他方式进行操作。
例如，如果两个PIVOT语句共享同一个GROUP BY表达式，则可以使用GROUP BY子句中的列将它们连接在一起。
%%sql
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
FROM (
PIVOT bigquant_table ON index_code USING SUM(volume) GROUP BY date
JOIN (
PIVOT bigquant_table ON instrument USING SUM(volume) GROUP BY date
USING (date);
与简化语法不同，必须要为透视的每一列指定IN子句。 如果您对动态透视感兴趣，建议使用简化语法。
请注意，FOR子句中的表达式之间没有逗号分隔，但value和GROUP BY表达式必须以逗号分隔！
此示例使用单值表达式、单列表达式和单行表达式：
%%sql
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
FROM bigquant_table
PIVOT (
SUM(volume)
FOR date IN ('2023-09-12', '2023-09-13')
GROUP BY instrument
%%sql
CREATE TABLE IF NOT EXISTS bigquant_table AS
SELECT a.date, a.instrument, b.index_code, a.volume
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2023-09-11'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
WHERE a.instrument IN ('000001.SZ', '000002.SZ', '002061.SZ', '002063.SZ', '002068.SZ') AND index_code IN ('399330.SZ', '000300.SH', '000852.SH', '000852.SH')
ORDER BY a.date, a.instrument;
FROM bigquant_table
PIVOT (
SUM(volume),
AVG(volume)
date IN ('2023-09-12', '2023-09-13')
instrument IN ('000001.SZ', '000002.SZ')
select语法详解
SELECT子句指定查询将返回的列的列表。虽然它首先出现在子句中，但从逻辑上讲，此处的表达式却仅在最后执行。它可以包含转换输出的任意表达式，以及聚合函数和窗口函数。
%%sql
/*从后复权日行情表cn_stock_bar1d中抽取所有字段的数据*/
SELECT * FROM cn_stock_bar1d;
/*对表cn_stock_bar1d按照字段instrument去重后取出instrument字段的数据*/
SELECT DISTINCT instrument FROM cn_stock_bar1d;
/*统计数据的条数(一共多少行)*/
SELECT COUNT(*) FROM cn_stock_bar1d;
/*除了成交量, 其他数据全部取出*/
SELECT * EXCLUDE (volume) FROM cn_stock_bar1d;
/*取出所有字段的数据, 其中让股票代码所有英文后缀小写*/
SELECT * REPLACE (LOWER(instrument) AS instrument) FROM cn_stock_bar1d;
/*筛选出列名包含特定字符的字段(正则表达式匹配)*/
SELECT COLUMNS('v[a-z]*e') FROM cn_stock_bar1d;
/*拿到每个字段的最小值*/
SELECT MIN(COLUMNS(*)) FROM cn_stock_bar1d;
from & join
FROM子句指定查询操作的数据源。从逻辑上讲，FROM子句是查询开始执行的位置。FROM子句可以包含单个表、使用JOIN子句联接在一起的多个表的组合或子查询节点内的另一个SELECT查询。DAI还可以把FROM放在最前面，可以在没有SELECT语句的情况下进行查询。
%%sql
/*从表cn_stock_bar1d中取出所有字段*/
FROM cn_stock_bar1d;
/*同上*/
SELECT * FROM cn_stock_bar1d;
/*从csv文件中导入数据*/
SELECT * FROM 'test.csv';
/*子查询出来的数据依旧可以看一个表, 所以可以对子表进行查询语句*/
SELECT * FROM (SELECT * FROM cn_stock_bar1d);
/*使用join函数对两个表进行连接*/
SELECT a.date, a.instrument, a.close, b.total_market_cap
FROM cn_stock_bar1d AS a
JOIN (
SELECT date, instrument, total_market_cap
FROM cn_stock_valuation
WHERE date > '2022-01-01'
) AS b
ON a.date = b.date AND a.instrument = b.instrument;
/*从2023年9月18日当天抽取80%的样本作为股票池(最好不要用百分数)*/
SELECT * FROM (
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE date = '2023-09-18'
TABLESAMPLE 80%;
/*抽取10个样本(推荐)*/
SELECT * FROM (
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE date = '2023-09-18'
TABLESAMPLE 10 ROWS;
联接是用于水平连接两个表或关系的基本关系操作。基于它们在JOIN子句中的位置，这些关系称为连接的左侧和右侧。每个结果行都有来自两个关系的列。
联接会使用某一种规则来匹配每个关系中的行对。通常这是一个谓词，但也可以指定其他隐含规则。
OUTER JOIN
如果指明是OUTER JOIN，那么没有任何匹配项的行仍然会返回。 外联接可以是以下的一种：
LEFT (左侧表中的所有行至少出现一次)RIGHT (右侧表中的所有行至少出现一次)FULL (两个表中的所有行至少出现一次)与OUTER相对的是INNER，它表示仅返回配对的行。
返回未配对的行时，不存在的属性将设置为NULL。
CROSS JOIN
最简单的联接类型是CROSS JOIN。这种类型的联接没有条件，它只是返回所有可能的组合。
%%sql
/*返回所有匹配行*/
SELECT a.*, b.* FROM cn_stock_valuation AS a CROSS JOIN cn_stock_bar1d AS b;
大多数联接可以使用ON或者WHERE子句显式指定联接条件。
%%sql
/*查看指数和其成分股的换手率*/
SELECT a.date, a.instrument, a.turn, b.index_code
FROM cn_stock_bar1d AS a
INNER JOIN (
SELECT date, instrument index_code, member_code instrument
FROM cn_stock_index_component
WHERE date > '2022-01-01'
) AS b
ON a.date = b.date AND a.instrument = b.instrument;
如果列名相同且条件是相等， 可以使用更简单的USING语法：
%%sql
/*日行情数据和估值数据公用日期和股票代码, 使用USING语句对二表进行连接*/
SELECT date, instrument, close, total_market_cap
FROM cn_stock_valuation
JOIN cn_stock_bar1d
USING(date, instrument);
表达式不必是等式，可以使用任何谓词：
%%sql
/*选取收盘价大于开盘价的股票(动量较大)*/
SELECT date, instrument, close, open
FROM cn_stock_bar1d
WHERE close > open AND date > '2020-01-01';
POSITIONAL JOIN
使用相同大小的数据框或其他嵌入式表时，这些行可能具有基于其物理顺序的自然对应关系。在python脚本语言中，这很容易用循环来表达：
for i in range(n):
f(t1.a[i], t2.b[i])
然后在标准SQL中却很难表达出来，因为关系表不是有序的，然而导入的表（如Dataframe）或磁盘文件（如CSV或Parquet文件）往往具有自然排序。如果要使用这种顺序进行联接则可以使用POSITIONAL JOIN：
%%sql
/*没有连接条件, 原来的表是怎样的顺序, 连接的时候依旧是原来的顺序(除非两表的数据一一对应, 否则不推荐使用该连接方式)*/
SELECT a.date, a.instrument, a.close, b.total_market_cap
FROM cn_stock_bar1d AS a
POSITIONAL JOIN
cn_stock_valuation AS b
WHERE a.date = '2022-02-17'
ORDER BY a.instrument;
POSITIONAL JOIN是FULL OUTER联接。
ASOF JOIN
处理时态或类似顺序数据时的常见操作是在表中查找最近的（第一个）数据：
%%sql
SELECT a.date, a.instrument, a.close, b.open
FROM (SELECT * FROM cn_stock_bar1d WHERE date > '2023-07-01') AS a
ASOF JOIN
(SELECT * FROM cn_stock_bar1d WHERE date > '2023-07-01') AS b
ON a.date = b.date AND a.instrument = b.instrument AND a.close > b.open;
ASOF JOIN要求排序字段上至少有一个不等条件，并且左侧的表必须是更大的一侧， 任何其他条件必须是相等的（或NOT DISTINCT）。 这意味着表的左/右顺序在这种联接中是很重要的。
ASOF JOIN仅将每个左侧行与最多一个右侧行配对。可以将其指定为OUTER联接以查找未配对的行
%%sql
SELECT a.date, a.instrument, a.close, b.open
FROM (SELECT * FROM cn_stock_bar1d WHERE date > '2023-07-01') AS a
ASOF LEFT JOIN
(SELECT * FROM cn_stock_bar1d WHERE date > '2023-07-01') AS b
ON a.date = b.date AND a.instrument = b.instrument AND a.close > b.open;
where
WHERE子句指明要应用于数据源的筛选器，这样您就可以选择感兴趣的数据子集。一般来说，WHERE子句紧跟在FROM子句之后执行。
%%sql
/*查看个股000001.SZ收盘价的时间序列*/
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE instrument = '000001.SZ';
/*筛选出股票代码后缀包含SZ的所有股票的收盘价*/
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE instrument LIKE '%SZ';
/*筛选出股票代码为000001.SZ、000002.SZ的收盘价序列*/
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE instrument = '000001.SZ' OR instrument = '000002.SZ';
group by
GROUP BY子句指明应使用哪些分组列来执行SELECT子句中的任何聚合。如果指定了GROUP BY子句，则查询就是聚合查询，即使SELECT子句中不存在聚合也是如此。
当指定GROUP BY子句时，分组列中具有匹配数据的所有元组（即属于同一组的所有元组）将被合并。分组列本身的值保持不变，任何其他列都可以使用聚合函数（如COUNT、SUM、AVG等）进行合并。
通常，GROUP BY子句沿单个维度分组。使用GROUPING SETS、CUBE或ROLLUP子句可以沿多个维度进行分组。
%%sql
/*统计自2022年初以来每日市场的平均换手率*/
SELECT date, AVG(turn)
FROM cn_stock_bar1d
WHERE date > '2022-01-01'
GROUP BY date;
grouping sets
GROUPING SETS，ROLLUP和CUBE都可以在GROUP BY子句中使用，以对同一查询中的多个维度进行分组。
%%sql
/*统计一级行业日均成交量和二级行业日均成交量*/
WITH bigquant_table AS (
SELECT a.date, a.instrument, a.industry_level1_name, a.industry_level2_name, b.volume
FROM cn_stock_industry_component AS a
INNER JOIN (
SELECT date, instrument, volume
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
) AS b
ON a.date = b.date AND a.instrument = b.instrument
SELECT date, industry_level1_name, industry_level2_name, AVG(volume) FROM bigquant_table GROUP BY GROUPING SETS((date, industry_level1_name), (date, industry_level2_name)) ORDER BY date;
GROUPING SETS可以在单个查询中跨不同GROUP BY子句执行相同的聚合。
%%sql
CREATE TABLE bigquant_employee(course VARCHAR, type VARCHAR);
INSERT INTO bigquant_employee(course, type) VALUES ('CS', 'Bachelor'), ('CS', 'Bachelor'), ('CS', 'PhD'), ('Math', 'Masters'), ('CS', NULL), ('CS', NULL), ('Math', NULL);
%%sql
SELECT course, type, COUNT(*)
FROM bigquant_employee
GROUP BY GROUPING SETS ((course, type), course, type, ());
┌────────┬──────────┬──────────────┐
│ course │ type │ count_star() │
├────────┼──────────┼──────────────┤
│ CS │ Bachelor │ 2 │
│ CS │ PhD │ 1 │
│ Math │ Masters │ 1 │
│ CS │ NULL │ 2 │
│ Math │ NULL │ 1 │
│ CS │ NULL │ 5 │
│ Math │ NULL │ 2 │
│ NULL │ Bachelor │ 2 │
│ NULL │ PhD │ 1 │
│ NULL │ Masters │ 1 │
│ NULL │ NULL │ 3 │
│ NULL │ NULL │ 7 │
└────────┴──────────┴──────────────┘
在上面的查询中，我们分为四个不同的集合：course, type，course，type和()（空组）。上述查询等效于以下UNION语句：
%%sql
/*对course和type作聚类*/
SELECT course, type, COUNT(*)
FROM bigquant_employee
GROUP BY course, type
UNION ALL
/*对type作聚类*/
SELECT NULL AS course, type, COUNT(*)
FROM bigquant_employee
GROUP BY type
UNION ALL
/*对course作聚类*/
SELECT course, NULL AS type, COUNT(*)
FROM bigquant_employee
GROUP BY course
UNION ALL
/*不作聚类*/
SELECT NULL AS course, NULL AS type, COUNT(*)
FROM bigquant_employee
CUBE和ROLLUP是句法糖，可以轻松生成常用的分组集。
ROLLUP子句将产生分组集的所有“子组”，比如ROLLUP (country, city, zip)会生成分组集(country, city, zip), (country, city), (country), ()。也就是会生成n+1个分组集，其中n是ROLLUP子句中的项数。
CUBE为所有输入组合生成分组集，例如CUBE (country, city, zip) 将产生(country, city, zip), (country, city), (country, zip), (city, zip), (country), (city), (zip), ()。即生成2^n个分组集。
having
HAVING子句可以在GROUP BY子句后使用，以便在分组完成后提供筛选条件。在语法方面，HAVING子句和WHERE子句是相同的，只是WHERE在分组前执行，而HAVING子句在分组后执行。
%%sql
/*统计2023年9月20日不同指数下成分股的数量大于100个的指数*/
SELECT name, COUNT(*) number
FROM cn_stock_index_component
WHERE date = '2023-09-20'
GROUP BY name
HAVING number > 100;
order by
ORDER BY是一个输出修饰语。从执行逻辑上说，它在查询的最后才会执行。ORDER BY子句按照排序条件对行进行升序或者降序排序。此外，每个order子句都可以指定将NULL值移动到开头还是结尾。
如果未指定修饰语，默认按照ASC NULLS FIRST排序，即按升序排序，并且空值放在第一位。
文本默认使用二进制比较规则进行排序，即根据其二进制的UTF8值进行排序。
%%sql
/*获取2022年以来的收盘价数据, 并按照日期进行升序排序*/
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE date > '2022-01-01'
ORDER BY date;
/*将存在NULL的行放在最后一行*/
SELECT date, instrument, close
FROM cn_stock_bar1d
WHERE date > '2022-01-01'
ORDER BY date NULLS LAST;
/*对日期进行升序排序, 进一步对换手率升序排序*/
SELECT date, instrument, turn
FROM cn_stock_bar1d
WHERE date > '2022-01-01'
ORDER BY date, turn;
/*COLLATE会对目标字符按照一定规则进行排序*/
SELECT *
FROM cn_stock_index_component
WHERE date = '2023-09-20'
ORDER BY member_name COLLATE DE;
limit
LIMIT跟ORDER BY一样也是输出修饰语，也是在查询的最后执行。LIMIT子句限制提取的行数。OFFSET子句指明从哪个位置开始读取数据。
注意，虽然LIMIT可以在没有ORDER BY子句的情况下使用，但如果没有ORDER BY子句，结果可能是不定的。但无论如何，LIMIT可以帮助您查看部分数据快照。
%%sql
/*筛选出2023年9月20号这一天换手率最小的三只股票*/
SELECT date, instrument, turn
FROM cn_stock_bar1d
WHERE date = '2023-09-20'
ORDER BY turn LIMIT 3;
/*从第五行开始, 输出换手率最低的前三只股票*/
SELECT date, instrument, turn
FROM cn_stock_bar1d
WHERE date = '2023-09-20'
ORDER BY turn LIMIT 3 OFFSET 5;
/*按照成交量对股票降序排序*/
SELECT date, instrument, volume
FROM cn_stock_bar1d
WHERE date = '2023-09-20'
ORDER BY volume DESC;
unnest
UNNEST函数用于将列表转换为一组行，即平展操作。该函数可以用作常规标量函数，但只能在SELECT子句中使用。UNNEST是一个特殊函数，因为它改变了结果的基数。
当UNNEST与常规的标量表达式结合使用时，将对列表中的每个条目重复这些表达式。当多个列表在同一SELECT子句中取消展开时，这些列表将并排展开。如果一个列表比另一个列表长，则较短的列表将填充NULL值。
空列表和NULL列表都将平展为零元素。非类型化和类型化NULL参数都将返回零行。
%%sql
/*将列表[1, 2, 3]展开成一张表, 表共三行*/
SELECT UNNEST([1, 2, 3]);
/*每个UNNEST语句都会展开成一列, 将两个列表展开成三行两列的表, 不足三行的用NULL填充*/
/*返回结果((1, 10), (2, 11), (3, NULL))*/
SELECT UNNEST([1, 2, 3]), UNNEST([10, 11]);
/*返回结果((1, 10), (2, 10), (3, 10))*/
SELECT UNNEST([1, 2, 3]), 10;
/*建立一个每行为列表的表, 再对每行的列表进行展开*/
/*((1, 2, 3), (4, 5))-->((11), (12), (13), (14), (15))*/
SELECT UNNEST(l) + 10 FROM (VALUES ([1, 2, 3]), ([4, 5])) tbl(l);
/*创建一个空表, 没有行数*/
SELECT UNNEST([]);
/*创建一个空表, 没有行数*/
SELECT UNNEST(NULL);
/*创建一个空表, 没有行数*/
SELECT UNNEST(NULL::int[]);
WITH子句用于指定公用表表达式（CTE）。常规（非递归）公用表表达式本质上是仅限于特定查询的视图。CTE可以相互引用，并且可以嵌套。
基础的CTE例子
%%sql
/*创建一个CTE, 将其命名为bigquant_table, 并且用查询语句去调用它*/
WITH bigquant_table AS (SELECT 42 AS x)
SELECT * FROM bigquant_table;
┌────┐
│ x │
├────┤
│ 42 │
└────┘
%%sql
/*创建两个CTE, 第二个cte嵌套第一个cte*/
WITH bigquant_table_1 AS (SELECT 42 AS i),
bigquant_table_2 AS (SELECT i*100 AS x FROM bigquant_table_1)
SELECT * FROM bigquant_table_2;
┌──────┐
│ x │
├──────┤
│ 4200 │
└──────┘
递归CTE例子——树遍历
WITH RECURSIVE可用于遍历树。例如，处理以下标签的层次结构：
%%sql
/*创建一个树*/
CREATE TABLE bigquant_tag(id int, name varchar, subclassof int);
INSERT INTO bigquant_tag VALUES
(1, 'U2', 5),
(2, 'Blur', 5),
(3, 'Oasis', 5),
(4, '2Pac', 6),
(5, 'Rock', 7),
(6, 'Rap', 7),
(7, 'Music', 9),
(8, 'Movies', 9),
(9, 'Art', NULL);
以下查询返回从Oasis节点到树根的路径（Art）。
%%sql
/*完整代码*/
CREATE TABLE bigquant_tag(id int, name varchar, subclassof int);
INSERT INTO bigquant_tag VALUES
(1, 'U2', 5),
(2, 'Blur', 5),
(3, 'Oasis', 5),
(4, '2Pac', 6),
(5, 'Rock', 7),
(6, 'Rap', 7),
(7, 'Music', 9),
(8, 'Movies', 9),
(9, 'Art', NULL);
WITH RECURSIVE tag_hierarchy(id, source, path) AS (
SELECT id, name, [name] AS path
FROM bigquant_tag
WHERE subclassof IS NULL
UNION ALL
SELECT bigquant_tag.id, bigquant_tag.name, list_prepend(bigquant_tag.name, tag_hierarchy.path)
FROM bigquant_tag, tag_hierarchy
WHERE bigquant_tag.subclassof = tag_hierarchy.id
SELECT path
FROM tag_hierarchy
WHERE source = 'Oasis';
┌───────────────────────────┐
│ path │
├───────────────────────────┤
│ [Oasis, Rock, Music, Art] │
└───────────────────────────┘
递归CTE例子——图形遍历
WITH RECURSIVE子句可用于表示任意图上的图遍历。但如果是有环图，则查询必须执行环路检测以防止无限循环。实现此目的的一种方法是将遍历的路径存储在列表中，并在使用新的边扩展路径之前，检查其端点之前是否被访问过（请参阅后面的示例）。
我们来看看以下有向图:
%%sql
CREATE TABLE bigquant_edge(node1id int, node2id int);
INSERT INTO bigquant_edge VALUES (1, 3), (1, 5), (2, 4), (2, 5), (2, 10), (3, 1), (3, 5),
(3, 8), (3, 10), (5, 3), (5, 4), (5, 8), (6, 3), (6, 4), (7, 4), (8, 1), (9, 4);
注意，该图包含有向循环，例如节点1、2和5之间的有向循环。
枚举节点中的所有路径
以下查询返回从节点 1 开始的所有路径：
%%sql
/*完整代码*/
CREATE TABLE bigquant_edge(node1id int, node2id int);
INSERT INTO bigquant_edge VALUES (1, 3), (1, 5), (2, 4), (2, 5), (2, 10), (3, 1), (3, 5),
(3, 8), (3, 10), (5, 3), (5, 4), (5, 8), (6, 3), (6, 4), (7, 4), (8, 1), (9, 4);
WITH RECURSIVE paths(startNode, endNode, path) AS (
/*定义路径*/
SELECT
node1id AS startNode,
node2id AS endNode,
[node1id, node2id] AS path
FROM bigquant_edge
WHERE startNode = 1
UNION ALL
/*整合新的边*/
SELECT
paths.startNode AS startNode,
node2id AS endNode,
array_append(path, node2id) AS path
FROM paths
JOIN bigquant_edge ON paths.endNode = node1id
/*防止添加重复的节点*/
/*保证没有圆形循环发生*/
WHERE node2id != ALL(paths.path)
SELECT startNode, endNode, path
FROM paths
ORDER BY length(path), path;
┌───────────┬─────────┬───────────────┐
│ startNode │ endNode │ path │
├───────────┼─────────┼───────────────┤
│ 1 │ 3 │ [1, 3] │
│ 1 │ 5 │ [1, 5] │
│ 1 │ 5 │ [1, 3, 5] │
│ 1 │ 8 │ [1, 3, 8] │
│ 1 │ 10 │ [1, 3, 10] │
│ 1 │ 3 │ [1, 5, 3] │
│ 1 │ 4 │ [1, 5, 4] │
│ 1 │ 8 │ [1, 5, 8] │
│ 1 │ 4 │ [1, 3, 5, 4] │
│ 1 │ 8 │ [1, 3, 5, 8] │
│ 1 │ 8 │ [1, 5, 3, 8] │
│ 1 │ 10 │ [1, 5, 3, 10] │
└───────────┴─────────┴───────────────┘
请注意，此查询的结果不限于最短路径，例如，对于节点5，结果包括路径[1, 5]和[1, 3, 5]。
枚举节点中未加权的最短路径
在大多数情况下，枚举所有路径是不切实际或不可行的。然而，（未加权的）最短路径却是比较有意思的。要找到这些路径，应调整WITH RECURSIVE查询的后半部分，使其仅包含尚未访问的节点。这是通过使用子查询来实现的，该子查询检查前面的任何路径是否包含节点：
%%sql
CREATE TABLE bigquant_edge(node1id int, node2id int);
INSERT INTO bigquant_edge VALUES (1, 3), (1, 5), (2, 4), (2, 5), (2, 10), (3, 1), (3, 5),
(3, 8), (3, 10), (5, 3), (5, 4), (5, 8), (6, 3), (6, 4), (7, 4), (8, 1), (9, 4);
WITH RECURSIVE paths(startNode, endNode, path) AS (
SELECT -- define the path as the first edge of the traversal
node1id AS startNode,
node2id AS endNode,
[node1id, node2id] AS path
FROM bigquant_edge
WHERE startNode = 1
UNION ALL
SELECT -- concatenate new edge to the path
paths.startNode AS startNode,
node2id AS endNode,
array_append(path, node2id) AS path
FROM paths
JOIN bigquant_edge ON paths.endNode = node1id
-- Prevent adding a node that was visited previously by any path.
-- This ensures that (1) no cycles occur and (2) only nodes that
-- were not visited by previous (shorter) paths are added to a path.
WHERE NOT EXISTS (SELECT 1
FROM paths previous_paths
WHERE list_contains(previous_paths.path, node2id))
SELECT startNode, endNode, path
FROM paths
ORDER BY length(path), path;
┌───────────┬─────────┬────────────┐
│ startNode │ endNode │ path │
├───────────┼─────────┼────────────┤
│ 1 │ 3 │ [1, 3] │
│ 1 │ 5 │ [1, 5] │
│ 1 │ 8 │ [1, 3, 8] │
│ 1 │ 10 │ [1, 3, 10] │
│ 1 │ 4 │ [1, 5, 4] │
│ 1 │ 8 │ [1, 5, 8] │
└───────────┴─────────┴────────────┘
枚举两个节点之间的未加权最短路径
WITH RECURSIVE还可用于查找两个节点之间的所有（未加权）最短路径。为了确保递归查询在到达最终节点后立即停止，我们使用一个窗口函数来检查终端节点是否在新添加的节点中。
以下查询返回节点1（起始节点）和节点8（结束节点）之间的所有未加权最短路径：
%%sql
CREATE TABLE bigquant_edge(node1id int, node2id int);
INSERT INTO bigquant_edge VALUES (1, 3), (1, 5), (2, 4), (2, 5), (2, 10), (3, 1), (3, 5),
(3, 8), (3, 10), (5, 3), (5, 4), (5, 8), (6, 3), (6, 4), (7, 4), (8, 1), (9, 4);
WITH RECURSIVE paths(startNode, endNode, path, endReached) AS (
SELECT -- define the path as the first edge of the traversal
node1id AS startNode,
node2id AS endNode,
[node1id, node2id] AS path,
(node2id = 8) AS endReached
FROM bigquant_edge
WHERE startNode = 1
UNION ALL
SELECT -- concatenate new edge to the path
paths.startNode AS startNode,
node2id AS endNode,
array_append(path, node2id) AS path,
max(CASE WHEN node2id = 8 THEN 1 ELSE 0 END)
OVER (ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS endReached
FROM paths
JOIN bigquant_edge ON paths.endNode = node1id
WHERE NOT EXISTS (SELECT 1
FROM paths previous_paths
WHERE list_contains(previous_paths.path, node2id))
AND paths.endReached = 0
SELECT startNode, endNode, path
FROM paths
WHERE endNode = 8
ORDER BY length(path), path;
┌───────────┬─────────┬───────────┐
│ startNode │ endNode │ path │
├───────────┼─────────┼───────────┤
│ 1 │ 8 │ [1, 3, 8] │
│ 1 │ 8 │ [1, 5, 8] │
└───────────┴─────────┴───────────┘
window
WINDOW子句允许您指定可在窗口函数中使用的命名窗口。当您有多个窗口函数时，这些命名窗口就很有用，因为它们让您避免重复相同的窗口子句。
qualify
QUALIFY子句用于筛选窗口函数。过滤结果跟应用于GROUP BY子句的HAVING子句类似。
QUALIFY子句避免了使用子查询或WITH子句来执行此筛选的需要（与HAVING避免子查询类似）。下面有一个使用WITH子句而不是QUALIFY的示例。
请注意，这是基于窗口函数进行筛选，而不一定是基于WINDOW子句。WINDOW子句可用于简化多个WINDOW函数表达式的创建，但不是必需的。
指定QUALIFY子句的位置是在SELECT语句中的WINDOW子句之后（WINDOW也可以不指定），并且在ORDER BY之前。
以下每个示例生成的输出都是相同的：
%%sql
/*编写威廉R(W&R指标), 定位指标值大于80的个股(超卖状态)*/
SELECT date, instrument,
(MAX(high) OVER (PARTITION BY instrument ORDER BY date ROWS 5 PRECEDING) - close) /
(MAX(high) OVER (PARTITION BY instrument ORDER BY date ROWS 5 PRECEDING) - MIN(low) OVER (PARTITION BY instrument ORDER BY date ROWS 5 PRECEDING)) * 100 WR
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
QUALIFY WR > 80;
/*将分窗函数放入WINDOW语句中*/
SELECT date, instrument,
100 * (MAX(high) OVER my_win - close) / (MAX(high) OVER my_win - MIN(low) OVER my_win) WR
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
WINDOW
my_win AS (PARTITION BY instrument ORDER BY date ROWS 5 PRECEDING)
QUALIFY WR > 80;
/*将窗口函数放入WITH语句中, 此时再查询WITH语句中的表格时不需要用QUALIFY*/
WITH t1 AS (
SELECT date, instrument,
(MAX(high) OVER my_win -close) / (MAX(high) OVER my_win - MIN(low) OVER my_win) * 100 WR
FROM cn_stock_bar1d
WHERE date > '2023-01-01'
WINDOW
my_win AS (PARTITION BY instrument ORDER BY date ROWS 5 PRECEDING)
SELECT * FROM t1
WHERE WR > 80;
/*生成两行两列的表, 并给列赋予列名*/
SELECT * FROM (VALUES ('bigquant', 1), ('othes', 2)) Cities(Name, Id);
/*用value子句生成的表创建一个新表*/
CREATE TABLE Cities AS SELECT * FROM (VALUES ('bigquant', 1), ('othes', 2)) Cities(Name, Id);
filter
FILTER子句可以选择性地跟在在SELECT语句中的聚合函数后面。这样可以筛选聚合函数处理的数据行，其方式与WHERE子句筛选行的方式相同。当聚合函数在窗口中使用时，目前FILTER还不能使用。
FILTER在很多情况下都非常有用，比如使用不同过滤条件计算多个聚合时，以及创建数据透视图时。与下面讨论的更传统的CASE WHEN方法相比，FILTER为透视数据提供了更简洁的语法。
某些聚合函数不会筛选出NULL值，因此使用FILTER子句可以返回有效的结果，而CASE WHEN方法有时则不能。比如FIRST和LAST函数在非聚合的透视操作中使用时。FILTER 还改进了使用LIST和ARRAY_AGG函数时的NULL值处理，因为CASE WHEN方法会在列表结果中包含NULL值，而FILTER子句则会删除它们。
%%sql
/*统计2023年1月5日, 2023年1月6日这两天有多少只股票在交易, 并统计1月6日当天一字涨停或跌停的股票数量*/
SELECT COUNT(*) FILTER (WHERE date = '2023-01-05') trading_stock_1,
COUNT(*) FILTER (WHERE date = '2023-01-06') trading_stock_2,
COUNT(*) FILTER (WHERE high = low AND date = '2023-01-06') count_1
FROM cn_stock_bar1d;