Re: [SQL] JOIN

Поиск
Список
Период
Сортировка
От Loredana Curugiu
Тема Re: [SQL] JOIN
Дата
Msg-id 1c23c8e70706060126y51f0edcel2be8fb3a3812c583@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [SQL] JOIN  ("Oliveiros Cristina" <oliveiros.cristina@marktest.pt>)
Список pgsql-novice
Дерево обсуждения
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

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Ali, Luqman"
Дата:
Сообщение: Re: Installation of postgresql database as root
Следующее
От: John DeSoi
Дата:
Сообщение: Re: Installation of postgresql database as root