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

2024-06-14 10:44
文章标签 类型 数据 比较 详解 查询 优化 性能 时间 规则 dolphindb

本文主要是介绍优化查询性能：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 条件来提升查询的效率。尤其针对数据量非常大的分区表进行查询时，分区剪枝能够节省大量时间。

这篇关于优化查询性能：DolphinDB 时间类型数据比较规则详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1060185。

相关文章

Linux线程同步/互斥过程详解

Linux线程同步/互斥过程详解

《Linux线程同步/互斥过程详解》文章讲解多线程并发访问导致竞态条件,需通过互斥锁、原子操作和条件变量实现线程安全与同步,分析死锁条件及避免方法,并介绍RAII封装技术提升资源管理效率... 目录01. 资源共享问题1.1 多线程并发访问1.2 临界区与临界资源1.3 锁的引入02. 多线程案例2.1 为
阅读更多...
批量导入txt数据到的redis过程

批量导入txt数据到的redis过程

《批量导入txt数据到的redis过程》用户通过将Redis命令逐行写入txt文件,利用管道模式运行客户端,成功执行批量删除以Product*匹配的Key操作,提高了数据清理效率... 目录批量导入txt数据到Redisjs把redis命令按一条 一行写到txt中管道命令运行redis客户端成功了批量删除k
阅读更多...
Oracle查询表结构建表语句索引等方式

Oracle查询表结构建表语句索引等方式

《Oracle查询表结构建表语句索引等方式》使用USER_TAB_COLUMNS查询表结构可避免系统隐藏字段（如LISTUSER的CLOB与VARCHAR2同名字段）,这些字段可能为dbms_lob.... 目录oracle查询表结构建表语句索引1.用“USER_TAB_COLUMNS”查询表结构2.用“a
阅读更多...
Python使用Tenacity一行代码实现自动重试详解

Python使用Tenacity一行代码实现自动重试详解

《Python使用Tenacity一行代码实现自动重试详解》tenacity是一个专为Python设计的通用重试库,它的核心理念就是用简单、清晰的方式,为任何可能失败的操作添加重试能力,下面我们就来看... 目录一切始于一个简单的 API 调用Tenacity 入门：一行代码实现优雅重试精细控制：让重试按我
阅读更多...
SpringBoot多环境配置数据读取方式

SpringBoot多环境配置数据读取方式

《SpringBoot多环境配置数据读取方式》SpringBoot通过环境隔离机制,支持properties/yaml/yml多格式配置,结合@Value、Environment和@Configura... 目录一、多环境配置的核心思路二、3种配置文件格式详解2.1 properties格式（传统格式）1.
阅读更多...
Python标准库之数据压缩和存档的应用详解

Python标准库之数据压缩和存档的应用详解

《Python标准库之数据压缩和存档的应用详解》在数据处理与存储领域,压缩和存档是提升效率的关键技术,Python标准库提供了一套完整的工具链,下面小编就来和大家简单介绍一下吧... 目录一、核心模块架构与设计哲学二、关键模块深度解析1.tarfile：专业级归档工具2.zipfile：跨平台归档首选3.
阅读更多...
解决pandas无法读取csv文件数据的问题

解决pandas无法读取csv文件数据的问题

《解决pandas无法读取csv文件数据的问题》本文讲述作者用Pandas读取CSV文件时因参数设置不当导致数据错位,通过调整delimiter和on_bad_lines参数最终解决问题,并强调正确参... 目录一、前言二、问题复现1. 问题2. 通过 on_bad_lines=‘warn’ 跳过异常数据3
阅读更多...
idea的终端(Terminal)cmd的命令换成linux的命令详解

idea的终端(Terminal)cmd的命令换成linux的命令详解

《idea的终端(Terminal)cmd的命令换成linux的命令详解》本文介绍IDEA配置Git的步骤：安装Git、修改终端设置并重启IDEA,强调顺序,作为个人经验分享,希望提供参考并支持脚本之... 目录一编程、设置前二、前置条件三、android设置四、设置后总结一、php设置前二、前置条件
阅读更多...
python中列表应用和扩展性实用详解

python中列表应用和扩展性实用详解

《python中列表应用和扩展性实用详解》文章介绍了Python列表的核心特性：有序数据集合,用[]定义,元素类型可不同,支持迭代、循环、切片,可执行增删改查、排序、推导式及嵌套操作,是常用的数据处理... 目录1、列表定义2、格式3、列表是可迭代对象4、列表的常见操作总结1、列表定义是处理一组有序项目的
阅读更多...
python使用try函数详解

python使用try函数详解

《python使用try函数详解》Pythontry语句用于异常处理,支持捕获特定/多种异常、else/final子句确保资源释放,结合with语句自动清理,可自定义异常及嵌套结构,灵活应对错误场景... 目录try 函数的基本语法捕获特定异常捕获多个异常使用 else 子句使用 finally 子句捕获所
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2