Count rows by day interval - Mailing list pgsql-sql
| From | Loredana Curugiu |
|---|---|
| Subject | Count rows by day interval |
| Date | |
| Msg-id | 1c23c8e70705100101y63bd0c21g466ca0ee830cdec8@mail.gmail.com Whole thread Raw |
| Responses |
Re: Count rows by day interval
(chester c young <chestercyoung@yahoo.com>)
|
| List | pgsql-sql |
Dear all,
I have the following 3 tables:
TABLE 1: themes
TABLE 2: reminder_services
TABLE 3: sent_messages
With the following query
I get the result:
With my query I get, for each day, the number of messages per theme and per receiver.
I have to have a query which returns the number of messages per theme and per receiver
within a interval in days. The interval should be specified by reminder_services.activity_min_days.
PS: I use the 7.4.2 version of postgres.
Please help.
Loredana
I have the following 3 tables:
TABLE 1: themes
uid | theme
-----+--------
1 | HOME
2 | BILL
3 | ERROR
4 | ACTION
5 | ANA
6 | LIA
7 | MIA
-----+--------
1 | HOME
2 | BILL
3 | ERROR
4 | ACTION
5 | ANA
6 | LIA
7 | MIA
TABLE 2: reminder_services
uid | theme_uid | activity_min_days | activity_max_months
-----+----------------+----------------------------+---------------------
3 | 4 | 10 | 2
1 | 1 | 2 | 2
2 | 2 | 9 | 2
4 | 3 | 2 | 2
5 | 5 | 4 | 2
6 | 6 | 1 | 2
7 | 7 | 7 | 2
-----+----------------+----------------------------+---------------------
3 | 4 | 10 | 2
1 | 1 | 2 | 2
2 | 2 | 9 | 2
4 | 3 | 2 | 2
5 | 5 | 4 | 2
6 | 6 | 1 | 2
7 | 7 | 7 | 2
TABLE 3: sent_messages
theme | receiver | date
----------+---------------------+--------------------------------------
MIA | +40741775623 | 2007-04-27 09:25:00.739539+00
MIA | +40741775623 | 2007-04-27 09:25:05.520008+00
MIA | +40741775623 | 2007-04-27 09:25:09.530823+00
MIA | +40741775623 | 2007-04-27 09:25:11.734992+00
MIA | +40741775623 | 2007-04-27 09:25:13.91252+00
LIA | +40741775622 | 2007-04-27 09:25:19.411224+00
LIA | +40741775622 | 2007-04-27 09:25:21.877943+00
LIA | +40741775622 | 2007-04-27 09:25:23.965741+00
LIA | +40741775622 | 2007-04-27 09:25:25.788078+00
LIA | +40741775622 | 2007-04-27 09:25:27.523619+00
LIA | +40741775622 | 2007-04-27 09:25:29.607638+00
LIA | +40741775622 | 2007-04-27 09:25:31.642954+00
LIA | +40741775622 | 2007-04-27 09:25:33.517135+00
LIA | +40741775622 | 2007-04-27 09:25:35.715635+00
LIA | +40741775622 | 2007-04-26 09:31:35.464341+00
LIA | +40741775622 | 2007-04-26 09:31:38.802103+00
LIA | +40741775622 | 2007-04-26 09:31:41.477627+00
LIA | +40741775622 | 2007-04-26 09:31:43.593623+00
LIA | +40741775622 | 2007-04-26 09:31:46.330541+00
LIA | +40741775622 | 2007-04-25 09:32:12.526063+00
LIA | +40741775622 | 2007-04-25 09:32:14.797835+00
LIA | +40741775622 | 2007-04-25 09:32:17.117164+00
LIA | +40741775622 | 2007-04-25 09:32:19.17326+00
LIA | +40741775622 | 2007-04-25 09:32:21.293361+00
MIA | +40741775623 | 2007-05-09 06:54:46.299291+00
----------+---------------------+--------------------------------------
MIA | +40741775623 | 2007-04-27 09:25:00.739539+00
MIA | +40741775623 | 2007-04-27 09:25:05.520008+00
MIA | +40741775623 | 2007-04-27 09:25:09.530823+00
MIA | +40741775623 | 2007-04-27 09:25:11.734992+00
MIA | +40741775623 | 2007-04-27 09:25:13.91252+00
LIA | +40741775622 | 2007-04-27 09:25:19.411224+00
LIA | +40741775622 | 2007-04-27 09:25:21.877943+00
LIA | +40741775622 | 2007-04-27 09:25:23.965741+00
LIA | +40741775622 | 2007-04-27 09:25:25.788078+00
LIA | +40741775622 | 2007-04-27 09:25:27.523619+00
LIA | +40741775622 | 2007-04-27 09:25:29.607638+00
LIA | +40741775622 | 2007-04-27 09:25:31.642954+00
LIA | +40741775622 | 2007-04-27 09:25:33.517135+00
LIA | +40741775622 | 2007-04-27 09:25:35.715635+00
LIA | +40741775622 | 2007-04-26 09:31:35.464341+00
LIA | +40741775622 | 2007-04-26 09:31:38.802103+00
LIA | +40741775622 | 2007-04-26 09:31:41.477627+00
LIA | +40741775622 | 2007-04-26 09:31:43.593623+00
LIA | +40741775622 | 2007-04-26 09:31:46.330541+00
LIA | +40741775622 | 2007-04-25 09:32:12.526063+00
LIA | +40741775622 | 2007-04-25 09:32:14.797835+00
LIA | +40741775622 | 2007-04-25 09:32:17.117164+00
LIA | +40741775622 | 2007-04-25 09:32:19.17326+00
LIA | +40741775622 | 2007-04-25 09:32:21.293361+00
MIA | +40741775623 | 2007-05-09 06:54:46.299291+00
With the following query
SELECT COUNT(*),
sent_messages.theme,
sent_messages.receiver,
date_trunc('day',sent_messages.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')
GROUP BY sent_messages.theme, sent_messages.receiver, date_trunc('day',sent_messages.date);
sent_messages.theme,
sent_messages.receiver,
date_trunc('day',sent_messages.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')
GROUP BY sent_messages.theme, sent_messages.receiver, date_trunc('day',sent_messages.date);
I get the result:
count | theme | receiver | date_trunc
----------+---------+----------------------+------------------------
5 | LIA | +40741775622 | 2007-04-26 00:00:00+00
5 | LIA | +40741775622 | 2007-04-25 00:00:00+00
9 | LIA | +40741775622 | 2007-04-27 00:00:00+00
1 | MIA | +40741775623 | 2007-05-09 00:00:00+00
5 | MIA | +40741775623 | 2007-04-27 00:00:00+00
----------+---------+----------------------+------------------------
5 | LIA | +40741775622 | 2007-04-26 00:00:00+00
5 | LIA | +40741775622 | 2007-04-25 00:00:00+00
9 | LIA | +40741775622 | 2007-04-27 00:00:00+00
1 | MIA | +40741775623 | 2007-05-09 00:00:00+00
5 | MIA | +40741775623 | 2007-04-27 00:00:00+00
With my query I get, for each day, the number of messages per theme and per receiver.
I have to have a query which returns the number of messages per theme and per receiver
within a interval in days. The interval should be specified by reminder_services.activity_min_days.
PS: I use the 7.4.2 version of postgres.
Please help.
Loredana