Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
1
Help us understand the problem. What is going on with this article?
@plusultra

LISTAGG関数の新機能を試す(Oracle Database 12c Release 2)

More than 1 year has passed since last update.

LISTAGG関数

Oracle Databaseには行列の入れ替えを行う関数としてLISTAGGが提供されています。この関数は取得した行データを単一の文字列に連結します。
LISTAGG関数では各行データを連結する際に、レコードのセパレータを指定することができます。連結する順番はWITHIN GROUP (ORDER BY)句で指定します。
Oracle Database 12cでは、文字列長があふれた場合の挙動を変更できるようになりました。
LISTAGG関数の仕様はマニュアルに記載されています。

データの準備

この検証で使用するデータはTPC-DSで定義されたcustomerテーブルです。SQL*Loaderを使って100,000件のレコードを格納します。

列名 データ型 NOT NULL
C_CUSTOMER_SK NUMBER(38) NOT NULL
C_CUSTOMER_ID CHAR(16) NOT NULL
C_CURRENT_CDEMO_SK NUMBER(38)
C_CURRENT_HDEMO_SK NUMBER(38)
C_CURRENT_ADDR_SK NUMBER(38)
C_FIRST_SHIPTO_DATE_SK NUMBER(38)
C_FIRST_SALES_DATE_SK NUMBER(38)
C_SALUTATION CHAR(10)
C_FIRST_NAME CHAR(20)
C_LAST_NAME CHAR(30)
C_PREFERRED_CUST_FLAG CHAR(1)
C_BIRTH_DAY NUMBER(38)
C_BIRTH_MONTH NUMBER(38)
C_BIRTH_YEAR NUMBER(38)
C_BIRTH_COUNTRY VARCHAR2(20)
C_LOGIN CHAR(13)
C_EMAIL_ADDRESS CHAR(50)
C_LAST_REVIEW_DATE CHAR(10)

データベースの設定

Oracle Database 12cではVARCHAR2型の最大文字列長が32,768バイトまで拡張できるようになりました。ただし検証環境ではテスト環境ではデフォルトのままです。このためVARCHAR2型の最大長は4,000バイトになります。

SQL> SHOW PARAMETER max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL>

LISTAGG関数の実行

正常実行時

LISTAGG関数が正常に実行される場合、行レコードで取得された値が単一の文字列に変換されます。
下記は、セパレータとしてコロン(:)を指定しています。条件としてc_last_review_date列と、c_birth_day列を指定しています。

SQL> SELECT
  2  LISTAGG(TRIM(c_first_name), ':') WITHIN GROUP (ORDER BY c_customer_id)
  3  FROM customer WHERE c_last_review_date='2452648' AND c_birth_day=31;

LISTAGG(TRIM(C_FIRST_NAME),':')WITHINGROUP(ORDERBYC_CUSTOMER_ID)
--------------------------------------------------------------------------------
Christine:Gerald:Eliseo:Vivian:Michael:Walter:Gregory:Bernice

c_first_name列の値がコロンを区切りとして、単一の文字列に連結されていることがわかります。TRIM関数を指定しているのはc_first_name列がCHAR型であるためです。VARCHAR2型の場合は空白は出力されません。

デフォルトの動作

WHERE句に指定した条件により、LISTAGG関数が生成する文字列の長さは変わってきます。文字列の長さが最大長を超えるとデフォルトでは
LISTAGG関数はORA-01489が発生して異常終了します。

SQL> SELECT 
  2 LISTAGG(c_last_name, ':') WITHIN GROUP (ORDER BY c_customer_id) 
  3 FROM customer;
FROM customer
     *                                                                            *
行3でエラーが発生しました。:
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

この動作はLISTAGG関数にON OVERFLOW ERRORを指定しても同じです(Oracle Database 12c新構文)。

SQL> SELECT 
  2  LISTAGG(c_last_name, ':' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY c_customer_id)
  3  FROM customer;
FROM customer
     *
行3でエラーが発生しました。:
ORA-01489: 文字列を連結した結果、長さが最大長を超えました

