BigQuant使用文档

数据平台/DAI

由jliang创建,最终由rydeng 被浏览 3052 用户

什么是DAI

DAI (Data for AI) 是BigQuant研发的高性能分布式数据平台

  • 使用简单:通过统一接口访问BigQuant各类数据
  • 数据丰富:提供PB级金融数据、另类投资数据和因子数据 (数据字典),并支持用户自定义数据
  • 技术先进:采用现代化的分布式架构,支持大规模数据的低延迟读写和高性能计算

{w:100}{w:100}{w:100}

\

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()
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-012023-05-01
      • AND 并且
      • instrument = '000001.SZ' 股票代码为 000001.SZ
        • .SZ 是深交所的股票代码后缀,.SH 为上交所,平台所有数据定义保持一致
  • .df() 将返回数据转为 pandas DataFrame

计算因子

从中国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()
df

DAI核心概念

SQL

DAI提供多种数据格式的访问和计算支持,包括SQL、DataFrame、Arrow等。

其中SQL:

  • 全兼容和支持SQL标准
  • 性能优化,支持CPU和GPU
  • 专为量化因子和指标计算开发和优化的函数
  • 双计算引擎
    • BigDB:DAI的高性能向量化计算引擎,主要用于大规模数据计算场景
    • BigStream:DAI的低延迟实时数据流计算引擎,有高度的并行性和快速的数据处理能力。可用于高频因子实时计算交易、高频交易、市场实时监控等
  • QuantChat 辅助生成查询和计算SQL,通过自然语言就可以实现因子构建

\

DataSource

DataSource是DAI组织数据的基本单元,类似于数据库中的table。

DataSource使用列式存储、支持数据分区和索引。基于高性能的数据表示,可以实现TB/s的数据从磁盘到内存高吞吐。

\

View

View是一种特殊的数据表,类似于数据库中的view。

View可以用于联合多个DataSource创建新的数据表,比如BigQuant平台的因子表,其实是一个view,底层由成百上千个不同的DataSource联合组成。通过专门的性能优化,View已能实现高性能的JOIN和裁剪,也能支持无限层次嵌套。

\

数据字典和文档

数据字典

BigQuant平台数据字典入口:BigQuant数据字典和文档

{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}

\

数据表规范

数据表名

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:数字货币

    \

  • 数据类别:命名的第三部分表示数据类别,多个单词见用下划线(_)连接,示例如下 (更多见术语部分):

    1. bar1d:日线行情数据
    2. bar1m:分钟行情数据
    3. income_sheet:利润表
    4. financial_pit_ttm:滚动十二期的PIT财务数据
    5. dividend:分红信息
    6. basic_info:基础信息
    7. index_bar1d:指数日线行情数据
    8. index_weights:指数权重
    9. industry_components:行业成分

    \

通用字段

以下为数据表通用字段,它们在大部分表中都有一致的定义:

  • date
    • 日期和时间
    • 类型:datetime
  • instrument
    • 股票代码
    • 类型:字符串 (categorical)

\

术语

术语 说明 示例
bar 行情数据 cn_stock_bar1d
changes 变更记录 cn_stock_index_changes
cn 中国A股市场 cn_stock_bar1d
financial 财务相关 cn_stock_financial_pit_ttm
index 指数相关 cn_stock_index_bar1d
industry 行业分类相关 cn_stock_industry_members
components 成分 cn_stock_industry_members
weights 权重 cn_stock_index_weights

\

数据研究和探索

数据标注

可视化数据模块

\

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表中选择dateinstrument以及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 FIRSTNULLS LAST的选项。

WHERE / GROUP BY / HAVING中使用列别名

在许多SQL方言中,除了在那个语句的ORDER BY子句中,无法在其他地方使用在SELECT子句中定义的别名。在dai中,SELECT子句中的非聚合别名可以立即在WHEREGROUP 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;

这里为收盘价和布林带上轨的差值取了别名deltadelta > 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_turnHAVING子句中用别名做了判断。

字符串切片

在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 语句中的后续列中重复使用。不仅如此,这些别名还可以在 whereorder 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_5avg_amount_20作为中间变量,最后输出 avg_amount_5/avg_amount_20 因子,则可以在avg_amount_5avg_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

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 提供了条件语句,例如 ifelse

x = 10
if x > 5:
    print("x is greater than 5")
else:
    print("x is not greater than 5")
  • SQL

SQL 提供了 CASEWHENTHENELSE 条件语句。

%%sql
CREATE TABLE students AS (SELECT 10 as age);
SELECT 
    CASE 
        WHEN age > 5 THEN 'Age is greater than 5'
        ELSE 'Age is not greater than 5'
    END
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

在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

在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

在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实现。

例如:分别用可视化编程和代码编程提取当日收盘价并求对数值。

可视化编程
代码编程

\

替换表达

描述 Python表达式 DAI示例
数据过滤函数 where %%sql \nselect \ninstrument,\ndate,\nif(close>m_lag(close,1), 1, 0) as kline_color \nfrom \ncn_stock_bar1d \nwhere date >= '2015-01-01'
正弦函数 sin %%sql \nSELECT \n    instrument, \n    date, \n    SIN(change_ratio) as sin_change_ratio\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
余弦函数 cos %%sql \nSELECT \n    instrument, \n    date, \n    COS(change_ratio) as cos_change_ratio\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
正切函数 tan %%sql \nSELECT \n    instrument, \n    date, \n    TAN(change_ratio) as tan_change_ratio\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
反正弦函数 arcsin %%sql \nSELECT \n    instrument, \n    date, \n    asin(x) as asin_change_ratio\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
反余弦函数 arccos %%sql SELECT    instrument,    date,    acos(0.1) as sin_change_ratioFROM    cn_stock_bar1dWHERE    date >= '2015-01-01'
反三角函数 arctan %%sql SELECT instrument, date, atan(2) as sin_change_ratioFROM cn_stock_bar1dWHERE date >= '2015-01-01'
反三角函数,=arctan(x/y) arctan2(x, y) %%sql \nSELECT \n    instrument, \n    date, \n    ATAN2(close, open) as atan2_close_open\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
双曲正弦 sinh(x) %%sql SELECT instrument, date, (EXP(close) - EXP(-1*close)) / 2 as sinh_change_ratioFROM cn_stock_bar1dWHERE date >= '2015-01-01'
双曲余弦 cosh(x) %%sql SELECT instrument, date, (EXP(close) + EXP(-1*close)) / 2 as sinh_change_ratioFROM cn_stock_bar1dWHERE date >= '2015-01-01'
双曲正切 tanh(x) %%sql SELECT instrument, date, (EXP(close) - EXP(-1*close)) / (EXP(close) + EXP(-1*close)) as tanh_change_ratio FROM cn_stock_bar1dWHERE date >= '2015-01-01'
反双曲正弦 arcsinh(x) %%sql SELECT     instrument,     date,     LOG(close + SQRT(close * close + 1)) as asinh_change_ratio FROM     cn_stock_bar1d WHERE     date >= '2015-01-01'
反双曲余弦 arccosh(x) %%sql SELECT date, instrument,     LOG(close + SQRT(close*close - 1)) FROM     cn_stock_bar1d WHERE     date >= '2015-01-01'
反双曲正切 arctanh(x) %%sql \nSELECT \n    0.5 * LOG((1 + close) / (1 - close)) \nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
自然对数 log(x) %%sql \nSELECT \n    instrument, \n    date,\n    LOG(close) as log_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
10为底的对数 log10(x) %%sql \nSELECT \n    instrument, \n    date,\n    LOG10(close) as log10_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
log(1+x) log1p(x) %%sql \nSELECT \n    instrument, \n    date,\n    LOG(1 + close) as log1p_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
指数 exp(x) %%sql \nSELECT \n    instrument, \n    date,\n    EXP(close) as exp_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
exp(x)-1 expm1(x) %%sql \nSELECT \n    instrument, \n    date,\n    EXP(close) - 1 as expm1_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
平方根 sqrt(x) %%sql \nSELECT \n    instrument, \n    date,\n    SQRT(close) as sqrt_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
绝对值 abs(x) %%sql \nSELECT \n    instrument, \n    date,\n    ABS(close) as abs_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
向上取整 ceil(x) %%sql \nSELECT \n    instrument, \n    date,\n    CEIL(close) as ceil_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
向下取整 floor(x) %%sql \nSELECT \n    instrument, \n    date,\n    FLOOR(close) as floor_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
取 x 的符号,如果 x > 0, 为1; x == 0,为0; 否则为-1 sign(x) %%sql SELECT instrument, date, sign(close/open - 1) FROM cn_stock_bar1d WHERE date >= '2015-01-01'
等价于 sign(x) * (abs(x)**a) signedpower(x, a) %%sql \nSELECT \n    instrument, \n    date,\n    SIGN(close) * POWER(ABS(close), 2) as signedpower_close\nFROM \n    cn_stock_bar1d\nWHERE\n    date >= '2015-01-01'
最小值 min %%sql SELECT     instrument,     min(close) as min_close FROM     cn_stock_bar1d WHERE     date > '2015-01-01' GROUP BY     instrument
求最大值 max %%sql SELECT     instrument,    max(close) as max_close FROM     cn_stock_bar1d WHERE     date > '2015-01-01' GROUP BY     instrument
判断是否为NaN isnan(number) %%sql SELECT instrument, date, close, if(isnan(close), 0, close) as adjusted_close FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;
对s做裁剪,裁剪区间为[lower,upper],如果lower,则设置为lower,如果upper,则设置为upper clip(s, lower, upper) %%sql SELECT date, instrument, close, clip(close, 10, 20) AS clipped_close FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;
计算s的第q个百分位数, q属于[0, 1]。则 all_quantile(s, 0)得到s的最小值,all_quantile(s, 1)对应s的最大值。注意,这里计算的是全部s数据的百分位(而不是按天或者按股票处理的) all_quantile(s, q) %%sql select date,instrument, close, all_quantile_cont(close, 0.01) from cn_stock_bar1d where date >= '2023-01-01' ORDER BY date, instrument;
按等宽做离散化,映射从0开始。bins可以是正整数,表示bins的数量;list,表示splits all_wbins(s, bins) %%sql SELECT instrument, close, all_wbins(close, 5) as f FROM cn_stock_bar1d WHERE date >= '2022-01-01'
按等频做离散化,映射从0开始。bins可以是正整数,表示bins的数量;list,表示每个bin里的数据比例 all_cbins(s, bins) %%sql SELECT date, instrument, close, all_wbins(close, 5) AS close_bin FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;
移动x值的函数 shift %%sql SELECT     instrument, date,     m_lead(close,1), FROM     cn_stock_bar1d WHERE     date > '2022-01-01'
今天的x值减去d天以前的x值 delta(x, d) %%sql SELECT     instrument, date,     m_lead(close,1), FROM     cn_stock_bar1d WHERE     date > '2022-01-01'
在过去长度为d天,x和y的相关性 correlation(x, y, d) %%sql SELECT     instrument,     date,     m_CORR(close, volume, 20)  AS vol_price_corr FROM     cn_stock_bar1d where date >= '2015-01-01'  ORDER BY     date, instrument;
在过去长度为d天,x和y的协方差 covariance(x, y, d) %%sql SELECT instrument, date, m_covar_pop(close, volume, 20) FROM cn_stock_bar1d where date >= '2015-01-01' and instrument == '000002.SZ' ORDER BY date, instrument;
返回y与x的滚动回归所得到的result_type序列 ols(result_type, y, x, d) %%sql SELECT instrument, date, m_regr_slope(close, volume, 20) FROM cn_stock_bar1d where date >= '2015-01-01' and instrument == '000002.SZ' ORDER BY date, instrument;
过去 d 天 x 的和 sum(x, d) %%sql SELECT instrument, date, m_sum( volume, 5) FROM cn_stock_bar1d where date >= '2015-01-01' ORDER BY date, instrument;
过去 d 天 x 的乘积 product(x, d) %%sql SELECT instrument, date, m_product( close/m_lag(close,1)-1, 1) FROM cn_stock_bar1d where date >= '2015-01-01' ORDER BY date, instrument;
过去 d 天 x 的标准差 std(x,d) %%sql SELECT instrument, date, amount / m_stddev(close, 21) AS factor FROM cn_stock_bar1d where date >= '2015-01-01' ORDER BY date, instrument;
过去 d 天 x 的均值 mean(x, d) %%sql SELECT instrument, date, m_avg(close, 5) AS factor FROM cn_stock_bar1d where date >= '2015-01-01' ORDER BY date, instrument;
过去 d 天 x 的均值(去除NaN值) nanmean(x, d) %%sql\nSELECT instrument, date,\n==m_nanavg(close, 5)== as nanmean_close\nFROM cn_stock_bar1d\nWHERE \n    date >= '20151001'
过去 d 天 x 的方差(去除NaN值) nanvar(x, d) %%sql\nSELECT instrument, date,\n==m_nanvar(close, 5)== as nanvar_close\nFROM cn_stock_bar1d\nWHERE \n    date >= '20151001'
过去 d 天 x 的标准差(去除NaN值) nanstd(x, d) %%sql\nSELECT instrument, date,\n==m_nanstd(close, 5)== as nanstd_close\nFROM cn_stock_bar1d\nWHERE \n    date >= '20151001'
过去 d 天 x 的方差 var(x, d) %%sql SELECT instrument, date, m_var_pop(close, 5) AS factor FROM cn_stock_bar1d where date >= '2015-01-01' ORDER BY date, instrument;
过去 d 天 x 的偏度 skew(x, d) %%sql SELECT date, instrument, m_skewness(close, 20) AS skewness_20 FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;
过去 d 天 x 的峰度 kurt(x, d) %%sql SELECT date, instrument, m_kurtosis(close, 20) AS skewness_20 FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;
过去 d 天的加权平均,权重是1,…,d-1,d decay_linear(x, d) %%sql SELECT     date,     instrument,     m_decay_linear(close, 10) AS decay_linear_10 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument
时间序列函数, d 天内的最小值 ts_min(x, d) %%sql SELECT     date,     instrument,     m_min(low, 20) AS min_price_in_20_days FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
时间序列函数, d 天内的最大值 ts_max(x, d) %%sql SELECT     date,     instrument,     m_max(low, 20) AS min_price_in_20_days FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
时间序列函数, d 天内的最大值发生在哪一天 ts_argmax(x, d) %%sql SELECT     date,     instrument,     20 - m_imax(high, 20) as high_price_position FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
时间序列函数, d 天内的最小值发生在哪一天 ts_argmin(x, d) %%sql SELECT     date,     instrument,     20 - m_imin(high, 20) as high_price_position FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
时间序列函数, 当天的值在d天的内的排名百分比 ts_rank(x, d) %%sql SELECT     date,     instrument,     m_rolling_rank(close, 20) as rank_in_20_days   FROM cn_stock_bar1d where date >= '2015-01-01' ORDER BY     date, instrument;
x在当天的百分比排名 rank %%sql SELECT     date,     instrument,     volume,     pct_rank_by(date, volume) as volume_percent_rank FROM     cn_stock_bar1d   where date >= '2015-01-01'  ORDER BY     date, instrument;
按日期分组后的数据处理函数 scale(x, a=1) %%sql select date, amount,close/sum(abs(amount)) over (PARTITION BY date ) as scale from cn_stock_bar1d where date >= '2020-01-01' order by date, instrument
同时按日期和key做分组,求平均 group_mean(key, x) %%sql select date, amount, if(close>open,1,0) as key, avg(amount) over( PARTITION BY date, key) from cn_stock_bar1d where date >= '2020-01-01' order by date, instrument;
同时按日期和key做分组,求和 group_sum(key, x) %%sql select date, amount, if(close>open,1,0) as key, sum(amount) over( PARTITION BY date, key) from cn_stock_bar1d where date >= '2020-01-01' order by date, instrument;
同时按日期和key做分组,求当前值在分组内的排名百分比 group_rank(key, x) %%sql select date, amount, if(close>open,1,0) as key, percent_rank() over( PARTITION BY date,key order by amount asc) from cn_stock_bar1d where date >= '2020-01-01' order by date, instrument;
timeperiod周期的简单移动平均值 ta_sma(x, timeperiod) %%sql SELECT     date,     instrument,     m_ta_sma(close,20) AS SMA_20 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date,     instrument;
timeperiod周期的指数移动平均值 ta_ema(x, timeperiod) %%sql SELECT     date,     instrument,     m_ta_ema(close,20) AS SMA_20 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date,     instrument;
timeperiod周期的加权移动平均值 ta_wma(x, timeperiod) %%sql SELECT     date,     instrument,     m_ta_wma(close,20) AS SMA_20 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date,     instrument;
timeperiod周期的动量指标 ta_mom(x, timeperiod) %%sql SELECT     date,     instrument,     close,     m_ta_mom(close, 10) AS mom10 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
timeperiod周期的变动率指标 ta_roc(x, timeperiod) %%sql SELECT     date,     instrument,     close,     m_ta_roc(close, 10) AS roc10 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
timeperiod周期的相对强弱指标 ta_rsi(x, timeperiod) %%sql SELECT     date,     instrument,     close,     m_ta_rsi(close, 14) AS rsi14 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
timeperiod周期的三重指数平滑平均线 ta_trix(x, timeperiod) %%sql SELECT     date,     instrument,     close,     m_ta_trix(close, 15) AS trix15 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
timeperiod周期的威廉指标 ta_willr(x, timeperiod) %%sql SELECT     date,     instrument,     close,     m_ta_willr(close, low, close,14) AS willr14 FROM     cn_stock_bar1d WHERE     date >= '2015-01-01' ORDER BY     date, instrument;
timeperiod周期的均幅指标 ta_atr(high, low, close, timeperiod) %%sql SELECT     instrument,     date,     m_avg(GREATEST(high - low, ABS(high - pre_close), ABS(low - pre_close)), 14) AS atr FROM     cn_stock_bar1d WHERE     date >= '2015-01-01'   ORDER BY     date,     instrument;
timeperiod周期的平均趋向指数 ta_adxr(high, low, close, timeperiod) %%sql SELECT date, instrument, m_ta_adxr(high, low, close,4) as adxr FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;
timeperiod周期的顺势指标 ta_cci(high, low, close, timeperiod) %%sql SELECT date, instrument, m_ta_cci(high, low, close,14) as cci FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;
timeperiod周期的回归斜率 ta_beta(x, y, timeperiod) %%sql SELECT     instrument,     date,     m_ta_beta(close, volume, 5)  as beta FROM     cn_stock_bar1d WHERE     date >= '2021-01-01' AND date <= '2021-12-31'
timeperiod多空指数,空头市场 ta_bbi(close, 'long', timeperiod_1=3, timeperiod_2=6, timeperiod_3=12, timeperiod_4=24) %%sql SELECT date, instrument,     (m_avg(close,3) + m_avg(close, 6) + m_avg(close,12) + m_avg(close, 24)) / 4 as bbi FROM cn_stock_bar1d WHERE date >= '2015-01-01' ORDER BY date, instrument;

使用示例

SQL使用示例

上述DAI示例代码为sql代码,可直接运行

%%sql 
select 
*
from 
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 
from 
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_WIDERPIVOT的另一种写法,二者可相互替换使用。


① 简化的pivot语法

简化的PIVOT语法可以总结如下:

PIVOT [dataset]
ON [column(s)]
USING [value(s)]
GROUP BY [row(s)]

ONUSINGGROUP 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;
date instrument index_code volume
2023-09-12 000001.SZ 399330.SZ 49548506
2023-09-12 000001.SZ 000300.SH 49548506
2023-09-12 000002.SZ 399330.SZ 41997403
2023-09-12 000002.SZ 000300.SH 41997403
…… …… …… ……
2023-09-15 000002.SZ 399330.SZ 38951414
2023-09-15 002061.SZ 000852.SH 15470940
2023-09-15 002063.SZ 000852.SH 12598762
2023-09-15 002068.SZ 000852.SH 5826088

\

  • PIVOT ON和USING

使用以下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);
instrument index_code 2023-09-12 …… 2023-09-15
000001.SZ 399330.SZ 49548506 …… 89047613
000001.SZ 000300.SH 49548506 …… 89047613
…… …… …… …… ……
002068.SZ 000852.SH 11382458 …… 5826088

在上面的示例中,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;
instrument 2023-09-12 …… 2023-09-15
000001.SZ 99097012 …… 1158
…… …… …… ……
002068.SZ 11382458 …… 5826088

\

  • 在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;
instrument 2023-09-12 2023-09-13
000001.SZ 49548506 49021797
000002.SZ 41997403 38931911
002061.SZ 9552777 19570800
002063.SZ 9497603 19020440
002068.SZ 11382458 11276348

\

  • 每个子句多个表达式

可以在ONGROUP BY子句中指定多个列,并且可以在USING子句中包含多个聚合表达式。

ON多个列和ON多个表达式

可以将多个列透视到它们自己的列中。我们会在每个ON子句列中找到不同的值,并为这些值的所有组合创建一个新列(笛卡尔积)。

在下面的示例中,instrumentindex_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;
date 000001.SZ_000300.SH 000001.SZ_000852.SH …… 002068.SZ_399330.SZ
2023-09-12 49548506 NULL …… NULL
2023-09-13 49021797 NULL …… NULL
2023-09-14 55165017 NULL …… NULL
2023-09-15 89047613 NULL …… NULL

若仅要透视基础数据中存在的值组合,可以在ON子句中使用表达式。

在下面的例子中,instrumentindex_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;
date 000001.SZ-000300.SH …… 002068.SZ-000852.SH
2023-09-12 49548506 …… 11382458
2023-09-13 49021797 …… 11276348
2023-09-14 55165017 …… 7103837
2023-09-15 89047613 …… 5826088

USING多个表达式

可以为USING子句中的每个表达式设置一个别名。该别名会被添加到生成的列名之后,并用下划线(_)连接。这使得在 USING 子句中包含多个表达式时列名会更加清晰。

在此示例中,计算每年volume列的SUMMAX被拆分成了单独的列:

%%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;
date 000001.SZ_max 000001.SZ_avg …… 002063.SZ_avg 002068.SZ_max 002068.SZ_avg
2023-09-12 49548506 49548506.0 …… 9497603.0 11382458 11382458.0
…… …… …… …… …… …… ……
2023-09-18 50132501 50132501.0 …… 11398882.0 10294500 10294500.0

GROUP BY多个列

请注意,必须使用列名而不是列位置(1、2 等),并且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;

PIVOT bigquant_table ON date USING SUM(volume) GROUP BY instrument, index_code;
instrument index_code …… 2023-09-15 2023-09-18
000001.SZ 399330.SZ …… 89047613 50132501
…… …… …… …… ……
002068.SZ 000852.SH …… 5826088 50132501

\

  • 在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);
instrument 2023-09-12 2023-09-13 …… 000300.SH 000852.SH 399330.SZ
000001.SZ 99097012 98043594 …… 292915434 NULL 292915434
…… …… …… …… …… …… ……
002068.SZ 11382458 11276348 …… NULL 45883231 NULL


② SQL标准pivot语法

SQL的标准PIVOT语法如下:

FROM [dataset] 
PIVOT (
    [values(s)]
    FOR 
        [column_1] IN ([in_list])
        [column_2] IN ([in_list])
        ...
    GROUP BY [rows(s)]
)

