DB2 withの定義と使用方法

2022-03-02 03:06:42

定義と使用法

の一部が再掲載され、照合されています。

1.理解と基本的な使い方をもって

WITH文というと、初めて聞く人以外は、再帰的な問い合わせに使うものだと思っている人が多いのではないでしょうか。しかし、それは使い方のひとつに過ぎないのです。

本当の名前は、タイトルにあるようにCommon Table Expressionで、文字通り、何に使うか？

実は、一時的なコレクションを定義するために使われるんだ。VALUES文は一時的なコレクションを定義するために使われているのではありませんか？どうしてWITH文は一時的なコレクションを定義するためにも使われるのですか？両者の違いは何ですか？

VALUES文は、次のように、明示的な値を持つ一時的なコレクションを定義するために使用されます。

値 (1,2), (1,3),(2,1)

WITH文は、一時的なコレクションを定義するためのクエリー（つまりselect文）であり、その意味ではビューに少し似ていますが、ビューではないので誤解のないようにお願いします。例えば、次のようなものです。

-- ビルドテーブル

drop table user;

create table user (

NAME VARCHAR(20) NOT NULL,---NAME

SEX INTEGER,---性別 (1, 男性) 2、女性)

誕生日 --- 誕生日

);

--データを挿入する

insert into user (name,sex,birthday) values ('zhangshan','1','1990-1-1');

insert into user (name,sex,birthday) values ('lisi','2','1991-1-1');

insert into user (name,sex,birthday) values ('wangwu','1','1992-1-1');

insert into user (name,sex,birthday) values ('sunliu','2','1949-10-1');

insert into user (name,sex,birthday) values ('tianqi','1','1994-1-1');

insert into user (name,sex,birthday) values ('zhaoba','2','1995-1-1');

with test(name_test, bday_test) as -testはクエリの結果セットの名前を括弧で囲み、その後に名前を変更したカラムを続けます。

(

SELECT NAME,BIRTHDAY FROM USER - ステートメント1

)

SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1';--Statement 2

まずステートメント1が実行され、2つのカラム(NAME,BIRTHDAY)を持つ結果セットを生成します。この結果セットをテストと名付け、カラム名をNAME_TEST、BDAY_TESTに変更すると説明します。

最後に、この一時集合から誕生日が1949-10-1、すなわち共和国である仲間を見つけるために文2を実行します。

いかがでしょうか。いまいちピンとこない方は、上の文をよく分析してみてください。VALUES文とWITH文を併用した例として、次のようなものがあります。

with test(name_test, bday_test) as

(

VALUES ('チャン・サン','1997-7-1'),('リー・シー','1949-10-1')

)

select name_test from test where bday_test='1949-10-1'

以上と、WITH文がほとんどの人になじみがないことから、WITH文は複雑なクエリのために設計されていると推測できますし、実際その通りです。

ここで、スキルアップを目指すなら外せない複雑な例をご紹介しましょう。次のようなケースを考えてみましょう。

-- ビルドテーブル

テーブルuser2を削除します。

テーブルuser2を作成

(

NAME VARCHAR(20) NOT NULL,-- 名前

DEGREE INTEGER NOT NULL,--学歴(1、専門 2、学士 3、修士 4、博士)

STARTWORKDATE date NOT NULL,--入室時間

SALARY1 FLOAT NOT NULL,--基本給

SALARY2 FLOAT NOT NULL-- ボーナス

);

--データを挿入する

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('zhangsan',1,'1995-1-1',10000.00,1600.00);

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('lisi',2,'1996-1-1',5000.00,1500.00);

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('wangwu',3,'1997-1-1',6000.00,1400.00);

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('sunliu',4,'1998-1-1',7000.00,1300.00);

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('tianqi',2,'1999-1-1','7000','1300')。

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('zhaoba',1,'2000-1-1',9000,1400);

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('qianjiu',3,'1997-1-1',2000,1000);

insert into user2 (name,degree,startworkdate,salary1,salary2) values ('dushe',4,'1992-1-1',3000,1000)。

select * from user2;

次に、1.修士号または博士号を持っている人に問い合わせるように言われたとします。 2. 学歴が同じで入社年度も同じだが、給与（基本給＋賞与）が同条件の社員の平均給与より低い社員。

(ははは、おそらく給料を上げるためでしょう）、質問が理解できているか分かりませんが？どのように問い合わせればいいのでしょうか？こんな風に考えています。

1. 学歴が修士または博士の従業員に対するクエリは、次のように結果セット1を生成します。

select name,degree,year(startworkdate) as worddate,salary1+salary2 as salary from user2 where degree in (3,4).名前、学位、年(startworkdate)をワードデートとして選択します。

2. 学歴と入社年次のグループ分けから平均給与を求め、次のように結果セット2を得る。

select degree,year(startworkdate) as worddate, avg(salary1+salary2) as avg_salary

from user2 where degree in (3,4)

学位,年(開始日)でグループ化。

3、学歴と入社年に基づいて2つの結果セットを結合して、給与<平均給与を持つ従業員を見つける、以下は完全なSQLである。

with temp1(name,degree,worddate,salary) as

(

select 名前,学位,年(startworkdate) as worddate, salary1+salary2 as salary from user2 where degree in (3,4)

temp2 (学位,ワードデート,平均給与) as

(

select degree,year(startworkdate) as worddate, avg(salary1+salary2) as avg_salary.

from user2 where degree in (3,4)

group by degree,year(startworkdate)

)

select name from temp1, temp2 where

temp1.度=temp2.度

と temp1.worddate=temp2.worddate である。

AND SALARY<AVG_SALARY;

このクエリーはまさに正しいのですが、改善の余地があります。クエリ結果セット2において、usersテーブルからデータを取得しています。

実はこの時点ですでに結果セット1がクエリされており、結果セット1からグループ化することで結果セット2を得ることができるのです

uerテーブルから結果セット2を取得するのではなく、上と下の文を比較すれば、私の言っていることがわかるでしょう

with temp1(name,degree,worddate,salary) as

(

select 名前,学位,年(startworkdate) as worddate, salary1+salary2 as salary from user2 where degree in (3,4)

temp2 (学位,ワードデート,平均給与) as

(

select degree,worddate, avg(salary) as avg_salary.

FROM TEMP1

学位、ワードデートによるグループ

)

select name from temp1, temp2 where

temp1.度=temp2.度

と temp1.worddate=temp2.worddate である。

AND SALARY<AVG_SALARY;

WITH文を使わなくても分かるよ、という方もいらっしゃるかもしれませんが、確かに以下のようになります。

select u.name from user2 as u,

(

select degree,year(startworkdate) as worddate, avg(salary1+salary2) as avg_salary.

from user2 where degree in (3,4)

group by degree,year(startworkdate)

) AS G

ここで、u.degree=g.degree

かつ年(u.startworkdate)=g.worddateとする。

AND (SALARY1+SALARY2)<G.AVG_SALARY.AND (SALARY1+SALARY2)<G.AVG_SALARY;

WITHを使用する場合と使用しない場合の違いは何ですか？一般的には、この2つの書き方でパフォーマンスに大きな差はありませんが

1. USERテーブルのレコード数が多い場合

2. USERテーブルの修士・博士（DEGREE IN (3,4)）の割合が少ない場合

上記の条件を満たしたとき、2つの書き込み方法の性能の違いが明らかになります。なぜなら、WITH書き込みメソッドを使用しないステートメントは、USERテーブルに2回アクセスするからです。

DEGREEフィールドにインデックスがない場合、パフォーマンスの差は大きくなります。

2. with Iの再帰的適用

これを読む頃には、上記をよく理解していれば、WITH文の良さがある程度わかっていただけると思います。しかし、WITH文の魅力はそれだけではありません。

ここでは、WITHステートメントを使って再帰的なクエリを行う方法を説明します。再帰的な問い合わせの典型的な例は、ツリー構造のテーブルに対する問い合わせで、次のように考えてみます。

01.フォーラムホーム

02. - データベース開発

03. ----DB2

04. -----DB2 記事 1

05. -------DB2 第1条のコメント1

06. --------DB2 第1条のコメント2

07. -----DB2 第2条

08. ----オラクル

09. - Javaテクノロジー

上記はフォーラムの典型的な例です。以下では、上記の情報を保存するために新しいテーブルを作成します。

ドロップテーブルBBS

bbsテーブルの作成

(

parentid integer not null。

id integer not null。

NAME VARCHAR(200) NOT NULL --- 掲示板、記事、コメントなど。

);

挿入 into bbs (PARENTID,ID,NAME) values

(0,0,'フォーラムホーム'),

(0,1,'データベース開発'),

(1,11,'DB2')です。

(11,111,'DB2条1')である。

(111,1111,'DB2条1項のコメント1'),

(111,1112,'DB2第1条コメント2'),

(11,112,'DB2条2')とする。

(1,12,'Oracle')です。

(0,2,'Java Technology')です。

さて、すべての準備が整ったので、クエリーを開始しましょう。例えば、「DB2 Article 1」に関する全てのコメントを調べるように言われ、誰かが「それは簡単だ、次のようにすればいい」と言ったとします。

SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2 Article 1').SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2 Article 1');

答えは、まさにその通りです。では、今度はDB2に全記事とコメントを問い合わせろということですが、どうすればいいのでしょうか？従来の方法では問い合わせが難しいので、次のような再帰的な問い合わせが便利です。

をtemp(parentid,id,name)とする。

(

SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2' --- 文章1

UNION ALL ---ステートメント2

SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTID=T.ID ---statement 3

)

SELECT NAME FROM TEMP; --- ステートメント4

WITH句の中の最初のSELECT文は、テーブルの初期化である。これは一度だけ実行されます。その結果は、再帰の種として使用される仮想テーブルの初期内容を形成する。上の例では、種は'NAME'である。 DB2の1行または複数行。

2回目のSELECT文は複数回実行されます。次の行のコレクションを生成するために、2番目のSELECT文の入力として種を渡します。結果は仮想テーブルの現在の内容に追加（UNION ALL）され、次のパスの入力を形成するために戻されます。この処理は、行が生成される限り続けられます。

実行後、結果は全く正しいことがわかりましたが、では具体的にどのように動作するのでしょうか？以下、詳しく説明します。

1. まず、ステートメント1が実行されますが、これはループの起点として一度だけ実行されます。2.結果セットを取得します。DB2

2. 次にステートメント3がループで実行されますが、ここではもう少し詳しく説明する必要があります。

そもそもステートメント3はどのような意図で作成されたのでしょうか。はっきり言って、ステートメント1で生成された結果セット（DB2）の次のレベルを探すことです。では、ディレクトリツリーでDB2の次のレベルは何かというと、「DB2 Article 1」「DB2 Article 2」ということになります。

そして、そのクエリ結果セットを次のループの出発点として、次のレベルがなくなるまでクエリを実行します。

どうですか？まだわからない？ははは、気にしないで、一歩一歩進んでいきましょう。

まず、ステートメント1が結果セットを生成します。DB2は、ループの開始点として、その次のレベルを見つけるために、BBSテーブルとそれを関連付け、クエリの結果は次のとおりです： 'DB2 Article 1' と 'DB2 Article 2'

次に、前回のクエリの結果（つまり「DB2 Article 1」と「DB2 Article 2」）をBBSテーブルに関連付け、その次のレベルを探すと、クエリの結果は「DB2 Article 1のコメント1 ' と 'DB2 Article 1のコメント2' 」となります。

そして、最後のクエリの結果（すなわち、「DB2 Article 1のコメント1」と「DB2 Article 1のコメント2」）をBBSテーブルに関連付け、その次のレベルを探した後、結果は返らず、ループは終了する。

3. 3つ目は、ステートメント2を実行し、すべての結果セットをまとめて、temp結果セットを取得することです。

4. 最後に、ステートメント4により、一時的なテンポラリーセットから期待するクエリ結果を取得します。

については、特別な注意事項が必要です。

1. ステートメント3の関連付けの条件に注意しないと、デッドループを書きやすくなります。

2. ステートメント2はUNION ALLでなければならない

最後に、ステートメント1のwhere句を削除すると、どのような結果になるかを推測してください。WHERE句を削除すると、結果セットは毎回、常にチェックされる行のフルセットとなるため、すべてのデッドループとなります。

3. 再帰的アプリケーション2、行から列へを使用する場合

-1. テーブルを作る

ドロップテーブル zxt_test

テーブルzxt_testの作成

( id varchar(10),

ivalue varchar(20)。

名前 varchar(20)

);

コミットします。

select * from zxt_test;

-----------

--- 2.テスト文の挿入

insert into zxt_test values('1','aa','x'),('2','bb','x'),('3','bb','x'),('1','bb','y'),('2','bb','y'),('3','bb','y');

コミットします。

と

s as ( -- ここでは、inameで分割され、idでソートされています。もしテーブルにこのような順次分割された id フィールドがない場合は、 rowNum() を使って連番を生成することができます。

select row_number() over(partition by iname order by id) id1,

row_number() over(partition by iname order by id) id2,

zxt_testのivalue,iname。

t(iname,id1,id2,ivalue) as

(

select iname,id1,id2,cast(ivalue as varchar(100)) from s where id1 =1 and id2=1 -- 文章1

ユニオン・オール

select t.iname,t.id1+1,t.id2,cast(s.ivalue||','||t.ivalue as varchar(100)) --ステートメント2

から s, t

ここで s.id2=t.id1+1 かつ t.iname = s.iname です。

) --ここで、s.iname = t.iname は影響を与えずに削除することができます。

select iname,ivalue from t where t.id1= (select max(id1) from s where s.iname = t.iname); --Statement 3

結果セット

一時テーブルtの内部では、まずステートメント1.を実行して、ループの基礎となるルート結果セットを取得します。注：ステートメント1は一度だけ実行される。

最初のループの文2にtを渡して得られる結果集合は: この時点での組合結果集合の一時表tは。(Vはbean記号を付加した後の値)

INAME t.id1+1 t.id2 V イナメ t.id1 t.id2 V

X 2 1 bb,aa X 1 1 aa

Y 2 1 bb,bb Y 1 1 bb

X 2 1 bb,aa

Y 2 1 bb,bb

2番目のループはステートメント2にtを渡し、結果セットを: この時点での組合の最終結果セットであるテンポラリーテーブルtは:

名前 t.id1+1 t.id2 V イナメ t.id1 t.id2 V

X 3 1 bb,bb,aa X 1 1 aa

Y 3 1 bb,bb,bb Y 1 1 bb

X 2 1 bb,aa

Y 2 1 bb,bb

X 3 1 bb,bb,aa

Y 3 1 bb,bb,bb

ステートメント3に、最終結果セットの一時テーブルtの中でid1の値が最大のレコードを取得する条件を追加する

最終的な希望する行と列の結果セットを取得する。

<スパン例

DB2の行から列へ。(何行あるかは不明)

実装のアイデア、まず再帰的に、そして最初の行を取るようにソートされます。

rs as (select bbd043,row_number() over() RN from bb72 where bae007='10001' ) とする。

RPL(RN,bbd043) as

(

select ROOT.RN,CAST(ROOT.bbd043 as varchar(2000)) from rs ROOT

UNION ALL

SELECT CHILD.RN,CHILD.bbd043||','||PARENT.bbd043 FROM

RPL PARENT,rs CHILD WHERE

parent.rn+1=child.rnです。

)

SELECT MAX(bbd043) bbd043 FROM RPL

group by rn order by rn desc 最初の1行だけを取り出します。

DB2における行間効率の比較

大容量データにも効率的に対応

と

を(

select row_number() over() id1,

row_number() over() id2,

AAE004 from BB20 where AAE004 <> '' ------sql01

t(id1,id2,AAE004) as

(

select id1,id2,AAE004 from s where id1 =1 and id2=1

ユニオン・オール

select t.id1+1,t.id2,cast(s.AAE004||','||t.AAE004 as varchar(20000))

から s, t

ここで、s.id2=t.id1+1です。

)

select AAE004 from t where t.id1= (select max(id1) from s );

効率の悪さとデータ量の多さで敗因となる

を rs as (select AAE004,row_number() over() RN from BB20 where AAE004 <> '' ---- --sql02) とした場合。

としてRPL(RN,AAE004)

(

select ROOT.RN,CAST(ROOT.AAE004 as varchar(20000)) from rs ROOT

UNION ALL

select child.rn,child.aae004||','||parent.aae004 from

RPL PARENT,rs CHILD WHERE

DB2 withの定義と使用方法

最新

nginxです。[emerg] 0.0.0.0:80 への bind() に失敗しました (98: アドレスは既に使用中です)

htmlページでギリシャ文字を使うには

ピュアhtml+cssでの要素読み込み効果

純粋なhtml + cssで五輪を実現するサンプルコード

ナビゲーションバー・ドロップダウンメニューのHTML+CSSサンプルコード

タイピング効果を実現するピュアhtml+css

htmlの選択ボックスのプレースホルダー作成に関する質問

html css3 伸縮しない画像表示効果

トップナビゲーションバーメニュー作成用HTML+CSS

html+css 実装サイバーパンク風ボタン

おすすめ

ハートビート・エフェクトのためのHTML+CSS

HTML ホテルフォームによるフィルタリング

HTML+cssのボックスモデル例（円、半円など）「border-radius」使いやすい

HTMLテーブルのテーブル分割とマージ（colspan, rowspan）

ランダム・ネームドロッパーを実装するためのhtmlサンプルコード

Html階層型ボックスシャドウ効果サンプルコード

QQの一時的なダイアログボックスをポップアップし、友人を追加せずにオンラインで話す効果を達成する方法

sublime / vscodeショートカットHTMLコード生成の実装

HTMLページを縮小した後にスクロールバーを表示するサンプルコード

html のリストボックス、テキストフィールド、ファイルフィールドのコード例