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
Saturday, December 30, 2006
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment