这题非常适合用来举例如何巧妙的使用窗口函数解决实际问题~

题目描述

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. 要求tiv_2015至少与其他一人的投保额相同，也就是统计相同的投保额时，计数大于等于2
2. 基于以上分析，那么我使用count(tiv_2015) over (partition by tiv_2015) as 2015_num 来记录每个tiv_2015出现的次数
3. 此题目的另外一个要求，(lat,lon)不能和其他任何一个人完全相同，那么也可以使用相同的思路来解决这个问题，不过区别在于要使用count(*) 因为count()无法输入多个参数，只能使用*的方式来记录多参数的计数，count(*) over (partition by lat,lon) as tar_num
4. 在完成以上两个数量的记录之后，就可以直接使用where的方式来筛选 2015_num >=2 且 tar_num =1的行
5. 使用select sum()方法即可对目标行的tiv_2016投保额进行计数，题解写在下方

题解

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