OVER(PARTITION BY ***)用法,力扣mysql算法题,每日一题

力扣mysql题

题目: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 。

解题分析:
1.主要需要的字段:
tiv_2016:用于汇总数据
tiv_2015:有相同数据的条件
lat lon :用于区别投保人位置,且是唯一数据
2.由上可得:

SELECT tiv_2016,
COUNT(1) OVER(PARTITION BY tiv_2015 ) cnt_tiv_2015,
COUNT(1) OVER(PARTITION BY lat,lon ) cnt_lat
FROM Insurance

输出结果:

tiv_2016	cnt_tiv_2015	cnt_lat
5	3	1
30	3	2
20	1	2
40	3	1

查询出我们需要的结果:cnt_tiv_2015 相同的不唯一的,cnt_lat唯一的
3.再根据条件最后查询:

SELECT round(sum(tiv_2016),2) tiv_2016
FROM 
(
SELECT tiv_2016,
COUNT(1) OVER(PARTITION BY tiv_2015 ) cnt_tiv_2015,
COUNT(1) OVER(PARTITION BY lat,lon ) cnt_lat
FROM Insurance 
) t
WHERE cnt_tiv_2015 > 1 AND cnt_lat = 1;

输入结果:

tiv_2016
45

最后所涉及到的函数及要点
1.round(x [,y]):返回离 x 最近的整数，可选参数 y 表示要四舍五入的小数位数，如果省略，则返回整数
SELECT ROUND(1.23456) --1
SELECT ROUND(345.156, 2) – 345.16
2.COUNT(1) OVER(PARTITION BY tiv_2015) cnt_tiv_2015:分析tiv_2015这个字段的数据在表中出现相同的次数
3.COUNT(1) OVER(PARTITION BY lat,lon ) cnt_lat:分析lat,lon这两个字段在表中出现相同的次数
4.sum():返回指定字段的总和
计算 OrderDetails 表中字段 Quantity 的总和：

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

详解2和3:

SELECT *,
COUNT(1) OVER(PARTITION BY tiv_2015 ) cnt_tiv_2015,
COUNT(1) OVER(PARTITION BY lat,lon ) cnt_lat
FROM Insurance 

结果:

pid	tiv_2015	tiv_2016	lat	lon	cnt_tiv_2015	cnt_lat
1	10	5	10	10	3	1
3	10	30	20	20	3	2
2	20	20	20	20	1	2
4	10	40	40	40	3	1

pid=1时,tiv_2015=10,tiv_2015字段在表中出现了 3次=10的情况,cnt_tiv_2015=3;
pid=2时,tiv_2015=10,tiv_2015字段在表中出现了 3次=10的情况;cnt_tiv_2015=3
pid=3时,tiv_2015=20,tiv_2015字段在表中出现了 1次=20的情况;cnt_tiv_2015=1
pid=4时,tiv_2015=10,tiv_2015字段在表中出现了 3次=10的情况;cnt_tiv_2015=3
同理可得:
pid=1时,lat=10,lon=10 表中出现了 1次的情况,cnt_lat=1;
pid=2时,lat=20,lon=20 表中出现了 2次的情况,cnt_lat=2;
pid=3时,lat=20,lon=20 表中出现了 2次的情况,cnt_lat=2;
pid=4时,lat=40,lon=40 表中出现了 1次的情况,cnt_lat=1;