SQL +

阅读(406) 标签: sql+, 转换, spl,

描述:

SQL+是一种语法参照SQL92标准、新增多种功能性标识符、以查询功能为主的SQL语言。

SQL+语句与常规SQL类似,有所区别的是常规SQL是操作数据库中的表,而SQL+则只支持查询btxctx及其内存表,不支持其它外部数据。

SQL+有两种工作方式:翻译或执行。前者可通过集算器提供的界面工具,将SQL+翻译为集算器脚本语言SPL;而后者可通过JDBCODBC调用,直接运行算出结果。

 

常规语法:

first( F order by …)

排序后取第一条记录,可作为聚合函数用于group by,没有order by则随便取一条记录

last( F order by …)

排序后取最后一条记录,可作为聚合函数用于group by,没有order by则随便取一条记录

median(F)

计算中位数

in

条件查询

exists

检验查询是否返回数据

select

可用于子查询语句中

where rownum=1

获取第一条数据

where F=(select max(F) …)

条件查询

decode

将查询结果翻译成其他值

/*+external*/ T

使用/*+external*/标记表示表T不可内存,要用游标和直接文件处理,缺省是内存表

/*+big*/ group by

大分组,结果是游标,该语法对内存表无效

group by

无标识时认为是普通分组

/*+group*/ in

in判断要转join时,先分组

/*+group*/ exists

exists判断要转join时,先分组

/*+var(v)*/(…)

定义成变量v

(…)/*+var(v)*/

使用变量v,忽略(…)内容,变量v可一次定义,0次或多次使用

[arg]

拼成参数

 

Join类型:

/*+primary*/ join

同维表或主子表关系,要求两表的关联字段都有序,多个关联字段时按SQL中关联字段出现的顺序排序,

若不能保证有序会导致最终结果异常,如有必要可使用/*+ORDER*/ 标志进行计算过程中的排序预处理。

/*+foreign*/ join

外键表关联,要求右表的关键字段的值不能重复(主键约束)。当右表为外存表时要求右表的关联字段有序,多个关联字段时按SQL中关联字段出现的顺序排序。

/*+foreign(fk)*/ join

外键预关联,要关联的两个内存表已经预先做了A.join@k的预关联,其中关联字段名为fkSQL+将直接使用此结果。OLAP模式下此预关联可以自动识别。

/*+switch*/ join

Switch预关联,要关联的两个内存表已经预先做了A.swicth的预关联,SQL+将直接使用此结果。OLAP模式下此预关联可以自动识别。

join  on

普通连接,只有AND连接的全等值连接时,右表为内存表时不要求对关联字段有序,右表为外存表时要求左右表都对关联字段有序,多个关联字段时按SQL中关联字段出现的顺序排序,若不能保证有序会导致最终结果异常(如有必要可使用/*+ORDER*/ 标志进行计算过程中的排序预处理) ;存在OR连接或存在非等值连接时,不要求有序(但此种类型的JOIN计算速度很慢,应尽量避免出现)

right join

表位置反过来解析成left join

 

外存表(BTX文件、CTX文件)与内存表(序表)的处理:

1FROMJOIN后接CTXBTX文件时,如果无/*+EXTERNAL*/标志则默认将对表文件作内存化处理。 /*+EXTERNAL*/修饰序表变量时不起任何作用,包括表文件因某些中间计算过程(如GROUP BY等)而生成了序表(序表优先)。

2FROMJOIN后接子查询时,子查询内部的/*+EXTERNAL*/标志决定是否对子查询中使用的表文件直接作内存化处理,子查询外部的/*+EXTERNAL*/标志则决定了当子查询的最终结果为游标时,是否对子查询最终结果作内存化处理。

3 分组(GROUP BY)操作时,若分组对象为外存表并且有/*+BIG*/标志修饰GROUP BY时,最终结果为游标,否则将默认作内存化处理。执行取部分数据(LIMIT)的操作最终也将作内存化处理。

归并操作(UNIONINTERSECTMINUS)无/*+ORDERED*/标志修饰时,则默认将作归并计算的表都作内存化处理。还有其他任何能够让最终结果足够小的操作(如聚合全集或过滤条件等价于FALSE等),最终结果也都将作内存化处理。

