Radino’s blog

Finally, a blog post after ages . Recently I received a new feedback on my two years old article on bitwise or aggregate function, where a visitor is looking for a bitcount aggregate function. This was a nice exercise for me. After a little of googling I have found a website with plenty of bitwise operations implementations. I’ve chosen an easy Keringhan’s approach. Here is the function:

CREATE OR REPLACE TYPE bitcount_impl AS OBJECT
(
  bitcount NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitcount_impl) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT bitcount_impl,
                                       VALUE IN NUMBER) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitcount_impl,
                                     ctx2 IN bitcount_impl) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT bitcount_impl,
                                         returnvalue OUT NUMBER,
                                         flags       IN NUMBER) RETURN NUMBER
)
/

CREATE OR REPLACE TYPE BODY bitcount_impl IS
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitcount_impl) RETURN NUMBER IS
  BEGIN
    ctx := bitcount_impl(0);
    RETURN ODCIConst.Success;
  END ODCIAggregateInitialize;

  MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT bitcount_impl,
                                       VALUE IN NUMBER) RETURN NUMBER IS
    val number := value;
  BEGIN
    WHILE val > 0 LOOP
      val := bitand(val, val-1);
      SELF.bitcount := SELF.bitcount + 1;
    END LOOP;
    RETURN ODCIConst.Success;
  END ODCIAggregateIterate;

  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitcount_impl,
                                     ctx2 IN bitcount_impl) RETURN NUMBER IS
  BEGIN
    SELF.bitcount := SELF.bitcount + ctx2.bitcount;
    RETURN ODCIConst.Success;
  END ODCIAggregateMerge;

  MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT bitcount_impl,
                                         returnvalue OUT NUMBER,
                                         flags       IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnvalue := SELF.bitcount;
    RETURN ODCIConst.Success;
  END ODCIAggregateTerminate;
END;
/

CREATE OR REPLACE FUNCTION bitcountagg(x IN NUMBER) RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING bitcount_impl;
/

DROP TABLE bitcount_test;

CREATE TABLE bitcount_test(val number);

INSERT INTO bitcount_test VALUES (5);  -- 0101

INSERT INTO bitcount_test VALUES (1);  -- 0001

INSERT INTO bitcount_test VALUES (9);  -- 1001

INSERT INTO bitcount_test VALUES (12); -- 1100

COMMIT;

SELECT bitcountagg(val) FROM bitcount_test;

BITCOUNTAGG(VAL)
----------------
               7

Tags: aggregation, bit count, bitwise