幻灯片 1

Size: px

Start display at page:

Download "幻灯片 1"

谓鲁
4 years ago
Views:

1 数据挖掘在管理审计的应用探讨 - 以关系型数据库在欠费管理审计中的应用为例吴龙昌 2017 年 9 月 25 日

2 欠费分析概述数据导入指引单表数据分析多表数据分析欠费分析实例 2 2

3 欠费分析概述公众欠费记录数据量大, 维度众多, 但只需要描述性统计分析, 因此适合使用数据库管理系统公众欠费记录数据量大 ( 百万级千万级 ) 维度众多 ( 产品资费用户维度客户维度信用度入网渠道 ) 分析方法简单, 不涉及回归分析结构方程模型列联表 3 数据分析处理软件 EXCEL( 最多容纳 104 万行数据 ) ACCESS ( 数据库 100MB 以上时, 性能大幅下降 ) MSSQL ( 在 WINDOWS 系统上运行, 上手易 ) ORACLE( 对硬件的要求很高, 管理维护麻烦 ) SPSS( 不适用百万级数据分析 ) SAS( 分析功能过剩, 学习成本比数据库高 ) R 语言 ( 提供全面的分析算法包 ) 3

4 欠费分析概述 SQL Server 系列软件是 Microsoft 公司推出的关系型数据库管理系统, SQL Server 2014 于 2014 年 4 月 16 日于发布主要版本硬件要求 SQL Server 版本 Enterprise(64 位和 32 位 ) Business Intelligence( 64 位和 32 位 ) 定义作为高级版本,SQL Server 2014 Enterprise 版提供了全面的高端数据中心功能, 性能极为快捷虚拟化不受限制, 还具有端到端的商业智能 - 可为关键任务工作负荷提供较高服务级别, 支持最终用户访问深层数据 SQL Server 2014 Business Intelligence 版提供了综合性平台, 可支持组织构建和部署安全可扩展且易于管理的 BI 解决方案它提供基于浏览器的数据浏览与可见性等卓越功能功能强大的数据集成功能, 以及增强的集成管理组件内存处理器速度要求最低要求 : Express 版本 :512 MB 所有其他版本 :1 GB 建议 : Express 版本 :1 GB 所有其他版本 : 至少 4 GB 并且应该随着数据库大小的增加而增加, 以便确保最佳的性能最低要求 : x86 处理器 :1.0 GHz x64 处理器 :1.4 GHz 建议 : 2.0 GHz 或更快 Standard(64 位和 32 位 ) SQL Server 2014 Standard 版提供了基本数据管理和商业智能数据库, 使部门和小型组织能够顺利运行其应用程序并支持将常用开发工具用于内部部署和云部署 - 有助于以最少的 IT 资源获得高效的数据库管理处理器类型 x64 处理器 : AMD Opteron AMD Athlon 64 支持 Intel EM64T 的 Intel Xeon 支持 EM64T 的 Intel Pentium IV x86 处理器 : Pentium III 兼容处理器或更快 4 4

5 欠费分析概述数据导入指引单表数据分析多表数据分析欠费分析实例 5 5

6 数据导入指引使用管理器新建数据库时, 鼠标右键点击数据库, 通过新建数据库建立新的数据库 6 6

7 数据导入指引导入数据表时, 右键点击目标数据库, 通过导入数据库功能开启数据导入功能若源数据文件为 EXCEL, 则选择 Microsoft Excel ; 若源数据文件为 TXT, 则选择平面文件源 7 7

8 数据导入指引在选择导入目标时, 选择 SQL Server Native Client

9 数据导入指引如果需要把数据库当中多个月份或者多个地市的表合并为一张表, 可以使用 Transact-Structured Query Language (T-SQL) 语言进行合并 insert into q2016_12 select * from dbo.q201612_01 union all select * from dbo.q201612_02 union all select * from dbo.q201612_03 union all select * from dbo.q201612_04 union all select * from dbo.q201612_05 union all select * from dbo.q201612_06 union all select * from dbo.q201612_07 union all select * from dbo.q201612_08 union all select * from dbo.q201612_09 union all select * from dbo.q201612_10 union all select * from dbo.q201612_11 9 注意 : insert 函数只能将多个数据表合并到已存在的表当中该方式的优势在于不用重新建立新表的结构 9

10 数据导入指引导入数据时经常遇到的两个问题 : 数据格式错误和源文件存在乱码数据格式错误一般是某些记录的某些字段存在异常值导致的 10 10

