postgresql 计算 - 分组 top SQL

使用 postgresql over 窗口函数

1. 数据展现
postgres=# select depname,empno,salary ,enroll_date from empsalary order by depname,salary ;

 depname  | empno | salary | enroll_date
-----------+-------+--------+-------------
 develop   |     7 |   4200 | 2008-01-01
 develop   |     9 |   4500 | 2008-01-01
 develop   |    10 |   5200 | 2007-08-01
 develop   |    11 |   5200 | 2007-08-15
 develop   |     8 |   6000 | 2006-10-01
 personnel |     5 |   3500 | 2007-12-10
 personnel |     2 |   3900 | 2006-12-23
 sales     |     4 |   4800 | 2007-08-08
 sales     |     3 |   4800 | 2007-08-01
 sales     |     1 |   5000 | 2006-10-01
 sales     |     6 |   5500 | 2007-01-02
(11 rows)



rank 展现分组排名
postgres=# select depname,empno,rank() OVER (PARTITION BY depname ORDER BY salary),salary,enroll_date FROM empsalary ;
  depname  | empno | rank | salary | enroll_date
-----------+-------+------+--------+-------------
 develop   |     7 |    1 |   4200 | 2008-01-01
 develop   |     9 |    2 |   4500 | 2008-01-01
 develop   |    10 |    3 |   5200 | 2007-08-01
 develop   |    11 |    3 |   5200 | 2007-08-15
 develop   |     8 |    5 |   6000 | 2006-10-01
 personnel |     5 |    1 |   3500 | 2007-12-10
 personnel |     2 |    2 |   3900 | 2006-12-23
 sales     |     4 |    1 |   4800 | 2007-08-08
 sales     |     3 |    1 |   4800 | 2007-08-01
 sales     |     1 |    3 |   5000 | 2006-10-01
 sales     |     6 |    4 |   5500 | 2007-01-02

postgres=# select depname,empno,salary ,enroll_date from empsalary order by depname,salary ; depname | empno | salary | enroll_date
-----------+-------+--------+-------------
 develop | 7 | 4200 | 2008-01-01
 develop | 9 | 4500 | 2008-01-01
 develop | 10 | 5200 | 2007-08-01
 develop | 11 | 5200 | 2007-08-15
 develop | 8 | 6000 | 2006-10-01
 personnel | 5 | 3500 | 2007-12-10
 personnel | 2 | 3900 | 2006-12-23
 sales | 4 | 4800 | 2007-08-08
 sales | 3 | 4800 | 2007-08-01
 sales | 1 | 5000 | 2006-10-01
 sales | 6 | 5500 | 2007-01-02
(11 rows)

http://blog.sina.com.cn/s/blog_544a710b0101angq.html


mysql 仿真
SELECT bu,addr_isp,domain,s_total,rank FROM
(
    SELECT bu,addr_isp,domain,s_total,
    IF((@t_bu=tmp.bu && @t_addr_isp=tmp.addr_isp),@rank:=@rank + 1,@rank:=1) AS rank,
    @t_bu:=tmp.bu,@t_addr_isp:=tmp.addr_isp
     FROM (
        SELECT bu,addr_isp,domain,SUM(total) AS s_total FROM all_isp
        GROUP BY bu,addr_isp,domain ORDER BY bu ASC,addr_isp_20130619 ASC,s_total DESC
        ) tmp,
(SELECT @t_bu:=NULL,@t_addr_isp:=NULL,@rank:=0) a
) result HAVING rank<=5