38 2016年的投资

[astak16]

题目

将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。

对于一个投保人，他在 2016 年成功投资的条件是：

• 他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
• 他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。

create table insurance (
	pid int, 
	tiv_2015 float, 
	tiv_2016 float, 
	lat float, 
	lon float
);

insert into insurance (pid, tiv_2015, tiv_2016, lat, lon) values 
('1', '10', '5', '10', '10'),
('2', '20', '20', '20', '20'),
('3', '10', '30', '20', '20'),
('4', '10', '40', '40', '40');

分析

这题的难点有两个：

1. 一个人在 2015 年投保额至少和任何一个人在 2015年的投保额相同
2. 筛选出不重复的地点

解决了这两个方法，这题就解决了。 方法一使用的是窗口函数，方法二使用的是子查询

SQL：方法一

select round(sum(tiv_2016), 2) tiv_2016 from (
	select
		tiv_2016,
		count(*) over(partition by tiv_2015) count_tiv_2015,
		count(*) over(partition by lat, lon) count_lat_lon
	from insurance
) as temp where count_lat_lon = 1 and count_tiv_2015 > 1

解析

使用窗口函数进行分组

1. 筛选出一个人在 2015 年投保额至少和任何一个人在 2015 年的投保额相同，就是按照 2015 年的投保额进行分组，并计算个数

   count(*) over(partition by tiv_2015) count_tiv_2015

2. 筛选不同的地点，就是按照经纬度进行分组，计算每个每个经纬度的个数

   count(*) over(partition by lat, lon) count_lat_lon

3. 将 1 、 2 两步和 tiv_2016 字段作为临时表 temp

4. 查询 temp 筛选出问题中的两步

   • count_tiv_2015 > 1 投保额 tiv_2015 至少有两个人是相等的
   • count_lat_lon = 1 经纬度是唯一的

5. 通过筛选条件后使用 sum() 计算出 tiv_2016 并且使用 round() 保留两个小数

SQL：方法二

select round(sum(tiv_2016), 2) tiv_2016 from insurance where 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
);

解析

使用子查询

1. 筛选出一个人在 2015 年投保额至少和任何一个人在 2015 年的投保额相同，使用 group by 按照 tiv_2015 分组，并且使用 count() 计算，筛选出大于 1 的数据，因为大于 1 代表至少有两个人在 2015 年投保额相同

   select tiv_2015 from insurance group by tiv_2015 having count(*) > 1;

2. 筛选不同的地点，就是按照 lat 和 lon 进行分组，这里查询的字段是使用 concat() 连接 lat, lon 后的值，并且使用 count() 计算，筛选出等于 1 的数据，因为等于 1 代表地点唯一

   ps：使用 in 操作会消耗性能

   select 
   	concat(lat, lon)
   from insurance group by lat, lon having count(*) = 1;

3. 查询 insurance ，筛选条件是两个子查询， tiv_2015 in 1 和 concat(lat, lon) in 2

4. 通过筛选条件后使用 sum() 计算出 tiv_2016 并且使用 round() 保留两个小数