Oracle Database 12c Release 2では、エラーを抑止するためにはON OVERFLOW TRUNCATE句を指定することができます。

SQL> SELECT 
  2  LISTAGG(TRIM(c_last_name), ':' ON OVERFLOW TRUNCATE)
  3  WITHIN GROUP (ORDER BY c_customer_id)
  4* FROM customer;

LISTAGG(TRIM(C_LAST_NAME),':'ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYC_CUSTOMER_ID
--------------------------------------------------------------------------------
Sanders:Williams:Brewer:Lohr:Terry:Dillon:Huber:Mcgrath:Martin:Jarrett:Perez:Par
ker:Cooper:Brooks:Roberts:Patterson:Goodwin:Thompson:Gresham:Roland:Nicholson:At
(省略しています)
Thornton:Barba:Fulcher:Sheppard:Barr:Ramirez:Donovan:Andrews:Long:Jones:Norton:P
utman:Anderson:Clark:Thompson:Fuller:Fink:Cohen:...(99416)

文字列の最後にセパレータ(...)が出力され、(99416)という数字が出力されています。これは99416個のデータが省略されたことを示します。
セパレータの文字列は変更できます。下記はセパレータを文字列'---'に指定しています。

SQL> SELECT 
  2  LISTAGG(TRIM(c_last_name), ':' ON OVERFLOW TRUNCATE '---')
  3  WITHIN GROUP (ORDER BY c_customer_id)
  4  FROM customer;

LISTAGG(TRIM(C_LAST_NAME),':'ONOVERFLOWTRUNCATE'---')WITHINGROUP(ORDERBYC_CUSTOM
--------------------------------------------------------------------------------
Sanders:Williams:Brewer:Lohr:Terry:Dillon:Huber:Mcgrath:Martin:Jarrett:Perez:Par
ker:Cooper:Brooks:Roberts:Patterson:Goodwin:Thompson:Gresham:Roland:Nicholson:At
(省略しています)
Thornton:Barba:Fulcher:Sheppard:Barr:Ramirez:Donovan:Andrews:Long:Jones:Norton:P
utman:Anderson:Clark:Thompson:Fuller:Fink:Cohen:---(99416)

省略された数を出力しない場合はWITHOUT COUNT句を指定します。セパレータを出力させなくすることはできません。

SQL> SELECT 
  2  LISTAGG(TRIM(c_last_name), ':' ON OVERFLOW TRUNCATE '---' WITHOUT COUNT)
  3  WITHIN GROUP (ORDER BY c_customer_id)
  4  FROM customer;

LISTAGG(TRIM(C_LAST_NAME),':'ONOVERFLOWTRUNCATE'---'WITHOUTCOUNT)WITHINGROUP(ORD
--------------------------------------------------------------------------------
Sanders:Williams:Brewer:Lohr:Terry:Dillon:Huber:Mcgrath:Martin:Jarrett:Perez:Par
ker:Cooper:Brooks:Roberts:Patterson:Goodwin:Thompson:Gresham:Roland:Nicholson:At
(省略しています)
Thornton:Barba:Fulcher:Sheppard:Barr:Ramirez:Donovan:Andrews:Long:Jones:Norton:P
utman:Anderson:Clark:Thompson:Fuller:Fink:Cohen:Alexander:Thompson:Mclendon:---

ON OVERFLOW TRUNCATE句を指定した場合の最大長は、システムが許可する最大長よりも小さくなります。連結する行の値が途中で切れることはありません。

SQL> SELECT
  2  LENGTH(LISTAGG(c_last_name, ':' ON OVERFLOW TRUNCATE '---')
  3  WITHIN GROUP (ORDER BY c_customer_id))
  4  FROM customer;

LENGTH(LISTAGG(C_LAST_NAME,':'ONOVERFLOWTRUNCATE'---')WITHINGROUP(ORDERBYC_CUSTO
--------------------------------------------------------------------------------
                                                                            3978

SQL>
1
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
1
Help us understand the problem. What is going on with this article?