11 数据导入指引解决数据格式错误方法有两种 :1 消除异常值 ;2 更改字段的数据格式, 使异常值不异常 SQL Server 数据类型占用字节数表示范围适用场景 char char(n) 使用 char(2) 来表示类型或状态 ( 建议用 tinyint 代替 ) varchar varchar(n) 1~8000 只包含英文字符的字符串 nvarchar nvarchar(n) 1~4000 包含中文字符的字符串 int bigint 4 个字节 8 个字节 -2,147,483,648 到 2,147,483,647-9,223,372,036,854,7 75,808 到 9,223,372,036,854,7 75,807 整数, 比如自增 ID 或数量表示长整数, 比如自增 ID( 数量比较大的情况下 ) decimal 5~17 字节金钱价格 tinyint 1 字节 0~255 类型和状态, 比 char(2) 扩展性好 bit 0,1 或 NULL 一般用来表示是和否两种情形 datetime 8 字节 1753 年 1 月 1 日到 9999 年 12 月 31 日表示日期和时间 time varbinary 11 表示时间间隔, 比如计时和耗時表示二进制数据 11

12 数据导入指引在数据源文件存在乱码, 则需要进行数据清洗, 即把乱码消除掉并保证源数据的格式未被破坏由于乱码把列标识符给消除了, 所以不但需要将乱码删除, 还需把加回去 12 12

13 数据导入指引分析公众欠费, 主要涉及到全量公众欠费清单坏账核销清单欠费金额 100 元以上用户代付费记录截止 2017 年 4 月底的所有因非实名制而被关停用户清单测试卡全量清单表名称记录数量全量公众欠费清单年坏账核销清单欠费金额 100 元以上用户代付费记录截止 2017 年 4 月底的所有因非实名制而被关停用户清单测试卡全量清单

14 欠费分析概述数据导入指引单表数据分析多表数据分析欠费分析实例 14 14

15 单表数据分析 SELECT 语句用于从表中选取数据, 结果被存储在一个结果表中 ( 称为结果集 ) SQL 语法 SELECT 列名称 FROM 表名称或 SELECT * FROM 表名称或 SELECT 函数 (* ) FROM 表名称注释 :SQL 语句对大小写不敏感,SELECT 等效于 select 示例 15 15

16 单表数据分析如需有条件地从表中选取数据, 可将 WHERE 子句添加到 SELECT 语句 SQL 语法 SELECT 列名称 FROM 表名称 WHERE 列名称运算符值示例 16 16

17 单表数据分析 WHERE 语句需要和各式各样的运算符配合使用操作符描述 = 等于 <> 不等于 > 大于 < 小于 >= 大于等于 <= 小于等于 BETWEEN 在某个范围内 LIKE 搜索某种模式 17 17

18 单表数据分析 GROUP BY 语句用于结合聚类函数, 根据一个或多个列对结果集进行分组 SQL 语法 SELECT 列名称, 聚类函数 ( 列名称 ) FROM 表名 WHERE 列名称运算符值 GROUP BY 列名称示例 18 18

19 单表数据分析在 SQL 中增加 HAVING 子句原因是,WHERE 无法与聚类函数一起使用 SQL 语法 SELECT 列名称, 聚类函数 ( 列名称 ) FROM 表名 WHERE 列名称运算符值 GROUP BY 列名称 HAVING 聚类函数 ( 列名称 ) 运算符值示例 19 19

20 单表数据分析在 SQL 中增加 HAVING 子句原因是,WHERE 无法与聚类函数一起使用 SQL 语法 SELECT 列名称, 聚类函数 ( 列名称 ) FROM 表名 WHERE 列名称运算符值 GROUP BY 列名称 HAVING 聚类函数 ( 列名称 ) 运算符值示例 20 20

21 欠费分析概述数据导入指引单表数据分析多表数据分析欠费分析实例 21 21

22 多表数据分析 join 用于根据两个或多个表中的列之间的关系, 从这些表中查询数据, 根据表之间连接方式的不同, 可以分为 inner join, left join 和 right join 三种函数名称 JOIN/INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN 功能如果表中有至少一个匹配, 则返回行即使右表中没有匹配, 也从左表返回所有的行即使左表中没有匹配, 也从右表返回所有的行只要其中一个表中存在匹配, 就返回行 22 22

