pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Join
22+ messages / 8 participants
[nested] [flat]
* Re: Join
@ 2000-09-07 14:03 Kristofer Munn <kmunn@munn.com>
parent: Craig May <craig.may@s2.enthdimension.com.au>
0 siblings, 0 replies; 22+ messages in thread
From: Kristofer Munn @ 2000-09-07 14:03 UTC (permalink / raw)
To: Craig May <craig.may@s2.enthdimension.com.au>; +Cc: pgsql-sql
> I'm having trouble with this statement, could someone help out please.
>
> Select g.ID From Grouping g join Entity_Grouping eg on
> g.ID=eg.Group_ID where eg.Entity_ID=1
Try
select g.ID from Grouping g, Entity_Grouping eg where g.ID = eg.Group_ID
and eg.Entity_ID = 1;
That's not the join syntax you were using but that should do the trick.
- K
Kristofer Munn * KMI * 732-254-9305 * AIM KrMunn * http://www.munn.com/
^ permalink raw reply [nested|flat] 22+ messages in thread
* Join
@ 2000-09-08 00:59 Craig May <craig.may@s2.enthdimension.com.au>
0 siblings, 1 reply; 22+ messages in thread
From: Craig May @ 2000-09-08 00:59 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm having trouble with this statement, could someone help out please.
Select g.ID From Grouping g join Entity_Grouping eg on g.ID=eg.Group_ID where
eg.Entity_ID=1
The error message is:
ERROR: transfromExpr: does not know hot to transfrom node 501 (internal error)
Craig May
Enth Dimension
http://www.enthdimension.com.au/
^ permalink raw reply [nested|flat] 22+ messages in thread
* JOIN
@ 2007-06-05 08:11 Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 08:11 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN
@ 2007-06-05 11:26 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 2 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 11:26 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN
@ 2007-06-05 12:13 Richard Huxton <dev@archonet.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Richard Huxton @ 2007-06-05 12:13 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
Loredana Curugiu wrote:
> Any help, please?
>> 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?
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.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 12:18 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 12:18 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-novice@postgresql.org; pgsql-sql
>> 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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN
@ 2007-06-05 12:39 Richard Huxton <dev@archonet.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Richard Huxton @ 2007-06-05 12:39 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
Loredana Curugiu wrote:
>>> 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.
But you're not saying what you think the figure(s) should be.
There's two possibilities:
1. SUM() has a bug and can't add up numbers. Unlikely
2. The INNER JOIN isn't working as you think it should. This seems more
likely.
Try the query without totalling, just to see what matches you're getting.
> 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 .
You're happy that daylight-saving adjustments aren't causing any
problems with your timezone settings then?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN
@ 2007-06-05 12:45 Gregory Stark <stark@enterprisedb.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Gregory Stark @ 2007-06-05 12:45 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-novice@postgresql.org; pgsql-sql
"Loredana Curugiu" <loredana.curugiu@gmail.com> writes:
> I am trying to say that sum column it is not calculated correctly.
It looks like your counts are high. That would imply that your join clauses
are matching more than one combination of rows. Ie, it's counting some rows
multiple times because there are multiple join matches.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 12:59 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 12:59 UTC (permalink / raw)
To: dev@archonet.com; pgsql-novice@postgresql.org; pgsql-sql
>> 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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 13:19 Oliveiros Cristina <oliveiros.cristina@gmail.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Oliveiros Cristina @ 2007-06-05 13:19 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
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!
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN
@ 2007-06-05 13:21 Richard Huxton <dev@archonet.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Richard Huxton @ 2007-06-05 13:21 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
Loredana Curugiu wrote:
>>> You don't actually say what's wrong. What are you expecting as output?
>
> I should obtain the following result:
[snip]
Well, I've attached a test script using your example data and a copy of
my results. Nothing leaping out as wrong here. It's entirely possible
I've not had enough coffee today though and I'm missing something
staring me in the face...
--
Richard Huxton
Archonet Ltd
Attachments:
test_results.txt (text/plain, 6.7K) download
sum | theme | receiver | date | dates
-----+-------+--------------+------------------------+--------------------------------------------------------------------------------
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}
2 | 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}
3 | 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}
18 | 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}
4 | 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}
6 | 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}
10 | 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}
(8 rows)
count | theme | receiver | date | dates
-------+-------+--------------+------------------------+--------------------------------------------------------------------------------
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-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 | CRIS | +40741775622 | 2007-06-02 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-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-03 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-03 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 | 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-04 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 | CRIS | +40741775622 | 2007-06-04 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}
1 | CRIS | +40741775622 | 2007-06-04 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}
1 | CRIS | +40741775622 | 2007-06-04 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 | CRIS | +40741775622 | 2007-06-04 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}
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-04 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}
1 | CRIS | +40741775622 | 2007-06-04 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 | CRIS | +40741775622 | 2007-06-04 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}
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-04 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}
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 | {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
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}
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
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}
2 | LIA | +40741775621 | 2007-06-03 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-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 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {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-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}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {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}
1 | LIA | +40741775621 | 2007-06-04 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 | LIA | +40741775621 | 2007-06-04 00:00:00+00 | {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
(40 rows)
zzz_test.sql (text/x-sql, 2.1K) download
/*
CREATE TABLE loredana_test (
count int4,
theme text,
receiver text,
"date" timestamptz,
dates date[]
);
COPY loredana_test FROM stdin WITH DELIMITER '|';
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}
\.
*/
-- Uncomment one of these to control whether any rows match
SET timezone = 'GMT';
-- SET timezone = 'GB';
-- Original query, but with the "date" column displayed too
SELECT
SUM(A.count),
A.theme,
A.receiver,
A.date,
A.dates
FROM
loredana_test A
INNER JOIN
loredana_test B
ON
A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = ANY(B.dates)
GROUP BY
A.theme,A.receiver,A.date,A.dates;
-- Query to test matches
SELECT
A.count,
A.theme,
A.receiver,
A.date,
B.dates
FROM
loredana_test A
INNER JOIN
loredana_test B
ON
A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = ANY(B.dates)
ORDER BY
A.theme, A.receiver, A.date
;
* Re: JOIN
@ 2007-06-05 13:42 Sean Davis <sdavis2@mail.nih.gov>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Sean Davis @ 2007-06-05 13:42 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
Loredana Curugiu 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.
Loredana,
It is great to see your determination to get the answer, but we still do
not know what is "wrong" with the query result. You will need to
explain what you think is wrong before anyone can help. The output
looks like it matches the query perfectly.
Sean
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 14:08 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Oliveiros Cristina <oliveiros.cristina@gmail.com>
0 siblings, 2 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 14:08 UTC (permalink / raw)
To: oliveiros.cristina@gmail.com; pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 14:15 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 2 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 14:15 UTC (permalink / raw)
To: oliveiros.cristina@gmail.com; pgsql-novice@postgresql.org; pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 14:30 Oliveiros Cristina <oliveiros.cristina@gmail.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Oliveiros Cristina @ 2007-06-05 14:30 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
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!
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 14:46 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Oliveiros Cristina <oliveiros.cristina@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 14:46 UTC (permalink / raw)
To: Oliveiros Cristina <oliveiros.cristina@gmail.com>; pgsql-novice@postgresql.org; pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 14:52 Oliveiros Cristina <oliveiros.cristina@marktest.pt>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Oliveiros Cristina @ 2007-06-05 14:52 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; oliveiros.cristina@gmail.com; pgsql-novice@postgresql.org; pgsql-sql
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 Curugiu
To: oliveiros.cristina@gmail.com ; pgsql-novice@postgresql.org ; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 14:57 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-05 14:57 UTC (permalink / raw)
To: Oliveiros Cristina <oliveiros.cristina@marktest.pt>; pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-05 15:00 Oliveiros Cristina <oliveiros.cristina@marktest.pt>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Oliveiros Cristina @ 2007-06-05 15:00 UTC (permalink / raw)
To: Loredana Curugiu <loredana.curugiu@gmail.com>; Oliveiros Cristina <oliveiros.cristina@gmail.com>; pgsql-novice@postgresql.org; pgsql-sql
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
From: Loredana Curugiu
To: Oliveiros Cristina ; pgsql-novice@postgresql.org ; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: [SQL] JOIN
@ 2007-06-06 08:26 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-06 08:26 UTC (permalink / raw)
To: Oliveiros Cristina <oliveiros.cristina@marktest.pt>; pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN
@ 2007-06-06 12:46 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-06 12:46 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: JOIN
@ 2007-06-06 12:47 Loredana Curugiu <loredana.curugiu@gmail.com>
parent: Loredana Curugiu <loredana.curugiu@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Loredana Curugiu @ 2007-06-06 12:47 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
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
>
Attachments:
file.sql (text/x-sql, 3.4K) download
CREATE TABLE reminder_services (
uid SERIAL,
theme_uid INT NOT NULL,
activity_MT_amount INT NOT NULL,
activity_min_days INT NOT NULL,
activity_max_months INT NOT NULL,
CONSTRAINT pk_reminder_services PRIMARY KEY(uid),
CONSTRAINT un_reminder_services_theme UNIQUE(theme_uid) );
CREATE TABLE themes (
uid SERIAL PRIMARY KEY,
theme VARCHAR(25) UNIQUE NOT NULL );
CREATE TABLE sent_messages (
uid SERIAL PRIMARY KEY,
receiver VARCHAR(25),
theme VARCHAR(25),
date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP );
COPY reminder_services FROM stdin WITH DELIMITER '|';
5 | 6 | 3 | 6 | 1
6 | 7 | 4 | 5 | 1
\.
COPY themes FROM stdin WITH DELIMITER '|';
6 | CRISTI
7 | LIA
\.
COPY sent_messages FROM stdin WITH DELIMITER '|';
62 | +40741775622 | CRISTI | 2007-06-04 07:44:45.406271+00
70 | +40741775622 | CRISTI | 2007-06-04 07:45:01.788533+00
71 | +40741775622 | CRISTI | 2007-06-04 07:45:03.764506+00
80 | +40741775621 | LIA | 2007-06-04 07:45:26.309215+00
81 | +40741775621 | LIA | 2007-06-04 07:45:28.314075+00
63 | +40741775622 | CRISTI | 2007-06-03 07:44:00+00
64 | +40741775622 | CRISTI | 2007-06-02 07:44:00+00
65 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
66 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
67 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
68 | +40741775622 | CRISTI | 2007-04-01 07:44:00+00
69 | +40741775622 | CRISTI | 2007-05-01 07:44:00+00
72 | +40741775621 | LIA | 2007-06-03 07:44:00+00
73 | +40741775621 | LIA | 2007-06-03 07:44:00+00
74 | +40741775621 | LIA | 2007-06-02 07:44:00+00
75 | +40741775621 | LIA | 2007-06-02 07:44:00+00
76 | +40741775621 | LIA | 2007-06-01 07:44:00+00
77 | +40741775621 | LIA | 2007-06-01 07:44:00+00
78 | +40741775621 | LIA | 2007-06-01 07:44:00+00
79 | +40741775621 | LIA | 2007-06-01 07:44:00+00
\.
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM ( SELECT DISTINCT COUNT(*),
sent_messages.theme,
receiver,
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 TRIM(sent_messages.theme)=TRIM(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
FROM reminder_services, themes,sent_messages
WHERE themes.uid=reminder_services.theme_uid
AND TRIM(sent_messages.theme)=TRIM(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;
end of thread, other threads:[~2007-06-06 12:47 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-08 00:59 Join Craig May <craig.may@s2.enthdimension.com.au>
2000-09-07 14:03 ` Kristofer Munn <kmunn@munn.com>
2007-06-05 08:11 JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 11:26 ` Re: JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 12:13 ` Re: JOIN Richard Huxton <dev@archonet.com>
2007-06-05 12:18 ` Re: [SQL] JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 12:39 ` Re: JOIN Richard Huxton <dev@archonet.com>
2007-06-05 12:59 ` Re: [SQL] JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 13:21 ` Re: JOIN Richard Huxton <dev@archonet.com>
2007-06-05 12:45 ` Re: JOIN Gregory Stark <stark@enterprisedb.com>
2007-06-05 13:19 ` Re: [SQL] JOIN Oliveiros Cristina <oliveiros.cristina@gmail.com>
2007-06-05 14:08 ` Re: [SQL] JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 14:15 ` Re: [SQL] JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 14:30 ` Re: [SQL] JOIN Oliveiros Cristina <oliveiros.cristina@gmail.com>
2007-06-05 14:46 ` Re: [SQL] JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 15:00 ` Re: [SQL] JOIN Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2007-06-06 08:26 ` Re: [SQL] JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 14:52 ` Re: [SQL] JOIN Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2007-06-05 14:57 ` Re: [SQL] JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-06 12:46 ` Re: JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-06 12:47 ` Re: JOIN Loredana Curugiu <loredana.curugiu@gmail.com>
2007-06-05 13:42 ` Re: JOIN Sean Davis <sdavis2@mail.nih.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox