SQL 标准化

下表共统计了 92 项常用的 ANSI SQL-92 关键字，支持 62 项。其中，在不支持的 30 项中：5 项部分兼容，7 项与约束相关（在分析项目中没有太大的使用价值），10 项有替代方案。

• √：表示兼容 ×：不兼容 ○：部分兼容

• “说明”字段会给出对应函数或可替代的方案

注意：DolphinDB 内部的权限管理机制是一套独立的机制，和 SQL 不直接挂钩，因此此处关键字不包含权限相关的关键字，如：GRANT, DENY 等。

DolphinDB 针对分布式计算提供了很多特有的关键字，也根据应用场景提供了一些特色的 join 方法。

DolphinDB 还提供了一些辅助 SQL 查询的 HINT 关键字：

更多有关 HINT 关键字的内容，参考：HINT。

本文的示例采用了 HR（人力资源数据集）做为案例数据集（见节尾）进行演示，包含以下几个表：

每个表的字段及类型如下：

• countries

  Column Name	Type
  COUNTRY_ID	SYMBOL
  COUNTRY_NAME	STRING
  REGION_ID	INT

• departments

  Column Name	Type
  DEPARTMENT_ID	INT
  DEPARTMENT_NAME	STRING
  MANAGER_ID	INT
  LOCATION_ID	INT

• employees

  Column Name	Type
  EMPLOYEE_ID	INT
  FIRST_NAME	STRING
  LAST_NAME	STRING
  EMAIL	STRING
  PHONE_NUMBER	STRING
  HIRE_DATE	DATE
  JOB_ID	SYMBOL
  SALARY	INT
  COMMISSION_PCT	DOUBLE
  MANAGER_ID	INT
  DEPARTMENT_ID	INT

• jobs

  Column Name	Type
  JOB_ID	SYMBOL
  JOB_TITLE	STRING
  MIN_SALARY	INT
  MAX_SALARY	INT

• job_history

  Column Name	Type
  EMPLOYEE_ID	INT
  START_DATE	DATE
  END_DATE	DATE
  JOB_ID	SYMBOL
  DEPARTMENT_ID	INT

• locations

  Column Name	Type
  LOCATION_ID	INT
  STREET_ADDRESS	STRING
  POSTAL_CODE	LONG
  CITY	STRING
  STATE_PROVINCE	STRING
  COUNTRY_ID	SYMBOL

• regions

  Column Name	Type
  REGION_ID	INT
  REGION_NAME	STRING

数据文件：

• COUNTRIES.csv
• DEPARTMENTS.csv
• EMPLOYEES.csv
• JOB_HISTORY.csv
• JOBS.csv
• LOCATIONS.csv
• REGIONS.csv

在 DolphinDB 中既可以通过符合标准 SQL 语言习惯的脚本建库建表，也可以通过 DolphinDB 内置的函数接口建库建表。

1. 创建数据库

   create database "dfs://hr" partitioned by HASH([INT, 10])

2. 创建数据表。此处以创建表 "job_history" 为例进行说明。完整的建表脚本见附件：create_db_table_sql.txt

   第一步：创建分布式表

   // 通过标准 SQL 的方式创建
   create table "dfs://hr"."job_history" (
   	EMPLOYEE_ID INT,
   	START_DATE DATE,
   	END_DATE DATE,
   	JOB_ID SYMBOL,
   	DEPARTMENT_ID INT
   )
   partitioned by EMPLOYEE_ID

   第二步：导入数据

   DolphinDB 的 INSERT INTO 语句仅支持内存表的数据追加，对于维度表、分布式表，需要通过 tableInsert 或者 append! 接口进行追加。

   job_history_tmp=loadText(dir+"JOB_HISTORY.csv")
   job_history = loadTable("dfs://hr", "job_history")
   
   job_history.append!(job_history_tmp)

注意：DolphinDB 与其他关系型数据库、NoSQL、NewSQL 数据库不同的是，数据库、编程语言和分布式计算三者融为一体。这种设计使得 DolphinDB 可以一站式轻量化的解决大数据问题。但是，引用数据库和表时，因为与脚本中的变量名可能会冲突，故不能直接用数据库或表名称，必须使用 loadTable 函数加载数据表。

DolphinDB 支持通过 ALTER 语句，对字段进行增加、删除、重命名的操作。

alter table tableObj add columnName columnType;
alter table tableObj drop [column] columnName;
alter table tableObj rename [column] columnName to newColumnName;