与简化语法不同,必须要为透视的每一列指定IN子句。 如果您对动态透视感兴趣,建议使用简化语法。

请注意,FOR子句中的表达式之间没有逗号分隔,但valueGROUP 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
);
instrument 2023-09-12 2023-09-13
000001.SZ 99097012 98043594
…… …… ……
002068.SZ 11382458 11276348

此示例在FOR子句中使用多个值表达式和多个列。

%%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)
    FOR 
        date IN ('2023-09-12', '2023-09-13')
        instrument IN ('000001.SZ', '000002.SZ')
);
index_code 2023-09-12_000001.… …… 2023-09-13_000001.… 2023-09-13_000002.… 2023-09-13_000002.…
399330.SZ 49548506 …… 49021797.0 38931911 38931911.0
000300.SH 49548506 …… 49021797.0 38931911 38931911.0
000852.SH NULL …… NULL NULL NULL

\

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 JOINFULL 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子句时,分组列中具有匹配数据的所有元组(即属于同一组的所有元组)将被合并。分组列本身的值保持不变,任何其他列都可以使用聚合函数(如COUNTSUMAVG等)进行合并。

通常,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 SETSROLLUPCUBE都可以在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, typecoursetype()(空组)。上述查询等效于以下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

CUBEROLLUP是句法糖,可以轻松生成常用的分组集。

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

LIMITORDER 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

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可用于遍历树。例如,处理以下标签的层次结构:

{w:100}

%%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子句可用于表示任意图上的图遍历。但如果是有环图,则查询必须执行环路检测以防止无限循环。实现此目的的一种方法是将遍历的路径存储在列表中,并在使用新的边扩展路径之前,检查其端点之前是否被访问过(请参阅后面的示例)。

我们来看看以下有向图:


{w:100}

%%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;
date instrument WR
2023-01-19 000025.SZ 93.08755760368672
2023-02-15 000025.SZ 83.14606741573056
…… …… ……
2023-07-17 002435.SZ 88.88888888888879

\

values

VALUES子句用于指定固定的行数。VALUES子句可用作独立语句或FROM子句的一部分。

  • 例子
%%sql
/*产生一张两行两列的表, 并直接返回他们*/
VALUES ('bigquant', 1), ('othes', 2);

/*生成两行两列的表, 并给列赋予列名*/
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方法有时则不能。比如FIRSTLAST函数在非聚合的透视操作中使用时。FILTER 还改进了使用LISTARRAY_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;
trading_stock_1 trading_stock_2 count_1
5067 5068 5
%%sql
/*统计2023年1月5日, 2023年1月6日这两天平均换手率, 并统计1月6日当天剔除一字涨停或跌停的股票后平均换手率*/
SELECT AVG(turn) FILTER (WHERE date = '2023-01-05') trading_turn_1,
AVG(turn) FILTER (WHERE date = '2023-01-06') trading_turn_2, 
AVG(turn) FILTER (WHERE high != low AND date = '2023-01-06') count_turn_1
FROM cn_stock_bar1d;
trading_turn_1 trading_turn_2 count_turn_1
0.025115489605260284 0.02556454240905246 0.025607455009196516

FILTER子句还可用于将数据从行透视到列中。这是一种静态透视,因为在SQL中列必须在运行时之前定义。

%%sql
CREATE TEMP TABLE bigquant_data as 
    SELECT 
        i,
        CASE WHEN i <= rows * 0.25 THEN 2022 
             WHEN i <= rows * 0.5 THEN 2023 
             WHEN i <= rows * 0.75 THEN 2024 
             WHEN i <= rows * 0.875 THEN 2025
             ELSE NULL 
             END as year 
    FROM (
        SELECT 
            i, 
            count(*) over () as rows 
        FROM generate_series(1,10000) tbl(i)
    ) tbl;

/*统计每年的数据量*/
SELECT
    count(i) FILTER (WHERE year = 2022) as "2022",
    count(i) FILTER (WHERE year = 2023) as "2023",
    count(i) FILTER (WHERE year = 2024) as "2024",
    count(i) FILTER (WHERE year = 2025) as "2025",
    count(i) FILTER (WHERE year IS NULL) as "NULLs"
FROM bigquant_data;

/*语法不同但结果一致*/
SELECT
    count(CASE WHEN year = 2022 THEN i END) as "2022",
    count(CASE WHEN year = 2023 THEN i END) as "2023",
    count(CASE WHEN year = 2024 THEN i END) as "2024",
    count(CASE WHEN year = 2025 THEN i END) as "2025",
    count(CASE WHEN year IS NULL THEN i END) as "NULLs"
FROM bigquant_data;
2022 2023 2024 2025 NULLs
2500 2500 2500 1250 1250

当使用不忽略NULL值的聚合函数时,CASE WHEN方法将无法按预期工作。上面已经谈到,FIRST函数就属于此类别,因此FILTER在这种情况下是首选。

%%sql
CREATE TEMP TABLE bigquant_data as 
    SELECT 
        i,
        CASE WHEN i <= rows * 0.25 THEN 2022 
             WHEN i <= rows * 0.5 THEN 2023 
             WHEN i <= rows * 0.75 THEN 2024 
             WHEN i <= rows * 0.875 THEN 2025
             ELSE NULL 
             END as year 
    FROM (
        SELECT 
            i, 
            count(*) over () as rows 
        FROM generate_series(1,10000) tbl(i)
    ) tbl;

SELECT
    first(i) FILTER (WHERE year = 2022) as "2022",
    first(i) FILTER (WHERE year = 2023) as "2023",
    first(i) FILTER (WHERE year = 2024) as "2024",
    first(i) FILTER (WHERE year = 2025) as "2025",
    first(i) FILTER (WHERE year IS NULL) as "NULLs"
FROM bigquant_data;
2022 2023 2024 2025 NULLs
1 2501 5001 7501 8751
%%sql
/*每当CASE WHEN子句的第一个计算返回NULL时, 将生成NULL值*/
CREATE TEMP TABLE bigquant_data as 
    SELECT 
        i,
        CASE WHEN i <= rows * 0.25 THEN 2022 
             WHEN i <= rows * 0.5 THEN 2023 
             WHEN i <= rows * 0.75 THEN 2024 
             WHEN i <= rows * 0.875 THEN 2025
             ELSE NULL 
             END as year 
    FROM (
        SELECT 
            i, 
            count(*) over () as rows 
        FROM generate_series(1,10000) tbl(i)
    ) tbl;

SELECT
    first(CASE WHEN year = 2022 THEN i END) as "2022",
    first(CASE WHEN year = 2023 THEN i END) as "2023",
    first(CASE WHEN year = 2024 THEN i END) as "2024",
    first(CASE WHEN year = 2025 THEN i END) as "2025",
    first(CASE WHEN year IS NULL THEN i END) as "NULLs"
FROM bigquant_data;
2022 2023 2024 2025 NULLs
1 NULL NULL NULL NULL

\

集合操作

集合操作指的是UNION [ALL]INTERSECTEXCEPT子句。

传统的集合操作按列位置统一查询,并要求要组合的查询具有相同数量的输入列。如果列的类型不同,则可以添加强制转换。结果将使用第一个查询中的列名。

DAI也支持UNION BY NAME,它按名称而不是按位置连接列。UNION BY NAME不要求输入具有相同数量的列。如果缺少列,将添加NULL值。

  • 例子
%%sql
/*拼接两个区间的数据, 分别是从0到9的所有整数和0到4的所有整数*/
SELECT * FROM range(10) bigquant_t1 UNION ALL SELECT * FROM range(5) bigquant_t2;

/*在上一个结果的基础上剔除缺失值*/
SELECT * FROM range(10) bigquant_t1 UNION SELECT * FROM range(5) bigquant_t2;

/*在之前的两个数据集基础上取交集*/
SELECT * FROM range(10) bigquant_t1 INTERSECT SELECT * FROM range(5) bigquant_t2;

/*在之前的两个数据集上用大的区间集合与小的区间集合取差集(从大集合剔除小集合中的元素)*/
SELECT * FROM range(10) bigquant_t1  EXCEPT SELECT * FROM range(5) bigquant_t2;

/*连接两个子表, 缺少列值用NULL填充*/
SELECT 24 AS id UNION ALL BY NAME SELECT 'bigquant' as Company;
  • 示例表
%%sql
CREATE TABLE bigquant_capitals_table(city VARCHAR, country VARCHAR);
INSERT INTO bigquant_capitals_table VALUES ('Amsterdam', 'NL'), ('Berlin', 'Germany');

