Re: [ADMIN] windown function count Unexpected results

hello winston,

  by your data,

 select dep_id, ename, salary, count(*) over (partition by dep_id) from employee where salary<3000 order by dep_id ;

 dep_id |       ename       | salary | count

--------+-------------------+--------+-------

     30 | Shelli Baida      |   2900 |     4

     30 | Sigal Tobias      |   2800 |     4

     30 | Guy Himuro        |   2600 |     4

     30 | Karen Colmenares  |   2500 |     4

     50 | Michael Rogers    |   2900 |    20

     50 | Timothy Gates     |   2900 |    20

     50 | Vance Jones       |   2800 |    20

     50 | Mozhe Atkinson    |   2800 |    20

     50 | Girard Geoni      |   2800 |    20

     50 | John Seo          |   2700 |    20

     50 | Irene Mikkilineni |   2700 |    20

     50 | Douglas Grant     |   2600 |    20

     50 | Randall Matos     |   2600 |    20

     50 | Donald OConnell   |   2600 |    20

     50 | Joshua Patel      |   2500 |    20

     50 | Peter Vargas      |   2500 |    20

     50 | Martha Sullivan   |   2500 |    20

     50 | James Marlow      |   2500 |    20

     50 | Randall Perkins   |   2500 |    20

     50 | James Landry      |   2400 |    20

     50 | Ki Gee            |   2400 |    20

     50 | Hazel Philtanker  |   2200 |    20

     50 | Steven Markle     |   2200 |    20

     50 | TJ Olson          |   2100 |    20

2017-05-05 11:31 GMT+08:00 winston cheung <winston_cheung@163.com>:

hello

When I used the count in the window function, I found that the results were not true. In the table I use department_id as a partition condition, I think when department_id = 30, the calculation of salary <3000 should be 4, when department_id = 50,the calculation of salary < 3000 should be 20, why the value of each line are different?

Thanks a lot!

postgres@160.40:5410/testdb=# select department_id as dep_id, concat(first_name, ' ', last_name) as employee_name, salary, count(*) over w from employees where salary<3000 window w as (partition by department_id order by salary desc);
 dep_id |   employee_name   | salary  | count
--------+-------------------+---------+-------
     30 | Shelli Baida      | 2900.00 |     1
     30 | Sigal Tobias      | 2800.00 |     2
     30 | Guy Himuro        | 2600.00 |     3
     30 | Karen Colmenares  | 2500.00 |     4
     50 | Michael Rogers    | 2900.00 |     2
     50 | Timothy Gates     | 2900.00 |     2
     50 | Vance Jones       | 2800.00 |     5
     50 | Mozhe Atkinson    | 2800.00 |     5
     50 | Girard Geoni      | 2800.00 |     5
     50 | John Seo          | 2700.00 |     7
     50 | Irene Mikkilineni | 2700.00 |     7
     50 | Douglas Grant     | 2600.00 |    10
     50 | Randall Matos     | 2600.00 |    10
     50 | Donald OConnell   | 2600.00 |    10
     50 | Joshua Patel      | 2500.00 |    15
     50 | Peter Vargas      | 2500.00 |    15
     50 | Martha Sullivan   | 2500.00 |    15
     50 | James Marlow      | 2500.00 |    15
     50 | Randall Perkins   | 2500.00 |    15
     50 | James Landry      | 2400.00 |    17
     50 | Ki Gee            | 2400.00 |    17
     50 | Hazel Philtanker  | 2200.00 |    19
     50 | Steven Markle     | 2200.00 |    19
     50 | TJ Olson          | 2100.00 |    20
(24 rows)


winston , regards




--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin