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

Replace usage of a table in query with its array values

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Replace usage of a table in query with its array values
Date: 2007-07-02 08:23:58
Message-ID: 1c23c8e70707020123o5a8b6512i2d2ad8888cb921cc@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-novicepgsql-sql
Hi everybody,

I have the following 3 tables:

sent_messages

 uid |   receiver        | theme    |             date
-----+----------------------+-------------+-------------------------------
   1 | +40741775630  | CAND1 | 2007-06-12 14:06:57.843024+00
   2 | +40741775630  | MAMA  | 2007-06-12 14:07:03.14247+00
   3 | +40741775630  | MAMA  | 2007-06-12 14:10:01.250825+00
   4 | +40741775621  | LIA       | 2007-07-02 07:02:28.245648+00
   5 | +40741775621  | LIA       | 2007-07-02 07:02:32.541576+00
   6 | +40741775621  | LIA       | 2007-07-02 07:02:33.80777+00
   7 | +40741775621  | LIA       | 2007-07-02 07:02:34.780668+00
   8 | +40741775621  | LIA       | 2007-07-02 07:02:35.664055+00
   9 | +40741775621  | LIA       | 2007-07-02 07:02:36.445283+00
  10 | +40741775621 | MIA      | 2007-07-02 07:02:41.518046+00
  11 | +40741775621 | MIA      | 2007-07-02 07:02:42.287176+00
  12 | +40741775621 | MIA      | 2007-07-02 07:02:43.003328+00
  13 | +40741775621 | MIA      | 2007-07-02 07:02:43.581017+00
  14 | +40741775621 | MIA      | 2007-07-02 07:02:44.163022+00
  15 | +40741775621 | MIA      | 2007-07-02 07:02:44.720369+00
  16 | +40741775621 | MIA      | 2007-07-02 07:02:45.346606+00
  17 | +40741775622 | MIA      | 2007-07-02 07:02:52.061171+00
  18 | +40741775622 | MIA      | 2007-07-02 07:02:52.85079+00
  19 | +40741775622 | MIA      | 2007-07-02 07:02:53.913305+00
  20 | +40741775622 | MIA      | 2007-07-02 07:02:54.50295+00
  21 | +40741775622 | MIA      | 2007-07-02 07:02:55.108986+00

themes
 uid | theme
-----+-------
   8 | MIA
   7 | LIA

reminder_services
 uid | theme_uid | activity_mt_amount | activity_min_days |
activity_max_months | inactivity_days | limit_reminders | limit_months
|         scanning_time
------+---------------+------------------------------+---------------------------+-------------------------------+-----------------------+-----------------------+-------------------+-------------------------------------------
  20 |             8 |                             4 |
    3 |                              2 |                     0 |
         3 |                 6 | 2007-07-02 07:38:04.349592+00
      (current_time)
  34 |             7 |                             7 |
   1 |                              1 |                     0 |
        1 |                 1 | 2007-07-02 07:38:04.349592+00
   (current_time)



I created the following query using this three tables:

SELECT DISTINCT filtered.theme,
                filtered.receiver
  FROM ( SELECT SUM( B.count ),
                A.theme,
                A.receiver,
                A.dates,
                A.activity_min_days,
                A.activity_MT_amount,
                A.activity_max_months
           FROM ( SELECT DISTINCT sent_messages.theme,
                         reminder_services.theme_uid,
                         receiver,
                         ARRAY( SELECT date::date + s.a FROM
generate_series( 0, activity_min_days -1 ) AS s( a ) ) AS dates,
                         activity_min_days,
                         activity_MT_amount,
                         activity_max_months
                    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' )
                     AND date_trunc( 'day', sent_messages.date ) < ( now() -
reminder_services.inactivity_days * INTERVAL'1 day')
                     AND EXTRACT( DOW FROM CURRENT_TIMESTAMP )    = EXTRACT(
DOW FROM scanning_time)
                     AND EXTRACT( HOUR FROM CURRENT_TIMESTAMP )   = EXTRACT(
HOUR FROM scanning_time )
                     AND EXTRACT( MINUTE FROM CURRENT_TIMESTAMP ) = EXTRACT(
MINUTE FROM scanning_time ) ) 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 sent_messages.theme = themes.theme
                     AND date_trunc( 'day', sent_messages.date ) > ( now() -
reminder_services.activity_max_months * INTERVAL' 1 month' )
                     AND date_trunc( 'day', sent_messages.date ) < ( now() -
reminder_services.inactivity_days * INTERVAL'1 day' )
                     AND EXTRACT( DOW FROM CURRENT_TIMESTAMP )    = EXTRACT(
DOW FROM scanning_time)
                     AND EXTRACT( HOUR FROM CURRENT_TIMESTAMP )   = EXTRACT(
HOUR FROM scanning_time )
                     AND EXTRACT( MINUTE FROM CURRENT_TIMESTAMP ) = EXTRACT(
MINUTE FROM scanning_time )
                GROUP BY sent_messages.theme, receiver, date ) 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,
                A.activity_min_days,
                A.activity_MT_amount,
                A.activity_max_months) AS filtered
 WHERE filtered.sum >= filtered.activity_MT_amount;

The above query returns the following result:

theme |   receiver
---------+--------------
 MIA   | +40741775621
 MIA   | +40741775622

After database redesign it was decided that reminder_services table to be
moved
into another database. In production, reminder_services table will never
have more
than 100 of records, but sent_messages table it is a log table with a lot of
records
and the above query will be executed every minute.  So, I was thinking to
keep
the skeleton of the above query and using arrays instead of using the
reminder_services
table. I want that the following query to be executed  and then the arrays
to be used in
the above query:

SELECT ARRAY( SELECT uid FROM reminder_services ) AS uid,
        ARRAY( SELECT theme_uid FROM reminder_services) as theme_uid,
        ARRAY( SELECT activity_mt_amount FROM reminder_services) as
activity_mt_amount,
        ARRAY( SELECT activity_min_days FROM reminder_services) as
activity_min_days,
        ARRAY( SELECT activity_max_months FROM reminder_services) as
activity_max_months,
        ARRAY( SELECT inactivity_days FROM reminder_services) as
inactivity_days,
        ARRAY( SELECT limit_reminders FROM reminder_services) as
limit_reminders,
        ARRAY( SELECT limit_months FROM reminder_services) as limit_months,
        ARRAY( SELECT scanning_time FROM reminder_services) as
scanning_time;

My problem is that I don't know how to integrate(loop, access etc) these
arrays in the query.
Hope it's possible. Any other ideea would be greatly appreciated.


Best,
    Loredana

Responses

pgsql-novice by date

Next:From: Ashish KaralkarDate: 2007-07-02 08:44:59
Subject: Schema Problem
Previous:From: saumya goelDate: 2007-07-02 05:00:34
Subject: problem in plpgsql

pgsql-jdbc by date

Next:From: Mario SplivaloDate: 2007-07-02 12:41:42
Subject: Re: Log files cluttered with jdbc3/4 for pg8.2
Previous:From: Albe LaurenzDate: 2007-06-28 13:00:28
Subject: Re: setObject type for Interval objects

pgsql-sql by date

Next:From: Guy RouillierDate: 2007-07-02 15:38:16
Subject: Re: Replace usage of a table in query with its array values
Previous:From: Andrej Ricnik-BayDate: 2007-07-02 00:16:45
Subject: Re: yet another simple SQL question

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