8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG Analytic Functions

This article gives an overview of the BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG analytic functions, introduced in Oracle database 21c. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Related articles.

Basics

You can find an introduction to bitwise operations here.

Here are some features common to all three functions.

Setup

The following table is used by the examples in this article.

-- drop table t1 purge;

create table t1 (
  id            number,
  code          varchar2(10),
  string_value  varchar2(10),
  bin_value     number
);


insert into t1 values (1 , 'one', '1010', bin_to_num(1,0,1,0));
insert into t1 values (2 , 'one', '0101', bin_to_num(0,1,0,1));

insert into t1 values (3 , 'two', '1000', bin_to_num(1,0,0,0));
insert into t1 values (4 , 'two', '0100', bin_to_num(0,1,0,0));
insert into t1 values (5 , 'two', '0010', bin_to_num(0,0,1,0));

insert into t1 values (6 , 'three', '0001', bin_to_num(0,0,0,1));
insert into t1 values (7 , 'three', '0010', bin_to_num(0,0,1,0));
insert into t1 values (8 , 'three', '0100', bin_to_num(0,1,0,0));
insert into t1 values (9 , 'three', '0100', bin_to_num(0,1,0,0));

insert into t1 values (10, 'four', '1000', bin_to_num(1,0,0,0));
insert into t1 values (11, 'four', '1000', bin_to_num(1,0,0,0));
commit;

BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG as Aggregate Functions

The following query uses the BIT_AND_AGG, BIT_OR_AGG and BIT_XOR_AGG aggregate functions to perform bitwise operations on the BIN_VALUE column.

select bit_and_agg(bin_value) as bit_and_agg_value,
       bit_or_agg(bin_value) as bit_or_agg_value,
       bit_xor_agg(bin_value) as bit_xor_agg_value
from   t1;


BIT_AND_AGG_VALUE BIT_OR_AGG_VALUE BIT_XOR_AGG_VALUE
----------------- ---------------- -----------------
                0               15                 2

SQL>

We can get more granularity of information by including a GROUP BY clause. This time we perform the bitwise operations separately for each value of the CODE column.

select code,
       bit_and_agg(bin_value) as bit_and_agg_value,
       bit_or_agg(bin_value) as bit_or_agg_value,
       bit_xor_agg(bin_value) as bit_xor_agg_value
from   t1
group by code
order by code;

CODE       BIT_AND_AGG_VALUE BIT_OR_AGG_VALUE BIT_XOR_AGG_VALUE
---------- ----------------- ---------------- -----------------
four                       8                8                 0
one                        0               15                15
three                      0                7                 3
two                        0               14                14

SQL>

Using DISTINCT or UNIQUE keywords mean only unique values in the expression are used for the bitwise operation. The ALL keyword is that same as the default action.

select bit_xor_agg(bin_value) as bit_xor_agg_value,
       bit_xor_agg(all bin_value) as bit_xor_agg_all_value,
       bit_xor_agg(distinct bin_value) as bit_xor_agg_distinct_value,
       bit_xor_agg(unique bin_value) as bit_xor_agg_unique_value
from   t1;


BIT_XOR_AGG_VALUE BIT_XOR_AGG_ALL_VALUE BIT_XOR_AGG_DISTINCT_VALUE BIT_XOR_AGG_UNIQUE_VALUE
----------------- --------------------- -------------------------- ------------------------
                2                     2                          0                        0

SQL>

The DISTINCT, UNIQUE and ALL keywords are also available for the analytic functions.

BIT_AND_AGG Analytic Function

Using an empty OVER clause turns the BIT_AND_AGG function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition. The following query uses the BIT_AND_AGG analytic function to display the row data, and the bit AND operation of the BIN_VALUE column for all the rows in the T1 table.

select id,
       code,
       bin_value,
       bit_and_agg(bin_value) over () as bit_and_agg_value
from   t1
order by id;

        ID CODE        BIN_VALUE BIT_AND_AGG_VALUE
---------- ---------- ---------- -----------------
         1 one                10                 0
         2 one                 5                 0
         3 two                 8                 0
         4 two                 4                 0
         5 two                 2                 0
         6 three               1                 0
         7 three               2                 0
         8 three               4                 0
         9 three               4                 0
        10 four                8                 0
        11 four                8                 0

SQL>

Adding the partitioning clause allows us to display the bit AND operation for each distinct value of the CODE column.

