简单 SQL

阅读(5128) 标签: 简单sql,

本章介绍简单SQL的用法。

概念:

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

目前支持的数据文件类型包括:txtcsvxlsxxlsbtx(集文件)。数据文件均认为有标题,查询数据文件时既可以用相对路径也可以用绝对路径,相对路径相对于集算器主目录。

简单SQL与普通SQL类似, 可用于db.query(sql)$(db)sql函数中,不同的是,普通SQL是从数据库中查询,要指定连接的数据源名称db,而简单SQL是从文件系统中查询内容的,无需设置数据源名称,数据源参数db直接置空即可,写法为:connect().query(sql)/$()sql

简单SQL语法:

with T as (x) 

用集算器脚本定义数据文件中的表,x可返回序表或游标。

select x F,…

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

_file

如果from文件,该字段表示文件名,其它from时无定义。

_ext

扩展名。

_size

文件大小。

_date

文件最后修改时间。

*

*只包含文件内的字段不含文件属性。

.from T T’

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

from fn T

直接用文件fn作为表T,文件类型包括: txtcsvxlsxlsxbtx(集文件),按游标处理;文件均认为有标题,fn文件可以用相对路径或绝对路径,相对路径相对于集算器主目录。

文件名中允许使用通配符,*匹配0个或多个字符,?匹配单个字符;可以对应多个文件,认为是多个同结构的数据文件做并集,暂不支持访问子目录。

from {x}

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

as

别名前可以用as

where

条件过滤。

join left joinfull join

关联,有join时认为全内存,使用xjoin实现。

/*+parallel (n)*/

joinfrom btx(集文件)/ctx(组表)时可以并行。

group by

分组,用groups,认为结果集可内存化。

group by n 

select中第n个表达式分组,n只能是常数。

having

分组过滤。

order by

排序,默认升序。

order by n

select中第n个表达式排序,n只能是常数。

distinct

SQL,只能单表。

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

将结果集写入到文件中,根据扩展名决定文件类型,目前支持的扩展名包括txtcsvbtx

文件已存在则判断结构相同后追加,结构不同则报错。

fn文件可以用相对路径或绝对路径,相对路径相对于集算器主目录。 

into ${x}.ext

可在文件名中使用有宏的串,将计算出x拼入文件名,支持写入多文件。

??i

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

聚合函数

sumcount max minavg 

集合运算

unionunion allintersectminus;集合运算认为全内存。

子查询

from之外涉及的子查询均认为全内存,可支持无参数的子查询。

计算函数

sqltranslate函数参照表中定义(无数据库名称,对应SPL表达式),暂不支持窗口函数。

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)

ex次幂。

FLOOR(x)

求小于等于x的最大整数。

LN(x)

求自然对数。

LOG10(x)

求以10为底的对数。

MOD(x,m)

xm

POWER(x,y)

xy次幂。

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)

条件计数。

注意:

在简单SQL语句中,当字段名称、数据文件路径、数据文件名称、别名等位置中的字符串,在使用特殊标识符或者首字母非下划线和字母时,需要使用双引号引起来,否则会报错,并且使用双引号时请注意判断是否需要转义。

示例:

db.query(sql)中的用法:

 

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.btx  where city  like  'N%' ")

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

15

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

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

 

 

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为游标。

$()sql中的用法:

 

A

 

1

$()select * from Persons.txt

查询数据文件Persons.txt中的数据,结果返回序表。

2

$select * from D:/Orders.txt

使用绝对路径方式查询,结果返回序表。

3

$select * from Persons.csv where Id_P=? or Id_P>?;2,2

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

4

$select  *  from Persons.txt  P  join Orders.txt  O  on P.Id_P = O.Id_P

多表联合查询。

5

$select  distinct(Id_P)  from Orders.btx

去重复值查询。

 

 

A

 

1

$()select * from Order_Wines.txt

2

$()select * from Order_Foods.txt

3

$()select * from Order_*.txt

Order_Wines.txt Order_Foods.txt 为同构的两个数据文件,使用通配符*匹配文件名称:

4

$()select _file,_size,_ext,_date,* from Order_*.txt

查询数据文件的字段及其文件名、扩展名、文件大小、文件最后修改时间:

5

>f1="C1,C2,C3"

 

6

$()select *  into  ${f1}.txt  from City.txt

City.txt中查询到的数据写入到文件中,f1为宏,最终在主目录下生成如下三个数据文件:

简单SQL中使用特殊标识符:

 

A

 

1

=connect().query("select * from \"D:/2f/cities.txt\" ")

数据文件路径名称首字母为数字的情况,使用双引号,由于外层还有一对双引号,所以需要对此处的双引号进行转义。

2

$select * from "D:/1cities.txt"

数据文件名称首字母非下划线和字母,使用双引号,此处双引号无需转义。

3

$select "@ID" from D:/cities.txt

字段名称中含有特殊标识符,需使用双引号。