数仓设计规范

1、命名规范

1.1 User权限说明

1.2 Table命名规范

1.3 Column命名规范

1.4字段后缀使用规范

1.5 FUNCTION/PROCEDURE 命名规范

1.6建分区表语句

create table fct_bootstrap
(
   date_id              date comment '日期',
   site_id              int(2) comment '站点ID',
   terminal_id          int(2) comment '平台ID',
   utm_id               int(6) comment '渠道ID',
   gu_id                varchar(100) comment 'gu_id',
   version              varchar(20) comment 'version',
   boot_num             bigint comment '启动次数',
   user_id              bigint comment 'user_id',
   update_time          TIMESTAMP comment '更新时间' default now()
)partition by range(TO_DAYS(date_id))
(partition P20140101 values less than (TO_DAYS('2014-01-01')));

1.7维度视图命名规范

1. 针对有缓慢变化维的维度表（后面备有list）建立视图分别会有V_XXX_CUR(XXX代表维度表，比如DIM_YHD_PM)和V_XXX两个视图，V_XXX_CUR视图代表只取当前有效标记的数据，V_XXX视图代表取所有的数据。对于所需列可根据实际情况而定。见下面例子

2    数据类型定义

1           数仓中整型最小用int<不会使用smallint和tinyint>。
2           金额最小单位存储为分<int或者bigint>。
3           日期时间使用string类型，除特殊情况外，业务系统中的timestamp全部转换为年月日时分秒格式。
4           将过大字段拆分到其他表中。
5           禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。

3    性能规范

1           按需查询，SELECT语句必须显式的指明字段名称，避免 SELECT * FROM …
2           ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。
3           避免使用%前缀模糊查询，例如LIKE "%juanpi"。
4           WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化。
5           WHERE条件中避免非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。
6           避免使用between and，增强代码可读性
7           统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。

4    存储过程开发规范

4.1 代码注释

请参看存储过程模板
调用存储过程的通用shell模板参考

4.2 索引的建立

索引要根据表的使用情况，建立常用的索引。对于查询中，执行计划经常不会使用到的索引要进行删除。索引的字段数不要过多，通常2个为佳。一个表的索引个数也不要太多。

4.3 分区和复合分区的建立

对于数据量在十万级以上的事实表，需要考虑建立分区或者复合分区。建立分区的字段，也通常是查询中的常用字段。

4.4 表和字段的注释

建立的表的过程中，需要加上对表的注释，解释该表的用途。同时，对表中所有字段也加上注释，解释字段含义，字段值的含义，及字段相关的逻辑。
下面给出，注释的几个例子：
对于字段，首先要给出每个字段的含义。如果字段值是数字，请给出数字的含义。如果字段值是金额，请给出金额的单位。另外，有些字段是通过运算得到的，请将字段的运算逻辑在注释中写明。

对于表，首先给出每张表的用途，已经数据的粒度，有效时间，主要存储的数据是哪些。

4.5 INSERT语句

在insert语句中，需要指定插入的字段。例如：
Insert into table_name （column1,column2）Values(x,y);
Insert into table_name （column1,column2） select column1, column2 ...;

4.6 空值

维表必须增加缺省值
维度表 空值主键 默认设置为-999
事实表 维度字段为空 默认设置为-999
       指标字段依具体需求而定

4.7聚合值

      事实表中被汇总的字段，数值型设置为-99，字符型设置为'TOTAL'