select id,
       code,
       bin_value,
       bit_and_agg(bin_value) over (partition by code) as bit_and_agg_value
from   t1
order by id;

        ID CODE        BIN_VALUE BIT_AND_AGG_VALUE
---------- ---------- ---------- -----------------
         1 one                10                 0
         2 one                 5                 0
         3 two                 8                 0
         4 two                 4                 0
         5 two                 2                 0
         6 three               1                 0
         7 three               2                 0
         8 three               4                 0
         9 three               4                 0
        10 four                8                 8
        11 four                8                 8

SQL>

BIT_OR_AGG Analytic Function

Using an empty OVER clause turns the BIT_OR_AGG function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition. The following query uses the BIT_OR_AGG analytic function to display the row data, and the bit OR operation of the BIN_VALUE column for all the rows in the T1 table.

select id,
       code,
       bin_value,
       bit_or_agg(bin_value) over () as bit_or_agg_value
from   t1
order by id;

        ID CODE        BIN_VALUE BIT_OR_AGG_VALUE
---------- ---------- ---------- ----------------
         1 one                10               15
         2 one                 5               15
         3 two                 8               15
         4 two                 4               15
         5 two                 2               15
         6 three               1               15
         7 three               2               15
         8 three               4               15
         9 three               4               15
        10 four                8               15
        11 four                8               15

SQL>

Adding the partitioning clause allows us to display the bit OR operation for each distinct value of the CODE column.

select id,
       code,
       bin_value,
       bit_or_agg(bin_value) over (partition by code) as bit_or_agg_value
from   t1
order by id;

        ID CODE        BIN_VALUE BIT_OR_AGG_VALUE
---------- ---------- ---------- ----------------
         1 one                10               15
         2 one                 5               15
         3 two                 8               14
         4 two                 4               14
         5 two                 2               14
         6 three               1                7
         7 three               2                7
         8 three               4                7
         9 three               4                7
        10 four                8                8
        11 four                8                8

SQL>

BIT_XOR_AGG Analytic Function

Using an empty OVER clause turns the BIT_XOR_AGG function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition. The following query uses the BIT_XOR_AGG analytic function to display the row data, and the bit XOR operation of the BIN_VALUE column for all the rows in the T1 table.

select id,
       code,
       bin_value,
       bit_xor_agg(bin_value) over () as bit_xor_agg_value
from   t1
order by id;

        ID CODE        BIN_VALUE BIT_XOR_AGG_VALUE
---------- ---------- ---------- -----------------
         1 one                10                 2
         2 one                 5                 2
         3 two                 8                 2
         4 two                 4                 2
         5 two                 2                 2
         6 three               1                 2
         7 three               2                 2
         8 three               4                 2
         9 three               4                 2
        10 four                8                 2
        11 four                8                 2

SQL>

Adding the partitioning clause allows us to display the bit XOR operation for each distinct value of the CODE column.

select id,
       code,
       bin_value,
       bit_xor_agg(bin_value) over (partition by code) as bit_xor_agg_value
from   t1
order by id;

        ID CODE        BIN_VALUE BIT_XOR_AGG_VALUE
---------- ---------- ---------- -----------------
         1 one                10                15
         2 one                 5                15
         3 two                 8                14
         4 two                 4                14
         5 two                 2                14
         6 three               1                 3
         7 three               2                 3
         8 three               4                 3
         9 three               4                 3
        10 four                8                 0
        11 four                8                 0

SQL>

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

AVG * BIT_AND_AGG * BIT_OR_AGG * BIT_XOR_AGG * CHECKSUM *
CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_SET CORR *
COUNT * COVAR_POP * COVAR_SAMP * CUME_DIST DENSE_RANK
FEATURE_DETAILS FEATURE_ID FEATURE_SET FEATURE_VALUE FIRST
FIRST_VALUE * KURTOSIS_POP * KURTOSIS_SAMP * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION
PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY PREDICTION_SET RANK
RATIO_TO_REPORT REGR_ (Linear Regression) Functions * ROW_NUMBER SKEWNESS_POP * SKEWNESS_SAMP *
STDDEV * STDDEV_POP * STDDEV_SAMP * SUM * VAR_POP *
VAR_SAMP * VARIANCE * String Aggregation Top-N Queries  

For more information see:

Hope this helps. Regards Tim...

Back to the Top.