优化查询性能：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。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
大模型研发全揭秘：客服工单数据标注的完整攻略

大模型研发全揭秘：客服工单数据标注的完整攻略

在人工智能（AI）领域，数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者，掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中，工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注，不仅能够帮助提升客服自动化系统的智能化水平，还能优化客户服务流程，提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
百度/小米/滴滴/京东，中台架构比较

百度/小米/滴滴/京东，中台架构比较

小米中台建设实践 01 小米的三大中台建设：业务+数据+技术 业务中台--从业务说起 在中台建设中，需要规范化的服务接口、一致整合化的数据、容器化的技术组件以及弹性的基础设施。并结合业务情况，判定是否真的需要中台。 小米参考了业界优秀的案例包括移动中台、数据中台、业务中台、技术中台等，再结合其业务发展历程及业务现状，整理了中台架构的核心方法论，一是企业如何共享服务，二是如何为业务提供便利。
阅读更多...
服务器集群同步时间手记

服务器集群同步时间手记

1.时间服务器配置（必须root用户） （1）检查ntp是否安装 [root@node1 桌面]# rpm -qa|grep ntpntp-4.2.6p5-10.el6.centos.x86_64fontpackages-filesystem-1.41-1.1.el6.noarchntpdate-4.2.6p5-10.el6.centos.x86_64 （2）修改ntp配置文件 [r
阅读更多...
关于数据埋点，你需要了解这些基本知识

关于数据埋点，你需要了解这些基本知识

产品汪每天都在和数据打交道，你知道数据来自哪里吗？ 移动app端内的用户行为数据大多来自埋点，了解一些埋点知识，能和数据分析师、技术侃大山，参与到前期的数据采集，更重要是让最终的埋点数据能为我所用，否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式，可以区分为： 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理：自动程度高的，能解决通用统计，便于统一化管理，但个性化定
阅读更多...
性能测试介绍

性能测试介绍

性能测试是一种测试方法，旨在评估系统、应用程序或组件在现实场景中的性能表现和可靠性。它通常用于衡量系统在不同负载条件下的响应时间、吞吐量、资源利用率、稳定性和可扩展性等关键指标。 为什么要进行性能测试 通过性能测试，可以确定系统是否能够满足预期的性能要求，找出性能瓶颈和潜在的问题，并进行优化和调整。 发现性能瓶颈：性能测试可以帮助发现系统的性能瓶颈，即系统在高负载或高并发情况下可能出现的问题
阅读更多...
使用SecondaryNameNode恢复NameNode的数据

使用SecondaryNameNode恢复NameNode的数据

1）需求： NameNode进程挂了并且存储的数据也丢失了，如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2）故障模拟 （1）kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 （2）删除NameNode存储的数据（/opt/module/hadoop-3.1.4/data/tmp/dfs/na
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2