简单 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

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