优化查询性能：DolphinDB 时间类型数据比较规则详解

在数据库中，时间是一种常见的数据类型。在处理时间数据时，比较操作是非常常见的需求。然而，在不同的场景下，对时间类型数据进行比较时应用的规则不同。本文将从 DolphinDB 支持的时间类型开始，由浅入深分别介绍时间类型数据在不同场景下的比较规则。涵盖以下场景：

  • 时间类型的转换规则
  • 时间类型的比较规则
  • 时间类型的分区剪枝规则

1. 时间类型介绍

DolphinDB 支持的时间类型包括：TIME, MINUTE, SECOND, DATE, MONTH, DATEHOUR, DATETIME, TIMESTAMP, NANOTIME, NANOTIMESTAMP。以上数据类型可以按照包含的时间信息分成：

  • 日期型：仅包含日期信息，包括 DATE, MONTH
  • 时间型：仅包含时间信息，包括 MINUTE, SECOND, TIME, NANOTIME
  • 日期时间型：同时包含日期和时间信息，包括 DATEHOUR, DATETIME, TIMESTAMP, NANOTIMESTAMP

以下是每个时间类型格式说明和例子：

DolphinDB 的时间类型不包含时区信息，由用户来决定时间对下的时区。通过 localtime、 gmtime、convertT 函数可以转换时区信息，通过today和now函数可以获取当前的系统时间。

获取当前的日期，可以使用today函数，函数会返回一个 DATE 类型的数据，表示当前的日期：

today()  // 2024.06.03

获取当前的时间，可以使用now函数，默认情况下，该函数返回的是 TIMESTAMP 类型，精确到毫秒；也可以指定参数nanoSecond=true，返回 NANOTIMESTAMP 类型，精确到纳秒。

now()  // 2024.06.03T09:29:38.390
now(true)  // 2024.06.03T09:31:17.318298137

2. 显式的时间类型转换

在 DolphinDB 中，可以使用数据类型转换函数或者cast函数进行数据类型转换。时间类型的转换规则可以总结成下表（横轴为目标数据类型，纵轴为源数据类型，√表示支持转换，x表示不支持转换。）。

日期型日期时间型时间型
日期型√√×
日期时间型√√√
时间型××√

时间类型的转换规则可概括为：

（1）相同分类中的时间类型可以相互转换。

同为日期型的 DATE 和 MONTH 可以互相转换；

date(2012.01M)   // 2012.01.01
month(2012.01.02)  // 2012.01M

同为时间型的 MINUTE、SECOND、TIME、NANOTIME 可以互相转换；

minute(23:30:00)  // 23:30m
minute(23:30:00.000)  // 23:30m
minute(23:30:00.000000000)  // 23:30m
second(23:30m)  // 23:30:00
second(23:30:00.001)  // 23:30:00
second(23:30:00.000000001)  // 23:30:00
time(23:31m)  // 23:31:00.000
time(23:30:01)  // 23:30:01.000
time(23:30:01.000000001)  // 23:30:01.000
nanotime(23:30m)  // 23:30:00.000000000
nanotime(23:30:31)  // 23:30:31.000000000
nanotime(23:30:31.001)  //23:30:31.001000000

同为日期时间型的 DATEHOUR、DATETIME、TIMESTAMP、NANOTIMESTAMP 可以互相转换。

datehour(2020.01.01 13:30:01)  // 2020.01.01T13
datehour(2020.01.01T13:30:01.001)  //  2020.01.01T13
datehour(2020.01.01T13:30:01.001002003)  // 2020.01.01T13
datetime(datehour(2020.01.01 13:00:01))  // 2020.01.01T13:00:00
datetime(2020.01.01T13:30:01.001)  // 2020.01.01T13:30:01
datetime(2020.01.01T13:30:01.001002003)  //  2020.01.01T13:30:01
timestamp(datehour(2020.01.01 13:00:01))  //  2020.01.01T13:00:00.000
timestamp(2020.01.01 13:00:01)  // 2020.01.01T13:00:01.000
timestamp(2020.01.01T13:30:01.001002003)  // 2020.01.01T13:30:01.001
nanotimestamp(datehour(2020.01.01 13:00:01))  //  2020.01.01T13:00:00.000000000
nanotimestamp(2020.01.01T13:30:01)  //  2020.01.01T13:30:01.000000000
nanotimestamp(2020.01.01T13:30:01.001)  // 2020.01.01T13:30:01.001000000

