Re: Particular GROUP BY sum requiring analytic function(s) -I guess...-

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 30 Apr 2007 16:28:48 +0200
Message-ID: <4635FD1F.8070907@arcor.de>

Sébastien de Mapias schrieb:
> Hi,
> Let's say I have the following table B containing
>
> SQL> select * from b order by bl_id;
>
> NAME BYTES BLOCKS FILEID BL_ID
> -------------------- ---------- ---------- ---------- ----------
> SEGMENT_NAME 8388608 512 100 1
> SEGMENT_NAME 8388608 512 100 2
> SEGMENT_NAME 8388608 512 100 3
> SEGMENT_NAME 8388608 512 100 4
> SEGMENT_NAME 8388608 512 100 5
> SEGMENT_NAME 8388608 512 100 6
> SEGMENT_NAME 8388608 512 100 7
> SEGMENT_NAME 8388608 512 100 8
> SEGMENT_NAME 8388608 512 100 9
> SEGMENT_NAME 8388608 512 100 10
> SEGMENT_NAME 8388608 512 100 11
> SEGMENT_NAME 8388608 512 100 12
> SEGMENT_NAME 8388608 512 100 13
> SEGMENT_NAME 8388608 512 100 14
> SEGMENT_NAME 8388608 512 100 15
> SEGMENT_NAME 8388608 512 100 16
> SEGMENT_NAME 8388608 512 100 17
> SEGMENT_NAME 8388608 512 100 18
> SEGMENT_NAME 67108864 4096 100 19
> SEGMENT_NAME 67108864 4096 100 20
> SEGMENT_NAME 67108864 4096 100 21
> SEGMENT_NAME 67108864 4096 100 22
> free 63963136 3904 100 23
> free 119537664 7296 100 24
> SEGMENT_NAME 1048576 64 100 25
> SEGMENT_NAME 1048576 64 100 26
> SEGMENT_NAME 1048576 64 100 27
> free 3145728 192 100 28
>
> 28 rows selected.
>
> I'd like to get the following output:
> SQL> select name, sum(bytes) from b
> 2 group by ??? ;
> NAME SUM(BYTES)
> -------------------- ----------
> SEGMENT_NAME 419430400
> free 183500800
> SEGMENT_NAME 3145728
> free 3145728
>
> As you can see I've grouped by my figures by NAME *but* I've
> kept the order of the BL_ID figures: as long as I find the same
> NAME I sum up BYTES, then I encounter another value for
> NAME: 'free', so I start summing up BYTES values again for
> this new value of NAME as long as it remains 'free', then again
> 3 times 'SEGMENT_NAME' => I display the sum of BYTES for
> the 3 adjacent rows, and finally the ultimate sole value for 'free',
> 3145728.
> Thus the ORDER BY BL_ID is *extremely* important (to get
> contiguous pieces of occupied/free space).
>
> I couldn't manage to find the SQL that'll give me this output,
> I guess analytic functions should allow for an easy and
> elegant way of getting it: who could please help me ?
>
> Thanks a lot in advance.
> SR
>

WITH step1 AS (
  select
   decode(lag(NAME)

          over(ORDER BY  bl_id),
          NAME,
          0,
          1) start_of_group,

   b.*
  from b),
step2 AS (
  SELECT
   SUM(start_of_group)
   over(ORDER BY bl_id) group_no,
   step1.*
  FROM step1)
SELECT NAME,SUM(bytes)
FROM step2
GROUP BY group_no,NAME
/

Best regards

Maxim Received on Mon Apr 30 2007 - 09:28:48 CDT