CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
Seb
Seb

Reputation: 33

Oracle SQL: Sum over columns with PARTITION BY but keep same order and exclude sorting column

From this table:

FILE_ID SEG_NAME       ROOT_BLID      BYTES  
------- ------------- ---------- ----------
    265 *                    128    1048576
    265 *                    256    1048576
    265 *                    384    1048576
    265 *                    512    1048576
    265 *                    640    1048576
    265 *                    768    1048576
    265 *                    896    1048576
    265 *                   2048    1048576
    265 *                   2432    1048576
    265 *                   9856    1048576
    265 free                9984    2097152
    265 *                  10240  134217728
    265 *                  26624    8388608
    265 *                  27648    8388608
    265 free               29184    1048576
    265 *                  29312    1048576
    265 *                  29440    1048576
    265 *                  29568    1048576
    265 SEGMENT_NAME       29696    2097152
    265 free               29952    2097152
    265 *                  30208    8388608
    265 *                  31232    8388608
    265 SEGMENT_NAME       32256    8388608
    265 *                  33280    8388608
    265 SEGMENT_NAME       34304 2634022912
    265 SEGMENT_NAME      355840    8388608
    265 SEGMENT_NAME      356864 1238368256
    265 free              508032    3145728
    265 SEGMENT_NAME      508416  805306368
    265 SEGMENT_NAME      606720    8388608
    265 SEGMENT_NAME      607744 2281701376
    265 SEGMENT_NAME      886272    8388608
    265 SEGMENT_NAME      887296 1053818880
    265 *                1015936    1048576
    265 *                1016064    1048576
    265 free             1016192    1048576
    265 SEGMENT_NAME     1016320  134217728
    265 *                1032704    8388608
    265 *                1033728    8388608
    265 *                1034752   67108864
    265 *                1042944    8388608
    265 *                1043968   67108864
    265 *                1052160    8388608
    265 free             1053184 2378170368

61 rows selected.

I would like to get the following:

FILE_ID SEG_NAME           BYTES
------- ------------- ----------
    265 *               10485760
    265 free             2097152
    265 *              150994944
    265 free             1048576
    265 *                3145728
    265 SEGMENT_NAME     2097152
    265 free             2097152
    265 *               16777216
    265 SEGMENT_NAME     8388608
    265 *                8388608
    265 SEGMENT_NAME  3880779776
    265 free             3145728
    265 SEGMENT_NAME  4157603840
    265 *                2097152
    265 free             1048576
    265 SEGMENT_NAME   134217728
    265 *              167772160
    265 free          2378170368

18 rows selected.

i.e. display a sum of BYTES grouped by FILE_ID and SEG_NAME, but keep same order as ROOT_BLID (which must remain in the background and not be displayed)... So far I didn't manage to find a solution.

Someone can help please?
Thanks!
Seb

Upvotes: 0

Views: 90

Answers (1)

MT0
MT0

Reputation: 168480

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY file_id
  ORDER     BY root_blid
  MEASURES
    FIRST(seg_name) AS seg_name,
    SUM(bytes) AS bytes
  PATTERN ( same_seg_name+ )
  DEFINE
    same_seg_name AS seg_name = FIRST(seg_name)
);

Which, for the sample data:

CREATE TABLE table_name(FILE_ID, SEG_NAME, ROOT_BLID, BYTES) AS
SELECT 265, '*',                 128,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                 256,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                 384,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                 512,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                 640,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                 768,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                 896,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                2048,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                2432,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',                9856,    1048576 FROM DUAL UNION ALL
SELECT 265, 'free',             9984,    2097152 FROM DUAL UNION ALL
SELECT 265, '*',               10240,  134217728 FROM DUAL UNION ALL
SELECT 265, '*',               26624,    8388608 FROM DUAL UNION ALL
SELECT 265, '*',               27648,    8388608 FROM DUAL UNION ALL
SELECT 265, 'free',            29184,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',               29312,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',               29440,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',               29568,    1048576 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',    29696,    2097152 FROM DUAL UNION ALL
SELECT 265, 'free',            29952,    2097152 FROM DUAL UNION ALL
SELECT 265, '*',               30208,    8388608 FROM DUAL UNION ALL
SELECT 265, '*',               31232,    8388608 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',    32256,    8388608 FROM DUAL UNION ALL
SELECT 265, '*',               33280,    8388608 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',    34304, 2634022912 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',   355840,    8388608 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',   356864, 1238368256 FROM DUAL UNION ALL
SELECT 265, 'free',           508032,    3145728 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',   508416,  805306368 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',   606720,    8388608 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',   607744, 2281701376 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',   886272,    8388608 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',   887296, 1053818880 FROM DUAL UNION ALL
SELECT 265, '*',             1015936,    1048576 FROM DUAL UNION ALL
SELECT 265, '*',             1016064,    1048576 FROM DUAL UNION ALL
SELECT 265, 'free',          1016192,    1048576 FROM DUAL UNION ALL
SELECT 265, 'SEGMENT_NAME',  1016320,  134217728 FROM DUAL UNION ALL
SELECT 265, '*',             1032704,    8388608 FROM DUAL UNION ALL
SELECT 265, '*',             1033728,    8388608 FROM DUAL UNION ALL
SELECT 265, '*',             1034752,   67108864 FROM DUAL UNION ALL
SELECT 265, '*',             1042944,    8388608 FROM DUAL UNION ALL
SELECT 265, '*',             1043968,   67108864 FROM DUAL UNION ALL
SELECT 265, '*',             1052160,    8388608 FROM DUAL UNION ALL
SELECT 265, 'free',          1053184, 2378170368 FROM DUAL;

Outputs:

FILE_ID SEG_NAME BYTES
265 * 10485760
265 free 2097152
265 * 150994944
265 free 1048576
265 * 3145728
265 SEGMENT_NAME 2097152
265 free 2097152
265 * 16777216
265 SEGMENT_NAME 8388608
265 * 8388608
265 SEGMENT_NAME 3880779776
265 free 3145728
265 SEGMENT_NAME 4157603840
265 * 2097152
265 free 1048576
265 SEGMENT_NAME 134217728
265 * 167772160
265 free 2378170368

fiddle

Upvotes: 2

Related Questions