数据仓库SQL编码规范

更改习惯是一件不轻易的事，但是为了更好地统一，提拔开发效率，请各人务必跳出舒服区。规范一旦实验，最紧张的是严格遵守它，养成一种习惯。


一般原则


应该

• 关键字总是大写，如 SELECT 和 WHERE
  
• **换行时，一般关键字右对齐，如许能确保列左对齐。有助于快速扫描获取整个语句的寄义。
  
• 保证代码简洁明了、没有多余的 SQL —— 好比非必要的引号或括号，或者可以推导出的 WHERE 子句
  
• 合理地利用空格和缩进来加强可读性
  
• 必要时在 SQL 代码中加入解释
  

• 
  
  
  
  • 优先利用 C 语言式的以 /* 开始以 */ 结束的块解释
    
  • 或利用以 -- 开始的行解释，并在末端换行
    
  
  

1. SELECT file_hash  -- stored ssdeep hash
2.   FROM file_system
3. WHERE file_name = '.vimrc';

复制代码

1. /* Updating the file record after writing to the file */
2. UPDATE file_system
3.    SET file_modified_date = '1980-02-22 13:19:01.00000',
4.        file_size = 209732
5. WHERE file_name = '.vimrc';

复制代码

制止

• 驼峰命名法 —— 它不适合快速扫读
  
• 复数形式 —— 尽量利用更自然的集合术语。好比，用“staff”更换“employees”，或用“people”更换“individuals”
  

关键字


关键字总是大写，如 SELECT 和 WHERE。AS做为别名利用时，不可省略。

1. SELECT model_num
2.   FROM phones AS p
3. WHERE p.release_date > '2014-09-30';

复制代码

换行


下面的情形必要换行：

• 在 AND 或 OR 前
  
• 在分号后（分隔语句以提高可读性）
  
• 在每个关键字定义之后
  
• 将多个列构成一个逻辑组时的逗号后
  
• 将代码分隔成相关联的多个部分，帮助提高大段代码的可读性
  

通过换行保证一行只有一个类型的关键字，除了AS。如下所示：

1. SELECT f.species_name,
2.         AVG(f.height) AS average_height, AVG(f.diameter) AS average_diameter
3.    FROM flora AS f
4.   WHERE f.species_name = 'Banksia'
5.      OR f.species_name = 'Sheoak'
6.      OR f.species_name = 'Wattle'
7.   GROUP BY f.species_name, f.observation_date);

复制代码

对于条件类型的关键字，如OR，AND，保证其后所跟列名与上一行对齐。

对于UNION ALL等必要将两段SQL拼接在一起的关键字，其上下空一行。如下所示：

1. (SELECT f.species_name,
2.         AVG(f.height) AS average_height, AVG(f.diameter) AS average_diameter
3.    FROM flora AS f
4.   WHERE f.species_name = 'Banksia'
5.      OR f.species_name = 'Sheoak'
6.      OR f.species_name = 'Wattle'
7.   GROUP BY f.species_name, f.observation_date)
9.   UNION ALL
11. (SELECT b.species_name,
12.         AVG(b.height) AS average_height, AVG(b.diameter) AS average_diameter
13.    FROM botanic_garden_flora AS b
14.   WHERE b.species_name = 'Banksia'
15.      OR b.species_name = 'Sheoak'
16.      OR b.species_name = 'Wattle'
17.   GROUP BY b.species_name, b.observation_date);

复制代码

空格


注意下列环境总是加入空格：

• 在等号（=）前后
  
• 在逗号（,）后
  
• 成对的单引号（'）前后，除非在括号中或背面是逗号 / 分号
  

1. SELECT a.title, a.release_date, a.recording_date
2.   FROM albums AS a
3. WHERE a.title = 'Charcoal Lane'
4.     OR a.title = 'The New Danger';

复制代码

缩进


join语句


必要用到JOIN时，应该进行缩进。如下所示：

1. SELECT r.last_name
2.   FROM riders AS r
3.        INNER JOIN bikes AS b
4.        ON r.bike_vin_num = b.vin_num
5.           AND b.engine_tally > 2
7.        INNER JOIN crew AS c
8.        ON r.crew_chief_last_name = c.last_name
9.           AND c.chief = 'Y';

复制代码

子查询

1. SELECT r.last_name,
2.        (SELECT MAX(YEAR(championship_date))
3.           FROM champions AS c
4.          WHERE c.last_name = r.last_name
5.            AND c.confirmed = 'Y') AS last_championship_year
6.   FROM riders AS r
7. WHERE r.last_name IN
8.        (SELECT c.last_name
9.           FROM champions AS c
10.          WHERE YEAR(championship_date) > '2008'
11.            AND c.confirmed = 'Y');