（2）日期型和日期时间型可以相互转换。

日期型转换为日期时间型，会自动补充时间为0点的信息。

datehour(2023.01.02)  // 2023.01.02T00
datetime(2023.01.02)  // 2023.01.02T00:00:00
timestamp(2023.01.02)  // 2023.01.02T00:00:00.000
nanotimestamp(2023.01.02)  // 2023.01.02T00:00:00.000000000
datehour(2023.01M)  // 2023.01.01T00
datetime(2023.01M)  // 2023.01.01T00:00:00
timestamp(2023.01M)  //  2023.01.01T00:00:00.000
nanotimestamp(2023.01M)  // 2023.01.01T00:00:00.000000000

日期时间型转换为日期型，会舍弃时间信息。

date(datehour(2020.01.01 13:00:01)) // 2020.01.01
date(2020.01.01 13:00:01)  // 2020.01.01
date(2020.01.01 13:00:01.001)  // 2020.01.01
date(2020.01.01 13:00:01.001002003)  // 2020.01.01
month(datehour(2020.01.01 13:00:01))  // 2020.01M
month(2020.01.01 13:00:01)  // 2020.01M
month(2020.01.01 13:00:01.001)  // 2020.01M
month(2020.01.01 13:00:01.001002003)  // 2020.01M

（3）日期时间型可以转换为时间型，但时间型不能转换为日期时间型。

日期时间型转换为时间型，会舍弃日期信息；

time(2020.01.01 13:00:01.001002003)  // 13:00:01.001
minute(2020.01.01 13:00:01)  // 13:00m

时间型转换为日期时间型，会抛出异常。

datetime(13:00:01)  // The function datetime does not support second data

（4）日期型和时间型不能相互转换。

month(13:00:01)  // The function month does not support second data
minute(2020.01.01)  // The function minute does not support date data

3. 常规的时间类型比较

在 DolphinDB 中，常规的时间类型比较通常用于数据量比较小的内存表和流表中，或者单纯的只比较两个时间的大小的向量中，例如：对流数据引擎得到的结果根据需要过滤相应时间段的数据。不同时间类型之间可以使用比较运算符（>, <, >=, <=, ==, !=），in 和 between 进行比较。

使用比较运算符对不同时间类型进行比较时，系统会按照第2章中的转换规则，尝试将时间粒度较粗的类型转换成时间粒度较细的类型，如果能够转换，就作比较；如果不能够转换，则抛出异常。例如，表达式2023.01.04T13:30:10.001 > 2023.01.04 执行时，会将2023.01.04转换成2023.01.04T00:00:00.000再进行比较，因此返回结果是true。

2023.01.04T13:30:10.001 > 2023.01.04   // true
2011.01.01T13:00:00 > 2011.01.02   // false
2023.01.04T13:30:10.001 == 2023.01.04   // false
2023.01.04 == 2023.01.04T00:00:00.000  //  true

需要注意的是，

  • MONTH 类型的数据比较特殊，虽然它可以和日期时间型以及同为日期型的 DATE 类型相互转换，但是它们之间不能进行比较。MONTH 只能和MONTH 类型进行比较。
  • 日期时间型虽然可以转换为时间型，但是它们之间不能比较。
  • 关系运算符 between 和其他比较运算符不同，只有运算符的左右两边的类型一致时，才可以比较。
2023.01.04T13:30:10.001 between 2023.01.04T13:30:10.003:2023.01.04T13:30:10.004   // false

运算符的左右两边的类型不一致时，会报错Temporal data comparison should have the same data type.

 2023.01.04 between 2023.01.04T13:30:10.003:2023.01.04T13:30:10.004
//  between(X, Y). Temporal data comparison should have the same data type.'

4. 时间类型的分区剪枝

在 DolphinDB 的应用实践中，时序数据的时间戳通常会作为分布式数据库的分区列，按照值或者范围分片存储。当查询语句的过滤条件包含分区列时，系统会进行分区剪枝，以减少扫描分区的数量，提升查询性能。了解时间类型的分区剪枝规则能够帮助我们写出高效的 SQL 语句。

在实际使用中，通常会对时间分区列直接进行过滤查询，或者对时间分区列进行显式类型转换后再进行过滤。这两种情况，DolphinDB 的分区剪枝规则略有不同。下面将分开阐述。

