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
