キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
2019年の木村明治
0
    2019年の木村明治

    去年はこんな抱負を出していましたが、まぁ2.3.がギリできていたくらいですかね。

    今年の抱負は以下。

    1.新しい本を出す(SQL関連)
    2.ライブにいく、ブログで発信する。
    3.分散処理本の読書会に継続して出る、か?
    4.スケートをまじめに練習する
    5.音楽活動を緩やかに再開

    以下順に、詳細を。。。。

    1.新しい本を出す(SQL関連)
     目黒バイナリ勉強会でSQLに関連する二つの本(プログラマのためのSQL, Effective SQL)を読書会してきましたが、それもそろそろ終わりそうです。これらの読書会を通じてベンダーに依存しないSQLクエリの重要性と、ベンダーごとの差異の明確か、それを利用者に方向性として知らせる情報の少なさを肌で痛感しました。そのちょうどよいタイミングで書籍執筆の話があり、今年の前半は書籍執筆に時間を費やして、その出版を目指します。

    2.ライブにいく、ブログで発信する。
     昨年から、いくつか好きなバンドのライブを継続してみています。今年も平日夜できるだけ参加していくつもりですが、せっかくよいバンド・曲があるので、ブログで紹介していきたいと思います。TWEEDEES赤い公園ねごとyonigeしなまゆ
     しなまゆ対バン(or サポート)つながり?で、ハイエナカーGOOD BYE APRILBARBARSチロルGIVE ME OWTHE SALMON PINKSエルモアスコッティーズ渕上里奈、あたり :)


    3.分散処理本の読書会に継続して出る、か?

    ノーチラステクノロジーの神林さん主催で、複数の読書会が開かれています。
    べた文系の私は毎回異次元ですが w ピースがつながるまで(つながるのか?) もう少し粘ってみようと思います。

    4.スケートをまじめに練習する
     先日、外苑前のスケート場がリニューアルオープンしました。普通には滑れるのですが、スケート習ったことないので、ちょっとウエイティングに申し込んで、通ったらスケートちょっと習ってみたいと思います。

    5.音楽活動を緩やかに再開
     昨年は、自分がもっているカセットテープを整理していて、思いのほか多くのバンドライブ、練習曲、デモテープ、などがでてきました。それれと2.のライブを聞いていると自分でもまたやってみたくなってきました。パートはドラムで、作曲は楽器が弾けないのでコンピュータに頼ることになりますが、まぁ何かアウトプットができるとよい、くらいの緩やかさで開始したいと思います。
     情報処理と同様に音楽の下地はないのですが(笑)高校時代から友人とゲームを作っていて、そのグラフィックと音楽を担当しておりました。(楽器は弾けないのでMMLやなつかしのヤマハ・ミュージックコンピュータを使ってました)
     大学時代もゲームを作っていましたが、広島にいて、留年を含めて(笑) 6年間広島大学のフォークソング同好会でバンドもやっていました。ほとんどコピーでしたが、オリジナル曲もいくつかやっていて、ヤマハのミュージックコンピュータでデモ作成してました。ひとつ上にストリートビーツ、二つか三つくらい上(?) にUNICORNがいました。あと、LORAN浜崎さんのReplicaなど、結構バンド活動盛んでした。
    私がやっていたバンドも、PERSONZや、コレクターズ広島Wodd Streetでの前座をやったりしていました。大学卒業、上京後もがんばってバンド活動やっていましたが、いかんせんドラムの技術不足と、かけれる時間のなさで、結局十年くらい前にすべての活動を終了、手持ちのドラム関連の楽器・パーツもドラムペダル以外は売却してしまいました。

    まぁくたばらないようにゆるゆるがんばりまっす。

    JUGEMテーマ:日記・一般



    | meijik | 日々 | 13:24 | comments(1) | trackbacks(0) | - | - |
    2019年のFirebird
    0
      2019年のFirebird

      本エントリはFirebird Advent Calendar 2018の25日目です。
      (まだいくつかとりこぼしていますが、それはおいおい。。。。)

      さて、2019年のFirebird ! といえば、まずは次版のFirebird 4.0の話になります。

      ....と書いてこれは、去年一昨年のエントリと同じ! うーん。

      ただ、今年は以下のエントリもあり、年内にBeta 1登場の機運が!!

      A Christmas gift is coming? (FirebirdNews)


      Firebirdのいいところ、わるいところ、は昨日のnakagamiさんのエントリに書いてあるとおりで、2019年もこのよいところ、特にLibreOffice6.2Baseのエンジンとなったことで、飛躍の年にしたいですね。

      JUGEMテーマ:コンピュータ



      | meijik | Firebird/InterBase | 00:25 | comments(0) | trackbacks(0) | - | - |
      Firebirdの分析関数の衝撃(15) 全称文を述語で表現する
      0
        Firebirdの分析関数の衝撃(15) 全称文を述語で表現する

        本エントリはFirebird Advent Calendar 2018 の22日目です。
        # もうすでに一週間以上遅れていますが。。。。

        4. 全称文を述語で表現する

        ミックさんの元のクエリ、山岸さんのクエリ(window関数で書き換えたSQL1, group byを使ったSQL1)は動作します。

        その他のクエリはbool_andを利用しているためPostgreSQLしか動作いたしません。
        ポスグレのマニュアルをみると、bool_andはSQL標準のevery集約関数にあたるそうです。
        そのため、もしあなたのRDBMSがevery集約関数をサポートしていれば、bool_andをeveryに置換すれば動作する(はずです)

        9.20. 集約関数(PostgreSQL 10 文書)

        # ポスグレ自体はevery()もサポートしています。

        ただ、Firebirdも含め、ほとんどのRDBMSはまだevery()に対応していません。。。。
        Firebirdで対応しているのは従来のものと、Nakagamiさんが実装してくれたこここらへんのみ、です。

        # JOOQというDBからJavaコードを生成するツールが対応時に説明しています >> every

        A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function(JAVA, SQL AND JOOQ)

        さて、これでFirebird分はなんとか年内に終わりました。
        元ネタとして使わせていただいたミックさん、山岸さんのブログ、プログラマのためのSQL(と読書会に参加していただいた方々)に感謝いたします。来年は読書会がそれぞれ終わるので、また新しい本でSQL界の冒険を継続予定です。ではでは。

        JUGEMテーマ:コンピュータ



        | meijik | Firebird/InterBase | 11:03 | comments(0) | trackbacks(0) | - | - |
        Firebirdの分析関数の衝撃(14) 等しい部分集合を見つける
        0
          Firebirdの分析関数の衝撃(14) 等しい部分集合を見つける

          本エントリはFirebird Advent Calendar 2018 の21日目です。
          # もうすでに一週間以上遅れていますが。。。。

          3. 等しい部分集合を見つける

          Firebirdではバルクインサートが使えないので、以下のようにデータを用意します。
          (textをdomainで定義済みであることを前提としてます。定義していない場合はvarchar, charなどで書き換えてください)

          create table SupParts(
          sup text,
          part text,
          primary key(sup,part));

          insert into SupParts values('A','ボルト');
          insert into SupParts values('A','ナット');
          insert into SupParts values('A','パイプ');
          insert into SupParts values('B','ボルト');
          insert into SupParts values('B','パイプ');
          insert into SupParts values('C','ボルト');
          insert into SupParts values('C','ナット');
          insert into SupParts values('C','パイプ');
          insert into SupParts values('D','ボルト');
          insert into SupParts values('D','パイプ');
          insert into SupParts values('E','ヒューズ');
          insert into SupParts values('E','ナット');
          insert into SupParts values('E','パイプ');
          insert into SupParts values('F','ヒューズ');

          これで、ミックさんの元ネタや、山岸さんのWindows関数での書き換えも動作します。

          ただしPostgreSQLの独自関数array_aggを使用した別解1/2は動作しません。(そりゃそうだ)
          array_aggは行を配列に集約します。以下のブログ記事参照

          PostgreSQLのあまり知られていない型3種

          JUGEMテーマ:コンピュータ



          | meijik | Firebird/InterBase | 03:46 | comments(0) | trackbacks(0) | - | - |
          Firebirdの分析関数の衝撃(13) 関係除算を表現する
          0
            Firebirdの分析関数の衝撃(13) 関係除算を表現する

            本エントリはFirebird Advent Calendar 2018 の19日目です。

            2. 関係除算を表現する

            ミックさんの以下のエントリにあるように関係除算の標準的な定義はありません。

            注意4:除算の標準的な定義がない

            そのため、プログラマのためのSQL 第4版にあるようにさまざまな方法が考えられます。
            「27.2 関係除算」参照のこと。ここでも書かれているように関係除算には二種類あります。
            剰余を持った除算と、厳密な除算で、今回のエントリが対象とするのは前者です。

            EXCEPTを使ったものはFirebird(MySQLも)は扱えないので、山岸さんの考えたWindow関数版を動作させますが、この二つともにFirebird/MySQLで動作可能です。

            -- window関数で書き換えたSQL(Skillsテーブルが空集合の場合に非対応)

            select a.Emp
            from EmpSkills a,(select Skill,
            count(*) over() as SkillCount
            from Skills) b
            where a.Skill = b.Skill
            group by a.Emp,b.SkillCount
            having count(*) = b.SkillCount;


            -- window関数で書き換えたSQL(Skillsテーブルが空集合の場合に対応)

            select a.Emp
            from EmpSkills a
            Left Join (select Skill,
            count(*) over() as needCount
            from Skills) b
            on 1=1
            group by a.Emp,b.needCount
            having count(case when a.Skill=b.Skill then 1 end)
            = coalesce(b.needCount,0);

            EMP
            ====================
            神崎
            相田

            JUGEMテーマ:コンピュータ



            | meijik | Firebird/InterBase | 00:17 | comments(0) | trackbacks(0) | - | - |
            Firebirdの分析関数の衝撃(12) 2つのテーブルが相等であるかどうか
            0
              Firebirdの分析関数の衝撃(12) 2つのテーブルが相等であるかどうか

              本エントリはFirebird Advent Calendar 2018 18日目のエントリです。

              1. 2つのテーブルが相等なら「等しい」、そうでなければ「異なる」を返すクエリ

              INTERSECTとEXCEPTがないFirebird(とMySQL)にとっては、この書き換えは迷宮のようなわけのわからなさになることうけあいです。そのため、以下のように一番短い(そのように見える)以下のクエリを題材にしましょう。

              -- window関数で書き換えたSQL
              select case when count(*) = 0
              then '等しい'
              else '異なる' end as result
              from (select count(*) over(),* from tbl_A
              except all
              select count(*) over(),* from tbl_B) a;

              [Firebird版の完成系はこちら]
              -- CTEに複数の定義を書いて、そのサブクエリ化
              select case when count(*) = 0
              then '等しい'
              else '異なる' end as result
              from
              (
              -- S1 EXCEPT ALL S2(Firebird with CTEバージョン)
              WITH RECURSIVE Series AS
              (
              SELECT 1 AS seq from rdb$database
              UNION ALL
              SELECT 1+seq FROM Series where seq < 8
              ),
              S1 AS (select count(*) over() c, tbl_A.* from tbl_A),
              S2 AS (select count(*) over() c, tbl_B.* from tbl_B)
              SELECT D2.a
              FROM (SELECT D1.a, SUM(cnt)
              FROM (SELECT a1, COUNT(*)
              FROM S1
              GROUP BY a1
              UNION ALL
              SELECT a2, -COUNT(*)
              FROM S2
              GROUP BY a2) AS D1(a, cnt)
              GROUP BY D1.a
              HAVING SUM(cnt) > 0) AS D2(a, dups)
              INNER JOIN
              Series ON seq <= D2.dups
              ) a;

              元のクエリとは当然大きく異なるのですが、Firebird版では以下の変更が必要です。
              (1) count(*) over()にカラム名が必要。ここではcとした。つけないと以下のエラー
              Statement failed, SQLSTATE = 42000
              Dynamic SQL Error
              -SQL error code = -104
              -Invalid command
              -no column name specified for column number 1 in derived table S1
              (2) (1)に続く*ではエラー。テーフル名.*の表記が必要。

              なお、MySQL版では(1)は不要ですがオリジナルのクエリと比較して(2)と以下(3)が必要。
              (3) テーブルの別名にASが必要。すなわちクエリの最後が) as a;である必要があり。

              また

              (4) SELECT 1 AS seq from rdb$databaseのfrom以下が不要、なのはご存知のとおり。

              以下、[MySQL版]のクエリを組み立てながら説明します。

              "except all"については、先日のブログでとりあげましたよね。
              select count(*) over(),* from tbl_AにS1という名称をつけて、
              select count(*) over(),* from tbl_BにS2という名称をつけて、
              fromの中を(S1 except all S2)という形にすると、先日のブログと同じように処理できそうです。

              さて、どうやって名前をつけましょうか? 実はCTEはカンマで繋いで、複数定義できます!

              with — Organize Complex Queries

              なので、以下のようにexcept allを表現できます。

              -- S1 EXCEPT ALL S2(MySQL with CTEバージョン)
              WITH RECURSIVE Series AS
              (
              SELECT 1 AS seq -- from rdb$database
              UNION ALL
              SELECT 1+seq FROM Series where seq < 8
              ),
              S1 AS (select count(*) over(), tbl_A.* from tbl_A),
              S2 AS (select count(*) over(), tbl_B.* from tbl_B)

              SELECT D2.a
              FROM (SELECT D1.a, SUM(cnt)
              FROM (SELECT a1, COUNT(*)
              FROM S1
              GROUP BY a1
              UNION ALL
              SELECT a2, -COUNT(*)
              FROM S2
              GROUP BY a2) AS D1(a, cnt)
              GROUP BY D1.a
              HAVING SUM(cnt) > 0) AS D2(a, dups)
              INNER JOIN
              Series ON seq <= D2.dups;

              これに使うtbl_A, tbl_Bを用意します。keyをa1,a2に変更して、同一のテーブルを生成します。

              create table tbl_A(
              a1 char(1) not null primary key,
              col_1 int,
              col_2 int,
              col_3 int);

              insert into tbl_A values('A',2,3,4);
              insert into tbl_A values('B',0,7,9);
              insert into tbl_A values('C',5,1,6);

              create table tbl_B(
              a2 char(1) not null primary key,
              col_1 int,
              col_2 int,
              col_3 int);

              insert into tbl_B select * from tbl_A;
              commit;

              これでCTEの戻り値はEmptyです。(tbl_A, tbl_Bは同一)

              元のクエリが(S1 except all S2)をサブクエリにしていたので、同様に上のCTEもサブクエリにしてみます。

              [MySQL版の完成系はこちら]
              -- CTEに複数の定義を書いて、そのサブクエリ化
              select case when count(*) = 0
              then '等しい'
              else '異なる' end as result
              from
              (
              -- S1 EXCEPT ALL S2(MySQL with CTEバージョン)
              WITH RECURSIVE Series AS
              (
              SELECT 1 AS seq -- from rdb$database
              UNION ALL
              SELECT 1+seq FROM Series where seq < 8
              ),
              S1 AS (select count(*) over(), tbl_A.* from tbl_A),
              S2 AS (select count(*) over(), tbl_B.* from tbl_B)
              SELECT D2.a
              FROM (SELECT D1.a, SUM(cnt)
              FROM (SELECT a1, COUNT(*)
              FROM S1
              GROUP BY a1
              UNION ALL
              SELECT a2, -COUNT(*)
              FROM S2
              GROUP BY a2) AS D1(a, cnt)
              GROUP BY D1.a
              HAVING SUM(cnt) > 0) AS D2(a, dups)
              INNER JOIN
              Series ON seq <= D2.dups
              ) as a;

              +-----------+
              | result |
              +-----------+
              | 等しい |
              +-----------+
              1 row in set (0.00 sec)

              一行増やして
              insert into tbl_A values('X',1,2,3); commit;

              再実行すると、この結果
              +-----------+
              | result |
              +-----------+
              | 異なる |
              +-----------+
              1 row in set (0.00 sec)

              JUGEMテーマ:コンピュータ



              | meijik | Firebird/InterBase | 00:43 | comments(0) | trackbacks(0) | - | - |
              Windows上で、SQLiteのGeopolyを使う(ということはMSVCでコンパイルするのねん)
              0
                Windows上で、SQLiteのGeopolyを使う(ということはMSVCでコンパイルするのねん)

                本エントリはRDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2018
                SQLite Advent Calendar 2018の17日目です。

                SQLiteでGIS ! ということで、SQLiteの3.25.0以降でサポートされたGeopolyを試そうと思ったのですが、標準のバイナリにはなくコンパイルが必要、ということでSpatiaLiteでお茶を濁そうと思っていましたが、ぼーっとしているうちに、先にエントリが公開されてしまいましたので、
                初心に戻り、コンパイルすることにしました。しかも、Windows上で。。。。


                1.コンパイル環境の構築
                うーん、ここらへんをみるとインストールできるんですかね? オプションでちゃんとCやC++がインストールされれば大丈夫だと思います。

                Visual Studio 2017 のインストール

                 以下のブログでも少しふれましたが従来のExpress Editionに加えPro相当のCommunity Editionも出たので、Windows上の開発環境は相当楽に構築・運用できるようになりました。

                2015年のFirebird

                2.ソースのダウンロード
                以下のページをみて進めていきます。

                How To Compile SQLite

                 ソースは以下からアマルガム(歯のつめもので有名 w)をダウンロード。

                SQLite Download Page

                 現在の最新はsqlite-amalgamation-3260000.zip(2.18 MiB)なので、これをダウンロードして解凍します。
                以下のようにソースファイル四つのシンプルなものがダウンロードできます。

                C:¥Users¥mekimura¥Downloads¥sqlite-amalgamation-3260000 のディレクトリ

                2018/12/30 12:46 .
                2018/12/30 12:46 ..
                2018/12/01 22:38 504,837 shell.c
                2018/12/01 22:38 7,770,073 sqlite3.c
                2018/12/01 22:38 556,318 sqlite3.h
                2018/12/01 22:38 33,713 sqlite3ext.h
                4 個のファイル 8,864,941 バイト
                2 個のディレクトリ 10,993,639,424 バイトの空き領域

                3.コンパイル
                 上記ディレクトリでまずclと打ってみます。

                C:¥Users¥mekimura¥Downloads¥sqlite-amalgamation-3260000>cl
                Microsoft (R) C/C++ Optimizing Compiler Version 19.00.24215.1 for x86
                Copyright (C) Microsoft Corporation. All rights reserved.

                usage: cl [ option... ] filename... [ /link linkoption... ]

                このようにでればコンパイラは動きそうです。

                いきなり実行すると以下のエラー。おお標準ライブラリとか環境変数で設定してなかった!

                C:¥Users¥mekimura¥Downloads¥sqlite-amalgamation-3260000>cl shell.c sqlite3.c -Fesqlite3.exe
                Microsoft (R) C/C++ Optimizing Compiler Version 19.00.24215.1 for x86
                Copyright (C) Microsoft Corporation. All rights reserved.

                shell.c
                shell.c(77): fatal error C1034: stdlib.h: no include path set
                sqlite3.c
                sqlite3.c(1074): fatal error C1034: stdarg.h: no include path set
                Generating Code...

                ああ、後は以下をみればいいのかな?

                チュートリアル: コマンドラインでの C プログラムをコンパイルします。

                メニューから開発コマンドプロンプト(Developper Command Prompt)を開きます。そこから実行すればいろいろよしなに設定してくれているはずなので、再度実行。

                C:¥Users¥mekimura¥Downloads¥sqlite-amalgamation-3260000>cl shell.c sqlite3.c -Fesqlite3.exe
                Microsoft (R) C/C++ Optimizing Compiler Version 19.00.24215.1 for x86
                Copyright (C) Microsoft Corporation. All rights reserved.

                shell.c
                sqlite3.c
                Generating Code...
                Microsoft (R) Incremental Linker Version 14.00.24215.1
                Copyright (C) Microsoft Corporation. All rights reserved.

                /out:sqlite3.exe
                shell.obj
                sqlite3.obj

                これでexeができました!

                2018/12/30 12:57 999,424 sqlite3.exe

                けど、このままではオリジナルと同じですね :) Geopolyを使うためには、それようのオプション指定が必要です。

                cl /DSQLITE_ENABLE_GEOPOLY /DSQLITE_ENABLE_RTREE shell.c sqlite3.c -Fesqlite3.exe

                4.実行
                このバイナリで「CREATE VIRTUAL TABLE newtab USING geopoly(a,b,c);」を実行すると、以前は以下のようなつれない返事だったものが、

                Error: no such module: geopoly

                そのまま受け入れてもらえます。やったね! これでいろいろ試せますね!

                The Geopoly Interface To The SQLite R*Tree Module


                JUGEMテーマ:コンピュータ



                | meijik | SQLite | 13:54 | comments(0) | trackbacks(0) | - | - |
                NULLと重複行に対するEXCEPT ALL
                0
                  NULLと重複行に対するEXCEPT ALL

                  本エントリはFirebird Advent Calendar 2018の16日目です。

                  NULLと重複行に対するINTERSECT, INTERSECT ALLは説明しました。
                  では、EXCEPT ALLはどうでしょうか?

                  これも「プログラマのためのSQL 第4版 34.2.2 NULLと重複行に対するINTERSECTとEXCEPT」という部分に以下の回答があります。これの先頭に六行CTEの定義をつければ、そのまま動作します。(前回のブログと同じ)

                  -- S1 EXCEPT ALL S2(Firebird with CTEバージョン)
                  WITH RECURSIVE Series AS
                  (
                  SELECT 1 AS seq from rdb$database
                  UNION ALL
                  SELECT 1+seq FROM Series where seq < 8
                  )
                  SELECT D2.a
                  FROM (SELECT D1.a, SUM(cnt)
                  FROM (SELECT a1, COUNT(*)
                  FROM S1
                  GROUP BY a1
                  UNION ALL
                  SELECT a2, -COUNT(*)
                  FROM S2
                  GROUP BY a2) AS D1(a, cnt)
                  GROUP BY D1.a
                  HAVING SUM(cnt) > 0) AS D2(a, dups)
                  INNER JOIN
                  Series ON seq <= D2.dups;

                  ちなみにMySQL 8.0では、以下のように初期行セットのFROM句以下を省けば動作します。

                  -- S1 EXCEPT ALL S2(MySQL with CTEバージョン)
                  WITH RECURSIVE Series AS
                  (
                  SELECT 1 AS seq -- from rdb$database
                  UNION ALL
                  SELECT 1+seq FROM Series where seq < 8
                  )
                  SELECT D2.a
                  FROM (SELECT D1.a, SUM(cnt)
                  FROM (SELECT a1, COUNT(*)
                  FROM S1
                  GROUP BY a1
                  UNION ALL
                  SELECT a2, -COUNT(*)
                  FROM S2
                  GROUP BY a2) AS D1(a, cnt)
                  GROUP BY D1.a
                  HAVING SUM(cnt) > 0) AS D2(a, dups)
                  INNER JOIN
                  Series ON seq <= D2.dups;

                  ところでCTEによる再帰RECURSIVEは、実装上の再帰上限があります。
                  「SELECT 1+seq FROM Series where seq < 8」の8の部分を大きくしていくとそれぞれ以下の値でエラーになります。

                  Firebird : 1025
                  MySQL : 1001

                  それぞれ以下のエラーになります。
                  [Firebird]
                  Statement failed, SQLSTATE = 54001
                  Too many concurrent executions of the same request

                  [MySQL]
                  ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger

                  value.

                  MySQLはエラーのとおり以下の変数を大きくすれば大丈夫です。最大値は 4294967295 !

                  mysql> select @@cte_max_recursion_depth;
                  +---------------------------+
                  | @@cte_max_recursion_depth |
                  +---------------------------+
                  | 1000 |
                  +---------------------------+
                  1 row in set (0.00 sec)

                  えっとFirebirdは、もしかして。。。。Firebird 2.1のまま1024がハードコード上限?(がくぶる)

                  Recursion Limit: Currently the recursion depth is limited to a hard-coded value of 1024.

                  以上です。。。。あれ「S1 EXCEPT S2」が無いぞ→プログラマのためのSQL

                  JUGEMテーマ:コンピュータ



                  | meijik | Firebird/InterBase | 11:12 | comments(0) | trackbacks(0) | - | - |
                  NULLと重複行に対するINTERSECT ALL
                  0
                    NULLと重複行に対するINTERSECT ALL

                    エントリはFirebird Advent Calendar 2018の15日目です。

                    前日のエントリでNULLや重複行が「ある」場合にも対応したINTERSECTを紹介しました。

                    では、INTERSECT ALLはどうでしょう?

                    やはり「プログラマのためのSQL 第4版 34.2.2 NULLと重複行に対するINTERSECTとEXCEPT」という部分に以下の回答があります。

                    -- S1 INTERSECT ALL S2
                    SELECT D2.a
                    FROM (SELECT D1.a, MIN(cnt) AS mincnt
                    FROM (SELECT a1, COUNT(*)
                    FROM S1
                    GROUP BY a1
                    UNION ALL
                    SELECT a2, COUNT(*)
                    FROM S2
                    GROUP BY a2) AS D1(a, cnt)
                    GROUP BY D1.a
                    HAVING COUNT(*) > 1) AS D2
                    INNER JOIN
                    Series
                    ON seq <= mincnt;

                    これで答えは求まるのですが、あらかじめSeriesというテーブルを内容{1,2,3,4,....n}で、それぞれのテーブルの行数最大値nで用意しておく必要があります。

                    まぁ、このような内容のテーブルはCTE(Common Table Expression)の再帰で気軽に作成できるので、今回のようにそれぞれ7行とわかっている場合には以下のように頭に6行(WITH RECURSIVE...())つけて、CTEの再帰クエリでSeries(cnt)をつくれば、あらかじめSeriesというテーブルを追加せずに求めることができます。

                    WITH RECURSIVE Series AS
                    (
                    SELECT 1 AS seq from rdb$database
                    UNION ALL
                    SELECT 1+seq FROM Series WHERE seq < 8
                    )
                    SELECT D2.a
                    FROM (SELECT D1.a, MIN(cnt) AS mincnt
                    FROM (SELECT a1, COUNT(*)
                    FROM S1
                    GROUP BY a1
                    UNION ALL
                    SELECT a2, COUNT(*)
                    FROM S2
                    GROUP BY a2) AS D1(a, cnt)
                    GROUP BY D1.a
                    HAVING COUNT(*) > 1) AS D2
                    INNER JOIN
                    Series
                    ON seq <= mincnt;

                    A
                    ======

                    a
                    b
                    b
                    c

                    ご参考まで。なおNULLが頭にくるのはFirebirdの仕様です。詳細は以下のマニュアルをご参照ください。

                    使い慣れたSQLに潜む実装依存: Firebirdの場合(10) ORDER BY でのNULLの扱い

                    まぁ今回の場合は明示的にORDER BYがないので、単純にGROUP BYで暗黙のソートがされた結果というわけです。

                    JUGEMテーマ:コンピュータ



                    | meijik | Firebird/InterBase | 00:34 | comments(0) | trackbacks(0) | - | - |
                    NULLと重複行に対するINTERSECT
                    0
                      NULLと重複行に対するINTERSECT

                      エントリはFirebird Advent Calendar 2018の14日目です。

                      前日のエントリでFirebird(とMySQLも)INTERSECTとEXCEPTは対応していないことと、NULLや重複行がない場合はググればでてくるようなクエリでだいたい大丈夫ということを述べました。

                      では、NULLや重複行が「ある」場合はどうでしょう?

                      一つの解は「プログラマのためのSQL 第四版 日本語版」の「34.2.2 NULLと重複行に対するINTERSECTとEXCEPT」に掲載されているイツァック・ベンガン(itzik ben-gan)によるものがあります。

                      -- 利用するテーブル
                      CREATE TABLE S1(a1 CHAR(1));
                      INSERT INTO S1 VALUES('a');
                      INSERT INTO S1 VALUES('a');
                      INSERT INTO S1 VALUES('b');
                      INSERT INTO S1 VALUES('b');
                      INSERT INTO S1 VALUES('c');
                      INSERT INTO S1 VALUES(NULL);
                      INSERT INTO S1 VALUES(NULL);

                      CREATE TABLE S2 (a2 CHAR(1));
                      INSERT INTO S2 VALUES('a');
                      INSERT INTO S2 VALUES('b');
                      INSERT INTO S2 VALUES('b');
                      INSERT INTO S2 VALUES('b');
                      INSERT INTO S2 VALUES('c');
                      INSERT INTO S2 VALUES('d');
                      INSERT INTO S2 VALUES(NULL);
                      COMMIT;

                      上記のように重複やNULLが入ったテーブルS1とS2のINTERSECTを取るときには、重複やNULLの考慮がないクエリだと正しい結果がかえりません。

                      S1 INTERSECT S2の場合、望ましい結果は以下になります。

                      a
                      b
                      c
                      NULL

                      例えば"firebird intersect"で検索すると上位にでる以下のエントリの場合、

                      Emulate INTERSECT and EXCEPT where they aren't natively supported #3581

                      select distinct
                      S1.*
                      from
                      S1
                      inner join S2 on S1.a1 = S2.a2;

                      結果はNULLのないものとなってしまいます。

                      a
                      b
                      c

                      # INTERSECTなので、S1 INTERSECT S2, S2 INTERSECT S1はどちらも同じ結果になる
                      Firebirdは本にのっている以下のクエリで結果がでます。

                      -- S1 INTERSECT S2(オリジナル)
                      SELECT D.a
                      FROM (SELECT DISTINCT a1 FROM S1
                      UNION ALL
                      SELECT DISTINCT a2 FROM S2) AS D(a)
                      GROUP BY D.a
                      HAVING COUNT(*) > 1;

                      A1
                      ======

                      a
                      b
                      c

                      ちなみにオリジナルのままではMySQLエラーになるので、エラーにならないバージョンは以下。

                      -- S1 INTERSECT S2 (MySQLでエラーにならないバージョン)
                      SELECT D.a1
                      FROM (SELECT DISTINCT a1 FROM S1
                      UNION ALL
                      SELECT DISTINCT a2 FROM S2) AS D
                      GROUP BY D.a1
                      HAVING COUNT(*) > 1;



                      | meijik | Firebird/InterBase | 22:19 | comments(0) | trackbacks(0) | - | - |
                        12345
                      6789101112
                      13141516171819
                      20212223242526
                      2728293031  
                      << January 2019 >>
                      + RECOMMEND
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊 (JUGEMレビュー »)

                      100人のうちの一人としてミックさんの本を推薦しています。
                      + RECOMMEND
                      MySQL 5.1 Plugin Development
                      MySQL 5.1 Plugin Development (JUGEMレビュー »)
                      Andrew Hutchings,Sergei Golubchik
                      MySQL 5.1 のプラグインを作るならこれ!
                      + RECOMMEND
                      AWKを256倍使うための本 (Ascii 256倍)
                      AWKを256倍使うための本 (Ascii 256倍) (JUGEMレビュー »)
                      志村 拓,鷲北 賢,西村 克信
                      ここにきてまさかの復刊。15〜16年前の本ですが、いまでも十分役立ちますよ!!
                      + RECOMMEND
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION)
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION) (JUGEMレビュー »)
                      松信 嘉範
                      Linux-DB システム構築の新スタンダード。DB Magazine人気連載、待望の書籍化です!!
                      + RECOMMEND
                      + RECOMMEND
                      超・極める!MySQL
                      超・極める!MySQL (JUGEMレビュー »)
                      坂井 恵,志村 和彦,ひろせ まさあき,松信 嘉範
                      内容は少々古くなりましたが、内部説明があり、いろいろ面白いです。
                      + RECOMMEND
                      The Root of .NET Framework
                      The Root of .NET Framework (JUGEMレビュー »)
                      荒井 省三
                      .NETの根っこをつかむ、か?
                      + RECOMMEND
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド (JUGEMレビュー »)
                      奥野 幹也
                      これまでにない構築、そして構築後のMySQLのための本。
                      + RECOMMEND
                      Database Management Systems
                      Database Management Systems (JUGEMレビュー »)
                      Raghu Ramakrishnan,Johannes Gehrke
                      ミックさん連載の参考書籍です。
                      + RECOMMEND
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series)
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series) (JUGEMレビュー »)
                      W. Richard Stevens,Stephen A. Rago
                      必読! といいつつ私が読んだのは初版の日本語訳です。差分はチェックしないといけませんね。
                      + RECOMMEND
                      + RECOMMEND
                      + RECOMMEND
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE (JUGEMレビュー »)

                      DELL SX260の換装用に購入。今はコチラのほうが安いですね。
                      + RECOMMEND
                      情熱プログラマー ソフトウェア開発者の幸せな生き方
                      情熱プログラマー ソフトウェア開発者の幸せな生き方 (JUGEMレビュー »)
                      Chad Fowler
                      OSC2010神戸に出展されたジュンク堂で買いました。
                      + RECOMMEND
                      Software Design 総集編 【2000~2009】(DVD付)
                      Software Design 総集編 【2000~2009】(DVD付) (JUGEMレビュー »)

                      メガトン級の過去記事。売り切れる前にお店に急げ!
                      + RECOMMEND
                      + RECOMMEND
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2 (JUGEMレビュー »)

                      Aspire 1410 HDD換装後、昔のHDDはこれにいれます。
                      + RECOMMEND
                      + RECOMMEND
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22 (JUGEMレビュー »)

                      色違い黒(他に青と白があります): メモリは最大4GBとあるが、がんばれば8GBいけるらしい。
                      + RECOMMEND
                      Firebird 徹底入門
                      Firebird 徹底入門 (JUGEMレビュー »)
                      木村 明治,はやし つとむ,坂井 恵
                      Firebird日本ユーザ会のはやしさん、そしてMyNAから拝借(?) してきた坂井さんとともに書きました。まだ誰も知らないFirebird 2.5や、いままでドキュメントのなかったツール類についてもソースを確認してとりあげた本です。是非ご活用ください。
                      + SELECTED ENTRIES
                      + RECENT COMMENTS
                      • 2019年の木村明治
                        田中尚 (01/08)
                      • 使い慣れたSQLに潜む実装依存: Firebirdの場合(6) 文字列との結合演算子
                        noname (12/10)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        Moriaki_ (01/05)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        Moriaki_ (01/05)
                      • 2015年版 InterBaseの歩み
                        nakagami (12/06)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        meijik (11/22)
                      • 分散DB本読書会第46回メモ「STOP AFTER 10」
                        masudahidehiko (09/01)
                      • Windows上でのMySQLビルド方法
                        kinoyasu (03/31)
                      • 気軽に新しいMySQLを試してみる: MySQL Sandboxのススメ
                        MeijiK (09/19)
                      • 伝わらない曲シリーズ: 川島恵「ミスター不思議」
                        96 (07/05)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE