更改习惯是一件不容易的事,但是为了更好地统一,提升开发效率,请大家务必跳出舒适区。规范一旦执行,最重要的是严格遵守它,养成一种习惯。
一般原则
应该
- 关键字总是大写,如 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 |
住院 | inpatient | Inp | dwd_fr_ipt_xxx_di/df |
检验 | Lis | Lis | dwd_fr_lis_xxx_di/df |
检查 | ris | Ris | dwd_fr_ris_xxx_di/df |
病案 | Mrs | mrs | dwd_fr_mrs_xxx_di/df |
电子病历 | Emr | emr | dwd_fr_emr_xxx_di/df |
费用 | Fee | fee | dwd_fr_fee_xxx_di/df |
随访 | followup | followup | dwd_fr_followup_xxx_di/df |
财务&结算域 | finance & settlement | fin | dwd_fr_fin_xxx_di/df |
医保 | 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等开头,后面接三位数字,如 |
数据类型
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]