Saturday, December 30, 2006

The mighty MODEL or poor-man's bitwise aggregates

Hi, this one is nice and interesting I believe. Oracle does not have bitwise aggregate functions which are very useful for specific needs, so the script below illustrates how one can use Oracel MODEL clause to build custom aggregates. It's probably not an ideal solution and requires some polishing but it supposed to work :-)

SQL> COLUMN s_or  FORMAT A15
SQL> COLUMN s_xor FORMAT A15
SQL> COLUMN s_and FORMAT A15
SQL> COLUMN l FORMAT 99
SQL> COLUMN bit_or FORMAT 999999
SQL> COLUMN bit_xor FORMAT 999999
SQL> COLUMN bit_and FORMAT 999999
SQL> SET PAGES 1000 LINES 1000
SQL> WITH
  2   tbl AS (
  3     SELECT 1 l, 1 v FROM dual
  4     UNION ALL
  5     SELECT 1, 3 FROM dual
  6     UNION ALL
  7     SELECT 2, 6 FROM dual
  8     UNION ALL
  9     SELECT 2, 1 FROM dual
 10     UNION ALL
 11     SELECT 3, 0 FROM dual
 12     UNION ALL
 13     SELECT 4, 8 FROM dual
 14     UNION ALL
 15     SELECT 4, 16 FROM dual
 16     UNION ALL
 17     SELECT 5, 7 FROM dual
 18     UNION ALL
 19     SELECT 5, 5 FROM dual
 20     UNION ALL
 21     SELECT 6, 1 FROM dual
 22     UNION ALL
 23     SELECT 6, 3 FROM dual
 24     UNION ALL
 25     SELECT 6, 7 FROM dual
 26     UNION ALL
 27     SELECT 6, 15 FROM dual
 28  )
 29    SELECT l
 30         , str s_or
 31         , bit_or
 32         , REPLACE(str, '|', '^') s_xor
 33         , bit_xor
 34         , REPLACE(str, '|', '&') s_and
 35         , bit_and
 36      FROM (
 37           SELECT l
 38                , bit_or
 39                , bit_xor
 40                , bit_and
 41                , str
 42                , i
 43             FROM tbl
 44            MODEL
 45              PARTITION BY (l)
 46              DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY l ORDER BY l) AS s)
 47              MEASURES (v, 0 AS bit_and, 0 AS bit_or, 0 bit_xor, 0 AS i, CAST('' AS VARCHAR2(30)) AS str)
 48              IGNORE NAV
 49              RULES UPSERT ITERATE(4294967295)
 50              UNTIL (PRESENTV(v[ITERATION_NUMBER + 1], 1, 0) = 0)
 51              (
 52                -- AND: BITAND(x, y)
 53                bit_and[0] = BITAND(
 54                               v[ITERATION_NUMBER]
 55                             , CASE
 56                                 WHEN ITERATION_NUMBER = 1
 57                                 THEN POWER(2, 63)
 58                                 ELSE bit_and[0]
 59                               END
 60                             )
 61                -- OR: x + y - BITAND(x, y)
 62              , bit_or[0] =
 63                  bit_or[0]
 64                + v[ITERATION_NUMBER]
 65                - BITAND(bit_or[0], v[ITERATION_NUMBER])
 66                -- XOR: x + y - 2 * BITAND(x, y)
 67              , bit_xor[0] =
 68                  bit_xor[0]
 69                + v[ITERATION_NUMBER]
 70                - 2
 71                * BITAND(bit_xor[0], v[ITERATION_NUMBER])
 72                -- indicator
 73              , i[0] = ITERATION_NUMBER
 74                -- contcatenated string with bitwise operators
 75              , str[0] = CASE
 76                          WHEN ITERATION_NUMBER = 1
 77                          THEN TO_CHAR(v[ITERATION_NUMBER], 'FM99999')
 78                          ELSE str[0] || ' | ' || TO_CHAR(v[ITERATION_NUMBER], 'FM99999')
 79                         END
 80              )
 81           )
 82     WHERE i > 0
 83     ORDER BY l
 84  /

  L S_OR             BIT_OR S_XOR           BIT_XOR S_AND           BIT_AND
--- --------------- ------- --------------- ------- --------------- -------
  1 1 | 3                 3 1 ^ 3                 2 1 & 3                 1
  2 6 | 1                 7 6 ^ 1                 7 6 & 1                 0
  3 0                     0 0                     0 0                     0
  4 8 | 16               24 8 ^ 16               24 8 & 16                0
  5 7 | 5                 7 7 ^ 5                 2 7 & 5                 5
  6 1 | 3 | 7 | 15       15 1 ^ 3 ^ 7 ^ 15       10 1 & 3 & 7 & 15        1

6 rows selected.


One can use RETURN UPDATED ROWS and get rid of i[], if needed and UNIQUE SINGLE REFERENCE to speed up data processing.

Update 02/19/2007:

SELECT l, bit_and, bit_or, bit_xor
  FROM (
       SELECT l, r, c, bit_and, bit_or, bit_xor
         FROM tbl
        MODEL
          PARTITION BY (l)
          DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY l ORDER BY l) AS r)
          MEASURES (v, COUNT(*) OVER (PARTITION BY l) AS c, 0 AS bit_and, 0 AS bit_xor, 0 AS bit_or)
          IGNORE NAV
          RULES (
            -- BITAND(x, y)
            bit_and[ANY] ORDER BY r =
              BITAND(
                v[CV()]
              , CASE
                  WHEN CV(r) = 1
                  THEN POWER(2, 63)
                  ELSE bit_and[CV() - 1]
                END
              )
            -- x + y - BITAND(x, y)
          , bit_or[ANY] ORDER BY r =
              bit_or[CV() - 1]
            + v[CV()]
            - BITAND(bit_or[CV() - 1], v[CV()])
            -- x + y - 2 * BITAND(x, y)
          , bit_xor[ANY] ORDER BY r =
                bit_xor[CV() - 1]
              + v[CV()]
              - 2
              * BITAND(bit_xor[CV() - 1], v[CV()])
          )
       )
 WHERE c = r

No comments: