报表建设篇-之sql脚本开发基本样式

前言

本文适合初学者，主要阐述了使用sql开发指标的相关过程，希望对部分人有所帮助。

开发目的

为公司业务提供数据分析与决策；对接应用，提供直观展示效果，方便业务方或领导及时获悉当前业务现状；为当前企业状态提供数据支持，从而为企业的发展指明前进方向。
注：当数据集市的建设在每个阶段会有它的侧重点，比如建设初期，就是想尽快实现所有业务能看到对应的统计的数据，而到了中期则是希望各指标具有准确性，一致性，且能满足及时看数的需要。

开发过程

总述

报表开发一般可分三层：
贴源层：提供明细数据，如 贴源层：交易明细，按照交易流水号为粒度 存储的该内容的详细信息，该层是不应该存储对交易汇总数据的。
中间层：提供汇总数据，对明细数据汇总，出的如按照各客户当日交易，汇总的交易笔数，交易金额；按照客户进行当月汇总的交易笔数，交易金额，按照地区汇总的交易笔数，交易金额等，这些会作为中间表加工出来
应用层：提供直接应用于前端展示的数据。而对于应用表的加工一般是使用中间表进行处理的，会节省加工时间，可以拿来继续使用。

需求来源

接业务需求，按照业务提供的需求分析需要开发的报表有哪些字段，确认字段具体范围与含义。

整理需求文档

通过业务提出的各字段具体含义分析取值来源，具体到来源表名称字段与对该来源表的删除逻辑。

开发目标表，创建表结构

根据业务需求，提供各需求字段的业务口径分析技术口径，具体到该字段的取数源表，取数字段，对应的筛选逻辑。
例：
表名：btrb_sl_cust_mon_trx_sr 客户月交易统计表
表结构：dw_stat_dt date priamry key 统计日期 ，cust_id varchar(20) priamry key 客户号，sex char(1)性别，trx_amt decimal(18,2) 交易金额，trx_cnt int 交易笔数
这里面会包括表结构的确认，表主键的确认，以及还需考虑以后的表使用，表数据扩展问题。

开发该目标表的脚本

定义参数

库名参数如：$od{s}_{d}ata，${pdm_data};$pd{m}_{v}iew;时间参数${vt_dat_dt}:表示当天，${v_start_mon_dt}:表示月初，或其他便于循环参数。

创建临时表

Create multiset volatile table tmp001
as(dw_snsh_dt date parimary key
,cust_id varchar(20) not null
,sex char(1) )
);
Create multiset volatile table tmp002
as(dw_dat_dt date parimary key
,cust_id varchar(20) not null primary key
,trx_amt decimal(9,2)
,trx_cnt int )
);
Create multiset volatile table vt_res as(
Dw_stat_dt date priamary key
Cust_id varchar(20) parimary key
Sex char(1)
Trx_amt decimal(18,2)
Trx_cnt int
)

源表中取对应字段插入临时表

Step1：Insert into tmp001
Select dw_snsh_dt ,cust_id,sex from $od{s}_{d}ata.pd{m}_{c}us{t}_{b}as{e}_{i}n{f}_{s}whered{w}_{s}ns{h}_{D}t=${DW_DAT_DT};
If error,exception 1…
解释：pdm_cust_base_inf_s有30个字段，其存储数据有五年的数据记录，我们只需要用3个字段且只需要当天数据所以只取三个，且取当天日期。
Step2
Insert into tmp002
Select last_day(dw_dat_dt)　as　dw_stat_Dt,cust_ID,sum(trx_amt) as trx_amt,sum(trx_cnt) as trx_cnt from $od{s}_{d}ata.pd{m}_{c}us{t}_{t}r{x}_{e}vtwhered{w}_{d}at{e}_{d}t>=‘${dw_mon_start}’ and dw_date_dt <= ‘${dw_dat_dt}’ group by last_day(dw_dat_dt),cust_id
If error,exception 1…
解释：该段表示从该表取当天的客户交易数据，而该表每天有100万交易数据，数据量很大，且该表包括交易相关信息40个，之所以会直接加工是避免内存占用过大，加载不了过多数据报错或占用过多临时资源影响执行效率，所以直接进行了聚合，。一般来讲还是先从表中直接取数做成临时表再加工较好，应避免通过数仓中的表直接进行加工

Step3：
结果表加工：
Insert into vt_res
Select ‘${dw_dat_dt}’ as dw_mon_dt --统计月份
,a.cust_Id --客户号
,a.sex --性别
,coalesce(b.trx_amt,0) as trx_amt --交易金额
,coalesce(b.trx_cnt) as trx_cnt --交易笔数
From tmp001 a
Left join tmp002 b
On a.cust_id =b.cust_ID
解释：通过做关联取出对应客户的月交易笔数，月交易金额

删除当日数据，支持当天数据重跑，并把数据插入目标表中

Step4:删除当日数据，支持数据重跑
Delete from $btr{b}_{d}ata.btr{b}_s{l}_{c}us{t}_{m}o{n}_{t}r{x}_{s}rWhered{w}_{s}ta{t}_{d}t=’${dw_dat_dt}’
Step5 :向目标表插入结果数据
Insert into ${btrb_data}.btrb_sl_cust_mon_trx_sr
Select dw_mon_dt，cust_id,sex,trx_amt,trx_cnt from vt_res where trx_amt>0 or trx_cnt>0
解释：通过结果临时表数据插入目标表，剔除交易笔数，金额均为0的数据（该数据存储无意义），减少空间浪费。
为何要把第四第五步放一起？
一般会把这两个步骤做成事务，既数据插入不成功，当天数据是不会被删除的，否则若把先数据删除后，数据又没插入成功，但又没法及时处理掉这个问题，那就有些得不尝失了。
最后：step1,step2 可以统称为获取数据来源 step3 为对来源数据进行加工，step4向目标表插入结果数据。（step2严格说其实包括了两部分：数据获取与数据加工）

表命名规范

维度层： 库名_层级标识_<自定义表名>[HD（手工维护可选）]
基础层： 库名_层级标识<框架分类><自定义表名><表形态>
服务层(标签)： 库名_层级标识_<业务模块>[业务子类（可选）]<自定义表名><表形态>
服务层(宽表)： 库名_层级标识<自定义表名>_<表形态>
框架每个公司不一样：比如客户组件，信用卡组件，存款，贷款等等；可先定义好
业务模块也可根据公司现有业务分类：比如贸易，信贷，结算，收款等
表形态即属于什么类型的表：如事件表（增量表），快照表（全量表），统计表（即可以是增量统计，也可以是全量统计），拉链表。