Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: JOIN

Re: JOIN

From
"Loredana Curugiu"
Date:
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




Re: [SQL] JOIN

From
"Loredana Curugiu"
Date:

>> 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

Re: [SQL] JOIN

From
"Loredana Curugiu"
Date:


>> 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)

 

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.

Regards,
      Loredana

Re: [SQL] JOIN

From
"Oliveiros Cristina"
Date:
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

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!

Re: [SQL] JOIN

From
"Loredana Curugiu"
Date:


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 ?

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} |


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,
         Loredana

Re: [SQL] JOIN

From
"Loredana Curugiu"
Date:


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 ?

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} |


 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.
 

The same problem it is at the eigth row. The sum should be 2.

Best regards,
         Loredana


Re: [SQL] JOIN

From
"Oliveiros Cristina"
Date:
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

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 ?

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} |


 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.
 

The same problem it is at the eigth row. The sum should be 2.

Best regards,
         Loredana





--
O Quê? SQL Server 2005 Express Edition? for free?  easy-to-use??  lightweight???  and embeddable???  Isso deve ser uma fortuna, homem!

Re: [SQL] JOIN

From
"Loredana Curugiu"
Date:
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

Re: [SQL] JOIN

From
"Oliveiros Cristina"
Date:
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 -----
Sent: Tuesday, June 05, 2007 3:15 PM
Subject: Re: [SQL] JOIN



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 ?

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} |


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.
 

The same problem it is at the eigth row. The sum should be 2.

Best regards,
         Loredana


Re: [SQL] JOIN

From
"Loredana Curugiu"
Date:


On 6/5/07, Oliveiros Cristina <oliveiros.cristina@marktest.pt> wrote:
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

if the first column is count then the sum(count) should be 4.


Loredana

Re: [SQL] JOIN

From
"Oliveiros Cristina"
Date:
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;
 
 
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
 
Sent: Tuesday, June 05, 2007 3:46 PM
Subject: Re: [SQL] JOIN

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

Re: [SQL] JOIN

From
"Loredana Curugiu"
Date:
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

Re: JOIN

From
"Loredana Curugiu"
Date:
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

Re: JOIN

From
"Loredana Curugiu"
Date:
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

Attachment