最近SQL刷题，真是开了眼了

嗨，朋友们好呀~

都说21天养成一个习惯，这才第5天，我都快养成每天两道SQL题的习惯了。

alt

事情是这样的，之前读了一本书，也给大家做了推荐--《SQL数据分析：从基础破冰到面试题解》。这本书的基础部分很快就读完了，而SQL题目实战部分却被我一再耽搁。既然自己无法积极主动，就索性组织了几个人，一起刷题，相互监督。

目前的刷题进展地很顺利，于我个人而言，不仅完成了我的刷题任务，且还从大家的解题方法里学到了新思路、新方法。这不，迫不及待地跟大家分享下。

本次分享包括三部分内容：书中的两道题目，交流群讨论的一道题目，最近刷题后的一些想法。

一、书中的两道题目

题目1：

有一张商品优惠活动时间表product_promotion，包括字段commodity_id（商品DI）：varchar，start_date（优惠活动起始日期）：DATE，end_date（优惠活动结束日期）：DATE。查询在2021年1月7日至2021年1月9日期间参与优惠活动的商品，输出商品ID。

输入与输出示例为：

alt

题目看上去很简单吧！也确实很简单，一道简单题目，逻辑不复杂，只需要限定好筛选条件即可。

不过简单题目也会出现一些好玩的东西。比如没关注输出示例，未升序排列商品ID；比如信息满满，感觉小菜一碟，结果筛选条件漏了，输出变少了；再比如我仔仔细细写满了所有条件发现别人少我两个条件也写得超好。

select commodity_id
from product_promotion
where (start_date >= '2021-01-07' and end_date <= '2021-01-09')
or (start_date <= '2021-01-07' and end_date >= '2021-01-09')
or (start_date <= '2021-01-09' and end_date >= '2021-01-09')
or (start_date <= '2021-01-07' and end_date >= '2021-01-07')
order by commodity_id

我是这样写得，按我个人思路，写得不重不漏，但其实还能这样写。

select commodity_id
from product_promotion
where (start_date <= '2021-01-07' and end_date >= '2021-01-07' )
or (start_date >= '2021-01-07' and start_date <= '2021-01-09')
order by commodity_id

哎，还能这样。

select commodity_id
from product_promotion
where end_date >= '2021-01-07' and start_date <= '2021-01-09'
order by commodity_id

限定条件是越来越少，这波操作可着实让我学到了，你们学到了吗？

题目2：

现有一张app累计下载情况表app_download，该表记录了应用商店中app累计下载次数的信息，包括三个字段，app_id（app ID）：varchar，app_type（app类型）：varchar，download（下载次数）：int。查询不同类型app的平均下载次数，需将下载次数排在前10%与后10%的app排除在外。输出app_type（app类型），avg_download（平均下载次数）。

输入与输出示例为：

alt

这是一道逻辑稍微有点复杂的题目，难度中等。这道题目在群里引发了热烈的讨论。

我是这样做的。

##1.按下载次数降序排序，剔除前10%和后10%
##2.按app类型求平均下载次数
-- 法1：以前的做法，通过排序和计数手动计算top位置
select app_type
      ,round(avg(download),4) as avg_download -- 计算平均下载次数，保留4位小数
from 
(
     select app_id 
          ,app_type
          ,download
          ,row_number()over(order by download desc)/sum(1) over() as num --计算每个app_id的下载次数top位置
     from app_download
)as t 
where num > 0.1 and num < 0.9 -- 排除前10%和后10%
group by app_type
order by app_type -- 排序

-- 法2：知道ntile()函数后，用此函数进行分桶
select app_type
      ,round(avg(download),4) as avg_sownload -- 计算平均下载次数，保留4位小数
from 
(
      select app_id 
            ,app_type
            ,download
            ,ntile(10) over(order by download desc) as num -- 分桶，计算每个app_id的下载次数top位置
      from app_download
)as t 
where num > 1 and num < 9 -- 排除前10%和后10%
group by app_type
order by app_type -- 排序

