キムラデービーブログ

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

    DISTINCT, COUNT, そしてNULL

    唐突ですが、次の文章のうち間違っているものは? SELECT文で。。。。

    (1) 行数を数えるときにはCOUNT(*)
    (2) 行数を数えるときにはCOUNT(カラム名)でも(1)と同じ
    (3) カラムのユニークな行数を数えるときはCOUNT(DISTINCT カラム名)
    (4) 複数カラムのユニークな行数を数えるときはCOUNT(DISTINCT カラム名1, カラム名2)

    じゃ確認してみましょう。

    create table t1(i1 int, i2 int);
    insert into t1 values(1,1);
    insert into t1 values(1,2);
    insert into t1 values(2,3);
    select count(*) from t1;

    COUNT
    =====================
    3

    select count(i1) from t1;

    COUNT
    =====================
    3

    (1),(2)ともによさそうに見えますね。。。。ってちょっと待ったー! もう一行nullを含む行を追加してみましょう。

    insert into t1 values(null,3);

    select count(*) from t1;

    COUNT
    =====================
    4

    select count(i1) from t1;

    COUNT
    =====================
    3

    そう、実は集約関数はNULLを除外します。ただし「COUNT(*)」は例外的にNULLを除外しないのです。
    そのため、上記のような違いが現れます。Firebirdに限らず、標準SQLに準拠しているものはすべてそうなります。

    SQLのCOUNT(*)とCOUNT(列名)では結果が異なる(山本隆の開発日誌)

    select count(distinct i1) from t1;

    COUNT
    =====================
    2

    これはまず、distinctで1,2,nullの三行になり、count(列名)はnullを除外するので、2となります。
    これはcountを除いた以下のクエリで一目両全です。(3)はOKそうですね。

    select distinct i1 from t1;

    I1
    ============
    <null>
    1
    2

    次に(4)にトライしてみると。。。。エラーですね。

    select count(distinct i1,i2) from t1;
    Statement failed, SQLSTATE = 42000
    Dynamic SQL Error
    -SQL error code = -104
    -Token unknown - line 1, column 25
    -,

    そう、Firebirdを含めたほとんどのRDBMSではCOUNT(DISTINCT マルチカラム)は許されていないのです。
    そのため、よく代案として利用されるのが、DISTINCTをサブクエリにした以下のクエリや、DISTINCTの代わりにGROUP BYを使うものです。

    select count(*) from (select distinct i1,i2 from t1);

    COUNT
    =====================
    4

    もう一つはカラムを、そのカラムに出てこないデリミタを入れてCONCATするものです。

    select count(distinct i1 || '-' || i2) from t1;

    COUNT
    =====================
    3

    OK, って一行減ってますね。。。。これもcountを除くと意味がわかります。

    select distinct i1 || '-' || i2 from t1;

    CONCATENATION
    =======================
    <null>
    1-1
    1-2
    2-2

    NULLをCONCATするとNULLになり、それはCOUNTに計上されません。そのため巷のDISTINCTをサブクエリに
    する方法は、データにNULLがあると正しくないのです。厳密にいうと、こうなります。

    select count(*) from (select distinct i1,i2 from t1 where i1 is not null and i2 is not null);

    COUNT
    =====================
    3

    本ブログエントリの最初のほうで「ほとんどのRDBMSではCOUNT(DISTINCT マルチカラム)は許されていない」
    と記述しましたが、唯一許されているのがMySQLです。

    create table t1(i1 int, i2 int);
    insert into t1 values(1,1);
    insert into t1 values(1,2);
    insert into t1 values(2,3);
    insert into t1 values(null,3);

    select count(distinct i1,i2) from t1;
    +-----------------------+
    | count(distinct i1,i2) |
    +-----------------------+
    | 3 |
    +-----------------------+
    1 row in set (0.03 sec)

    ということで、正しくでるのですが、DISTINCTしたマルチカラムそれぞれにIS NOT NULLをANDでつけていないクエリで
    NULLを含むデータを投入して「結果が違う〜」という向きがいるので、それはこのような違いがある、ということを
    覚えておいてください!

    結果(1),(3)はOK, (2)は違う(カラムがnot nullならOK), (4) はMySQLだけ、ということになります。

    JUGEMテーマ:コンピュータ



    | meijik | Firebird/InterBase | 19:55 | comments(0) | trackbacks(0) | - | - |
    プログラマのためのSQL第四版読書会(12)11.時間型 (p.225)
    0
      読書会はゆるゆると続けていましたが、なかなかアウトプットができず。。。。
      とりあえず最新回から順にさかのぼってアウトプットします。。。

      プログラマのためのSQL 読書会(12) - connpass

      [Typo]
      (1) p.226
      ユリウス暦は4年につき1日の閏年をもっており、

      ユリウス暦は4年につき1日の閏日をもっており、
      (2) p.230
      ランダムに近いデータ作る簡便な方法ではある。

      ランダムに近いデータを作る簡便な方法ではある。
      (3) p.234
      11.3 INVERVAL型の冒頭
      「以下にこのデータ型のBNF定義を示す」の後のBNF定義がない。
      英語版にはある。
       ただ、その後のINTERVAL型は期間をあらわすデータ型だ〜の部分は原著4thにはなく、
      5thの冒頭部分なので、中途半端に間違えている原著をミックさんがつかまされて :)
      原著はしれっとなおしている可能性もあり。。。。

      [原著の??な点]
      p.227 「英語話者においては、年は4桁、月は3文字、日は2桁であらわすのがもっとも誤読誤記が少なくソートするときも便利だとい

      うことを発見した」ですが、月を3文字(例: 12月はDec)とすると、ソートには便利ではないのでは? という話がでた。確かに文字列
      としては月の3文字はソートすると1->2->3月というわけではないので。
       ただ、日付や日付時刻型だと三文字月は入出力のときだけで、内部では別に英字で格納されているわけではないので、特に問題とはならない気もしてきた。後で考えると。

      [用語]
      EDIFACT


      [人物]
      p.228の訳注とp.237
      アリゾナ大学のリチャード・T・スノッドグラス
      Richard Snodgrass

      アレサンドロ・メサ
      →Alejandro Mesa , Microsoft SQLサーバのMVPらしい。SQL Server関連からよく参照されている。

      [ドキュメント・書籍]
      p.237で紹介している二冊
      (1) Developing Time-Oriented Database Applications in SQL (Richard Snodgrass著, Morgan Kaufmann, 1999)
      (2) How to Design, Update and Query Temporal Data (Tom Johnston, Randall Weis著, Morgan Kaufmann, 2010)

      前者はpdfで公開されているので、まず読みたい。(2)は未読。また、アマゾンで検索してみるとTom Johnstonの別の著作と
      Dateの著作がヒットした。これらも購入して、時間がみつけて、読んでみたい。

      (3) Bitemporal Data: Theory and Practice (Tom Johnston著, Morgan Kaufmann, 2014)
      (4) Time and Relational Theory, Second Edition: Temporal Databases in the Relational Model and SQL (C.J. Date,‎ Hugh Darwen,‎ Nikos Lorentzos 著 Morgan Kaufmann 2014)

      [所感]
      暦の細かい説明や、Septemberの語源などは「へぇー」の連続でしたが、その部分は特にSQLには役立たなさそう。トリビア的な感じです。

      読書会の次回は年明け(2018年) 1/15 or 22の月曜日となりそうです。

      JUGEMテーマ:コンピュータ



      | meijik | SQL | 00:37 | comments(0) | trackbacks(0) | - | - |
      FirebirdでPostgreSQL(標準SQL?)のCURRENT_TIMESTAMPを模倣
      0
        本エントリはFirebird Advent Calendar 2017の14日目です。

        FirebirdでPostgreSQL(標準SQL?)のCURRENT_TIMESTAMPを模倣


        現在セルコ著、ミックさん翻訳のプログラマのためのSQLを絶賛読書会中(参加者絶賛募集中!)なのですが

        プログラマのためのSQL 読書会(12)

        その「11章時間型」にも記述がありますし、(プログラマのためのSQL第四版(日本語版と英語版からの引用))

        →ここから引用
        タイムスタンプはシステムの時計を一度だけ読み、同一トランザクション内のすべての項目に同じ時間を適用する。
        Remember that a TIMESTAMP will read the system clock once and use that same time on all the items involved in a transaction.
        ←ここまで引用

        去年のFirebird Advent Calendar 2016でとりあげましたが他とは違うPostgreSQL(とVertica)の実装が正しいように見えます。

        使い慣れたSQLに潜む実装依存: Firebirdの場合(5) CURRENT_TIMESTAMP

        今回はその動作をFirebirdで模倣する方法について考えてみました。

        基本的には以下の考えでいけるはずです。

        (1) トランザクションの開始時刻をシステムから取得する。
        (2) (1) をコンテキスト変数に格納する。
        (3) トランザクション内のCURRENT_TIMESTAMPを参照する部分を(2)に置き換える。

        (1)
        トランザクションの開始時刻を知るにはモニタリングテーブルのうち、以下のテーブルを使います。

        MON$TRANSACTION

        現在のトランザクションIDはcurrent_transaction, 現在の接続はcurrent_connectionで取得できますので、
        MON$TRANSACTIONから、トランザクション開始時刻を求めるクエリは以下になります。

        select mon$timestamp from mon$transactions where current_connection = mon$attachment_id and current_transaction=mon$transaction_id;

        MON$TIMESTAMP
        =========================
        2017-12-13 22:56:49.3670

        (2)
        これをユーザコンテキスト変数'CurrentTS'にrdb$set_contextを使って値を設定します。以下のクエリとなります。

        select rdb$set_context('USER_TRANSACTION', 'CurrentTS', (select mon$timestamp from mon$transactions where current_connection = mon$attachment_id and current_transaction=mon$transaction_id)) from rdb$database;

        RDB$SET_CONTEXT
        ===============
        1

        (3)
         (2)で格納した値をrdb$get_contextを使って利用します。
        たとえば以下のようにtimestamp列に現在時刻を挿入していた場合、

        insert into ts1 values(current_timestamp);

        以下のように変更すれば、トランザクション中は、トランザクション開始時刻を設定することが可能です。

        insert into ts1 values((select rdb$get_context('USER_TRANSACTION', 'CurrentTS') from rdb$database));

        JUGEMテーマ:コンピュータ



        | meijik | Firebird/InterBase | 09:05 | comments(0) | trackbacks(0) | - | - |
        Firebird CTEでFizzBuzz
        0
          Firebird CTEでFizzBuzz

          本エントリはFirebird Advent Calendar 2017の13日目です。

          DEKOさんが以下のエントリを書いていたので、それのCTE(Common Table Expression)版を作ってみました。

          Firebird SQL のストアドプロシージャで FizzBuzz(FizzBuzz Advent Calendar 2017)

          WITH RECURSIVE my_cte AS
          (
          SELECT 1 AS i from rdb$database
          UNION ALL
          SELECT 1+i FROM my_cte WHERE i<20
          )
          SELECT CASE
          WHEN (mod(i,3)=0) AND (mod(i,5)=0) THEN 'Fizz Buzz'
          WHEN mod(i,3)=0 THEN 'Fizz'
          WHEN mod(i,5)=0 THEN 'Buzz'
          ELSE i END
          FROM my_cte;

          実行するとこんな感じ。

          CASE
          ==========
          1
          2
          Fizz
          4
          Buzz
          Fizz
          7
          8
          Fizz
          Buzz
          11
          Fizz
          13
          14
          Fizz Buzz
          16
          17
          Fizz
          19
          Buzz

          CTEの再帰クエリで連番を生成し、それを検索CASE式で分岐しています。

          連番の生成の説明は以下をご覧ください。

          MySQL 8.0 Lab版: MySQLの (再帰)共通テーブル式(CTE)(Yakst)

          検索CASE式については以下をご覧ください。

          CASE式のススメ(リレーショナル・データベースの世界)

          JUGEMテーマ:コンピュータ



          | meijik | Firebird/InterBase | 02:17 | comments(0) | trackbacks(0) | - | - |
          Firebirdでカジュアルに再帰クエリでUPDATE/DELETE
          0
            Firebirdでカジュアルに再帰クエリでUPDATE/DELETE

            本エントリはFirebird Advent Calendar 2017の12日目です。

            さて、CTEはOSSのRDBMSの中でも、わりと早い時期(Firebird 2.1)に実装したFirebirdですが、
            最近のCTEのようにUPDATE, DELETE, INSERTなどには対応せず、SELECTのみの対応になります。
            そのFirebirdで、先日紹介したPostgreSQLのようにUPDATEやDELETEをする方法をお知らせします。

            PostgreSQLでカジュアルに再帰クエリ実行with共通表式(CTE:Common Table Expression)

            1.前準備
            UPDATEのためにあらかじめtimestamp列を追加しておきます。

            CREATE TABLE shain(empid INT PRIMARY KEY, name VARCHAR(20), mgrid INTEGER,ts timestamp);
            INSERT INTO shain(empid,name,mgrid) VALUES ( 1, 'みやはら', NULL);
            INSERT INTO shain(empid,name,mgrid) VALUES ( 2, 'きむら', 1);
            INSERT INTO shain(empid,name,mgrid) VALUES ( 3, 'かまたき', 1);
            INSERT INTO shain(empid,name,mgrid) VALUES ( 4, '加藤', 2);
            INSERT INTO shain(empid,name,mgrid) VALUES ( 5, '林', 2);
            INSERT INTO shain(empid,name,mgrid) VALUES ( 6, '三浦', 5);

            UPDATE shain SET mgrid = (SELECT empid FROM shain WHERE name='かまたき') WHERE name = '林';

            2.UPDATE/DELETE

            CTEのSELECTをUPDATE条件の副問い合わせの中にいれてUPDATEです。

            UPDATE shain SET ts = CURRENT_TIMESTAMP WHERE empid IN (
            WITH RECURSIVE n AS
            (
            SELECT empid, name FROM shain WHERE name = 'かまたき'
            UNION ALL
            SELECT nnext.empid, nnext.name FROM shain as nnext, n WHERE n.empid= nnext.mgrid
            )
            SELECT empid FROM n);

            FirebirdではUPDATE ... FROMができないため、結合してのUPDATEはできません。

            Update a table with join?(Stack Overflow)

            DELETEはUPDATEと同様に、以下のとおりです。

            DELETE FROM shain WHERE empid IN (
            WITH RECURSIVE n AS
            (
            SELECT empid, name FROM shain WHERE name = 'かまたき'
            UNION ALL
            SELECT nnext.empid, nnext.name FROM shain as nnext, n WHERE n.empid= nnext.mgrid
            )
            SELECT empid FROM n);

            JUGEMテーマ:コンピュータ



            | meijik | Firebird/InterBase | 06:31 | comments(0) | trackbacks(0) | - | - |
            Firebirdでデータの歯抜けを探す(NULLあり、の続き)
            0
              Firebirdでデータの歯抜けを探す(NULLあり、の続き)

              本件はFirebird Advent Calendar 2017の11日目です。

              一昨日のブログ書いてから気づいたんですが、以下の条件に加えてもうひとつ条件がありました。

              条件1: seqは整数
              条件2: seqは1からはじまり、1ずつ増えていく連番
              条件3: seqはNULLではない。
              条件4: (seq+1)はオーバーフローしない
              条件5: seqは重複しない

              つまり、seqをINTEGERで宣言すると条件1を満たし、さらにUNIQUE制約をつけると条件5、
              さらにPRIMARY KEYにすれば条件条件3, ダメ押しでMySQLでいう「AUTO_INCREMENT」的な
              機能をつけると条件4以外を満たすことになります。

              ただしAUTO_INCREMENTはMySQL特有の機能でFirebirdにはないので

              3.6.9 AUTO_INCREMENT の使用(MySQL 5.6 日本語マニュアル)

              Firebirdでは以下のブログやそこから参照している"Firebird Generator Guide"を参考にしてみてください。

              FirebirdでSEQUENCEを使う。(山本隆の開発日誌)

              さて、一昨日はWindow関数を使わない解法でしたが、Window関数を使った場合でNULLが含まれているとどうでしょう?
              表の状態は一昨日の状態(つまり2017-12-08の表に、null行を二行追加したもの)で、2017-12-08の解法をそのまま使うと
              どうなるでしょうか?

              select coalesce(max(seq), 0) + 1 as gap
              from (select seq, Row_Number() over(order by seq) as Rn
              from SeqTbl) a
              where seq = Rn;

              GAP
              =====================
              9

              あれ、正しい4でなく、9が戻りました。2017-12-08のように、副問い合わせの中身だけで確認すると以下のとおりです。

              select seq, Row_Number() over(order by seq) as Rn from SeqTbl;

              SEQ RN
              ============ =====================
              <null> 1
              <null> 2
              1 3
              2 4
              3 5
              5 6
              6 7
              8 8 <- たまたま一致した行のmax(seq) + 1で9が戻る

              これを避けるためには、NULLを省いて、SEQの先頭を1にそろえる必要があります。
              簡単な方法はorder byにnulls lastを加えて、nullの行をソートした結果の最後においやる方法です。

              select coalesce(max(seq), 0) + 1 as gap
              from (select seq, Row_Number() over(order by seq nulls last) as Rn
              from SeqTbl) a
              where seq = Rn;

              GAP
              =====================
              4

              これで無事正しい値が求まりました。Firebirdのnulls lastについては以下のブログ記事をご参照ください。

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

              JUGEMテーマ:コンピュータ



              | meijik | Firebird/InterBase | 00:11 | comments(0) | trackbacks(0) | - | - |
              PostgreSQLでカジュアルに再帰クエリ実行with共通表式(CTE:Common Table Expression)
              0
                本エントリはPostgreSQL Advent Calendar 2017の10日目です。

                PostgreSQLでカジュアルに再帰クエリ実行with共通表式(CTE:Common Table Expression)

                PostgreSQLでは8.4から再帰クエリ(WITH RECURSIVE)に対応しています。

                1.基本編(WITH RECURSIVE ... SELECT)
                ここ十年くらい使っている以下のサンプルで試してみます。

                階層構造を隣接リストで表現(slideshare)

                なぜ十年くらい使っているかというと、Firebirdでは丁度そのころ2.1でCTE実装したからです。

                Firebird 2.1の新機能(3) 共通表式(CTE: Common Table Expression), 再帰問い合わせ(キムラデービーブログ)

                CREATE TABLE shain(empid INT PRIMARY KEY, name VARCHAR(20), mgrid INTEGER);
                INSERT INTO shain VALUES ( 1, 'みやはら', NULL);
                INSERT INTO shain VALUES ( 2, 'きむら', 1);
                INSERT INTO shain VALUES ( 3, 'かまたき', 1);
                INSERT INTO shain VALUES ( 4, '加藤', 2);
                INSERT INTO shain VALUES ( 5, '林', 2);
                INSERT INTO shain VALUES ( 6, '三浦', 5);

                WITH RECURSIVE n AS
                (
                SELECT empid, name FROM shain WHERE name = 'きむら'
                UNION ALL
                SELECT nnext.empid, nnext.name FROM shain as nnext, n WHERE n.empid= nnext.mgrid
                )
                SELECT * FROM n;

                empid | name
                -------+--------
                2 | きむら
                4 | 加藤
                5 | 林
                6 | 三浦
                (4 rows)

                無事出力できました。環境は以下のブログ記事を参考に、真ん中あたりをざっくりとばして、各種文字を9.x -> 10に変更して、PostgreSQL 10.1 on CentOS 7.4にて確認しました。

                CentOS7にPostgreSQLを導入&初期設定をおこなう (vdeep)


                2.応用編(WITH RECURSIVE ... (UPDATE|DELETE))
                SQL標準ではSELECTだけですが、PostgreSQLは9.1から更新も可能になっています。
                Microsoft SQL Serverは2005から, MySQL 8.0も対応予定です。

                きむらの部下、林さんと林さんの部下を、かまたきさんの部下にすげかえましょう!
                ....ってあれ、これは再帰クエリは不要ですね。。。。

                UPDATE shain SET mgrid = (SELECT empid FROM shain WHERE name='かまたき') WHERE name = '林';

                気をとりなおして。。。

                例えば、表にタイムスタンプ列を追加して、

                postgres=# alter table shain add column ts timestamp default current_timestamp;

                指定した階層以下の人を更新する、とかに使えます。

                WITH RECURSIVE n AS
                (
                SELECT empid, name FROM shain WHERE name = 'かまたき'
                UNION ALL
                SELECT nnext.empid, nnext.name FROM shain as nnext, n WHERE n.empid= nnext.mgrid
                )
                UPDATE shain SET ts = CURRENT_TIMESTAMP WHERE empid IN (SELECT empid FROM n);

                もしくは

                WITH RECURSIVE n AS
                (
                SELECT empid, name FROM shain WHERE name = 'かまたき'
                UNION ALL
                SELECT nnext.empid, nnext.name FROM shain as nnext, n WHERE n.empid= nnext.mgrid
                )
                UPDATE shain AS t SET ts = CURRENT_TIMESTAMP FROM n WHERE t.empid = n.empid;

                postgres=# select * from shain;
                empid | name | mgrid | ts
                -------+----------+-------+----------------------------
                1 | みやはら | | 2017-12-10 01:26:50.533647
                2 | きむら | 1 | 2017-12-10 01:26:50.533647
                4 | 加藤 | 2 | 2017-12-10 01:26:50.533647
                3 | かまたき | 1 | 2017-12-10 07:30:56.803044
                5 | 林 | 3 | 2017-12-10 07:30:56.803044
                6 | 三浦 | 5 | 2017-12-10 07:30:56.803044
                (6 rows)

                当然これらをDELETEに適用すると、指定した階層以下の人を削除することができます。

                WITH RECURSIVE n AS
                (
                SELECT empid, name FROM shain WHERE name = 'かまたき'
                UNION ALL
                SELECT nnext.empid, nnext.name FROM shain as nnext, n WHERE n.empid= nnext.mgrid
                )
                DELETE FROM shain WHERE empid IN (SELECT empid FROM n);

                postgres=# select * from shain;
                empid | name | mgrid | ts
                -------+----------+-------+----------------------------
                1 | みやはら | | 2017-12-10 01:26:50.533647
                2 | きむら | 1 | 2017-12-10 01:26:50.533647
                4 | 加藤 | 2 | 2017-12-10 01:26:50.533647
                (3 rows)

                PostgreSQLはWITH RECURSIVE ... INSERTも対応していますが、それはまた別の機会に。

                明日はcuzicさんです。

                JUGEMテーマ:コンピュータ



                | meijik | PostgreSQL | 07:48 | comments(0) | trackbacks(0) | - | - |
                Firebirdでデータの歯抜けを探す(NULLあり)
                0
                  本件はFirebird Advent Calendar 2017の9日目です。

                  昨日のブログとそこから参照している記事の当該部分は、以下の条件が(暗黙的に?)あります。

                  条件1: seqは整数
                  条件2: seqは1からはじまり、1ずつ増えていく連番
                  条件3: seqはNULLではない。
                  条件4: (seq+1)はオーバーフローしない

                  そのためミックさんが記事で最後にふれていたように、seqがNULLだとうまく動作しません。
                  前提は昨日のブログで作成したテーブル(SeqTbl)で、そこに一件挿入してみます。

                  insert into seqtbl values(null,null);
                  SELECT '歯抜けあり' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq);

                  GAP
                  ==========
                  歯抜けあり

                  あれ、動作しますね。ではもう一件。

                  insert into seqtbl values(null,null);
                  SELECT '歯抜けあり' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq);

                  あれれ、検知できなくなりました。これはNULL込みの行数が8行(6+2行)になり、MAX(seq)と一緒になってしまったからです。
                  これはcount(*)がnullの行を含んでカウントするからです。nullを含まないでカウントするにはcount(seq)のように直接カラムを指定します。

                  SELECT '歯抜けあり' AS gap FROM SeqTbl HAVING COUNT(seq) <> MAX(seq);

                  GAP
                  ==========
                  歯抜けあり

                  「そもそもNULLあるの?」というのはcount(*)とcount(seq)が違うことでわかります。

                  SELECT 'NULLあり' AS gap FROM SeqTbl HAVING COUNT(*) <> count(seq);

                  GAP
                  ========
                  NULLあり

                  まぁ、ただそれを知るだけなら、以下のクエリのほうが速そうな気がします。

                  select exists(select seq from seqtbl where seq is null) as "nullあり?" from rdb$database;

                  nullあり?
                  =========
                  <true>

                  なお、データにnullがあるとgapの最小値をもとめるこのクエリも当然うごきませんが、

                  SELECT MIN(seq + 1) AS gap FROM SeqTbl WHERE (seq+1) NOT IN ( SELECT seq FROM SeqTbl );

                  GAP
                  =====================
                  <null>

                  動作させるためには、NOT INのサブクエリでnullをはぶく(where seq is not null)だけで大丈夫です。

                  SELECT MIN(seq + 1) AS gap FROM SeqTbl WHERE (seq+1) NOT IN ( SELECT seq FROM SeqTbl where seq is not null);

                  GAP
                  =====================
                  4

                  本日はこんなところで。明日はまたnakagamiさんです!!


                  JUGEMテーマ:コンピュータ



                  | meijik | Firebird/InterBase | 08:04 | comments(0) | trackbacks(0) | - | - |
                  Firebirdの分析関数の衝撃(1)row_numberでデータの歯抜けを探す
                  0
                    MySQLも8.0からWindow関数が実装され、これでオープンソースRDBMS御三家すべてでWindow関数が利用できるようになります。(PostgreSQLは8.4から、Firebirdは3.0から実装済み)

                    今後は各種SQL本でも、Window関数が普通に掲載され、普通に利用されてくると思いますので、
                    ここではミックさんや山岸さんが扱わない(たぶん)Firebirdでの利用について、検証・紹介しておきます。

                    1.データの歯抜けを探す(Window関数を使わない方法)

                    データの歯抜けを探す方法はミックさんの「HAVING句の力(CodeZine)」記事にて説明されています。

                    FirebirdでもWindow関数を持たない3.0より前のバージョンでは、この方法が使えます。以下のテーブルを
                    使って、参照先の方法を試してみてください。

                    create table SeqTbl(seq int, name varchar(10));
                    insert into SeqTbl values(1,'ディック');
                    insert into SeqTbl values(2,'アン');
                    insert into SeqTbl values(3,'ライル');
                    insert into SeqTbl values(5,'カー');
                    insert into SeqTbl values(6,'マリー');
                    insert into SeqTbl values(8,'ベン');
                    commit;

                    2.データの歯抜けを探す(Window関数を使う方法)
                    以下で説明されています。

                    PostgreSQLの分析関数の衝撃1(モードとメジアン)(CodeZine)

                    select coalesce(max(seq), 0) + 1 as gap
                    from (select seq, Row_Number() over(order by seq) as Rn
                    from SeqTbl) a
                    where seq = Rn;

                    これをFirebird 3.0で実行すると、データの歯抜けで一番若い番号、すなわち4が入ります。

                    GAP
                    =====================
                    4

                    動作のイメージがわかない! という人はサブクエリaだけを抜き出して実行するとわかりやすいと思います。
                    (山岸さんの記事の「SQLの処理イメージと考え方」の部分です)

                    select seq, Row_Number() over(order by seq) as Rn from SeqTbl;

                    SEQ RN
                    ============ =====================
                    1 1
                    2 2
                    3 3 <- この行までSEQ = RNなのでmax(seq)はここ、それに1を足すとGAPになる
                    5 4
                    6 5
                    8 6

                    実は参照した山岸さんの記事は二年まえにもざっくり紹介したのですが、今回はもう少し細かくみていくことにします。

                    Firebird 3.0 の Window関数(キムラデービーブログ)

                    JUGEMテーマ:コンピュータ



                    | meijik | Firebird/InterBase | 06:07 | comments(0) | trackbacks(0) | - | - |
                    Firebird 3 に移行するときに考慮すべき事柄(5) 結論
                    0
                      エントリはFirebird Advent Calendar 2017の6日目です。

                      著者のカルロスから許可をいただいて、Things to consider when migrating to Firebirdの日本語訳を
                      お届けしてきました。今回が最終回(五回目)です。

                      Firebird 3 に移行するときに考慮すべき事柄(5) 結論

                      結論

                      これまでご覧いただいたように、Firebird 3.0へのスムーズな移行には、異なるエリアに対する多くの注意が必要です。
                      十分幸運だとしてもバックアップとリストアは必要です。しかし、賢明で悪いニュースが嫌いなユーザであれば、
                      本番環境に投入する前、危険なポイントをすべてチェックするためにいくばくかの時間を費やすことにより、
                      狂ったカスタマーや激おこなユーザの対応から逃れることができるでしょう。

                      本記事でディスカッションしたトピックのみならず、多くのほかの課題についても注意を払う必要があります。
                      特にできうる限りスムーズにFirebird 3.0に移行することを望むならば。それが私(カルロス)が全部入っている本を書いた理由になります。
                      Firebird 3.0への移行ガイド(MGTFB3: Migration Guide to Firebird 3)は調査と頭痛についやす莫大な時間から
                      あなたを救います。以下からPDFもしくは紙版を買うことができます。(英語版とポルトガル語版があります)

                      https://www.firebirdnews.org/migration-guide-to-firebird-3/

                      JUGEMテーマ:コンピュータ



                      | meijik | Firebird/InterBase | 01:45 | comments(0) | trackbacks(0) | - | - |
                           12
                      3456789
                      10111213141516
                      17181920212223
                      24252627282930
                      31      
                      << December 2017 >>
                      + 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
                      • 使い慣れた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)
                      • 気軽に新しいMySQLを試してみる: MySQL Sandboxのススメ
                        MeijiK (02/17)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE