SQLクックブックの劔"Tsurugi"1.1.0版

アンソニー・モリナロ、ロバート・デ・グラーフ著「SQLクックブック 第2版」の劔"Tsurugi"版をまとめてみます。各レシピの詳細は書いていません。同書を参照してください。

Tsurugi 1.1.0で動作確認をしていますが、SQLに疎い人が勉強した記録なので、間違いがあったらすみません。

背景

劔"Tsurugi"は、NEDOとNEC、ノーチラス・テクノロジーズが開発した次世代RDBです。

TsurugiのSQLを勉強するために、今更ながら「SQLクックブック 第2版」を買いました。この書籍では、色々なお題に「レシピ」と名付けて、DB2・MySQL・Oracle・PostgreSQL・SQL Serverそれぞれでどのように書けば解決できるか紹介されています。

同書の各レシピについてTsurugiでどう書けるか確認してみました。レシピで紹介されている各DBMSの機能や関数がTsurugiで使えない場合、あまりがんばらずに「書けない」と諦めています。

Tsurugiが使えるSQLについては、公式サイトの Available SQL features in Tsurugi にまとめられています。

1章 レコードの取得

レシピ1.1 テーブルからすべての行と列を取得する

Tsurugiではこう書けます。

select *
    from emp;

レシピ1.2 テーブルから行の一部を取得する

Tsurugiではこう書けます。

select *
    from emp
    where deptno = 10;

レシピ1.3 複数の条件を満たす行を取得する

Tsurugiではこう書けます。

select *
    from emp
    where deptno = 10
        or comm is not null
        or sal <= 2000 and deptno = 20;

レシピ1.4 テーブルから列の一部を取得する

Tsurugiではこう書けます。

select ename, deptno, sal
    from emp;

レシピ1.5 列に意味のある名前を付ける

Tsurugiではこう書けます。

select sal as salary, comm as commission
    from emp;

レシピ1.6 WHERE句の中でエイリアス化された列を参照する

Tsurugiではこう書けます。

select *
    from (
        select sal as salary, comm as commission
            from emp
    ) x
    where salary < 5000;

レシピ1.7 列値を連結する

Tsurugiではこう書けます。

select ename ||' WORKS AS A ' || job as msg
    from emp
    where deptno = 10;

レシピ1.8 SELECT文で条件ロジックを使う

Tsurugiではこう書けます。

select ename, sal,
    case
        when sal <= 2000 then 'UNDERPAID'
        when sal >= 4000 then 'OVERPAID'
        else 'OK'
    end as status
    from emp;

レシピ1.9 返す行数を制限する

Tsurugiではこう書けます。

select *
    from emp
    limit 5;

レシピ1.10 テーブルからn個のランダムなレコードを返す

Tsurugiでは乱数が提供されていないので書けません。

レシピ1.11 nullを探す

Tsurugiではこう書けます。

select *
    from emp
    where comm is null;

レシピ1.12 nullを実際の値に変換する

Tsurugiではこう書けます。

select case
        when comm is not null then comm
        else 0
    end
    from emp;

レシピ1.13 パターンを検索する

TsurugiではLIKEが提供されていないので書けません。
Planned featuresに記載があるので実装待ちです。

2章 クエリ結果のソート

レシピ2.1 クエリ結果を指定の順序で返す

Tsurugiではこう書けます。

select ename, job, sal
    from emp
    where deptno = 10
    order by sal asc;

レシピ2.2 複数のフィールドでソートする

Tsurugiではこう書けます。

select empno, deptno, sal, ename, job
    from emp
    order by deptno, sal desc;

レシピ2.3 部分文字列でソートする

Tsurugiでは部分文字列関数が提供されていないので書けません。

レシピ2.4 英数字の混合データをソートする

TsurugiではTRANSLATE関数が提供されていないので書けません。

レシピ2.5 ソート時にnullを扱う

Tsurugiではこう書けます。

select ename, sal, comm
    from (
        select ename, sal, comm, case
            when comm is null then 0
            else 1
        end as is_null
        from emp
    ) x
    order by is_null desc, comm;

レシピ2.6 データ依存のキーに対してソートする

Tsurugiではこう書けます。

select ename, sal, job, comm
    from emp
    order by case
        when job = 'SALESMAN' then comm
        else sal
    end;

3章 複数テーブルの扱い

レシピ3.1 ある行セットを別の行セットの上にスタックする

Tsurugiではこう書けます。

select ename as ename_and_dname, deptno
    from emp
    where deptno = 10

union all
select '----------', null
    from t1

union all
select dname, deptno
    from dept;

レシピ3.2 関連する行を組み合わせる

Tsurugiではこう書けます。

select e.ename, d.loc
    from emp e, dept d
    where e.deptno = d.deptno
    and e.deptno = 10;

レシピ3.3 2つのテーブルに共通する行を取得する

TsurugiではVIEWが提供されていませんが、同様に定義したテーブル emp_3_3 との差分抽出はこう書けます。

select e.empno, e.ename, e.job, e.sal, e.deptno
    from emp e, emp_3_3 as V
    where e.ename = V.ename
        and e.job = V.job
        and e.sal = V.sal;

レシピ3.4 テーブルAからテーブルBには存在しない値を取得する

Tsurugiではこう書けます。

select deptno from dept
    except
    select deptno from emp;

レシピ3.5 テーブルAからテーブルB内に対応する行がない行を取得する

Tsurugiではこう書けます。

select d.*
    from dept d left outer join emp e
        on (d.deptno = e.deptno)
    where e.deptno is null;

レシピ3.6 他の結合を妨げずにクエリに結合を追加する

Tsurugiではこう書けます。本文にある列番号によるorder by句は、Tsurugiでは提供されていません。

select e.ename, d.loc, eb.received
    from emp e join dept d
        on (e.deptno = d.deptno)
    left join emp_bonus_3_6 eb
        on (e.empno = eb.empno)
    order by d.loc;

レシピ3.7 2つのテーブルが同じデータを持つかどうかを判断する

TsurugiではVIEWが提供されていませんが、同様に定義したテーブル emp_3_7 との差分抽出はこう書けます。

select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
    from emp_3_7
    group by empno, ename, job, mgr, hiredate, sal, comm, deptno
    except
        select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
        from emp
    group by empno, ename, job, mgr, hiredate, sal, comm, deptno

union all
select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
    from emp
    group by empno, ename, job, mgr, hiredate, sal, comm, deptno
    except
        select empno, ename, job, mgr, hiredate, sal, comm, deptno, count(*) as cnt
        from emp_3_7
    group by empno, ename, job, mgr, hiredate, sal, comm, deptno;

レシピ3.8 直積を特定して回避する

Tsurugiではこう書けます。

select e.ename, d.loc
    from emp e, dept d
    where e.deptno = 10
    and d.deptno = e.deptno;

レシピ3.9 集約の使用時に結合を実行する

Tsurugiではsum関数内でdistinctを指定できません。本文中にある .1 というリテラル表現も使えません。
したがって以下のように書きます。

select d.deptno, d.total_sal,
        sum(e.sal * case
            when eb.type = 1 then 0.1
            when eb.type = 2 then 0.2
            else 0.3 end) as total_bonus
    from emp e, emp_bonus_3_9 eb,
        (
            select deptno, sum(sal) as total_sal
                from emp
                where deptno = 10
                group by deptno
        ) d
    where e.deptno = d.deptno
        and e.empno = eb.empno
    group by d.deptno,d.total_sal;

レシピ3.10 集約の使用時に外部結合を実行する

レシピ3.9と同じ理由で、Tsurugiでは以下のように書けます。

select d.deptno, d.total_sal,
        sum(e.sal * case
            when eb.type = 1 then 0.1
            when eb.type = 2 then 0.2
            else 0.3 end) as total_bonus
    from emp e, emp_bonus_3_10 eb,
        (
            select deptno, sum(sal) as total_sal
                from emp
                where deptno = 10
                group by deptno
        ) d
    where e.deptno = d.deptno
         and e.empno = eb.empno
    group by d.deptno,d.total_sal;

レシピ3.11 複数テーブルから欠損データを返す

TsrugiはFULL OUTER JOINが使えるので以下のように書けます。

select d.deptno, d.dname, e.ename
    from dept d full outer join emp_3_11 e
        on (d.deptno=e.deptno);

レシピ3.12 演算や比較でnullを使う

TsurugiはスカラのサブクエリやCOALESCE関数が提供されていないので、以下のように書けます。

select e.ename, e.comm
    from emp e, (
        select comm
            from emp
            where ename = 'WARD'
    ) w
    where e.comm is null or e.comm < w.comm;

4章 挿入、更新、削除

レシピ4.1 新しいレコードを挿入する

Tsurugiではこう書けます。

insert into dept_4_1 (deptno, dname, loc)
    values (50, 'PROGRAMMING', 'BALTIMORE');

レシピ4.2 デフォルト値を挿入する

Tsurugiではこう書けます。

insert into d_4_2 default values;
insert into d_4_2 (id) default values;

以下はTsurugiではエラーSQL-03005になります。

-- VALUE_ANALYZE_EXCEPTION (SQL-03005)
insert into d_4_2 values (default);
-- VALUE_ANALYZE_EXCEPTION (SQL-03005)
insert into d_4_2 (id) values (default);

レシピ4.3 デフォルト値をnullにオーバーライドする

Tsurugiではこう書けます。

insert into d_4_3 (id, name) values (null, 'Brighten');

レシピ4.4 別のテーブルに行をコピーする

Tsurugiではこう書けます。

insert into dept_east_4_4 (deptno, dname, loc)
    select deptno, dname, loc
        from dept
        where loc in ('NEW YORK','BOSTON');

レシピ4.5 テーブル定義をコピーする

TsurugiはCREATE TABLE LIKE文, CREATE TABLE AS文, 新規テーブルへのSELECT INTOに準じる機能が提供されていないので書けません。

レシピ4.6 一度に複数のテーブルに挿入する

TsurugiはINSERT ALL文、CHECK制約が提供されていないので書けません。

レシピ4.7 特定の列への挿入をブロックする

TsurugiではVIEWやCHECK制約が提供されていないので書けません。

レシピ4.8 テーブル内のレコードを変更する

Tsurugiではこう書けます。

update emp_4_8
    set sal = sal * 1.10
    where deptno = 20;

レシピ4.9 対応する行が存在する場合に更新する

TsurugiではIN句中のサブクエリが提供されていないので書けません。
Planned featuresに記載があるので実装待ちです。

レシピ4.10 別のテーブルの値で更新する

TsurugiではUPDATE文中のサブクエリや複数テーブルへのUPDATEが提供されていないので書けません。

レシピ4.11 レコードをマージする

TsurugiではMERGE文やINSERT OR UPDATE文中のDELETE句が提供されていないので書けません。

レシピ4.12 テーブルからすべてのレコードを削除する

Tsurugiではこう書けます。

delete from emp_4_12;

レシピ4.13 特定のレコードを削除する

Tsurugiではこう書けます。

delete from emp_4_13
    where deptno = 10;

レシピ4.14 1つのレコードを削除する

Tsurugiではこう書けます。

delete from emp_4_14
    where empno = 7782;

レシピ4.15 参照整合性違反を削除する

TsurugiではNOT EXISTS式、IN句中のサブクエリが提供されていないので書けません。

レシピ4.16 重複レコードを削除する

TsurugiではIN句中のサブクエリが提供されていないので書けません。

レシピ4.17 他のテーブルから参照されているレコードを削除する

TsurugiではIN句中のサブクエリが提供されていないので書けません。

5章 メタデータクエリ

レシピ5.1 スキーマ内のテーブルの一覧を表示する

TsurugiではSQLで提供されていませんが、こう書けます。

\show table;

レシピ5.2 テーブルの列の一覧を表示する

TsurugiではSQLで提供されていませんが、こう書けます。

\show table emp;

レシピ5.3 テーブルのインデックス付けされた列の一覧を表示する

Tsurugiでは確認する手段が見当たりません。

レシピ5.4 テーブルに対する制約の一覧を表示する

Tsurugiでは確認する手段が見当たりません。

レシピ5.5 対応するインデックスがない外部キーを一覧表示する

Tsurugiでは外部キーが提供されていないので書けません。

レシピ5.6 SQLを使ってSQLを生成する

Tsurugiのテーブル一覧出力機能はSQLによるものではないので書けません。

レシピ5.7 Oracleでデータディクショナリビューを表示する

TsurugiではVIEWが提供されていないので書けません。

6章 文字列

レシピ6.1 文字列を反復処理する

Tsurugiでは部分文字列関数が提供されていないので書けません。

レシピ6.2 文字列リテラル内にクォートを埋め込む

Tsurugiではこう書けます。

select 'g''day mate' qmarks from t1

union all
select 'beavers'' teeth' from t1