23 多表数据分析在表中存在至少一个匹配时,INNER JOIN 即返回对应的行 ( 一行即一条记录 ) SQL 语法 SELECT 列名称 FROM 表名 1 INNER JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名示例 23 23

24 多表数据分析 LEFT JOIN 会从左表返回所有的行, 即使在右表中没有可匹配的行 SQL 语法 SELECT 列名称 FROM 表名 1 LEFT JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名示例 24 24

25 多表数据分析 RIGHT JOIN 会从右表返回所有的行, 即使在左表中没有可匹配的行 SQL 语法 SELECT 列名称 FROM 表名 1 RIGHT JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名示例 25 25

26 多表数据分析 FULL JOIN 只要其中某个表存在匹配, 就会返回行 SQL 语法 SELECT 列名称 FROM 表名 1 FULL JOIN 表名 2 ON 表名 1. 列名 = 表名 2. 列名示例 26 26

27 欠费分析概述数据导入指引单表数据分析多表数据分析欠费分析实例 27 27

28 欠费分析实例查找 2015 年坏账核销清单里面欠费未核销的用户 select * from dbo.q2016_all inner join dbo. 欠费核销 2015 on dbo.q2016_all.user_id=dbo. 欠费核销用户 ID 28 28

29 欠费分析实例找出因非实名制导致的局拆欠费 ( 该部分欠费永远无法追回 ) 用户 select * from dbo.q201704_all inner join dbo. 截止 2017 年 4 月底的所有因非实名制而被关停用户清单 on dbo. 截止 2017 年 4 月底的所有因非实名制而被关停用户清单.user_ID=dbo.q201704_all.user_ID where dbo.q201704_all. 状态 =' 欠费销号 ' 29 29

30 欠费分析实例业务测试卡产生的欠费规模 select * from dbo.q201704_all inner join dbo. 测试卡全量清单 on dbo. 测试卡全量清单.ID_NO=dbo.q201704_all.user_ID 30 30

31 欠费分析实例业务优化前的历史代付费规模 select * from dbo.q201704_all inner join dbo. 欠费金额 100 元以上用户代付费记录 on dbo. 欠费金额 100 元以上用户代付费记录. 用户 ID=dbo.q201704_all.user_ID where dbo.q201704_all. 最小欠费日期 <

32 欠费分析实例找出欠费清单当中疑似集客产品 select * from dbo.q201704_all where dbo.q201704_all. 欠费金额 >10000 or dbo.q201704_all. 产品资费名称 like '% 专网 %' or dbo.q201704_all. 产品资费名称 like '% 专线 %' or dbo.q201704_all. 产品资费名称 like '% 集团 %' order by dbo.q201704_all. 欠费金额 desc 32 32

欠费分析实例找到同渠道批量入网的疑似高额恶意欠费用户群 select * from dbo.q201704_all where dbo.q201704_all. 入网渠道名称 in ( select dbo.q201704_all. 入网渠道名称 from dbo.q201704_all group by 入网渠道名称 having count( 入网渠道名称 )>5 ) and dbo.

33 欠费分析实例找到同渠道批量入网的疑似高额恶意欠费用户群 select * from dbo.q201704_all where dbo.q201704_all. 入网渠道名称 in ( select dbo.q201704_all. 入网渠道名称 from dbo.q201704_all group by 入网渠道名称 having count( 入网渠道名称 )>5 ) and dbo.q201704_all. 状态 =' 欠费销号 ' and dbo.q201704_all. 欠费金额 >800 and dbo.q201704_all. 是否为测试卡用户 =' 否 ' and dbo.q201704_all. 手机号码 > and dbo.q201704_all. 是否红名单用户 =' 否 ' and dbo.q201704_all. 是否协议缴费 =' 否 ' and dbo.q201704_all. 帐户信誉度 <=

34 谢谢 34 34

Autodesk Product Design Suite Standard 系统统需求典型用户户和工作流 Autodesk Product Design Suite Standard 版本为为负责创建非凡凡产品的设计师师和工程师提供供基本方案设计和和制图工具, 以获得令人惊叹叹的产品

Autodesk Product Design Suite Standard 20122 系统统需求典型用户户和工作流 Autodesk Product Design Suite Standard 版本为为负责创建非凡凡产品的设计师师和工程师提供供基本方案设计和和制图工具, 以获得令人惊叹叹的产品设计 Autodesk Product Design Suite Standard 版本包包括以下软件产产品