キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
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) | - | - |
                    Firebirdの分析関数の衝撃(11) 「2日前の値」
                    0
                      本エントリはFirebird Advent Calendar 2018 の10日目です。

                      Firebirdの分析関数の衝撃(11) 「2日前の値」


                      5. 「2日前の値」

                      テーブル定義とデータはそのまま使えます。

                      クエリは前回と同様に「prc_date-interval '2 day'」の部分を「dateadd(-2 day to prc_date)」に変更すればよいだけです。

                      -- window関数で書いたSQL(dateaddを使ったFirebird版)
                      select prc_date,prc_amt,
                      case when Lag(prc_date,2) over(order by prc_date) = dateadd(-2 day to prc_date)
                      then Lag(prc_amt,2) over(order by prc_date)
                      when Lag(prc_date) over(order by prc_date) = dateadd(-2 day to prc_date)
                      then Lag(prc_amt) over(order by prc_date)
                      else 0 end as before_prc_amt
                      from Accounts;

                      -- 相関サブクエリで書いたSQL(dateaddを使ったFirebird版)
                      select prc_date,prc_amt,
                      coalesce((select b.prc_amt
                      from Accounts b
                      where b.prc_date = dateadd(-2 day to a.prc_date))
                      ,0) as before_prc_amt
                      from Accounts a;

                      -- 外部結合で書いたSQL(dateaddを使ったFirebird版)
                      select a.prc_date,a.prc_amt,
                      coalesce(b.prc_amt,0) as before_prc_amt
                      from Accounts a Left Join Accounts b
                      on dateadd(-2 day to a.prc_date) = b.prc_date;

                      [それぞれのクエリ結果]
                      PRC_DATE PRC_AMT BEFORE_PRC_AMT
                      =========== ============ ==============
                      2006-10-26 100 0
                      2006-10-28 200 100
                      2006-10-31 400 0
                      2006-11-03 800 0
                      2006-11-04 1600 0
                      2006-11-06 3200 1600
                      2006-11-11 6400 0

                      ここまで書いて「あれ、window関数版はorder byあるけど、相関サブクエリ版や外部結合版はorder byないのでAccountsテーブルがprc_dateでソートされている保障ないじゃん!」と思ったのですが、よくよくそれぞれのクエリをみると、相関サブクエリも外部結合も=で比較しているだけですし、prc_dateは主キーの日付なので問題ないですね。

                      またひとつ前「4. 「2日前からの累計」」のクエリも相関サブクエリ版はbetweenで日付を指定しているだけで、そのbetween内の行順序は保障する必要はないので、これもまた大丈夫でした。

                      JUGEMテーマ:コンピュータ

                      | meijik | Firebird/InterBase | 00:10 | comments(0) | trackbacks(0) | - | - |
                         1234
                      567891011
                      12131415161718
                      19202122232425
                      262728293031 
                      << May 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