Laurent Schneider

OR aggregate

you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR


1010 (10)
1100 (12)
0110 (6)
=========
1110 (14)

Let’s try


with t as (
  select 10 n from dual union all 
  select 12 from dual union all 
  select 6 from dual) 
select 
  utl_raw.cast_to_binary_integer(
    sys.mvaggrawbitor(
      utl_raw.cast_from_binary_integer(
        n
      )
    )
  ) N 
from t;
  N
---
 14

It is that easy !

disclaimer: mvaggrawbitor is not documented

This entry was posted on Tuesday, November 20th, 2007 at 16:12 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

16 Responses to “OR aggregate”

Don Burleson Says:
November 20th, 2007 at 16:47
Hi Laurent,

>> It is that easy !

Ha! Maybe easy for you!

This is very impressive and elegent solution to a complex problem! You are a worthy scholar indeed . . . .
Chen Shapira Says:
November 20th, 2007 at 22:19
Hi Laurent,

Any idea why Oracle has mvaggrawbitor but no aggregated “and” and “not” operators? (At least on 10.2.0.2)
If they only have enough space for just one aggregating binary operator, XOR would be a better option.
Laurent Schneider Says:
November 20th, 2007 at 22:32
according to the name, it seems to be an internal function used for materialized views
Rob van Wijk Says:
November 22nd, 2007 at 17:48
or:

SQL> with t as
2 ( select *
3 from mytable
4 model
5 dimension by (row_number() over (order by null) rn)
6 measures (n)
7 rules
8 ( n[any] order by rn desc = nvl(n[cv()+1],0) + n[cv()] - bitand(nvl(n[cv()+1],0),n[cv()])
9 )
10 )
11 select n
12 from t
13 where rn = 1
14 /

N
———-
14

Laurent Schneider Says:
November 23rd, 2007 at 11:06

the same without subquery


select n
from mytable
model
return updated rows
dimension by (row_number() over (order by null) rn)
measures (n)
rules iterate (10000)
until (presentv(n[ITERATION_NUMBER+3],1,2)=2)
( n[1] = n[1] + nvl(n[ITERATION_NUMBER+2],0) -
  bitand(n[1],nvl(n[ITERATION_NUMBER+2],0)))
/

Rob van Wijk Says:
November 23rd, 2007 at 17:37
That’s even better.
And doing some final (?) little optimizations:

SQL> select n
2 from mytable
3 model
4 return updated rows
5 dimension by (rownum r)
6 measures (n)
7 rules iterate (10000) until n[iteration_number+3] is null
8 ( n[1] = n[1] + n[iteration_number+2] - bitand(n[1],n[iteration_number+2])
9 )
10 /

N
———-
14

Now, it really starts looking that easy

Regards,
Rob.

Laurent Schneider Says:
November 23rd, 2007 at 18:06

well, if the table contains only one row, it will return null and if the third row is a NULL, it will exit too early (therefore UNTIL PRESENTV)


with mytable as (
  select 1 p, 63 n from dual union all
  select 2,1 from dual union all
  select 2,1 from dual union all
  select 2,null from dual union all
  select 2,1 from dual)
select p,n
from mytable
model
return updated rows
partition by (p)
dimension by (row_number() over (partition by p order by null) rn)
measures (n)
rules iterate (10000)
until (presentv(n[ITERATION_NUMBER+3],1,2)=2)
(
  n[1] = n[1] + nvl(n[ITERATION_NUMBER+2],0) -
  bitand(n[1],nvl(n[ITERATION_NUMBER+2],0))
)
/

         P          N
---------- ----------
         1         63
         2          1

Rob van Wijk Says:
November 24th, 2007 at 09:44
Good points.

Although the preconditions were not mentioned, your solution handles them and is therefore more robust.

Regards,
Rob.
Aketi Jyuuzou Says:
December 3rd, 2007 at 09:04
Oh
Good solution!

I post homeMadeBitOr

with t as (
select 10 n from dual union all
select 12 from dual union all
select 6 from dual)
select
sum(distinct(bitand(32,n)))+
sum(distinct(bitand(16,n)))+
sum(distinct(bitand( 8,n)))+
sum(distinct(bitand( 4,n)))+
sum(distinct(bitand( 2,n)))+
sum(distinct(bitand( 1,n))) as N
from t;
Laurent Schneider Says:
December 3rd, 2007 at 10:14
Nice approach, I wished I found it myself
Aketi Jyuuzou Says:
December 3rd, 2007 at 10:54
oops
We can use solution which is more simple.

with t as (
select 10 n from dual union all
select 12 from dual union all
select 6 from dual)
select
max(bitand(32,n))+
max(bitand(16,n))+
max(bitand( 8,n))+
max(bitand( 4,n))+
max(bitand( 2,n))+
max(bitand( 1,n)) as N
from t;
Laurent Schneider Says:
December 3rd, 2007 at 11:20
this makes sense!
Laurent Schneider Says:
December 3rd, 2007 at 11:27
and for AGGREGATE_BITAND, we could use MIN then!

Laurent Schneider Says:
December 3rd, 2007 at 11:34

for AGGREGATE_BITXOR, I would then use :

select
mod(sum(sign(bitand(32,n))),2)*32+
mod(sum(sign(bitand(16,n))),2)*16+
mod(sum(sign(bitand( 8,n))),2)* 8+
mod(sum(sign(bitand( 4,n))),2)* 4+
mod(sum(sign(bitand( 2,n))),2)* 2+
mod(sum(sign(bitand( 1,n))),2) n
from t;

Aketi Jyuuzou Says:
December 6th, 2007 at 12:59
http://oraclesqlpuzzle.hp.infoseek.co.jp/8-20.html
My site mentions these solutions (written by Japanese language)
Thank you
Laurent Schneider Says:
December 6th, 2007 at 13:15
wow! this is exactly the same, I like this

arigato!

Use <code> and </code> to post code

OR aggregate

16 Responses to “OR aggregate”

Leave a Reply