SQL如何分析商品订单？（表连接的区别、汇总函数）

【题目】

有两张表，一张是订单列表，表名为“订单明细表”；一张是用户名单，表名为“注册表”。“订单明细表”中的用户ID与”注册表”中的用户ID一一对应。

 

问题1：查询出每天成功付费的付费人数、付费金额、付费单量

问题2：2013年6月14日注册用户的付费人数、付费金额

【问题1解题思路】

查询出每天成功付费的付费人数、付费金额、付费单量

1.题中提到查询“每天”的数据，涉及到“每个”这类问题要想到《猴子 从零学会SQL》里讲过的“分组汇总”来解决这里类问题

按“付费时间”分组，每天的付费人数用汇总函数（count对用户ID这一列统计有多少行），每天的付费金额用汇总函数（sum对付费金额这一列求和），每天的付费单量要用汇总函数（count对订单号这一列有多少行）

2.上面涉及到的列用户ID、付费金额、订单号都在“订单明细表”里

3.需要注意的是，要统计的是“人数”而不是“人次”，所以在统计“用户ID”时要做一次去重处理

4.题目中并没有明确说明“付费时间”的数据格式，根据日常处理数据的经验，需要用时间处理函数（date_format）先把时间格式统一为“年月日”，也就是date_format(付费时间,'%Y-%c-%d')

select date_format(付费时间,'%Y-%c-%d') as 付费时间,
count(distinct 用户ID) as 付费人数,
sum(付费金额) as 付费金额,
count(订单号) as 付费单量
from 订单明细表
group by date_format(付费时间,'%Y-%c-%d');

 5.注意统计的是“成功付费”的数据，那么需要用条件（where）指定“订单状态”为“成功”，把“失败”的订单排除在外，也就是在上一步的SQL中加入where子句

select date_format(付费时间,'%Y-%c-%d') as 付费时间,
count(distinct 用户ID) as 付费人数,
sum(付费金额) as 付费金额,
count(订单号) as 付费单量
from 订单明细表
where 订单状态 =成功
group by date_format(付费时间,'%Y-%c-%d');

【本题考点】

1.如果涉及到“每个”的问题，需要想到用分组汇总或者窗口函数去解决

2.考察了SQL对数据的分组应用，本题是在分组前对数据按条件筛选，使用的是where子句。如果是要对分组结果后的数据指定条件，记得是使用having，而不是where

3.考察对SQL汇总函数的应用，常用的汇总函数有以下几个：

【问题2解题思路】

查询出2013年6月14日注册用户的付费人数、付费金额

首先我们用多维度拆解分析方法对问题拆解

1.先找出“2013年6月14日注册用户”，这个数据可以从“注册表”里查找到

2.再统计“2013年6月14日注册用户”的“付费人数”和“付费金额”总数，这个数据可以从“订单明细表”里找到

3.因为涉及到两张表，所以需要用到多表联结。这道题目要我们找到的是“注册表”中有注册的用户，同时在“订单明细表”中指定日期消费过的用户，也就是两个表中“用户ID”这一列数据有交集的数据。

所有需要查找的同时出现在两张表中的共同数据，应该使用多表联结中的“内联结”（inner join）

 

select * 
from 订单明细表 as a
inner join 注册表 as b
where a.用户ID = b.用户ID;

 因为要统计的是付费人数，付费金额，我们在上一步SQL中加入汇总函数可以得到

select count(distinct 用户ID) as 付费人数,
sum(付费金额) as 付费金额 
from 订单明细表 as a
inner join 注册表 as b
where a.用户ID = b.用户ID;

4.找到两张表“用户ID”列共有的数据后，设置条件 where 注册表.注册时间= “2013年6月14日”。同时需要是有效订单，即订单明细表.订单状态=“成功”

5.同上一题一样，会用到时间处理函数（date_format），先把时间格式统一为“年月日”，也就是date_format(付费时间,'%Y-%c-%d')

select count(distinct 用户ID) as 付费人数,
sum(付费金额) as 付费金额 
from 订单明细表 as a
inner join 注册表 as b
where a.用户ID = b.用户ID
and date_format(b.注册时间,'%Y-%c-%d')=2013年6月14日
and a.状态 = 成功;

 

2.考察多表查询。通过“as”语句，自定义表的名称，能够比较方便的对两张表进行关联比较。以及如何判断使用哪种联结。 

表连接的区别

（1）通过INNER JOIN来从两个表获取数据

（在数据库语言中，被称为显性连接。内连接返回的是两个表匹配得上的数据，匹配不上的不返回。 注：INNER JOIN 和JOIN是一样的）

（2）通过LEFT JOIN来从两个表获取数据

(以左表为基础,显示左表中的所有列,而右表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充。注：LEFT JOIN 和LEFT OUTER JOIN是一样的)

（3）通过RIGHT JOIN来从两个表获取数据

(以右表为基础,显示右表中的所有列,而左表中的数据只显示与关联条件相匹配的列,不匹配的列以NULL字符填充。注：RIGHT JOIN 和RIGHT OUTER JOIN是一样的)

left join 是以左表来作为一个基准，他只会拿到左表想要的数据。

right left是以右表来作为一个基准，他只会拿到右表想要的数据。

（4）通过FULL JOIN来从两个表获取数据

(显示两个表的全部列,不匹配的列以NULL字符填充。注：FULL JOIN 和FULL OUTER JOIN是一样的)