上述三个语句也可以分别用 DolphinDB 函数 addColumn, dropColumns!, rename! 实现。

注意：对于分布式表，仅 OLAP 引擎支持删除和重命名操作。

• 新增字段

  给 "employees" 表增加一列全名“FULL_NAME”列，并更新该列的数据。

  employees = loadTable("dfs://hr", "employees") // 加载表 employees
  
  alter table employees add FULL_NAME STRING 
  employees = loadTable("dfs://hr", `employees) // 增加列后需要重新加载表
  update employees set FULL_NAME=FIRST_NAME + " " + LAST_NAME
  select * from employees

  
  

• 重命名字段

  将“FULL_NAME”列重命名为 "EMPLOYEE_NAME"。

  alter table employees rename "FULL_NAME" to "EMPLOYEE_NAME" 
  employees = loadTable("dfs://hr", `employees) // 重命名列后需要重新加载表
  select * from employees

  
  

• 删除字段

  删除上文新增的 "EMPLOYEE_NAME" 字段。

  alter table employees drop EMPLOYEE_NAME 
  employees = loadTable("dfs://hr", `employees) // 删除列后需要重新加载表
  select * from employees

  
  

1. 删除数据库

   drop database if exists "dfs://hr"

2. 删除数据表

   drop table if exists "dfs://hr"."job_history"

谓词指的是返回值为真值的函数，DolphinDB 支持谓词的关键字有：(not) in, (not) like, between, (not) exists, is (not) null。

1. (not) in

   选取 "employees" 表中 "EMPLOYEE_ID" 为 [101, 103, 152] 的对应记录。

   select * from employees where EMPLOYEE_ID in [101, 103, 152];

   选取 "employees" 表中 "EMPLOYEE_ID" 不在 100~150 的对应记录。

   select * from employees where EMPLOYEE_ID not in 100..150;

2. (not) like

   选取 "employees" 表中 "PHONE_NUMBER" 以“515”开头的对应记录。

   select * from employees where PHONE_NUMBER like "515%";

   选取 "employees" 表中 "JOB_ID" 不以“AD”开头的对应记录。

   select * from employees where JOB_ID not like "AD%";

3. between

   统计 2006 年雇佣的职员人数。

   select count(*) from employees where date(HIRE_DATE) between 2006.01.01 and 2006.12.31 // output: 24

4. (not) exists

   注：由于 exists 暂不支持在分布式查询中使用，下述脚本先将维度表和分布式表赋值给内存表再进行查询。

   job_history = select * from loadTable("dfs://hr", "job_history")
   employees = select * from loadTable("dfs://hr", "employees")

   查询记录在 "job_history" 表中的 "employees" 表的职员对应的记录。

   select * from employees where exists(select * from job_history where employees.EMPLOYEE_ID in job_history.EMPLOYEE_ID)

   查询记录不在 "job_history" 表中的 "employees" 表的职员对应的记录。

   select * from employees where not exists(select * from job_history where employees.EMPLOYEE_ID in job_history.EMPLOYEE_ID)

5. is (not) null

   查询存在经理的部门对应的记录。

   select * from departments where MANAGER_ID is not null

   查询任务百分比为空的职员对应的记录。

   select * from employees where COMMISSION_PCT is null

distinct 关键字添加在 select / exec 语句后，用于去除重复值并返回唯一值（distinct value）。

注：支持在分布式查询中使用，但暂不支持 distinct 与 group by, context by 或 pivot by 配合使用。

需要注意 DolphinDB 中存在同名的函数 distinct，若在 SQL 中使用 distinct 函数，则不保证返回结果的顺序，且默认将结果列的列名重命名为 "distinct_colName"。

select distinct COUNTRY_ID from locations // (1)
select distinct(COUNTRY_ID) from locations // (2)

在函数中应用 distinct：统计唯一值的数量。

select count(distinct JOB_ID) from employees // output: 19

对多列应用 distinct：取 "DEPARTMENT_ID", "MANAGER_ID" 的唯一值。

select distinct DEPARTMENT_ID, MANAGER_ID from employees

支持使用 any / all 进行谓词比较，谓词包括：=, !=, >, <, <=, >=.

• any

  查询和采购部门任意职工薪水相同的职工信息。

  select * from employees
  where salary = 
  any(select salary 
   from employees
   where department_id = 30) 
  order by employee_id

  
  

• all

  查询薪水大于等于 IT 部门的最低薪水的员工信息。

   select * from employees
   where salary >=
   all (select salary from employees where department_id=60)
   order by employee_id

  
  

  **注：**目前不支持形如 ALL (1400, 3000) 这样的比较。

支持在 order by 子句中，对空值字段进行 first/last 排序。

查询职工表并按经理 ID 排序，没有经理的职工优先。

select * from employees
order by manager_id asc nulls first

查询职工表并按经理 ID 排序，没有经理的职工放至最后。

select * from employees
order by manager_id asc nulls last

with 子句可以极大地提高 SQL 的可读性，并且通过 with 生成可复用的中间表，提高一些复杂 SQL 的执行效率。

查询财务部门已经工作 5 年以上，薪水 8000 以上的职员列表，并按薪水从高到低排序。该查询先查询财务部门工作 5 年以上的职工，生成临时表 "employees_with_salary_increase"，并在 "employees_with_salary_increase" 的基础上继续查询薪水在 8000 以上的职工信息生成 "employees_with_raise"表，最终在 "employees_with_raise" 表上查询并得到最终结果。

//查询在部门ID为100的员工中，那些在过去五年内（由has_5_years字段表示）有工资增长（即当前工资大于8000）的员工信息。这些信息包括员工ID、工资和是否有五年工作经验。
with  
  employees_with_salary_increase as (  
    select employee_id, salary, year(now()) as current_year,   
           case when  year(now()) - year(hire_date) > 5 then 1 else 0 end as has_5_years  
    from employees  
    where department_id = 100  
  ),  
  employees_with_raise as (  
    select employee_id, salary, has_5_years  
    from employees_with_salary_increase  
    where salary > 8000  
    and has_5_years = 1  
  )  
select employee_id, salary, has_5_years  
from employees_with_raise  
order by salary desc; //查询结果按工资降序排列

如上，在后续的版本中，通过使用 with 语句，我们可以将复杂的查询过程分解成多个步骤，并且将这些步骤的结果进行临时存储。这可以大大简化查询过程，并且提高查询效率。

支持集合的并运算，包括 union、union all。

• union all

  将地区信息表与部门信息表合并查询，查询所有的地区 ID（可能存在重复记录）。

  select location_id from locations 
  union all 
  select location_id from departments
  order by location_id

  
  

• union

  将地区信息表与部门信息表合并查询，查询非重复的地区 ID。

  select location_id from locations 
  union  
  select location_id from departments
  order by location_id

  
  

具体包括 cross join、inner join、left join、right join、full join。除标准 SQL 的关联之外，还拓展了 left semi join（左半连接）。

• cross join（笛卡尔积）

  获取职工的两两信息表，可以基于此结果集做一些关联分析。

  1. SQL 92

     select * 
     from employees a, employees b
     where a.employee_id <> b.employee_id

  2. SQL99

      select * 
      from employees a
      cross join employees b
      where a.employee_id <> b.employee_id

     
     

• inner join

  职工表自关联，获取职工、经理 ID 信息。

  1. SQL92

     select e1.employee_id, e1.manager_id
     from employees e1, employees e2
     where e1.manager_id = e2.employee_id
     order by e1.employee_id, e1.manager_id

     
     

  2. SQL99

     select e1.employee_id, e1.manager_id
     from employees e1 
     inner join employees e2
     on e1.manager_id = e2.employee_id
     order by e1.employee_id, e1.manager_id

     
     

• left join

  职工表自关联获取职工 ID，经理 ID，包括没有上级经理的职工。

  select e1.employee_id, e1.last_name, e2.last_name as manager_name  
  from employees e1
  left join employees e2 
  on e1.manager_id = e2.employee_id

  
  

• left semi join

  left semi join 不属于标准 SQL 的范畴，但应用广泛，且在分布式环境有较好的性能，故予以支持。

  获取部门信息，并展示一个薪水在 2500 以上的员工信息。

  select department_id, department_name,employee_id, first_name, last_name, salary
  from departments 
  left semi join employees // or left semijoin
  on departments.department_id = employees.department_id 
         and employees.salary > 2500
  order by department_id

  
  

• right join

  获取职工薪水、部门信息，包括那些没有所属部门的职工。

   select department_id, employee_id, first_name, last_name, salary
   from departments 
   right join employees
   on departments.department_id = employees.department_id 
          and employees.salary > 2500
   order by department_id

  
  

• full join

  展示所有部门的职工信息，包括没有员工的部门和没有所属部门的员工。

   select department_id, department_name, employee_id, first_name, last_name, salary
   from departments a
   full join employees b
   on a.department_id = b.department_id 

  
  

参与 join 的表类型支持：内存表、分布式表、维度表、非相关子查询（单表、多表 join 的子查询）的临时表。

查找各职员的工作历史信息，测试各类型的表进行关联查询的兼容性情况。

select j.job_id, j.job_title, j.min_salary
  , h.start_date, h.end_date
from jobs j  
left join job_history h 
on h.job_id = j.job_id 

现在你可以将任意多个表 join（之前的版本只能支持两个分区表的 join），来拼接一些大宽表。

关联查询职工表、部门表，获取详细的职工信息，包括个人、经理、部门相关信息。

 select a.employee_id, a.last_name, a.manager_id, b.last_name as manager_name
    , a.department_id, c.department_name
 from employees a
 inner join employees b 
 on a.manager_id = b.employee_id 
 inner join departments c 
 on a.department_id = c.department_id 

查询职工的工作历史，并将 FI_ACCOUNT，AC_ACCOUNT 都视为 AC_ACCOUNT（会计）。

select employee_id, j.job_id, j.job_title, j.min_salary
  , h.start_date, h.end_date
from job_history h 
left join jobs j
on j.job_id = case when h.job_id in ("FI_ACCOUNT", "AC_ACCOUNT") then "FI_ACCOUNT" else h.job_id end  
order by employee_id

支持在 join 的条件列上做如下转换：

• 函数
• case when
• 支持整型与 STRING/SYMBOL 类型字段 join

注：目前仍然不支持 on 1=2 这样的常量表达式（对开发、框架代码可能有一些影响）。

方言模式可以在客户端工具 session 级别进行指定，设置后按指定的 SQL 方言进行解析执行。以 DolphinDB GUI 环境为例：

设置 File → Preferences → Always show sqlStd dropDown(√)

选择对应的 SQL 方言即可，目前支持 Oracle、MySQL、DolphinDB 三种模式。

下面示例以 Oracle 模式按部门统计职工薪水情况，包括部门信息、职工人数等：

select   
  d.department_id, 
  d.department_name,  
  count(a.employee_id) as num_of_employee_id,
  sum(a.salary) as total_salary,  
  avg(a.salary) as avg_salary,  
  max(a.salary) as max_salary,
  decode(a.job_id, 'IT_PROG' , 'Programmer', 'FI_ACCOUNT', 'Accountant', 'Others') as job_title
from employees a 
inner join departments d
on a.department_id = d.department_id  
group by   
  d.department_id,  
  d.department_name,  
  decode(a.job_id, 'IT_PROG' , 'Programmer', 'FI_ACCOUNT', 'Accountant', 'Others') as job_title

上述代码使用了 Oracle 的 decode 函数，除此之外，目前还支持： concat, sysdate, nvl, to_char, to_date, to_number, regexp_like, trunc, asciistr，instr，row_number。通过设置 SQL 方言，使用 Oracle 的应用程序迁移至 DolphinDB 时，SQL 代码迁移成本大大降低。MySQL 模式的使用方式类似，方言选项选择 MySQL 即可。目前 MySQL 只支持 sysdate() 函数，后续版本会逐步提高兼容性。

• Java API

  使用 Java API 时可以在构造 DBconnection 对象时输入 SqlstdEnum, 包括 DolphinDB，Oracle，MySQL。

  package com.dolphindb.sqlstd;
  
  import com.xxdb.DBConnection;
  import com.xxdb.comm.SqlStdEnum;
  import com.xxdb.data.Entity;
  import java.io.IOException;
  
  public class OracleMode {
      public static void main(String[] args) throws IOException {
          DBConnection connection = new DBConnection(SqlStdEnum.Oracle);
          connection.connect("192.168.1.206", 11702, "admin", "123456");
          String sql = String.format(
                  "select employee_id, first_name, last_name, \n" +
                  "  decode(job_id, 'IT_PROG' , 'Programmer', 'FI_ACCOUNT', 'Accountant', 'Others') as jobs_title\n" +
                  "from loadTable(%s, %s) a"
                  , "\"dfs://hr\"", "\"employees\""
          );
          Entity result = connection.run(sql);
          System.out.println(result.getString());
      }
  }

• JDBC

  使用 jdbc 时需要在 url 上增加配置项 sqlStd。

  spring.datasource.url=jdbc:dolphindb://192.168.1.206:11702?databasePath=dfs://hr&sqlStd=Oracle
  spring.datasource.username=admin
  spring.datasource.password=123456
  spring.datasource.driver-class-name=com.dolphindb.jdbc.Driver