From: Loredana Curugiu <loredana.curugiu@gmail.com>
To: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
To: pgsql-novice@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] JOIN
Date: Wed, 6 Jun 2007 11:26:30 +0300
Message-ID: <1c23c8e70706060126y51f0edcel2be8fb3a3812c583@mail.gmail.com> (raw)
In-Reply-To: <012601c7a782$40dd4ae0$ec5a3d0a@marktestcr.marktest.pt>
References: <1c23c8e70706050111i5ba672d6j78bd9afa4b786616@mail.gmail.com>
<1c23c8e70706050426y12d567ban70ffdafb75808e0e@mail.gmail.com>
<f54607780706050619u2e2c12dpcc3a4414370e0636@mail.gmail.com>
<1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com>
<1c23c8e70706050715y45688e12n90fcf8cecd84b251@mail.gmail.com>
<f54607780706050730i23979220t97bdef2e199c09d6@mail.gmail.com>
<1c23c8e70706050746v10dfaa6bn88f2f4ef8cd4b057@mail.gmail.com>
<012601c7a782$40dd4ae0$ec5a3d0a@marktestcr.marktest.pt>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: loredana.curugiu@gmail.com, oliveiros.cristina@marktest.pt, pgsql-novice@postgresql.org
Subject: Re: [SQL] JOIN
In-Reply-To: <1c23c8e70706060126y51f0edcel2be8fb3a3812c583@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox