windown function count Unexpected results

From: winston cheung <winston_cheung(at)163(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: windown function count Unexpected results
Date: 2017-05-05 03:31:21
Message-ID: eaec97a5-9412-7edc-15ff-c5c193649c75@163.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)160(dot)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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2017-05-05 04:15:16 Re: windown function count Unexpected results
Previous Message Stéphane KANSCHINE 2017-05-05 01:57:42 Re: Setting wild card IP's on postgres