ToB企服应用市场:ToB评测及商务社交产业平台

标题: 逐日刷力扣SQL题(七) [打印本页]
作者: 用户云卷云舒    时间: 2024-8-28 06:03
标题: 逐日刷力扣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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4