pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReplace usage of a table in query with its array values
3+ messages / 2 participants
[nested] [flat]
* Replace usage of a table in query with its array values
@ 2007-07-02 08:23 Loredana Curugiu <loredana.curugiu@gmail.com>
2007-07-02 15:38 ` Re: Replace usage of a table in query with its array values Guy Rouillier <guyr-ml1@burntmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Loredana Curugiu @ 2007-07-02 08:23 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Replace usage of a table in query with its array values
2007-07-02 08:23 Replace usage of a table in query with its array values Loredana Curugiu <loredana.curugiu@gmail.com>
@ 2007-07-02 15:38 ` Guy Rouillier <guyr-ml1@burntmail.com>
2007-07-02 16:49 ` Re: Replace usage of a table in query with its array values Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Guy Rouillier @ 2007-07-02 15:38 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org
Loredana Curugiu wrote:
> 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.
Cross posting to multiple lists is frowned upon, so I'm reply only here.
Have you considered using the dblink contrib module? That seems a far
simpler solution than trying to do this with arrays.
--
Guy Rouillier
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Replace usage of a table in query with its array values
2007-07-02 08:23 Replace usage of a table in query with its array values Loredana Curugiu <loredana.curugiu@gmail.com>
2007-07-02 15:38 ` Re: Replace usage of a table in query with its array values Guy Rouillier <guyr-ml1@burntmail.com>
@ 2007-07-02 16:49 ` Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Loredana Curugiu @ 2007-07-02 16:49 UTC (permalink / raw)
To: Guy Rouillier <guyr-ml1@burntmail.com>; pgsql-jdbc@postgresql.org
On 7/2/07, Guy Rouillier <guyr-ml1@burntmail.com> wrote:
>
> Loredana Curugiu wrote:
> > 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.
>
> Cross posting to multiple lists is frowned upon, so I'm reply only here.
> Have you considered using the dblink contrib module? That seems a far
> simpler solution than trying to do this with arrays.
Thank you for informations.
Best,
Loredana
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-02 16:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-02 08:23 Replace usage of a table in query with its array values Loredana Curugiu <loredana.curugiu@gmail.com>
2007-07-02 15:38 ` Guy Rouillier <guyr-ml1@burntmail.com>
2007-07-02 16:49 ` Loredana Curugiu <loredana.curugiu@gmail.com>
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