To: pgsql-admin@postgresql.org
From: winston cheung <winston_cheung@163.com>
Subject: windown function count Unexpected results
Message-ID: <eaec97a5-9412-7edc-15ff-c5c193649c75@163.com>
Date: Fri, 5 May 2017 11:31:21 +0800
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.8.0
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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