SQL 编写范例¶
数据联邦服务下的联邦查询支持输入SQL语句查询数据源中的数据。输入的 SQL 语句需要遵循 ANSI/SQL2003 标准,常用的 SQL 语句包括:
show schemas
:查看所有添加的数据源结构describe {表的完整路径}
:查询表结构(如果文件不是 csv 结构,可能无法展示其结构)select {* / 列名} from {表名} where {column_name} operator {value}
:检索数据库表中的数据行和列- 注意:通道类型不同,表名的编写规则不同。当通道类型为 READ 时,单源查询表名由
库名称.表名称
构成;跨源查询表名称由数据源名称.库名称.表名称
构成。当通道类型为 DOWNLOAD 时,表名由库名称.表名称
构成。
使用限制¶
- 由于文件系统读取默认为 varchar 类型,只支持 COUNT,MAX,MIN 这三个函数。如果需要使用其他函数,需要使用
CAST (xxxx AS type)
对数据类型进行转换。 - Redis 中有的表是固定的名字,因此在使用
JOIN
时需要定义表名字的别名。 JOIN
一定要有条件。- 不支持
CROSS JOIN
。 - 使用
BETWEEN ... AND
时,也需要使用CAST (xxxx AS type)
对数据类型进行转换。
数据查询 SQL 范例¶
下表列出从各类数据源查询数据的SQL语句范例。根据通道类型不同,表名的编写规则不同。
数据源 | 表结构 | 范例配置 | SQL范例 |
---|---|---|---|
MySQL | data_source.database.table |
|
|
Hive | data_source.database.table |
|
|
Redis | data_source.db.type |
|
|
HDFS | data_source.folder0.folder1.file0(文件系统区别于数据库类型,访问时结构为数据源、文件夹、文件) |
|
|
S3/Blob | data_source.bucket.folder1.file0(S3/Blob 同样是文件系统,但是需要增加 bucket) |
|
|
Kafka | data_source.topic(Kafka 区别于所有数据源,只有数据源和 Topic 两级) |
|
|
常用 Select 语法¶
select {* / 列名} from {表名} where {column_name} operator {value}
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
--------- 只能在使用like关键字的where子句中使用通配符)
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stock*** = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序,desc- 降序,asc- 升序
order by 1,2 --------- by 列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层 select 只返回一个行的值,
--------- 否则应在外层 where 子句中用一个 in 限定符
select distinct column_name form table_name --------- distinct 指定检索独有的列值,不重复
select stocknumber , (stocknumber + 10)`stocknumber+10` from table_name
select stockname , (count(*)`count` from table_name group by stockname
--------- group by 将表按行分组,指定列中有相同的值
having count(*) = 2 --------- having 选定指定的组
select *
from table1, table2
where table1.id = table2.id
select stockname from table1
union [all] ----- union 合并查询结果集,all- 保留重复行
select stockname from table2
常用函数¶
统计函数¶
- AVG:求平均值
- COUNT:统计数目
- MAX:求最大值
- MIN:求最小值
- SUM:求和
- STDDEV():返回表达式中所有数据的标准差
- STDDEV_POP():返回总体标准差
- VARIANCE():返回表达式中所有值的统计变异数
- VAR_POP():返回总体变异数
算数函数¶
三角函数
- SIN(float_expression):返回以弧度表示的角的正弦
- COS(float_expression):返回以弧度表示的角的余弦
- TAN(float_expression):返回以弧度表示的角的正切
- COT(float_expression):返回以弧度表示的角的余切
INTEGER/MONEY/REAL/FLOAT 类型
- EXP(float_expression):返回表达式的指数值
- LOG(float_expression):返回表达式的自然对数值
- LOG10(float_expression):返回表达式的以 10 为底的对数值
- SQRT(float_expression):返回表达式的平方根
取近似值函数
- CEILING(numeric_expression):返回大于等于表达式的最小整数,返回的数据类型与表达式相同
- FLOOR(numeric_expression):返回小于等于表达式的最小整数,返回的数据类型与表达式相同
- ROUND(numeric_expression):返回以 integer_expression 为精度的四舍五入值
- ABS(numeric_expression):返回表达式的绝对值返回的数据类型与表达式相同
- SIGN(numeric_expression):测试参数的正负号返回 0 零值 1 正数或 -1 负数返回的数据类型
- PI():返回值为 π,即 3.1415926535897936
- RAND([integer_expression]):用任选的[integer_expression]做种子值得出 0-1 之间的随机浮点数
字符串函数
- ASCII():返回字符表达式最左端字符的 ASCII 码值
- CHAR():用于将ASCII码转换为字符,如果没有输入 0 ~ 255 之间的 ASCII 码值,CHAR 函数会返回一个 NULL 值
- LOWER():把字符串全部转换为小写
- UPPER():把字符串全部转换为大写
日期函数
- DAY():返回 date_expression中 的日期值
- MONTH():返回 date_expression 中的月份值
- YEAR():返回 date_expression 中的年份值
HDFS 查询语法规则¶
- 通过数据联邦通道跨组织访问 HDFS 中的数据之前,需通过 数据资产权限 页面添加跨组织访问 HDFS 的权限策略。
- HDFS 查询不支持通配符。若以通配符授权 HDFS 中的访问路径,运行 SQL 会报错,需要在查询 SQL 中编写具体的 HDFS 路径。
对 TSDB 数据的聚合示例¶
本示例以存储在 TSDB 中的以下数据为例,介绍如何通过联邦通道对数据进行查询和聚合处理:
- 设备:”hiGNjDBJ”, “9wmyXxKf”
- 测点:”ai_point_1”,”ai_point_2”,”ai_point_3”,”ai_point_4”,”ai_point_5”
- 数据密度:每秒 1 个测点值
- 查询时间范围:”2020-11-01 00:00:00” “2020-12-01 00:00:00” (86400 条数据/point)
- 通道类型:Read - 跨源分析
多设备多测点¶
使用以下查询语句,对多个设备多个测点在一天时间内的数据进行小时级聚合,并求均值:
select cast((`timestamp`/3600000) * 3600000 as timestamp) as ts , assetId, pointId, avg(value) as point_avg from tsdb.o15504722874071.eniot where assetId in ('hiGNjDBJ', '9wmyXxKf') and pointId in('ai_point_1','ai_point_2')and local_time >= '2020-11-01 00:00:00' and local_time < '2020-11-02 00:00:00' GROUP BY (`timestamp`/3600000), assetId,pointId
聚合结果如下图所示:
多设备单测点¶
使用以下查询语句,对多个设备单个测点的数据在聚合后进行切面上的聚合计算:
point_x=sum(device.point_x)
with t as (select cast((`timestamp`/3600000) * 3600000 as timestamp) as ts , assetId, pointId, avg(value) as value from tsdb.o15504722874071.eniot where assetId in ('hiGNjDBJ', '9wmyXxKf') and pointId in('ai_point_1','ai_point_2')and local_time >= '2020-11-01 00:00:00' and local_time < '2020-11-02 00:00:00' GROUP BY (`timestamp`/3600000), assetId,pointId) select t.pointId,t.ts,sum(t.value) as sumPointValue from t where t.pointId in('ai_point_1','ai_point_2') group by t.ts, t.pointId ORDER BY ts
聚合结果如下图所示:
单设备多测点¶
使用以下查询语句,对同一设备的两个测点数据进行聚合计算:
device.pointTmp=device.sum(ai_point_1+ai_point_2)
with t as (select cast((`timestamp`/3600000) * 3600000 as timestamp) as ts , assetId, pointId, avg(value) as value from tsdb.o15504722874071.eniot where assetId in ('hiGNjDBJ', '9wmyXxKf') and pointId in('ai_point_1','ai_point_2')and local_time >= '2020-11-01 00:00:00' and local_time < '2020-11-02 00:00:00' GROUP BY (`timestamp`/3600000), assetId,pointId) select t.ts, t.assetId, sum(CASE WHEN t.pointId in ('ai_point_1','ai_point_2') THEN 1 ELSE 0 END) as pointTmp from t group by t.ts,t.assetId
聚合结果如下图所示:
与MySQL中的维度表进行聚合计算¶
使用以下查询语句,与 MySQL 中的维度表进行聚合计算:
point1_real= ai_point_1*ai_point_1_coefficient
with t as (select cast((`timestamp`/3600000) * 3600000 as timestamp) as ts , assetId, pointId, avg(value) as value from tsdb.o15504722874071.eniot where assetId in ('hiGNjDBJ', '9wmyXxKf') and pointId in('ai_point_1')and local_time >= '2020-11-01 00:00:00' and local_time < '2020-11-02 00:00:00' GROUP BY (`timestamp`/3600000), assetId,pointId) select t.assetId, t.pointId, t.value*m.ai_point_1_coefficient as point1_real from mysql196.test.device m join t on m.dev_id=t.assetId
聚合结果如下图所示: