数据仓库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工具进行操作，命名由工具自动制定。

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

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

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

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

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

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

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

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

• • dm_fr_<自定义表名>

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

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

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

数据域定义

刷新周期

存储策略

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

时间维度

字段级命名规范

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

属性字段

属性字段指的是文本字段，比如患者姓名，患者年龄等。这些字段不加前缀和后缀，尽可能使用通用的英文单词表示。 如果字段直接来自上游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层。

数据类型

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

CDM层及ADM层的数据类型按以下标准执行：

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

其他命名规范

视图命名规范

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

临时表命名规范

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

脚本内临时表命名规范

周期性任务建议采用分区表tmp<目标表名>[n]