基于SQL的数据分析应用-all.ppt

基于SQL的数据分析应用,主要内容,数据处理的基础ETL 数据质量探查 数据预处理 基于SQL的程序设计,数据处理的基础ETL,大数据的批量导入问题 SQL Server的导入导出工具 SSIS（Integration Services 项目） BCP工具、Bulk Insert命令,bulk insert dataTrainDB.dbo.thealldata2 from e:shifenzheng_test.csv with ( FIELDTERMINATOR =, FIRSTROW = 2 ),数据处理的基础ETL,导入过程容易引发的问题 原始数据和目标数据的格式 中文转码 字段内的特殊符号 导入导出过程的错误处理 导入导出的效率 数据导入后的处理 索引 触发器,2013年商务酒店泄露数据库 2.9G CSV文件,数据质量的探查,SQL Server的聚合函数 AVG MAX MIN SUM COUNT VAR VARP STDEV STDEVP,SELECT ColumnName OVER(PARTITION BY . ORDER BY) FROM TABLENAME WHERE . GROUP | ORDER,数据质量的探查,集中趋势描述 均值、中位数、众数 离散程度描述 方差和标准差,按照生产所需时间，分类计算安全库存量的均值，并按照productid排序,select productid,name,daystomanufacture, AVG(SafetyStockLevel) over(partition by DaysToManufacture) as avgStock, ROW_NUMBER() over(partition by DaysToManufacture order by productid) as newRowID from Production.Product,计算SafetyStockLevel（安全库存量）的众数,select SafetyStockLevel,COUNT(1) as cnt from Production.Product group by SafetyStockLevel order by cnt,数据预处理,文本型数据的问题及处理方法 编码类型 特殊字符 非规范的文本格式,查找 替换 字符串截取及拼接,ASCII()、CHAR()、UNICODE()、NCHAR()、REVERSE() CHARINDEX()和PATINDEX() LEN() LEFT()、RIGHT() SUBSTRING() LOWER()、UPPER() LTRIM()、RTRIM()和TRIM() REPLACE() REPLICATE()、SPACE(),数据预处理-字符串函数,ascii(字符串表达式) char(整数表达式) charindex(字符串表达式 1, 字符串表达式2,整数表达式) patindex(字符串表达式 1，字符串表达式 2),select ascii(abc),select char(100),select charindex(ab,BCabTabD),select charindex(ab,BCabTabD,4),select patindex(%ab%,123ab456),select patindex(ab%,123ab456),select patindex(_ab%,123ab456),select patindex(_ab_,123ab456),数据预处理-字符串函数,substring(字符串表达式，开始位置，长度) replace(字符串表达式 1，字符串表达式 2，字符串表达式 3),select substring(abcdef,2,2),select replace(abcttabchhabc,abc,123),数据预处理,数值型数据存在的问题及处理方法 数据的规范性 数据的类型 数据的范围 噪声数据,SQL聚合函数 抽样,RAND() NEWID(),随机抽样100条记录,数据预处理,时间日期型数据的处理 格式问题 初步的时间解码及计算,DATEADD() DATEDIFF() DATEPART() DATENAME() GETDATE() DAY()、MONTH()、YEAR(),根据生日查询年龄,数据预处理-时间日期函数,dateadd(日期部分，数字，日期) datediff(日期部分，开始日期，结束日期) datename(日期部分，日期),select dateadd(year,45,1990-12-11),select dateadd(mm,45,1990-12-11),select dateadd(qq,12,1990-12-11),select dateadd(hh,12,1990-12-11),select dateadd(yy,-12,1990-12-11),select datediff(yy,1990-12-11,2008-9-10),select datediff(mm,2007-12-11,2008-9-10),select datename(dw,2007-12-11),dw 表示一星期中星期几，wk 表示一年中的第几个星期 dy 表示一年中的第几天,数据预处理,数据类型的转换 Cast Convert,SELECT Default Date: + CONVERT(Varchar(50), GETDATE(), 100) SELECT US Date: + CONVERT(Varchar(50), GETDATE(), 101) SELECT ANSI Date: + CONVERT(Varchar(50), GETDATE(), 103) SELECT UK/French Date: +CONVERT (Varchar(50), GETDATE(), 103) SELECT German Date: + CONVERT(Varchar(50), GETDATE(), 104),convert 在进行日期转换时还提供了丰富的样式 cast 只能进行普通的日期转换,复杂数据处理,系统表 临时表 定义变量,select * from sys.tables select * from sys.columns,select * into #temp from ,declare temp nvarchar(5) set temp = 123 print temp,复杂数据处理,复杂数据处理要求 使用一条SQL查询无法完成任务 需要有变量等暂存中间数据 需要使用判断或循环来完成功能 存储过程,存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL