Re: grouping subsets

Yes. This is somewhat more complicated because it has more constraints.
I've noticed that a given combination doesn't appear with holes on a certain 
day.

For ex, on a daily basis, we have every three key combinations together.

We dont have things like
2010-7-01  1726 3212 1428
2010-7-01  1726 3212 1428
...                318   1846 1012
2010-7-01  1726 3212 1428

Can I assume that, for a certain day , the records for the same three 
combination are all together? There is just one set per day for a given 
combination?

Or is it possible to have the same combination on one day with several sets?

Best,
Oliveiros


----- Original Message ----- 
From: "Rainer Stengele" <rainer.stengele@diplan.de>
Newsgroups: gmane.comp.db.postgresql.sql
To: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
Cc: <>
Sent: Thursday, July 29, 2010 10:41 AM
Subject: Re: grouping subsets


> Howdy Cristina,
>
> unfortunately things are more complicated. I have inserted an excerpt of 
> the real data here:
>
> ================================================================================
> TableID         MasterID dtBegin                           dtEnd 
> idR      idL      idB     consumption
> 4057312           295530 2010-07-01 00:59:21.077           2010-07-01 
> 01:32:59.670           1726     3212     1428     279
> 4061043           295574 2010-07-01 01:59:31.137           2010-07-01 
> 02:32:09.373           1726     3212     1428     183
> 4083397           295838 2010-07-01 07:57:51.327           2010-07-01 
> 08:28:28.117           318      1846     1012     30
> 4090858           295920 2010-07-01 09:52:33.777           2010-07-01 
> 10:31:34.393           318      1846     1012     487
> 4094589           295961 2010-07-01 10:47:59.370           2010-07-01 
> 11:32:20.903           318      1846     1012     472
> 4098330           296013 2010-07-01 11:58:53.890           2010-07-01 
> 12:31:35.730           318      1846     1012     195
> 4102069           296058 2010-07-01 12:36:19.170           2010-07-01 
> 13:32:13.950           318      1846     1012     338
> 4105809           296102 2010-07-01 13:58:53.170           2010-07-01 
> 14:02:57.710           318      1846     1012     105
> 4109555           296150 2010-07-01 14:59:11.663           2010-07-01 
> 15:32:33.810           318      1846     1012     187
> 4113305           296194 2010-07-01 15:59:01.797           2010-07-01 
> 16:02:27.260           318      1846     1012     108
> 4117048           296238 2010-07-01 16:20:47.997           2010-07-01 
> 17:32:49.367           318      1846     1012     179
> 4120791           296282 2010-07-01 17:58:27.657           2010-07-01 
> 18:29:01.733           318      1846     1012     256
> 4128291           296370 2010-07-01 19:54:17.687           2010-07-01 
> 20:32:53.850           318      1846     1012     239
> 4132044           296413 2010-07-01 20:31:37.653           2010-07-01 
> 21:29:13.497           318      1846     1012     39
> 4135797           296458 2010-07-01 21:59:13.983           2010-07-01 
> 22:32:46.503           318      1846     1012     157
> 4139572           296506 2010-07-01 22:58:49.530           2010-07-01 
> 23:32:22.543           318      1846     1012     218
> 4142941           296554 2010-07-01 23:59:13.857           2010-07-02 
> 00:32:30.390           318      1846     1012     248
> 4146289           296598 2010-07-02 00:58:55.763           2010-07-02 
> 01:32:41.983           318      1846     1012     204
> 4149616           296642 2010-07-02 01:46:57.357           2010-07-02 
> 02:32:56.983           318      1846     1012     42
> 4152952           296686 2010-07-02 02:55:19.653           2010-07-02 
> 03:32:28.013           318      1846     1012     135
> 4156289           296730 2010-07-02 03:43:52.777           2010-07-02 
> 04:32:55.250           318      1846     1012     743
> 4159624           296774 2010-07-02 04:43:15.310           2010-07-02 
> 05:32:44.547           318      1846     1012     277
> 4162961           296817 2010-07-02 05:58:59.483           2010-07-02 
> 06:32:37.340           318      1846     1012     121
> 4166303           296862 2010-07-02 06:58:50.733           2010-07-02 
> 07:32:39.113           318      1846     1012     239
> 4172981           296950 2010-07-02 07:28:55.293           2010-07-02 
> 09:33:01.200           318      1846     1012     512
> 4176322           296993 2010-07-02 09:59:04.607           2010-07-02 
> 10:33:01.903           318      1846     1012     139
> 4179667           297038 2010-07-02 10:55:27.760           2010-07-02 
> 11:32:56.560           318      1846     1012     722
> 4183012           297082 2010-07-02 11:59:33.650           2010-07-02 
> 12:32:14.700            318      1846     1012     163
> 4186351           297126 2010-07-02 12:23:45.997           2010-07-02 
> 13:32:59.500            318      1846     1012     284
> 4189689           297169 2010-07-02 13:44:21.253           2010-07-02 
> 14:18:05.080            318      1846     1012     254
> 4196371           297258 2010-07-02 16:16:19.123           2010-07-02 
> 16:32:53.437           1706     3541     1511     161
> 4199720           297301 2010-07-02 16:59:35.127           2010-07-02 
> 17:32:57.950           1706     3541     1511     250
> 4203068           297346 2010-07-02 17:59:34.027           2010-07-02 
> 18:32:54.337           1706     3541     1511     302
> 4206413           297389 2010-07-02 18:59:28.730           2010-07-02 
> 19:32:37.950           1706     3541     1511     276
> 4209758           297434 2010-07-02 19:54:00.243           2010-07-02 
> 20:32:57.433           1706     3541     1511     209
> 4213102           297473 2010-07-02 20:49:10.963           2010-07-02 
> 21:30:44.540           1706     3541     1511     76
> 4216447           297511 2010-07-02 21:59:34.810           2010-07-02 
> 22:33:00.603           1706     3541     1511     287
> 4219818           297569 2010-07-02 22:56:52.750           2010-07-02 
> 23:59:31.607           1706     3541     1511     1877
> 4219819           297570 2010-07-02 23:59:21.577           2010-07-03 
> 00:54:40.153           1706     3541     1511     1798
> 4219821           297572 2010-07-03 00:48:03.310           2010-07-03 
> 01:59:37.920           1706     3541     1511     1125
> 4219823           297574 2010-07-03 01:51:01.057           2010-07-03 
> 02:59:45.433           1706     3541     1511     1629
> 4219820           297571 2010-07-03 02:59:29.393           2010-07-03 
> 03:59:54.920           1706     3541     1511     2462
> 4219822           297573 2010-07-03 03:59:18.663           2010-07-03 
> 04:01:48.810           1706     3541     1511     70
> 4225738           297656 2010-07-03 06:13:34.980           2010-07-03 
> 06:28:09.697           1726     3212     1428     46
> 4228694           297695 2010-07-03 06:59:15.560           2010-07-03 
> 07:32:45.653           1726     3212     1428     251
> 4231649           297733 2010-07-03 07:59:11.937           2010-07-03 
> 08:32:57.217           1726     3212     1428     284
> 4234604           297771 2010-07-03 08:57:00.357           2010-07-03 
> 09:32:47.903           1726     3212     1428     227
> 4237559           297809 2010-07-03 09:59:19.813           2010-07-03 
> 10:33:02.063           1726     3212     1428     285
> 4261156           298596 2010-07-04 22:59:09.863           2010-07-04 
> 23:33:45.530           1726     3212     1428     1286
> 4264114           298646 2010-07-04 23:59:16.967           2010-07-05 
> 00:33:08.107           1726     3212     1428     297
> 4267067           298690 2010-07-05 00:59:15.187           2010-07-05 
> 01:32:48.300           1726     3212     1428     333
> 4270023           298734 2010-07-05 01:59:02.497           2010-07-05 
> 02:32:48.780           1726     3212     1428     270
> 4272977           298778 2010-07-05 02:41:43.737           2010-07-05 
> 03:32:56.043           1726     3212     1428     317
> 4275927           298822 2010-07-05 03:59:17.027           2010-07-05
> 04:33:14.947           1726     3212     1428     1623
> ================================================================================
>
> Description:
> 1. Column: some ID
> 2. Column: reference to another table
> 3. and 4. column: timestamp from/to of the item
> 5. Column: ID R
> 6. Column: ID L
> 7. Column: ID B
> 8. Column: Sum of components
>
> Requirement:
> Sum over all components (from column 8) for each combination of ID R, ID 
> L, ID B, but (!)
> rows with same keys (R,L,B) should be summed up only until the keys 
> change.
> Do not sum up the components for identical keys, if there are other keys 
> between them.
>
> Example result:
>
> idR       idL      idB                 SUM
> 1726     3212     1428                 462
> 318     1846     1012                 ...
> 1706     3541     1511                 ...
> 1726     3212     1428                 ...
>
>
> Note that the first and last entry here has the same keys
>
> Maybe you find a similar monster SQL solving such a requirement.
> Thanks for considering!
>
> Rainer
>
>
>
> Am 27.07.2010 12:37, schrieb Oliveiros d'Azevedo Cristina:
>> Howdy, Rainer.
>>
>> It's been a while, so I don't know if you are still interested in this 
>> problem or if you, in the meantime, found yourself a solution,
>> but I've tried this on a local copy of the example you provided   and it 
>> seems to work.
>>
>> The problem is that I suspect that if you have several thousands of 
>> records on your table it will become slow...
>>
>> Best,
>> Oliveiros
>>
>> SELECT SUM(tudo.parcela),tudo.a
>> FROM
>> (
>> SELECT fo.parcela,fo.a,fo.b,fo.c,MIN(th.c) as d
>> FROM
>> (
>> SELECT se.a as parcela,se.b as a,se.c as b, MAX(pr.c) as c
>> FROM
>> yourTable se
>> LEFT JOIN
>> (
>> SELECT a.*
>> FROM yourTable a
>> JOIN yourTable b
>> ON (b.b <> a.b)
>> AND ((age(a.c,b.c) = '1 day'::interval)
>>
>> )
>> ) pr
>> ON pr.b = se.b
>> AND    se.c >= pr.c
>> GROUP BY se.a,se.b,se.c
>> ) fo
>> LEFT JOIN
>> (
>> SELECT a.*
>> FROM yourTable a
>> JOIN yourTable b
>> ON (b.b <> a.b)
>> AND ((age(a.c,b.c) = '-1 day'::interval)
>> )
>> ) th
>> ON fo.a = th.b
>> AND fo.b <= th.c
>> GROUP BY fo.parcela,fo.a,fo.b,fo.c
>> ) tudo
>> GROUP BY tudo.a,tudo.c,tudo.d
>>
>>
>>
>>
>> To: <pgsql-sql@postgresql.org>
>> Sent: Thursday, July 22, 2010 9:09 AM
>> Subject: [SQL] grouping subsets
>>
>>
>>> Hi,
>>>
>>> having a table similar to
>>>
>>> | 1 | B | [2010-07-15 Do] |
>>> | 1 | B | [2010-07-16 Fr] |
>>> |---+---+-----------------|
>>> | 2 | C | [2010-07-17 Sa] |
>>> | 2 | C | [2010-07-18 So] |
>>> |---+---+-----------------|
>>> | 1 | B | [2010-07-19 Mo] |
>>> | 1 | B | [2010-07-20 Di] |
>>> | 1 | B | [2010-07-21 Mi] |
>>> | 1 | B | [2010-07-22 Do] |
>>> |---+---+-----------------|
>>> | 3 | D | [2010-07-23 Fr] |
>>>
>>> a simple group by gives me:
>>>
>>> | 6 | B |
>>> | 4 | C |
>>> | 3 | D |
>>>
>>>
>>> What I want to get is the values grouped by "subset", where a subset is 
>>> a set of rows with identical column until the colum changes.
>>> Is there a way to get
>>>
>>> | 2 | B |
>>> | 4 | C |
>>> | 4 | B |
>>> | 3 | D |
>>>
>>> by SQL only?
>>>
>>> - Rainer
>>>
>>>
>>>
>>>
>>>
>>> -- 
>>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>>