over()函数使用

1、RATIO_TO_REPORT() 是一个分析函数，主要用于计算一个值在组内占总和的比例。
不分组计算比例：
SELECT Department, Sales, RATIO_TO_REPORT(Sales) OVER () AS Sales_Ratio
FROM sales;
分组计算比例：
SELECT Region, Department, Sales, RATIO_TO_REPORT(Sales) OVER (PARTITION BY Region) AS Sales_Ratio
FROM sales;

2、RANK() 是一种窗口函数，用于在查询结果集中基于排序规则为每行分配一个排名.
简单排名：
SELECT employee_id, department_id, salary,RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
分组排名：
SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

3、group by 和over()分析：
SQL1:
select nf,orgid_,xxbm,xxmc,sum(XSDYQKDYZS) from f_bwlnhz
where nf='2024' and xxmc='北京大学' and DTDP_TYPE NOT LIKE '%1' AND XDM >= 9 AND XDM <= 17 AND XDM NOT IN ('9','12','15')
group by nf,orgid_,xxbm,xxmc,RIGHT(XMC,2)

SQL2:
select nf,orgid_,xxbm,xxmc,sum(XSDYQKDYZS) over(partition by nf,orgid_,xxbm,xxmc,RIGHT(XMC,2)) from f_bwlnhz
where nf='2024' and xxmc='北京大学' and DTDP_TYPE NOT LIKE '%1' AND XDM >= 9 AND XDM <= 17 AND XDM NOT IN ('9','12','15')

上述两个SQL中，SQL2的结果没有去重：

1. GROUP BY(SQL1)：GROUP BY 会将查询结果分组，每个分组仅保留一行。在 GROUP BY 的分组字段（如 nf, orgid_, xxbm, xxmc, RIGHT(XMC,2)）相同的情况下，所有满足条件的记录会被合并，并对指定字段（如 SUM(XSDYQKDYZS)）计算聚合值。

2. OVER()(SQL2):窗口函数 SUM() OVER() 不会压缩结果集，它为每一行单独计算窗口内的聚合值。即使多行的数据满足相同的分区条件（PARTITION BY nf, orgid_, xxbm, xxmc, RIGHT(XMC,2)），这些行仍然保持独立存在，每一行都会显示该分区的聚合值。