电信计费系统ETL开发

本项目进行电信计费系统开发，基于ETL工具Informatica，SQL，数据库Oracle和PL/SQL进行开发，数据来源微信公众号数据爱分享的数据。

业务需求分析：

电信运营情况分析：从各业务系统数据(用户表、地区表、通话表)分析公司运营情况（本项目只分析通话，暂不分析流量）
报表结果：
统计各维度与各指标的用户量与运营收入情况
维度（时间，地区，产品）
指标（用户数，运营收入）

电信计费系统数据
用户表(oltp)
地区表(oltp)
通话表(oltp)
产品表(file_csv)

开发流程
OLTP------->ODS-------->EDW--------->DM

计费系统开发流程及开发逻辑：
A：地区表处理
B：开发过程
1： 先计算每个号码每个月的累计通话时长
SQ 组件来进行计算
2： 关联客户表（得到用户所使用的产品及地区）,
关联产品表（得到免费通话时长，及通话计算标准）
计算每个号码每个月的费用
C: 根据地区维度，产品维度，来统计用户数及整个收入费用

业务系统数据抽取至 ODS 层

Oracle用户下用户表(oltp)、地区表(oltp)、通话表(oltp)以及产品表(file_csv)抽取至ODS层

创建任务，修改任务映射源表与目标表的连接数据库层，通过任务启动工作流

A：地区表处理
地区维度表进行相应的处理
将地区分为 区局--->端局(镇)--->社区
SQL开发思路分析

select * from ods_department where dept_type_no=5;--社区
select * from ods_department where dept_type_no=4;--端局
select * from ods_department where dept_type_no=2;--区局

将区局、端局、社区整合到地区维度表

select quju.dept_id,quju.dept_name,
       duanju.dept_id,duanju.dept_name,
       shequ.dept_id,shequ.dept_name
from
(select dept_id,dept_name,level_no
from ods_department where dept_type_no=2)quju,--区局
( select dept_id,dept_name,level_no
from ods_department where dept_type_no=4)duanju,--端局(镇)
( select dept_id,dept_name,level_no
from ods_department where dept_type_no=5)shequ--社区
WHERE substr(duanju.level_no,1,3)=quju.level_no
and substr(shequ.level_no,1,6)=duanju.level_no

CREATE TABLE edw_department_dim
(
      quju_id VARCHAR2(40),
      quju_name VARCHAR2(40),
      duanju_id VARCHAR2(40),
      duanju_name VARCHAR2(40),
      shequ_id VARCHAR2(40),
      shequ_name VARCHAR2(40)
)

将源表与目标表拖入mapping，编辑SQ组件，保留和目标表相同的字段(保留id和name)，记住端口字段顺序与SQL输出字段保持一致(即目标表quju端口是第一顺序，与SQL的输出字段quju是第一顺序保持一致)，连接映射关系，ctrl+s保存

SELECT call_id,
       call_date,
       phone_no,
       begin_time,
       time_long,
       SUM(time_long)OVER(PARTITION BY phone_no,to_char(call_date,'yyyy-mm')
                          ORDER BY to_char(call_date,'yyyy-mm-dd'),begin_time)leiji
--使用窗口函数的聚合函数sum()，以电话号码、月份进行分组
，以日期和开始通话时间进行排序，统计出每个用户每月的累计通话时长
FROM ods_Call_Record 
WHERE phone_no = '13421728761' AND to_char(call_date,'yyyy-mm')='2018-03'  
 --测试，一个用户一个月的通话时长

查看数据，可以知道号码为13421728761的用户，在2018-03月份中的通话时长为218分钟

该号码的用户在用户表(ods_cust_info)中查询套餐为4G58，在产品表中查询该套餐免费通话时长为30分钟和每分钟0.19的话费计费

获取该号码用户累计的通话时长和话费，知道该用户的免费时长为30分钟和每分钟计费为0.19/min

WITH CALL_time_long AS (SELECT call_id,
       call_date,
       phone_no,
       begin_time,
       time_long,
       SUM(time_long)OVER(PARTITION BY phone_no,to_char(call_date,'yyyy-mm')
                          ORDER BY to_char(call_date,'yyyy-mm-dd'),begin_time)leiji
FROM ods_Call_Record 
WHERE phone_no = '13421728761' AND to_char(call_date,'yyyy-mm')='2018-03')   

SELECT phone_no,
       call_date,
       begin_time,
       time_long,
       leiji,
       CASE WHEN leiji>30 THEN    --免费通话时长为30分钟
            CASE WHEN (leiji-30-time_long)<0 THEN (leiji-30)*0.19
            ELSE time_long*0.19 END    --累计时长-30-当次通话时长小于0,说明当次通话时长还有免费时长，大于0说明当次通话时长没有免费时长
       ELSE 0 END call_fee    --在免费时长里没有产生费用
FROM CALL_time_long

create table EDW_CALL_FEE
(
  phone_no  VARCHAR2(20),
  month_id  VARCHAR2(20),
  product   VARCHAR2(20),
  adress    VARCHAR2(40),
  fee       NUMBER(20,2),
  basic_fee NUMBER(10)
)

需要源表ods_call_record、ods_cust_info、ods_product，同目标表一起拖入mapping进行开发，编辑ods_call_record的SQ组件，实现通话时长累计功能。

2、实现多表连接，获取相应需求字段

关联客户表（得到用户所使用的产品、地区),
关联产品表（得到免费通话时长、通话收费标准）
计算每个号码每个月的费用

第一步ods_call_record与ods_cust_info相连接，获取用户的地区(社区)和其使用的产品，使用joiner组件，并添加连接条件。
第二步与ods_product相连，获取产品的套餐以及套餐的免费时长与通话费用，使用joiner组件，并添加连接条件，
特别注意连接条件的字段要数据类型一致
连接表时只保留需要的字段，不需要就不相连

3、计算每次通话的费用
通过获取免费通话时长MF_TIME、通话费用CALL_MINUTE_FEE计算每次通话费用

iif(LEIJI>MF_TIME,
iif((LEIJI-MF_TIME-TIME_LONG)<0,(LEIJI-MF_TIME)*CALL_MINUTE_FEE,TIME_LONG*CALL_MINUTE_FEE),0)

下一步通过汇总组件按照(用户、月份、地区、套餐、基本月租)进行分组，计算每个用户每月的通话费用

sum(fee)+BASIC_FEE --当月的话费费用加上月租

连接映射，完成mapping

create table DM_DEPT_CT_COMM
(
  month_id       VARCHAR2(10),
  area           VARCHAR2(40),
  prod_id        VARCHAR2(15),
  num_total      NUMBER(10),
  basic_fee      NUMBER(10),
  total_call_fee NUMBER(15,2)
)

将源表与目标表导入mapping

地区维度表与已完成的edw_call_fee表关联，实现地区是区局的用户数及收入费用的统计

创建任务，修改源表与目标表的数据库层，创建工作流，通过任务启动工作流

到此ODS层，EDW层，DM层已开发成功，后续可根据不同维度，指标进一步分析！