复制代码

CASE表达式

1. SELECT CASE postcode
2.        WHEN 'BN1' THEN 'Brighton'
3.        WHEN 'EH1' THEN 'Edinburgh'
4.        END AS city
5.   FROM office_locations
6. WHERE country = 'United Kingdom'
7.    AND opening_time BETWEEN 8 AND 9
8.    AND postcode IN ('EH1', 'BN1', 'NN1', 'KW1');

复制代码

模型分层规范


规范化数据分层逻辑，制止数据位置杂乱；高内聚松耦合；便于project管理；模型规范实用于地区医疗和单院版本，终极做到统一。

数据层次分别为三层：业务层(ods)、数据仓库中间层(dwd/dws/dim)、应用层(adm)，project新建一个frdata或者放到alijk_bigdata内里。tmp临时表作为辅助，用来存放临时效果。

发起差异地区的项目单独规划odps项目。

术语表明

• ODS：Operational Data Store，业务操作层数据。
  
• CDM：Common Data Model，通用数据模型（数据中间层），包含DWD和DWS。
  
• DWD：Data Warehouse Detail，数据仓库明细层数据。
  
• DWS：Data Warehouse Summary，数据仓库汇总层数据。
  
• ADM：Application Data Model，面向应用层数据
  

业务ODS层(ods)


ODS层，所有数据同步要求全部放入这个项目，保举利用DATAX工具来操作。

• 按源头业务系统分割的数据原始组织层；
  
• 数据驱动；
  
• 与源系统数据保持划一；
  

数据中间层(cdm)


中间层模型和公共指标层。新的中间层模型（DWD/DWS)请放入这个项目进行规范管理。

• 公共维度层：基于维度建模理念思想，建立整个数据仓库的划一性维度；
  
• 明细粒度究竟层：以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层究竟表，结合数据仓库的数据利用特点，将明细究竟表的某些紧张维度属性字段做适当冗余，也就是宽表化处置处罚；
  
• 公共汇总粒度究竟层：以分析的主题对象作为建模驱动，基于上层的应用和产物的指标需求，构建公共粒度的汇总指标究竟表，以宽表化本领物理化模型；
  
• 数据生命周期、数据质量保障；
  

应用层ADM(frdata)


应用数据层，如果你要面对一个数据产物/应用或者有以前的工作，还是可以利用这个项目。

• 面向特定产物需求/应用布局构建的特定格式数据组织；
  
• 功能需求驱动；
  
• 模型布局以应用便捷快速为组织原则；
  

其他数据

• fr_asset: 数据资产库，安全品级较高，专业用户利用（指标沉淀的资产）。
  
• fr_analyst: 数据分析职员利用
  
• fr_tech: 一些算法应用利用
  

模型计划规范


计划约定

• 统计表允许多个维度，表命名按照维度名称首字符排序，首字符划一时，比对第2个字符
  
• 模型复用性：先计划公用模型，各业务优先利用公用模型指标，个性化指标可分别计划模型
  
• 命名规范化：
  

• 
  
  
  
  • dwd／dws层命名只控制头（业务域＋数据域）尾（刷新周期及数据粒度）
    
  • adm表命名： adm业务域名称***
    
  
  

• 指标数据（通用指标、明细、比率等）统一下沉到dwd/dws实现，分身性能，原则上不超过1个半小时
  

层次调用约定


应用层优选调用DW公共层数据，已经存在中间层数据，不允许应用层跨过中间层从ODS层重复加工数据。一方面，中间层团队应该积极了解应用层数据的建设需求，将公用的数据沉淀到公共层，为其他团队提供数据服务；另一方面，应用层团队也需积极共同中间层团队进行持续的数据公共建设的改造和迁徙。必须制止出现过度的ODS层引用和不合理的数据复制和子集合冗余。

表命名规范


目前ODS表统一落地在差异业务域的ods目录下，利用odps的datax工具进行操作，命名由工具自动制定。

目录格式根据业务域定义。

类型	表名格式	示例
逐日全量分区	s_<上游逻辑表名>	cnods.s_emr_xml
逐日增量分区	s<上游逻辑表名>delta	cnods.s_mz_guahao_delta
小时归并同步	s<上游逻辑表名>hh	cnods.s_data_area_hh

• 中间层DWD表命名规范如下：
  

• 
  
  
  
  • dwd_fr_<数据域名称>_<业务过程名称｜自定义表名>_<刷新周期><存储策略>
    
  
  

• 中间层DWS表命名规范如下：
  

• 
  
  
  
  • dws_fr_<数据域名称>_<粒度｜自定义表名>_<时间维度>
    
  
  

