Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 82C769FBA22 for ; Wed, 6 Jun 2007 05:26:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 25014-04 for ; Wed, 6 Jun 2007 05:26:31 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.249]) by postgresql.org (Postfix) with ESMTP id 87B9B9FB9A4 for ; Wed, 6 Jun 2007 05:26:31 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so24637ana for ; Wed, 06 Jun 2007 01:26:30 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=r/rYrVPgsvipVm4o2+WTBV+/psuZr9cZvCeDILHD/8az/VeJyMg2Gd5jHI0Uv7Yd7/gBonR7CQCWOlTUC/VypzisluFTVLnf9tyS9ugW6pHtNVDCqvROKZ4not7E9Ggmpr7SaciH9VFTg060adBbjmnITwTooVmObLqiAcfFMs0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=HEuMn+JNN/tT7pTepi0wK+pWTdVIHEbv26IdK7NojTjZC3Dq7LxlLdPk99mC45lfy7tPEWpe6zifssrVkNE+hktaSASuOVkmwOhWqSa3hfpodvv4zxF40AQLaRAU2FmeyngOBEyfZtEbQ2lGCCmpT2ZSCqmoC8yqIDe6lDThURA= Received: by 10.100.226.15 with SMTP id y15mr123209ang.1181118390470; Wed, 06 Jun 2007 01:26:30 -0700 (PDT) Received: by 10.100.42.1 with HTTP; Wed, 6 Jun 2007 01:26:30 -0700 (PDT) Message-ID: <1c23c8e70706060126y51f0edcel2be8fb3a3812c583@mail.gmail.com> Date: Wed, 6 Jun 2007 11:26:30 +0300 From: "Loredana Curugiu" To: "Oliveiros Cristina" , pgsql-novice@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] JOIN In-Reply-To: <012601c7a782$40dd4ae0$ec5a3d0a@marktestcr.marktest.pt> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29429_7906300.1181118390303" References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com> <1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com> <1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com> <1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com> <1c23c8e70706050746v10dfaa6bn88f2f4ef8cd4b057@mail.gmail.com> <012601c7a782$40dd4ae0$ec5a3d0a@marktestcr.marktest.pt> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/46 X-Sequence-Number: 18944 ------=_Part_29429_7906300.1181118390303 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_29429_7906300.1181118390303 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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











 ------=_Part_29429_7906300.1181118390303--