数据分析---SQL（Access数据库）

1. SQL（Structure Quest Language）

一种结构化查询语言，它是一种通用的关系型数据库操作语言，用于存取数据，查询，更新和管理数据库。

2. 基本语句

Select, Create, Insert, Drop

3. SQL语句注意事项

1）SQL语句中，英文字母大写或小写均可

2）每个SQL语句的关键字用空白符号分隔

3）字段或参数之间用逗号分隔

4）SQL语句中，如参数为字符型，需要使用单引号，数值型不使用单引号

5）SQL语句结束时，在语句结尾处添加分号，在Access数据库中，无强制要求SQL语句结束时添加用分号。

6）Access数据库SQL语句中，如表名，字段名中出现空格，‘/’, '\'等特殊字符时，需用方括号‘ []’ 将含有特殊字符的表名或字段名括起来，以免得到不正确的结果或SQL语句无法运行。

7）SQL语句中，‘*’代表选定数据表中的所有字段，并且按照其在数据库中的固定顺序来显示

8）Access数据库，在函数参数中或条件查询中，若参数或查询条件为日期和时间类型数据，需要在数据值两端加上井字符号（#），以表示数据类型为日期型

9）SQL语句中使用的逗号，分号，单引号，括号等符号均需要是在输入法为英文状态下输入的符号。

10）应尽量避免在数据库中进行全表扫描：

        a. 首先应考虑用where子句筛选出需要的数据；

        b. 其次，在where子句中，应尽量避免使用 “！=”或 “<>”, "OR"等

        c. 最后应尽量避免在where子句中对字段进行函数操作，否则将进行全表扫描

4. 字段拆分

 1）相关函数

         a. Left(字符串， 提取的位数)

         b. Right (字符串， 提取的位数)

         c. Mid (字符串， 提取的起始位置， 提取的位数)

2）示例：从身份证号码中提取省份编码，区县编码，出生日期     

SELECT 身份证号码， Left (身份号码，2) as 省份编码，
Mid (身份号码， 7,4) as 年，
Mid （身份号码，11,2） as 月，
Mid （身份号码， 13,2）as 日，
Mid (身份号码， 17,1) as 性别
FROM 用户明细；

5. 随机抽样：按照随机原则，进行抽取样本的一种方法

  例如： 随机抽取20条记录        

SELECT TOP 20 *
FROM 用户明细
ORDER BY Rnd (用户ID);

6. 数据合并

1）字段合并   

           a. 使用连接符: &, +         

SELECT 身份证号码，
（Mid(身份证号码，7,4) & ‘-’ & Mid（身份证号码，11,2）& ‘-’ & Mid(身份证号码，13,2)） as 出生日期
FROM 用户明细；

           b. 使用相关函数（比如日期函数DateSerial）             

SELECT 身份证号码，
DateSerial (Mid(身份证号码，7,4)， Mid(身份证号码，11,2)， Mid(身份证号码，13,2)) as 出生日期
FROM 用户明细；

2）字段匹配

          a.连接类型：主要包含内连接（INNER JOIN）,左连接（LEFT JOIN），右连接 （RIGHT JOIN）三种数据库关系；

SELECT 订购明细.订单编号， 订购明细.订购日期， 订购明细.用户ID， 订购明细.产品， 订购明细.[单价（元）]， 用户明细.性别
FROM 订购明细 INNER JOIN 订购明细.用户ID=用户明细.用户ID

3）记录合并

a. 记录合并：要求各表具有相同字段，结构，使用UNION ALL 或 UNION 指令进行两表或多表合并；

b. UNION会删除各表存在的重复记录，并进行排序，要慎用。

###合并所有记录，不擅长重复记录
1 SELECT * FROM (SELECT *FROM 订购明细20110901) UNION ALL SELECT * FROM 订购明细20110902）  

#### 创建一张空表
1 SELECT *INTO 订购明细201109
FROM 订购明细20110901
WHERE 1=2;      ### 该条件不可能满足

7. 数据去重：保留唯一的数据记录

1）Group By: 对数据按指定的分组字段进行分组。

SELECT 用户ID
FROM 用户明细重复
GROUP BY 用户ID；

