Oracle Database 21c で追加された関数を試す（Oracle Database 21c)

Oracle Database 21c にはいくつか集計関数が追加されましたので検証します。

CHECKSUM

CHECKSUM 関数 は列値の集約されたチェックサムを計算します。チェックサムは８バイトの整数で出力されます。他の集計関数（SUM, AVG など）と同じように使うことができます。テーブルの特定の列値が同一であるかを簡単に確認することができるため、データベースの移行時に移行前と移行後が同値であることを知りたい場合等に便利です。もっとも簡単な使い方は列名を指定します。

SQL> SELECT CHECKSUM(c1) FROM data1;

CHECKSUM(C1)
------------
      466639

チェックサムが同一であれば集計に含まれる値は同一であるといえます。チェックサムは検索の出力順番には影響を受けません。単一行を扱う関数ではないため、GROUP BY句や他の集約関数と同時に使用する必要があります。

SQL> SELECT CHECKSUM(c1), c1 FROM data1;
SELECT CHECKSUM(c1), c1 FROM data1
                     *
行1でエラーが発生しました。:
ORA-00937: 単一グループのグループ関数ではありません。

他の集約関数と同様に、ALL 句と DISTINCT 句を指定することができます。それぞれすべてのレコードまたは一意化されたレコードのチェックサムを計算します。OVER 句を指定することもできます。

SQL> SELECT CHECKSUM(ALL c1), CHECKSUM(DISTINCT c2) FROM data1;

CHECKSUM(ALLC1) CHECKSUM(DISTINCTC2)
--------------- --------------------
         466639               437609

すべてのレコードが同一であるグループに対してCHECKSUMを計算すると結果は0になります。下記の例では単一の値が格納された c2 列に対してチェックサムを計算しています。

SQL> SELECT COUNT(DISTINCT c2), CHECKSUM(c2) FROM data1;

COUNT(DISTINCTC2) CHECKSUM(C2)
----------------- ------------
                1            0

ANY_VALUE

ANY_VALUE 関数 は集約関数ですが、グループの中から単一の値を返します。実際には最初に見つかった値を返すように最適化されています。グループ内からどれでもいいので一つ値を返すような場合に、GROUP BY 句を書かなくても良くなります。

SQL> SELECT SUM(c1), ANY_VALUE(c1) FROM data1;

   SUM(C1) ANY_VALUE(C1)
---------- -------------
5000050000             1

LONG、LOB、FILE または COLLECTION 型には使用できません。

SQL> CREATE TABLE long1(c1 LONG);

表が作成されました。

SQL> SELECT ANY_VALUE(c1) FROM long1;
SELECT ANY_VALUE(c1) FROM long1
                 *
行1でエラーが発生しました。:
ORA-00997: LONGデータ型は使用できません。

DISTINCT 句、ALL 句と共に使うことができます。

BIT_AND_AGG / BIT_OR_AGG / BIT_XOR_AGG

ビット単位の AND / OR / XOR を集約します。これらも集約関数ですので、CHECKSUM 関数と同様の配慮（GROUP BY や、他の集約関数と同時に使う）が必要です。数値列にのみ使用することができます。

SQL> SELECT BIT_AND_AGG(c1), BIT_OR_AGG(c1), BIT_XOR_AGG(c1) FROM data1;

BIT_AND_AGG(C1) BIT_OR_AGG(C1) BIT_XOR_AGG(C1)
--------------- -------------- ---------------
              0         131071          100000

SQL> SELECT BIT_AND_AGG(c2) FROM data1;
SELECT BIT_AND_AGG(c2) FROM data1
                   *
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。

他の集約関数と同じように ALL / DISTINCT / UNIQUE 句を指定することができます。

SQL> SELECT BIT_OR_AGG(ALL c1), BIT_OR_AGG(DISTINCT c1), BIT_OR_AGG(UNIQUE c1) FROM data1;

BIT_OR_AGG(ALLC1) BIT_OR_AGG(DISTINCTC1) BIT_OR_AGG(UNIQUEC1)
----------------- ---------------------- --------------------
           131071                 131071               131071

NULL 値に対しては 0 を返します。

SQL> SELECT BIT_AND_AGG(NULL) FROM data1;

BIT_AND_AGG(NULL)
-----------------
                0