逐日刷力扣SQL题(七)

1321.餐馆业务额变化增长

有两种实现方式：
利用窗口函数，窗口函数比较好明白利用 6 PRECEDING AND current ROW 就能查找出来了（方案一）
利用自连，毗连条件不太容易想到，需要利用 DATEDIFF 函数，这个函数可以计算两个日期之间的天数，然后利用 BETWEEN 条件（方案二和方案三）
1、要知道过去 7 天的均匀消费额，需要先知道每天的总消费额，作为临时表 tmp1
2、利用窗口函数，计算过去 7 天的总的消费额，作为临时表 tmp2
3、计算过去 7 天的均匀消费额，作为临时表 tmp3
4、筛选出计算数据大于等于七天的数据

1. WITH tmp1 AS
2. (
3.     select
4.     visited_on ,
5.     SUM(amount) as sum_amount
6.     from Customer
7.     group by visited_on)
8. , tmp2 AS
9. (
10.     select
11.     visited_on ,
12.     sum(sum_amount)  over (
13.     order by to_days(visited_on)
14.     range between 6 preceding and current row) as sum_amount
15.     from tmp1
16. )
17. , tmp3 AS
18. (
19.     select
20.     visited_on ,
21.     sum_amount ,round(sum_amount/7,2) as average_amount
22.     from tmp2
23. )
24. select visited_on,
25.     sum_amount as amount , average_amount
26.     from tmp3 where datediff(visited_on,(select min(visited_on) from Customer)) >=6

复制代码

方法二

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

复制代码

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

复制代码

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

方法：将 requester_id 和 accepter_id 团结起来 [Accepted]
算法
成为朋侪是一个双向的过程，以是假如一个人担当了另一个人的哀求，他们两个都会多拥有一个朋侪。
以是我们可以将 requester_id 和 accepter_id 团结起来，然后统计每个人出现的次数。

1. select id  , sum(id_count) num
2. from
3. (
4. select requester_id  as id , count(*) as id_count
5.     from RequestAccepted
6.     group by  requester_id
7. union ALL
8. select accepter_id as id,count(*) as id_count
9.     from RequestAccepted
10.     group by  accepter_id
11. ) as t
12. group by id
13. order by sum(id_count) DESC
14. limit 1

复制代码

1. WITH t1 as (SELECT requester_id as num
2.             FROM   RequestAccepted
3.             union all
4.             SELECT accepter_id num
5.             FROM   RequestAccepted)
6. SELECT num as id,count(num) as num
7. from t1
8. group by num
9. order by count(num) desc
10. LIMIT 1;

复制代码

1. select t1.ids as id,count(*) as num
2. from(
3.         select requester_id as ids from RequestAccepted
4.         union all
5.         select accepter_id as ids from RequestAccepted
6. ) as t1
7. group by id
8. order by num desc
9. limit 1;

复制代码

585.2016年的投资

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

1. # Write your MySQL query statement below
2. select round(sum(t.tiv_2016),2)  as tiv_2016
3. from
4. ( select distinct a.pid as pid,
5. a.tiv_2015 as tiv_2015,
6. a.tiv_2016 as tiv_2016
7. from Insurance a join  Insurance b on a.tiv_2015 = b.tiv_2015,0 and a.pid != b.pid
8. where a.pid  not in
9. (
10.     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)
11. )
12. and b.pid not in
13. (
14.     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)
15. )
16. ) t

复制代码

修改如下：

1. select round(sum(t.tiv_2016),2)  as tiv_2016
2. from
3. ( select distinct a.pid as pid,
4. a.tiv_2015 as tiv_2015,
5. a.tiv_2016 as tiv_2016
6. from Insurance a join  Insurance b on a.tiv_2015 = b.tiv_2015 and a.pid != b.pid
7. where a.pid  not in
8. (
9.     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)
10. )
11. ) t

复制代码

官方解答：
为了判定一个值在某一列中是不是唯一的，我们可以利用 GROUP BY 和 COUNT。
算法
查抄每一个 TIV_2015 是否是唯一的，假如不是唯一的且同时坐标是唯一的，那么这条纪录就符合题目要求。应该被统计到答案中。

1. SELECT
2.     SUM(insurance.TIV_2016) AS TIV_2016
3. FROM
4.     insurance
5. WHERE
6.     insurance.TIV_2015 IN
7.     (
8.       SELECT
9.         TIV_2015
10.       FROM
11.         insurance
12.       GROUP BY TIV_2015
13.       HAVING COUNT(*) > 1
14.     )
15.     AND CONCAT(LAT, LON) IN
16.     (
17.       SELECT
18.         CONCAT(LAT, LON)
19.       FROM
20.         insurance
21.       GROUP BY LAT , LON
22.       HAVING COUNT(*) = 1
23.     )
24. ;

复制代码

利用窗口函数：

1. with t as (
2.     select
3.         *,
4.         sum(1) over (partition by tiv_2015) as same_tiv_2015_num,
5.         sum(1) over (partition by concat(lat, '-', lon)) as same_position_num
6.     from Insurance
7. )
8. select
9.     round(sum(tiv_2016), 2) as tiv_2016
10. from t
11. where same_tiv_2015_num > 1 and same_position_num = 1

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。