Обсуждение: Count rows group by time intervals
Dear all,
I have the following table:
theme | receiver | date
---------+----------------------+------------------------
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-05-09 00:00:00+00
I would like to count rows group by theme, receiver, and time intervals of
two days. I don't know how to start.
Hope that somebody could help me.
I have the following table:
theme | receiver | date
---------+----------------------+------------------------
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-05-09 00:00:00+00
I would like to count rows group by theme, receiver, and time intervals of
two days. I don't know how to start.
Hope that somebody could help me.
At 8:07a -0400 on 09 May 2007, Loredana Curugiu wrote: > I have the following table: > > theme | receiver | date > ---------+----------------------+------------------------ > LIA | +40741775622 | 2007-04-27 00:00:00+00 > LIA | +40741775622 | 2007-04-25 00:00:00+00 > MIA | +40741775622 | 2007-04-27 00:00:00+00 > MIA | +40741775622 | 2007-05-09 00:00:00+00 > > I would like to count rows group by theme, receiver, and time > intervals of > two days. I don't know how to start. Should get you started: SELECT COUNT( * ), theme, receiver, date FROM yourTable GROUP BY theme, receiver, date ; Kevin
Howdy, Loredana.
You need a query that returns the number of receivers on each theme, in two days intervals, is this correct?
Please try something like this. I am not sure if it works, because I don't have a table like yours.
I am assuming your table is called table
SELECT a."date",a."theme",a.receiver,COUNT(*)
FROM table a
INNER JOIN table b
ON a."theme" = b."theme" AND a.receiver = b.receiver
WHERE (((b."date" - a."date") = 1) OR (b."date" - a."date") = 0)
GROUP BY a."date",a."theme",a.receiver
HAVING (a."date" - '2007-01-01' ) % 2 = 0
ORDER BY a."date"
FROM table a
INNER JOIN table b
ON a."theme" = b."theme" AND a.receiver = b.receiver
WHERE (((b."date" - a."date") = 1) OR (b."date" - a."date") = 0)
GROUP BY a."date",a."theme",a.receiver
HAVING (a."date" - '2007-01-01' ) % 2 = 0
ORDER BY a."date"
Then tell me if it worked
Cheers,
Oliveiros
----- Original Message -----From: Loredana CurugiuSent: Wednesday, May 09, 2007 1:07 PMSubject: [NOVICE] Count rows group by time intervalsDear all,
I have the following table:
theme | receiver | date
---------+----------------------+------------------------
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-27 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-26 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
LIA | +40741775622 | 2007-04-25 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-04-27 00:00:00+00
MIA | +40741775622 | 2007-05-09 00:00:00+00
I would like to count rows group by theme, receiver, and time intervals of
two days. I don't know how to start.
Hope that somebody could help me.
--- Loredana Curugiu <loredana.curugiu@gmail.com> wrote: > theme | receiver | date > ---------+----------------------+------------------------ > LIA | +40741775622 | 2007-04-27 00:00:00+00 > > I would like to count rows group by theme, receiver, and time intervals of > two days. I don't know how to start. Another way is to use an auxillary table to join on your required date range. SELECT A.theme, A.receiver, COUNT(A.date), Time_range.date_start, Time_range.date_end FROM Your_table A INNER JOIN ( SELECT ( SELECT MIN( date ) FROM Your_table ) + x * INTERVAL '1 day' AS date_start, ( SELECT MIN( date ) FROM Your_table ) + 2 + x * INTERVAL '1 day' AS date_end, FROM Generate_series( 1, ( SELECT MAX( date ) FROM Your_table ) - ( SELECT MIN( date ) FROM Your_table ), 2 ) ) AS Time_range( date_start, date_end ) ON B.date_start <= A.date AND B.date_end > A.date GROUP BY A.theme, A.receiver, Time_range.date_start, Time_range.date_end; I hope this helps. Regards, Richard Broersma Jr. P.S. the Generate_series() function is just standing in the place of an auxillary table.
Another way is to use an auxillary table to join on your required date range.
SELECT A.theme, A.receiver, COUNT(A.date),
Time_range.date_start, Time_range.date_end
FROM Your_table A
INNER JOIN ( SELECT ( SELECT MIN( date ) FROM Your_table ) + x * INTERVAL '1 day' AS date_start,
( SELECT MIN( date ) FROM Your_table ) + 2 + x * INTERVAL '1 day' AS date_end,
FROM Generate_series( 1, ( SELECT MAX( date ) FROM Your_table ) -
( SELECT MIN( date ) FROM Your_table ), 2 )
) AS Time_range( date_start, date_end )
ON B.date_start <= A.date AND B.date_end > A.date
GROUP BY A.theme, A.receiver, Time_range.date_start, Time_range.date_end;
Hi Richard,
I think your solution is very good and elegant, but I cannot call generate_series()
because in the 7.4.2 version of postgres ( I use this version ) this function doesn't
exist.
Many thanks,
Loredana