• 中间层DIM维表命名规范如下：
  

• 
  
  
  
  • dim_fr_<数据域名称>_<自定义表名>
    
  
  

• 数据集市层DM表命名规范如下：
  

• 
  
  
  
  • dm_fr_<自定义表名>
    
  
  

• 应用层ADM表命名规范如下：
  

• 
  
  
  
  • adm_<业务域名称|数据产物名称>_<自定义表名>
    
  
  

业务域定义（单医院忽略、地区平台业务域放到表分区）

业务域中文名	业务域命名	业务域命名（缩写）	命名示例
浙二	浙江省第二人民医院	zher
余杭	余杭区卫健委	yhwjw
桐庐	桐庐卫健委	tlwjw
余杭区第一人民医院	余杭区第一人民医院	yh1

数据域定义

数据域中文名	数据域命名	数据域命名（缩写）	命名示例
门诊	outpatient	outp	dwd_fr_opt_xxx_di/df dws_fr_opt_xxx_1d
住院	inpatient	Inp	dwd_fr_ipt_xxx_di/df dws_fr_ipt_xxx_1d
检验	Lis	Lis	dwd_fr_lis_xxx_di/df dws_fr_lis_xxx_1d
检查	ris	Ris	dwd_fr_ris_xxx_di/df dws_fr_ris_xxx_1d
病案	Mrs	mrs	dwd_fr_mrs_xxx_di/df dws_fr_mrs_xxx_1d
电子病历	Emr	emr	dwd_fr_emr_xxx_di/df dws_fr_emr_xxx_1d
费用	Fee	fee	dwd_fr_fee_xxx_di/df dws_fr_fee_xxx_1d
随访	followup	followup	dwd_fr_followup_xxx_di/df dws_fr_followup_xxx_1d
财务&结算域	finance & settlement	fin	dwd_fr_fin_xxx_di/df dws_fr_fin_xxx_1d
医保	insurance	Ins
药品	Drug	Drug
患者	Patient	Patient
体检	CheckUp	chp

刷新周期

刷新周期	刷新周期命名	刷新周期命名（缩写）	形貌
天	day	d	每天更新数据
周	week	w	每周更新数据
月	month	m	每月更新数据
季度	quarter	q	每季度更新数据
年	year	y	每年更新数据
小时	hour	h	每小时更新数据
实时	realtime	r	实时更新数据

存储策略

策略类型	后缀缩写
实时全量	_rf
实时增量	_ri
15分钟全量	_qhf
15分钟增量	_qhi
小时全量累计	_hf
小时当天累计	_hh
小时增量	_hi
小时月增量	_hm
逐日全量	_df
逐日增量	_di
每全面量	_wf
每周增量	_wi
每月全量	_mf
每月增量	_mi
每季度全量	_qf
每季度增量	_qi
每年全量	_yf
每年增量	_yi

注意：小时分区表，根据同步方式的选择，可以分为零点截止当前小时分区落盘(hh)，每小时净增量分区落盘(hi)，每小时存放全量数据(_hf)

时间维度

中文名	时间维度命名	时间维度命名（缩写）	形貌
最近1天	1day	_1d	最近1天
最近3天	3day	_3d	最近3天
最近7天	1week	_1w	最近7天
最近14天	2week	_2w	最近14天
最近30天	1month	_1m	最近30天
最近60天	2month	_2m	最近60天
最近90天	3month	_3m	最近90天
最近180天	6month	_6m	最近180天
180天以前	before 6month	_b6m	180天以前
自然周	calendar week	_cw	自然周
自然月	calendar month	_cm	自然月
自然季度	calendar quarter	_cq	自然季度
自然年	calendar year	_cy	自然年
财年	finance year	_fy	财年
半财年	half finance year	_hfy	半财年
汗青截至当日	start to day	_std	汗青截至当日
自然年初截至当日	year to day	_ytd	自然年初截至当日
自然季度初截至当日	quarter to day	_qtd	自然季度初截至当日
自然月初截至当日	month to day	_mtd	自然月初截至当日
自然周初截至当日	week to day	_wtd	自然周初截至当日
财年年初截至当日	finance to day	_ftd	财年年初截至当日
最近1小时	1hour	_1h	最近1小时
0点截至当前小时	day to hour	_dth	0点截至当前小时
0点截至当前	day to realtime	_dtr	0点截至当前
小时截至当前	hour to realtime	_htr	小时截至当前
分钟截至当前	minute to realtime	_mtr	分钟截至当前
未来7天	predict 1week	_p1w	未来7天
未来4周	predict 1month	_p1m	未来4周
混合多天	nd	_nd	混合多天
报表周	report week	_rw	上周四到本周三
15分钟	quarter hour	qh	一刻钟

