Re: Fw: Count rows group by time intervals

Поиск
Список
Период
Сортировка
От Oliveiros Cristina
Тема Re: Fw: Count rows group by time intervals
Дата
Msg-id 013301c79313$0497fe00$ec5a3d0a@marktestcr.marktest.pt
обсуждение исходный текст
Ответ на Fw: Count rows group by time intervals  ("Oliveiros Cristina" <oliveiros.cristina@marktest.pt>)
Список pgsql-novice
Дерево обсуждения
Hello  again, Loredana.
 
False alarm!
 
Sorry, the query we have is not the perfect version yet. It worked because (by luck) you have  even days for all intervals, but if you change the 9/5 day to 10/5 it would fail.
 
I am very sorry for having mislead you.
 
OK, then please substitute (SELECT DISTINCT * FROM t_loredana)
by
(SELECT DISTINCT  theme,receiver, (DATE'2007-01-01' + 2 *((date - '2007-01-01') / 2)) as date  FROM t_loredana)
 
and please eliminate the HAVING clause
 
So final result should be :
 
SELECT   a."date",a."theme",a.receiver,COUNT(*)
FROM (
SELECT DISTINCT  theme,receiver, (DATE'2007-01-01' + 2 *((date - '2007-01-01') / 2)) as date  FROM t_loredana
) a
INNER JOIN t_loredana b
ON a."theme" = b."theme" AND a.receiver = b.receiver
 WHERE (((b."date" - a."date") < 2) AND ((b."date" - a."date") >= 0) )
GROUP BY a."date",a."theme",a.receiver
 
ORDER BY a."date" 
 
Here 2 is the length of interval, the x I talked about on previous query.
 
You can substitute it by the length in days of the interval you want.
 
So to obtain 3 days interval it would become
 
 
SELECT   a."date",a."theme",a.receiver,COUNT(*)
FROM (
SELECT DISTINCT  theme,receiver, (DATE'2007-01-01' + 3 *((date - '2007-01-01') / 3)) as date  FROM t_loredana
) a
INNER JOIN t_loredana b
ON a."theme" = b."theme" AND a.receiver = b.receiver
WHERE (((b."date" - a."date") < 3) AND ((b."date" - a."date") >= 0) )
GROUP BY a."date",a."theme",a.receiver
ORDER BY a."date" 
 
The date column will have the first day of the 3 intervals where there are records, since 2007-01-01
 
Hope I got it right this time...
 
La revedere,
 
Oliveiros
----- Original Message -----
Sent: Thursday, May 10, 2007 2:37 PM
Subject: Re: Fw: [NOVICE] Count rows group by time intervals

Hello, Loredana.
 
Great to hear about the progress!!
 
Well, that part of the condition is to select the day, or the day after.
 
with the condition  ((a."date" - '2007-01-01' ) % 2 = 0 you obtain just the
even days, correct?
 
Then with the WHERE clause you are selecting, for a certain even day, all the records that match that day and the day after,  got the idea?
 
Then all you have to do it is COUNT them.
 
If it is to allow the time interval to vary, say you have a number of days of x.
 
Then you should change the HAVING clause to ((a."date" - '2007-01-01' ) % x = 0 ,
and change the WHERE clause to something like WHERE (((b."date" - a."date") >= 0) OR (b."date" - a."date") < x)
 
Try this, and please let me know if it solved the problem.
 
I use C# to access postgres database and it is very easy to parameterize a query with the NpgsqlParameter class.
 
I don't know if you are also using C# to interface with your database....
 
Take care
 
La revedere ,
 
Oliveiros :-)

 
----- Original Message -----
Sent: Thursday, May 10, 2007 12:40 PM
Subject: Re: Fw: [NOVICE] Count rows group by time intervals

On 5/10/07, Oliveiros Cristina <oliveiros.cristina@marktest.pt> wrote:
Yes,I've already figured out the mistake.
Can you please try this one?
Please Tell me if it worked


I worked! Thanks a lot, Oliveiros.

I didn't understand which is the aim of the condition
WHERE (((b."date" - a."date") = 1) OR (b."date" - a."date") = 0)

And should I modify the query if the time interval ( in days ) is varing?



Loredana


В списке pgsql-novice по дате отправления:

Предыдущее
От: "Oliveiros Cristina"
Дата:
Сообщение: Re: Fw: Count rows group by time intervals
Следующее
От: Richard Broersma Jr
Дата:
Сообщение: Re: Count rows group by time intervals