力扣面试_SQL50题

高频 SQL 50 题（基础版） - 学习计划 - 力扣（LeetCode）全球极客挚爱的技术成长平台

585. 2016年的投资

Create Table If Not Exists Insurance (pid int, tiv_2015 float, tiv_2016 float, lat float, lon float);
Truncate table Insurance;
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('1', '10', '5', '10', '10');
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('2', '20', '20', '20', '20');
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('3', '10', '30', '20', '20');
insert into Insurance (pid, tiv_2015, tiv_2016, lat, lon) values ('4', '10', '40', '40', '40');

题目：

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和： 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。 tiv_2016 四舍五入的 两位小数 。 */

解析：

-- 分析一：他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
-- 就是说按照tiv_2015分组之后，count(tiv_2015) >= 2

select *, count(*) over(partition by tiv_2015) cnt from Insurance;

-- 分析二：他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
-- 就是说按照  lat, lon 分组之后，count( lat, lon) = 1

select
    *,
    count(*) over(partition by tiv_2015) cnt_1,
    count(*) over(partition by lat, lon) cnt_2
from Insurance;

-- 过滤

with tb as (
    select
        tiv_2016 ,
        count(*) over(partition by tiv_2015) cnt_1,
        count(*) over(partition by lat, lon) cnt_2
    from Insurance
)
select cast(sum(tiv_2016) as decimal (10, 2)) tiv_2016  from tb where cnt_1 >= 2 and cnt_2 = 1;