4、对于SELECTWHERE中的子查询或EXISTSIN后接的子查询:结果为常量类型的子查询必须是非外存化的;非常量类型(含主查询字段)的子查询,若无/*+PRIMARY*/标志修饰也必须是非外存化的,只有用/*+PRIMARY*/标志修饰的非常量类型(含主查询字段)的子查询可以外存化。

5、执行连接( JOIN )操作时,待连接的表中有任一个是外存表时,最终结果即为游标;只有当全部待连接的表都为内存化时,最终结果才为序表。

6、执行连接( JOIN )操作时,关联的表如果是组表(即.ctx文件),则根据主键和关联字段判断是按照主键关联或是外键关联处理。

7FROMJOIN表为组表或集文件时,可在表T后加 /*+CTX*/ /*+BTX*/标志,此时要省略表后缀名;若表文件保存目录为主目录,则表文件路径也可省略不写。

8FROMJOIN表为组表或集文件时,可在主目录下添加meta.txt文件为组表或集文件定义表名,meta.txt文件结构如下:

Table:为File文件定义表名,访问集算器JDBC/ODBC时可直接使用Table查询。如:select * from tt1

File:数据文件名称,需指定相对主目录下的全路径。使用子目录时,不同操作系统需使用不同的文件分隔符号。如windowsctx//1.ctxlinuxctx/1.txt

ColumnType字段此处可省略置空

 

使用并行操作:

/*+parallel (n) */ T

该标志主要针对外存文件的并行读取(即多路游标),对于序表并无实际意义。集文件必须是通过@z生成的才能进行并行读取。

有连接( JOIN )操作时,建议尽量不要使用并行标志/*+PARALLEL*/

如果有连接( JOIN )操作,又实在需要并行标志/*+PARALLEL*/读取外存文件提速。那么可先将SQL+翻译成SPL,再根据具体情况对SPL做进一步的调整:比如joinx要使用多路游标,就需要分段数相同且为同步分段;而xjoinx则禁止任何的并行读取……

 

非常量类型的子查询标志:

/*+group*/

默认情况下,主查询与子查询认为是外键表的关系时,因为要求子查询中关联字段的值必须唯一(有主键约束) ,但EXISTSIN理论上也可以接受关联字段值不唯一(非主键约束)的情况,此时可以就用/*+GROUP*/ 标志将子查询处理为关联字段有主键约束的情况。

/*+var(v)*/

主要针对同一主查询中相同子查询多次重复的情况(作用范围不能跨查询语句),使名称v相同的子查询在SPL中被复用从而节约计算量。可复用的子查询必须是被内存化的, 而且实际的执行顺序与用户定义的顺序可能不同但不影响最终结果;除了非常量类型的子查询以外,该标志也同样适用于常量类型的子查询

ROWNUM = 1

对于ROWNUM 关键字,SQL+现只支持 ROWNUM = 1 功能。当ROWNUM = 1用于子查询中时,应确保子查询结果中的数据无差异,即:取其中任意一条,都能得到同样的最终结果。

/*+PRIMARY*/

代表主查询与子查询是主子表/同维表的关系,对应的SPL中将使用joinx来进行处理,适用于子查询不可内存化的情况,此时需要子查询与主查询相对关联字段有序;也兼容子查询可内存化的情况,此时不要求子查询与主查询相对关联字段有序。默认情况下,主查询与子查询认为是外键表的关系,对应的SPL中将使用T.find来进行处理,要求子查询必须可内存化,且子查询中关联字段的值必须唯一(有主键约束)

 

有序数据的处理:

/*+ordered*/

对于有序数据的利用都是通过/*+ORDERED*/标志来实现的 (注意要与排序标志/*+ORDER*/加以区分 ),对于有序数据使用/*+ORDERED*/标志,可以极大地提升计算速率。

/*+ordered*/ group

有序分组,要求待处理数据对于分组(GROUP BY)表达式有序(有多项表达式时,应按表达式出现的顺序作有序排列)。

/*+ordered*/ distinct

有序去重,要求待处理数据对于查询(SELECT)表达式有序(有多项表达式时,应按表达式出现的顺序作有序排列)。

count(/*+ordered*/ distinct)

有序去重计数,要求待处理数据对于COUNT中需要作计数运算的表达式有序。

/*+ordered*/  union

有序归并,要求待处理的两个同构表按表中字段原始顺序作有序排列。仅有序归并操作可支持外存表。

