Thread: How do write a query...
Hello, I have the following problem : I have a table like Id Num Date AValue 1 10 01/01/2005 50 2 10 31/05/2005 60 3 25 02/02/2005 55 4 25 15/03/2005 43 5 25 28/05/2005 62 etc.. Id is unique, Num is an identification number with duplicates possible, date is a ... date and Avalue... a value! If we have Id Num Date AValue Id1 Num1 Date1 AValue1 Id2 Num1 Date2 AValue2 The table is ordered on Num+Date. What I would like to calculate is (AValue2-AValue1) for a given Num (here num1). In this case, I would have to calculate 60-50 for Num 10 and 43-55, 62-43 for Num 25. Do you have any idea if it can be done simply with a request... I thank you Regards. Alain Reymond
You could have a look at the OFFSET and LIMIT modifiers as for untested example select ((select max( "AValue") from table group by "Num") - "AValue") as difference from table order by "AValue" desc offset 1 this says: give me a inversed ordered AValue-list but ommitting the first (biggest) and subtract each from the biggest value of each group |-----Original Message----- |From: Alain Reymond [mailto:arwebmail@skynet.be] |Sent: Mittwoch, 01. Juni 2005 18:01 |To: pgsql-sql@postgresql.org |Subject: [SQL] How do write a query... | | |Hello, | |I have the following problem : | |I have a table like |Id Num Date AValue |1 10 01/01/2005 50 |2 10 31/05/2005 60 |3 25 02/02/2005 55 |4 25 15/03/2005 43 |5 25 28/05/2005 62 |etc.. | |Id is unique, Num is an identification number with duplicates possible, |date is a ... date and Avalue... a value! | |If we have |Id Num Date AValue |Id1 Num1 Date1 AValue1 |Id2 Num1 Date2 AValue2 | |The table is ordered on Num+Date. |What I would like to calculate is (AValue2-AValue1) for a given Num |(here num1). | |In this case, I would have to calculate |60-50 for Num 10 |and |43-55, 62-43 for Num 25. | |Do you have any idea if it can be done simply with a request... | |I thank you | |Regards. | |Alain Reymond | | | |---------------------------(end of |broadcast)--------------------------- |TIP 2: you can get off all lists at once with the unregister command | (send "unregister YourEmailAddressHere" to |majordomo@postgresql.org) |
On Wed, Jun 01, 2005 at 18:00:49 +0200, Alain Reymond <arwebmail@skynet.be> wrote: > Hello, > > I have the following problem : > > I have a table like > Id Num Date AValue > 1 10 01/01/2005 50 > 2 10 31/05/2005 60 > 3 25 02/02/2005 55 > 4 25 15/03/2005 43 > 5 25 28/05/2005 62 > etc.. > > Id is unique, Num is an identification number with duplicates possible, > date is a ... date and Avalue... a value! > > If we have > Id Num Date AValue > Id1 Num1 Date1 AValue1 > Id2 Num1 Date2 AValue2 > > The table is ordered on Num+Date. > What I would like to calculate is (AValue2-AValue1) for a given Num > (here num1). > > In this case, I would have to calculate > 60-50 for Num 10 > and > 43-55, 62-43 for Num 25. > > Do you have any idea if it can be done simply with a request... I would suggest using a different design. Probably the easiest is to put the start and end dates and values in one record. Otherwise you could write a function or have your application read entries for a particular Num value ordered by date and treat them as pairs. If any intervals overlap then there really isn't a fix.
Please keep replies copied to the list unless you have a specific reason not
to. This will help you get help and will help other people learn from
the discussion.
On Wed, Jun 08, 2005 at 13:56:36 +0200, Alain <arwebmail@skynet.be> wrote:
>
> In fact, these are results of analyses. For one patient and one type of
> meseare, I can have results at different dates and I need to follow the
> differences between dates. So, if I have 5 records for one person, I'll
> have 4 values of differences (record2-rec1, rec3-rec2, rec4-rec3,
> rec5-rec4). Problem is to create the pairs from the records I have. I
> can do it with an algorithm. But a query - if possible - returning the
> number of days and the differences between the values would be nicer.
This is actually easier. You can write a subquery that gets the row that
has the greatest date less than the current date. With an index on Num
and the date column it shouldn't be too slow.
Below is an example test script and the output. This relies on Postgres
supprting ORDER BY in subselects which isn't standard SQL. You can do this
with standard SQL but that will require getting the max value of day less
than the day in the current record with a matching num and then joining
that result back to test to get the corresponding value.
DROP TABLE test;
CREATE TABLE test ( id SERIAL PRIMARY KEY, num INT NOT NULL, day DATE NOT NULL, value INT NOT NULL, UNIQUE (num, day)
);
INSERT INTO test (num, day, value) VALUES (10, '2005-01-01', 50);
INSERT INTO test (num, day, value) VALUES (10, '2005-05-31', 60);
INSERT INTO test (num, day, value) VALUES (25, '2005-02-02', 55);
INSERT INTO test (num, day, value) VALUES (25, '2005-03-15', 43);
INSERT INTO test (num, day, value) VALUES (25, '2005-05-28', 62);
SELECT num, day, value, value - (SELECT value FROM test WHERE num = a.num AND
day < a.day ORDER BY num DESC, day DESC LIMIT 1) FROM test a WHERE (SELECT value FROM
test WHERE num = a.num AND day < a.day ORDER BY num DESC, day DESC LIMIT 1) IS NOT NULL ORDER
BYnum, day
;
bruno=> \i test.sql
DROP TABLE
psql:test.sql:8: NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
psql:test.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
psql:test.sql:8: NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_num_key" for table "test"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1num | day | value | ?column?
-----+------------+-------+---------- 10 | 2005-05-31 | 60 | 10 25 | 2005-03-15 | 43 | -12 25 |
2005-05-28| 62 | 19
(3 rows)
> |From: Alain Reymond [mailto:arwebmail@skynet.be] > |Sent: Mittwoch, 01. Juni 2005 18:01 > |Subject: [SQL] How do write a query... > | > |I have a table like > |Id Num Date AValue > |1 10 01/01/2005 50 > |2 10 31/05/2005 60 > |3 25 02/02/2005 55 > |4 25 15/03/2005 43 > |5 25 28/05/2005 62 > |etc.. > | > |Id is unique, Num is an identification number with duplicates > possible, > |date is a ... date and Avalue... a value! > | > |If we have > |Id Num Date AValue > |Id1 Num1 Date1 AValue1 > |Id2 Num1 Date2 AValue2 > | > |The table is ordered on Num+Date. > |What I would like to calculate is (AValue2-AValue1) for a given Num > |(here num1). > | > |In this case, I would have to calculate > |60-50 for Num 10 > |and > |43-55, 62-43 for Num 25. Not sure if this covers all that you want, given your examples (what if there's only one row for a given Num value?), but ... select Num, AValue-AValue1 from Tafel join (select Num, min(AValue) as AValue1 from Tafel group by Num) as T using(Num) where AValue > AValue1