Skip site navigation (1) Skip section navigation (2)

Re: [SQL] JOIN

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>, pgsql-novice(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] JOIN
Date: 2007-06-06 08:26:30
Message-ID: 1c23c8e70706060126y51f0edcel2be8fb3a3812c583@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
Oliveiros,

I think it's time to give some more details about my task. I will start with
the begining :)

I have a "log" table which stores the dates when users send messages
with a theme from their mobile phone. This table is named
sent_messages and looks like this:

         receiver   | theme   |             date
 ----------------------+------------+-------------------------------
 +40741775622 | CRISTI | 2007-06-04 07:44:45.406271+00
 +40741775622 | CRISTI | 2007-06-04 07:45:01.788533+00
 +40741775622 | CRISTI | 2007-06-04 07:45:03.764506+00
 +40741775621 | LIA    | 2007-06-04 07:45:26.309215+00
 +40741775621 | LIA    | 2007-06-04 07:45:28.314075+00
 +40741775622 | CRISTI | 2007-06-03 07:44:00+00
 +40741775622 | CRISTI | 2007-06-02 07:44:00+00
 +40741775622 | CRISTI | 2007-06-01 07:44:00+00
 +40741775622 | CRISTI | 2007-06-01 07:44:00+00
 +40741775622 | CRISTI | 2007-06-01 07:44:00+00
 +40741775622 | CRISTI | 2007-04-01 07:44:00+00
 +40741775622 | CRISTI | 2007-05-01 07:44:00+00
 +40741775621 | LIA    | 2007-06-03 07:44:00+00
 +40741775621 | LIA    | 2007-06-03 07:44:00+00
 +40741775621 | LIA    | 2007-06-02 07:44:00+00
 +40741775621 | LIA    | 2007-06-02 07:44:00+00
 +40741775621 | LIA    | 2007-06-01 07:44:00+00
 +40741775621 | LIA    | 2007-06-01 07:44:00+00
 +40741775621 | LIA    | 2007-06-01 07:44:00+00
 +40741775621 | LIA    | 2007-06-01 07:44:00+00

I have also a themes table:
 uid | theme
-----+--------
   6 | CRISTI
   7 | LIA

And the table named reminder_services it is used to filter
users by theme from sent_messages table. This table looks like this:

 uid | theme_uid | activity_mt_amount | activity_min_days |
activity_max_months
 -----+----------------+-----------------------------+---------------------------+----------------------------
    5 |              6 |                           3 |
             6 |                   1
    6 |              7 |                           4 |
    5 |                   1

The filtering should select users which sent at least activity_mt_amount
messages
with theme_uid  within activity_min_days consecutive days,
in the maximum activity_max_months months in the past.

Example:
The first row of the table reminder_services says that it should be selected
users which sent at least 3 messages with the theme_uid=6 (theme=CRISTI),
within 6 consecutive days, in the maximum 1 month in the past.


So, I created the following query:

      SELECT SUM(B.count),
                    A.theme,
                    A.receiver,
                    A.dates
         FROM ( SELECT COUNT(*),
                                    sent_messages.theme,
                                    receiver,
                                    date_trunc('day',sent_messages.date) AS
date,
                                   ARRAY(SELECT date::date + s.a FROM
generate_series(0,activity_min_days) AS s(a)) AS dates
                         FROM reminder_services, themes,sent_messages
                       WHERE themes.uid=reminder_services.theme_uid
                           AND sent_messages.theme=themes.theme
                           AND date_trunc('day',sent_messages.date) > (now()
- reminder_services.activity_max_months * INTERVAL' 1 month')
                      GROUP BY sent_messages.theme, receiver, date,
activity_min_days ) A
INNER JOIN ( SELECT COUNT(*),
                                   sent_messages.theme,
                                   receiver,
                                   date_trunc('day',sent_messages.date) AS
date,
                                   ARRAY(SELECT date::date + s.a FROM
generate_series(0,activity_min_days) AS s(a)) AS dates
                         FROM reminder_services, themes,sent_messages
                      WHERE themes.uid=reminder_services.theme_uid
                           AND sent_messages.theme=themes.theme
                           AND date_trunc('day',sent_messages.date) > (now()
- reminder_services.activity_max_months * INTERVAL' 1 month')
                      GROUP BY sent_messages.theme, receiver, date,
activity_min_days ) B
              ON A.theme=B.theme
            AND A.receiver=B.receiver
            AND B.date=ANY (A.dates)
        GROUP BY A.theme, A.receiver, A.dates;

The result of this query is:

 sum | theme  |   receiver         |
dates

-----+------------+----------------------+--------------------------------------------------------------------------------
     8 | CRISTI | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
     5 | CRISTI | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
     4 | CRISTI | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
     9 | CRISTI | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
   10 | LIA       | +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
     6 | LIA       | +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
     4 | LIA       | +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
     4 | LIA       | +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)

The red colored values 9 and 4 are not correct, should be 3 respectively 2.


Regards,
       Loredana

In response to

pgsql-novice by date

Next:From: John DeSoiDate: 2007-06-06 11:55:56
Subject: Re: Installation of postgresql database as root
Previous:From: Ali, LuqmanDate: 2007-06-06 04:20:26
Subject: Re: Installation of postgresql database as root

pgsql-sql by date

Next:From: Richard HuxtonDate: 2007-06-06 08:31:11
Subject: Re: How to find missing values across multiple OUTER JOINs
Previous:From: DrewDate: 2007-06-06 06:55:28
Subject: How to find missing values across multiple OUTER JOINs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group