很明显我为了证明我不是以前的我了，写了两种方法。第一种是我之前不太熟悉各种函数的时候，会选择用排序/总记录数的方式来求得百分比。第二种是我学会分桶函数后使用的一种“装逼”手法。

我以为我挺能了，但人外有人，山外有山，我把percnt_rank()和cume_dist()函数忘记了，也不是，第二个函数我见都没见过（惭愧）。

总结来说。

1.读题很重要，不要忽略条件。当然此题目其实较为模糊，也没说下载次数就必须是降序排列呀？前后各10%包含与否也没说明。我们面对具体业务时需先明确需求。

2.重要的是思路，也就是你的逻辑，只要逻辑在，随便选个能实现的函数即可，当然实际工作中也需考虑效率。

3.对于同一需求，使用不同函数时需要注意边界条件，避免出现数据错误（多选、少选问题）。

4.小注：此题目示例有10条数据不代表只有10条数据，不可耍滑头。

二、交流群讨论的一道题目

有天我在做一个业务需求，按照想法和逻辑，选了一种表连接的方式达到了目的。但突然不太满足，是否还有别的方法可实现呢？

晚些时候我便类比了一个例子发到群里，想看看大家的想法和解决方法。

题目要求为：输出每个uid的第一第二position。

输入与输出示例为：

alt

我最初的解法是这样的。

-- 思路：左表取第一个position（使用窗口函数），右表取第二个position（使用窗口函数），left join一下
select t1.uid
      ,t1.position as position1
      ,t2.position as position2
from 
(
    select uid
        ,position
    from 
    (
        select uid
            ,position
            ,row_number() over(partition by uid order by log_time) as rnk 
        from tmp_table 
    )as t 
    where rnk = 1
)as t1 --第一个position
left join 
(
    select uid
        ,position
    from 
    (
        select uid
            ,position
            ,row_number() over(partition by uid order by log_time) as rnk 
        from tmp_table 
    )as t 
    where rnk = 2
)as t2 -- 第二个position
on t1.uid = t2.uid

之后我也考虑了窗口函数，群里小伙伴们也恰好给到了类似的解题思路和方法。

-- 思路：使用lead/lag实现取下一个position，取最近（最小）时间的记录（使用rank）
select uid 
	  ,position1
      ,position2
from 
(
	select uid
    	,log_time
    	,position as position1
    	,lead(position)over(partition by uid order by log_time) as position2
    	,row_number() over(partition by uid order by log_time) as rnk
	from tmp_table 
)as t 
where rnk = 1

-- 法3：
-- 先使用窗口函数排序，得到rank，使用case when对rank进行判断
select uid
      ,max(case when rnk = 1 then position end) as position1 -- 使用max选出非空值
      ,max(case when rnk = 2 then position end) as position2
from 
(
    select uid
        ,position
        ,row_number() over(partition by uid order by log_time) as rnk 
    from tmp_table 
)as t 
group by uid

这不就卷起来了吗？

注意：此例子只是我随便抽象出来的，不具有实际业务意义，有同学考虑到用户访问页面等实际情况，想到了需要去重，很严谨，具体业务要具体分析。

三、刷题后的一些想法

刷了4天的题目，收获颇丰。

一起刷题的几位同学都比较善于思考和总结，对于一个问题总有打破纱窗问到底之势。

也超喜欢这种思维碰撞的感觉，很刺激。同一个问题，大家的思路可能有一丢丢不一样，方法也不一样，但最终得到的结果却是一样的。

我有时候抽查大家的文档，同一个问题，有用表连接的，有用开窗函数的，有定义变量的。

就连从字符串中按照某种规则取子串，方法也不一样，多的是我没见过的，还需要学习的方法。是什么呢？先卖个关子，之后再找机会告诉大家~

好啦，今天的分享就到这里了，希望今日分享于你有益。