pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwindown function count Unexpected results
5+ messages / 3 participants
[nested] [flat]
* windown function count Unexpected results
@ 2017-05-05 03:31 winston cheung <winston_cheung@163.com>
0 siblings, 2 replies; 5+ messages in thread
From: winston cheung @ 2017-05-05 03:31 UTC (permalink / raw)
To: 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@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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: windown function count Unexpected results
@ 2017-05-05 04:15 David G. Johnston <david.g.johnston@gmail.com>
parent: winston cheung <winston_cheung@163.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2017-05-05 04:15 UTC (permalink / raw)
To: winston cheung <winston_cheung@163.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: windown function count Unexpected results
@ 2017-05-05 04:42 Steven Chang <stevenchang1213@gmail.com>
parent: winston cheung <winston_cheung@163.com>
1 sibling, 1 reply; 5+ messages in thread
From: Steven Chang @ 2017-05-05 04:42 UTC (permalink / raw)
To: winston cheung <winston_cheung@163.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: windown function count Unexpected results
@ 2017-05-05 05:14 Steven Chang <stevenchang1213@gmail.com>
parent: Steven Chang <stevenchang1213@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Steven Chang @ 2017-05-05 05:14 UTC (permalink / raw)
To: winston cheung <winston_cheung@163.com>; +Cc: 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 | 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
>>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: windown function count Unexpected results
@ 2017-05-05 11:10 winston cheung <winston_cheung@163.com>
parent: Steven Chang <stevenchang1213@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: winston cheung @ 2017-05-05 11:10 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-05-05 11:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-05 03:31 windown function count Unexpected results winston cheung <winston_cheung@163.com>
2017-05-05 04:15 ` David G. Johnston <david.g.johnston@gmail.com>
2017-05-05 04:42 ` Steven Chang <stevenchang1213@gmail.com>
2017-05-05 05:14 ` Steven Chang <stevenchang1213@gmail.com>
2017-05-05 11:10 ` winston cheung <winston_cheung@163.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox