Thread: How do write a query...

How do write a query...

From
Alain Reymond
Date:
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




Re: How do write a query...

From
KÖPFERL Robert
Date:
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)
|


Re: How do write a query...

From
Bruno Wolff III
Date:
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.



Re: How do write a query...

From
Bruno Wolff III
Date:
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)


Re: How do write a query...

From
Mischa Sandberg
Date:
> |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