6、刷sql题第6~9天

第六天

题目内容：

表：Trips

表：Users

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。

返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

查询结果格式如下例所示。
示例：

我的思路：

题目要求的是取消率，那么就可以转化为求取消的有效订单和总的有效订单数目，然后相除即可。
题目上说有效订单是指乘客和司机都必须未被禁止，那首先要在Users表中查找到未被禁止的所有类型的用户，然后在订单中筛选出来订单里用户和司机同时没有被禁止的数据，同时过滤出日期范围内的数据，最后按日期进行分组求数量，对于总有效订单数直接count(*)即可，取消订单可以使用sum(if())进行求和。
SQL语句如下：

select
    request_at Day,
    cast(sum(if(t1.status in ('cancelled_by_driver','cancelled_by_client'),1,0))*1.0/count(*) as DECIMAL(16,2)) as 'Cancellation Rate'
from
(
    select
        client_id,
        driver_id,
        status,
        request_at
    from Trips
    where request_at between '2013-10-01' and '2013-10-03'
)t1
where client_id in 
(
    select
        users_id
    from Users
    where banned='No'
)
and driver_id in 
(
    select
        users_id
    from Users
    where banned='No'
)
group by request_at

要注意的是：结果保留两位小数，使用cast方法；结果字段中别名有空格，要加单引号

第七天

题目内容：

表: Seat

编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序 返回结果表。

查询结果格式如下所示。

示例 1:

我的思路：

看到题目，我首先就想到了要确定总体数据是奇数还是偶数，然后发现这个思路是错误的。。。
其实，交换座位，对于一条数据来说，无非就两种情况，处于奇数位置的数据要拿到它的下一行的数据（对于最后一个不需要交换）；处于偶数位置的数据要拿到它的上一行的数据。所以，其实还是窗口函数的方法，先不区分是奇数位置还是偶数位置，先把当前数据的前一条和后一条数据都拿到，最后再进行判断。
如果当前数据为奇数位置，就输出它的下一条数据的内容（如果为null，代表为最后一条数据，就输出自身）；如果当前数据为偶数位置，就输出它的上一条内容。
要注意的地方：如果只有一条数据，那它拿到的前一条数据为null，后一条数据也为null；而对于最后一条数据，它无论什么时候拿到的后一条数据都为null；
sql语句如下：

select
    id,
    if(id%2=0,last,ifNull(next,student)) student
from
(
    select
        id,
        student,
        LAG(student,1) over() last,
        LEAD(student,1) over() next
    from Seat
)t1

第七天

题目内容：

表：Stadium

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列 的结果表。

查询结果格式如下所示。
示例 1:

我的思路：

要查找连续三行及以上的记录，它们的people字段都大于等于100，我还是想到了开窗。。。我判断当前记录是否符合条件，其实连续三行及以上，实际上判断三行是否连续即可，那当前记录有三种情况。第一种情况，当前记录作为第三个数，那就要拿到它的前两个数且要保证前两个数都大于等于100；第二种情况，当前记录作为第二个数，那就要保证它的前一个数和后一个数都大于等于100；第三种情况，当前记录作为第一个数，那就要保证它的后两个数都大于等于100。所以要拿到当前行的前两行数据和后两行数据，然后三种情况判断即可。
sql语句如下：

select
    id,
    visit_date,
    people
from
(
    select
        id,
        visit_date,
        people,
        LAG(people,2) over() last_last,
        LAG(people,1) over() last,
        LEAD(people,1) over() next,
        LEAD(people,2) over() next_next
    from Stadium
)t1
where people>=100 and ((last_last>=100 and last>=100) or (last>=100 and next>=100) or (next>=100 and next_next>=100))

别人的思路：

我自己实现以后，去看别人有没有什么好的思路，看到了一个，觉得很不错，也提一下。
首先，我们可以查找出people字段大于等于100的记录，然后对这些记录进行排序，用id减去排序的值，连续的数据得到的结果肯定是相同的（好好理解这个地方）。比如查找完大于等于100的记录后，剩下记录的id为1、2、3、6、8、9、10…，这7个数据的排序的结果为1、2、3、4、5、6、7，那么就可以看出来，连续的1、2、3减去排序值后都为0，连续的8、9、10减去排序的值后都为3，之后我们按差值开窗，进行count，查找count值大于等于3的记录，即为所求的记录。
sql语句如下：

select
    id,
    visit_date,
    people
from
(
    select
        id,
        visit_date,
        people,
        count(*) over(partition by cha) num
    from
    (
        select
            id,
            visit_date,
            people,
            id-rank() over(order by id) 'cha'
        from
        (
            select
                id,
                visit_date,
                people
            from Stadium
            where people>=100
        )t1
    )t2
)t3
where t3.num>=3
order by id asc

第八天

题目内容：

描述
有一个试卷作答记录表exam_record，请从中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。
示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

示例输出：

解释：表示截止当前，有11次试卷作答记录，已完成的作答次数为7次（中途退出的为未完成状态，其交卷时间和份数为NULL），已完成的试卷有9001和9002两份。

我的思路：

要求的有三个值，总作答次数就是总的数据的条数，直接count(*)即可；试卷已完成作答数代表了开始时间和结束时间都不为null，当然还代表了score字段不为null，所以count所有score不为null的数据；已完成的试卷数，首先就是score不为null，其次试卷有可能重复，所以要对score不为null的exam_id去重。
sql语句如下：

select
    count(*),
    count(if(score is not null,1,null)),
    count(distinct(if(score is not null,exam_id,null)))
from exam_record

第九天

题目内容：

描述
请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
示例数据 exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

examination_info表（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

示例输出数据：

解释：试卷9001和9002为SQL类别，作答这两份试卷的得分有[80,89,87,90]，平均分为86.5，不小于平均分的最小分数为87

我的思路：

从表中可以看到，SQL类型的试卷可能有多个exam_id，所以要先在examination_info中查找到SQL类型的所有exam_id，然后在exam_record中找到所有SQL类型且score不为null的数据，然后对这些数据求平均值，并得出score大于等于平均值的数据，然后排序并输出一条数据即可。
sql语句如下：

select
    score
from
(
    select
        score,
        avg(score) over() score_avg
    from
    (
        select
        exam_id,
        score
        from exam_record
        where score is not null
    )t1
    inner join
    (
        select
        exam_id
        from examination_info
        where tag='SQL'
    )t2
    on t1.exam_id=t2.exam_id
)t3
where t3.score>=t3.score_avg
order by score
limit 1;