Re: Join query help - Mailing list pgsql-sql

On Aug 18, 2007, at 0:35 , novice wrote:

> What query do I write to generate the following?
>
> week_no | count(record_id | count(observation_id) | sum(score_id)  
> where = '1'
> 2007, 30  |   2   |   8   |   6
> 2007, 29  |   1   |   2   |   1

Okay: let's take a look at what you're trying to get:

first column is year and week of year (which might be better labeled  
"week" rather than "week_no")
Checking the available date/time functions, I see extract can get at  
both of these attributes of a date value. That could be helpful.  
However, this splits the week and year into two separate values,  
which are two things to keep track of. As well, those values are now  
integers, which have little to do with dates. date_trunc allows you  
to truncate timestamps to the week, returning a timestamp value. This  
mean's we can look at timestamps with week-precision: and they're  
still timestamps, which means we can rely on them to act as dates in  
terms of ordering (Some would go ahead and format the year, week  
column as text right a way using TO_CHAR and rely on string  
comparisons for grouping and ordering, but there's no reason to do  
this, and you can no longer handle the value easily as a datetime  
value. If you need a specific output format, do it at the end or in  
your middleware.)

If you're going to group records by week (rather than timestamp)  
often, I'd go ahead and create a VIEW:

CREATE VIEW record_with_week AS
SELECT record_id       , date_trunc('week', record_date) AS record_week
FROM record;
SELECT *, extract(week from record_week) as week_number
test-# FROM record_with_week;
record_id |      record_week       | week_number
-----------+------------------------+-------------         1 | 2007-07-16 00:00:00-05 |          29         2 |
2007-07-2300:00:00-05 |          30         3 | 2007-07-16 00:00:00-05 |          29
 

You're also looking for the count of distinct records and  
observations per week, so let's do that:

SELECT record_week    , count(DISTINCT record_id) AS record_count    , count(DISTINCT observation_id) AS
observation_count
FROM record_with_week
NATURAL JOIN observation
GROUP BY record_week;      record_week       | record_count | observation_count
------------------------+--------------+-------------------
2007-07-16 00:00:00-05 |            2 |                 6
2007-07-23 00:00:00-05 |            1 |                 4
(2 rows)

Now here's where I started having trouble. I can't figure out how to  
get 2 observations for week 29 (record_id 1 & 3) and 8 for week 30  
(record_id 2). Assuming the data is wrong (which is admittedly a poor  
assumption), I moved ahead.

I'm interpreting "sum(score_id) where = '1'" as the sum of scores for  
observations of that week where score_description is 'SAFE' (note  
that '1' is text, and it appears that your score_id column is an  
integer). So, let's get the observations that were safe:

SELECT record_week, sum(score_id) as safe_score_sum
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN score
WHERE score_description = 'SAFE'
GROUP BY record_week;      record_week       | safe_score_sum
------------------------+----------------
2007-07-23 00:00:00-05 |              3
2007-07-16 00:00:00-05 |              4
(2 rows)

Note I'm using score_description = 'SAFE' rather than score_id = 1,  
as this is much more descriptive of what you're actually doing. It  
makes the query easier to read as well.

Again, I can't get my numbers to agree with yours, so I'm probably  
misinterpreting something, but I can't figure out another way to  
interpret what you've described.

So, let's join this to the query:

SELECT record_week    , count(DISTINCT record_id) AS record_count    , count(DISTINCT observation_id) AS
observation_count   , safe_score_sum
 
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN (    SELECT record_week, sum(score_id) as safe_score_sum    FROM record_with_week    NATURAL JOIN
observation   NATURAL JOIN score    WHERE score_description = 'SAFE'    GROUP BY record_week    ) safe_observation
 
GROUP BY record_week, safe_score_sum;      record_week       | record_count | observation_count |  
safe_score_sum
------------------------+--------------+------------------- 
+----------------
2007-07-16 00:00:00-05 |            2 |                 6  
|              4
2007-07-23 00:00:00-05 |            1 |                 4  
|              3
(2 rows)

One thing that struck me as odd is that you're summing an ID column.  
I'm guessing you're doing this as an attempt to count the total safe  
observations, taking advantage of the coincidence that the score_id  
is 1, so sum of observations with score_id = 1 is the same as the  
count of observations where score_id is 1. If this is indeed what's  
happening, I think I've got a better way to do it:

SELECT record_week    , count(DISTINCT record_id) AS record_count    , count(DISTINCT observation_id) AS
observation_count   , count(DISTINCT safe_observation_id) as safe_observation_count
 
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN (    SELECT record_week        , observation_id as safe_observation_id    FROM record_with_week    NATURAL
JOINobservation    NATURAL JOIN score    WHERE score_description = 'SAFE'    ) safe_observation
 
GROUP BY record_week;      record_week       | record_count | observation_count |  
safe_observation_count
------------------------+--------------+------------------- 
+------------------------
2007-07-16 00:00:00-05 |            2 |                 6  
|                      4
2007-07-23 00:00:00-05 |            1 |                 4  
|                      3
(2 rows)

And finally, formatting for output:

SELECT TO_CHAR(record_week, 'YYYY, IW') AS formatted_record_week    , count(DISTINCT record_id) AS record_count    ,
count(DISTINCTobservation_id) AS observation_count    , count(DISTINCT safe_observation_id) as safe_observation_count
 
FROM record_with_week
NATURAL JOIN observation
NATURAL JOIN (    SELECT record_week        , observation_id as safe_observation_id    FROM record_with_week    NATURAL
JOINobservation    NATURAL JOIN score    WHERE score_description = 'SAFE'    ) safe_observation
 
GROUP BY record_week;
formatted_record_week | record_count | observation_count |  
safe_observation_count
-----------------------+--------------+------------------- 
+------------------------
2007, 29              |            2 |                 6  
|                      4
2007, 30              |            1 |                 4  
|                      3
(2 rows)

I've got data below so others may play along at home.

Does this help?

Michael Glaesemann
grzm seespotcode net

CREATE TABLE record
(    record_id INTEGER PRIMARY KEY    , record_date TIMESTAMP(0) WITH TIME ZONE
);

INSERT INTO record (record_id, record_date) VALUES
(1, '2007-07-23 11:30:37+10')
, (2, '2007-07-27 11:30:14+10')
, (3, '2007-07-17 13:15:03+10');

CREATE TABLE score
(    score_id INTEGER PRIMARY KEY    , score_description TEXT NOT NULL UNIQUE
);
INSERT INTO score (score_id, score_description) VALUES
(0, 'NA')
, (1, 'SAFE')
, (2, 'AT RISK');

CREATE TABLE observation
(    observation_id INTEGER PRIMARY KEY    , record_id INTEGER NOT NULL    , score_id INTEGER NOT NULL
REFERENCESscore
 
);
INSERT INTO observation (observation_id, record_id, score_id) VALUES
(3240,1,1)
, (3239,1,1)
, (3238,1,2)
, (3237,1,1)
, (2872,2,1)
, (2869,2,2)
, (2870,2,1)
, (2871,2,1)
, (3218,3,2)
, (3217,3,1);