pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Loredana Curugiu <loredana.curugiu@gmail.com>
To: pgsql-novice@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Re: JOIN
Date: Wed, 6 Jun 2007 15:47:02 +0300
Message-ID: <1c23c8e70706060547l692c0e0cn879daaf6f3a1b0d5@mail.gmail.com> (raw)
In-Reply-To: <1c23c8e70706060546r54a6cdbfr967d2f684bddf9c9@mail.gmail.com>
References: <1c23c8e70706050708h6c5b708dpc2f53ff76a73740c@mail.gmail.com>
	<19770846.16757231181128801282.chat@gmail.com>
	<1c23c8e70706060447ib2925fftc5d34128e123e690@mail.gmail.com>
	<1c23c8e70706060542pfbec88crfca188149d2b3abd@mail.gmail.com>
	<1c23c8e70706060546r54a6cdbfr967d2f684bddf9c9@mail.gmail.com>

I forgot the attachement :)

On 6/6/07, Loredana Curugiu <loredana.curugiu@gmail.com> wrote:
>
> I found my problem. I attaches an .sql file with my tables, data
> and my new query. If you are interested you can take a look.
>
> Thanks to all.
>
> Regards,
>      Loredana
>

Attachments:


file.sql (text/x-sql, 3.4K) download
CREATE TABLE reminder_services (
       uid SERIAL,
       theme_uid INT NOT NULL,
       activity_MT_amount INT NOT NULL,
       activity_min_days INT NOT NULL,
       activity_max_months INT NOT NULL,
       CONSTRAINT pk_reminder_services PRIMARY KEY(uid),
       CONSTRAINT un_reminder_services_theme UNIQUE(theme_uid) );

CREATE TABLE themes (
       uid           SERIAL PRIMARY KEY,
       theme         VARCHAR(25) UNIQUE NOT NULL );

CREATE TABLE sent_messages (
       uid      SERIAL PRIMARY KEY,
       receiver VARCHAR(25),
       theme    VARCHAR(25),
       date     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP );

COPY reminder_services FROM stdin WITH DELIMITER '|';
  5 |         6 |                  3 |                 6 |                   1
  6 |         7 |                  4 |                 5 |                   1
\.

COPY themes FROM stdin WITH DELIMITER '|';
6 | CRISTI
7 | LIA
\.

COPY sent_messages FROM stdin WITH DELIMITER '|';
  62 | +40741775622 | CRISTI | 2007-06-04 07:44:45.406271+00
  70 | +40741775622 | CRISTI | 2007-06-04 07:45:01.788533+00
  71 | +40741775622 | CRISTI | 2007-06-04 07:45:03.764506+00
  80 | +40741775621 | LIA    | 2007-06-04 07:45:26.309215+00
  81 | +40741775621 | LIA    | 2007-06-04 07:45:28.314075+00
  63 | +40741775622 | CRISTI | 2007-06-03 07:44:00+00
  64 | +40741775622 | CRISTI | 2007-06-02 07:44:00+00
  65 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
  66 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
  67 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
  68 | +40741775622 | CRISTI | 2007-04-01 07:44:00+00
  69 | +40741775622 | CRISTI | 2007-05-01 07:44:00+00
  72 | +40741775621 | LIA    | 2007-06-03 07:44:00+00
  73 | +40741775621 | LIA    | 2007-06-03 07:44:00+00
  74 | +40741775621 | LIA    | 2007-06-02 07:44:00+00
  75 | +40741775621 | LIA    | 2007-06-02 07:44:00+00
  76 | +40741775621 | LIA    | 2007-06-01 07:44:00+00
  77 | +40741775621 | LIA    | 2007-06-01 07:44:00+00
  78 | +40741775621 | LIA    | 2007-06-01 07:44:00+00
  79 | +40741775621 | LIA    | 2007-06-01 07:44:00+00
\.


    SELECT SUM(B.count), 
           A.theme, 
           A.receiver, 
           A.dates
      FROM ( SELECT DISTINCT COUNT(*), 
                    sent_messages.theme, 
                    receiver, 
                    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 TRIM(sent_messages.theme)=TRIM(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
              FROM reminder_services, themes,sent_messages 
             WHERE themes.uid=reminder_services.theme_uid 
               AND TRIM(sent_messages.theme)=TRIM(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;


view thread (22+ messages)

Message-ID: <1c23c8e70706060547l692c0e0cn879daaf6f3a1b0d5@mail.gmail.com>
Permalink:  ../1c23c8e70706060547l692c0e0cn879daaf6f3a1b0d5@mail.gmail.com/
Also on:    postgresql.org/message-id/1c23c8e70706060547l692c0e0cn879daaf6f3a1b0d5@mail.gmail.com

 · 

reply
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, pgsql-novice@postgresql.org
  Subject: Re: JOIN
  In-Reply-To: <1c23c8e70706060547l692c0e0cn879daaf6f3a1b0d5@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