下文介绍中将频繁出现三个名称，在此先介绍它们的概念：

  • 分区方案类型：database 函数的 partitionScheme 参数指定的数据类型。在这个例子中，分区方案的类型是 [2022.09.01,2022.09.02, 2022.09.03] 的类型，即 DATE 类型：
dbName = "dfs://time_comparison"
if(existsDatabase(dbName))
	dropDatabase(dbName)
db = database(dbName, VALUE, [2022.09.01,2022.09.02, 2022.09.03])
  • 分区列类型：createPartitionedTable 函数的 partitionColumns 指定的列类型。在这个例子中，分区列的类型是表t的time列的类型，即 DATETIME 类型：
n = 6
t = table(n:n,[`time,`value],[DATETIME,DOUBLE])
t[`time] = [2022.09.01T00:00:00, 2022.09.01T12:00:00, 2022.09.02T00:00:00, 2022.09.02T12:00:00, 2022.09.03T00:00:00, 2022.09.03T12:00:00]
t[`value] = 1..6
pt = db.createPartitionedTable(t, `pt, `time).append!(t)
  • 过滤比较中的时间对象。在这个例子中，分布式表的分区列time和数据2022.09.01进行比较，2022.09.01则是过滤比较中的时间对象，是 DATE 类型的数据：
select * from pt where time == 2022.09.01
time                   value
-----------------------------------
2022.09.01T00:00:00    1.00000000
2022.09.01T12:00:00    2.00000000

4.1 对分区列直接过滤

在分布式查询中，当我们使用运算符 <, <=, =, ==, >, >=, in, between 对时间分区列和其他时间类型的数据进行比较时， 比较的规则和在内存表中相同，且系统会进行分区剪枝。但要注意，当分区方案是 DATEHOUR 和 DATETIME 类型时，不支持创建分区表。

下例中，时间列的类型为 DATETIME，按照该列对数据按天进行 VALUE 分区。

dbName = "dfs://time_comparison"
if(existsDatabase(dbName))
	dropDatabase(dbName)
	
db = database(dbName, VALUE, [2022.09.01,2022.09.02, 2022.09.03])
n = 6
t = table(n:n,[`time,`value],[DATETIME,DOUBLE])
t[`time] = [2022.09.01T00:00:00, 2022.09.01T12:00:00, 2022.09.02T00:00:00, 2022.09.02T12:00:00, 2022.09.03T00:00:00, 2022.09.03T12:00:00]
t[`value] = 1..6
pt = db.createPartitionedTable(t, `pt, `time).append!(t)

想要查询2022.09.01这一天的数据，可以直接用分区列和2022.09.01进行比较。即使2022.09.01的数据类型和分区列的数据类型不同，依然能够进行分区剪枝，只需扫描2022.09.01这个分区的数据即可。我们可以使用sqlDS 来查看分布式查询拆分子查询的情况。

sqlDS(<select * from pt where time == 2022.09.01>)

想要查询2022.09.01T00:00:00.000这一时刻的数据，也可以直接用分区列和该时刻进行比较，即使两者数据类型不同，依然能进行分区剪枝，只需扫描2022.09.01这个分区的数据即可。

sqlDS(<select * from pt where time == 2022.09.01T00:00:00.000>)

4.2 对分区列进行显式类型转换后过滤

对于显式类型转换的过滤条件中，形如convert_func(col) <operator> constant的表达式，其中convert_func是 date、month 等时间类型转换函数， col是分区列，operator是运算符，constant是比较的值，以下情况可以进行分区剪枝。

4.2.1 使用比较运算符

当<operator>为比较运算符（<, <=, =, ==, >, >=）时，convert_func 返回的数据类型的精度小于等于constant 的精度，并且convert_func 返回的数据类型的精度小于等于分区列的精度，可以进行分区剪枝。

时间精度由低到高的排序为：MONTH < DATE < DATEHOUR < DATETIME < TIMESTAMP < NANOTIMESTAMP

这里以 VALUE 分区为例，分区方案的类型为 DATE，按照 DATE 进行分区创建分布式表：

dbName = "dfs://time_comparison"
if(existsDatabase(dbName))
	dropDatabase(dbName)
	
