SQL-leetcode-585. 2016年的投资

585. 2016年的投资

Insurance 表：

±------------±------+
| Column Name | Type |
±------------±------+
| pid | int |
| tiv_2015 | float |
| tiv_2016 | float |
| lat | float |
| lon | float |
±------------±------+
pid 是这张表的主键(具有唯一值的列)。
表中的每一行都包含一条保险信息，其中：
pid 是投保人的投保编号。
tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
lon 是投保人所在城市的经度。题目数据确保 lon 不为空。

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：

他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016 四舍五入的 两位小数 。

查询结果格式如下例所示。

示例 1：

输入：
Insurance 表：
±----±---------±---------±----±----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
±----±---------±---------±----±----+
| 1 | 10 | 5 | 10 | 10 |
| 2 | 20 | 20 | 20 | 20 |
| 3 | 10 | 30 | 20 | 20 |
| 4 | 10 | 40 | 40 | 40 |
±----±---------±---------±----±----+
输出：
±---------+
| tiv_2016 |
±---------+
| 45.00 |
±---------+
解释：
表中的第一条记录和最后一条记录都满足两个条件。
tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。

第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。
因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。

题解

• 在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
  含义：count(tiv_2015) 按人分组要大于1

• 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
  含义： count(tiv_2015) 按 “lat、lon” 分组要等于1

• tiv_2016 四舍五入的 两位小数 。
  含义：round(col, 2) 一下

于是乎应该想到的是 group by 、count ，要特别复杂的子查询吧？
有没有简单的做法呢？
窗口函数：count、sum … row_number() over

方法一 窗口函数计数

我觉得这个比较清晰，也可以跟原本的数据合到一起来用挺好的。
【推荐】

select
    round(sum(tiv_2016),2) as tiv_2016
from (
    select 
        pid,tiv_2015,tiv_2016,lat,lon,
        count(tiv_2015) over(partition by tiv_2015) as rn_1,
        count(tiv_2015) over(partition by lat,lon) as rn_2
    from Insurance
) tmp where rn_2=1 and rn_1>1

方法二 join

思路：count(tiv_2015) 按人分组要大于1、count(tiv_2015) 按 “lat、lon” 分组要等于1
【有点麻烦不推荐】

select
    round(sum(tiv_2016),2) as tiv_2016
from Insurance i1 inner join (
    select tiv_2015 
	from Insurance 
	group by tiv_2015 
	having count(tiv_2015) > 1 
) i2 on i1.tiv_2015 = i2.tiv_2015
inner join (
    select lat, lon	
        from Insurance 
        group by lat, lon
        having count(pid) = 1 
) i3 on i1.lat = i3.lat and i1.lon = i3.lon

方法三 子查询

找出相同数据的2015年的数据，再找出经纬度不重复的数据即可

select
    round(sum(tiv_2016),2) as tiv_2016
from Insurance 
where tiv_2015 in (
	select tiv_2015 
	from Insurance 
	group by tiv_2015 
	having count(tiv_2015) > 1 
) 
and concat(lat, ',', lon) in (
	select concat(lat, ',', lon)	
	from Insurance 
	group by lat, lon
	having count(pid) = 1 
)

其他就先到这里吧