[SQL] Count rows by day interval
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
Re: [SQL] query to select a linked list
On Thu, May 10, 2007 at 09:49:32AM +1000, Robert Edwards wrote: > > Hi Louis-David, > > I also have written a forum application using PostgreSQL. > > My schema has a "threadid" for each posting, which is actually also the > "messageid" of the first posting in the thread, but that is irrelevant. > > I can then just select all messages belonging to that thread. The actual > hierarchy of messages (which posting is in response to which) is dealt > with by a "parentid", identifying the messageid of the post being > responded to. Sorting that out is done by the middleware (PHP in this > case) - the SQL query simply returns all messages in the thread in a > single query. Because our database is somewhat busy, I have opted to > keep the queries to the database simple and let the middleware sort > out the heirarchical structure (which it is quite good at). > > I hope this helps. This helps a lot, thanks. I just wrote a little pl/sql function to compensate for the absence of a threadid in my schema: create or replace function forum_children(integer) returns setof forum as $$ declare rec record; subrec record; begin for rec in select * from forum where id_parent=$1 loop return next rec; for subrec in select * from forum_children(rec.id_forum) loop return next subrec; end loop; end loop; return; end; $$ language 'plpgsql'; But in the end it might just be more convenient and clear to have that threadid column as you did. Sorting in middleware (perl in my case) also seems like good compromise. Cheers, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] insert a sequence
Dear postgres Users, I have a simple question I think. I have a table that contains some columns one of these columns (the columns ID) contains distinct integer values ... I need to insert into this table some other records but I only need that values were inserted into the ID column and that they were progressive... (a sequence starting from 100 and ending to 300, step=1) the other columns must remains null can I do this with a sequence and then an "insert into"? many thanks -- Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg. Preferisco formati liberi. Please try to avoid to send me .doc, .xls, .ppt, .dwg files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Open_format Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax (university): +39(0)755853756 fax (home): +39(0)5782830887 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Count rows by day interval
> ...
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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
