数据仓库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]