简单 SQL

with T as (x) 

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

select x F,…

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

_file

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

_ext

扩展名。

_size

文件大小。

_date

文件最后修改时间。

*

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

.from T T’

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

from fn T

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

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

from {x}

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

as

别名前可以用as。

where

条件过滤。

join 、left join、full join

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

/*+parallel (n)*/

无join且from 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

将结果集写入到文件中，根据扩展名决定文件类型，目前支持的扩展名包括txt、csv、btx。

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

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

into ${x}.ext

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

?、?i

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

聚合函数

sum、count、 max、 min、avg  。

集合运算

union、union all、intersect、minus；集合运算认为全内存。

子查询

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)

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

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为宏，最终在主目录下生成如下三个数据文件：

A

1

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

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

2

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

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

3

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

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