2) Distinct： 忽略所选字段中包含重复数据的记录，简单来说，就是数据去重。

SELECT DISTINCT 用户ID, 注册日期， 身份证号码，性别，年龄
FROM 用户明细重复；

8.数据分组

1）数值分组---IIF函数，与Excel中的IF用法一致

       IIF(条件表达式，表达式成立返回的值，表达式不成立返回的值)： 

SELECT 用户ID, 年龄
IIF（年龄<=20, "20岁及其以下"，
IIF(年龄 <=30, "21-30岁"，
IIF（年龄 <= 40, "31-40岁"， "40岁以上"）））AS 年龄分组
FROM 用户明细；

2）日期分组

       a. YEAR, MONTH, DAY函数：

SELECT 订单编号，订购日期，
YEAR (订购日期) AS 年，
MONTH（订购日期）AS 月，
DAY（订购日期）AS 日
FROM 订购明细；

        前提：订购日期字段必须为日期型数据

       b. FORMAT函数：FORMAT(日期/时间， 日期/时间格式参数)

日期/时间格式参数	说明
：（冒号）	时间分隔符
/	日期分隔符
d	根据需要以一位或两位数字表示一个月中的第几天（1-31）
dddd	星期的全称（Sunday-Saturday）
w	一周中的第几天（1-7）
ww	一年中的第几周（1-53）
m	根据需要以一位或两位数字表示一年中的月份（1-12）
mmmm	月份的全称（January-December）
q	一年中的第几个季度（1-4）
y	一年中的第几天（1-366）
yyyy	完整的年份（0100-9999）
h	根据需要以一位或两位数字表示小时（0-23）
n	根据需要以一位或两位数字表示分钟（0-59）
s	根据需要以一位或两位数字表示秒（0-59）

 

 

 

 

 

 

 

 

 

 

 

 

 

如下示例：

SELECT 订单编号， 订购日期，
FORMAT(订购日期， "yyyy") AS 年，
FORMAT(订购日期， "q") AS 季，
FORMAT(订购日期， "m") AS 月，     
FORMAT(订购日期， "d") AS 日，
FORMAT(订购日期， "dddd") AS 星期，
FORMAT(订购日期， "h") AS 小时，
FORMAT(订购日期， "n") AS 分，
FORMAT(订购日期， "s") AS 秒，
FROM 订购明细；

9.数据计算

1) 简单计算： 加，减，乘，除

SELECT 订单编号，产品，【单价（元）】， 数量， 订购金额，【数量】*【单价（元）】AS 订单金额
FROM 订购明细；   

 2）函数计算：通过软件内置的函数进行计算，比如：DATEDIFF("参数"， 起始日期， 结束日期)

参数	说明
yyyy	年
q	季度
m	月
d	天
w	周
h	时
n	分
s	秒

       

 

 

 

 

 

 

 SELECT 用户ID, 注册日期， DATEDIFF("D", 注册日期， #2015-2-14#) AS 注册天数             -----------------日期数据要用##标明
FROM 用户明细；

10. 数据分析

1）简单统计

统计方式	统计函数	说明
计数	Count()	统计指定列中值的个数
求和	Sum()	计算数值型数据的总和
平均	Avg()	计算数值型数据的平均值
最大值	Max()	筛选出数据的最大值
最小值	Min()	筛选出数据的最小值
标准差	StDev()	计算数值型数据的标准差
方差	Var()	计算数值型数据的方差

 

 

 

 

 

 

 

SELECT Count(订单编号) AS 订单总数， Sum(订购金额) AS 订购金额总额， Avg(订购金额) AS 平均订单金额
FROM 订单明细；

2）分组分析

a. 定量分组：数值分组，日期分组

SELECT IIF （年龄<=20，"20岁及其以下"，
IIF（年龄<=30, "21-30岁"，
IIF （年龄 <=40， "31-40岁"， "40岁以上"））） AS 年龄分组，
Count（用户ID）AS 用户数
FROM 用户明细
GROUP BY  IIF （年龄<=20，"20岁及其以下"，
IIF（年龄<=30, "21-30岁"，
IIF （年龄 <=40， "31-40岁"， "40岁以上"）））；

b. 定性分组：按事物已有的类别进行划分，用Group By语句

SELECT 产品，Count(订单编号) AS 订单总数，Sum(订购金额) AS 订购总金额，Avg（订购金额） AS 平均订单金额
FROM 订购明细
GROUP BY 产品；

   日期分组：

SELECT FORMAT(注册日期， "m") AS 月，
Count(用户ID) AS 用户数
FROM 用户明细
GROUP BY FORMAT(注册日期， "m")

 

3) 排序分析   