CREATE TABLE bigquant_weather_table(city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO bigquant_weather_table VALUES ('Amsterdam', 10, '2022-10-14'), ('Seattle', 8, '2022-10-12');
  • UNION (ALL)

UNION子句可用于合并来自多个查询的行。查询必须具有相同数量的列和相同的列类型。

UNION子句默认执行重复消除,结果中仅包含唯一行。

UNION ALL则返回两个查询的所有行,而不进行重复消除。

%%sql
CREATE TABLE bigquant_capitals_table(city VARCHAR, country VARCHAR);
INSERT INTO bigquant_capitals_table VALUES ('Amsterdam', 'NL'), ('Berlin', 'Germany');
CREATE TABLE bigquant_weather_table(city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO bigquant_weather_table VALUES ('Amsterdam', 10, '2022-10-14'), ('Seattle', 8, '2022-10-12');

SELECT city FROM bigquant_capitals_table UNION SELECT city FROM bigquant_weather_table;
-- 返回结果: Amsterdam, Berlin, Seattle

SELECT city FROM bigquant_capitals_table UNION ALL SELECT city FROM bigquant_weather_table;
-- 返回结果: Amsterdam, Amsterdam, Berlin, Seattle
  • INTERSECT

INTERSECT子句可用于选择在两个查询的结果中都出现的所有行。INTERSECT会执行重复消除,仅返回唯一行。

%%sql
CREATE TABLE bigquant_capitals_table(city VARCHAR, country VARCHAR);
INSERT INTO bigquant_capitals_table VALUES ('Amsterdam', 'NL'), ('Berlin', 'Germany');
CREATE TABLE bigquant_weather_table(city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO bigquant_weather_table VALUES ('Amsterdam', 10, '2022-10-14'), ('Seattle', 8, '2022-10-12');

SELECT city FROM bigquant_capitals_table INTERSECT SELECT city FROM bigquant_weather_table;
-- 返回结果: Amsterdam
  • EXCEPT

EXCEPT子句可用于选择仅在左侧查询中出现的所有行。EXCEPT会执行重复消除,仅返回唯一行。

%%sql
CREATE TABLE bigquant_capitals_table(city VARCHAR, country VARCHAR);
INSERT INTO bigquant_capitals_table VALUES ('Amsterdam', 'NL'), ('Berlin', 'Germany');
CREATE TABLE bigquant_weather_table(city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO bigquant_weather_table VALUES ('Amsterdam', 10, '2022-10-14'), ('Seattle', 8, '2022-10-12');

SELECT city FROM bigquant_capitals_table EXCEPT SELECT city FROM bigquant_weather_table;
-- 返回结果: Berlin
  • UNION (ALL) BY NAME

UNION (ALL) BY NAME子句可用于按名称(而不是按位置)合并不同表中的行。UNION BY NAME不要求两个查询具有相同的列数。仅存在于在其中一个查询中的任何列在另一个查询中都将被填充为NULL值。

%%sql
CREATE TABLE bigquant_capitals_table(city VARCHAR, country VARCHAR);
INSERT INTO bigquant_capitals_table VALUES ('Amsterdam', 'NL'), ('Berlin', 'Germany');
CREATE TABLE bigquant_weather_table(city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO bigquant_weather_table VALUES ('Amsterdam', 10, '2022-10-14'), ('Seattle', 8, '2022-10-12');

SELECT * FROM capitals UNION BY NAME SELECT * FROM weather;
┌───────────┬─────────┬─────────┬────────────┐
│   city    │ country │ degrees │    date    │
│  varchar  │ varchar │  int32  │    date    │
├───────────┼─────────┼─────────┼────────────┤
│ Amsterdam │ NULL    │      10 │ 2022-10-14 │
│ Seattle   │ NULL    │       8 │ 2022-10-12 │
│ Amsterdam │ NL      │    NULL │ NULL       │
│ Berlin    │ Germany │    NULL │ NULL       │
└───────────┴─────────┴─────────┴────────────┘

UNION BY NAME执行重复消除,而UNION ALL BY NAME不执行。

\

数据类型

  • 通用数据类型

下表显示了所有内置的通用数据类型。别名列中列出的替代项也可用于引用这些类型,但请注意,别名不是标准SQL的一部分,因此可能不被其他数据库引擎接受。

名称 别名 描述
BIGINT INT8, LONG 有符号八字节整数
BIT BITSTRING 1 和 0 的字符串
BOOLEAN BOOL, LOGICAL 逻辑布尔值 (true/false)
BLOB BYTEA, BINARY, VARBINARY 可变长度二进制数据
DATE 日历日期(年、月、日)
DOUBLE FLOAT8, NUMERIC, DECIMAL 双精度浮点数(8 字节)
DECIMAL(s, p) 具有给定小数位数和精度的固定精度浮点数
HUGEINT 有符号 16 字节整数
INTEGER INT4, INT, SIGNED 有符号四字节整数
INTERVAL 日期/时间增量
REAL FLOAT4, FLOAT 单精度浮点数(4 字节)
SMALLINT INT2, SHORT 有符号双字节整数
TIME 一天中的时间(无时区)
TIMESTAMP DATETIME 时间和日期的组合
TIMESTAMP WITH TIME ZONE TIMESTAMPTZ 使用当前时区的时间和日期的组合
TINYINT INT1 有符号单字节整数
UBIGINT 无符号八字节整数
UINTEGER 无符号四字节整数
USMALLINT 无符号双字节整数
UTINYINT 无符号单字节整数
UUID UUID 数据类型
VARCHAR CHAR, BPCHAR, TEXT, STRING 可变长度字符串
  • 嵌套/复合类型

DAI支持三种嵌套数据类型:LISTSTRUCTMAP。每种类型都有不同的用法,并具有不同的结构。

名称 描述 在列中的使用规则 从值构建 DDL/CREATE中的定义方式
LIST 相同类型的数据值的有序序列 每个 LIST 中的每一行必须具有相同的数据类型,但可以包含任意数量的元素 [1, 2, 3] INT[ ]
STRUCT 包含多个命名值的字典,其中每个键都是一个字符串,但每个键的值可以是不同的类型 每行必须具有相同的键 {'i': 42, 'j': 'a'} STRUCT(i INT, j VARCHAR)
MAP 包含多个命名值的字典,每个键具有相同的类型,每个值具有相同的类型。键和值可以是任何类型,也可以是彼此不同的类型 行可以具有不同的键。 map([1,2],['a','b']) MAP(INT, VARCHAR)
  • 嵌套

LISTSTRUCTMAP可以任意嵌套到任意深度,只要遵守类型规则即可。

%%sql
/*struct嵌套list*/
SELECT {'birds': ['duck', 'goose', 'heron'], 'aliens': NULL, 'amphibians': ['frog', 'toad']};

/*struct嵌套list, 列表中嵌套map*/
SELECT {'bigquant_table': [map([1, 5], [42.1, 45]), map([1, 5], [42.1, 45])]};

\

NULL

NULL值是用于表示 SQL 中缺失数据的特殊值。任何类型的列都可以包含NULL值。

%%sql
/*往表中插入NULL*/
CREATE TABLE bigquant_table(i INTEGER);
INSERT INTO bigquant_table VALUES (NULL);

SELECT * FROM bigquant_table;
-- 返回一张只包含NULL的表

NULL值在查询的许多部分以及许多函数中具有特殊的语义:

任何与NULL值的比较都会返回NULL,包括NULL=NULL

您可以使用IS NOT DISTINCT FROM执行相等比较,其中NULL值彼此相等。IS (NOT) NULL用于检查值是否为NULL

%%sql
SELECT NULL=NULL;
-- 返回NULL

SELECT NULL IS NOT DISTINCT FROM NULL;
-- 返回true
SELECT NULL IS NULL;

-- 返回true
  • NULL和函数

如果函数以NULL作为输入参数,其通常会返回NULL

%%sql
SELECT COS(NULL);
-- NULL

COALESCE是一个例外。COALESCE接受任意数量的参数,并为每一行返回第一个不是NULL的参数。如果所有参数都是NULL,则COALESCE也返回NULL

%%sql
SELECT COALESCE(NULL, NULL, 1);
-- 1

SELECT COALESCE(10, 20);
-- 10

SELECT COALESCE(NULL, NULL);
-- NULL
  • NULL和连接词

NULL值在AND/OR连词中具有特殊的语义。有关三元逻辑真值表,请参阅Boolean文档

  • NULL和聚合函数

NULL在大多数聚合函数中是被忽略的值。

不忽略NULL值的聚合函数包括:FIRSTLASTLISTARRAY_AGG。若要从这些聚合函数中排除NULL值,可以使用FILTER子句

%%sql
CREATE TABLE bigquant_table(i INTEGER);
INSERT INTO bigquant_table VALUES (1), (10), (NULL);

SELECT MIN(i) FROM bigquant_table;
-- 1

SELECT MAX(i) FROM bigquant_table;
-- 10

\

boolean

名称 别名 描述
BOOLEAN bool 逻辑布尔值 (true/false)

BOOLEAN类型表示真值陈述(“true”或“false”)。在 SQL 中,布尔字段还可以具有第三个状态“unknown”,该状态由 SQL NULL值表示。

%%sql
/*bool类型的三个状态*/
SELECT TRUE, FALSE, NULL::BOOLEAN;
-- 依次返回true, false, NULL

可以使用字面上的TRUEFALSE显式地创建布尔值。但是,它们通常是在比较或连词中创建的。比如i > 10的结果为布尔值。布尔值可用于 SQL 语句的WHEREHAVING子句,以从结果中筛选出元组。在这种情况下,谓词计算结果为TRUE的元组将通过筛选器,谓词计算结果为FALSENULL的元组将被筛选掉。请参考以下示例:

%%sql
/*创建一个包含5, 15, NULL三个值的表格*/
CREATE TABLE bigquant_table(i INTEGER);
INSERT INTO bigquant_table VALUES (5), (15), (NULL);

/*查询所有值大于10的行, NULL会被过滤掉*/
SELECT * FROM bigquant_table WHERE i > 10;
  • 连词

AND/OR连词可用于组合布尔值。

下面是x AND y的真值表。

X X AND TRUE X AND FALSE X AND NULL
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL

下面是x OR y的真值表。

X X OR TRUE X OR FALSE X OR NULL
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL
  • 表达式

请参阅逻辑表达式比较表达式

\

enum

名称 描述
ENUM 字典编码,表示列的所有可能的字符串值
  • 枚举

ENUM类型表示具有列的所有可能的唯一值的字典数据结构。比如,存储星期几的列可以是包含所有可能日期的枚举。枚举对于具有高基数的字符串列尤其有用。这是因为该列仅在ENUM字典中存储对字符串的数字引用,从而大大节省了存储空间并提高了查询性能。

  • 枚举定义

ENUM类型是从一组硬编码的值或从返回varchar单列的SELECT语句中创建的。SELECT语句中的值集将被删除重复数据,但如果枚举是从硬编码集创建的,则可能没有任何重复项。

-- Create enum using hardcoded values
CREATE TYPE ${enum_name} AS ENUM ([${value_1},${value_2},...])

-- Create enum using a select statement that returns a single column of varchars
CREATE TYPE ${enum_name} AS ENUM (${SELECT expression})

例如:

%%sql
/*创建一个名为mood的枚举*/
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

/*由于之前存在了名为mood的枚举, 所以这里会报错*/
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy', 'anxious');

/*枚举对象不能存在NULL值*/
CREATE TYPE breed AS ENUM ('maltese', NULL);

/*这里会报错, 因为枚举对象不能存在重复值*/
CREATE TYPE breed AS ENUM ('maltese', 'maltese');

/*从查询语句中创建一个枚举对象*/
CREATE TABLE bigquant_inputs AS 
    SELECT 'duck'  AS my_varchar UNION ALL
    SELECT 'duck'  AS my_varchar UNION ALL
    SELECT 'goose' AS my_varchar;

-- 这里会自动删除重复值
CREATE TYPE birds AS ENUM (SELECT my_varchar FROM bigquant_inputs);

-- 使用enum_range函数显示鸟类枚举中的可用值
SELECT enum_range(NULL::birds) AS my_enum_range;
my_enum_range
[duck, goose]
  • 枚举用法

创建枚举后,可以在任何使用标准内置类型的地方使用它。例如,我们可以创建一个表,其中包含引用枚举的列。

%%sql
/*创建一个枚举*/
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

-- 创建一个表格, 其中name为字符, current_mood引用了mood枚举
CREATE TABLE bigquant_person (
    name text,
    current_mood mood
);

-- 从这个表格中插入数据
INSERT INTO bigquant_person VALUES ('Pedro','happy'), ('Mark', NULL), ('Pagliacci', 'sad'), ('Mr. Mackey', 'ok');

/*由于枚举中不存在quackity-quack, 所以抛出错误异常*/
INSERT INTO bigquant_person VALUES ('Hannes','quackity-quack');

-- 'sad' 在这里是mood枚举类型, 不是字符型
SELECT * FROM bigquant_person WHERE current_mood = 'sad';
-- 返回结果Pagliacci

/*从外部导入数据并创建枚举*/
CREATE TYPE mood AS ENUM (SELECT mood FROM 'path/to/file.csv');

-- 之后便可以创建一个包含枚举的表, 并从外部导入数据
CREATE TABLE bigquant_person_2 (name text, current_mood mood);
COPY bigquant_person_2 FROM 'path/to/file.csv' (AUTO_DETECT TRUE);
  • 枚举与字符串

枚举会在必要时自动强制转换为VARCHAR类型。此特征允许在任何VARCHAR函数中使用ENUM列。此外,它还允许在不同ENUM列之间或者ENUMVARCHAR列之间进行比较。

例如:

%%sql
/*创建一个枚举*/
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

-- 创建一个表格, 其中name为字符, current_mood引用了mood枚举
CREATE TABLE bigquant_person (
    name text,
    current_mood mood
);

-- 从这个表格中插入数据
INSERT INTO bigquant_person VALUES ('Pedro','happy'), ('Mark', NULL), ('Pagliacci', 'sad'), ('Mr. Mackey', 'ok');

-- 使用regexp_matches函数将current_mood字段的值转化为字符型数据
SELECT regexp_matches(current_mood, '.*a.*') FROM person;
-- 返回结果: TRUE、NULL、 TRUE、FALSE

/*接下来建立一个新的枚举*/
CREATE TYPE new_mood AS ENUM ('happy', 'anxious');

CREATE TABLE bigquant_person_2(
    name text,
    current_mood mood,
    future_mood new_mood,
    past_mood VARCHAR
);

INSERT INTO bigquant_person_2 VALUES ('Pedro','happy', 'happy', 'ok'), ('Mark', NULL, 'anxious', 'sad'), ('Pagliacci', 'sad', 'anxious', 'sad'), ('Mr. Mackey', 'ok', 'anxious', 'anxious');

-- 即便是两个不一样的枚举, 在做判断的时候依旧转化成字符型作判断
SELECT * FROM bigquant_person_2 WHERE current_mood = future_mood;


SELECT * FROM bigquant_person_2 WHERE current_mood = past_mood;
  • 枚举删除

枚举类型存储在目录中,并将目录依赖项添加到使用它们的每个表中。可以使用以下命令从目录中删除枚举:

DROP TYPE ${enum_name}

注意,在删除枚举之前必须删除任何依赖项,或者使用CASCADE参数。

例如:

%%sql
/*创建一个枚举*/
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

-- 创建一个表格, 其中name为字符, current_mood引用了mood枚举
CREATE TABLE bigquant_person (
    name text,
    current_mood mood
);
DROP TYPE mood   -- 这样是会报错的, 因为在创建表格bigquant_person时用到了这个枚举

DROP TABLE bigquant_person;  -- 所以在删除枚举之前, 需要删除之前包含枚举的表格

DROP TYPE mood; -- 最终删除成功

\

numeric

  • 整数类型

TINYINTSMALLINTINTEGERBIGINTHUGEINT类型存储各种范围的整数,即没有小数部分的数字。尝试存储超过存储范围的值将发生错误。类型UTINYINTUSMALLINTUINTEGERUBIGINT存储完整的无符号数字。尝试将存储负数或超过存储范围的值将发生错误。

名称 别名 最小值 最大值
TINYINT INT1 -128 127
SMALLINT INT2, SHORT -32768 32767
INTEGER INT4, INT, SIGNED -2147483648 2147483647
BIGINT INT8, LONG -9223372036854775808 9223372036854775807
HUGEINT -170141183460469231731687303715884105727* 170141183460469231731687303715884105727
UTINYINT - 0 255
USMALLINT - 0 65535
UINTEGER - 0 4294967295
UBIGINT - 0 18446744073709551615

整数类型是常见的类型,因为它在范围、存储大小和性能之间提供了最佳平衡。SMALLINT类型通常仅在存储空间非常宝贵时才使用。BIGINTHUGEINT类型为在整数类型的范围不足时设计。

比如-170141183460469231731687303715884105728 (-1 « 127)不能用内部结构表示。

  • 定点小数

数据类型DECIMAL(WIDTH,SCALE)表示精确的定点十进制值。创建DECIMAL类型的值时,可以指定WIDTHSCALE来定义字段中可以保存的十进制值的大小。WIDTH字段确定可以保存多少位数,scale确定小数点后的位数。例如,类型DECIMAL(3,2)可以拟合值1.23,但不能拟合值12.3或值1.234。默认的WIDTHSCALEDECIMAL(18,3)

在内部,小数根据其指定的宽度表示为整数。

宽度 内部 大小 (字节)
1-4 INT16 2
5-9 INT32 4
10-18 INT64 8
19-38 INT128 16

使用太大的DECIMAL可能会影响性能。特别是宽度大于 19 的十进制值非常慢,因为涉及INT128类型的算术比涉及INT32或者INT64类型的操作要耗时得多。建议坚持宽度为18或以下,除非有充分的理由说明这是不够的。

  • 浮点类型

数据类型REALDOUBLE的精度是不精确的,也被称作可变精度数值类型。这些类型通常是二进制浮点运算(分别为单精度和双精度)的IEEE 754标准的实现。

名称 别名 描述
REAL FLOAT4, FLOAT 单精度浮点数(4 字节)
DOUBLE FLOAT8 双精度浮点数(8 字节)

不精确意味着某些值无法精确转换为内部格式,而是存储为近似值,因此存储和检索值可能会显示细微差异。管理这些错误以及它们如何通过计算传播是数学和计算机科学的一整个分支主题,这里不做讨论。除了以下几点外:

  • 如果需要精确的存储和计算(例如货币金额),请改用数值类型。
  • 如果要对这些类型进行复杂的计算,尤其是在边界情况下(无穷大、下溢)时,则应仔细评估实现。
  • 比较两个浮点值的相等性也并不总是符合预期。

在大多数平台上,REAL类型的范围为 1E-37 到 1E+37,精度至少为 6 位。DOUBLE类型通常具有大约从 1E-307 到 1E+308 的范围,精度至少为 15 位。值过大或过小都会引发错误。如果输入数字的精度太高,则可能会进行舍入。太接近零且无法表示为与零不同的数字将导致下溢错误。

除了普通数值之外,浮点类型还具有几个特殊值:

Infinity -Infinity NaN

这些分别表示 IEEE 754 的几个特殊值“无穷大”、“负无穷大”和“非数字”。(在不遵循 IEEE 754 浮点运算的计算机上,这些值也会有些问题)在 SQL 命令中将这些值作为常量写入时,必须在它们两边加上引号,例如:UPDATE table SET x = '-Infinity'。在输入时,这些字符串以不区分大小写的方式识别。

\

text

名称 别名 描述
VARCHAR CHAR, BPCHAR, TEXT, STRING 可变长度字符串
VARCHAR(n) 最大长度为 n 的可变长度字符串

通过VARCHAR(n)这种形式提供一个数字和类型可以进行初始化,其中n是一个正整数。注意,指定长度不是必需的,对系统也没有什么影响。指定此长度不会提高性能或减少数据库中字符串的存储空间。出于与需要为字符串指定长度的其他系统的兼容性原因,支持了此种用法。

如果出于数据完整性原因希望限制VARCHAR列中的字符数,则应使用CHECK约束,如下:

%%sql
CREATE TABLE strings(
	val VARCHAR CHECK(LENGTH(val) <= 10) -- val字段只能存储长度不大于10的字符型数据
);

VARCHAR字段允许存储 unicode 字符。在内部,数据会被编码为 UTF-8。

\

date

名称 别名 描述
DATE 日历日期(年、月、日)

DATE类型是年、月和日的组合。可以使用DATE关键字创建日期,其中数据必须根据 ISO 8601 格式(YYYY-MM-DD)进行格式化。

%%sql
SELECT DATE '1992-09-20';
  • 特殊值

有三个特殊的日期值可用于输入:

输入字符串 描述
epoch 1970-01-01 (Unix系统零日)
infinity 晚于所有其他日期
-infinity 早于所有其他日期

infinity-infinity值是系统内的特定表达,显示不会有什么变化; 但epoch只是一个符号速记,在读取时将转换为日期值。

%%sql
SELECT '-infinity'::DATE, 'epoch'::DATE, 'infinity'::DATE;
Negative Epoch Positive
-infinity 1970-01-01 infinity

\

timestamp

时间戳表示绝对时间中的点,通常称为时刻。DAI将时刻表示为自1970-01-01 00:00:00+00以来的微秒数(μs)。

名称 别名 描述
TIMESTAMP datetime 时间和日期的组合(忽略时区)
TIMESTAMP WITH TIME ZONE TIMESTAMPTZ 时间和日期的组合(使用时区)

时间戳表示DATE(年、月、日)和TIME(小时、分钟、秒、毫秒)的组合。可以使用TIMESTAMP关键字创建时间戳,其中数据必须根据 ISO 8601 格式(YYYY-MM-DD hh:mm:ss[.zzzzzz][+-TT[:tt]])进行格式化。

%%sql
SELECT TIMESTAMP '1992-09-20 11:30:00';
  • 特殊值

有三个特殊的日期值可用于输入:

输入字符串 有效的类型 描述
epoch timestamp, timestamptz 1970-01-01 00:00:00+00 (Unix 系统时间零)
infinity timestamp, timestamptz 晚于所有其他时间戳
-infinity timestamp, timestamptz 早于所有其他时间戳

DATE类型一样,infinity-infinity是系统中的特定写法,显示不会发生改变; 但epoch是一个简写,在读取时会将其转换为具体的时间戳。

%%sql
SELECT '-infinity'::TIMESTAMP, 'epoch'::TIMESTAMP, 'infinity'::TIMESTAMP;
Negative Epoch Positive
-infinity 1970-01-01 00:00:00 infinity

\

interval

INTERVAL类型表示一段时间。这个周期可以用多种单位来衡量,比如年、天或秒。

名称 描述
INTERVAL 时间段

INTERVAL可以直接生成,也可以是函数的结果(例如,计算两个时间戳之间的差)。INTERVAL可用于修改DATETIMESTAMP或者TIMESTAMP WITH TIME ZONE数据类型。

%%sql
/*一年*/
SELECT INTERVAL 1 YEAR;
-- 你还可以这样写
SELECT INTERVAL 1 YEARS;

/*28天*/
SELECT INTERVAL '28' DAYS;
-- 你还可以这样写
SELECT INTERVAL '28 DAYS';

/*30秒*/
SELECT INTERVAL 30 SECONDS;

/*timestamp数据还可以由日期相减得到*/
SELECT '2022-01-02 01:00:00'::TIMESTAMP - '2022-01-01'::TIMESTAMP;

/*不存在小数的日期间隔, sql会自动将小数点后面抹去*/
SELECT INTERVAL '1.5' YEARS; --WARNING! This returns 1 year!

\

blob

名称 别名 描述
blob bytea 可变长度二进制数据

BLOB(Binary Large OBject)类型表示存储在数据库系统中的任意二进制对象。BLOB类型可以包含任何类型的二进制数据,没有任何限制。实际字节所代表的内容对数据库系统是不透明的。

%%sql
-- 创建具有单个字节的二进制值
SELECT '\xAA'::BLOB;

-- 创建包含三个字节的二进制值
SELECT '\xAA\xAB\xAC'::BLOB;

-- 创建具有两个字节的二进制值
SELECT 'AB'::BLOB;

BLOB通常用于存储数据库不提供显式支持的非文本对象,例如图像。虽然BLOB可以容纳最大为 4GB 的对象,但通常不建议在数据库系统中存储非常大的对象。在许多情况下,最好将大文件存储在文件系统上,并将文件的路径存储在数据库系统中的VARCHAR字段。

\

list

  • 列表数据类型

LIST列可以具有不同长度的值,但它们必须具有相同的基础类型。LIST通常用于存放数字数组,但也能包含任何统一的数据类型,包括其他LISTSTRUCT

  • 创建列表
%%sql
-- 只包含整数的列表
SELECT [1, 2, 3];

-- 包含NULL的列表
SELECT ['duck', 'goose', NULL, 'heron'];

-- 包含列表和NULL的列表
SELECT [['duck', 'goose', 'heron'], NULL, ['frog', 'toad'], []];

-- list_value函数是产生列表的函数
SELECT list_value(1, 2, 3);

-- 创建一个表格, 表格中包含两列, 第一列只能存储整数型列表, 第二列只能存放字符型列表. 
CREATE TABLE list_table (int_list INT[], varchar_list VARCHAR[]);
  • 从列表中获取元素

从列表中检索一个或多个值可以使用方括号和切片表示法,也可以通过列表函数(如list_extract)完成。

%%sql
-- 获取到列表中的第二个字符. 
SELECT (['a','b','c'])[2];

-- 获取到列表最后一个字符
SELECT (['a','b','c'])[-1];

-- 你还可以对索引作简单运算
SELECT (['a','b','c'])[1 + 1];

-- 你还可以利用list_extract函数传入索引参数获取列表中的元素
SELECT list_extract(['a','b','c'], 2);

-- 你还可以切片索引
SELECT (['a','b','c'])[1:2];

-- 你还可以单值切片索引
SELECT (['a','b','c'])[:2];

-- 切片索引最后两个元素
SELECT (['a','b','c'])[-2:];

-- sql中还支持切片索引函数
SELECT list_slice(['a','b','c'],2,3);

\

struct

  • Struct数据类型

STRUCT列包含被称为“条目”的其他列的有序列表。这些条目使用字符串按名称引用。本文档将这些条目名称称为key。STRUCT列中的每一行必须具有相同的key。每行的每个STRUCT值的key都必须具有相同类型。

STRUCT通常用于将多个列嵌套到单个列中,嵌套列可以是任何类型,包括其他STRUCTLIST

STRUCT类似于Postgres的ROW类型。主要区别在于STRUCT列的每一行中都需要相同的key。这样能够通过充分利用向量执行引擎来提高性能,并且还强制实施类型一致性以提高正确性。DAI包含一个row函数作为生成STRUCT的特殊方法,但没有ROW这种数据类型。

有关各个嵌套数据类型的比较,请参考数据类型

STRUCT可以使用STRUCT_PACK(name := expr, ...)函数或等效的数组表示法{'name': expr, ...}创建。表达式可以是常量或任意表达式。

\

  • 创建STRUCT
%%sql
-- Struct数据类型中包含整数

SELECT {'x': 1, 'y': 2, 'z': 3};
-- Struct数据类型中既有字符又有NULL
SELECT {'a': 'b', 'c': 'd', 'e': NULL, 'f': 'g'};

-- Struct数据类型中既有字符又有整数和浮点数
SELECT {'key1': 'string', 'key2': 1, 'key3': 12.345};

-- 使用struct_pack函数创建Struct数据类型
SELECT struct_pack(key1 := 'value1',key2 := 42);

-- Struct数据类型中既有Struct数据又有NULL
SELECT {'a':
            {'b': 'c', 'd': 'e', 'f': NULL, 'g': 'h'},
        'i':
            NULL,
        'j':
            {'k':'l', 'm': 'n', 'o': 'p', 'q':'r'}
        };
        
-- 使用row函数创建struct类型数据, 以下语句返回{'x': 1, 'v2': 2, 'y': a}
SELECT row(x, x + 1, y) FROM (SELECT 1 as x, 'a' as y);

-- 以下方式省略了row函数, 返回的依旧是{'x': 1, 'v2': 2, 'y': a}
SELECT (x, x + 1, y) FROM (SELECT 1 as x, 'a' as y);

\

  • 向STRUCT添加字段/值
%%sql
-- 使用struct_insert函数往现有的struct数据中插入新数据
SELECT struct_insert({'a': 1, 'b': 2, 'c': 3}, d := 4);

\

  • 从STRUCT中检索

从STRUCT中检索值可以使用点表示法、括号表示法或通过struct函数(如struct_extract)完成。

%%sql
-- 你可以使用: 表格名.键 的方式去访问struct中的值
SELECT a.x FROM (SELECT {'x':1, 'y':2, 'z':3} as a);

-- 你还可以这样访问值
SELECT a."x space" FROM (SELECT {'x space':1, 'y':2, 'z':3} as a);

-- 你还可以用方括号将键括起来访问它的值
SELECT a['x space'] FROM (SELECT {'x space':1, 'y':2, 'z':3} as a);

-- 你还可以通过struct_extract函数将键传入第二个参数位置访问其值
SELECT struct_extract({'x space': 1, 'y': 2, 'z': 3},'x space');

\

  • Struct.*

星号表示法(*)可用于将结构中的所有键作为单独的列检索,而不是从结构中检索单个键。当先前的操作创建未知形状的结构时,或者查询必须处理任何潜在的结构键时,该功能很有用。

%%sql
-- 使用以下方式可以访问所有的键值, 返回一个以键为列名, 以值为取值的表格
SELECT a.* FROM (SELECT {'x':1, 'y':2, 'z':3} as a);
x y z
1 2 3

\

  • 点表示法的操作顺序

使用点表示法引用STRUCT可能会与引用schema和table产生混淆。通常,DAI会首先查找列,然后查找列中的STRUCT键。DAI按照这种顺序解析引用,使用第一个匹配项进行操作:

不用.

SELECT part1 FROM tbl
  1. part1是列

一个.

SELECT part1.part2 FROM tbl
  1. part1是表,part2是列
  2. part1是列,part2是列的属性

两个或者更多.

SELECT part1.part2.part3 FROM tbl
  1. part1是schema,part2是表,part3是列
  2. part1是表,part2是列,part3是列的属性
  3. part1是列,part2是列的属性,part3是列的属性的属性

任何额外的部分(例如 .part4.part5 等)始终被视为属性。

\

  • 使用row函数创建STRUCT

row函数可用于自动将多个列转换为单个STRUCT列。每个输入列的名称用作键,每列的值将成为该键处的值。

将多个表达式转换为STRUCT时,row函数名称是可选的-只需要一对括号也可以。

名为t1的示例数据表:

my_column another_column
1 a
2 b

row函数示例:

%%sql
CREATE TABLE bigquant_t1 (my_column INT, another_column VARCHAR);
INSERT INTO bigquant_t1 VALUES(1, 'a'), (2, 'b');

SELECT 
    row(my_column, another_column) as my_struct_column,
    (my_column, another_column) as identical_struct_column
FROM bigquant_t1;

示例输出:

my_struct_column identical_struct_column
{'my_column': 1, 'another_column': a} {'my_column': 1, 'another_column': a}
{'my_column': 2, 'another_column': b} {'my_column': 2, 'another_column': b}

row函数(或简化的括号语法)也可以任意表达式作为输入而不只是列名。对于表达式的情况,将以vN的格式自动生成一个键,其中N是一个数字,表示其在row函数中的参数位置(例如:v1、v2 等)。这可以与列名组合使用,作为对row函数同一调用中的输入。此示例使用与上面相同的输入表。

以列名、常量和表达式作为输入的row函数示例:

%%sql
CREATE TABLE bigquant_t1 (my_column INT, another_column VARCHAR);
INSERT INTO bigquant_t1 VALUES(1, 'a'), (2, 'b');

SELECT 
    row(my_column, 42, my_column + 1) as my_struct_column,
    (my_column, 42, my_column + 1) as identical_struct_column
FROM t1;

示例输出:

my_struct_column identical_struct_column
{'my_column': 1, 'v2': 42, 'v3': 2} {'my_column': 1, 'v2': 42, 'v3': 2}
{'my_column': 2, 'v2': 42, 'v3': 3} {'my_column': 2, 'v2': 42, 'v3': 3}

\

map

  • Map 数据类型

MAPSTRUCT类似,它们都是“条目”的有序列表,而条目中的键映射到值。但是,MAP不需要在每一行上都存在相同的键。当架构事先未知时,MAP很有用。这两者的灵活性是一个关键的差异。

MAP必须对所有键具有单一类型,对于所有值也必须具有单一类型。键和值可以是任何类型,并且键的类型不需要与值的类型匹配(例如:INT映射到VARCHARMAP)。MAP不能有重复的键。如果未找到键,则MAP返回一个空列表,而不是像STRUCT那样抛出错误;

STRUCT必须具有字符串键,但每个键可以具有不同类型的值。有关嵌套数据类型之间的比较,请参阅数据类型概述

要构造MAP,请使用map函数。提供键列表作为第一个参数,提供第二个参数的值列表。或者使用map_from_entries函数。

\

  • 创建Map
%%sql
-- 建立一个整数到字符的映射关系: {1=a, 5=e}
SELECT map([1, 5], ['a', 'e']);

-- 或者你还可以使用函数map_from_entries
SELECT map_from_entries([(1, 'a'), (5, 'e')]);

-- 建立一个从整数到浮点数的对应关系
SELECT map([1, 5], [42.001, -32.1]);

-- 建立一个从字典到字典的映射关系
SELECT map([['a', 'b'], ['c', 'd']], [[1.1, 2.2], [3.3, 4.4]]);

-- 使用具有整数键和双精度值的映射列创建表
CREATE TABLE map_table (map_col MAP(INT,DOUBLE));

\

  • 查询Map

MAP使用括号表示法检索值。从MAP中查询将返回LIST而不是单个值,空LIST表示未找到键。

%%sql
-- 你可以将键(原象)放入方括号中去访问值(象)
SELECT map([100, 5], [42, 43])[100];

-- 上述返回的只是映射关系中的象, 下面再套上一个索引访问象中的值
SELECT map([100, 5], [42, 43])[100][1];

-- 123不存在原象中, 所以找不到它对应的象
SELECT map([100, 5], [42, 43])[123];

-- 你还可以利用element_at函数, 将原象的值传入第二个参数
SELECT element_at(map([100, 5], [42, 43]),100);

\

union

  • Union数据类型

UNION类型(不要与 SQL UNION运算符混淆)是一种嵌套类型,能够保存多个“替代”值中的一个,就像C中的union一样。主要区别在于这些UNION类型是tagged unions,因此总是带有一个标签tag,该标签指示它当前持有的替代值,即使内部值本身为空。 因此,UNION类型更类似于C++17的std::variant,Rust的Enum或大多数函数式语言中存在的“sum type”。

UNION类型必须始终至少有一个成员,虽然它们可以包含同一类型的多个成员,但tag名称必须是唯一的。UNION类型最多可以有 256 个成员。

在底层,UNION类型是在STRUCT类型之上实现的,只需将tag作为为第一个条目。

UNION值可以使用UNION_VALUE(tag := expr)函数或通过从成员类型强制转换来创建。

\

  • 示例
%%sql
/*创建一个包含字段u的表, 这个字段可以储存整数型和字符型数据, 并且给数据类型搭上了标签, 其中整数型的标签为num, 字符型标签为str*/
CREATE TABLE bigquant_table(u UNION(num INT, str VARCHAR));
INSERT INTO bigquant_table values (1) , ('two') , (union_value(str := 'three'));
SELECT u from bigquant_table;
-- returns:
--    1
--    two
--    three
-- Select all the 'str' members

/*查询出所有字符型的值, 如果不是字符串, 则输出NULL*/
SELECT union_extract(u, 'str') FROM bigquant_table;
-- 你还可以用: 变量.数据类型 的方法, 实现的功能和上一句的功能一致
SELECT u.str FROM bigquant_table;
-- returns: 
--    NULL
--    two
--    three

-- 查询所有值背后的数据类型标签
SELECT union_tag(u) FROM bigquant_table;
-- returns:
--    num
--    str
--    str

\

  • Union强制转换

与其他嵌套类型相比,UNION允许一系列的隐式强制转换,以便在将其成员作为“子类型”使用。 这些强制转换的设计考虑了两个原则:避免歧义和避免可能导致信息丢失的转换。这可以防止UNION完全transparent,同时允许UNION类型与其成员具supertype关系。

UNION类型通常不能隐式强制转换为其任意的成员类型,因为与目标类型不匹配的其他成员中的信息将“丢失”。如果要强制将UNION转换成其某一个成员,则应显式使用union_extract函数。

唯一的例外是将UNION转换为VARCHAR时,成员都将使用其相应的VARCHAR强制转换。由于一切都可以强制转换到VARCHAR,这在某种程度上是安全的。

\

  • 强制转换成Union
    • 如果类型可以隐式强制转换为UNION其中一个成员类型,那么该类型始终可以隐式强制转换为UNION
    • 如果有多个候选项,则内置的隐式强制转换优先级规则将确定目标类型。例如,FLOAT -> UNION(i INT, v VARCHAR)强制转换将始终将FLOAT优先强制转换为INT而非VARCHAR
    • 如果强制转换仍然不明确,即有多个候选者具有相同的隐式转换优先级,则会引发错误。这通常发生在UNION包含相同类型的多个成员时,例如FLOAT -> UNION(i INT, num INT)总是不明确的。

那么,如果我们想创建一个具有相同类型多个成员的UNION,要如何消除歧义呢?

通过使用union_value函数,该函数使用一个参数用来指定tag。例如,union_value(num := 2::INT)将创建一个带有标签num的类型为INT单成员UNION。这可以用来消除UNIONUNION的显式(或隐式)强制转换,比如CAST(union_value(b := 2) AS UNION(a INT, b INT))

\

  • Union间的强制转换

如果源类型是目标类型的“子集”,则UNION类型可以在彼此之间强制转换类型。换句话说,源UNION中的所有tag都必须存在于目标UNION中,并且所有匹配tag的类型必须在源和目标之间隐式转换。实质上,这意味着UNION类型相对于其成员是协变的。

Ok 目标 注释
UNION(a A, b B) UNION(a A, b B, c C)
UNION(a A, b B) UNION(a A, b C) 如果B可以隐式转换到C
UNION(a A, b B, c C) UNION(a A, b B)
UNION(a A, b B) UNION(a A, b C) 如果B不能隐式转换到C
UNION(A, B, D) UNION(A, B, C)

\

表达式

逻辑表达式

有以下三种可用的逻辑运算符:ANDORNOT。SQL 使用TRUEFALSENULL的三值逻辑系统。注意,涉及NULL的逻辑运算符并不总是计算为NULL。例如,NULL AND FALSE的计算结果为FALSENULL OR TRUE的计算结果为TRUE。以下是完整的真值表:

a b a AND b a OR b
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL NULL NULL NULL
a NOT a
TRUE FALSE
FALSE TRUE
NULL NULL

ANDOR运算符是可交换的,即可以在不影响结果的情况下切换左右操作数。

\

比较表达式

  • 比较运算符

下表展示了所有的标准比较运算符。只要输入参数中的任何一个为NULL,则比较的输出为NULL

算子 描述 例子 结果
< 小于 2 < 3 TRUE
> 大于 2 > 3 FALSE
<= 小于等于 2 <= 3 TRUE
>= 大于等于 4 >= NULL NULL
= 等于 NULL = NULL NULL
<>!= 不等于 2 <> 2 FALSE

下表展示了标准区分运算符。这些运算符将NULL值视为相等。

运算符 描述 例子 结果
IS DISTINCT FROM 相等,包括NULL 2 IS DISTINCT FROM NULL TRUE
IS NOT DISTINCT FROM 不相等,包括NULL NULL IS NOT DISTINCT FROM NULL TRUE

\

  • BETWEENIS和(NOT) NULL

除了标准的比较运算符之外,还有BETWEENIS (NOT) NULL运算符。它们的行为与标准运算符非常相似,但具有 SQL 标准要求的特殊语法。如下表所示:

谓词 描述
a BETWEEN x AND y 等价于 a >= x AND a <= y
a NOT BETWEEN x AND y 等价于 a < x OR a > y
expression IS NULL 如果表达式为NULL返回TRUE, 否则返回FALSE
expression ISNULL IS NULL的别名
expression IS NOT NULL 如果表达式为NULL返回FALSE, 否则返回TRUE
expression NOTNULL IS NOT NULL的别名


\

类型转换表达式

强制类型转换是指将行的类型从一种类型更改为另一种类型的过程。标准 SQL 语法是CAST(expr AS typename)。DAI还支持更容易缩写方式expr::typename

%%sql
/*产生一个整数型序列, 并将该序列的值全部转化为字符型*/
SELECT CAST(i AS VARCHAR) FROM generate_series(1, 3) bigquant_table(i);
-- "1", "2", "3"

/*产生一个整数型序列, 并将该序列的值全部转化为浮点型*/
SELECT i::DOUBLE FROM generate_series(1, 3) bigquant_table(i);
-- 1.0, 2.0, 3.0

/*你不能将一个字符型的数据转化为整数型的数据, 否则程序报错*/
SELECT CAST('hello' AS INTEGER);

/* 在数据类型转化前, 你可以使用函数TRY_CAST来判断是否能转化成功, 如果不成功则返回NULL*/
SELECT TRY_CAST('hello' AS INTEGER);
-- NULL

强制转换的行为取决于源和目标类型,并非所有CAST都可以。例如,无法将INTEGER转换为DATE。当无法成功执行强制转换时,强制转换也可能引发错误。例如,尝试将字符串'hello'强制转换为INTEGER将导致引发错误。TRY_CAST则不会抛出错误,而是返回NULL

  • 隐式强制转换

在许多情况下,系统将自行添加强制转换,这称为隐式强制转换。例如,当使用与函数参数类型不匹配的参数调用函数时,就会发生这种情况。

考虑函数SIN(DOUBLE)。此函数将类型DOUBLE的列作为输入参数,但是,也可以使用整数调用:SIN(1)。整数在传递给SIN函数之前被转换为DOUBLE

通常,隐式强制转换仅向上转换。也就是说,我们可以隐式地将INTEGER转换为BIGINT,但不能反过来。

\

条件表达式

CASE语句根据条件执行切换。基本形式与许多编程语言中使用的三元条件相同(CASE WHEN cond THEN a ELSE b END等效于cond ? a : b),该语句也可以简单的表示成 IF(cond, a, b)

%%sql
CREATE OR REPLACE TABLE bigquant_table as SELECT UNNEST([1, 2, 3]) as i;

SELECT i, CASE WHEN i>2 THEN 1 ELSE 0 END AS test FROM bigquant_table;
-- 1, 2, 3
-- 0, 0, 1

-- 等价于
SELECT i, IF(i > 2, 1, 0) AS test FROM bigquant_table;
-- 1, 2, 3
-- 0, 0, 1

CASE语句的WHEN cond THEN expr部分可以链接,每当某个元组有任何条件返回 true时,都会计算并返回相应的表达式。

%%sql
CREATE OR REPLACE TABLE bigquant_table as SELECT UNNEST([1, 2, 3]) as i;
SELECT i, CASE WHEN i=1 THEN 10 WHEN i=2 THEN 20 ELSE 0 END AS test FROM bigquant_table;
-- 1, 2, 3
-- 10, 20, 0

CASE语句的ELSE部分是可选的。如果未提供 else 语句并且没有任何条件匹配,则CASE语句将返回NULL

%%sql
CREATE OR REPLACE TABLE bigquant_table as SELECT UNNEST([1, 2, 3]) as i;
SELECT i, CASE WHEN i=1 THEN 10 END AS test FROM bigquant_table;
-- 1, 2, 3
-- 10, NULL, NULL

CASE之后WHEN之前也可以提供单个表达式,CASE语句实质上将转换为 switch 语句。

%%sql
CREATE OR REPLACE TABLE bigquant_table as SELECT UNNEST([1, 2, 3]) as i;
SELECT i, CASE i WHEN 1 THEN 10 WHEN 2 THEN 20 WHEN 3 THEN 30 END AS test FROM bigquant_table;
-- 1, 2, 3
-- 10, 20, 30

-- 等价于
SELECT i, CASE WHEN i=1 THEN 10 WHEN i=2 THEN 20 WHEN i=3 THEN 30 END AS test FROM bigquant_table;

\

in

IN操作符检查右侧表达式集(RHS)中是否包含左侧表达式。如果表达式存在于RHS中,则IN操作符返回true;如果表达式不在RHS中且RHS没有NULL值返回false,或者如果表达式不在RHS中且RHS具有NULL值,则运算符返回NULL

%%sql
SELECT 'Math' IN ('CS', 'Math');
-- true

SELECT 'English' IN ('CS', 'Math');
-- false

SELECT 'Math' IN ('CS', 'Math', NULL);
-- true

SELECT 'English' IN ('CS', 'Math', NULL);
-- NULL

NOT IN可用于检查集合中是否存在元素。X NOT IN Y等效于NOT(X IN Y)

IN操作符还可以与返回单个列的子查询一起使用。

\

*

*表达式可用于SELECT语句中以选择FROM子句中投影的所有列。

%%sql
/*取出表格cn_stock_bar1d中所有的字段*/
SELECT * FROM cn_stock_bar1de;

可以使用EXCLUDEREPLACE修改*表达式。

  • EXCLUDE子句

EXCLUDE让我们可以从*表达式中排除特定列。

%%sql
/*取出表格中除了换手率以外的所有字段*/
SELECT * EXCLUDE (turn) FROM cn_stock_bar1d;
  • REPLACE子句

REPLACE让我们可以用不同的表达式替换特定的列。

%%sql
/*换手率乘以100的值替换原来的换手率*/
SELECT * REPLACE (turn / 1000 AS turn) FROM bigquant_table;
  • COLUMNS

COLUMNS表达式可用于在多个列上执行相同的表达式。与*表达式一样,它只能在SELECT子句中使用。

%%sql
CREATE TABLE bigquant_numbers(id int, number int);
INSERT INTO bigquant_numbers VALUES (1, 10), (2, 20), (3, NULL);
SELECT MIN(COLUMNS(*)), COUNT(COLUMNS(*)) from bigquant_numbers;
min(numbers.id) min(numbers.number) count(numbers.id) count(numbers.number)
1 10 3 2

COLUMNS语句中的*表达式也可以包含EXCLUDEREPLACE,类似于常规的*表达式。

%%sql
CREATE TABLE bigquant_numbers(id int, number int);
INSERT INTO bigquant_numbers VALUES (1, 10), (2, 20), (3, NULL);

SELECT MIN(COLUMNS(* REPLACE (number + id AS number))), COUNT(COLUMNS(* EXCLUDE (number))) from bigquant_numbers;
min(numbers.id) min(number := (number + id)) count(numbers.id)
1 11 3

COLUMNS表达式也可以组合,只要COLUMNS包含相同的(*)表达式:

%%sql
CREATE TABLE bigquant_numbers(id int, number int);
INSERT INTO bigquant_numbers VALUES (1, 10), (2, 20), (3, NULL);

SELECT COLUMNS(*) + COLUMNS(*) FROM bigquant_numbers;
(numbers.id + numbers.id) (numbers.number + numbers.number)
2 20
4 40
6 NULL

最后,COLUMNS支持将正则表达式作为字符串常量传入:

%%sql
CREATE TABLE bigquant_numbers(id int, number int);
INSERT INTO bigquant_numbers VALUES (1, 10), (2, 20), (3, NULL);

SELECT COLUMNS('(id|numbers?)') FROM bigquant_numbers ;
id number
1 10
2 20
3 NULL
  • Struct.*

*表达式还可用于从STRUCT中检索所有键作为单独的列。当先前的操作创建了未知形状的STRUCT时,或者查询必须处理任何潜在的STRUCT键时,这特别有用。

%%sql
/*如果查询的是struct数据, 利用: 表名.*的规则即可获取所有的键和值*/
SELECT bigquant_t.* FROM (SELECT {'x':1, 'y':2, 'z':3} as bigquant_t);
x y z
1 2 3

\

子查询

  • 标量子查询

标量子查询是返回单个值的子查询。它们可以在任何可以使用常规表达式的地方使用。如果标量子查询返回多个值,则将使用返回的第一个值。

以下表作为示例:

  • 分数
grade course
7 Math
9 Math
8 CS
%%sql
CREATE TABLE bigquant_grades(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

我们可以运行以下查询来获取最低分数:

%%sql
CREATE TABLE bigquant_grades(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

SELECT MIN(grade) FROM bigquant_grades ;
-- 返回{7}

通过在WHERE子句中使用标量子查询,我们可以找出某个分数是哪门课程的:

%%sql
CREATE TABLE bigquant_grades(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

SELECT course FROM bigquant_grades WHERE grade = (SELECT MIN(grade) FROM bigquant_grades);
-- 返回{Math}
  • Exists

EXISTS运算符用于测试子查询中是否存在任何行。当子查询返回一条或多条记录时,它返回true,否则返回false。

例如,我们可以使用它来确定给定课程是否存在分数:

%%sql
CREATE TABLE bigquant_grades(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

SELECT EXISTS(SELECT * FROM bigquant_grades WHERE course='Math');
-- 返回true

SELECT EXISTS(SELECT * FROM grades WHERE course='History');
-- 返回false
  • In运算符

IN运算符检查由子查询或右侧表达式集(RHS)定义的结果中是否包含左侧表达式。如果表达式存在于RHS中,则运算符返回true;如果表达式不在RHS中且RHS没有NULL值,则返回false;如果表达式不在RHS中且RHS具有NULL值,则运算符返回false。

我们可以像使用EXISTS运算符一样使用IN运算符:

%%sql
CREATE TABLE bigquant_grades(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

SELECT 'Math' IN (SELECT course FROM bigquant_grades);
-- true
  • 相关子查询

到目前为止,这里介绍的所有子查询都是不相关的子查询,其中子查询本身是完全独立的,可以在没有父查询的情况下运行。存在第二种类型的子查询,称为相关子查询。对于相关子查询,子查询使用父子查询中的值。

子查询对父查询中的每一行运行一次。也许设想这一点的一种简单方法是,相关子查询是一个应用于源数据集中每一行的函数。

例如,假设我们想找到每门课程的最低分数。我们可以按如下方式进行操作:

%%sql
CREATE TABLE bigquant_grades(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');
CREATE TABLE bigquant_grades_parent(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades_parent VALUES (7, 'Math'), (10, 'Math'), (8, 'CS');

SELECT *
FROM bigquant_grades bigquant_grades_parent
WHERE grade=
    (SELECT MIN(grade)
     FROM bigquant_grades
     WHERE bigquant_grades.course=bigquant_grades_parent.course);
-- {7, Math}, {8, CS}

子查询使用父查询(grades_parent.course)中的列。从概念上,我们可以将子查询视为一个函数,其中相关列是该函数的参数:

%%sql
CREATE TABLE bigquant_grades(grade INTEGER, course VARCHAR);
INSERT INTO bigquant_grades VALUES (7, 'Math'), (9, 'Math'), (8, 'CS');

-- 返回7
SELECT MIN(grade) FROM grades WHERE course='Math';

-- 返回8
SELECT MIN(grade) FROM grades WHERE course='CS';

现在,当我们为每一行执行此函数时,我们可以看到对于Math将返回7,而对于CS则返回8。我们将它与该行实际的分数进行比较,结果(Math, 9)这一行将被过滤掉,因为9 <> 7

\

函数

操作符

函数名称 描述 例子
+ 加法 1 + 2 = 3; '2023-1-1'::DATE + INTERVAL 1 MONTH = '2023-2-1'::DATE
- 减法 1 - 2 = -1; '2023-1-1'::DATE - INTERVAL 1 MONTH = '2022-12-1'::DATE
* 乘法 1 * 2 = 2
/ 除法 7 / 2 = 3.5
// 整数除法 7 // 2 = 3
% 取模 26 % 3 = 2
** 指数 2 ** 3 = 8
^ 指数, ** 的别名 2 ^ 3 = 8
& 按位与 1 & 2 = 0
| 按位或 1 | 2 = 3
<< 位左移 1 << 3 = 8
>> 位右移 8 >> 2 = 2
~ 按位取反 ~15 = -16
! 阶乘 4! = 24
string ^@ search_string starts_with 的别名 'abc' ^@ 'a' = true
string || string 字符串连接 'Big' || 'Quant' = 'BigQuant'
string[index] array_extract 的别名 'BigQuant'[4] = 'Q'
string[begin:end] array_slice 的别名,如果没有参数则返回 NULL 'BigQuant'[:4] = 'BigQ'
l1 @> l2 A ⊃ B? 后者是否为前者子集, list_has_all(l1, l2) 的别名 [1,2,3] @> [2,null] = true
l1 <@ l2 A ⊂ B? 前者是否为后者子集, list_has_all(l2, l1) 的别名 [1,2,null] <@ [1,2] = true
l1 && l2 A ∩ B? 两者是否有交集, list_has_any(l1, l2) 的别名 [1, 2, 3] && [2, 3, 4] = true
string LIKE pattern string 是否匹配 pattern,支持通配符 %_ 'hello' LIKE '%lo' = true
string NOT LIKE pattern string 是否不匹配 pattern,支持通配符 %_ 'hello' NOT LIKE '%lo' = false
string ILIKE pattern string 是否匹配 pattern,支持通配符 %_,忽略大小写 'hello' ILIKE '%LO' = true
string NOT ILIKE pattern string 是否不匹配 pattern,支持通配符 %_,忽略大小写 'hello' NOT ILIKE '%LO' = false
string ~~ pattern Postgres-style, 等同于 LIKE 'hello' ~~ '%lo' = true
string !~~ pattern Postgres-style, 等同于 NOT LIKE 'hello' !~~ '%lo' = false
string ~~* pattern Postgres-style, 等同于 ILIKE 'hello' ~~* '%LO' = true
string !~~* pattern Postgres-style, 等同于 NOT ILIKE 'hello' !~~* '%LO' = false
string LIKE pattern ESCAPE escape_character string 是否匹配 pattern,使用 escape_character 转义通配符 %_ 'xlo' LIKE '$_lo' ESCAPE ' = false; '_lo' LIKE '$_lo' ESCAPE ' = true
string NOT LIKE pattern ESCAPE escape_character string 是否不匹配 pattern,使用 escape_character 转义通配符 %_ 'xlo' NOT LIKE '$_lo' ESCAPE ' = true; '_lo' NOT LIKE '$_lo' ESCAPE ' = false
string ILIKE pattern ESCAPE escape_character string 是否匹配 pattern,使用 escape_character 转义通配符 %_,忽略大小写 'xlo' ILIKE '$_LO' ESCAPE ' = false; '_lo' ILIKE '$_LO' ESCAPE ' = true
string NOT ILIKE pattern ESCAPE escape_character string 是否不匹配 pattern,使用 escape_character 转义通配符 %_,忽略大小写 'xlo' NOT ILIKE '$_LO' ESCAPE ' = true; '_lo' NOT ILIKE '$_LO' ESCAPE ' = false
string SIMILAR TO pattern 使用 regex 判断 string 是否匹配 pattern 'hello' SIMILAR TO '[xh].*lo' = true
string NOT SIMILAR TO pattern 使用 regex 判断 string 是否不匹配 pattern 'hello' NOT SIMILAR TO '[xh].*lo' = false
string ~ pattern POSIX-style, 等同于 SIMILAR TO 'hello' ~ '[xh].*lo' = true
string !~ pattern POSIX-style, 等同于 NOT SIMILAR TO 'hello' !~ '[xh].*lo' = false

标量函数

函数名称 描述 例子
abs 绝对值 abs(-17.4)
acos 计算 x 的反余弦 acos(0.5)
age 减去参数,得到两个时间戳之间的时间差 age(TIMESTAMP '2001-04-10', TIMESTAMP '1992-09-20')
aggregate 对列表元素执行聚合函数名称 aggregate([1, 2, NULL], 'min')
alias 返回给定表达式的名称 alias(42 + 1)
apply 返回一个列表,该列表是将 lambda 函数应用于输入列表的每个元素的结果。有关更多详细信息,请参阅 Lambda 函数部分 apply([1, 2, 3], x -> x + 1)
array_aggr 对列表元素执行聚合函数名称 array_aggr([1, 2, NULL], 'min')
array_aggregate 对列表元素执行聚合函数名称 array_aggregate([1, 2, NULL], 'min')
array_apply 返回一个列表,该列表是将 lambda 函数应用于输入列表的每个元素的结果。有关更多详细信息,请参阅 Lambda 函数部分 array_apply([1, 2, 3], x -> x + 1)
array_cat 连接两个数组, list_concat 的别名 array_cat([1,2], [3,4]) = [1,2,3,4]
array_concat 连接两个数组, list_concat 的别名 array_concat([1,2], [3,4]) = [1,2,3,4]
array_contains 判断数组是否包含元素, list_contains 的别名 array_contains([1,2,null], 2) = true
array_cosine_similarity 计算两个相同大小的数组之间的余弦相似度。数组元素不能为 NULL。数组可以具有任意大小,只要两个参数的大小相同即可。 array_cosine_similarity([1, 2, 3], [1, 2, 3])
array_cross_product 计算两个大小为 3 的数组的叉积。数组元素不能为 NULL。 array_cross_product([1, 2, 3], [1, 2, 3])
array_distance 计算两个相同大小的数组之间的距离。数组元素不能为 NULL。数组可以具有任意大小,只要两个参数的大小相同即可。 array_distance([1, 2, 3], [1, 2, 3])
array_distinct 从列表中删除所有重复项和 NULL。不保留原始顺序 array_distinct([1, 1, NULL, -3, 1, 5])
array_dot_product 计算两个相同大小的数组之间的内积。数组元素不能为 NULL。数组可以具有任意大小,只要两个参数的大小相同即可。 array_dot_product([1, 2, 3], [1, 2, 3])
array_extract 提取数组中的元素 (从 1 开始), 可缩写成 array[index] array_extract([1,2,3], 2) = 2
array_filter 根据输入列表中 lambda 函数返回 true 的元素构造一个列表 array_filter([3, 4, 5], x -> x > 4)
array_grade_up 返回其排序位置的索引。 array_grade_up([3, 6, 1, 2])
array_has 判断数组是否包含元素, list_contains 的别名 array_has([1,2,null], 2) = true
array_indexof 返回数组中元素的索引 (从 1 开始), list_position 的别名 array_indexof([1,2,3], 2) = 2
array_inner_product 计算两个相同大小的数组之间的内积。数组元素不能为 NULL。数组可以具有任意大小,只要两个参数的大小相同即可。 array_inner_product([1, 2, 3], [1, 2, 3])
array_length 返回数组长度, len 的别名 array_length([1,2,3]) = 3
array_position 返回数组中元素的索引 (从 1 开始), list_position 的别名 array_position([1,2,3], 2) = 2
array_resize 调整数组大小, list_resize 的别名 array_resize([1,2,3], 5) = [1,2,3,null,null]
array_reverse_sort 以相反的顺序对列表的元素进行排序 array_reverse_sort([3, 6, 1, 2])
array_slice 使用切片约定提取子列表。接受负值 array_slice(l, 2, 4)
array_sort 对列表的元素进行排序 array_sort([3, 6, 1, 2])
array_transform 返回一个列表,该列表是将 lambda 函数应用于输入列表的每个元素的结果。有关更多详细信息,请参阅 Lambda 函数部分 array_transform([1, 2, 3], x -> x + 1)
array_unique 计算列表中的唯一元素 array_unique([1, 1, NULL, -3, 1, 5])
array_value 创建一个包含参数值的数组。 array_value(4, 5, 6)
ascii 返回一个整数,表示字符串第一个字符的 Unicode 代码点 ascii('Ω')
asin 计算 x 的反正弦 asin(0.5)
atan 计算 x 的反正切 atan(0.5)
atan2 计算反正切 (y, x) atan2(1.0, 0.0)
bar 绘制一条带,其宽度与 (x - min) 成正比,并且当 x = max 时等于宽度字符。宽度默认为80 bar(5, 0, 20, 10)
base64 将 blob 转换为 base64 编码的字符串 base64('A'::blob)
bin 将值转换为二进制表示形式 bin(42)
bit_count 返回设置的位数 bit_count(31)
bit_length 返回字符串位长度 bit_length('xyz') = 24
bit_position 返回位内指定子字符串的第一个起始索引,如果不存在则返回零。第一个(最左边)位索引为 1 bit_position('010'::BIT, '1110101'::BIT)
bitstring 填充位串直到指定长度 bitstring('1010'::BIT, 7)
cardinality 返回地图的大小(或地图中的条目数) cardinality( map([4, 2], ['a', 'b']) );
cbrt 返回 x 的立方根 cbrt(8)
ceil 将数字向上舍入 ceil(17.4)
ceiling 将数字向上舍入 ceiling(17.4)
century 从日期或时间戳中提取世纪部分 century(timestamp '2021-08-03 11:59:44.123456')
chr 返回与 ASCII 代码值或 Unicode 代码点对应的字符 chr(65)
contains 判断字符串是否包含子串 contains('hello world', 'world') = true
cos 计算 x 的余弦 cos(90)
cot 计算 x 的余切 cot(0.5)
current_database 返回当前活动数据库的名称 current_database()
current_date 返回当前日期 current_date()
current_localtime 返回当前时间 current_localtime()
current_localtimestamp 返回当前时间戳 current_localtimestamp()
current_query 以字符串形式返回当前查询 current_query()
current_schema 返回当前活动模式的名称。默认为主 current_schema()
current_schemas 返回模式列表。传递 True 参数以包含隐式模式 current_schemas(true)
current_setting 返回配置设置的当前值 current_setting('access_mode')
currval 返回序列的当前值 currval('my_sequence_name')
cut 将 arg 的值按照 bin_cut_points 进行分桶, 返回值为 arg 所在的桶。bin_cut_points 为一个列表, 列表中的元素为分桶的边界值 cut(open, [-inf, 70, 80, 90, inf])
damerau_levenshtein 编辑距离的扩展还包括相邻字符的换位作为允许的编辑操作。换句话说,将一个字符串更改为另一个字符串所需的最少编辑操作(插入、删除、替换或转置)次数。区分大小写 damerau_levenshtein('hello', 'world')
date_diff 时间戳之间的分区边界数 date_diff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
date_part 获取子字段(相当于提取) date_part('minute', TIMESTAMP '1992-09-20 20:38:40')
date_sub 时间戳之间完整分区的数量 date_sub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
date_trunc 截断至指定精度 date_trunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')
datediff 时间戳之间的分区边界数 datediff('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
datepart 获取子字段(相当于提取) datepart('minute', TIMESTAMP '1992-09-20 20:38:40')
datesub 时间戳之间完整分区的数量 datesub('hour', TIMESTAMPTZ '1992-09-30 23:59:59', TIMESTAMPTZ '1992-10-01 01:58:00')
datetrunc 截断至指定精度 datetrunc('hour', TIMESTAMPTZ '1992-09-20 20:38:40')
day 从日期或时间戳中提取日期部分 day(timestamp '2021-08-03 11:59:44.123456')
dayname 工作日的(英文)名称 dayname(TIMESTAMP '1992-03-22')
dayofmonth 从日期或时间戳中提取月份部分 dayofmonth(timestamp '2021-08-03 11:59:44.123456')
dayofweek 从日期或时间戳中提取星期几部分 dayofweek(timestamp '2021-08-03 11:59:44.123456')
dayofyear 从日期或时间戳中提取年份部分 dayofyear(timestamp '2021-08-03 11:59:44.123456')
decade 从日期或时间戳中提取十年部分 decade(timestamp '2021-08-03 11:59:44.123456')
decode 将 blob 转换为 varchar。如果 blob 不是有效的 utf-8,则失败 decode('\xC3\xBC'::BLOB)
degrees 将弧度转换为度数 degrees(pi())
editdist3 将一个字符串更改为另一个字符串所需的最少单字符编辑(插入、删除或替换)次数。区分大小写 editdist3('big','db')
element_at 返回包含给定键的值的列表,如果映射中不包含该键,则返回空列表。第二个参数中提供的键的类型必须与映射键的类型匹配,否则将返回错误 element_at(map(['key'], ['val']), 'key')
encode 将 varchar 转换为 blob。将 utf-8 字符转换为文字编码 encode('my_string_with_ü')
enum_code 返回支持给定枚举值的数值 enum_code('happy'::mood)
enum_first 返回输入枚举类型的第一个值 enum_first(NULL::mood)
enum_last 返回输入枚举类型的最后一个值 enum_last(NULL::mood)
enum_range 以数组形式返回输入枚举类型的所有值 enum_range(NULL::mood)
enum_range_boundary 以数组形式返回两个给定枚举值之间的范围。这些值必须是相同的枚举类型。当第一个参数为 NULL 时,结果从枚举类型的第一个值开始。当第二个参数为NULL时,结果以枚举类型的最后一个值结束 enum_range_boundary(NULL, 'happy'::mood)
epoch 从时间类型中提取纪元分量 epoch(timestamp '2021-08-03 11:59:44.123456')
epoch_ms 从时间类型中提取以毫秒为单位的纪元分量 epoch_ms(timestamp '2021-08-03 11:59:44.123456')
epoch_ns 从时间类型中提取纳秒级的纪元分量 epoch_ns(timestamp '2021-08-03 11:59:44.123456')
epoch_us 从时间类型中提取以微秒为单位的纪元分量 epoch_us(timestamp '2021-08-03 11:59:44.123456')
era 从日期或时间戳中提取时代部分 era(timestamp '2021-08-03 11:59:44.123456')
error 抛出给定的错误消息 error('access_mode')
even 通过从零舍入将 x 舍入到下一个偶数 even(2.9)
exp 计算 e 的 x 次方 exp(1)
factorial x 的阶乘。计算当前整数与其以下所有整数的乘积 4!
filter 根据输入列表中 lambda 函数返回 true 的元素构造一个列表 filter([3, 4, 5], x -> x > 4)
flatten 将嵌套列表展平一层 flatten([[1, 2, 3], [4, 5]])
floor 将数字向下舍入 floor(17.4)
format 使用 fmt 语法格式化字符串 format('Benchmark "{}" took {} seconds', 'CSV', 42)
formatReadableDecimalSize 将字节转换为人类可读的表示形式(例如 16000 -> 16.0 KB) formatReadableDecimalSize(1000 * 16)
formatReadableSize 将字节转换为人类可读的表示形式(例如 16000 -> 15.6 KiB) formatReadableSize(1000 * 16)
format_bytes 将字节转换为人类可读的表示形式(例如 16000 -> 15.6 KiB) format_bytes(1000 * 16)
from_base64 将base64编码的字符串转换为字符串 from_base64('QQ==')
from_binary 将值从二进制表示形式转换为 blob from_binary('0110')
from_hex 将值从十六进制表示形式转换为 blob from_hex('2A')
gamma (x-1) 阶乘的插值(因此允许小数输入) gamma(5.5)
gcd 计算 x 和 y 的最大公约数 gcd(42, 57)
gen_random_uuid 返回类似于以下内容的随机 UUID:eeccb8c5-9943-b2bb-bb5e-222f4e14b687 gen_random_uuid()
generate_series 创建开始和停止之间的值列表 - 包含停止参数 generate_series(2, 5, 3)
get_bit 从位串中提取第 n 位;第一个(最左边)位索引为 0 get_bit('0110010'::BIT, 2)
get_current_time 返回当前时间 get_current_time()
get_current_timestamp 返回当前时间戳 get_current_timestamp()
grade_up 返回其排序位置的索引。 grade_up([3, 6, 1, 2])
greatest 返回输入参数集的最大值 greatest(42, 84)
greatest_common_divisor 计算 x 和 y 的最大公约数 greatest_common_divisor(42, 57)
hamming 2 个长度相等的字符串中不同字符的位置数。区分大小写 hamming('big','dog')
hash 返回一个带有该值的哈希值的整数。请注意,这不是加密哈希 hash('🌎')
hex 将值转换为十六进制表示形式 hex(42)
hour 从日期或时间戳中提取小时部分 hour(timestamp '2021-08-03 11:59:44.123456')
ilike_escape 同 string ILIKE pattern ESCAPE escape_character ilike_escape('A%c', 'a$%c', ' ) = true
in_search_path 返回数据库/模式是否在搜索路径中 in_search_path('memory', 'main')
instr 返回 haystack 中第一次出现的针的位置,从 1 开始计数。如果没有找到匹配则返回 0 instr('test test','es')
isfinite 如果浮点值有限则返回 true,否则返回 false isfinite(5.5)
isinf 如果浮点值无穷大则返回 true,否则返回 false isinf('Infinity'::float)
isnan 如果浮点值不是数字,则返回 true,否则返回 false isnan('NaN'::FLOAT)
isodow 从日期或时间戳中提取 isodow 组件 isodow(timestamp '2021-08-03 11:59:44.123456')
isoyear 从日期或时间戳中提取等年部分 isoyear(timestamp '2021-08-03 11:59:44.123456')
jaccard 两个字符串之间的杰卡德相似度。不同的情况被认为是不同的。返回 0 到 1 之间的数字 jaccard('big','dog')
jaro_similarity 两个字符串之间的 Jaro 相似度。不同的情况被认为是不同的。返回 0 到 1 之间的数字 jaro_similarity('big','bigdb')
jaro_winkler_similarity 两个字符串之间的 Jaro-Winkler 相似度。不同的情况被认为是不同的。返回 0 到 1 之间的数字 jaro_winkler_similarity('big','bigdb')
julian 从日期或时间戳中提取儒略日数 julian(timestamp '2006-01-01 12:00')
last_day 返回该月的最后一天 last_day(TIMESTAMP '1992-03-22 01:02:03.1234')
lcase 返回小写字符串, lower 的别名 lcase('HELLO WORLD') = 'hello world'
lcm 计算 x 和 y 的最小公倍数 lcm(42, 57)
least 返回输入参数集的最小值 least(42, 84)
least_common_multiple 计算 x 和 y 的最小公倍数 least_common_multiple(42, 57)
left 提取最左边的计数字符 left('Hello🌎', 2)
left_grapheme 提取最左边的计数字素簇 left_grapheme('🤦🏼‍♂️🤦🏽‍♀️', 1)
len 返回字符串字符长度或列表长度, length 的别名 len('hello🌎') = 6
length 返回字符串字符长度或列表长度 length('hello🌎') = 6
length_grapheme 返回字素簇的长度 length_grapheme('🪐🌎') = 2
levenshtein 将一个字符串更改为另一个字符串所需的最少单字符编辑(插入、删除或替换)次数。区分大小写 levenshtein('big','db')
lgamma 计算伽玛函数的对数 lgamma(2)
like_escape 同 string LIKE pattern ESCAPE escape_character like_escape('a%c', 'a$%c', ' ) = true
list_aggr 对列表元素执行聚合函数名称 list_aggr([1, 2, NULL], 'min')
list_aggregate 对列表元素执行聚合函数名称 list_aggregate([1, 2, NULL], 'min')
list_apply 返回一个列表,该列表是将 lambda 函数应用于输入列表的每个元素的结果。有关更多详细信息,请参阅 Lambda 函数部分 list_apply([1, 2, 3], x -> x + 1)
list_cat 连接两个列表, list_concat 的别名 list_cat([1,2], [3,4]) = [1,2,3,4]
list_concat 连接两个列表 list_concat([1,2], [3,4]) = [1,2,3,4]
list_contains 判断列表是否包含元素 list_contains([1,2,null], 2) = true
list_cosine_similarity 计算两个列表之间的余弦相似度 list_cosine_similarity([1, 2, 3], [1, 2, 3])
list_distance 计算两个列表之间的距离 list_distance([1, 2, 3], [1, 2, 3])
list_distinct 从列表中删除所有重复项和 NULL。不保留原始顺序 list_distinct([1, 1, NULL, -3, 1, 5])
list_dot_product 计算两个列表之间的内积 list_dot_product([1, 2, 3], [1, 2, 3])
list_element 提取列表中的元素 (从 1 开始), list_extract 的别名 list_element([1,2,3], 2) = 2
list_extract 提取列表中的元素 (从 1 开始), 可缩写成 list[index] list_extract([1,2,3], 2) = 2
list_filter 根据输入列表中 lambda 函数返回 true 的元素构造一个列表 list_filter([3, 4, 5], x -> x > 4)
list_grade_up 返回其排序位置的索引。 list_grade_up([3, 6, 1, 2])
list_has 判断列表是否包含元素, list_contains 的别名 list_has([1,2,null], 2) = true
list_indexof list_position 的别名 list_indexof([1,2,null,3], 7) = 0
list_inner_product 计算两个列表之间的内积 list_inner_product([1, 2, 3], [1, 2, 3])
list_pack 创建包含参数值的列表 list_pack(4, 5, 6)
list_position 返回元素在列表中的索引 (从 1 开始) 如果有的话; 否则返回 0 list_position([1,2,null,3], 3) = 4
list_resize 调整列表大小, 如果新元素未设置则为 NULL list_resize([1,2,3], 5, 0) = [1,2,3,0,0]
list_reverse_sort 以相反的顺序对列表的元素进行排序 list_reverse_sort([3, 6, 1, 2])
list_slice 使用切片约定提取子列表。接受负值 list_slice(l, 2, 4)
list_sort 对列表的元素进行排序 list_sort([3, 6, 1, 2])
list_transform 返回一个列表,该列表是将 lambda 函数应用于输入列表的每个元素的结果。有关更多详细信息,请参阅 Lambda 函数部分 list_transform([1, 2, 3], x -> x + 1)
list_unique 计算列表中的唯一元素 list_unique([1, 1, NULL, -3, 1, 5])
list_value 创建包含参数值的列表 list_value(4, 5, 6)
ln 计算 x 的自然对数 ln(2)
log 计算 x 以 b 为底的对数。 b 可以省略,在这种情况下默认为 10 log(2, 64)
log10 计算 x 的 10 对数 log10(1000)
log2 计算 x 的 2-log log2(8)
lower 返回小写字符串 lower('HELLO WORLD') = 'hello world'
lpad 用左侧的字符填充字符串,直到有 count 个字符 lpad('hello', 10, '>')
ltrim 删除字符串左侧出现的任何字符 ltrim('>>>>test<<', '><')
make_date 给定零件的日期 make_date(1992, 9, 20)
make_time 给定部分的时间 make_time(13, 34, 27.123456)
make_timestamp 给定部分的时间戳 make_timestamp(1992, 9, 20, 13, 34, 27.123456)
make_timestamptz 构造当前时区的时间戳 make_timestamptz(2023, 8, 14, 13, 9, 23.123456) = 2023-08-14 13:09:23.123456+08:00
map 从一组键和值创建映射 map(['key1', 'key2'], ['val1', 'val2'])
map_concat 返回通过合并输入映射创建的映射,在键冲突时,该值是从具有该键的最后一个映射中获取的 map_concat(map([1,2], ['a', 'b']), map([2,3], ['c', 'd']));
map_entries 以键/值列表的形式返回映射条目 map_entries(map(['key'], ['val']))
map_extract 返回包含给定键的值的列表,如果映射中不包含该键,则返回空列表。第二个参数中提供的键的类型必须与映射键的类型匹配,否则将返回错误 map_extract(map(['key'], ['val']), 'key')
map_from_entries 返回根据数组条目创建的映射 map_from_entries([{k: 5, v: 'val1'}, {k: 3, v: 'val2'}]);
map_keys 以列表形式返回映射的键 map_keys(map(['key'], ['val']))
map_values 以列表形式返回映射的值 map_values(map(['key'], ['val']))
md5 以字符串形式返回值的 MD5 哈希值 md5('123')
md5_number 以 INT128 形式返回值的 MD5 哈希值 md5_number('123')
md5_number_lower 以 INT128 形式返回值的 MD5 哈希值 md5_number_lower('123')
md5_number_upper 以 INT128 形式返回值的 MD5 哈希值 md5_number_upper('123')
microsecond 从日期或时间戳中提取微秒部分 microsecond(timestamp '2021-08-03 11:59:44.123456')
millennium 从日期或时间戳中提取千年部分 millennium(timestamp '2021-08-03 11:59:44.123456')
millisecond 从日期或时间戳中提取毫秒部分 millisecond(timestamp '2021-08-03 11:59:44.123456')
minute 从日期或时间戳中提取分钟部分 minute(timestamp '2021-08-03 11:59:44.123456')
mismatches 2 个长度相等的字符串中不同字符的位置数。区分大小写 mismatches('big','dog')
month 从日期或时间戳中提取月份部分 month(timestamp '2021-08-03 11:59:44.123456')
monthname 月份的(英文)名称 monthname(TIMESTAMP '1992-09-20')
nextafter 返回 x 之后 y 方向的下一个浮点值 nextafter(1::float, 2::float)
nextval 返回序列的下一个值 nextval('my_sequence_name')
nfc_normalize 返回字符串的 NFC 归一化 nfc_normalize('café cafe\u0301') = 'café café'
not_ilike_escape 同 string NOT ILIKE pattern ESCAPE escape_character not_ilike_escape('A%c', 'a$%c', ' ) = false
not_like_escape 同 string NOT LIKE pattern ESCAPE escape_character not_like_escape('a%c', 'a$%c', ' ) = false
now 返回当前时间戳 now()
octet_length 返回 blob 型字符串字节长度 octet_length('\x4a\x7f'::BLOB) = 2
ord 返回字符串第一个字符的 unicode 代码点 ord('ü')
pi 返回 pi 的值 pi()
position 返回 haystack 中第一次出现的针的位置,从 1 开始计数。如果没有找到匹配则返回 0 position('test test','es')
pow 计算 x 的 y 次方 pow(2, 3)
power 计算 x 的 y 次方 power(2, 3)
prefix 判断字符串是否以指定前缀开头 prefix('hello world', 'hello') = true
printf 使用 printf 语法格式化字符串 printf('Benchmark "%s" took %d seconds', 'CSV', 42)
quarter 从日期或时间戳中提取季度部分 quarter(timestamp '2021-08-03 11:59:44.123456')
radians 将度数转换为弧度 radians(90)
random 返回 0 到 1 之间的随机数 random()
range 创建开始和停止之间的值列表 - 停止参数是独占的 range(2, 5, 3)
regexp_extract 正则表达式提取首次出现的 regexp_extract('hello_world', '([a-z ]+)_?', 1) = 'hello'
regexp_extract_all 正则表达式提取全部 regexp_extract_all('hello_world', '([a-z ]+)_?', 1) = ['hello', 'world']
regexp_full_match 正则表达式是否完全匹配 regexp_full_match('anabanana', '(an)*') = false
regexp_matches 正则表达式是否有部分匹配 regexp_matches('anabanana', '(an)*') = true
regexp_replace 正则表达式替换第一处匹配, 添加 'g' 修改全部 regexp_replace('hello', '[lo]', '-') = 'he-lo'
regexp_split_to_array 沿着正则表达式分割字符串 regexp_split_to_array('hello␣world; 42', ';?␣')
repeat 重复字符串计数次数 repeat('A', 5)
replace 将字符串中出现的任何源替换为目标 replace('hello', 'l', '-')
reverse 反转字符串 reverse('hello')
right 提取最右边的计数字符 right('Hello🌎', 3)
right_grapheme 提取最右边的计数字素簇 right_grapheme('🤦🏼‍♂️🤦🏽‍♀️', 1)
round 将 x 四舍五入到小数点后 s 位 round(42.4332, 2)
row 创建包含参数值的未命名 STRUCT。 row(4, 'hello')
rpad 用从右侧开始的字符填充字符串,直到有 count 个字符 rpad('hello', 10, '<')
rtrim 删除字符串右侧出现的任何字符 rtrim('>>>>test<<', '><')
second 从日期或时间戳中提取秒部分 second(timestamp '2021-08-03 11:59:44.123456')
set_bit 将位串中的第 n 位设置为新值;第一个(最左边)位的索引为 0。返回一个新的位串 set_bit('0110010'::BIT, 2, 0)
setseed 设置用于随机函数的种子 setseed(0.42)
sha256 返回值的 SHA256 哈希值 sha256('hello')
sign 返回 x 的符号为 -1、0 或 1 sign(-349)
signbit 返回符号位是否已设置 signbit(-0.0)
sin 计算 x 的正弦 sin(90)
split 沿分隔符分割字符串 split('hello-world', '-')
sqrt 返回 x 的平方根 sqrt(4)
starts_with 如果字符串以 search_string 开头,则返回 true starts_with('abc','a')
stats 返回一个字符串,其中包含有关表达式的统计信息。表达式可以是列、常量或 SQL 表达式 stats(5)
str_split 沿分隔符分割字符串 str_split('hello-world', '-')
str_split_regex 沿着正则表达式分割字符串 str_split_regex('hello␣world; 42', ';?␣')
strftime 根据格式字符串将时间戳转换为字符串 strftime(timestamp '1992-01-01 20:38:40', '%a, %-d %B %Y - %I:%M:%S %p')
string_split 沿分隔符分割字符串 string_split('hello-world', '-')
string_split_regex 沿着正则表达式分割字符串 string_split_regex('hello␣world; 42', ';?␣')
string_to_array 沿分隔符分割字符串 string_to_array('hello-world', '-')
strip_accents 返回字符串的去重音符 strip_accents('naïveté') = 'naivete'
strlen 返回字符串字节长度 strlen('🌎') = 4
strpos 返回 haystack 中第一次出现的针的位置,从 1 开始计数。如果没有找到匹配则返回 0 strpos('test test','es')
strptime 如果指定了 %Z,则根据格式字符串将字符串转换为带时区的时间戳 strptime('Wed, 1 January 1992 - 08:38:40 PST', '%a, %-d %B %Y - %H:%M:%S %Z')
struct_extract 提取结构体中的元素, 可缩写成 struct.key struct_extract({i: 7, s: 'string'}, 's') = 'string'
struct_insert 使用参数值将字段/值添加到现有 STRUCT。条目名称将是绑定变量名称 struct_insert({'a': 1}, b := 2)
struct_pack 创建包含参数值的 STRUCT。条目名称将是绑定变量名称 struct_pack(i := 4, s := 'string')
substr 返回开始于 start, 长度为 length 的子串, substring 的别名 substr('hello world', 1, 5) = 'hello'
substring 返回开始于 start, 长度为 length 的子串 substring('hello world', 1, 5) = 'hello'
substring_grapheme 返回开始于 start, 长度为 length 的子串 substring_grapheme('🪐👽🌎🚀', 2, 2) = '👽🌎'
suffix 判断字符串是否以指定后缀结尾 suffix('hello world', 'world') = true
tan 计算 x 的 tan tan(90)
time_bucket 按指定的时间间隔bucket_width截断TIMESTAMPTZ。存储桶相对于原点 TIMESTAMPTZ 对齐。对于不包含月份或年份间隔的存储桶,源默认为 2000-01-03 00:00:00+00;对于月份和年份间隔,源默认为 2000-01-01 00:00:00+00 time_bucket(INTERVAL '2 weeks', TIMESTAMP '1992-04-20 15:26:00-07', TIMESTAMP '1992-04-01 00:00:00-07')
timezone 从日期或时间戳中提取时区部分 timezone(timestamp '2021-08-03 11:59:44.123456')
timezone_hour 从日期或时间戳中提取 timezone_hour 部分 timezone_hour(timestamp '2021-08-03 11:59:44.123456')
timezone_minute 从日期或时间戳中提取 timezone_month 部分 timezone_minute(timestamp '2021-08-03 11:59:44.123456')
to_base 将值转换为给定基数的字符串,可以选择用前导零填充到最小长度 to_base(42, 16)
to_base64 将 blob 转换为 base64 编码的字符串 to_base64('A'::blob)
to_binary 将值转换为二进制表示形式 to_binary(42)
to_centuries 构造一个世纪间隔 to_centuries(5)
to_days 构造一天间隔 to_days(5)
to_decades 构建十年间隔 to_decades(5)
to_hex 将值转换为十六进制表示形式 to_hex(42)
to_hours 构建一个小时间隔 to_hours(5)
to_microseconds 构造微秒间隔 to_microseconds(5)
to_millennia 构建千年间隔 to_millennia(1)
to_milliseconds 构造毫秒间隔 to_milliseconds(5.5)
to_minutes 构建分钟间隔 to_minutes(5)
to_months 构造一个月间隔 to_months(5)
to_seconds 构造第二个区间 to_seconds(5.5)
to_timestamp 将纪元以来的秒数转换为带时区的时间戳 to_timestamp(1284352323.5)
to_weeks 构造一周间隔 to_weeks(5)
to_years 构建年间隔 to_years(5)
today 返回当前日期 today()
transaction_timestamp 返回当前时间戳 transaction_timestamp()
translate 将字符串中与 from 集中的字符匹配的每个字符替换为 to 集中的相应字符。如果 from 比 to 长,则删除 from 中出现的多余字符 translate('12345', '143', 'ax')
trim 删除字符串两侧出现的任何字符 trim('>>>>test<<', '><')
trunc 截断数字 trunc(17.4)
try_strptime 使用格式字符串将字符串转换为时间戳(如果指定了 %Z,则为带时区的时间戳)。失败时返回 NULL try_strptime('Wed, 1 January 1992 - 08:38:40 PM', '%a, %-d %B %Y - %I:%M:%S %p')
txid_current 返回当前事务的 ID(BIGINT)。如果当前事务还没有,它将分配一个新的 txid_current()
typeof 返回表达式结果的数据类型名称 typeof('abc')
ucase 返回大写字符串, upper 的别名 ucase('hello world') = 'HELLO WORLD'
unbin 将值从二进制表示形式转换为 blob unbin('0110')
unhex 将值从十六进制表示形式转换为 blob unhex('2A')
unicode 返回字符串第一个字符的 unicode 代码点 unicode('ü')
union_extract 从联合中提取具有命名标签的值。如果当前未选择标签,则为 NULL union_extract(s, 'k')
union_tag 以 ENUM 形式检索联合体当前选定的标签 union_tag(union_value(k := 'foo'))
union_value 创建包含参数值的单个成员 UNION。值的标签将是绑定的变量名称 union_value(k := 'hello')
upper 返回大写字符串 upper('hello world') = 'HELLO WORLD'
uuid 返回类似于以下内容的随机 UUID:eeccb8c5-9943-b2bb-bb5e-222f4e14b687 uuid()
vector_type 返回给定列的 VectorType vector_type(col)
version 返回当前活动的 BigDB 版本,格式如下:v0.3.2 version()
week 从日期或时间戳中提取周部分 week(timestamp '2021-08-03 11:59:44.123456')
weekday 从日期或时间戳中提取工作日部分 weekday(timestamp '2021-08-03 11:59:44.123456')
weekofyear 从日期或时间戳中提取 weekofyear 部分 weekofyear(timestamp '2021-08-03 11:59:44.123456')
xor 按位异或 xor(17, 5)
year 从日期或时间戳中提取年份部分 year(timestamp '2021-08-03 11:59:44.123456')
yearweek 从日期或时间戳中提取年周部分 yearweek(timestamp '2021-08-03 11:59:44.123456')

聚合函数

函数名称 描述 例子
any_value 返回任意一个值 (实际实现为第一个非空元素) any_value(open)
approx_count_distinct 使用 HyperLogLog 计算不同元素的近似计数。 approx_count_distinct(A)
approx_quantile 使用 T-Digest 计算近似分位数。 approx_quantile(A,0.5)
arbitrary 返回任意一个值 (实际实现为第一个元素) arbitrary(open)
arg_max 查找具有最大值的行。计算该行的 arg 表达式。 arg_max(A,B)
arg_min 查找具有最小 val 的行。计算该行的 arg 表达式。 arg_min(A,B)
argmax 查找具有最大值的行。计算该行的 arg 表达式。 argmax(A,B)
argmin 查找具有最小 val 的行。计算该行的 arg 表达式。 argmin(A,B)
array_agg 返回包含列的所有值的 LIST。 array_agg(A)
avg 计算 x 中所有元组的平均值。 公式: SUM(x) / COUNT(*) avg(A)
bit_and 返回给定表达式中所有位的按位与。 bit_and(A)
bit_or 返回给定表达式中所有位的按位或。 bit_or(A)
bit_xor 返回给定表达式中所有位的按位异或。 bit_xor(A)
bitstring_agg 返回一个位串,其中为每个不同值设置了位。 bitstring_agg(A)
bool_and 如果每个输入值均为 TRUE,则返回 TRUE,否则返回 FALSE。 bool_and(A)
bool_or 如果任何输入值为 TRUE,则返回 TRUE,否则返回 FALSE。 bool_or(A)
corr 返回组中非空对的相关系数。 公式: COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y)) corr(B, A)
count 返回非空值的个数 count(open)
count_star 返回总行数 count_star(*)
covar_pop 返回输入值的总体协方差。 公式: (SUM(xy) - SUM(x) * SUM(y) / COUNT()) / COUNT(*) covar_pop(B, A)
covar_samp 返回组中非空对的样本协方差。 公式: (SUM(xy) - SUM(x) * SUM(y) / COUNT()) / (COUNT(*) - 1) covar_samp(B, A)
decay_linear 当前窗口下加权平均,当前行值为 [1/sum, 2/sum, …, d/sum] * X, sum=1+2+…+d=(d+1)*d/2, X 为 arg 在当前 window 的列向量。窗口大小 d 由 over() 里面的 rows 指定 decay_linear(open)
entropy 返回 count 个输入值的 log-2 熵。 entropy(A)
favg 使用更准确的浮点求和 (Kahan Sum) 计算平均值 favg(A)
first 返回第一个值 first(open)
fsum 使用更精确的浮点求和 (Kahan Sum) 计算总和。 fsum(A)
group_concat 使用可选分隔符连接列字符串值。 group_concat(A, '-')
histogram 返回包含字段“bucket”和“count”的 STRUCT 列表。 histogram(A)
kahan_sum 使用更精确的浮点求和 (Kahan Sum) 计算总和。 kahan_sum(A)
kurtosis 返回所有输入值的超额峰度(Fisher 定义),并根据样本大小进行偏差校正 kurtosis(A)
last 返回最后一个值 last(open)
list 返回包含列的所有值的 LIST。 list(A)
mad 返回 x 内值的中值绝对偏差。 NULL 值将被忽略。时间类型返回正 INTERVAL。 公式: MEDIAN(ABS(x-MEDIAN(x))) mad(A)
max 返回 arg 中存在的最大值。 max(A)
max_by 查找具有最大值的行。计算该行的 arg 表达式。 max_by(A,B)
mean 计算 x 中所有元组的平均值。 公式: SUM(x) / COUNT(*) mean(A)
median 返回集合的中间值。 NULL 值将被忽略。对于偶数值计数,定量值将被平均,序数值将返回较低的值。 公式: QUANTILE_CONT(x, 0.5) median(A)
min 返回 arg 中存在的最小值。 min(A)
min_by 查找具有最小 val 的行。计算该行的 arg 表达式。 min_by(A,B)
mode 返回 x 内值的最频繁值。 NULL 值将被忽略。 mode(A)
nanavg 计算 x 中所有元组的平均值,忽略 NaN。 nanavg(A)
nanmean 计算 x 中所有元组的平均值,忽略 NaN。 nanmean(A)
nanstd 返回样本标准差,忽略 NaN。 nanstd(A)
nanstd_pop 返回总体标准差,忽略 NaN。 nanstd_pop(A)
nanstd_samp 返回样本标准差,忽略 NaN。 nanstd_samp(A)
nanvar 返回所有输入值的样本方差,忽略 NaN。 nanvar(A)
nanvar_pop 返回总体方差,忽略 NaN。 nanvar_pop(A)
nanvar_samp 返回所有输入值的样本方差,忽略 NaN。 nanvar_samp(A)
product 计算 arg 中所有元组的乘积。 product(A)
quantile 返回 0 到 1 之间的精确分位数。如果 pos 是 FLOAT 的列表,则结果是相应精确分位数的列表。 quantile(A, 0.5)
quantile_cont 返回 0 和 1 之间的插值分位数。如果 pos 是 FLOAT 的列表,则结果是相应插值分位数的列表。 quantile_cont(A, 0.5)
quantile_disc 返回 0 到 1 之间的精确分位数。如果 pos 是 FLOAT 的列表,则结果是相应精确分位数的列表。 quantile_disc(A, 0.5)
regr_avgx 返回组中非空对的自变量的平均值,其中 x 是自变量,y 是因变量。 regr_avgx(B, A)
regr_avgy 返回组中非空对的因变量的平均值,其中 x 是自变量,y 是因变量。 regr_avgy(B, A)
regr_count 返回组中非空数字对的数量。 公式: (SUM(xy) - SUM(x) * SUM(y) / COUNT()) / COUNT(*) regr_count(B, A)
regr_intercept 返回组中非空对的单变量线性回归线的截距。 公式: AVG(y)-REGR_SLOPE(y,x)*AVG(x) regr_intercept(B, A)
regr_r2 返回组中非空对的决定系数。 regr_r2(B, A)
regr_slope 返回组中非空对的线性回归线的斜率。 公式: COVAR_POP(x,y) / VAR_POP(x) regr_slope(B, A)
regr_sxx 公式: REGR_COUNT(y, x) * VAR_POP(x) regr_sxx(B, A)
regr_sxy 返回输入值的总体协方差 公式: REGR_COUNT(y, x) * COVAR_POP(y, x) regr_sxy(B, A)
regr_syy 公式: REGR_COUNT(y, x) * VAR_POP(y) regr_syy(B, A)
reservoir_quantile 使用水库采样给出近似分位数,样本大小是可选的,并使用 8192 作为默认大小。 reservoir_quantile(A,0.5,1024)
sem 返回平均值的标准误差 sem(A)
skewness 返回所有输入值的偏度。 skewness(A)
stddev 返回样本标准差 公式: sqrt(var_samp(x)) stddev(A)
stddev_pop 返回总体标准差。 公式: sqrt(var_pop(x)) stddev_pop(A)
stddev_samp 返回样本标准差 公式: sqrt(var_samp(x)) stddev_samp(A)
string_agg 使用可选分隔符连接列字符串值。 string_agg(A, '-')
sum 计算 arg 中所有元组的总和值。 sum(A)
sum_no_overflow 计算 arg 中所有元组的总和值,不进行溢出检查。 sum_no_overflow(A)
sumkahan 使用更精确的浮点求和 (Kahan Sum) 计算总和。 sumkahan(A)
var_pop 返回总体方差。 var_pop(A)
var_samp 返回所有输入值的样本方差。 公式: (SUM(x^2) - SUM(x)^2 / COUNT(x)) / (COUNT(x) - 1) var_samp(A)
variance 返回所有输入值的样本方差。 公式: (SUM(x^2) - SUM(x)^2 / COUNT(x)) / (COUNT(x) - 1) variance(A)

窗口函数

函数名称 描述 例子
row_number 分区中当前行的编号,从 1 开始计数 row_number()
rank 当前行的有间隙的排名;与row_number相同 rank()
percent_rank 当前行的相对排名:rank() / total non-NaN partition rows percent_rank()
rank_ext 当前行的滚动窗口排名,窗口大小由over()里面的rows指定 rank_ext(open,’avg’,true)
pct_rank_ext rank_ext()/window pct_rank_ext(open,’min’,false)
rolling_rank rank_ext() rolling_rank(open,’max’,true)
dense_rank 当前行的排名,没有间隙;此函数对相等的组进行计数 dense_rank()
rank_dense dense_rank的别名 rank_dense()
cume_dist 累积分布:(当前行之前或对等的分区行数)/分区总行数 cume_dist()
ntile 返回一个介于 1 到num_buckets参数值之间的整数,尽可能平均地划分分区 ntile(4)
lag 返回在分区中当前行之前的offset行处计算expr的结果;如果没有这样的行,则返回default(必须与expr的类型相同)。offsetdefault两者均针对当前行计算。offset的默认值为1default的默认值为null lag(column, 3, 0)
lead 返回在分区中当前行之后的offset行处计算expr的结果;如果没有这样的行,则返回default(必须与expr的类型相同)。offsetdefault两者均针对当前行计算。offset的默认值为1default的默认值为null lead(column, 3, 0)
first_value 窗口第一行计算expr的结果 first_value(column)
last_value 窗口最后一行计算expr的结果 last_value(column)
nth_value 窗口在第n(从 1 开始计数)行处计算expr的结果;如果没有这样的行,则返回null nth_value(column, 2)
first first_value的别名 first(column)
last last_value的别名 last(column)
decay_linear 当前窗口下加权平均,当前行值为 [1/sum, 2/sum, …, d/sum] * X, sum=1+2+…+d=(d+1)*d/2, X 为 arg 在当前 window 的列向量。窗口大小 d 由 over() 里面的 rows 指定 decay_linear(open)
imax 当前窗口下 arg 最大值所在的窗口索引 (从0开始) imax(open)
imin 当前窗口下 arg 最小值所在的窗口索引 (从0开始) imin(open)
cummax 计算 arg 在分区排序后的累计最大值 cummax(close)
cummin 计算 arg 在分区排序后的累计最小值 cummin(close)
cumsum 计算 arg 在分区排序后的累计和 cumsum(turn)
cumprod 计算 arg 在分区排序后的累计乘积 cumprod(turn)
sum_greatest_k 分区排序后滚动窗口内 arg 最大 k 个数对应的 val 的累加和 sum_greatest_k(amount/deal_number, change_ratio, 20, 10)
sum_least_k 分区排序后滚动窗口内 arg 最小 k 个数对应的 val 的累加和 sum_least_k(amount/deal_number, change_ratio, 20, 10)
sum_gl_k_delta 分区排序后滚动窗口内 arg 最大 k 个数对应的 val 的累加和与 arg 最小 k 个数对应的 val 的累加和的差值 sum_gl_k_delta(amount/deal_number, change_ratio, 20, 10)
sum_first_k 分区排序后 arg 的前 k 个值的累加和,分区内结果相同 sum_first_k(close, 5)
avg_first_k 分区排序后 arg 的前 k 个值的平均值,分区内结果相同 avg_first_k(close, 5)

TA-Lib 函数

函数名称 描述 例子
ta_2crows 两只乌鸦 ta_2crows(open, high, low, close)
ta_3black_crows 三只乌鸦 ta_3black_crows(open, high, low, close)
ta_3red_soldiers 红三兵 ta_3red_soldiers(open, high, low, close)
ta_ad Chaikin A/D (累积分布) 线 ta_ad(high, low, close, volume)
ta_adx 窗口大小周期的平均趋向指数 ta_adx(high, low, close, 5)
ta_adxr 窗口大小周期的平均趋向评指数 ta_adxr(high, low, close, 5)
ta_aroon Aroon 指标: 返回有两列的 list: aroondown, aroonup ta_aroon(high, low, 14)
ta_aroonosc 窗口大小周期的 AROONOSC 指标 ta_aroonosc(high, low, 14)
ta_atr 窗口大小周期的均幅指标 ta_atr(high, low, close, 5)
ta_bbands 布林带: 返回有三列的list: upper_band, middle_band, lower_band ta_bbands(close, 5, 2.0, 2.0, 0)
ta_beta 窗口大小周期的线性回归斜率 ta_beta(high, low, 5)
ta_cci 窗口大小周期的顺势指标 ta_cci(high, low, close, 5)
ta_consecutive_rise_count 计算 arg 最近一次连续上涨的次数: 若 arg 上涨,则连涨次数 +1; 否则为 0 ta_consecutive_rise_count(close)
ta_dark_cloud_cover 乌云盖顶 ta_dark_cloud_cover(open, high, low, close, 0.5)
ta_dema 窗口大小周期的双指数移动平均 ta_dema(open, 5)
ta_ema 窗口大小周期的指数移动平均值 ta_ema(open, 5)
ta_evening_star 黄昏之星 ta_evening_star(open, high, low, close, 0.3)
ta_hammer ta_hammer(open, high, low, close)
ta_inverted_hammer 倒锤 ta_inverted_hammer(open, high, low, close)
ta_kama 窗口大小周期的 Kaufman 自适应移动平均 ta_kama(open, 5)
ta_macd 移动平均收敛发散: 返回有三列的list: macd_dif (指数平滑移动平均线), macd_dea (DIF的 N 日 (默认9日) 指数平滑移动平均线), macd_hist (2 * (DIF-DEA)) ta_macd(close, 12, 26, 9)
ta_mfi 窗口大小周期的货币流量指数 ta_mfi(high, low, close, volume, 5)
ta_mom 窗口大小周期的动量指标 ta_mom(open, 5)
ta_morning_star 早晨之星 ta_morning_star(open, high, low, close, 0.3)
ta_obv 能量潮 ta_obv(open, volume)
ta_roc 窗口大小周期的变动率指标 ta_roc(open, 5)
ta_rsi 窗口大小周期的相对强弱指标 ta_rsi(open, 5)
ta_sar 抛物线转向 (SAR) 指标 ta_sar(high, low, 0.02, 0.2)
ta_shooting_star 流星线 ta_shooting_star(open, high, low, close)
ta_sma 窗口大小周期的简单移动平均值 ta_sma(open, 5)
ta_stoch 随机指标: 返回有两列的 list: K, D ta_stoch(high, low, close, 9, 3, 0, 3, 0)
ta_sum 窗口大小周期的累加和 ta_sum(open, 5)
ta_tema 窗口大小周期的三重指数移动平均 ta_tema(open, 5)
ta_trima 窗口大小周期的三角移动平均 ta_trima(open, 5)
ta_trix 窗口大小周期的三重指数平滑平均线 ta_trix(open, 5)
ta_willr 窗口大小周期的威廉指标 ta_willr(high, low, close, 5)
ta_wma 窗口大小周期的加权移动平均值 ta_wma(open, 5)

macro 函数

函数名称 描述 例子
all_cbins 对数据做基于全局分位数的离散化分组 all_cbins(open, 10)
all_quantile_cont x 在 pos 处的插值分位数 all_quantile_cont(x, 0.5)
all_quantile_disc x 在 pos 处的最近的确切分位数 all_quantile_disc(x, 0.5)
all_wbins 将 arg 按大小值均分成 bins 个桶并判断 arg 每行属于哪个桶(从 0 开始) all_wbins(open, 10)
array_append 把元素 el 添加到数组 arr 的末尾, list_append 的别名 array_append([1, 2], 3)
array_has_all 返回数组 l1 是否包含数组 l2 的所有元素, list_has_all 的别名 array_has_all([1, 2, 3], [2, 3, 4])
array_has_any 返回数组 l1 和 l2 是否有交集, list_has_any 的别名 array_has_any([1, 2, 3], [2, 3, 4])
array_intersect 返回数组 l1 和 l2 的交集, list_intersect 的别名 array_intersect([1, 2, 3], [2, 3, 4])
array_pop_back 删除数组 arr 的最后一个元素 array_pop_back([1, 2, 3])
array_pop_front 删除数组 arr 的第一个元素 array_pop_front([1, 2, 3])
array_prepend 把元素 el 添加到数组 arr 的开头, list_prepend 的别名 array_prepend(3, [1, 2])
array_push_back 把元素 e 添加到数组 arr 的末尾 array_push_back([1, 2], 3)
array_push_front 把元素 e 添加到数组 arr 的开头 array_push_front([1, 2], 3)
array_reverse 返回数组 l 的逆序, list_reverse 的别名 array_reverse([1, 2, 3])
array_to_string 把数组 arr 的元素用分隔符 sep 连接成字符串 array_to_string([1, 2, 3], ',')
c_avg 在时间截面上,求 x 的均值 c_avg(close)
c_cbins 在日期截面上做基于分位数的离散化。将数据离散化为尽可能相等大小的存储桶。 c_cbins(open, 4)
c_group_avg 在时间截面上按 key 分组后 arg 的均值 c_group_avg(sw2021_level2, close)
c_group_pct_rank 在时间截面上按 key 分组后 arg 的百分数排名 c_group_pct_rank(sw2021_level2, close)
c_group_quantile_cont 在时间截面上按 key 分组后 x 在 pos 处的插值分位数 c_group_quantile_cont(sw2021_level2, close, 0.3)
c_group_quantile_disc 在时间截面上按 key 分组后 x 在 pos 处的确切分位数 c_group_quantile_disc(sw2021_level2, close, 0.3)
c_group_std 在时间截面上按 key 分组后 arg 的(样本)标准差 c_group_std(sw2021_level2, close)
c_group_sum 在时间截面上按 key 分组后 arg 的和 c_group_sum(sw2021_level2, close)
c_indneutralize 在时间截面上计算行业中性化值 c_indneutralize(close, industry_level1_code)
c_mad 在时间截面上,求 x 的绝对中位差 c_mad(close)
c_median 在时间截面上,求 x 的中位数 c_median(close)
c_neutralize 在时间截面上计算行业市值中性化值 c_neutralize(close, industry_level1_code, market_cap)
c_normalize 在时间截面上,z-score标准化 c_normalize(close)
c_ols2d_resid 在时间截面上计算 y 与 [x1, x2] 的二元线性回归残差 c_ols2d_resid(y, x1, x2)
c_ols3d_resid 在时间截面上计算 y 与 [x1, x2, x3] 的三元线性回归残差 c_ols3d_resid(y, x1, x2, x3)
c_pct_rank 在时间截面上 arg 的百分数排名 c_pct_rank(close, ascending:=true)
c_quantile_cont 时间截面上,求 x 在 pos 处的插值分位数 c_quantile_cont(x, 0.5)
c_quantile_disc 时间截面上,求 x 在 pos 处的最近的确切分位数 c_quantile_disc(x, 0.5)
c_rank 在时间截面上 arg 的排名 c_rank(close, ascending:=true)
c_regr_residual 在时间截面上计算 y 与 x 的线性回归残差 c_regr_residual(y, x)
c_scale 时间截面上将 x 缩放使得缩放后的 x 的绝对值之和为 a: x -> x / sum(|x|) * a c_scale(x, 10)
c_std 在时间截面上,求 x 的(样本)标准差 c_std(close)
c_sum 在时间截面上,求 x 的和 c_sum(close)
c_var 在时间截面上,求 x 的(样本)方差 c_var(close)
c_wbins 在时间截面上,将 arg 按大小值均分成 bins 个桶并判断 arg 每行属于哪个桶(从 0 开始) c_wbins(open, 10)
clip 若 a < a_min, 则返回 a_min; 若 a > a_max, 则返回 a_max; 否则返回 a clip(open, 1, 99)
constant 返回自己本身 constant(13)
count_if 返回条件 cond 中为真的元素的个数 count_if(open > 50)
cume_dist 按照参数 ob 排序后的累积分布 cume_dist(open, ascending:=true)
cume_dist_by 依据 pb 分组后并按照 ob 做组内排序后的累积分布 cume_dist_by(date, open, ascending:=true)
cut_outliers 在时间截面上去极值;m为倍数,默认为3 cut_outliers(s)
date_add 返回 date 加上 interval 后的日期 date_add(DATE '2023-01-01', INTERVAL 5 MONTH)
dense_rank 按照参数 ob 排序后的无间隙排名 dense_rank(open, ascending:=true)
dense_rank_by 依据 pb 分组后并按照 ob 做组内排序后的无间隙排名 dense_rank_by(date, open, ascending:=true)
fdiv 返回 x/y 的整数部分 fdiv(5, 2)
fmod 返回 x/y 的余数 fmod(5, 2)
generate_subscripts 返回数组 arr 的第 dim 维的下标 generate_subscripts([1, 2, 3], 1)
geomean 返回 x 的几何平均数 geomean(open)
geometric_mean 返回 x 的几何平均数, geomean 的别名 geometric_mean(open)
list_add_head_tail 添加头元素和尾元素到列表 list 中 list_add_head_tail(2017, [2018, 2019, 2020], 2021)
list_any_value 返回列表 l 的任意一个值 list_any_value([1,2,3])
list_append 把元素 e 添加到列表 l 的末尾 list_append([1, 2], 3)
list_approx_count_distinct 返回列表 l 的近似不同值的个数 list_approx_count_distinct([1,2,3])
list_avg 返回列表 l 的平均值 list_avg([1,2,3])
list_bit_and 返回列表 l 的与值 list_bit_and([1,2,3])
list_bit_or 返回列表 l 的或值 list_bit_or([1,2,3])
list_bit_xor 返回列表 l 的异或值 list_bit_xor([1,2,3])
list_bool_and 返回列表 l 的与值 list_bool_and([true,false])
list_bool_or 返回列表 l 的或值 list_bool_or([true,false])
list_count 返回列表 l 的非空个数 list_count([1,2,3,null])
list_entropy 返回列表 l 的熵 list_entropy([1,2,3])
list_first 返回列表 l 的第一个值 list_first([1,2,3])
list_has_all 返回列表 l1 是否包含列表 l2 的所有元素 list_has_all([1, 2, 3], [2, 3, 4])
list_has_any 返回列表 l1 和 l2 是否有交集 list_has_any([1, 2, 3], [2, 3, 4])
list_histogram 返回列表 l 的直方图 list_histogram([1,2,3])
list_intersect 返回列表 l1 和 l2 的交集 list_intersect([1, 2, 3], [2, 3, 4])
list_kurtosis 返回列表 l 的峰度 list_kurtosis([1,2,3])
list_last 返回列表 l 的最后一个值 list_last([1,2,3])
list_mad 返回列表 l 的绝对中位差 list_mad([1,2,3])
list_max 返回列表 l 的最大值 list_max([1,2,3])
list_median 返回列表 l 的中位数 list_median([1,2,3])
list_min 返回列表 l 的最小值 list_min([1,2,3])
list_mode 返回列表 l 的众数 list_mode([1,2,3])
list_prepend 把元素 e 添加到列表 l 的开头 list_prepend(3, [1, 2])
list_product 返回列表 l 的乘积 list_product([1,2,3])
list_reverse 返回列表 l 的逆序 list_reverse([1, 2, 3])
list_sem 返回列表 l 的标准误差 list_sem([1,2,3])
list_skewness 返回列表 l 的偏度 list_skewness([1,2,3])
list_stddev_pop 返回列表 l 的总体标准差 list_stddev_pop([1,2,3])
list_stddev_samp 返回列表 l 的样本标准差 list_stddev_samp([1,2,3])
list_string_agg 返回列表 l 的字符串连接 list_string_agg(['a','b','c'])
list_sum 返回列表 l 的和 list_sum([1,2,3])
list_var_pop 返回列表 l 的总体方差 list_var_pop([1,2,3])
list_var_samp 返回列表 l 的样本方差 list_var_samp([1,2,3])
m_approx_count_distinct 时间序列上 x 在该窗口内的由 HyperLogLog 得出的不同元素的近似计数 m_approx_count_distinct(x, 5)
m_approx_quantile 时间序列上 x 在该窗口内的由 T-Digest 得出的近似分位数 m_approx_quantile(x, 0.5, 5)
m_arg_max 时间序列上 val 在该窗口内取最大值时的 arg 值 m_arg_max(arg, val, 5)
m_arg_min 时间序列上 val 在该窗口内取最小值时的 arg 值 m_arg_min(arg, val, 5)
m_avg 时间序列上 arg 在该窗口内的平均值 m_avg(arg, 5)
m_bit_and 时间序列上 arg 在该窗口内的按位与 m_bit_and(arg, 5)
m_bit_or 时间序列上 arg 在该窗口内的按位或 m_bit_or(arg, 5)
m_bit_xor 时间序列上 arg 在该窗口内的按位异或 m_bit_xor(arg, 5)
m_bool_and 时间序列上 arg 在该窗口内的逻辑与 m_bool_and(arg, 5)
m_bool_or 时间序列上 arg 在该窗口内的逻辑或 m_bool_or(arg, 5)
m_consecutive_rise_count 时间序列上 arg 最近一次连续上涨的次数:若 arg 上涨,则连涨次数 +1; 否则为 0 m_consecutive_rise_count(close)
m_corr 时间序列上 y 和 x 在该窗口内的相关系数 m_corr(y, x, 5)
m_count 时间序列上 arg 在该窗口内的计数 m_count(arg, 5)
m_covar_pop 时间序列上 y 和 x 在该窗口内的总体协方差 m_covar_pop(y, x, 5)
m_covar_samp 时间序列上 y 和 x 在该窗口内的样本协方差 m_covar_samp(y, x, 5)
m_cummax 时间序列上 arg 的累计最大值 m_cummax(close)
m_cummin 时间序列上 arg 的累计最小值 m_cummin(close)
m_cumprod 时间序列上 arg 的累计乘积 m_cumprod(turn)
m_cumsum 时间序列上 arg 的累计和 m_cumsum(turn)
m_decay_linear 时间序列上 arg 在该窗口内的线性衰减 m_decay_linear(arg, 5)
m_entropy 时间序列上 x 在该窗口内的熵 m_entropy(x, 5)
m_favg 时间序列上 arg 在该窗口内的 Kahan 平均值 m_favg(arg, 5)
m_first 时间序列上 arg 在该窗口内的第一个值 m_first(open, 5)
m_first_value 时间序列上 arg 在该窗口内的第一个值 m_first_value(open, 5)
m_fsum 时间序列上 arg 在该窗口内的 Kahan 和 m_fsum(arg, 5)
m_imax 时间序列上 arg 在该窗口内的最大值所在的窗口索引 m_imax(arg, 5)
m_imin 时间序列上 arg 在该窗口内的最小值所在的窗口索引 m_imin(arg, 5)
m_kurtosis 时间序列上 x 在该窗口内的峰度 m_kurtosis(x, 5)
m_lag 时间序列上 arg 向下偏移 n 行后的值 m_lag(open, 5)
m_last 时间序列上 arg 在该窗口内的最后一个值 m_last(open, 5)
m_last_value 时间序列上 arg 在该窗口内的最后一个值 m_last_value(open, 5)
m_lead 时间序列上 arg 向上偏移 n 行后的值 m_lead(open, 5)
m_mad 时间序列上 x 在该窗口内的绝对中位差 m_mad(x, 5)
m_max 时间序列上 arg 在该窗口内的最大值 m_max(arg, 5)
m_median 时间序列上 x 在该窗口内的中位数 m_median(x, 5)
m_min 时间序列上 arg 在该窗口内的最小值 m_min(arg, 5)
m_mode 时间序列上 x 在该窗口内的众数 m_mode(x, 5)
m_nanavg 时间序列上 arg 在该窗口内忽略 NaN 值后的平均值 m_nanavg(arg, 5)
m_nanstd 时间序列上 x 在该窗口内忽略 NaN 值后的(样本)标准差 m_nanstd(x, 5)
m_nanstd_pop 时间序列上 x 在该窗口内忽略 NaN 值后的总体标准差 m_nanstd_pop(x, 5)
m_nanstd_samp 时间序列上 x 在该窗口内忽略 NaN 值后的样本标准差 m_nanstd_samp(x, 5)
m_nanvar 时间序列上 x 在该窗口内忽略 NaN 值后的(样本)方差 m_nanvar(x, 5)
m_nanvar_pop 时间序列上 x 在该窗口内忽略 NaN 值后的总体方差 m_nanvar_pop(x, 5)
m_nanvar_samp 时间序列上 x 在该窗口内忽略 NaN 值后的样本方差 m_nanvar_samp(x, 5)
m_nth_value 时间序列上 arg 在该窗口内的第 n 个值 m_nth_value(open, 2, 5)
m_ols2d_intercept 时间序列上 y 与 [x1, x2] 在该窗口内的二元线性回归截距(常数项) m_ols2d_intercept(y, x1, x2, 5)
m_ols3d_intercept 时间序列上 y 与 [x1, x2, x3] 在该窗口内的三元线性回归截距(常数项) m_ols3d_intercept(y, x1, x2, x3, 5)
m_pct_rank 时间序列上 arg 在该窗口内的相对(百分数)排名 m_pct_rank(open, 5, method:='min', ascending:=true)
m_product 时间序列上 arg 在该窗口内的乘积 m_product(arg, 5)
m_quantile 时间序列上 x 在该窗口内 pos 处的(最近的确切)分位数 m_quantile(x, 0.5, 5)
m_quantile_cont 时间序列上 x 在该窗口内 pos 处的插值分位数 m_quantile_cont(x, 0.5, 5)
m_quantile_disc 时间序列上 x 在该窗口内 pos 处的最近的确切分位数 m_quantile_disc(x, 0.5, 5)
m_rank 时间序列上 arg 在该窗口内的排名 m_rank(open, 5, method:='min', ascending:=true)
m_regr_avgx 时间序列上 y 和 x 在该窗口内的非空对的自变量的平均值 m_regr_avgx(y, x, 5)
m_regr_avgy 时间序列上 y 和 x 在该窗口内的非空对的因变量的平均值 m_regr_avgy(y, x, 5)
m_regr_count 时间序列上 y 和 x 在该窗口内的非空个数 m_regr_count(y, x, 5)
m_regr_intercept 时间序列上 y 和 x 在该窗口内的截距 m_regr_intercept(y, x, 5)
m_regr_r2 时间序列上 y 和 x 在该窗口内的非空对的决定系数 m_regr_r2(y, x, 5)
m_regr_slope 时间序列上 y 和 x 在该窗口内的斜率 m_regr_slope(y, x, 5)
m_regr_sxx 时间序列上 y 和 x 在该窗口内的 Sxx: REGR_COUNT(y, x) * VAR_POP(x) m_regr_sxx(y, x, 5)
m_regr_sxy 时间序列上 y 和 x 在该窗口内的总体协方差 Sxy: REGR_COUNT(y, x) * COVAR_POP(y, x) m_regr_sxy(y, x, 5)
m_regr_syy 时间序列上 y 和 x 在该窗口内的 Syy: REGR_COUNT(y, x) * VAR_POP(y) m_regr_syy(y, x, 5)
m_reservoir_quantile 时间序列上 x 在该窗口内的由 Reservoir Sampling 得出的近似分位数 m_reservoir_quantile(x, 0.5, 1024, 5)
m_rolling_rank 时间序列上 arg 在该窗口内的排名 m_rolling_rank(open, 5, method:='min', ascending:=true)
m_shift 时间序列上 arg 向下 (n > 0) 或者向上 (n < 0) 偏移 |n| 行后的值 m_shift(open, 5), m_shift(open, -5)
m_skewness 时间序列上 x 在该窗口内的偏度 m_skewness(x, 5)
m_stddev 时间序列上 x 在该窗口内的(样本)标准差 m_stddev(x, 5)
m_stddev_pop 时间序列上 x 在该窗口内的总体标准差 m_stddev_pop(x, 5)
m_stddev_samp 时间序列上 x 在该窗口内的样本标准差 m_stddev_samp(x, 5)
m_sum 时间序列上 arg 在该窗口内的和 m_sum(arg, 5)
m_sum_gl_k_delta 时间序列上 arg 在该窗口内最大 k 个数对应的 val 的累加和与最小 k 个数对应的 val 的累加和的差值 m_sum_gl_k_delta(amount/deal_number, change_ratio, 20, 10)
m_sum_greatest_k 时间序列上 arg 在该窗口内最大 k 个数对应的 val 的累加和 m_sum_greatest_k(amount/deal_number, change_ratio, 20, 10)
m_sum_least_k 时间序列上 arg 在该窗口内最小 k 个数对应的 val 的累加和 m_sum_least_k(amount/deal_number, change_ratio, 20, 10)
m_ta_2crows 时间序列上的两只乌鸦 m_ta_2crows(open, high, low, close)
m_ta_3black_crows 时间序列上的三只乌鸦 m_ta_3black_crows(open, high, low, close)
m_ta_3red_soldiers 时间序列上的红三兵 m_ta_3red_soldiers(open, high, low, close)
m_ta_ad 时间序列上的 Chaikin A/D (累积分布) 线 m_ta_ad(high, low, close, volume)
m_ta_adx 时间序列上该窗口内的平均趋向指数 m_ta_adx(high, low, close, 5)
m_ta_adxr 时间序列上该窗口内的平均趋向指数评估 m_ta_adxr(high, low, close, 5)
m_ta_aroon 时间序列上的阿隆 (Aroon) 指标, 返回 list: [aroon_down, aroon_up] m_ta_aroon(high, low, 14)
m_ta_aroon_d 时间序列上的阿隆 (Aroon) 指标中的 aroon_down m_ta_aroon_d(high, low, 14)
m_ta_aroon_u 时间序列上的阿隆 (Aroon) 指标中的 aroon_up m_ta_aroon_u(high, low, 14)
m_ta_aroonosc 时间序列上的阿隆振荡器 (Aroon Oscillator) 指标 m_ta_aroonosc(high, low, 14)
m_ta_atr 时间序列上该窗口内的真实波动幅度均值 m_ta_atr(high, low, close, 5)
m_ta_bbands 时间序列上的布林带, 返回 list: [upper_band, middle_band, lower_band] m_ta_bbands(close, timeperiod:=5, nbdevup:=2.0, nbdevdn:=2.0, matype:=0)
m_ta_bbands_l 时间序列上的布林带中的 lower_band m_ta_bbands_l(close, timeperiod:=5, nbdevup:=2.0, nbdevdn:=2.0, matype:=0)
m_ta_bbands_m 时间序列上的布林带中的 middle_band m_ta_bbands_m(close, timeperiod:=5, nbdevup:=2.0, nbdevdn:=2.0, matype:=0)
m_ta_bbands_u 时间序列上的布林带中的 upper_band m_ta_bbands_u(close, timeperiod:=5, nbdevup:=2.0, nbdevdn:=2.0, matype:=0)
m_ta_beta 时间序列上 x, y 在该窗口内的贝塔系数 m_ta_beta(open, close, 5)
m_ta_bias (close - sma) / sma m_ta_bias(close, 3)
m_ta_cci 时间序列上该窗口内的顺势指标 m_ta_cci(high, low, close, 5)
m_ta_dark_cloud_cover 时间序列上的乌云盖顶 m_ta_dark_cloud_cover(open, high, low, close, penetration:=0.5)
m_ta_dema 时间序列上 arg 在该窗口内的双指数移动平均 m_ta_dema(open, 5)
m_ta_ema 时间序列上 arg 在该窗口内的指数均值 m_ta_ema(open, 5)
m_ta_evening_star 时间序列上的黄昏之星 m_ta_evening_star(open, high, low, close, penetration:=0.3)
m_ta_hammer 时间序列上的锤 m_ta_hammer(open, high, low, close)
m_ta_inverted_hammer 时间序列上的倒锤 m_ta_inverted_hammer(open, high, low, close)
m_ta_kama 时间序列上 arg 在该窗口内的 Kaufman 自适应移动平均 m_ta_kama(open, 5)
m_ta_kdj 时间序列上的 [K, D, J] 值 m_ta_kdj(high, low, close, fastk_period:=9, slowk_period:=3, slowd_period:=3)
m_ta_kdj_d 时间序列上 kdj 中的 D 值 m_ta_kdj_d(high, low, close, fastk_period:=9, slowk_period:=3, slowd_period:=3)
m_ta_kdj_j 时间序列上的 J 值 (= 3K - 2D) m_ta_kdj_j(high, low, close, fastk_period:=9, slowk_period:=3, slowd_period:=3)
m_ta_kdj_k 时间序列上 kdj 中的 K 值 m_ta_kdj_k(high, low, close, fastk_period:=9, slowk_period:=3, slowd_period:=3)
m_ta_macd 时间序列上的移动平均收敛/发散指标, 返回 list: [macd, macd_signal, macd_hist] m_ta_macd(close, fastperiod:=12, slowperiod:=26, signalperiod:=9)
m_ta_macd_dea 时间序列上的 macd 指标的第二列: '讯号线' (DEA), DIF 的 9 日移动平均 m_ta_macd_dea(close, fastperiod:=12, slowperiod:=26, signalperiod:=9)
m_ta_macd_dif 时间序列上的 macd 指标的第一列: '差离值' (DIF) m_ta_macd_dif(close, fastperiod:=12, slowperiod:=26, signalperiod:=9)
m_ta_macd_hist 时间序列上的 macd 指标的第三列的 2 倍: 放大后的柱状图 (HIST), (DIF - DEA) * 2 m_ta_macd_hist(close, fastperiod:=12, slowperiod:=26, signalperiod:=9)
m_ta_mfi 时间序列上该窗口内的货币流量指数 m_ta_mfi(high, low, close, volume, 5)
m_ta_mom 时间序列上 arg 在该窗口内的动量 m_ta_mom(open, 5)
m_ta_morning_star 时间序列上的早晨之星 m_ta_morning_star(open, high, low, close, penetration:=0.3)
m_ta_obv 时间序列上的能量潮 m_ta_obv(open, volume)
m_ta_roc 时间序列上 arg 在该窗口内的变化率 m_ta_roc(open, 5)
m_ta_rsi 时间序列上 arg 在该窗口内的相对强弱指数 m_ta_rsi(open, 5)
m_ta_sar 时间序列上的抛物线转向 (SAR) 指标 m_ta_sar(high, low, acceleration:=0.02, maximum:=0.2)
m_ta_shooting_star 时间序列上的流星线 m_ta_shooting_star(open, high, low, close)
m_ta_sma 时间序列上 arg 在该窗口内的简单平均值 m_ta_sma(open, 5)
m_ta_stoch 时间序列上的 K, D 值 m_ta_stoch(high, low, close, fastk_period:=9, slowk_period:=3, slowd_period:=3)
m_ta_sum 时间序列上 arg 在该窗口内的和 m_ta_sum(open, 5)
m_ta_tema 时间序列上 arg 在该窗口内的三重指数移动平均 m_ta_tema(open, 5)
m_ta_trima 时间序列上 arg 在该窗口内的三角移动平均 m_ta_trima(open, 5)
m_ta_trix 时间序列上 arg 在该窗口内的三重指数平滑平均线 m_ta_trix(open, 5)
m_ta_willr 时间序列上该窗口内的威廉指标 m_ta_willr(high, low, close, 5)
m_ta_wma 时间序列上 arg 在该窗口内的加权均值 m_ta_wma(open, 5)
m_var_pop 时间序列上 x 在该窗口内的总体方差 m_var_pop(x, 5)
m_var_samp 时间序列上 x 在该窗口内的样本方差 m_var_samp(x, 5)
m_variance 时间序列上 x 在该窗口内的(样本)方差 m_variance(x, 5)
ntile 按照 ob 排序后尽可能分成 num_buckets 等份, 并算出当前行所在的桶号(从 1 开始) ntile(4, open, ascending:=true)
ntile_by 依据 pb 分组后并按照 ob 排序后尽可能分成 num_buckets 等份, 并算出当前行所在的桶号(从 1 开始) ntile_by(4, date, open, ascending:=true)
nullif 如果 a=b, 返回 NULL; 否则返回 a nullif(open, 0)
nullifgt 若 a > b, 则返回 null; 否则返回 a nullifgt(open, 99)
nulliflt 若 a < b, 则返回 null; 否则返回 a nulliflt(open, 1)
pct_rank 按照参数 ob 排序后的相对(百分数)排名 pct_rank(open, ascending:=true)
pct_rank_by 依据 pb 分组后并按照 ob 做组内排序后的相对(百分数)排名 pct_rank_by(date, open, ascending:=true): 按 date 分组后每只股票每天在所有股票中开盘价的相对排名
rank 按照参数 ob 排序后的排名 rank(open, ascending:=true)
rank_by 依据 pb 分组后并按照 ob 做组内排序后的排名 rank_by(date, open, ascending:=true): 按 date 分组后每只股票每天在所有股票中开盘价的排名
round_even 把 x 四舍五入到 n 位小数,如果第 n+1 位及之后刚好为 5, 则取 round(x/2, n) * 2, 否则为 round(x, n)。e.g. round_even(1.2250, 2) = 1.22, round_even(1.2350, 2) = 1.24 round_even(1.2345, 2) = 1.23
roundbankers round_even(x, n) 的别名 roundbankers(x, 3)
row_number 按照 ob 排序后每行所处的行号 row_number(open, ascending:=true)
row_number_by 依据 pb 分组后并按照 ob 排序后每行在该分组内所处的行号 row_number_by(date, open, ascending:=true)
signedpower if (x > 0, 1, -1) * (|x| ^ a) signedpower(-2, 4) = -16
split_part 返回字符串 string 用分隔符 delimiter 分割后的第 position 个子串 split_part('abc,def,ghi', ',', 2)

进阶 DataSource

DataSource

dai.DataSource提供了数据表创建、读取、删除、更新等操作。

如果你主要是使用数据,可以不用关注本节类容。学习数据使用可以参考SQL入门指南

读取

使用DataSource访问数据:

import dai
import pandas as pd

ds = dai.DataSource("holidays")
df = ds.read_bdb(as_type=pd.DataFrame)

# 查看metadata
print(ds.metadata)

使用SQL语言访问数据:

# 分区的表不能读全表,如果读全表,就需要传入full_db_scan参数
dai.query("select * from 'holidays' ", full_db_scan=True).df()  

\

创建

使用 dai.DataSource.write_bdb 创建新表

  • 如果表存在,dai.DataSource.write_bdb 支持做插入操作
  • 写入数据不能为空(未来会增加空数据支持)。
import numpy as np
import pandas as pd

import dai

instruments = ["000001.SZ", "000002.SZ", "000003.SZ"]
dates = list(pd.date_range("2021-12-29", "2022-01-02"))

df = pd.DataFrame({"instrument": instruments * len(dates), "date": dates * len(instruments), "value1": np.random.random(len(dates) * len(instruments))})
# 可选:定义分区,可以用于数据访问加速
df[dai.DEFAULT_PARTITION_FIELD] = df["date"].apply(lambda x: f"{x.year}")

dai.DataSource.write_bdb(
    data=df,
    # datasource id是全局唯一的,支持小写字母、数字、下划线,以字母开始
    id="test_data_for_fun",
    # 数据插入时,根据unique_together如果有重复的,会去重.如果有分区,则需要传入索引参数indexes
    unique_together=["date", "instrument"],
    indexes=["date"],
)

\

更新

  • 更新和插入行
  • dai.DataSource.insert_bdb 支持插入行数据
  • 如果数据表指定了 unique_together,对于重复的数据会去重,保留插入的数据
import pandas as pd
import dai

ds = dai.DataSource("test_data_for_demo")

instruments = ["000001.SZ", "000002.SZ", "000003.SZ"]
dates = list(pd.date_range("2021-01-03", "2022-01-05"))

df = pd.DataFrame({"instrument": instruments * len(dates), "date": dates * len(instruments), "value": np.random.random(len(dates) * len(instruments))})
df[dai.DEFAULT_PARTITION_FIELD] = df["date"].apply(lambda x: f"{x.year}")

ds.insert_bdb(df)
  • 删除行
  • dai.DataSource.apply_bdb:支持对数据按分区做维护
  • apply_bdb 会处理所有分区。未来会增加分区裁剪优化。
import pandas as pd
import dai

ds = dai.DataSource("test_data_for_demo")

def delete_000001(df):
    df = df[df["instrument"] != "000001.SZ"]
    return df

ds.apply_bdb(delete_000001,as_type=pd.DataFrame)
  • 插入列
import pandas as pd
import dai

ds = dai.DataSource("test_data_for_demo")

def insert_value2(df):
    df["value2"] = np.random.random(len(df))
    return df

ds.apply_bdb(insert_value2,as_type=pd.DataFrame)
  • 删除列
import pandas as pd
import dai

ds = dai.DataSource("test_data_for_demo")

def remove_value2(df):
    del df["value2"]
    return df

ds.apply_bdb(remove_value2,as_type=pd.DataFrame)

\

删除表

删除DataSource。只有数据表创建者可以删除。

import dai

ds = dai.DataSource("test_data_for_demo")

ds.delete()

\

文档

进入我的数据,选择要编辑的DataSource,点击编辑,可以编写数据文档。

{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}

\

上架

数据上架后,可以在数据平台展示,供其他用户免费或者付费订阅使用:

  • 进入 我的数据,可以看到我创建的数据;
  • 点击发布,申请上架到数据平台,让其他用户订阅使用;
  • 平台审核通过后,其他用户可以订阅使用。


{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}{w:100}

\

权限

  • 数据表开发者
    • 任何用户都可以创建开发数据;
    • 数据表创建者有数据全部的权限,可以修改、删除数据,也可以为数据编写文档和上架到数据平台
  • 数据表使用者
    • 使用者可以订阅数据平台的数据;
    • 使用者可以读取订阅的数据。

\

DataSource - zero

DataSource zero是用于完全自定义的DataSource。

通过 dai.DataSource.write_zero 创建一个新的 DataSource zero,mnt = dai.DataSource(id=xxx).mount() 可以把 DataSource zero 挂载到用户 AIStudio,mnt.path 为挂载后的目录,可以像本地目录一样读写(只有创建者有权限写)。

create_zero

  • dai.DataSource.write_zero
class DataSource:
    @classmethod
    def write_zero(cls, id: str) -> "DataSource":
        """创建一个空的DataSource,并由用户自己管理

        Args:
            id (str): datasource id

        Returns:
            DataSource: datasource
        """
  • 创建DataSource zero
ds = dai.DataSource.write_zero("testzero001")
  • 在数据平台查看

创建后,可以在 数据平台 > 我的 看到这个DataSource,并完善文档,也可以申请上架到数据平台。

{w:100}

\

mount和使用

  • 自动方式
with ds.mount() as path:
    # mount到本地 path,并自动 unmount
    print(f"{path=}")
  • 手动方式
# 手动unmount

mnt = ds.mount()

print(f"{mnt.path=}")

# 用完后 unmount
mnt.unmount()

\

相关代码

https://bigquant.com/experimentshare/bb07f8326dfd430da6509622d9dc5c50

\

DataSource - pickle

主要用于存取 picklable 的数据。

创建数据

dai.DataSource.write_pickle(picklable, id="your-datasource-id")

读取数据

dai.DataSource("your-datasource-id").read_pickle()

管理数据

创建成功后,可到数据平台 → 我的数据,进行管理。

\

DataSource - text & binary

主要用于存取文本和二进制数据。

创建数据

# 创建文本数据
dai.DataSource.write_text(text, id="your-datasource-id")

# 创建二进制数据
dai.DataSource.write_binary(bytes, id="your-datasource-id")

读取数据

# 读取文本数据
dai.DataSource("your-datasource-id").read_text()

# 读取二进制数据
dai.DataSource("your-datasource-id").read_binary()

管理数据

创建成功后,可到数据平台 → 我的数据,进行管理。

\

进阶 View

\

DAI 接口文档

dai.udf.functional

  • UDFType
    • UDFType.NATIVE: python函数类型。
    • UDFType.ARROW: pyarrow表函数类型。
  • FunctionNullHandling
    • FunctionNullHandling.DEFAULT: 输入为null值,则直接返回null。
    • FunctionNullHandling.SPECIAL: 输入为null值,也继续按照函数逻辑处理。
  • ExceptionHandling
    • ExceptionHandling.DEFAULT: 遭遇异常,直接抛出异常。
    • ExceptionHandling.RETURN_NULL: 遭遇异常,返回null。

dai.udf.typing

自定义函数参数和返回值的数据类型:

  • DaiType
  • BIGINT
  • BIT
  • BLOB
  • BOOLEAN
  • DATE
  • DOUBLE
  • FLOAT
  • HUGEINT
  • INTEGER
  • INTERVAL
  • SMALLINT
  • SQLNULL
  • TIME
  • TIMESTAMP
  • TIMESTAMP_MS
  • TIMESTAMP_NS
  • TIMESTAMP_S
  • TIMESTAMP_TZ
  • TIME_TZ
  • TINYINT
  • UBIGINT
  • UINTEGER
  • USMALLINT
  • UTINYINT
  • UUID
  • VARCHAR

dai.DaiUDF

dai.DaiUDF (name, function, parameters=None, return_type=None, type=UDFType.NATIVE, null_handling=FunctionNullHandling.DEFAULT, exception_handling=ExceptionHandling.DEFAULT, side_effects=False)

DAI Python UDF类。

参数:

  • name: str,SQL语句。
  • function: Callable,函数体。
  • parameters: List[DaiPyType],参数列表,默认为空。
  • return_type: DaiPyType,返回值类型,默认为空。
  • type: UDFType,函数类型,默认为UDFType.NATIVE。
  • null_handling: FunctionNullHandling,是否处理空值,默认为FunctionNullHandling.DEFAULT。
  • exception_handling: ExceptionHandling,是否处理异常,默认为ExceptionHandling.DEFAULT。
  • side_effects: bool,是否产生副作用,默认为False。

示例:

# 添加自定义函数,带类型声明
def in_instruments_pool_with_type_annotation(instrument: str) -> bool:
    instruments_pool = [
        "000001.SZ",
        "000002.SZ",
        "600519.SH"
    ]
    return instrument in instruments_pool
# 自定义函数,不带类型声明
def in_instruments_pool(instrument):
    instruments_pool = [
        "000001.SZ",
        "000002.SZ",
        "600519.SH"
    ]
    return instrument in instruments_pool
# 如果自定义python函数有类型声明,一般可以不用指定参数类型和返回值
bar1d_my_instruments_pool_df = dai.query(
    "select * from cn_stock_bar1d where in_instruments_pool(instrument) and date> '2023-01-01'",
    udf_list=[
        dai.DaiUDF(
            name="in_instruments_pool",
            function=in_instruments_pool_with_type_annotation,
        )
    ]
).df()
# 如果使用不带类型声明的函数,则会抛出错误
# bar1d_my_instruments_pool_df = dai.query(
#     "select * from cn_stock_bar1d where in_instruments_pool(instrument) and date> '2023-01-01'",
#     udf_list=[
#         dai.DaiUDF(
#             name="in_instruments_pool",
#             function=in_instruments_pool,
#         )
#     ]
# ).df()
# InvalidInputException: Invalid Input Error: Could not infer the return type,
# please set it explicitly
# 需要指明函数的参数和返回值类型
bar1d_my_instruments_pool_df = dai.query(
    "select * from cn_stock_bar1d where in_instruments_pool(instrument) and date> '2023-01-01'",
    udf_list=[
        dai.DaiUDF(
            name="in_instruments_pool",
            function=in_instruments_pool,
            parameters=[dai.udf.typing.VARCHAR],
            return_type=dai.udf.typing.BOOLEAN
        )
    ]
).df()

dai.query

dai.query (sql, udf_list=[], full_db_scan=False, filters={}) -> QueryResult

通过SQL语句读取并处理数据。

参数:

  • sql: str,SQL语句。
  • udf_list: List[DaiUDF],python自定义函数,默认为空。
  • full_db_scan: bool,是否允许全表扫描,默认不允许,即为False。
  • filters: Dict[str, List[Any]],过滤条件,可用于优化查询速度,默认为空。

返回值: QueryResult

  • QueryResult.arrow () -> pyarrow.Table

    返回查询结果的Arrow格式。

  • QueryResult.df () -> pandas.DataFrame

    返回查询结果的DataFrame格式。

  • QueryResult.explain (explain_type="standard") -> str

    • explain_type: str,解释类型,"standard"或者"analyze",默认为"standard",analyze会做时间估算。

    返回查询结果的执行计划。

  • QueryResult.fetchall () -> list

    返回查询结果的所有行,列表格式。

  • QueryResult.pl () -> polars.DataFrame

    返回查询结果的polars DataFrame格式。

示例:

import dai
# 查询日线数据
bar1d_000001_df = dai.query(
    "select * from cn_stock_bar1d where instrument= '000001.SZ' and date >= '2023-01-01'").df()
# 过滤条件也可以写到filters参数中
bar1d_000001_df = dai.query(
    "select * from cn_stock_bar1d",
    filters={"instrument": ["000001.SZ"], "date": ["2023-01-01", "2024-01-01"]}).df()
# 如果要查询全表数据会抛错
# bar1d_full_df = dai.query("select * from cn_stock_bar1d").df()
# Error: Permission Error: Can not full scan bdb table cn_stock_bar1d,
# please add partition filters in query function or set full_db_scan=true; in sql
# 可以设置 limit 以避免读取全表数据
bar1d_limit_df = dai.query("select * from cn_stock_bar1d limit 10").df()
# 一些特殊情况的limit也会读取全表数据,比如 order,可以添加 full_db_scan 参数以查询全表数据
# bar1d_order_limit_df = dai.query("select * from cn_stock_bar1d order by close desc limit 10").df()
bar1d_order_limit_df = dai.query(
    "select * from cn_stock_bar1d order by close desc limit 10", full_db_scan=True).df()
# 或者可以选择在sql中设置 full_db_scan
bar1d_order_limit_df = dai.query("""
set full_db_scan=true;
select * from cn_stock_bar1d order by close desc limit 10""").df()
# 如果您所在在开发环境内存很小可以用set memory_limit='1GB'; 设置内存限制
# 默认会使用环境75%的内存
bar1d_full_df = dai.query("""
set full_db_scan=true;
set memory_limit='1GB';
select * from cn_stock_bar1d""").df()

dai.DataSource

dai.DataSource.read

自动判断DataSource的类型,获取相应的数据。

示例:

import dai

dai.DataSource("test_for_fun").read()
dai.DataSource("test_for_fun_pickle").read()
dai.DataSource("test_for_fun_binary").read()
dai.DataSource("test_for_fun_text").read()
dai.DataSource("test_for_fun_json").read()

dai.DataSource.exists

判断DataSource是否存在,存在返回True,不存在则返回False。

返回值: bool

示例:

import dai

dai.DataSource("test_for_fun").exists()

dai.DataSource.metadata

获取DataSource的元数据。

示例:

# 获取数据源的元数据
import dai

print(dai.DataSource("holidays").metadata)
# {'type': 'bdb', 'schema': {'date': 'timestamp[ns]', 'market_code': 'string'}, 'partitioning': None, 'indexes': None, 'unique_together': ['date', 'market_code'], 'on_duplicates': 'last', 'preserve_pandas_index': False, 'sort_by': None}

dai.DataSource.type

获取DataSource的类型。

示例:

# 获取数据源的类型
import dai

dai.DataSource("test_for_fun").type          # bdb
dai.DataSource("test_for_fun_pickle").type   # pickle
dai.DataSource("test_for_fun_binary").type   # binary
dai.DataSource("test_for_fun_text").type     # text
dai.DataSource("test_for_fun_json").type     # json

dai.DataSource.write_bdb

dai.DataSource.write_bdb (data, id=None, partitioning=None, indexes=None, excludes=None, unique_together=None, on_duplicates="last", sort_by=None, preserve_pandas_index=False, docs=None, timeout=300, extra="") -> DataSource

创建bdb新表。

参数:

  • data: Union[pa.Table, pd.DataFrame, ds.Dataset],数据。
  • id: str,数据源ID,默认为None。
  • partitioning: List[str],分区字段,默认为None。
  • indexes: List[str],索引字段,默认为None。
  • excludes: Set[str],排除字段,默认为None。
  • unique_together: List[str],唯一约束字段,默认为None。
  • on_duplicates: str,重复数据处理策略,默认为last。
  • sort_by: List[Tuple[str, str]],排序字段,默认为None。
  • preserve_pandas_index: bool,是否保留pandas索引,默认为False。
  • docs: Dict[str, Any],数据源文档,默认为None。
  • timeout: int,锁超时时间,默认为300秒。
  • extra: str,写入元数据的额外信息,默认为空字符串。

返回值: DataSource

示例:

import numpy as np
import pandas as pd

import dai

instruments = ["000001.SZ", "000002.SZ", "000003.SZ"]
dates = list(pd.date_range("2021-12-29", "2022-01-02"))

df = pd.DataFrame({"instrument": instruments * len(dates), "date": dates * len(instruments), "value1": np.random.random(len(dates) * len(instruments))})
# 可选:定义分区,可以用于数据访问加速
df[dai.DEFAULT_PARTITION_FIELD] = df["date"].dt.year

# 如果这里抛出 ArrowInvalid: Object is not allowed to access 的错误,请您修改id
dai.DataSource.write_bdb(
    data=df,
    # datasource id是全局唯一的,支持小写字母、数字、下划线,以字母开始
    id="test_for_fun",
    # 数据插入时,根据unique_together如果有重复的,会去重
    unique_together=["date", "instrument"],
    # 如果有分区,则需要传入索引参数indexes
    indexes=["date"],
)

dai.DataSource.read_bdb

dai.DataSource.read_bdb (as_type=pa.Table, partition_filter=None, columns=None) -> pa.Table | pd.DataFrame | ds.Dataset

读取bdb表。

参数:

  • as_type: Union[pa.Table, pd.DataFrame, ds.Dataset],返回类型,默认为pa.Table。
  • partition_filter: Dict[str, Union[tuple, set]],分区过滤条件,默认为None。
  • columns: List[str],返回部分列,默认为None。

返回值:

  • pa.Table,当as_type为pa.Table时返回。
  • pd.DataFrame,当as_type为pd.DataFrame时返回。
  • ds.Dataset,当as_type为ds.Dataset时返回。

示例:

import pandas as pd

import dai

df_result = dai.DataSource("test_for_fun").read_bdb(as_type=pd.DataFrame)
df_result_with_filter = dai.DataSource("test_for_fun").read_bdb(as_type=pd.DataFrame, partition_filter={
    "date": ("2023-01-01", "2023-01-15"),
    "instrument": {"000002.SZ"}
})
df_result_with_columns = dai.DataSource("test_for_fun").read_bdb(as_type=pd.DataFrame, partition_filter={
    "date": ("2023-01-01", "2023-01-15"),
    "instrument": {"000002.SZ"}
}, columns=["instrument", "date"])

dai.DataSource.insert_bdb

dai.DataSource.insert_bdb (data, excludes=None, timeout=300)

更新和插入数据行到bdb表。

参数:

  • data: Union[pa.Table, pd.DataFrame, ds.Dataset],数据,支持类型为pa.Table、pd.DataFrame、ds.Dataset。
  • excludes: Set[str],需要排除的字段,默认为None。
  • timeout: int,锁超时时间,默认为300秒。

返回值: DataSource

示例:

import numpy as np
import pandas as pd
import dai

ds = dai.DataSource("test_for_fun")

instruments = ["000001.SZ", "000002.SZ", "000003.SZ"]
dates = list(pd.date_range("2022-01-05", "2022-01-15"))

df = pd.DataFrame({"instrument": instruments * len(dates), "date": dates * len(instruments), "value1": np.random.random(len(dates) * len(instruments))})
df[dai.DEFAULT_PARTITION_FIELD] = df["date"].dt.year

ds.insert_bdb(df)

dai.DataSource.apply_bdb

dai.DataSource.apply_bdb (func=None, as_type=pa.Table, partition_filter=None, timeout=300) -> DataSource

按partition处理数据,可以用于数据增删查改。

参数:

  • func: Callable[[Union[pa.Table, pd.DataFrame, ds.Dataset]], Optional[Union[pa.Table, pd.DataFrame, ds.Dataset]]],数据处理函数。输入为当前分区的数据。返回数据,如果长度大于0,则使用返回数据更新分区;如果长度为0,则删除当前分区;如果为None,则不对分区做处理。
  • as_type: Union[pa.Table, pd.DataFrame, ds.Dataset],数据插入类型,默认为pa.Table。
  • partition_filter: Dict[str, Union[tuple, set]],分区过滤条件,默认为None。
  • timeout: int,锁超时时间,默认为300秒。

返回值: DataSource

示例:

import numpy as np
import pandas as pd
import dai

ds = dai.DataSource("test_for_fun")

def delete_000001(df):
    df = df[df["instrument"] != "000001.SZ"]
    return df

def insert_value2(df):
    df["value2"] = np.random.random(len(df))
    return df

def remove_value2(df):
    del df["value2"]
    return df

def delete_all(df):
    return pd.DataFrame()

# 删除行
ds.apply_bdb(delete_000001, as_type=pd.DataFrame)
# 插入列
ds.apply_bdb(insert_value2, as_type=pd.DataFrame)
# 删除列
ds.apply_bdb(remove_value2, as_type=pd.DataFrame)
# 指定 partition_filter 可以只处理某些分区的数据以提高效率,例如删除 2022 分区的所有数据
ds.apply_bdb(
    delete_all,
    as_type=pd.DataFrame,
    partition_filter={
        "date": ("2022-01-01", "2023-01-01")
    }
)

\

dai.DataSource.check_bdb

dai.DataSource.check_bdb (delete_invalid_data=False)

检查数据源是否存在异常。

参数:

  • delete_invalid_data: bool,是否删除异常数据,默认为False。

示例:

import dai
dai.DataSource("test_for_fun").check_bdb()
# 删除异常数据,后续再补充数据
dai.DataSource("test_for_fun").check_bdb(delete_invalid_data=True)

dai.DataSource.write_pickle

dai.DataSource.write_pickle (data, id=None) -> DataSource

写入pickle数据。

参数:

  • data: PyObject,数据。
  • id: str,数据id,默认为None,写入到cache数据。

返回值: DataSource

示例:

import dai

pyobject = {"a": 1, "b": 2}
dai.DataSource.write_pickle(pyobject, id="test_for_fun_pickle")

dai.DataSource.read_pickle

dai.DataSource.read_pickle () -> PyObject

读取pickle数据。

返回值: PyObject

示例:

import dai

dai.DataSource("test_for_fun_pickle").read_pickle()
# {'a': 1, 'b': 2}

dai.DataSource.write_text

dai.DataSource.write_text (data, id=None) -> DataSource

写入文本数据。

参数:

  • data: str,数据。
  • id: str,数据id,默认为None,写入到cache数据。

返回值: DataSource

示例:

import dai

text = "Hello, world!"
dai.DataSource.write_text(text, id="test_for_fun_text")

dai.DataSource.read_text

dai.DataSource.read_text () -> str

读取文本数据。

返回值: str

示例:

import dai

dai.DataSource("test_for_fun_text").read_text()
# 'Hello, world!'

dai.DataSource.write_json

dai.DataSource.write_json (data, id=None) -> DataSource

写入json数据。

参数:

  • data: any,json数据。
  • id: str,数据id,默认为None,写入到cache数据。

返回值: DataSource

示例:

import dai

json = {"a": 1, "b": 2, "c": [1, 2, 3]}
dai.DataSource.write_json(json, id="test_for_fun_json")

dai.DataSource.read_json

dai.DataSource.read_json () -> any

读取json数据。

返回值: any

示例:

import dai

dai.DataSource("test_for_fun_json").read_json()
# {"a": 1, "b": 2, "c": [1, 2, 3]}

dai.DataSource.write_binary

dai.DataSource.write_binary (data, id=None) -> DataSource

写入字节数据。

参数:

  • data: bytes,数据。
  • id: str,数据id,默认为None,写入到cache数据。

返回值: DataSource

示例:

import dai

binary = b"Hello, world!"
dai.DataSource.write_binary(binary, id="test_for_fun_binary")

dai.DataSource.read_binary

dai.DataSource.read_binary () -> bytes

读取字节数据。

返回值: bytes

示例:

import dai

dai.DataSource("test_for_fun_binary").read_binary()
# b'Hello, world!'

dai.DataSource.delete

dai.DataSource.delete ()

删除数据。

示例:

import dai

dai.DataSource("test_for_fun_binary").delete()

dai.DataSource.write_zero

dai.DataSource.write_zero (id) -> DataSource

创建一个空的DataSource,并由您自己管理。

参数:

  • id: str,数据id。

返回值: DataSource

示例:

import dai

ds = dai.DataSource.write_zero("test_for_fun_zero")

dai.DataSource.mount

dai.DataSource.mount (with_write=False, symlink_to=None, symlink_force=False) -> DataSourceMount

挂载DataSource目录,只支持 zero DataSource挂载。

参数:

  • with_write: bool,可写模式挂载,只有DataSource创建者可以执行此操作,默认为False。
  • symlink_to: str,创建软链到指定地址,默认为None。
  • symlink_force: bool,强制创建软链,如果目标地址文件存在,则删除,默认为False。

返回值: DataSourceMount

示例:

import dai

with dai.DataSource("test_for_fun_zero").mount(with_write=True) as path:
    # mount到本地 path,并自动 unmount
    print(f"{path=}")
    # 往挂载目录可以写入数据
    with open(f"{path}/test_for_fun.txt", "w") as f:
        f.write("Hello, world!")

dai.DataSource.unmount

dai.DataSource.unmount (path)

卸载挂载的DataSource目录。

参数:

  • path: str,挂载目录。

示例:

import dai

ds = dai.DataSource("test_for_fun_zero")
mnt = ds.mount()
print(f"{mnt.path=}")
with open(f"{mnt.path}/test_for_fun.txt", "r") as f:
    print(f.read())
ds.unmount(mnt.path)

dai.DataSource.save_view

dai.DataSource.save_view (id, sql, update_if_exists=True, docs=None, timeout=300) -> DataSource

保存视图类DataSource。

参数:

  • id: str,数据id。
  • sql: str,视图sql。
  • update_if_exists: bool,如果视图存在,则更新,默认为True。
  • docs: Dict[str, Any],视图文档,默认为None。
  • timeout: int,锁超时时间,默认为300秒。

返回值: DataSource

示例:

import dai

sql = """
select * from cn_stock_bar1d prune join cn_stock_valuation using (instrument, date)
"""
dai.DataSource.save_view("test_for_fun_view", sql)
result_df = dai.query("select * from test_for_fun_view where date > '2023-01-01' order by instrument, date").df()

dai.DataSource.persist

dai.DataSource.persist (id) -> DataSource

持久化缓存DataSource。

参数:

  • id: str,数据id。

返回值: DataSource

示例:

import dai

text = "Hello, world!"
cached_datasource = dai.DataSource.write_text(text)
cached_datasource.persist("test_for_persist")

DAI相关可视化模块文档




{{heading_numbering_zhCN}}

标签

因子数据金融数据平台金融数据获取

文档

BigQuant 数据字典DAI SQLDAI SQL FAQDAI SQL 函数列表
评论
  • 如何支持港股