由partition看窗口函数

    最近要完成一个项目，有一个查询可难住了笔者，无论是子查询还是分组，都没弄出来，还是基础知识不行啊。不过呢，可以查资料，最后用一个窗口函数解决了问题。由于开始的数据库是Access，后来笔者导成SQL Server的，一下子明白了很多。

    数据库类似是这个样子滴：

    Employee表的字段：

    empId,englishName,depId

    Department表的字段：

    depId,deptName

    需求是：查找出Department表的所有字段，但是在前面显示出该部门的人数。

    就是这样：

    peopleCount    depId deptName

    25                     1       人力资源部

    42                     2       市场营销部

    

    一、分组的失败

    首先说说分组的概念。根据关系数据库理论，分组的概念是（G，·，e ），其中G是聚集，·是二目运算，e是G的一个成员，SELECT和GROUP BY的关系如下：

    （一）当使用聚集函数（例如count），对于SELECT 列表中的项，如果没有把它当做聚集函数的参数使用，必须是分组的一部分，例如有一个SQL语句：

      

 

SELECT depId
       ,count(*) as peopleCount
FROM Employee

    那就必须在GROUP BY中出现deptId：

     

SELECT depId
       ,count(*) as peopleCount
FROM Employee 
GROUP BY depId

    但是窗口函数是例外的，不必（也不能）出现在Group BY子句中。

    而对于可能更改分组（或者聚集函数返回值，例如新的列），则一定要包含在GROUP BY子句之中。否则就会报错。

    二、窗口函数

    知道了分组的基本概念之后，理解窗口函数就容易了，与聚集函数一样，窗口函数也是针对元组（就是行）进行聚集，但是不像聚集函数那样只返回一个值（也就是聚集所有行，然后计算），窗口函数可以为每个分组返回多个值。执行聚集的元组（行组）是窗口。

     例如第一个代码：select count(*) as cnt from Employee 这很容易，只返回一行，但是往往需要从不表示聚集或者其他聚集的行中访问这种聚集数据，窗口函数就解决了这个问题。例如下面的SQL语句表示用窗口函数从细节行访问聚集数据，就是员工总数：

    

SELECT EnglishName
       ,deptId
       ,count(*) over() as peopleCount
FROM Employee
ORDER BY 2

 

      OVER关键字表明，把Count当成窗口函数，对于查询返回的每一行，它返回了表中所有行的计数，括号表示还可以接收一些条件来限定行数，即多一层聚集。

      三、partition的使用

      partition就可以成为那个括号中的条件，它能够定义行的分区或者分组，以完成聚集。空的括号表示分区是整个结果集。partition by是一个移动的GROUP BY，例如：

     

SELECT EnglishName
       ,depId
       count(*) over(partition by deptId) as peopleCount
FROM Employee
ORDER BY 2

     通过partition by depId，为每个部门执行count同一个部门的每个count值相同。所以会返回很多相同的行，这时可以通过内联视图的方式进行解决：

  

SELECT DISTINCT EnglishName
                ,depId
                ,peopleCount
FROM
    (SELECT EnglishName
            ,depId
            ,count(*) over(partition by depId) as peopleCount
     FROM Employee
     ORDER BY 2
    ) x

 

            如果要在Access中使用，由于Access不支持窗口函数，只能使用标量子查询，代码如下：

           

Code
SELECT DISTINCT EnglishName
                ,depId
                ,peopleCount
FROM
    (SELECT e.EnglishName
            ,e.depId
            ,(select count(*) from emp d where e.depId=d.depId) as peopleCount
     FROM Employee e,Department d
     ORDER BY 2
    ) x

      需要指出的是，窗口函数经过了数据库专门的优化，所以性能较为优异，比标量子查询要好，所以应当尽量使用。