SELECT 产品， Count(订单编号) AS 订单总数
FROM 订购明细
GROUP BY 产品
ORDER BY Count(订单编号) DESC ;

4) 结构分析

SELECT 产品，
Count(产品编号)/(SELECT Count(d订单编号) FROM 订购明细) AS 占比
FROM 订购明细
GROUP BY 产品；

5）分布分析：查看数据的分布情况

SELECT IIF(B.年龄<=20, “20岁及其以下”，IIF(B.年龄<=30, "21-30岁"，IIF(B.年龄<=40, "31-40岁"， “40岁以上”)）） AS 年龄分组， COUNT(A.用户ID) AS 购买用户数
FROM (SELECT 用户ID FROM 订购明细 GROUP BY 用户ID) AS A, 用户明细 AS B
WHERE A.用户ID=B.用户ID
GROUP BY IIF(B.年龄<=20, "20岁及其以下"， IIF(B.年龄<=30, “21-30岁”， IIF(B.年龄<=40, "31-40岁"， “40岁以上”)))；
 
  

6）交叉分析：通常用于分析两个或两个以上分组变量的关系，以交叉表的形式进行变量间关系的对比分析，从数据的不同维度综合进行分组细分，以进一步了解数据的构成和分布特征。

变量的类型：

a. 定量，定量分组交叉

b. 定量，定性分组交叉

c. 定性，定性分组交叉

交叉的维度：不宜过多，一般建议两个维度。

TRANSFORM COUNT(用户ID) AS 用户数
SELECT 年龄
FROM 用户明细
GROUP BY 年龄
PIVOT 性别；

7）留存分析：以留存率为基准，留存率是用户留下来的比率。

留存用户：（第一日登录，且在第二日登录的用户）

SELECT 登录用户明细20110101.用户ID， 登录用户明细20110102.用户ID
FROM 登录用户明细20110101 LEFT JOIN 登录用户明细20110102 ON 登录用户明细20110101.用户ID=登录用户明细20110102.用户ID

#### 计算留存率
1 SELECT count(登录用户明细20110101.用户ID) AS 第一日登录用户数，count( 登录用户明细20110102.用户ID)/count(登录用户明细20110101.用户ID) AS 次日留存率
FROM 登录用户明细20110101 LEFT JOIN 登录用户明细20110102 ON 登录用户明细20110101.用户ID=登录用户明细20110102.用户ID

8）矩阵分析：例如根据年龄，消费两个维度，分析各省份购买用户质量。

各省份购买用户平均订购金额：

SELECT LEFT(B.身份证号码，2) As 省份编码，
AVG(A.订购金额) AS 平均订购金额
FROM 订购明细 AS A， 用户明细 AS B
WHERE A.用户ID=B.用户ID
GROUP BY LEFT(B.身份证号码，2)

使用LEFT函数处理过的字段为短文本字段.

各省份购买用户平均订购金额：

SELECT C.省份
AVG（A.订购金额） AS 平均订购金额
FROM 订购明细 AS A， 用户明细 AS B, 省份 AS C
WHERE A.用户ID=B.用户ID
AND C.省份编码=LEFT（B.身份证号码，2）
GROUP BY C.省份

各省份购买用户平均年龄：

SELECT C.省份， AVG（B.年龄） AS 平均年龄
FROM 
(SELECT 用户ID FROM 订购明细 GROUP BY 用户ID) AS A,
用户明细 AS B,
省份 AS C
WHERE A.用户ID=B.用户ID AND C.省份编码=LEFT(B.身份证号码，2)
GROUP BY C.省份；