SQL Server OVER函数

OVER (PARTITION BY)子句可以和以下的函数一起使用：

SUM()：计算每个分组内指定列的总和。
AVG()：计算每个分组内指定列的平均值。
COUNT()：计算每个分组内符合条件的行数。
MAX()：返回每个分组内指定列的最大值。
MIN()：返回每个分组内指定列的最小值。
ROW_NUMBER()：为查询结果集中的每一行赋予一个唯一的整数值，用于比较和排序。
RANK()：为每个分组内的行分配一个排名，相同值的行具有相同的排名，并跳过下一个排名。
DENSE_RANK()：为每个分组内的行分配一个连续的排名，相同值的行具有相同的排名。

注意，OVER (PARTITION BY)子句必须紧随以上函数之后，并且语法为FUNCTION() OVER (PARTITION BY …)，其中FUNCTION()是想要使用的聚合函数名称。

以下是一个使用 OVER (PARTITION BY) 子句进行分组计算的示例：

假设我们有这样一张表格students，它包含了学校的成绩记录：

学生ID 学生姓名 学科 分数
1 Tom Math 80
2 Jack Math 70
3 Rose Math 90
4 Tom English 85
5 Jack English 75
6 Rose English 95
现在我们想要找出每个学生在各自的学科中的排名和平均分数。为此，我们可以使用以下 SQL 查询语句：

SELECT 
    学生ID, 学生姓名, 学科, 分数,
    RANK() OVER (PARTITION BY 学科 ORDER BY 分数 DESC) AS 排名,
    AVG(分数) OVER (PARTITION BY 学科) AS 平均分
FROM students;

这个查询将会按照学科对结果集进行分区，并在每个分区内按照分数从高到低排序，然后为每个学生在其所在的学科中计算排名和平均分。最终的查询结果如下所示：

学生ID 学生姓名 学科 分数 排名 平均分
1 Tom Math 80 2 80
2 Jack Math 70 3 80
3 Rose Math 90 1 80
4 Tom English 85 1 85
5 Jack English 75 2 85
6 Rose English 95 1 85
从查询结果可以看到，我们成功地计算出了每个学生在每个学科中的排名和平均分数。这是一个比较实用的例子，通过使用 OVER (PARTITION BY) 子句，我们能够更加灵活地进行数据分析和处理。

分组排序后一个字段取得sum，其他字段取每一组第一条的数据

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY FILEID ,DATAKB ORDER BY FILEID asc,DATAKB asc) AS row_num,
         SUM(RECNO) OVER (PARTITION BY FILEID,DATAKB) as group_sum
  FROM aaaa
) t1
WHERE t1.row_num = 1;

如果你想在每一行数据中返回总行数，可以使用窗口函数结合聚合函数来实现。在SQL中，你可以通过以下方式实现：

sql

SELECT 
    *,
    COUNT(*) OVER () AS total_rows
FROM
    your_table;

这里的关键点是 COUNT(*) OVER ()：

COUNT(*) 是一个聚合函数，用于计算行数。
OVER () 指定了一个窗口，其中的空括号表示窗口包含所有的行，没有特定的排序或分区。

在 OVER() 子句中，你可以使用以下窗口函数：

SUM(): 计算窗口内的总和。
AVG(): 计算窗口内的平均值。
MIN(): 查找窗口内的最小值。
MAX(): 查找窗口内的最大值。
RANK(): 根据排序顺序对行进行排名。
DENSE_RANK(): 与 RANK() 类似，但对排名没有间隙。
NTILE(n): 将窗口内的行分成 n 组，并分配组号。
LEAD(): 返回当前行之后指定偏移量的值。
LAG(): 返回当前行之前指定偏移量的值。
ROW_NUMBER(): 为窗口内的每一行分配唯一的序号。
这些函数可以结合 PARTITION BY 和 ORDER BY 来定义计算的窗口。

例子
SQL 查询，它将返回每组的行号、每组的总数、当前是第几组，并且还会返回总共分了多少组：

sql

SELECT 
    region, 
    product_id,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_date) AS row_num,  -- 每组的行号
    COUNT(*) OVER (PARTITION BY region) AS total_count,  -- 每组的总数
    DENSE_RANK() OVER (ORDER BY region) AS group_rank,  -- 当前是第几组
    COUNT(DISTINCT region) OVER () AS total_groups  -- 总共分了几组
FROM sales;

解释：
COUNT(DISTINCT region) OVER (): 这个部分计算了总共分了多少组，COUNT(DISTINCT region) 会统计唯一的 region 值，并且 OVER () 表示对整个结果集进行计算，而不是分组。

示例数据：
假设 sales 表数据如下：

region product_id sales_date
East A001 2025-01-01
East A002 2025-01-02
West B001 2025-01-01
East A003 2025-01-03
West B002 2025-01-02
输出结果：
region product_id row_num total_count group_rank total_groups
East A001 1 3 1 2
East A002 2 3 1 2
East A003 3 3 1 2
West B001 1 2 2 2
West B002 2 2 2 2