/*+ordered*/  intersect

有序归并,要求待处理的两个同构表按表中字段原始顺序作有序排列。仅有序归并操作可支持外存表。

/*+ordered*/  minus

有序归并,要求待处理的两个同构表按表中字段原始顺序作有序排列。仅有序归并操作可支持外存表。

 

优化标志的使用:

where /*+cursor*/ (…) and …

(…)内条件在创建游标时加上,该语法仅适用于ctx外存表,强制让WHERE中的过滤条件(需要全AND连接不能有OR)提前执行,一个/*+CURSOR*/只针对一个过滤条件(即作用域到下一个AND或句尾终止),想作用于多个过滤条件时可以用括号括起来。

where /*+index*/

使用索引过滤,该语法仅适用于ctx文件,应先确保有确实符合要求的组表索引文件可用

group by …,/*+redundant*/…

表明在/*+REDUNDANT*/标志后面的分组字段是冗余的(即仅使用此标志前的分组字段即可得到同样的最终结果),实际执行时冗余的分组字段将不参与分组计算,只随机抽取该分组中某一记录该字段的的值并列出。

C/*+bit(k)*/(p1,…,pn)

用于where语句中,判断以C为前缀的列第pi位是否全为1,每个列占k位,k缺省为32,要求C列的值为正整数

d in /*+dims(D) */ (d1,…,dn)

用于where语句中,判断序列(d1,…,dn)中是否有内存序列D的成员,字段d的值为小于序列D成员个数的无重复整数

 

备注:

1ORACLE中字符串类型与数字类型有时能混用,比如‘0’与数字0可以实现自动转换,但SQL+中需要严格区分二者。现场比对数据时要注意SQL+的正确修改。

2ORACLE中空字符串‘’等价 NULL,但SQL+中二者不等价。现场比对数据时,同样要注意此处差异。

3SQL+支持无表文件存在的纯翻译模式,但在这种情况下字段名的标准化将以处理流程中第一次出现的该字段名为准。所以在无表文件存在时纯翻译模式中,一定要确保表名字段名大小写的正确性

4SPL语言是大小写敏感的。SQL+翻译时虽然能屏蔽一些字段名或表名的大小写差异,但类似于字符串常量的大小写差异,却是无法屏蔽的(LIKE除外)。所以字符串常量的大小写一定要保证准确。

 

示例:

  注:上面为SQL+语句,下面为转换后的SPL脚本

 

select GENDER,first(NAME order by SALARY) from emp.btx group by GENDER

 

 

A

1

=file("D:/emp.btx").cursor@b(GENDER,SALARY,NAME).fetch()

2

=A1.groups(GENDER:GENDER;top@1(1,SALARY,NAME):_2)

 

select last(NAME order by SALARY) from emp.ctx

 

 

A

1

=file("D:/emp.ctx").create().cursor(SALARY,NAME).fetch()

2

=A1.groups(;top@1(-1,SALARY,NAME):_1)

 

  select median(SALARY) from emp.btx where EID<10

 

  

A

1

=file("D:/emp.btx").cursor@b(SALARY,EID).fetch()

2

=A1.select(EID<10&&EID!=null)

3

=A2.groups(;median(,SALARY):_1)

 

select median(SALARY) from emp.ctx where EID<10

 

 

A

1

=file("D:/emp.ctx").create().cursor(SALARY,EID;EID<10&&EID!=null).fetch()

2

=A1.groups(;median(,SALARY):_1)

 

select e.NAME, e.GENDER, d.DEPT, d.MANAGER from emp.btx e join Dep.btx d on d.DEPT = e.DEPT and d.MANAGER =4

 

  

A

1

=file("D:/emp.btx").cursor@b(DEPT,NAME,GENDER).fetch()

2

=file("D:/Dep.btx").cursor@b(DEPT,MANAGER).fetch()

3

=join(A1:L,DEPT,4;A2:R,DEPT,MANAGER)

4

>$2=A3.new(L.DEPT:e_DEPT,L.NAME:e_NAME,L.GENDER:e_GENDER,R.DEPT:d_DEPT,R.MANAGER:d_MANAGER)

5

=$2.new(e_NAME:e_NAME,e_GENDER:e_GENDER,d_DEPT:d_DEPT,d_MANAGER:d_MANAGER)

 

