【SQL每日一练】day6：leetcode高频习题练习

题目一：奇数和偶数交易

题目要求：

编写一个解决方案来查找每天 奇数 交易金额和 偶数 交易金额的 总和。如果某天没有奇数或偶数交易，显示为 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