每日刷力扣SQL题(七)

1321.餐馆营业额变化增长

有两种实现方式：

使用窗口函数，窗口函数比较好理解使用 6 PRECEDING AND current ROW 就能查找出来了（方案一）
使用自连，连接条件不太容易想到，需要使用 DATEDIFF 函数，这个函数可以计算两个日期之间的天数，然后使用 BETWEEN 条件（方案二和方案三）

1、要知道过去 7 天的平均消费额，需要先知道每天的总消费额，作为临时表 tmp1

2、使用窗口函数，计算过去 7 天的总的消费额，作为临时表 tmp2

3、计算过去 7 天的平均消费额，作为临时表 tmp3

4、筛选出计算数据大于等于七天的数据

WITH tmp1 AS
(
    select 
    visited_on ,
    SUM(amount) as sum_amount 
    from Customer 
    group by visited_on)
, tmp2 AS
(
    select 
    visited_on ,
    sum(sum_amount)  over (
    order by to_days(visited_on) 
    range between 6 preceding and current row) as sum_amount
    from tmp1
)
, tmp3 AS
(
    select 
    visited_on , 
    sum_amount ,round(sum_amount/7,2) as average_amount 
    from tmp2
)
select visited_on,
    sum_amount as amount , average_amount 
    from tmp3 where datediff(visited_on,(select min(visited_on) from Customer)) >=6

方法二

SELECT
	a.visited_on,
	sum( b.amount ) AS amount,
	round( sum( b.amount ) / 7, 2 ) AS average_amount
FROM
	( SELECT DISTINCT visited_on FROM Customer ) a
	JOIN Customer b ON datediff( a.visited_on, b.visited_on ) BETWEEN 0 AND 6
WHERE
	a.visited_on >= ( SELECT min( visited_on ) FROM Customer ) + 6
GROUP BY a.visited_on
ORDER BY visited_on

select visited_on, round(sum_amount, 2) as amount, round(sum_amount / 7, 2) as average_amount
from
(select distinct visited_on, 
sum(amount) over(order by visited_on asc range between interval 6 day preceding and current row) as sum_amount,
avg(amount) over(order by visited_on asc range between interval 6 day preceding and current row) as avg_amount,
dense_rank() over(order by visited_on asc) as rn
from Customer) a
where rn >= 7

602.好友申请|| ：谁有最多的好友

方法：将 requester_id 和 accepter_id 联合起来 [Accepted]
算法

成为朋友是一个双向的过程，所以如果一个人接受了另一个人的请求，他们两个都会多拥有一个朋友。

所以我们可以将 requester_id 和 accepter_id 联合起来，然后统计每个人出现的次数。

select id  , sum(id_count) num
from 
(
select requester_id  as id , count(*) as id_count
    from RequestAccepted
    group by  requester_id 
union ALL
 select accepter_id as id,count(*) as id_count
    from RequestAccepted
    group by  accepter_id 
) as t
group by id 
order by sum(id_count) DESC
limit 1 

WITH t1 as (SELECT requester_id as num
            FROM   RequestAccepted
            union all
            SELECT accepter_id num
            FROM   RequestAccepted)
SELECT num as id,count(num) as num
from t1
group by num
order by count(num) desc
LIMIT 1;

select t1.ids as id,count(*) as num
from(
        select requester_id as ids from RequestAccepted 
        union all
        select accepter_id as ids from RequestAccepted
) as t1
group by id
order by num desc
limit 1;

585.2016年的投资

错误解法：

会把位置相同 的数据过滤掉，这样在统计tiv_2015的数量时 有些count=0 过滤掉 至少有一个其他投保人在 2015 年的投保额相同 但位置不相同的数据

# Write your MySQL query statement below
select round(sum(t.tiv_2016),2)  as tiv_2016
from 
( select distinct a.pid as pid,
a.tiv_2015 as tiv_2015,
a.tiv_2016 as tiv_2016
from Insurance a join  Insurance b on a.tiv_2015 = b.tiv_2015,0 and a.pid != b.pid 
where a.pid  not in
(
    select distinct c.pid from Insurance c join  Insurance d on (c.pid != d.pid and c.lat = d.lat and c.lon = d.lon)
) 
and b.pid not in 
(
    select distinct c.pid from Insurance c join  Insurance d on (c.pid != d.pid and c.lat = d.lat and c.lon = d.lon)
)
) t

修改如下：

select round(sum(t.tiv_2016),2)  as tiv_2016
from 
( select distinct a.pid as pid,
a.tiv_2015 as tiv_2015,
a.tiv_2016 as tiv_2016
from Insurance a join  Insurance b on a.tiv_2015 = b.tiv_2015 and a.pid != b.pid 
where a.pid  not in
(
    select distinct c.pid from Insurance c join  Insurance d on (c.pid != d.pid and c.lat = d.lat and c.lon = d.lon)
) 
) t

官方解答：

为了判断一个值在某一列中是不是唯一的，我们可以使用 GROUP BY 和 COUNT。

算法

检查每一个 TIV_2015 是否是唯一的，如果不是唯一的且同时坐标是唯一的，那么这条记录就符合题目要求。应该被统计到答案中。

SELECT
    SUM(insurance.TIV_2016) AS TIV_2016
FROM
    insurance
WHERE
    insurance.TIV_2015 IN
    (
      SELECT
        TIV_2015
      FROM
        insurance
      GROUP BY TIV_2015
      HAVING COUNT(*) > 1
    )
    AND CONCAT(LAT, LON) IN
    (
      SELECT
        CONCAT(LAT, LON)
      FROM
        insurance
      GROUP BY LAT , LON
      HAVING COUNT(*) = 1
    )
;

使用窗口函数：

with t as (
    select
        *,
        sum(1) over (partition by tiv_2015) as same_tiv_2015_num,
        sum(1) over (partition by concat(lat, '-', lon)) as same_position_num
    from Insurance 
)
select 
    round(sum(tiv_2016), 2) as tiv_2016
from t
where same_tiv_2015_num > 1 and same_position_num = 1