Обсуждение: [ADMIN] windown function count Unexpected results
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
On Thursday, May 4, 2017, winston cheung <winston_cheung@163.com> wrote:
, why the value of each line are different?
window w as (partition by department_id order by salary desc);
This isn't an admin question; general or novice would be a better mailing list.
The answer is you added an order by clause to your window definition. The docs explain why doing that results in each row of the partition only summing the rows before and including the current row.
David J.
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
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@gmail.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 | 430 | Sigal Tobias | 2800 | 430 | Guy Himuro | 2600 | 430 | Karen Colmenares | 2500 | 450 | Michael Rogers | 2900 | 2050 | Timothy Gates | 2900 | 2050 | Vance Jones | 2800 | 2050 | Mozhe Atkinson | 2800 | 2050 | Girard Geoni | 2800 | 2050 | John Seo | 2700 | 2050 | Irene Mikkilineni | 2700 | 2050 | Douglas Grant | 2600 | 2050 | Randall Matos | 2600 | 2050 | Donald OConnell | 2600 | 2050 | Joshua Patel | 2500 | 2050 | Peter Vargas | 2500 | 2050 | Martha Sullivan | 2500 | 2050 | James Marlow | 2500 | 2050 | Randall Perkins | 2500 | 2050 | James Landry | 2400 | 2050 | Ki Gee | 2400 | 2050 | Hazel Philtanker | 2200 | 2050 | Steven Markle | 2200 | 2050 | TJ Olson | 2100 | 202017-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
thanks a lot,steven,david :-)
在 5/5/17 13:14, Steven Chang 写道:
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
winston , regards