union all
select '''' from t1;

レシピ6.3 文字列内の文字の出現回数をカウントする

Tsurugiでは文字列中の文字数を数える関数や、文字列から文字を置換・削除する関数が提供されていないので書けません。

レシピ6.4 文字列から不要な文字を取り除く

Tsurugiでは部分文字列関数や置換・削除する関数が提供されていないので書けません。

レシピ6.5 数値データと文字データを分離する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.6 文字列が英数字かどうかを判別する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.7 名前からイニシャルを抽出する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.8 文字列の一部でソートする

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.9 文字列内の数字でソートする

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.10 テーブル行から区切りリストを作成する

Tsurugiでは文字列連結用の関数が提供されていないので書けません。

レシピ6.11 区切りデータから複数値を持つINリストへ変換する

TsurugiではSPLIT_PARTのような文字列分割関数が提供されていないので書けません。

レシピ6.12 文字列をアルファベット順にする

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.13 数値として扱える文字列を特定する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.14 n番目の区切り部分文字列を抽出する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ6.15 IPアドレスをパースする

TsurugiではSPLIT_PARTのような文字列分割関数が提供されていないので書けません。

レシピ6.16 発音で文字列を比較する

TsurugiではSOUNDEXのような発音に変換する関数が提供されていないので書けません。

レシピ6.17 パターンにマッチしないテキストを探す

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

7章 数値

レシピ7.1 平均値を計算する

Tsurugiではこう書けます。

select avg(sal) as avg_sal
    from emp;
select deptno, avg(sal) as avg_sal
    from emp
    group by deptno;

レシピ7.2 列の最大値と最小値を探す

Tsurugiではこう書けます。

select min(sal) as min_sal, max(sal) as max_sal
    from emp;
select deptno, min(sal) as min_sal, max(sal) as max_sal
    from emp
    group by deptno;

レシピ7.3 列の値を合計する

Tsurugiではこう書けます。

select sum(sal)
    from emp;
select deptno, sum(sal) as total_for_dept
    from emp
    group by deptno;

レシピ7.4 テーブルの行数をカウントする

Tsurugiではこう書けます。

select count(*)
    from emp;
select deptno, count(*)
    from emp
    group by deptno;

レシピ7.5 列の値の数をカウントする

Tsurugiではこう書けます。

select count(comm)
    from emp;

レシピ7.6 累積和を求める

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ7.7 累積積を求める

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ7.8 値を平滑化する

TsurugiではLAGのようなウィンドウ関数が提供されていないので書けません。

レシピ7.9 最頻値を計算する

TsurugiではDENSE_RANK, OVERのようなウインドウ関数が提供されていないので書けません。

レシピ7.10 中央値を計算する

TsurugiではPERCENTILE_CONTのようなウインドウ関数が提供されていないので書けません。

レシピ7.11 総計に対する割合を求める

Tsurugiではこう書けます。

select (sum(case when deptno = 10 then sal end) / sum(sal)) * 100 as pct
    from emp;

レシピ7.12 nullを許容する列を集約する

TsurugiではCOALESCE関数が提供されておらず、NULL値を含むと集約関数がNULL値を返すので書けません。

NULL値を返すのは標準SQLの挙動と異なるので直して欲しいです。

レシピ7.13 最高値と最低値を除いた平均値を計算する

TsurugiではIN句中のサブクエリが提供されていないので書けません。

レシピ7.14 英数字文字列を数字に変換する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ7.15 累積和の値を変更する

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ7.16 中央絶対偏差を使って外れ値を探す

TsurugiではPERCENTILE_CONTのようなウインドウ関数が提供されていないので書けません。

レシピ7.17 ベンフォードの法則を使って異常を特定する

Tsurugiでは共通テーブル式(CTE)、LOG10関数が提供されていないので書けません。

8章 日付の演算

レシピ8.1 日、月、年の加算や減算を行う

Tsurugiでは日付型の演算や日付型の演算を行うための関数が提供されていないので書けません。

レシピ8.2 2つの日付間の日数を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数が提供されていないので書けません。

レシピ8.3 2つの日付間の平日の日数を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数が提供されていないので書けません。

レシピ8.4 2つの日付間の月数や年数を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数が提供されていないので書けません。

レシピ8.5 2つの日付間の秒数、分数、時間数を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数が提供されていないので書けません。

レシピ8.6 1年間の各曜日の出現回数をカウントする

Tsurugiでは日付型から曜日を得るための関数が提供されていないので書けません。

レシピ8.7 現在のレコードと次のレコードの日付の差を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数、OVERのようなウインドウ関数が提供されていないので書けません。

9章 日付の操作

レシピ9.1 ある年がうるう年かどうかを判定する

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.2 1年の日数を計算する

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.3 日付から単位時間を抽出する

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.4 月の最初の日と最終日を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.5 1年の中の特定の曜日のすべての日付を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.6 ある月に特定の曜日が最初と最後に出現する日付を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.7 カレンダーを作成する

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.8 年の四半期の開始日と終了日を表示する

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.9 指定された四半期の開始日と終了日を求める

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.10 欠損日付を埋める

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.11 特定の単位時間を探す

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.12 日付の特定の部分を使ってレコードを比較する

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ9.13 重複する日付範囲を特定する

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

10章 範囲

レシピ10.1 連続した値の範囲を特定する

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ10.2 同じグループやパーティション内の行間の差を求める

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ10.3 連続する値の範囲の最初と最後を求める

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ10.4 値の範囲内の欠損値を埋める

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数、TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ10.5 連続する数値を生成する

Tsurugiでは再帰WITH句やCTE、GENERATE_SERIESのようなレンジを生成する関数が提供されていないので書けません。

11章 高度な検索

レシピ11.1 結果セットをページネーションする

TsurugiではROW_NUMBER OVER関数やOFFSET句などが提供されていないので書けません。

レシピ11.2 テーブルからn行をスキップする

TsurugiではROW_NUMBER OVER関数やOFFSET句などが提供されていないので書けません。

レシピ11.3 外部結合の使用時に論理和ロジックを組み込む

Tsurugiではこう書けます。

select e.ename, d.deptno, d.dname, d.loc
    from dept d left join emp e
        on (d.deptno = e.deptno and (e.deptno=10 or e.deptno=20))
    order by d.deptno;

レシピ11.4 逆関係の行を判別する

Tsurugiではこう書けます。

select distinct v1.*
    from v_11_4 v1, v_11_4 v2
    where v1.test1 = v2.test2
        and v1.test2 = v2.test1
        and v1.test1 <= v1.test2;

レシピ11.5 上位n個のレコードを取得する

TsurugiではROW_NUMBER OVER関数やDENSE_RANK関数などが提供されていませんが、以下のように書けます。

select ename, sal
    from emp
    order by sal desc
    limit 5;

レシピ11.6 最高値と最低値を持つレコードを探す

TsurugiではMIN OVER関数やスカラのサブクエリなどが提供されていないので書けません。

レシピ11.7 先の行を調べる

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ11.8 行の値をシフトする

TsurugiではOVERのようなウインドウ関数が提供されていないので書けません。

レシピ11.9 結果をランク付けする

TsurugiではROW_NUMBER OVER関数やDENSE_RANK関数などが提供されていないので書けません。

レシピ11.10 重複を取り除く

TsurugiではROW_NUMBER OVER関数が提供されていませんが、以下のように書けます。

select distinct job
    from emp;
select job
    from emp
    group by job;

レシピ11.11 ナイト値を探す

TsurugiではMAX OVERのようなウインドウ関数が提供されていないので書けません。

レシピ11.12 簡単な予測を行う

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

12章 レポート作成と形状変換

レシピ12.1 結果セットを1行にピボットする

Tsurugiではこう書けます。

select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
        sum(case when deptno=30 then 1 else 0 end) as deptno_30
    from emp;

レシピ12.2 結果セットを複数行にピボットする

TsurugiではROW_NUMBER OVER関数が提供されていないので書けません。

レシピ12.3 結果セットをアンピボットする

Tsurugiではこう書けます。

select dept.deptno,
    case dept.deptno
        when 10 then v_12_3.deptno_10
        when 20 then v_12_3.deptno_20
        when 30 then v_12_3.deptno_30
    end as counts_by_dept
    from v_12_3 cross join
        (select deptno from dept where deptno <= 30) dept;

レシピ12.4 結果セットを1列にアンピボットする

TsurugiではROW_NUMBER OVER関数が提供されていないので書けません。

レシピ12.5 結果セットから反復値を取り除く

TsurugiではLAG OVER関数が提供されていないので書けません。

レシピ12.6 結果セットをピボットして行をまたいだ計算を容易にする

Tsurugiではこう書けます。SUM関数がNULL値を扱えないので条件が増えています。

select d20_sal - d10_sal as d20_10_diff, d20_sal - d30_sal as d20_30_diff
    from (
        select sum(case
                when deptno=10 and sal is not null then sal
                else 0
            end) as d10_sal,
            sum(case
                when deptno=20 and sal is not null then sal
                else 0
            end) as d20_sal,
            sum(case
                when deptno=30 and sal is not null then sal
                else 0
            end) as d30_sal
        from emp
    ) totals_by_dept;

レシピ12.7 固定長のデータグループを作成する

TsurugiではROW_NUMBER OVER関数が提供されていないので書けません。

レシピ12.8 所定の数のグループを作成する

TsurugiではNTILE_NUMBER OVER関数が提供されていないので書けません。

レシピ12.9 水平ヒストグラムを作成する

TsurugiではREPEATやLPADといった文字列操作関数が提供されていないので書けません。

レシピ12.10 垂直ヒストグラムを作成する

TsurugiではROW_NUMBER OVER関数が提供されていないので書けません。

レシピ12.11 非GROUP BY列を返す

TsurugiではROW_NUMBER OVER関数が提供されていないので書けません。

レシピ12.12 簡単な小計を計算する

TsurugiではGROUP BYにROLLUP拡張機能が提供されていませんが、以下のようには書けます。

select job, sum(sal) sal
    from emp
    group by job

union all
select 'TOTAL', sum(sal)
    from emp;

レシピ12.13 考えられるすべての表現の組み合わせに対する小計を計算する

TsurugiではCUBEやGROUPINGといった文字列操作関数が提供されていませんが、MySQL同様にこう書けます。

select deptno, job,
        'TOTAL BY DEPT AND JOB' as category,
        sum(sal) as sal
    from emp
    group by deptno, job

union all
select null, job, 'TOTAL BY JOB', sum(sal)
    from emp
    group by job

union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
    from emp
    group by deptno

union all
select null,null,'GRAND TOTAL FOR TABLE', sum(sal)
    from emp;

レシピ12.14 小計ではない行を識別する

TsurugiではCUBEやGROUPINGといった文字列操作関数が提供されていないので書けません。

レシピ12.15 CASE式を使って行にフラグを付ける

Tsurugiではこう書けます。カラムの別名を直接ORDER BY句から指定できないので2段構えになります。

select *
    from (
        select
            ename,
            case when job = 'CLERK' then 1 else 0 end as is_clerk,
            case when job = 'SALESMAN' then 1 else 0 end as is_sales,
            case when job = 'MANAGER' then 1 else 0 end as is_mgr,
            case when job = 'ANALYST' then 1 else 0 end as is_analyst,
            case when job = 'PRESIDENT' then 1 else 0 end as is_prez
            from emp
        ) as x
    order by is_clerk, is_sales, is_mgr, is_analyst, is_prez;

レシピ12.16 疎行列を作成する

Tsurugiではこう書けます。

select
        case deptno when 10 then ename end as d10,
        case deptno when 20 then ename end as d20,
        case deptno when 30 then ename end as d30,
        case job when 'CLERK' then ename end as clerks,
        case job when 'MANAGER' then ename end as mgrs,
        case job when 'PRESIDENT' then ename end as prez,
        case job when 'ANALYST' then ename end as anals,
        case job when 'SALESMAN' then ename end as sales
    from emp;

レシピ12.17 単位時間で行をグループ化する

Tsurugiは日付型の演算が提供されず、GROUP BYの対象には列名しか指定できないので書けません。

レシピ12.18 次元が異なるグループやパーティションに対して同時に集約を実行する

TsurugiではCOUNT OVER関数が提供されていないので書けません。

レシピ12.19 値の移動範囲に対して集約を実行する

Tsurugiは日付型の演算やSUM OVER関数、スカラのサブクエリなどが提供されていないので書けません。

レシピ12.20 小計を含む結果セットをピボットする

TsurugiではGROUP BYにROLLUP拡張機能が提供されていないので書けません。

13章 階層クエリ

レシピ13.1 親子関係を表す

Tsurugiではこう書けます。

select a.ename || ' works for ' || b.ename as emps_and_mgrs
    from emp a, emp b
    where a.mgr = b.empno;

レシピ13.2 親子孫関係を表す

Tsurugiでは再帰WITH句やCONNECT BY関数が提供されていないので書けません。

レシピ13.3 テーブルの階層的なビューを作成する

Tsurugiでは再帰WITH句やCONNECT BY関数が提供されていないので書けません。

レシピ13.4 ある親行に対するすべての子行を探す

Tsurugiでは再帰WITH句やCONNECT BY関数が提供されていないので書けません。

レシピ13.5 行がリーフノード、ブランチノード、ルートノードのいずれであるかを判別する

Tsurugiはスカラのサブクエリが提供されていないので書けません。

14章 その他

レシピ14.1 SQL ServerのPIVOT演算子を使ってクロス集計レポートを作成する

TsurugiはPIVOT演算子が提供されていないので書けません。

レシピ14.2 SQL ServerのUNPIVOT演算子を使ってクロス集計レポートをアンピボットする

TsurugiはUNPIVOT演算子が提供されていないので書けません。

レシピ14.3 OracleのMODEL句を使って結果セットを転置する

TsurugiはMODEL句が提供されていないので書けません。

レシピ14.4 任意の場所から文字列要素を抽出する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ14.5 1年の日数を求める（Oracle用の別の解決策）

Tsurugiでは日付型の演算や日付型の演算を行うための関数、年・月・日・時・分・秒・曜日を取得する関数が提供されていないので書けません。

レシピ14.6 英数字の混合文字列を探す

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ14.7 Oracleを使って整数を2進数に変換する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ14.8 ランク付けされた結果セットをピボットする

TsurugiではROW_NUMBER OVER関数やDENSE_RANK関数などが提供されていないので書けません。

レシピ14.9 変換した2つの結果セットに列ヘッダを追加する

TsurugiではCOUNT OVER関数が提供されていないので書けません。

レシピ14.10 Oracleでスカラサブクエリを複合サブクエリに変換する

TsurugiではOBJECT定義機能が提供されていないので書けません。

レシピ14.11 シリアル化されたデータを複数の行に分割する

Tsurugiでは部分文字列関数や置換・削除する関数、パターンマッチする関数が提供されていないので書けません。

レシピ14.12 総計に対する割合を求める

TsurugiではRATIO_TO_REPORT OVER関数が提供されていないので書けません。

レシピ14.13 グループ内の値の存在を調べる

TsurugiではMAX OVER関数やDECODE関数が提供されていないので書けません。

サンプルデータ

動作確認で使ったサンプルデータです。試行錯誤したのでTsurugiでは書けなかったレシピ用のテーブルも含まれます。

DDL

create table emp(
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table dept(
    deptno int primary key,
    dname varchar(10),
    loc varchar(10)
);

create table t1 (id int primary key);
create table t10 (id int primary key);
create table t100 (id int primary key);
create table t500 (id int primary key);

create table emp_3_3(
    ename varchar(10),
    job varchar(10),
    sal double
);

create table emp_bonus_3_6(
    empno int,
    received date,
    type int,
    primary key (empno, received)
);

create table emp_3_7(
    empno int,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table emp_bonus_3_9(
    empno int,
    received date,
    type int,
    primary key (empno, received)
);

create table emp_bonus_3_10(
    empno int,
    received date,
    type int,
    primary key (empno, received)
);

create table emp_3_11(
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table dept_4_1(
    deptno int primary key,
    dname varchar(15),
    loc varchar(10)
);

create table d_4_2 (id integer default 0);
create table d_4_3 (id integer default 0, name VARCHAR(10));

create table dept_east_4_4(
    deptno int primary key,
    dname varchar(10),
    loc varchar(10)
);

create table dept_east_4_6(
    deptno int primary key,
    dname varchar(10),
    loc varchar(10)
);

create table dept_mid_4_6(
    deptno int primary key,
    dname varchar(10),
    loc varchar(10)
);

create table dept_west_4_6(
    deptno int primary key,
    dname varchar(10),
    loc varchar(10)
);

create table emp_4_8 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table emp_4_9 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table emp_4_10 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table new_sal_4_10 (
    deptno int,
    sal double
);

create table emp_commission_4_11 (
    deptno int,
    empno int primary key,
    ename varchar(10),
    comm double
);

create table emp_4_12 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table emp_4_13 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table emp_4_14 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table emp_4_15 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table dupes_4_16 (id integer, name varchar(10));

create table emp_4_17 (
    empno int primary key,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);

create table dept_accidents_4_17 (
    deptno integer,
    accident_name varchar(20)
);

create table v_11_4(test1 int, test2 int);

create table v_12_3(deptno_10 int, deptno_20 int, deptno_30 int);

create table trx_log_12_17(trx_id int, trx_date timestamp, trx_cnt int);

create table it_research_14_9(deptno int, ename varchar(10));
create table it_apps_14_9(deptno int, ename varchar(10));

create table v_14_13(student_id int, test_id int, grade_id int, period_id int, test_date date, pass_fail int);

DML

insert into emp values
    (7369, 'SMITH', 'CLERK', 7902, date'2005-12-16', 800, null, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, date'2006-02-20', 1600, 300, 30),
    (7521, 'WARD', 'SALESMAN', 7698, date'2006-02-22', 1250, 500, 30),
    (7566, 'JONES', 'MANAGER', 7839, date'2006-04-02', 2975, null, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, date'2006-09-28', 1250, 1400, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, date'2006-05-01', 2850, null, 30),
    (7782, 'CLARK', 'MANAGER', 7839, date'2006-07-09', 2450, null, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, date'2007-12-09', 3000, null, 20),
    (7839, 'KING', 'PRESIDENT', null, date'2006-11-17', 5000, null, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, date'2006-09-08', 1500, 0, 30),
    (7876, 'ADAMS', 'CLERK', 7788, date'2008-01-12', 1100, null, 20),
    (7900, 'JAMES', 'CLERK', 7698, date'2006-12-03', 950, null, 30),
    (7902, 'FORD', 'ANALYST', 7566, date'2006-12-03', 3000, null, 20),
    (7934, 'MILLER', 'CLERK', 7782, date'2007-01-23', 1300, null, 10);

insert into dept values
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATION', 'BOSTON');

insert into t1 values (1);
insert into t10 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
insert into t100 select a.id * 10 + b.id from t10 a, t10 b;
insert into t500 select id from t100;
insert into t500 select id + 100 from t100;
insert into t500 select id + 200 from t100;
insert into t500 select id + 300 from t100;
insert into t500 select id + 400 from t100;

insert into emp_3_3
    select ename, job, sal
        from emp
        where job = 'CLERK';

insert into emp_bonus_3_6 values
    (7369, DATE'2015-05-14', 1),
    (7900, DATE'2015-05-14', 2),
    (7788, DATE'2015-05-14', 3);

insert into emp_3_7
    select *
        from emp
        where deptno <> 10
    union all
    select *
        from emp
        where ename = 'WARD';

insert into emp_bonus_3_9 values
    (7934, DATE'2015-03-17', 1),
    (7934, DATE'2015-02-15', 2),
    (7839, DATE'2015-02-15', 3),
    (7782, DATE'2015-02-15', 1);

insert into emp_bonus_3_10 values
    (7934, DATE'2005-05-17', 1),
    (7934, DATE'2005-02-15', 2);

insert into emp_3_11
    select * from emp
    union all
    select 1111, 'YODA', 'JEDI', null, hiredate, sal, comm, null
        from emp
        where ename = 'KING';

insert into emp_4_8
    select * from emp;

insert into emp_4_9
    select * from emp;

insert into emp_4_10
    select * from emp;

insert into new_sal_4_10 values (10, 4000);

insert into emp_commission_4_11 values
    (10, 7782, 'CLARK', null),
    (10, 7839, 'KING', null),
    (10, 7934, 'MILLER', null);

insert into emp_4_12
    select * from emp;

insert into emp_4_13
    select * from emp;

insert into emp_4_14
    select * from emp;

insert into emp_4_15
    select * from emp
    union all
    select 1111, 'YODA', 'JEDI', null, hiredate, sal, comm, null
        from emp
        where ename = 'KING';

insert into dupes_4_16 values
    (1, 'NAPOLEON'),
    (2, 'DYNAMITE'),
    (3, 'DYNAMITE'),
    (4, 'SHE SELLS'),
    (5, 'SEA SHELLS'),
    (6, 'SEA SHELLS'),
    (7, 'SEA SHELLS');

insert into emp_4_17
    select * from emp;

insert into dept_accidents_4_17 values
    (10,'BROKEN FOOT'),
    (10,'FLESH WOUND'),
    (20,'FIRE'),
    (20,'FIRE'),
    (20,'FLOOD'),
    (30,'BRUISED GLUTE');

insert into v_11_4 values
    (20, 20),
    (50, 25),
    (20, 20),
    (60, 30),
    (70, 90),
    (80, 130),
    (90, 70),
    (100, 50),
    (110, 55),
    (120, 60),
    (130, 80),
    (140, 70);

insert into v_12_3 values (3, 5, 6);

insert into trx_log_12_17 values
	(1, TIMESTAMP'2020-07-28 19:03:07', 44),
	(2, TIMESTAMP'2020-07-28 19:03:08', 18),
	(3, TIMESTAMP'2020-07-28 19:03:09', 23),
	(4, TIMESTAMP'2020-07-28 19:03:10', 29),
	(5, TIMESTAMP'2020-07-28 19:03:11', 27),
	(6, TIMESTAMP'2020-07-28 19:03:12', 45),
	(7, TIMESTAMP'2020-07-28 19:03:13', 45),
	(8, TIMESTAMP'2020-07-28 19:03:14', 32),
	(9, TIMESTAMP'2020-07-28 19:03:15', 41),
	(10, TIMESTAMP'2020-07-28 19:03:16', 15),
	(11, TIMESTAMP'2020-07-28 19:03:17', 24),
	(12, TIMESTAMP'2020-07-28 19:03:18', 47),
	(13, TIMESTAMP'2020-07-28 19:03:19', 37),
	(14, TIMESTAMP'2020-07-28 19:03:20', 48),
	(15, TIMESTAMP'2020-07-28 19:03:21', 46),
	(16, TIMESTAMP'2020-07-28 19:03:22', 44),
	(17, TIMESTAMP'2020-07-28 19:03:23', 36),
	(18, TIMESTAMP'2020-07-28 19:03:24', 41),
	(19, TIMESTAMP'2020-07-28 19:03:25', 33),
	(20, TIMESTAMP'2020-07-28 19:03:26', 19);

insert into it_research_14_9 values
    (100, 'HOPKINS'),
    (100, 'JONES'),
    (100, 'TONEY'),
    (200, 'MORALES'),
    (200, 'P.WHITAKER'),
    (200, 'MARCIANO'),
    (200, 'ROBINSON'),
    (300, 'LACY'),
    (300, 'WRIGHT'),
    (300, 'J.TAYLOR');

insert into it_apps_14_9 values
	(400, 'CORRALES'),
	(400, 'MAYWEATHER'),
	(400, 'CASTILLO'),
	(400, 'MARQUEZ'),
	(400, 'MOSLEY'),
	(500, 'GATTI'),
	(500, 'CALZAGHE'),
	(600, 'LAMOTTA'),
	(600, 'HAGLER'),
	(600, 'HEARNS'),
	(600, 'FRAZIER'),
	(700, 'GUINN'),
	(700, 'JUDAH'),
	(700, 'MARGARITO');

insert into v_14_13 values
    (1, 2, 2, 1, DATE'2020-03-01', 1),
    (1, 3, 2, 1, DATE'2020-04-01', 0),
    (1, 4, 2, 2, DATE'2020-05-01', 0),
    (1, 5, 2, 2, DATE'2020-06-01', 0),
    (1, 6, 2, 2, DATE'2020-07-01', 0);

まとめ

Tsurugiはまだ開発途上ということもあって、適用できないレシピが多数ありました。
今後Tsurugiの機能拡張に合わせて更新していきたいと思います。