Обсуждение: Re: JOIN
Any help, please?
On 6/5/07, Loredana Curugiu <loredana.curugiu@gmail.com> wrote:
Hi everybody,
I have the following table:
count | theme | receiver | date | dates | -------+-----------+----------------------+------------------------------------+-------------------------------------------------------------------------------------------------------------------------+-------------------
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
I want to add up the count column grouped by theme and receiver for the dates included in the dates column.
So I have the following query:
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
The result of the query is:
sum | theme | receiver | dates
-------+-----------+--------------+--------------------------------------------------------------------------------
3 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
3 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
18 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
4 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
6 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
10 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
The result is wrong. I don't know what it is wrong at my query.
Please help.
Best,
Loredana
>> So I have the following query:>>
>> SELECT SUM(A.count),
>> A.theme,
>> A.receiver,
>> A.dates
>> FROM my_table A
>> INNER JOIN my_table B
>> ON A.theme=B.theme
>> AND A.receiver=B.receiver
>> AND A.date=ANY(B.dates)
>> GROUP BY A.theme,A.receiver, A.dates;
[snip]
>>
>> The result is wrong. I don't know what it is wrong at my query.
>> Please help.
You don't actually say what's wrong. What are you expecting as output?
I am trying to say that sum column it is not calculated correctly.
Oh, and your "date" column isn't - it's a timestamp with time-zone. That
might or might not cause confusion with daylight-saving-times.
Yes, I am working with timestamp with time zone .
Regards,
Loredana
>> You don't actually say what's wrong. What are you expecting as output?
I should obtain the following result:
sum | theme | receiver | dates
--------+----------+----------------------+--------------------------------------------------------------------------------
8 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
3 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
2 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)
--------+----------+----------------------+--------------------------------------------------------------------------------
8 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
3 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
2 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)
Try the query without totalling, just to see what matches you're getting.
I obtain the same result without sum column.
You're happy that daylight-saving adjustments aren't causing any
problems with your timezone settings then?
I took in consideration the daylight-saving adjustments.
Loredana
Hey, Loredana.
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct?
But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Thanx in advance
Best,
Oliveiros
--
O Quê? SQL Server 2005 Express Edition? for free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma fortuna, homem!
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct?
But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Thanx in advance
Best,
Oliveiros
2007/6/5, Loredana Curugiu <loredana.curugiu@gmail.com>:
Any help, please?On 6/5/07, Loredana Curugiu <loredana.curugiu@gmail.com> wrote:Hi everybody,
I have the following table:
count | theme | receiver | date | dates | -------+-----------+----------------------+------------------------------------+-------------------------------------------------------------------------------------------------------------------------+-------------------
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
I want to add up the count column grouped by theme and receiver for the dates included in the dates column.
So I have the following query:
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
The result of the query is:
sum | theme | receiver | dates
-------+-----------+--------------+--------------------------------------------------------------------------------
3 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
3 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
18 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
4 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
6 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
10 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
The result is wrong. I don't know what it is wrong at my query.
Please help.
Best,
Loredana
--
O Quê? SQL Server 2005 Express Edition? for free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma fortuna, homem!
On 6/5/07, Oliveiros Cristina <oliveiros.cristina@gmail.com> wrote:
Hi Oliveiros! Nice to "see" you again!
Yap.
3
Let's take a look at the following data:
count | theme | receiver | date | dates
We can see that for LIA if we sum the count per day we have the following:
theme date count
LIA 2007-06-01 00:00:00+00 4
LIA 2007-06-02 00:00:00+00 2
LIA 2007-06-03 00:00:00+00 2
LIA 2007-06-04 00:00:00+00 2
Also for CRIS:
theme date count
CRIS 2007-06-01 00:00:00+00 3
CRIS 2007-06-02 00:00:00+00 1
CRIS 2007-06-03 00:00:00+00 1
CRIS 2007-06-04 00:00:00+00 3
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-----+----------+---------------------+--------------------------------------------------------------------------------
8 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We can see that for example for the first row, the sum 8 it is correctly calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not correct: it should be taken values for the count only for the date 2007-06-04.
The same problem it is at the eigth row.
Best regards,Hey, Loredana.
Hi Oliveiros! Nice to "see" you again!
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct?
Yap.
But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver | date | dates
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
We can see that for LIA if we sum the count per day we have the following:
theme date count
LIA 2007-06-01 00:00:00+00 4
LIA 2007-06-02 00:00:00+00 2
LIA 2007-06-03 00:00:00+00 2
LIA 2007-06-04 00:00:00+00 2
Also for CRIS:
theme date count
CRIS 2007-06-01 00:00:00+00 3
CRIS 2007-06-02 00:00:00+00 1
CRIS 2007-06-03 00:00:00+00 1
CRIS 2007-06-04 00:00:00+00 3
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-----+----------+---------------------+--------------------------------------------------------------------------------
8 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We can see that for example for the first row, the sum 8 it is correctly calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not correct: it should be taken values for the count only for the date 2007-06-04.
The same problem it is at the eigth row.
Loredana
On 6/5/07, Loredana Curugiu <loredana.curugiu@gmail.com> wrote:
On 6/5/07, Oliveiros Cristina < oliveiros.cristina@gmail.com> wrote:Hey, Loredana.
Hi Oliveiros! Nice to "see" you again!Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct?
Yap.But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver | date | dates2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
We can see that for LIA if we sum the count per day we have the following:
theme date count
LIA 2007-06-01 00:00:00+00 4
LIA 2007-06-02 00:00:00+00 2
LIA 2007-06-03 00:00:00+00 2
LIA 2007-06-04 00:00:00+00 2
Also for CRIS:
theme date count
CRIS 2007-06-01 00:00:00+00 3
CRIS 2007-06-02 00:00:00+00 1
CRIS 2007-06-03 00:00:00+00 1
CRIS 2007-06-04 00:00:00+00 3
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-----+----------+---------------------+--------------------------------------------------------------------------------
8 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We can see that for example for the first row, the sum 8 it is correctly calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not correct: it should be taken values for the count only for the date 2007-06-04
The sum shoud be 3.
Best regards,The same problem it is at the eigth row. The sum should be 2.
Loredana
Hey, Loredana.
Nice to "see" you too ;-)
Thank you for your detailed clarifications.
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY() returns true if B.date is on the vector A.dates, is this correct??
Loredane, Then please let me hear bout the result
Best,
Oliveiros
--
O Quê? SQL Server 2005 Express Edition? for free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma fortuna, homem!
Nice to "see" you too ;-)
Thank you for your detailed clarifications.
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY() returns true if B.date is on the vector A.dates, is this correct??
Loredane, Then please let me hear bout the result
Best,
Oliveiros
2007/6/5, Loredana Curugiu <loredana.curugiu@gmail.com>:
On 6/5/07, Loredana Curugiu < loredana.curugiu@gmail.com> wrote:On 6/5/07, Oliveiros Cristina < oliveiros.cristina@gmail.com> wrote:Hey, Loredana.
Hi Oliveiros! Nice to "see" you again!Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct?
Yap.But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver | date | dates2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
We can see that for LIA if we sum the count per day we have the following:
theme date count
LIA 2007-06-01 00:00:00+00 4
LIA 2007-06-02 00:00:00+00 2
LIA 2007-06-03 00:00:00+00 2
LIA 2007-06-04 00:00:00+00 2
Also for CRIS:
theme date count
CRIS 2007-06-01 00:00:00+00 3
CRIS 2007-06-02 00:00:00+00 1
CRIS 2007-06-03 00:00:00+00 1
CRIS 2007-06-04 00:00:00+00 3
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-----+----------+---------------------+--------------------------------------------------------------------------------
8 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We can see that for example for the first row, the sum 8 it is correctly calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not correct: it should be taken values for the count only for the date 2007-06-04The sum shoud be 3.
Best regards,The same problem it is at the eigth row. The sum should be 2.
Loredana
--
O Quê? SQL Server 2005 Express Edition? for free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma fortuna, homem!
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
Doesn't work. I get the result
sum | theme | receiver | dates
-----+--------+--------------+--------------------------------------------------------------------------------
3 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
2 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
Which is not correct. The wrong values ( red colored ) remain as before
adding the clause. And now it is summed the counter's values per day
( first day of dates array ).
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY() returns true if B.date is on the vector A.dates, is this correct??
Correct.
Regards,Loredana
Hello again, Loredana.
Additional information required :-)
imagine the following situation
1| LIA | recv1 | date1 | (date1,date2)
2|LIA | recv1 |date 1 | (date2,date3)
3| LIA | recv1 | date1 | (date1,date3)
Should this yield 6? Or 4?
date 1 is not on the second dates column, but it is on the remaining two
Cheers,
Oliveiros
----- Original Message -----From: Loredana CurugiuSent: Tuesday, June 05, 2007 3:15 PMSubject: Re: [SQL] JOINOn 6/5/07, Loredana Curugiu <loredana.curugiu@gmail.com> wrote:On 6/5/07, Oliveiros Cristina < oliveiros.cristina@gmail.com> wrote:Hey, Loredana.
Hi Oliveiros! Nice to "see" you again!Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears on count for every record whose date is in dates column, is this correct?
Yap.But in what record's dates column? On all of them? Or just the dates column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver | date | dates2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10} |
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06} |
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09} |
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
We can see that for LIA if we sum the count per day we have the following:
theme date count
LIA 2007-06-01 00:00:00+00 4
LIA 2007-06-02 00:00:00+00 2
LIA 2007-06-03 00:00:00+00 2
LIA 2007-06-04 00:00:00+00 2
Also for CRIS:
theme date count
CRIS 2007-06-01 00:00:00+00 3
CRIS 2007-06-02 00:00:00+00 1
CRIS 2007-06-03 00:00:00+00 1
CRIS 2007-06-04 00:00:00+00 3
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-----+----------+---------------------+--------------------------------------------------------------------------------
8 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We can see that for example for the first row, the sum 8 it is correctly calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not correct: it should be taken values for the count only for the date 2007-06-04The sum shoud be 3.
Best regards,The same problem it is at the eigth row. The sum should be 2.
Loredana
On 6/5/07, Oliveiros Cristina <oliveiros.cristina@marktest.pt> wrote:
if the first column is count then the sum(count) should be 4.
Hello again, Loredana.Additional information required :-)imagine the following situation1| LIA | recv1 | date1 | (date1,date2)2|LIA | recv1 |date 1 | (date2,date3)3| LIA | recv1 | date1 | (date1,date3)Should this yield 6? Or 4?
date 1 is not on the second dates column, but it is on the remaining two
if the first column is count then the sum(count) should be 4.
Loredana
And , still, in your query, you are grouping by A.dates... is there any reason for this that I am missing ?
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
If the dates column works as a "discriminator" to see if the row should be considered or not,
maybe this would work
SELECT SUM(count), theme,receiver,date
FROM my_table
WHERE date=ANY(dates)
GROUP BY theme,receiver,date ;
But I don't know, do you need to include the column "dates" on output ?
Best,
Oliveiros
From: Loredana CurugiuSent: Tuesday, June 05, 2007 3:46 PMSubject: Re: [SQL] JOINHmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
Doesn't work. I get the result
sum | theme | receiver | dates
-----+--------+--------------+--------------------------------------------------------------------------------
3 | CRIS | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
2 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
Which is not correct. The wrong values ( red colored ) remain as before
adding the clause. And now it is summed the counter's values per day
( first day of dates array ).I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY() returns true if B.date is on the vector A.dates, is this correct??Correct.Regards,
Loredana
Oliveiros,
I think it's time to give some more details about my task. I will start with
the begining :)
I have a "log" table which stores the dates when users send messages
with a theme from their mobile phone. This table is named
sent_messages and looks like this:
receiver | theme | date
----------------------+------------+-------------------------------
+40741775622 | CRISTI | 2007-06-04 07:44:45.406271+00
+40741775622 | CRISTI | 2007-06-04 07:45:01.788533+00
+40741775622 | CRISTI | 2007-06-04 07:45:03.764506+00
+40741775621 | LIA | 2007-06-04 07:45:26.309215+00
+40741775621 | LIA | 2007-06-04 07:45:28.314075+00
+40741775622 | CRISTI | 2007-06-03 07:44:00+00
+40741775622 | CRISTI | 2007-06-02 07:44:00+00
+40741775622 | CRISTI | 2007-06-01 07:44:00+00
+40741775622 | CRISTI | 2007-06-01 07:44:00+00
+40741775622 | CRISTI | 2007-06-01 07:44:00+00
+40741775622 | CRISTI | 2007-04-01 07:44:00+00
+40741775622 | CRISTI | 2007-05-01 07:44:00+00
+40741775621 | LIA | 2007-06-03 07:44:00+00
+40741775621 | LIA | 2007-06-03 07:44:00+00
+40741775621 | LIA | 2007-06-02 07:44:00+00
+40741775621 | LIA | 2007-06-02 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
I have also a themes table:
uid | theme
-----+--------
6 | CRISTI
7 | LIA
And the table named reminder_services it is used to filter
users by theme from sent_messages table. This table looks like this:
uid | theme_uid | activity_mt_amount | activity_min_days | activity_max_months
-----+----------------+-----------------------------+---------------------------+----------------------------
5 | 6 | 3 | 6 | 1
6 | 7 | 4 | 5 | 1
The filtering should select users which sent at least activity_mt_amount messages
with theme_uid within activity_min_days consecutive days,
in the maximum activity_max_months months in the past.
Example:
The first row of the table reminder_services says that it should be selected
users which sent at least 3 messages with the theme_uid=6 (theme=CRISTI),
within 6 consecutive days, in the maximum 1 month in the past.
So, I created the following query:
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM ( SELECT COUNT(*),
sent_messages.theme,
receiver,
date_trunc('day',sent_messages.date) AS date,
ARRAY(SELECT date::date + s.a FROM generate_series(0,activity_min_days) AS s(a)) AS dates
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, receiver, date, activity_min_days ) A
INNER JOIN ( SELECT COUNT(*),
sent_messages.theme,
receiver,
date_trunc('day',sent_messages.date) AS date,
ARRAY(SELECT date::date + s.a FROM generate_series(0,activity_min_days) AS s(a)) AS dates
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, receiver, date, activity_min_days ) B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme, A.receiver, A.dates;
The result of this query is:
sum | theme | receiver | dates
-----+------------+----------------------+--------------------------------------------------------------------------------
8 | CRISTI | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRISTI | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRISTI | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRISTI | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)
The red colored values 9 and 4 are not correct, should be 3 respectively 2.
Regards,
Loredana
I think it's time to give some more details about my task. I will start with
the begining :)
I have a "log" table which stores the dates when users send messages
with a theme from their mobile phone. This table is named
sent_messages and looks like this:
receiver | theme | date
----------------------+------------+-------------------------------
+40741775622 | CRISTI | 2007-06-04 07:44:45.406271+00
+40741775622 | CRISTI | 2007-06-04 07:45:01.788533+00
+40741775622 | CRISTI | 2007-06-04 07:45:03.764506+00
+40741775621 | LIA | 2007-06-04 07:45:26.309215+00
+40741775621 | LIA | 2007-06-04 07:45:28.314075+00
+40741775622 | CRISTI | 2007-06-03 07:44:00+00
+40741775622 | CRISTI | 2007-06-02 07:44:00+00
+40741775622 | CRISTI | 2007-06-01 07:44:00+00
+40741775622 | CRISTI | 2007-06-01 07:44:00+00
+40741775622 | CRISTI | 2007-06-01 07:44:00+00
+40741775622 | CRISTI | 2007-04-01 07:44:00+00
+40741775622 | CRISTI | 2007-05-01 07:44:00+00
+40741775621 | LIA | 2007-06-03 07:44:00+00
+40741775621 | LIA | 2007-06-03 07:44:00+00
+40741775621 | LIA | 2007-06-02 07:44:00+00
+40741775621 | LIA | 2007-06-02 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
+40741775621 | LIA | 2007-06-01 07:44:00+00
I have also a themes table:
uid | theme
-----+--------
6 | CRISTI
7 | LIA
And the table named reminder_services it is used to filter
users by theme from sent_messages table. This table looks like this:
uid | theme_uid | activity_mt_amount | activity_min_days | activity_max_months
-----+----------------+-----------------------------+---------------------------+----------------------------
5 | 6 | 3 | 6 | 1
6 | 7 | 4 | 5 | 1
The filtering should select users which sent at least activity_mt_amount messages
with theme_uid within activity_min_days consecutive days,
in the maximum activity_max_months months in the past.
Example:
The first row of the table reminder_services says that it should be selected
users which sent at least 3 messages with the theme_uid=6 (theme=CRISTI),
within 6 consecutive days, in the maximum 1 month in the past.
So, I created the following query:
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM ( SELECT COUNT(*),
sent_messages.theme,
receiver,
date_trunc('day',sent_messages.date) AS date,
ARRAY(SELECT date::date + s.a FROM generate_series(0,activity_min_days) AS s(a)) AS dates
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, receiver, date, activity_min_days ) A
INNER JOIN ( SELECT COUNT(*),
sent_messages.theme,
receiver,
date_trunc('day',sent_messages.date) AS date,
ARRAY(SELECT date::date + s.a FROM generate_series(0,activity_min_days) AS s(a)) AS dates
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, receiver, date, activity_min_days ) B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme, A.receiver, A.dates;
The result of this query is:
sum | theme | receiver | dates
-----+------------+----------------------+--------------------------------------------------------------------------------
8 | CRISTI | +40741775622 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRISTI | +40741775622 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRISTI | +40741775622 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRISTI | +40741775622 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 | {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA | +40741775621 | {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)
The red colored values 9 and 4 are not correct, should be 3 respectively 2.
Regards,
Loredana
I found my problem. I attaches an .sql file with my tables, data
and my new query. If you are interested you can take a look.
Thanks to all.
Regards,
Loredana
and my new query. If you are interested you can take a look.
Thanks to all.
Regards,
Loredana
I forgot the attachement :)
On 6/6/07, Loredana Curugiu <loredana.curugiu@gmail.com> wrote:
I found my problem. I attaches an .sql file with my tables, data
and my new query. If you are interested you can take a look.
Thanks to all.
Regards,
Loredana