Skip site navigation (1) Skip section navigation (2)

Re: Oracle Analytical Functions

From: Willem Buitendyk <willem(at)pcfish(dot)ca>
To: Adam Rich <adam(dot)r(at)sbcglobal(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Oracle Analytical Functions
Date: 2008-01-31 19:31:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
The 'all_client_times' table has 753698 rows.  The lagfunc() on the 
sorted view returns 753576 rows and appears to work exactly as needed.  
Using the function on an unsorted table returns only 686 rows and is 
missing a whole lot of data.  Running the count query returns 122 - 
which is correct as the amount of clients that I have.  Each client has 
between 5 - 7K  records each.

The way I see it is for each client there will be one row, namely, the 
first in the series, that will not be included in the final results as 
it would not have a previous time.  With that in mind, if I take my 
table row count as 753698 and minus the amount of clients I have, 122,  
then I should get the number of results as 753576 which is correct when 
I use your methods on a sorted table but which is not correct when I  
use your methods on an unsorted table.


Adam Rich wrote:
> Hi Willem,
>> for some reason the order by's aren't working.
> Could you provide more details?  Do you get a specific error message?
>> only returning 658 rows instead of the 750K.
> You should not expect the same row count in both source table and
> result set.  Even in your example -- you provided 8 source rows, and
> 4 result rows.  You can determine the correct number of results via
> "the number of records, related to client_ids having two or more records 
> in all_client_times, minus one".  It may be true that you have 750k
> records but only 658 rows that satisfy this requirement.
> What do you get for this query?
> select count(*) from ( select client_id, count(*) as rows 
> from all_client_times group by client_id having count(*) > 1 ) as x
> Adam

In response to


pgsql-general by date

Next:From: John SmithDate: 2008-01-31 19:38:49
Subject: [UMN_MAPSERVER-USERS] query postgres AND oracle
Previous:From: Erik JonesDate: 2008-01-31 19:26:24
Subject: Re: Recover by Log

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group