select * from emp.btx emp where emp.DEPT in (select DEPT from Dep.btx dep where dep.manager = 5 and dep.DEPT = emp.DEPT)

     

  

A

1

=file("D:/emp.btx").cursor@b().fetch()

2

=file("D:/Dep.btx").cursor@b(DEPT,MANAGER).fetch()

3

=A2.select(MANAGER==5&&MANAGER!=null)

4

>$9=A3.new(DEPT:$7,DEPT:DEPT)

5

>$9=if(ift($9),$9,create($7,DEPT).insert@r(0:$9))

6

>$9=$9.keys(DEPT).index()

7

>$8=A1.derive([].insert(0,$9.find(~.DEPT).(#1)):$7)

8

=$8.select($7.contain(DEPT)&&DEPT!=null)

9

=A8.new(EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY)

 

  select T1.CustomerName, T2.CityName from Customer.ctx T1 join City.ctx T2 on T1.CityID=T2.CityID where exists (select 1 from VIPCustomer.ctx where ID = T1.ID)

 

  

A

 

1

=file("D:/Customer.ctx").create().cursor(ID,CustomerName,CityID).fetch()

 

2

>$9=file("D:/VIPCustomer.ctx").create().cursor(ID).fetch()

 

3

func

 

4

 

=$9

5

 

=B4.select(ID==A3.(#1)&&ID!=null)

6

 

=B5.new(1:_1)

7

 

return B6.(#1).ifn()

8

>$7=A1.derive(func(A3,~):$6)

 

9

=$7.select($6!=null)

 

10

=A9.new(CustomerName:CustomerName,ID:ID,CityID:CityID)

 

11

=file("D:/City.ctx").create().cursor(CityID,CityName).fetch()

 

12

=join(A10:L,CityID;A11:R,CityID)

 

13

>$10=A12.new(L.CustomerName:T1_CustomerName,L.ID:T1_ID,L.CityID:T1_CityID,R.CityID:T2_CityID,R.CityName:T2_CityName)

 

14

=$10.new(T1_CustomerName:T1_CustomerName,T2_CityName:T2_CityName)

 

 

  select d.DEPT, (select count(EID) from emp.btx e where e.DEPT= d.DEPT group by DEPT) con from Dep.btx d

 

  

A

1

=file("D:/Dep.btx").cursor@b(DEPT).fetch()

2

=file("D:/emp.btx").cursor@b(EID,DEPT).fetch()

3

=A2.groups(DEPT:group_1;count(EID):count_1)

4

>$13=A3.new(count_1:$11,group_1:DEPT)

5

>$13=if(ift($13),$13,create($11,DEPT).insert@r(0:$13))

6

>$13=$13.keys(DEPT).index()

7

>$12=A1.derive($13.find(~.DEPT).(#1):$11)

8

=$12.new(DEPT:DEPT,$11:con)

 

select DEPT , MANAGER from Dep.btx where rownum =1

 

  

A

1

=file("D:/Dep.btx").cursor@b(DEPT,MANAGER).fetch()

2

=A1.to(1)

 

select * from emp.btx where salary = (select max(salary) from emp.btx)

 

  

A

1

=file("D:/emp.btx").cursor@b().fetch()

2

=A1.groups(;top(-1;SALARY):top_1).#1

 

select NAME, decode(GENDER,'2','F','1','M') from st.btx

 

  

A

1

=file("D:/st.btx").cursor@b(NAME,GENDER).fetch()

2

=A1.new(NAME:NAME,case(GENDER,"2":"F","1":"M"):_2)

 

select DEPT,sum(SALARY) from emp.btx group by DEPT

 

  

A

1

=file("D:/emp.btx").cursor@b(DEPT,SALARY).fetch()

2

=A1.groups(DEPT:DEPT;sum(SALARY):_2)

 

select * from /*+parallel (4) */ Persons_z.btx

  

A

1

=file("D:/Persons_z.btx").cursor@b().fetch()

 

select * from /*+external*/ emp.btx

 

  

A

1

=file("D:/emp.btx").cursor@b()

 

select o.OrderNo ,p.City from Orders.btx o /*+primary*/ join Persons.btx p on p.Id_P = o.Id_P

 

  

select * from /*+parallel (4) */ Persons_z.btx