db = database(dbName, VALUE, [2022.09.01,2022.09.30,2022.10.01,2022.10.02,2022.10.31,2022.11.01,2022.11.02,2022.12.31,2023.01.01])
n = 10
t = table(n:n,[`time,`value],[DATE,DOUBLE])
t[`time] = take([2022.09.01,2022.09.30,2022.10.01,2022.10.02,2022.10.31,2022.11.01,2022.11.02,2022.12.31,2023.01.01],n)
t[`value] = rand(100.0,n)
pt = db.createPartitionedTable(t, `pt, `time).append!(t)

想要查询大于某个月的数据时，如果直接用分区列和 MONTH 类型的数据比较，是不支持的；这个时候可以通过month函数，将分区列的类型转换为 MONTH 类型来进行比较。

select * from pt where month(time) > 2022.10M
time          value
------------------------
2022.11.01  15.00570112
2022.11.02  66.54577804
2022.12.31  48.09958597
2023.01.01  50.57664175

因为运算符左右的类型一致，且 MONTH 类型的精度低于分区列 DATE 类型，系统会将小于2022.11M的分区都"剪枝"。

想要查询等于某一时刻的数据时，可以对分区列使用timestamp函数，和 TIMESTAMP 类型的数据进行比较，比如：

select * from pt where timestamp(time) = 2022.09.30T00:00:00.000
time          value
------------------------
2022.09.30  19.33508650

但这个时候由于转换函数的类型 TIMESTAMP 的精度高于分区列 DATE的精度，不满足分区剪枝的条件，虽然可以进行比较，但是不能进行分区剪枝。因此在这种情况下，推荐对分区列直接进行过滤的方式进行比较，效率会更高。

4.2.2 使用 between

当<operator>为between时，convert_func返回的数据类型必须与constant相同，才能进行分区剪枝。

想要查询在某一连续的月的数据，直接将分区列和 MONTH 类型的数据进行比较，是不支持的：

select * from pt where time between month(2022.10M:2022.11M)
// between(X, Y). Temporal data comparison should have the same data type.'

可以对分区列使用month函数，和 MONTH 类型的数据进行比较：

select * from pt where month(time) between month(2022.10M:2022.11M)
time          value
------------------------
2022.10.01  24.45175347
2022.10.02  86.05015869
2022.10.31  78.28769609
2022.11.01  15.00570112
2022.11.02  66.54577804

运算符 between 的左右两边的类型一致，且 MONTH 类型的精度低于分区列 DATE 的精度，满足分区剪枝的条件，系统会从2022.10.01, 2022.10.02, 2022.10.31, 2022.11.01, 2022.11.02 这5个分区中过滤数据，其他的分区会被”剪枝“。

4.2.3 使用 in

当<operator>为 in 时，convert_func 返回的数据类型必须与constant相同，并且constant列表中的连续片段的数量小于16，才能进行分区剪枝。

假设列表为 [2020.01.02, 2020.01.03, 2020.01.04, 2020.01.06, 2020.01.07, 2020.01.12]，那么它包含3个连续片段 2020.01.02..2020.01.04，2020.01.06..2020.01.07，2020.01.12。

想要查询在某一不连续的月的数据，可以使用 month 函数，和 MONTH 类型的数据进行比较：

select * from pt where month(time) in [2022.09M, 2022.11M]
time          value
------------------------
2022.09.01  51.37807030
2022.09.01  50.86722047
2022.09.30  13.91816022
2022.11.01  76.58300183
2022.11.02  74.23354792

运算符 in 的左右两边类型一致，且 MONTH 类型的精度低于分区列 DATE 的精度，[2022.09M, 2022.11M] 是一个不连续的片段，但是片段的数据小于16，满足分区剪枝的条件，系统会从2022.09.01, 2022.09.30, 2022.11.01, 2022.11.02 这4个分区中过滤数据，其他的分区会被”剪枝“。

想要查询在某一连续时刻的数据，可以使用timestamp函数，和 TIMESTAMP 类型的数据进行比较：

select * from pt where timestamp(time) in timestamp(2022.10.31..2022.11.01)
time          value
------------------------
2022.10.31  78.28769609
2022.11.01  15.00570112

虽然运算符 in 的左右两边的类型一致，但因为 TIMESTAMP 的精度高于分区列 DATE 的精度，因此不能进行分区剪枝。

5. 小结

