キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
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) | - | - |
                あなたのDBはINTERSECTとEXCEPTをサポートしているかどうか。Firebird(とMySQL)はしてねえっす。
                0
                  あなたのDBはINTERSECTとEXCEPTをサポートしているかどうか。Firebirdはしてねえっす。

                  本エントリは(遅れていますが)Firebird Advent Calendar 2018の13日目のエントリです。

                  本来は「Firebirdの分析関数の衝撃(12) 2つのテーブルが相等であるかどうか」というエントリを書こうとおもったのですが、その前提(?) にINTERSECTとEXCEPTが必要だったので、それについて先にまとめておきたくて、このエントリになりました。

                  各データベースの集合演算(UNION, INTERSECTION, EXCEPT)の実装度合いはデータベースによって違う(UNION, UNION ALLは全てのデータベースでサポートされている) 割り算(DIVIDE BY的なもの)は、全てのデータベースでサポートされていません。

                  なぜALLがあるのか、とか、割り算がないのか、というところは以下のミックさんのブログ記事を御参照ください。

                  SQLで集合演算

                  各データベースの集合演算のサポート具合で、三つのグループに分けられます。

                  (1) INSERSECTとEXCEPTのどちらもサポートしていない: Firebird, MySQL
                  (2) INSERSECTとEXCEPT(MINUS)のどちらもサポートしているが、ALLをサポートしていない: Oracle, SQLite, SQL Server (2005以降), MariaDB (10.3以降)
                  (3) INSERSECTとEXCEPTもサポートし、それぞれALLもサポートしている: IBM Db2, PostgreSQL

                  # Firebird以外は以下のエントリが参考になります。

                  What’s New in MariaDB 10.3, 5.Intersect and Except

                  FirebirdへのFR(Feature Request)は以下。

                  [#CORE-808] Add support for INTERSECT and EXCEPT data set operators

                  PostgreSQL, IBM Db2以外のRDBMで、
                  (2) でINTERSECT ALL, EXCEPT ALLをシミュレート、
                  (1) で上記に加えてINTERSETC, EXCEPTをシミュレートするには、
                  プログラマのためのSQL 第四版 第34章集合演算(Set Operation)の34.2 INTERSECTとEXCEPTを読むといいです。(最新のSQL for Smarties 5th EditionではCHAPTER 16 SET Operationの16.2 INTERSECT and EXCEPT にあたります)

                  注意すべき点はNULLと値の重複の有無です。これらがなければネットで検索してでてくるようなクエリはほとんど動く(はず)です。例えば対象とする列が主キーであれば、問題なく動くはずです。

                  JUGEMテーマ:コンピュータ



                  | meijik | クロスデータベース | 01:23 | comments(0) | trackbacks(0) | - | - |
                  LibreOffice 6.2 の Firebird
                  0
                    本エントリはFirebird Advent Calendar 2018 の 12日目のエントリです。

                    四年前くらいからなんやかんやいっていたBase (MS OfficeでいうところのAccess的な立ち位置)で
                    Firebirdが使えるようになりました。再度「実験的な機能」からの脱却。

                    DB選択画面

                    バージョンは6.2 Beta このままGAを向かうことを期待しています。そして、そのときにはデフォルトがFirebirdになっていてくれるとありがたいですが。。。。

                    JUGEMテーマ:コンピュータ



                    | meijik | Firebird/InterBase | 23:27 | comments(0) | trackbacks(0) | - | - |
                    [勝手に対応]MySQL8.0の分析関数の2(Lag関数と累計と移動累計)
                    0
                      [勝手に対応]MySQL8.0の分析関数の2(Lag関数と累計と移動累計)

                      本エントリはMySQL Casual Advent Calendar 2018の11日目です。

                      MySQLでは8.0からウインドウ関数に対応しました。これにより商用RDBMS御三家+PostgreSQLが対応していた
                      多くの分析関数・ウインドウ関数が利用できるようになりました。

                      本エントリは以下のエントリの続きです。

                      [勝手に対応]MySQL8.0の分析関数の1(モードとメジアン)


                      1. 部分的に不一致なキーの検索

                      PostgreSQL 8.4でエラーになるクエリはMySQL 8.0でもエラーになります。(PostgreSQL 11 でも同じ)

                      ERROR 1235 (42000): This version of MySQL doesn't yet support '(DISTINCT ..)'

                      これはWindow関数内でDISTINCT を扱えない、実装上の問題です。

                      window関数で書き換えたSQL1,2は動作しますが、
                      window関数で書き換えたSQL3,4はPostgreSQL特有の配列を使ったものですので、PostgreSQL以外は動作しません。

                      [To be contined]


                      JUGEMテーマ:コンピュータ



                      | meijik | MySQL | 23:58 | comments(0) | trackbacks(0) | - | - |
                       123456
                      78910111213
                      14151617181920
                      21222324252627
                      28293031   
                      << July 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
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        meijik (03/03)
                      • 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)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE