Postgres Query for Beginners

Question

Ok, I deleted previous post and will try this again. I am sure I don't know the topic and I'm not sure if this is a loop or if I should use a stored function or how to get what I'm looking for. Here's sample data and expected output;

I have a single table A. Table has following fields; date created, unique person key, type, location.

I need a Postgres query that says for any given month(parameter, based on date created) and given a location(parameter based on location field), provide me fieds below where unique person key may be duplicated + or – 30 days from the date created within the month given for same type but all locations.

Example Data

    Date Created | Unique Person  |  Type   |  Location
    ---------------------------------------------------
     2/5/2017    |   1            |  Admit  |  Hospital1
     2/6/2017    |   2            |  Admit  |  Hospital2
     2/15/2017   |   1            |  Admit  |  Hospital2
     2/28/2017   |   3            |  Admit  |  Hospital2
     3/3/2017    |   2            |  Admit  |  Hospital1
     3/15/2017   |   3            |  Admit  |  Hospital3
     3/20/2017   |   4            |  Admit  |  Hospital1
     4/1/2017    |   1            |  Admit  |  Hospital2

Output for the month of March for Hospital1:

    DateCreated| UniquePerson | Type | Location | +-30days | OtherLoc.
    ------------------------------------------------------------------------
     3/3/2017  |    2         | Admit| Hospital1 | 2/6/2017   | Hospital2

Output for the month of March for Hospital2:

    None, because no one was seen at Hospital2 in March

Output for the month of March for Hospital3:

    DateCreated| UniquePerson | Type | Location | +-30days | otherLoc.
    ------------------------------------------------------------------------
     3/15/2017 |   3          | Admit| Hospital3 | 2/28/2017  | Hospital2

Community · Accepted Answer · 2017-05-23 12:17:36Z

2

Version 1

I would use a WITH clause. Please, notice that I've added a column id that is a primary key to simplify the query. It's just to prevent the rows to be matched with themselves.

WITH x AS (
    SELECT
        id,
        date_created,
        unique_person_id,
        type,
        location
    FROM
        a
    WHERE
        location = 'Hospital1' AND
        date_trunc('month', date_created) = date_trunc('month', '2017-03-01'::date)
)
SELECT
    x.date_created,
    x.unique_person_id,
    x.type,
    x.location,
    a.date_created AS "+-30days",
    a.location AS other_location
FROM
    x
    JOIN a
    USING (unique_person_id, type)
WHERE
    x.id != a.id AND
    abs(x.date_created - a.date_created) <= 30;

Now a little bit of explanations:

First we select, let's say a reference data with a WITH clause. Think of it as a temporary table that we can reference in the main query. In given example it could be a "main visit" in given hospital.
Then we join "main visits" with other visits of the same person and type (JOIN condition) that happen in date difference of 30 days (WHERE condition).

Notice that the WITH query has the limits you want to check (location and date). I use date_trunc function that truncates the date to specified precision (a month in this case).

Version 2

As @Laurenz Albe suggested, there is no special need to use a WITH clause. Right, so here is a second version.

SELECT
    x.date_created,
    x.unique_person_id,
    x.type,
    x.location,
    a.date_created AS "+-30days",
    a.location AS other_location
FROM
    a AS x
    JOIN a
    USING (unique_person_id, type)
WHERE
    x.location = 'Hospital1' AND
    date_trunc('month', x.date_created) = date_trunc('month', '2017-03-01'::date) AND
    x.id != a.id AND
    abs(x.date_created - a.date_created) <= 30;

This version is shorter than the first one but, in my opinion, the first is easier to understand. I don't have big enough set of data to test and I wonder which one runs faster (the query planner shows similar values for both).

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Apr 4, 2017 at 22:24

Adam

5,6296 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

I don't see a need for a WITH clause, why don't you just join a with itself and apply the contitions in the WHERE clause?

Adam Over a year ago

@LaurenzAlbe - you're right. I've added second query without WITH clause. I find my first version easier to understand, but maybe WITH is not for a beginner (as OP stated in the title). Thanks for the comment.

Collectives™ on Stack Overflow

Postgres Query for Beginners

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related