首页 集算器 函数参考 query() db.query(sql) 前一篇

db.query(sql)

阅读（717） 标签: sql, 查询, 外部表,

备注：

通过简单SQL查询外部表数据，返回查询结果

语法：

db.query( sql {,args …})

 

备注：

从外部数据文件中查询数据；外部数据文件可以直接以【文件名.后缀名】作为表名来使用，使用的语法类似于从数据库中查询用的SQL语句，称为简单SQL。外部数据可以类似普通序表一样用来查询，称为外部表。

目前支持的外部表包括：txt、csv、xlsx、xls、btx(集文件)、ctx(组表文件)。外部表文件均认为有标题，查询外部表时既可以用相对路径也可以用绝对路径，相对路径相对于集算器主目录。

参数：

db

connect()，文件系统连接

sql

需要查询的简单sql语句，如select * from filename.txt

args

简单sql中如果用到参数，就必须转入参数值。注意，各个参数之间用逗号分隔。

简单SQL语法：

with T as (x) 

用集算器脚本定义外部表，x可返回序表或游标

select x F,…

从表中选取数据，x为字段或表达式，F为字段别名。

.from T T’

使用定义过的表T，T’为表别名，T’可省略。

from fn T

直接用文件fn作为外部表T，文件类型包括： txt,csv,xls,xlsx,btx（集文件）和ctx（组表文件），按游标处理；文件均认为有标题，fn文件可以用  相对路径或绝对路径，相对路径相对于集算器主目录。

from {x}

x为当前网格内可执行的表达式，且必须是返回结果为序表或游标的集算器表达式。

as

别名前可以用as。

where

条件过滤。

join 、left join

关联(不支持full join)。

/* parallel (n) */

无join且from btx(集文件)/ctx(组表)时可以并行。

group by

分组。

group by n 

按select中第n个表达式分组，n只能用常数。

having

分组过滤。

order by

排序，默认升序。

order by n

按select中第n个表达式排序，n只能用常数。

distinct

同SQL，只能单表，暂不支持count( distinct )

and、 or 、not 、 in、 is null、

case when else end

使用SQL风格，in只能使用常数集合且不支持子查询。

 

between

介于两个值之间的数据范围，例如f1 between 1 and 3，相当于 f1 >= 1 && f1 <= 3

like

模糊查询，支持如下几种通配符：

%  多个字符

_  单个字符

[字符列表] 字符列表中的任何单一字符

[!字符列表]或[^字符列表] 不包含字符列表中的任何单一字符

into fn

将结果集写入到文件中，根据扩展名决定文件类型，目前支持的扩展名包括txt，csv，btx，xls,xlsx。fn文件可以用相对路径或绝对路径，相对路径相对于集算器主目录。 

?、?i

SQL中的参数，i代表第i个参数。 

insert

向文件/变量追加数据，组表追加时必须对维有序

update

修改数据，只支持组表，where必须用键

delete

删除数据，只支持组表，where必须用键

commit

更新数据暂存，在commit时才实际执行，之后才能被读出

rollback

放弃最近的更新动作

/*+olap*/

用于事实表与维表做join等值相连，要求from后接的表是事实表，join连接的必须全部都是维表，on中连接条件只能是 事实表.外键字段=维表.主键字段，或者 维表1.主键字段=维表2.主键字段

聚合函数

sum、count、 max、 min、avg；暂不支持 count(distinct) 

top n

取前n条记录

limit n offset m

跳过m条后取n条记录

其他函数

符合语法规则的集算器函数

字符串函数：

 

LOWER(str)

转成小写

UPPER(str)

转成大写

LTRIM(str)

删除前导空白

RTRIM(str)

删除后缀空白

TRIM(str)

删除两端空白

SUBSTR(str,start,len)
SUBSTR(str,start)

子串

LEN(str)

串长

INDEXOF(sub,str[,start])

子串的位置

LEFT(str,len)

str中最左边len个字符

RIGHT(str,len)

str中最右边len个字符

CONCAT(str1,str2)

连接2个字符串

CONCAT(s1,s2,…)

连接多个字符串

REPLACE(str,sub,rplc)

将str中的sub替换成rplc

数值函数：

 

ABS(x)

求绝对值

ACOS(x)

求反余弦

ASIN(x)

求反正弦

ATAN(x)

求反正切

ATAN2(x,y)

求反正切

CEIL(x)

求大于等于x的最小整数

COS(x)

求余弦

EXP(x)

求e的x次幂

FLOOR(x)

求小于等于x的最大整数

LN(x)

求自然对数

LOG10(x)

求以10为底的对数

MOD(x,m)

x模m

POWER(x,y)

x的y次幂

ROUND(x,n)

四舍五入

SIGN(x)

求符号

SIN(x)

求正弦

SQRT(x)

平方根

TAN(x)

正切

TRUNC(x,n)

截断

RAND(seed)

随机数

时间日期函数：

 

YEAR(d)

取年

MONTH(d)

取月

DAY(d)

取天

HOUR(d)

取小时

MINUTE(d)

取分钟

SECOND(d)

取秒

