逐日刷力扣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 团结起来
算法
成为朋侪是一个双向的过程，以是假如一个人担当了另一个人的哀求，他们两个都会多拥有一个朋侪。
以是我们可以将 requester_id 和 accepter_id 团结起来，然后统计每个人出现的次数。
select id, sum(id_count) num
from
(
select requester_idas id , count(*) as id_count
from RequestAccepted
group byrequester_id
union ALL
select accepter_id as id,count(*) as id_count
from RequestAccepted
group byaccepter_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 joinInsurance b on a.tiv_2015 = b.tiv_2015,0 and a.pid != b.pid
where a.pidnot in
(
select distinct c.pid from Insurance c joinInsurance 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 joinInsurance 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 joinInsurance b on a.tiv_2015 = b.tiv_2015 and a.pid != b.pid
where a.pidnot in
(
select distinct c.pid from Insurance c joinInsurance 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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]