Re: windown function count Unexpected results

Hello,

Official document in
https://www.postgresql.org/docs/9.3/static/functions-window.html says so :
An aggregate used with ORDER BY and the default window frame definition
produces a "running sum" type of behavior, which may or may not be what's
wanted. To obtain aggregation over the whole partition, omit ORDER BY or
use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Steven

2017-05-05 12:42 GMT+08:00 Steven Chang <stevenchang1213(at)gmail(dot)com>:

> 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(at)163(dot)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(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
>>
>>
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
>