QUARTER(d)

取季度

TODAY()

今天

NOW()

当前时间

ADDYEARS(d,n)

增加年数

ADDMONTHS(d, n)

增加月数

ADDDAYS(d, n)

增加天数

ADDHOURS(d, n)

增加小时数

ADDMINUTES(d, n)

增加分钟数

ADDSECONDS(d, n)

增加秒数

DAYOFYEAR(d)

当年中第几天

WEEKOFYEAR(d)

当年中第几星期

转换函数：

 

ASCII(str)

串中最左边字符的ASCII码

CHR(n)

整数n转换成字符

INT(x)

字符串或数值转成整数

DECIMAL(x,len,scale)

字符串或数值转成数值

TIMESTAMP(str)

yyyy-mm-dd hh24:mi:ss格式串转换成日期

NUMTOCHAR(d)

数值转成字符串

DATETOCHAR(date)

将日期转化成yyyy-mm-dd hh24:mi:ss格式串

其他：

 

NULLIF(x1,x2)

若x1=x2返回null，否则返回x1

COALESCE(x1,…)

返回第一个非null的参数

COUNTIF(x1,…,xn)

条件计数

返回值：

序表

示例：

 

A

 

1

=connect()

 

2

=A1.query("select * from Persons.txt")

3

=A1.query("select * from D:/Orders.txt")

4

=A1.query("select Id_P, sum(OrderNo) from Orders.csv group by 1 ")

按照Id_P分组

5

=A1.query("select Id_P, OrderNo from Orders.csv order by 2 ")

按照OrderNo字段排序，默认升序

6

=A1.query("select * into p1.txt from Persons.csv ")

将查询的结果集写入到p1.txt文件中

7

=A1.query("select * from Persons.csv where Id_P=? or Id_P>? ",2,2)

查询Id_P大于2或等于2的数据

8

=A1.query("select * from Persons.csv where Id_P=?1 or Id_P>?2 ",2,2)

查询Id_P大于2或等于2的数据，结果同上，?i表示第i个参数

 

9

=A1.query("with persons as (file(\"D:/Persons.btx\").import@b()) select * from persons ")

从Persons.btx文件中查询的结果集 命名为persons，然后再从persons中查询数据，对于大批量的SQL数据，起到优化的作用。

 

10

=A1.query("select /*+parallel(20)*/ count(*) from Persons.btx")

查询Persons.btx外部表中的总记录数，使用并行查询

11

=A1.query("select  *  from Persons.txt  P  join Orders.txt  O  on P.Id_P = O.Id_P  ")

多表联合查询

12

=A1.query("select distinct(Id_P),name  from Orders.txt  ")

去重复值查询

13

=A1.query(“select CASE  id_P  when 1  then 'one' when 2 then 'tow' else 'other'  end from p1.txt”)

使用case when语句

14

=A1.query("select * from Persons.ctx  where city  like  'N%' ")

使用like语法，查找文件Persons.ctx中字段city首字母为N的记录

15

=A1.query("select * from Persons.ctx  where Name  like  '_a_t%' ")

使用like语法，查找文件Persons.ctx中字段Name第二个字母为a第四个字母为t的记录

16

=A1.query("select * from Persons.ctx  where Name  like  '%[us]%' ")

使用like语法，查找文件Persons.ctx中字段Name中包含u或s字符的记录

17

=A1.query("select * from Persons.ctx  where Name  like  '%[!a]%' ")

使用like语法，查找文件Persons.ctx中字段Name中不包含a字符的记录

18

=A1.query("select * from Persons.ctx  where Name  like  '%[^s]%' ")

使用like语法，查找文件Persons.ctx中字段Name中不包含s字符的记录

 

 

A

 

1

=connect()

 

2

=A1.query("select * from Dep.txt ")

3

=A1.query("insert into Dep.txt  values ('ABC',11)")

insert语句，向Dep.txt文件中追加数据

4

=A1.query("commit")

更新数据暂存，执行commit操作后，上面追加的数据才能被读出

5

=A1.query("select * from ctb.ctx")

6

=A1.query("insert into ctb.ctx  values (8,80)")

向组表中追加一条记录

7

=A1.query("update ctb.ctx  set v1=20 where k1=2")

更新组表内容，k1是键

8

=A1.query("delete from ctb.ctx  where k1=11")

删除k1=11组表记录，k1是键

9

=A1.query("delete from ctb.ctx  where k1=10")

删除k1=10的组表记录，k1是键

10

=A1.query("rollback")

回滚操作，放弃最近的更新动作

11

=A1.query("commit")

提交

12

=A1.query("select * from ctb.ctx")

更新后的组表内容：

由于A10中进行了回滚操作，所以k1=10的记录未被删除

 

 

A

 

1

=file("score1.txt")

 

2

=file("score2.txt")

 

3

=A1.cursor@t()

 

4

=A2.cursor@t()

 

5

=[A3, A4].mcursor@t()

 

6

=connect().query("select CLASS, max(SCORE)  avg_score  from {A5} where SUBJECT='math' group by CLASS")

from {x} 语法，此处x为游标