SQL窗口函数——COUNT(*) OVER (PARTITION BY ...)

题目一：

Insurance 表有以下几列

pid 是这张表的主键(具有唯一值的列)。表中的每一行都包含一条保险信息，其中：
①pid 是投保人的投保编号。
②tiv_2015 是该投保人在 2015 年的总投保金额

③tiv_2016 是该投保人在 2016 年的总投保金额。
④lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
⑤lon 是投保人所在城市的经度。题目数据确保 lon 不为空。

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

• 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。

• 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。

tiv_2016 四舍五入的 两位小数 。

笨办法 ：

①查询2015年和其他投保人的投保金额相同的投保金额，用金额反推投保人的投保编号。

SELECT tiv_2015 as num
FROM Insurance
GROUP BY tiv_2015
HAVING COUNT(tiv_2015) >= 2)) p2 on t.pid = p2.pid

②查询投保人城市与其他投保人不同的投保人的投保编号。

SELECT pid,CONCAT(lat,lon) as d
FROM Insurance
Group by d
having COUNT(d) = 1

③通过inner join 内链接来查询符合条件的tiv_2016，求和(SUM)，并四舍五入的 两位小数（round）。

SELECT round(sum(tiv_2016),2) as tiv_2016
FROM Insurance as t
INNER JOIN
(SELECT pid
FROM Insurance
WHERE tiv_2015 in
(SELECT tiv_2015 as num
FROM Insurance
GROUP BY tiv_2015
HAVING COUNT(tiv_2015) >= 2)) p1 on t.pid = p1.pid
INNER JOIN
(SELECT pid,CONCAT(lat,lon) as d
FROM Insurance
GROUP by d
having COUNT(d) = 1) as p2 ON t.pid = p2.pid

高效办法：

使用COUNT(*) OVER (PARTITION BY ...)窗口函数——在每一行上执行聚合操作

它用于在查询结果中执行聚合操作，而不会影响查询的分组行数，同时在每个分组内进行计数。COUNT(): 这表示要计算在窗口内的行数， 代表计算所有行。
OVER: 这引入了窗口函数的定义，它告诉数据库引擎在什么样的窗口内执行计数。
(PARTITION BY …): 这部分定义了窗口的分区方式，即如何将数据划分为不同的分组。PARTITION BY 子句指定一个或多个列，根据这些列的值将数据划分为不同的窗口。

with temp as (
    SELECT *,
        count(*) over(partition by tiv_2015) as num1,
        count(*) over(partition by lat,lon) as num2
    FROM insurance
)
SELECT round(sum(tiv_2016),2) as tiv_2016
FROM temp
WHERE num1 > 1 and num2 = 1;

Insurance表数据：

借鉴学习：

COUNT(*) OVER (PARTITION BY ...)窗口函数——在每一行上执行聚合操作_count over partition by-CSDN博客