[ADMIN] windown function count Unexpected results

Поиск
Список
Период
Сортировка
Искать

[ADMIN] windown function count Unexpected results

От:
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



Re: [ADMIN] windown function count Unexpected results

От:
"David G. Johnston" <david.g.johnston@gmail.com>
Дата:
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. 

Re: [ADMIN] windown function count Unexpected results

От:
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

Re: [ADMIN] windown function count Unexpected results

От:
Steven Chang <stevenchang1213@gmail.com>
Дата:
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


Re: [ADMIN] windown function count Unexpected results

От:
winston cheung <winston_cheung@163.com>
Дата:

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
FAQ