38，SQL训练之，力扣，585. 2016年的投资

最新推荐文章于 2024-09-09 22:01:06 发布

凡梦_leo

最新推荐文章于 2024-09-09 22:01:06 发布

阅读量570

点赞数 21

分类专栏： SQL实战训练文章标签： leetcode 算法数据库服务器 hadoop sql linux

本文链接：https://blog.csdn.net/qq_55006020/article/details/140859010

版权

SQL实战训练专栏收录该内容

69 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新

练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

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 。

三，建表语句

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');

四，分析

题解：

表：保险表

字段：用户投保编号，pid；

2015年的投保金额 tiv_2015;

2016年的投保金额 tiv_2016;

经度和纬度

要求：

条件1：tiv_2015 的金额要重复；

条件2：经纬度不能重复；

条件3：求出来的tiv_2016 要求四舍五入保留2位小数

思路一，常规子查询解答

思路

第一步：

以 tiv_2015分组聚合pid

第二步对表添加条件去除不重复的；拿到最终的

对这个表别名t1

第三步创建一个新表添加一个字段 concat(lat,lon)

第四步以concat(lat,lon) 分组聚合count(pid) 拿到

第五步，去除重复的也就是大于1的

最后一步，观察3个表

中间的表和左边的表连接条件是 tiv_2015 = 左表的tiv_2015

在和右边的表连接条件是concat(lat,lon) =右表的concat(lat,lon)

然后sum tiv_2016 最后四舍五入拿到最终的数据

思路二，窗口函数解答

第一步：拼接三个字段

第一个字段 concat(lat,lon)
第二个字段 co1 以tiv_2015 分组 count pid 然后开窗
第三个字段以 concat(lat,lon) 分组 conut pid 然后开窗

第二步：条件 tiv_2015 允许重复并且concat(lat,lon)不允许重复
第三步，sum tiv_2016 然后四舍五入最后别名拿到最终的数据

五，SQL解答

解法一，常规子查询解答

with  t1 as (
   select tiv_2015 as t_2,count(pid) as c_p1 from insurance group by tiv_2015 having count(pid) !=1  -- 左表 
),t2 as (
    select concat(lat,lon) as cll,count(pid) as c_p from insurance group by concat(lat,lon) having count(pid) =1  -- 右表
),t3 as (
  select pid, tiv_2015, tiv_2016, lat, lon,concat(lat,lon) as ll1 from insurance   -- 原表 增加字段 concat
),t4 as (
    select * from t3 join t1 on t3.tiv_2015 =t1.t_2    -- 三个表内连接  拿到都满足的部分
                     join t2 on t3.ll1=t2.cll
)
select round(sum(tiv_2016),2) as tiv_2016
from t4

解法二，窗口函数解答

with t1 as (
   select pid, tiv_2015, tiv_2016, lat, lon,
       concat(lat,lon) as cll,
       count(pid) over(partition by tiv_2015) co1,
       count(pid) over(partition by concat(lat,lon)) co2
from insurance
)
select round(sum(tiv_2016),2) as tiv_2016 from t1 where t1.co1 != 1 and t1.co2 =1;