Re: Help by query

2011/2/1 Oliveiros d'Azevedo Cristina <oliveiros(dot)cristina(at)marktest(dot)pt>

> Howdy,
> Can you explain a little better the background of the problem you're trying
> to solve as it is not clear for me?
> Where did the line with id=6 come from?
> And what's the role of the "example number" variable you've provided ? Is
> it the threshold? What happens if a group surpasses it ?
>
> Best,
> Oliveiros
>
> ----- Original Message -----
> *From:* killspam killspam <01killspam(at)gmail(dot)com>
> *To:* pgsql-novice(at)postgresql(dot)org
> *Sent:* Tuesday, February 01, 2011 10:14 AM
> *Subject:* [NOVICE] Help by query
>
> Hello list.
>
> I have the following problem:
>
> Example table Origin
>
> *id | length | source | target*
>
> 1 25 33 45
> 2 100 45 60
> 3 12 22 11
> 4 78 60 55
> 5 20 24 60
>
> Example Results: new table
>
> *id | length | source | target | group | total length*
>
> 1 25 33 *45* 1 145
> 2 100 *45* *70* 1 145
> 3 12 22 11 3 12
> 4 78 * 60* 55 2 98
> 5 20 24 * 60* 2 98
> 6 20 30 *70* 1 145
>
> Example Number variable >= 150
>
> and I want to create groups of elements on two conditions.
>
> source or target must match, since this indicates that touch
>
> to group items with the first condition and that the sum of its length does
> not exceed a given number
>
> not be repeated elements in groups (The items can not be processed and
> reprocessed)
>
> Can someone help?
>
> Thanks in advance
>
> regards
> Mario
>
> Hi
Obrigado Oliveiros.

Sorry for the bad explanation.

Where did the line with id=6 come from?
The element id= 6 should be in the 2 examples.

Example table Origin

*id | length | source | target*

1 25 33 45
2 100 45 60
3 12 22 11
4 78 60 55
5 20 24 60
6 20 30 70

Example Results: new table

*id | length | source | target | group | total length*

1 25 33 *45* 1 145
2 100 *45* *70* 1 145
3 12 22 11 3 12
4 78 * 60* 55 2 98
5 20 24 * 60* 2 98
6 20 30 *70* 1 145

It is only 1 table with n elements.

And what's the role of the "example number" variable you've provided ? Is it
the threshold? What happens if a group surpasses it ?
- The number provided is the limit for the total of the sums of longitude
that accumulate to create each group, if it exceeds the next group is
created.

Explanation. :-)

1 condition: matching source or target.

2 condition: the sum of the lengths of the elements to be accumulated by the
1 st condition
can not exceed the number that would limit "the divisor" and then
when it comes next most the value of the number group closes.
and start a new one. So until there are no more records.

Each element can only belong to a group.

In a perfect world : Sum total length(all elements) / divider(limit) =
number of groups
Example: 10.000 /
1000 10

Thanks in advance.
regards

Mario.