题目一:奇数和偶数交易
题目要求:
编写一个解决方案来查找每天 奇数 交易金额和 偶数 交易金额的 总和。如果某天没有奇数或偶数交易,显示为
0
。返回结果表以
transaction_date
升序 排序。
表结构:
运行结果示例:
思路:
题目要求寻找每天奇数交易金额和偶数交易金额分别的和,那么很容易就能想到按日期分组求和的方法,但是本题中,需要我们加上一个判断语句,判断金额是奇数还是偶数,如果是我们需要的就计入列,否则加上0。判断一个数字是奇数还是偶数的方法有很多,我们之前详细学习过,这里不再赘述。
运行代码示例:
select transaction_date,sum(if(amount % 2 = 1, amount, 0)) as odd_sum, sum(if(amount % 2 = 0, amount, 0)) as even_sum
from transactions
group by transaction_date
order by transaction_date
题目二:产品销售分析III
题目要求:
编写解决方案,选出每个售出过的产品 第一年 销售的 产品 id、年份、数量 和 价格。
结果表中的条目可以按 任意顺序 排列。
表结构:
运行结果示例:
思路:
本题第一眼看到感觉很简单,直接按照产品id分组,找到最小的年份即可,所有写出了下面的代码:
select product_id, min(year) as first_year, quantity, price from sales group by product_id
然后,不出意外的出意外了。
那么我们分析一下上面的代码问题出在哪里呢?根据未通过测试的测试用例,我们可以观察到,存在某个产品在第一年多次出售的情况,每次售卖的数量和价格都不一样,但是我们需要返回所有的结果。例如在product_id =51这条数据中 ,我们查询到的第一次出售的年份是正确的,但是数量和价格不正确,而且输入表中根本没有我们查询到的这条数据。
找到了出错的地方,那么我们不妨分析一下产生这种错误的原因。因为我们的代码很简单,所以很容易想到问题可能出现在group by语句上,我们可以查看一下group by 语句的使用方法,其中注意事项中有一条引起了我的注意:
再进一步查询资料,可以知道SQL中,每一行的数据不是绑定的,如果去查询分组字段或者聚合函数参数的话,查询结果没有问题,但是其他列的数据会默认返回该组的第一行。也就产生了我们上面出现的那个错误。
弄明白这一点,那么我们正确的代码就不难写出来了。我们上面的整体思路没有问题,可以查询出产品id及第一次售出的年份,只是找不到对应的数量和价格,那么我们将其作为子查询表,去全表中寻找product_id,year组合在子表中的数据即可。
运行代码示例:
select product_id,year as first_year,quantity, price
from Sales
where(product_id,year) in (
select product_id,min(year) from Sales group by product_id
)
题目三:市场分析I
题目要求:
编写解决方案找出每个用户的注册日期和在
2019
年作为买家的订单总数。以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
这道题目同样难度不高,但新手同样容易出错。我们很容易想到按照id分组查询订单总数,并且使用左外连接连接两张表,获取用户注册时间信息,并将订单时间作为筛选条件,写出如下代码:
select user_id as buyer_id, join_date, count(order_id) as orders_in_2019 from users left join orders on users.user_id = orders.buyer_id where year(order_date) = 2019 group by users.user_id
但是我们会发现该代码无法通过测试用例,因为在查询出的结果中,缺失用户id为3、4的用户信息。但是我们明明使用了左外连接,为什么会缺失数据呢?这就涉及到了外连接中on子句和where子句的区别。我们都知道,外连接时要使用on关键字来连接两表,但是on子句其实也可以用于进行数据的筛选。而且,on是在连接构造临时表时执行的,不管on中条件是否成立都会返回主表的内容(也就是左外连接和右外连接的区别,也就是谁做主表),但是where是在临时表形成后执行筛选作用的,不满足条件的整行都会被过滤掉。由于我们这里将时间筛选放在了where子句里,那么也就是说我们在下面这张临时表中寻找符合条件的数据集,返回的结果是这样的:
我们可以看到,id为3和4的数据被过滤掉了,这时我们在进行分组聚合统计,自然也不会出现id为3和4的数据。
因此,我们可将时间判断条件放在on子句中,这样,即使不满足条件,也会以主表中的内容为主,返回主表的全部容,不会造成数据丢失。在此基础上,我们再进行聚合统计,就不会出错了。
select user_id as buyer_id, join_date,order_date from Users u left join Orders o on u.user_id = o.buyer_id and year(order_date)='2019'
运行代码示例:
select user_id as buyer_id,join_date,count(order_id) as orders_in_2019
from users left join orders on users.user_id = orders.buyer_id and year(order_date) = 2019
group by users.user_id
题目四:指定日期的产品
题目要求:
编写一个解决方案,找出在
2019-08-16
时全部产品的价格,假设所有产品在修改前的价格都是10
。以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
本题如果直接解决,会比较复杂,整体上我们可以考虑使用子查询或者窗口函数来解决,并不难,细心就好。这里我们提供一个较为简单的思路,使用union连接查询结果。
我们可以看出,题目中存在两种情况,第一种是在
2019-08-16
这个时间之前有价格数据的,我们以最新数据为准,另一种是在该节点之前没有价格数据的,我们按照初始价格为10来考虑。我们直接分别查询两种情况的结果,使用union连接即可。对于第一种情况,我们可以分组统计找到每个产品在
2019-08-16之前
的最后一次修改时间,再去查询对应的价格数据。第二种情况则更为简单,我们只需要找到在2019-08-16之前没有出现在表里的数据。
运行代码示例:
select product_id ,new_price as price
from Products
where (product_id,change_date) in (select product_id,max(change_date) from Products where change_date <= '2019-08-16' group by product_id )
union
select product_id,10 as price
from Products
where product_id not in (select product_id from Products where change_date <= '2019-08-16')
题目五:餐馆营业额变化增长
题目要求:
你是餐馆的老板,现在你想分析一下可能的营业额变化增长(每天至少有一位顾客)。
计算以 7 天(某日期 + 该日期前的 6 天)为一个时间段的顾客消费平均值。
average_amount
要 保留两位小数。结果按
visited_on
升序排序。
表结构:
运行结果示例:
思路:
这道题看似很难,实际上思路并不复杂,用窗口函数就可以解决。但需要注意的是我们要计算的是每7天的均值,如果使用窗口函数,它会自动计算范围内的所有数据:
因此我们需要使用where子句做一个判断,手动设置从第7天开始计算。
运行代码示例:
SELECT DISTINCT visited_on,
sum_amount AS amount,
ROUND(sum_amount/7, 2) AS average_amount
FROM (
SELECT visited_on, SUM(amount) OVER ( ORDER BY visited_on RANGE interval 6 day preceding ) AS sum_amount
FROM Customer) t
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6