Обсуждение: Fw: Count rows group by time intervals
Parents missing, sorry :-)
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"
----- Original Message -----
From: Oliveiros Cristina
Sent: Wednesday, May 09, 2007 2:44 PM
Subject: Re: [NOVICE] Count rows group by time intervals
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.
You need a query that returns the number of receivers on each theme, in two days intervals, is this correct?
yes, this is correct. Actually the interval in days is variable ( it would specified using PreparedStatemtent ).
Sorry I didn't say from the begining.
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 tableSELECT 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"
ERROR: operator does not exist: interval % integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
Funny thing.
In mine, it works...
On mine, If I subtract one date from the other I get the number of days in between...which is another integer, I guess. and '%' is the modular operator. Doesnt it work like this on yours?
Which version are you using?
try
SELECT (date - '2007-01-01') % 2
FROM table
to see if you get zeroes and ones .
Cheers,
Oliveiros
In mine, it works...
On mine, If I subtract one date from the other I get the number of days in between...which is another integer, I guess. and '%' is the modular operator. Doesnt it work like this on yours?
Which version are you using?
try
SELECT (date - '2007-01-01') % 2
FROM table
to see if you get zeroes and ones .
Cheers,
Oliveiros
2007/5/9, Loredana Curugiu <loredana.curugiu@gmail.com >:
The query above it's not working, I've got the following error:You need a query that returns the number of receivers on each theme, in two days intervals, is this correct?
yes, this is correct. Actually the interval in days is variable ( it would specified using PreparedStatemtent ).
Sorry I didn't say from the begining.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 tableSELECT 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"
ERROR: operator does not exist: interval % integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
Funny thing.
In mine, it works...
On mine, If I subtract one date from the other I get the number of days in between...which is another integer, I guess. and '%' is the modular operator. Doesnt it work like this on yours?
If I substract one date from the other I get an interval.
Which version are you using?
I am using 7.4.12 version of postgres.
try
SELECT (date - '2007-01-01') % 2
FROM table
to see if you get zeroes and ones .
If I use
SELECT date_part( date-'2007-01-01' )::int % 2
FROM table
I get zeros and ones.
SELECT date_part( date-'2007-01-01' )::int % 2
FROM table
I get zeros and ones.
Ops... Mine is 8.2 beta 3...
With High probability the problem is difference in versions...
Well, Loredana, I have to say that Ive never used your version, so probably I cannot be a big help, then.
But you seem to know how to convert intervals to integers with the datepart()::int thing.
then Please try to enhance my query adding that to the three parts where I use date differences
Best,
Oliveiros
----- Original Message -----From: Loredana CurugiuSent: Wednesday, May 09, 2007 4:01 PMSubject: Re: Fw: [NOVICE] Count rows group by time intervalsIt's not working. I've got the same error.Funny thing.
In mine, it works...
On mine, If I subtract one date from the other I get the number of days in between...which is another integer, I guess. and '%' is the modular operator. Doesnt it work like this on yours?
If I substract one date from the other I get an interval.Which version are you using?
I am using 7.4.12 version of postgres.try
SELECT (date - '2007-01-01') % 2
FROM table
to see if you get zeroes and ones .
"Loredana Curugiu" <loredana.curugiu@gmail.com> writes: >> On mine, If I subtract one date from the other I get the number of days >> in between...which is another integer, I guess. and '%' is the modular >> operator. Doesnt it work like this on yours? > If I substract one date from the other I get an interval. If you subtract two *date* values you get an integer. If you subtract two *timestamp* values you get an interval. regards, tom lane
Hi Oliveiros,
With your query I get the following result
date | theme | receiver | count
------------------------------------+----------+----------------------+-------
2007-04-27 00:00:00+00 | MIA | +40741775621 | 25
2007-04-25 00:00:00+00 | LIA | +40741775621 | 50
2007-05-09 00:00:00+00 | MIA | +40741775621 | 1
2007-04-27 00:00:00+00 | LIA | +40741775621 | 81
The count it is not correct.
Best,
Loredana
With your query I get the following result
date | theme | receiver | count
------------------------------------+----------+----------------------+-------
2007-04-27 00:00:00+00 | MIA | +40741775621 | 25
2007-04-25 00:00:00+00 | LIA | +40741775621 | 50
2007-05-09 00:00:00+00 | MIA | +40741775621 | 1
2007-04-27 00:00:00+00 | LIA | +40741775621 | 81
The count it is not correct.
Best,
Loredana
Yes,I've already figured out the mistake.
Can you please try this one?
Please Tell me if it worked
SELECT a."date",a."theme",a.receiver,COUNT(*)
FROM (SELECT DISTINCT * FROM t_loredana) a
INNER JOIN t_loredana 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 (SELECT DISTINCT * FROM t_loredana) a
INNER JOIN t_loredana 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"
----- Original Message -----From: Loredana CurugiuSent: Thursday, May 10, 2007 8:15 AMSubject: Re: Fw: [NOVICE] Count rows group by time intervalsHi Oliveiros,
With your query I get the following result
date | theme | receiver | count
------------------------------------+----------+----------------------+-------
2007-04-27 00:00:00+00 | MIA | +40741775621 | 25
2007-04-25 00:00:00+00 | LIA | +40741775621 | 50
2007-05-09 00:00:00+00 | MIA | +40741775621 | 1
2007-04-27 00:00:00+00 | LIA | +40741775621 | 81
The count it is not correct.
Best,
Loredana
On 5/10/07, Oliveiros Cristina <oliveiros.cristina@marktest.pt> wrote:
I worked! Thanks a lot, Oliveiros.
I didn't understand which is the aim of the condition
Loredana
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
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 -----From: Loredana CurugiuSent: Thursday, May 10, 2007 12:40 PMSubject: Re: Fw: [NOVICE] Count rows group by time intervalsOn 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 conditionWHERE (((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
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
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
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 -----From: Oliveiros CristinaSent: Thursday, May 10, 2007 2:37 PMSubject: Re: Fw: [NOVICE] Count rows group by time intervalsHello, 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 theeven 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 careLa revedere ,Oliveiros :-)
----- Original Message -----From: Loredana CurugiuSent: Thursday, May 10, 2007 12:40 PMSubject: Re: Fw: [NOVICE] Count rows group by time intervalsOn 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 conditionWHERE (((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
Hi, Oliveiros
Sorry for the delay. I wasn't on my desk on Friday, I took a short vacation :)
Your last solution is good, it solved my problem. The values for count are correct
now.
Your help and explanations are very precious for me. Thank you very much.
Pe curand,
Loredana
Sorry for the delay. I wasn't on my desk on Friday, I took a short vacation :)
Your last solution is good, it solved my problem. The values for count are correct
now.
Your help and explanations are very precious for me. Thank you very much.
Pe curand,
Loredana