字段级命名规范


原则上，除了一些约定的缩写以外，每个属性/列的名字均可自行确定，英文名应尽量是字段的全称，单词全部小写，单词间用下划线。一些特殊的约定如下：

属性字段


属性字段指的是文本字段，好比患者姓名，患者年龄等。这些字段不加前缀和后缀，尽可能利用通用的英文单词表现。 如果字段直接来自上游ODS表，则保持和源系统字段名称划一；与ODPS关键字冲突时加一个”col”后缀，即：源字段名col；一些有歧义的地方可以适当加上前后缀

示例：患者入院诊断编码，在frdata.dwd_fr_inp_diagnosis表中为diag_code，如果在计划鄙俚表字段的时间，可以利用inp_diag_code与其他诊断code区分

指标字段


定义：底子指标＝修饰词（可选）＋原子词＋时间修饰
示例：最近7天无线端付出金额 修饰词（wl）＋原子词（pay_ord_amt）＋时间修饰（1w） wl_pay_ord_amt_1w

计数字段


计数字段指的是单量、件数、用户数等整数值型信息字段，计数主体指计数运算的对象。字段命名格式为：<计数主体>_cnt
示例：门诊挂号人次，字段名为outp_ register_cnt；

比例字段


比例字段指的是占比，百分率等double值型信息字段。字段命名格式为：<计数主体>_rate
示例：如果门诊专家挂号比例名为opt_register_expert_rate

费用字段


各类费用，收入相关字段。命名格式为：<标识主体>_amt
示例：‘住院手术金额’字段名为 ipt_operate_amt

标识字段


标识字段指的是取值为'Y/N'的二元值字段。字段命名格式为：is_<标识主体>
示例：‘是否活泼小件员’字段名为 is_active

时间字段


时间字段是指数据类型为Sting,格式YYYY-MM-DD HH-MI-SS 带有小时分钟秒等精度的字段。字段命名格式为：<业务主体>_time
示例：手术时间，字段名为operation _time

日期字段


日期字段是指数据类型为String，格式为YYYYMMDD的准确到天的时间字段。字段命名格式为：<业务主体>_date
示例：‘统计日期（天）’字段名为 stat_date

分区字段


分区字段的寄义必要在ODPS表的环境阐明中进行明确。分区深度不发起超过3层。

类型	字段名	备注
日／报表周／周分区	ds	格式为yyyymmdd 默认业务运行日期（bizdate）
月／季度／年分区	ds	格式为yyyymmdd 默认月末了一天，季度末了一天，年末了一天
小时分区	hh	格式为00..23
分钟分区	mm	格式为00，15，30，45
业务分区	例如：type	按照业务需求进行分区。例如异常类型，约定为4位长度的字符串，以大写字母ABC等开头，背面接三位数字，如E001

数据类型


ODS层的数据类型基于源系统数据类型转换，转换规则如下：

Mysql数据类型	Odps数据类型
TINYINT/SMALLINT/ MEDIUMINT/ INTEGER/ BIGINT	Bigint
FLOAT/ DOUBLE/ DECIMAL	Double  有精度要求可用decimal
LONGTEXT/TEXT/VARCHAR/ CHAR	String
DATE	String(格式：YYYYMMDD)
DATETIME	String(格式：YYYY-MM-DD HI24:MM:SS)
TIMESTAMP	TIMESTAMP(格式：YYYY-MM-DD HI24:MM:SS.sss)
Oracle数据类型	Odps数据类型
Number	ID转换为bigint，根据现实数据，如果是浮点数则利用double，默认利用bigint。
VARCHAR2/VARCHAR	String(格式：YYYYMMDD)
DATE	String(格式：YYYY-MM-DD HI24:MM:SS)
CLOB	String
TT日志剖析前数据类型	TT日志剖析后数据类型
STRING大字段	String 所有字段

CDM层及ADM层的数据类型按以下标准实验：

数据类型	场景
Bigint	数字型关联字段，如会员ID、商品ID等；没有小数位的统计值
Double	有小数位的统计值
String	除上述环境

注意：所有度量字段必须利用bigint或者double类型，不允许利用string；上游数据库业务主键为bigint类型，中间层计划时也用bigint.

其他命名规范


视图命名规范


优先思量物理化，视图命名规范如下：<源表名>_v

临时表命名规范


测试、数据分析、临时取数等临时表命名规范：tmp_[工号]_<表名>

脚本内临时表命名规范


周期性使命发起接纳分区表tmp<目的表名>[n]

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。