导读:如何编写优雅可度性高的SQL？好的开发规范是必不可少的。

1. 规范目的

• 养成良好的编程习惯
• 写出清楚、易懂、易维护的程序代码
• 提高代码质量与生产率
• 减少软件编码中的不必要的错误

2. 开发规约

• 所有查询必须指定具体字段
• 统一采用小写字母编码(不包含参数、注解)，遵循SQL 92语法标准
• 所有查询表名必须添加数据库名+表名
• HQL文件一定要设置任务名名称
• HQL【会话临时表】必须要在末尾删除
• 别名一定要加AS关键字,多表关联必须要AS别名
• 字符串用单引号包括
• 不能使用
• SQL 必须经过验证，更新数仓再执行
• SQL代码中严格禁止使用硬编码，排除脏数据清洗编码、默认值。

3.1. 注释

此注释格式适用于SQL。

3.1.1. 头部注释

注释样例
– -----------------------------------------------------------------------
– 业务范围: 常规任务
– 创建人: username
– 创建日期: 2020-01-01
– 脚本用途: 这是一个测试样例
– 执行引擎: hive
– 执行周期: 天
– 对应报表: 这是一个测试报表
– 质量监控: 这是一个测试监控
– 变更记录:
– 变更日期 变更人 变更描述
– 2020-02-02 test 注释相应表
– -----------------------------------------------------------------------

3.1.2. 脚本注释

脚本注释可分为：分段注释、临时表注释、语句块注释

• 注释样例

-- 以下属于临时处理IP地址库(分段注释)
 
-- 此表用来临时存放常用IP相关数据(临时表注释)
drop table if exists temp_db.temp_tableName_yyyyMMdd;
create table temp_db.temp_tableName_yyyyMMdd
as
select 
 ip
,city_name
-- 为空时则使用xxx当默认值(语句块注释)
,nvl(city_id,'xxx') as city_id
from temp_db.temp_tableName
where p_dt='2019-04-15'
-- IP地址必须以数字开头(语句块注释)
and ip rlike '^[\\d]+'

3.2. DQL

3.2.1. 查询

特别说明，查询表全部字段，可以使用select * 且在代码上方备注"查询全部字段"，否则需要使用select 具体字段。

– 示例
– 查询全部字段
select
*
from db.table_name

常规查询 - SELECT关键字后换行 - 查询时必须指定具体字段 - 多个字段通过换行进行分割 - 首个字段前面空一空，非首个字段前面则写逗号(以便排查) - FROM后面必须接具体表名

– 示例
select
pk_column
,column1
,column2
,column3
from db.table_name

子查询 - 查询嵌套时子查询前面必须要有两个空格 - 子查询必须取别名，不能使用匿名方式 - 子查询与括号不能在出现在同一行。

– 示例
select
m.column1
,m.column2
,m.column3
from (
select
m.column1
,m.column2
,m.column3
from db.table_name_a as m
) as m

3.2.2. 关联

• 多表关联查询时必须使用[别名+字段]，不能直接写字段名
• 表关联时关键字必须全写，不能简写，如：INNER JOIN写成JOIN
• 表关联时必须先换行再写INNER JOIN关键字
• 表关联时换行后空两格ON关键字后必须紧接着关联条件
• 如果是子查询则直接写ON关键字后必须紧接着关联条件
• 如果关联条件超过两个必须换行，每行各一个关联条件且前面空两格，见示例3、示例4

-- 示例1
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join db.table_name_a as a
  on m.pk_column=a.id and m.column1=a.id 
 
-- 示例2
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join (
  select
   id
  ,name
  from db.table_name_a
) as a on m.pk_column=a.id
 
-- 示例3
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join db.table_name_a as a
  on m.pk_column=a.id
  and m.pk_column=a.id
  and m.pk_column=a.id
 
-- 示例4
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join (
  select
   id
  ,name
  from db.table_name_a
) as a on m.pk_column=a.id
  and m.pk_column=a.id
  and m.pk_column=a.id

3.2.3. 条件

• 优先将数据过滤最多的写在第一位
• 先换行再写关键字，然后再写过滤条件，多个过滤条件必须先换行再写关键字+条件

-- 示例
select
 m.pk_column
,m.column1
,m.column2
,m.column3
,a.column4
from db.table_name as m
inner join db.table_name_a as a
on m.pk_column=a.id
where a.column7='xxxx'
and a.column8='xxxx'

3.2.4. 分组

• 先换行再写关键字

• 第一个分组字段先空格再写字段名，非首个字段前面则写逗号(以便排查)

-- 示例
select
 m.column1
,m.column2
,m.column3
,count(1) as cnt
from db.table_name as m
group by
 m.column1
,m.column2
,m.column3

3.2.5. 合并

• 多结果集合并时，两结果集之间必须空一行再写关键字

-- 示例
select
 m.column1
,m.column2
,m.column3
from db.table_name_a as m
 
union all
select
 m.column1
,m.column2
,m.column3
from db.table_name_b as m

3.2.6. 聚合筛选

• 脚本尽量不用

-- 示例
select
 m.column1
,m.column2
,m.column3
,count(*) as pv
from db.table_name as m
group by
 m.column1
,m.column2
,m.column3
having count(*)>1

3.2.7. 排序

• 先换行再写关键字。
• 第一个排序字段先空格再写字段名，非首个字段前面则写逗号(以便排查)
• 必须明确指定排序关键字(DESC|ASC)

-- 示例
select
 m.column1
,m.column2
,m.column3
from db.table_name as m
order by
 m.column1 desc
,m.column2 asc

3.3. DML

Hive只做INSERT操作，MySQL可做INSERT、UPDATE、DELETE操作

3.3.1. INSERT

• hive

语法1：
insert overwrite table table_name [partition(column)]
select 
 column1
,column2 
from table_name
;
 
语法2：
insert into table table_name(column[...])
select 
 column1
,column2 
from table_name
;
 
语法3：
insert into table table_name(column[...])
values(column[...])
;

• MySQL

3.3.2. UPDATE

3.3.3. DELETE

3.4. DDL

3.4.1. 常规表

• 表必须指定存储类型、字段分割符、换行符、表描述
• 首个字段空一格，其余换行使用逗号+字段
• 字段必须有字段描述，字符必须是英文字符，编码类字段描述格式为：1:是,2:否
• 字段类型必须与源字段类型一致
• 首先必须注释表删除语法(防止表名重复)
• 如果是对应有固定报表、数据分析平台的dm表，是维度字段的字段描述后面要增加'维度'二字
• 其余未描述规范与示例保持一致

3.4.2. 临时表

会话临时表

• 先删除后创建原则

常规临时表

• 参考常规建表规范

3.5. DCL

目前

只针对MySQL数据库操作，目前此操作由运维管理。

结语：后续在开发过程中会不断迭代与修改，最终将规范形成平台化。