- データベース管理者のための主要20c新機能の学習
- データベース管理者のための主要20c新機能の学習
- ツールおよび言語
- 分析SQL関数および統計関数
- 分析関数の拡張
- 演習: 拡張された分析関数の使用
演習: 拡張された分析関数の使用
この演習では、ウィンドウ・フレーム句、GROUPSおよびEXCLUDEの新しいオプションと、表の式のWINDOW句を活用する方法を示します。
- 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
- ウィンドウ・フレームの
GROUPS句の使用を試してみます。- ウィンドウ・フレームの
GROUPS句の試用を開始する前に、/home/oracle/labs/M104784GC10/setup_analytic_table.shシェル・スクリプトを実行します。このシェル・スクリプトでは、PDB20とPDB19の両方でユーザーREPORTを作成し、REPORTにCREATE SESSION、CREATE TABLEおよびUNLIMITED TABLESPACE権限を付与して、最後に行を含む表TRADESを作成します。$ /home/oracle/labs/M104784GC10 $ /home/oracle/labs/M104784GC10/setup_analytic_table.sh SQL*Plus: Release 20.0.0.0.0 - Production on Mon Feb 3 09:23:40 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> DROP USER report CASCADE; User dropped. SQL> CREATE USER report IDENTIFIED BY password; User created. SQL> GRANT create session, create table, unlimited tablespace TO report; Grant succeeded. SQL> CREATE TABLE report.trades (acno NUMBER, tid NUMBER, Tday DATE, Ttype VARCHAR2(4), amount NUMBER, Ticker VARCHAR2(4)); Table created. SQL> INSERT INTO report.trades VALUES (123, 1, sysdate, 'buy', 1000, 'CSCO'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 1, sysdate, 'buy', 400, 'JNPR'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 3, sysdate+2, 'buy', 2000, 'SYMC'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 4, sysdate+2, 'buy', 1200, 'CSCO'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 5, sysdate+2, 'buy', 500, 'JNPR'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 6, sysdate+4, 'buy', 200, 'CSCO'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 7, sysdate+4, 'buy', 100, 'CSCO'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 9, sysdate+5, 'buy', 400, 'JNPR'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 10, sysdate+5, 'buy', 200, 'GOOG'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 11, sysdate+5, 'buy', 1000, 'JNPR'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 12, sysdate+5, 'buy', 4000, 'JNPR'); 1 row created. SQL> INSERT INTO report.trades VALUES (123, 13, sysdate+8, 'buy', 2000, 'HPQ'); 1 row created. SQL> COMMIT; Commit complete. SQL> EXIT Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 $ PDB20のREPORT.TRADESの行を表示します。ROWSを使用して、現在の行から行を前後にカウントすることによって、ウィンドウ・フレームのエクステントを指定します。ROWSでは、任意の数のソート・キー、または任意の順序のデータ型を指定できます。行のカウントでは、ソートされている値の"穴"に気づかないため、これは有益な場合があります。一方、ソート・キーに同一の行が複数存在し、ソート・キーに同じ値を持つ2つの行の間に任意のカットオフを引き起こす場合、現在の行からの行のカウントは非決定的になる可能性があります。RANGEを使用して、オフセットを指定します。ソート・キーは厳密に1つ存在する必要があり、その宣言された型は加算および減算(数値、日時または間隔)に適している必要があります。これにより、同じ値を持つ2つの隣接行の間で任意にカットするという非決定性は回避されますが、加法タイプの単一のソート・キーでのみ使用できます。SQL:2011規格には、キーワードGROUPSを使用してウィンドウ・フレームのエクステントを指定する3つ目の方法があります。ROWSと同様に、GROUPSウィンドウにも任意の数のソート・キーまたは任意の順序の型を指定できます。RANGEと同様に、GROUPSウィンドウでも、ソート・キーに同じ値を持つ隣接行の間でのカットオフは実行されません。したがって、GROUPSは、ROWSとRANGEの両方の機能の一部を組み合せます。$ sqlplus report@PDB20 SQL*Plus: Release 20.0.0.0.0 - Production on Mon Feb 3 09:31:17 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: password Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> SET PAGES 100 SQL> SELECT * FROM trades; ACNO TID TDAY TTYP AMOUNT TICK ---------- ---------- --------- ---- ---------- ---- 123 1 08-APR-20 buy 1000 CSCO 123 1 08-APR-20 buy 400 JNPR 123 3 10-APR-20 buy 2000 SYMC 123 4 10-APR-20 buy 1200 CSCO 123 5 10-APR-20 buy 500 JNPR 123 6 12-APR-20 buy 200 CSCO 123 7 12-APR-20 buy 100 CSCO 123 9 13-APR-20 buy 400 JNPR 123 10 13-APR-20 buy 200 GOOG 123 11 13-APR-20 buy 1000 JNPR 123 12 13-APR-20 buy 4000 JNPR 123 13 16-APR-20 buy 2000 HPQ 12 rows selected. SQL>- アカウント番号123が"購入"を実行した過去5日間の合計金額を計算します。この問合せに回答するには、データを取引日別にグループ化し、各取引日の金額の合計を計算してから、
ROWSウィンドウを使用して過去5取引日を合計します。SQL> SELECT trades.acno, trades.tday, SUM (agg.suma) OVER W FROM trades, (SELECT acno, tday, SUM(amount) AS suma FROM trades WHERE ttype = 'buy' GROUP BY acno, tday ) agg WHERE trades.acno = agg.acno AND trades.tday = agg.tday AND trades.ttype = 'buy' WINDOW W AS (PARTITION BY trades.acno ORDER BY trades.tday ROWS BETWEEN 4 PRECEDING AND CURRENT ROW); ACNO TDAY SUM(AGG.SUMA)OVERW ---------- --------- ------------------ 123 08-APR-20 1400 123 08-APR-20 2800 123 10-APR-20 6500 123 10-APR-20 10200 123 10-APR-20 13900 123 12-APR-20 12800 123 12-APR-20 11700 123 13-APR-20 13600 123 13-APR-20 15500 123 13-APR-20 17400 123 13-APR-20 22700 123 16-APR-20 24400 12 rows selected. SQL>この問合せが機能する理由は、合計を部分集計に分解し、それらの部分集計から最終的な合計を計算できるためです。この場合、問合せでは、
acnoおよびtdayで定義されたグループの合計を分解しています。次に、グループ化された問合せから部分合計を加算して、5日を超える取引日の合計を取得します。COUNT、MAXおよびMINも分解可能な集計です。AVGは、合計と数を計算して除算することで分解できます。ウィンドウ句で指定されているウィンドウ名は、直接、カッコなしでのみ参照できます。
- 過去5取引日の間に取引された個々のティッカ・シンボルの数を問い合せます。これには
COUNT DISTINCTが必要で、これはサンプル・データで見られるように、異なる取引日に重複するティッカ・シンボルが存在する可能性があるため、取引日ごとに1つずつ、部分カウントに分解することはできません。COUNT DISTINCTは分解できず、前述の問合せでの手法は使用できません。RANGEまたはROWSのかわりにキーワードGROUPSを使用します。キーワードGROUPSでは、グループ化された問合せとの関係を重要視します。この種のキーワードを使用すると、各アカウント番号について、アカウントが"購入"を実行した過去5取引日についてなどの問合せに回答し、支出額や購入された個々のティッカ・シンボルの数がわかります。SQL> SELECT acno, tday, SUM(amount) OVER W, COUNT(DISTINCT ticker) OVER W FROM trades WHERE ttype = 'buy' WINDOW W AS (PARTITION BY acno ORDER BY tday GROUPS BETWEEN 4 PRECEDING AND CURRENT ROW); SELECT acno, tday, SUM(amount) OVER W, COUNT(DISTINCT ticker) OVER W * ERROR at line 1: ORA-30487: ORDER BY not allowed here SQL>注意:
DISTINCTが指定された<aggregate function>は、<window order clause>が含まれる<window specification>とともに使用することはできません。SQL> SELECT acno, tday, SUM(amount) OVER W, COUNT(ticker) OVER W FROM trades WHERE ttype = 'buy' WINDOW W AS (PARTITION BY acno ORDER BY tday GROUPS BETWEEN 4 PRECEDING AND CURRENT ROW); ACNO TDAY SUM(AMOUNT)OVERW COUNT(TICKER)OVERW ---------- --------- ---------------- ------------------ 123 08-APR-20 1400 2 123 08-APR-20 1400 2 123 10-APR-20 5100 5 123 10-APR-20 5100 5 123 10-APR-20 5100 5 123 12-APR-20 5400 7 123 12-APR-20 5400 7 123 13-APR-20 11000 11 123 13-APR-20 11000 11 123 13-APR-20 11000 11 123 13-APR-20 11000 11 123 16-APR-20 13000 12 12 rows selected. SQL>この構文では、ステップcの場合と同様に、ネストされたグループ化された問合せおよび
TRADESとの結合が必要ないことに注意してください。
- ウィンドウ・フレームの
- ウィンドウ・フレームの
EXCLUDE句の使用を試してみます。- ウィンドウ・フレームの
EXCLUDE句の試用を開始する前に、/home/oracle/labs/M104784GC10/create_T_table.sqlSQLスクリプトを実行します。SQL> @/home/oracle/labs/M104784GC10/create_T_table.sql SQL> SET ECHO ON SQL> DROP TABLE t; Table dropped. SQL> CREATE TABLE t (v NUMBER); Table created. SQL> INSERT INTO t VALUES (1); 1 row created. SQL> INSERT INTO t VALUES (1); 1 row created. SQL> INSERT INTO t VALUES (3); 1 row created. SQL> INSERT INTO t VALUES (5); 1 row created. SQL> INSERT INTO t VALUES (5); 1 row created. SQL> INSERT INTO t VALUES (5); 1 row created. SQL> INSERT INTO t VALUES (6); 1 row created. SQL> COMMIT; Commit complete. SQL> T表の行を表示します。SQL> SELECT * FROM t; V ---------- 1 1 3 5 5 5 6 7 rows selected. SQL>ROWSによるウィンドウ・フレームの除外に、EXCLUDEオプションを使用します。EXCLUDE CURRENT ROWが指定されていて、現在の行がまだウィンドウ・フレームのメンバーである場合は、ウィンドウ・フレームから現在の行を削除します。EXCLUDE GROUPが指定されている場合は、現在の行と現在の行のピアをウィンドウ・フレームから削除します。EXCLUDE TIESが指定されている場合は、現在の行のピアである現在の行以外の行をすべてウィンドウ・フレームから削除します。現在の行がすでにウィンドウ・フレームから削除されている場合は、ウィンドウ・フレームから削除されたままになります。EXCLUDE NO OTHERSが指定されている場合(これがデフォルトです)、このルールによってウィンドウ・フレームからさらに行が削除されることはありません。SQL> SELECT v, sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group, sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties, sum(v) OVER (o ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW o AS (ORDER BY v); V CURRENT_ROW THE_GROUP TIES NO_OTHERS ---------- ----------- ---------- ---------- ---------- 1 1 1 2 1 4 3 4 5 3 6 6 9 9 5 8 3 8 13 5 10 5 15 5 11 6 11 16 6 5 5 11 11 7 rows selected. SQL> SELECT v, sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE GROUP) AS the_group, sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE TIES) AS ties, sum(v) OVER (o ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW o AS (ORDER BY v); V CURRENT_ROW THE_GROUP TIES NO_OTHERS ---------- ----------- ---------- ---------- ---------- 1 4 3 4 5 1 9 8 9 10 3 12 12 15 15 5 14 4 9 19 5 19 9 14 24 5 16 6 11 21 6 10 10 16 16 7 rows selected. SQL>RANGEによるウィンドウ・フレームの除外に、EXCLUDEオプションを使用します。SQL> SELECT v, sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group, sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties, sum(v) OVER (o RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW o AS (ORDER BY v); V CURRENT_ROW THE_GROUP TIES NO_OTHERS ---------- ----------- ---------- ---------- ---------- 1 1 1 2 1 1 1 2 3 3 3 5 16 6 11 21 5 16 6 11 21 5 16 6 11 21 6 15 15 21 21 7 rows selected. SQL>GROUPSによるウィンドウ・フレームの除外に、EXCLUDEオプションを使用します。SQL> SELECT v, sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS current_row, sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS the_group, sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS ties, sum(v) OVER (o GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS no_others FROM t WINDOW o AS (ORDER BY v); V CURRENT_ROW THE_GROUP TIES NO_OTHERS ---------- ----------- ---------- ---------- ---------- 1 4 3 4 5 1 4 3 4 5 3 17 17 20 20 5 19 9 14 24 5 19 9 14 24 5 19 9 14 24 6 15 15 21 21 7 rows selected. SQL> EXIT $
- ウィンドウ・フレームの
親トピック: 分析関数の拡張