数据仓库SQL编码规范

更改习惯是一件不容易的事，但是为了更好地统一，提升开发效率，请大家务必跳出舒适区。规范一旦执行，最重要的是严格遵守它，养成一种习惯。

一般原则

应该

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

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

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

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

避免

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

关键字

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

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

换行

下面的情形需要换行：

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

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

SELECT f.species_name,
        AVG(f.height) AS average_height, AVG(f.diameter) AS average_diameter
   FROM flora AS f
  WHERE f.species_name = 'Banksia'
     OR f.species_name = 'Sheoak'
     OR f.species_name = 'Wattle'
  GROUP BY f.species_name, f.observation_date);

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

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

(SELECT f.species_name,
        AVG(f.height) AS average_height, AVG(f.diameter) AS average_diameter
   FROM flora AS f
  WHERE f.species_name = 'Banksia'
     OR f.species_name = 'Sheoak'
     OR f.species_name = 'Wattle'
  GROUP BY f.species_name, f.observation_date)

  UNION ALL

(SELECT b.species_name,
        AVG(b.height) AS average_height, AVG(b.diameter) AS average_diameter
   FROM botanic_garden_flora AS b
  WHERE b.species_name = 'Banksia'
     OR b.species_name = 'Sheoak'
     OR b.species_name = 'Wattle'
  GROUP BY b.species_name, b.observation_date);

空格

注意下列情况总是加入空格：

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

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

缩进

join语句

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

SELECT r.last_name
  FROM riders AS r
       INNER JOIN bikes AS b
       ON r.bike_vin_num = b.vin_num
          AND b.engine_tally > 2

       INNER JOIN crew AS c
       ON r.crew_chief_last_name = c.last_name
          AND c.chief = 'Y';

子查询

SELECT r.last_name,
       (SELECT MAX(YEAR(championship_date))
          FROM champions AS c
         WHERE c.last_name = r.last_name
           AND c.confirmed = 'Y') AS last_championship_year
  FROM riders AS r
 WHERE r.last_name IN
       (SELECT c.last_name
          FROM champions AS c
         WHERE YEAR(championship_date) > '2008'
           AND c.confirmed = 'Y');

CASE表达式

SELECT CASE postcode
       WHEN 'BN1' THEN 'Brighton'
       WHEN 'EH1' THEN 'Edinburgh'
       END AS city
  FROM office_locations
 WHERE country = 'United Kingdom'
   AND opening_time BETWEEN 8 AND 9
   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]