On Jan 31, 2008 8:49 AM, Enrico Sirola [EMAIL PROTECTED] wrote:
I'd create a previousTime column and manage it using a trigger.
Anyway, it depends on the time-dependancy of the table
Then you can perform temporal in a much easier way.
You could be interested in taking a look at the following
Hi Willem,
Il giorno 30/gen/08, alle ore 22:15, Willem Buitendyk ha scritto:
I'm trying to replicate the use of Oracle's 'lag' and 'over
partition by' analytical functions in my query. I have a table
(all_client_times) such as:
client_id, datetime
122, 2007-05-01 12:00:00
122, 2007-05-01
I'm trying to replicate the use of Oracle's 'lag' and 'over
partition by' analytical functions in my query. I have a table
(all_client_times) such as:
and I would like to create a new view that takes the first table and
calculates the time difference in minutes between each row so that
Hi Adam,
Il giorno 31/gen/08, alle ore 16:13, Adam Rich ha scritto:
I'm trying to replicate the use of Oracle's 'lag' and 'over
partition by' analytical functions in my query. I have a table
(all_client_times) such as:
and I would like to create a new view that takes the first table and
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
Hey Adam,
I tried your sequence method this morning on an unsorted table and for
some reason the order by's aren't working. If I create a sorted view
(client_id, datetime) on the 'all_client_times' table and then use that
view with your sequence method all works fine. The strange thing is
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
@postgresql.org
Subject: Re: [GENERAL] Oracle Analytical Functions
Here is a little test example. It seems that the second order by
condition is not working - in this case datetime.
create table arb_test (
client_id integer,
arbnum integer);
insert into arb_test values (2,1);
insert
Here is a little test example. It seems that the second order by
condition is not working - in this case datetime.
create table arb_test (
client_id integer,
arbnum integer);
insert into arb_test values (2,1);
insert into arb_test values (2,33);
insert into arb_test values (2,6);
insert into
I'm trying to replicate the use of Oracle's 'lag' and 'over partition
by' analytical functions in my query. I have a table (all_client_times)
such as:
client_id, datetime
122, 2007-05-01 12:00:00
122, 2007-05-01 12:01:00
455, 2007-05-01 12:02:00
455, 2007-05-01 12:03:00
455, 2007-05-01
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Willem Buitendyk
Sent: Wednesday, January 30, 2008 1:15 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Oracle Analytical Functions
I'm trying to replicate the use of Oracle's
and I would like to create a new view that takes the first table and
calculates the time difference in minutes between each row so that the
result is something like:
client_id,datetime, previousTime, difftime
122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1
455,2007-05-01 12:03:00,
How about something like this:
SELECT
client_id
, datetime
, lagged as previoustime
, datetime - lagged difftime
FROM (
SELECT
client_id
,datetime
,(SELECT MAX(datetime)
FROM all_client_times def
WHERE def.client_id = abc.client_id
AND
create table data (
client_id integer,
datetime timestamp not null
);
create index data_client_id on data(client_id);
copy data from STDIN DELIMITER ',';
122,2007-05-01 12:00:00
122,2007-05-01 12:01:00
455,2007-05-01 12:02:00
455,2007-05-01 12:03:00
455,2007-05-01 12:08:00
I tried this function but it keeps returning an error such as:
ERROR: invalid input syntax for integer: 2007-05-05 00:34:08
SQL state: 22P02
Context: PL/pgSQL function lagfunc line 10 at assignment
I checked and there are no datetime values in the client_id field
anywhere in my table
Thanks Reece,
I got this to work for me. The only problem was with the ORDER BY
clause which did not seem to work properly. I took it out and instead
used a sorted view for the data table.
Cheers,
Willem
Reece Hart wrote:
create table data (
client_id integer,
datetime timestamp
Found the error:
client_id := thisrow.datetime;
should be
client_id := thisrow.client_id;
All works well now,
Thanks very much,
Willem
Willem Buitendyk wrote:
I tried this function but it keeps returning an error such as:
ERROR: invalid input syntax for integer: 2007-05-05 00:34:08
SQL
I tried this function but it keeps returning an error such as:
ERROR: invalid input syntax for integer: 2007-05-05 00:34:08
SQL state: 22P02
Context: PL/pgSQL function lagfunc line 10 at assignment
Whoops, this line:
client_id := thisrow.datetime;
Should be:
client_id :=
18 matches
Mail list logo
