Re: How do write a query... - Mailing list pgsql-sql

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)