对时间类型进行比较的规则可以分为两类：涉及分区剪枝时的比较和不涉及分区剪枝时的比较。通常时间类型向量比较，内存表查询和分布式表查询中过滤条件为非分区列时的时间类型比较均不涉及分区剪枝。而分布式表查询中过滤条件为分区列时，时间类型比较会涉及分区剪枝。​在实际使用过程中，推荐使用支持分区剪枝的 where 条件来提升查询的效率。尤其针对数据量非常大的分区表进行查询时，分区剪枝能够节省大量时间。


http://www.niftyadmin.cn/n/5520316.html

相关文章

ARM32开发--电源管理单元

ARM32开发--电源管理单元

知不足而奋进 望远山而前行 目录 文章目录 前言 学习目标 学习内容 PMU 电源域 VDD/VDDA域 备份域 1.2V域 省电模式 睡眠模式 深度睡眠模式 待机模式 几种模式总结 WFI和WFE指令 案例需求 模式初始化 源码 总结 前言 在嵌入式系统中&#xff0c;有效的电池管…
阅读更多...
php收银系统源码推荐

php收银系统源码推荐

智慧新零售系统是一套线下线上一体化的收银系统。致力于给零售门店提供『多样化线下收银』、『ERP进销存』、『o2o小程序商城』、『精细化会员管理』、『丰富营销插件』等一体化行业解决方案&#xff01; 一、多样化线下收银 1.聚合收款码 ①适用商户&#xff1a;小微门店&am…
阅读更多...
app-ios 内嵌h5的缓存问题

app-ios 内嵌h5的缓存问题

在iOS应用中内嵌H5页面时&#xff0c;可能会遇到缓存问题&#xff0c;导致页面更新不及时。以下是一些解决策略和方法&#xff1a; 目录 方法1&#xff1a;Nginx配置 方法2&#xff1a;使用版本号 方法1&#xff1a;Nginx配置 通过Nginx服务器配置来控制缓存行为。例如&#…
阅读更多...
【Java】解决Java报错：FileNotFoundException

【Java】解决Java报错：FileNotFoundException

文章目录 引言1. 错误详解2. 常见的出错场景2.1 文件路径错误2.2 文件名拼写错误2.3 文件权限问题2.4 文件路径未正确拼接 3. 解决方案3.1 检查文件路径3.2 使用相对路径和类路径3.3 检查文件权限3.4 使用文件选择器 4. 预防措施4.1 使用配置文件4.2 使用日志记录4.3 使用单元测…
阅读更多...
Git 基础操作(一)

Git 基础操作(一)

Git 基础操作 配置Git 安装完Git后&#xff0c;首先要做的事情是设置你的 用户名 和 e-mail 地址。这样在你向仓库提交代码的时候&#xff0c;就知道是谁提交的&#xff0c;以及提交人的联系方式。 配置用户名和邮箱 使用git config [--global] user.name "你的名字&qu…
阅读更多...
消费品贸易展会线上3d数字展会

消费品贸易展会线上3d数字展会

在数字化浪潮的推动下&#xff0c;3D云展平台为您带来了一款创新的展示工具——线上VR全景云展会搭建编辑器。这款编辑器不仅具备高度的可定制性和可扩展性&#xff0c;更能为您的企业或组织打造一个与众不同的品牌形象交互3d空间。 通过线上VR全景云展会搭建编辑器&#xff0…
阅读更多...
帕金森病的食疗建议

帕金森病的食疗建议

帕金森病&#xff08;PD&#xff09;是一种慢性、进展性的神经退行性疾病&#xff0c;主要影响中老年人。虽然目前尚无法根治&#xff0c;但及早规范治疗可显著改善症状&#xff0c;提高患者的生活质量。饮食调理作为帕金森病综合治疗的重要组成部分&#xff0c;对于维持患者较…
阅读更多...
VSCode格式化插件-prettier

VSCode格式化插件-prettier

VSCode格式化插件 1.安装插件&#xff1a;prettier 2.设置默认格式化工具 设置中&#xff0c;搜索 “Default Formatter”。 在编辑器设置中&#xff0c;将默认格式化工具设为 Prettier。 3.启用格式化选项&#xff1a; 在设置中搜索 “Format On Save”&#xff0c;并勾选…
阅读更多...
最新文章

Copyright @ 2022~2023