Обсуждение: Count rows group by time intervals

Поиск
Список
Период
Сортировка

Count rows group by time intervals

От
"Loredana Curugiu"
Дата:
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.




Re: Count rows group by time intervals

От
Kevin Hunter
Дата:
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

Re: Count rows group by time intervals

От
"Oliveiros Cristina"
Дата:
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"
 
 
Then tell me if it worked
 
Cheers,
Oliveiros
 
----- Original Message -----
Sent: Wednesday, May 09, 2007 1:07 PM
Subject: [NOVICE] 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.




Re: Count rows group by time intervals

От
Richard Broersma Jr
Дата:
--- 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.

Re: Count rows group by time intervals

От
"Loredana Curugiu"
Дата:

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