pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCount rows by day interval
2+ messages / 2 participants
[nested] [flat]
* Count rows by day interval
@ 2007-05-10 08:01 Loredana Curugiu <loredana.curugiu@gmail.com>
2007-05-10 18:34 ` Re: Count rows by day interval chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Loredana Curugiu @ 2007-05-10 08:01 UTC (permalink / raw)
To: pgsql-sql; pgsql-novice@postgresql.org
Dear all,
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
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
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
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);
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
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Count rows by day interval
2007-05-10 08:01 Count rows by day interval Loredana Curugiu <loredana.curugiu@gmail.com>
@ 2007-05-10 18:34 ` chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: chester c young @ 2007-05-10 18:34 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-sql
> ...
instead of date_trunc('day',sent_messages.date)
why don't you have a function that takes four three arguments:
- beginning date of query
- interval, ie, reminder_services.activity_days_min
- timestamp, ie, sent_messages.date
have it return the minimum date for that interval
as I think I understand your problem, that should work
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-05-10 18:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-10 08:01 Count rows by day interval Loredana Curugiu <loredana.curugiu@gmail.com>
2007-05-10 18:34 ` chester c young <chestercyoung@yahoo.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