キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
プログラマのためのSQL第四版読書会(28) 30.6 ベンダー拡張(p.570)
0
    # 2018-10-20に公開、2018-10-21に更新しました。

    このように書きましたが、やはりなかなかアウトプットできず(笑)
    とりあえずできる範囲でアウトプットしていきます。

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

    ちょっと前からyuyabuさんが参加して、ブログエントリを書いてくれているので、まずはそちらを御参照ください。(そこへの訂正・補足もあるので)

    プログラマのためのSQL 読書会(28)に参加(Yabu.log)

    [Typo]

    今回はなし。というか以下の(1)が実は翻訳ではなく、Typo ?

    [翻訳の??な点]

    (1) p.571 「高く」「高く」になっているが、正しくは「高く」「低く」
    高く最も近い給与は5000ドル、この社員より高く最も近い給与は

    高く最も近い給与は5000ドル、この社員より低く最も近い給与は

    原文はこう(less than)なので、単純に翻訳間違い。

    Looking at the salaries, we see that the first salary greater than
    his is $5000.00 and the flrst salary less than his is $1300.00.

    (2) p.575

    31.1 最頻値
    「モード」の文字も欲しかったなぁ。(原文はThe Mode)
    同様に「中央値」に対する「メジアン(Median)」や、分散と標準偏差(Variance and Standard Deviation)、平均偏差(Average Deviation)
    なども、見出しではなく本文の初出のときに、元綴りがあるとさらに調べる時に便利だと思います。

    (3) p.588

    [訳注] でVARIANCEに言及しているが、Db2やMySQLは(標本)分散ではなく、母分散の動作なので注意が必要です。
    また、Oracleには元々あるVARIANCE以外にSQL標準のVAR_SAMPが追加されてますが、これは入力値が一つのときの戻り値に違いがあります。

    [原著の??な点]
    (4) p.573 「IBMはこの仕様の一部をDB2 UDB 6.2に実装した」
    うーん6.2はさすがにしらないなぁ。DB2技術全書「p.24 表1-1はDB2に関連する20年間の年表です。」にも以下の記述しかないし、

    1999年 DB2 UDB for OS/390 V6.1 DB2 for Unix,Windows and OS/2 V6.1

    以下のpdfにバージョンの名前があがるくらいで。

    DB2 SQL Workshop for Experienced Users

    http://ourworld.compuserve.com/homepages/Graeme_Birchall
    DB2 UDB 6.2 SQL Cookbook

    ただ↑このURLはすでになく、Grame Birchallのこのシリーズのドキュメントをまとめてくれている人のサイトでも7.2以降でした。

    本当に6.2 ? あるの?? IBMの方に聞いてみましたが、このIBM DB2 バージョン6の検索でも見つからないので
    公にはリリースされなかったのではないかと。。。。(7.xになったか??)

    (5) 母分散にも言及が欲しい
    31.4 分散と標準偏差(Variance and Standard Deviation)
    (標本)分散(sample variance)のみに言及している。標準偏差を求めるための分散なので、意図的にはこれでいいが、
    VARIANCE関数が(標本)分散を示す場合と、母分散を示す場合の実装があるので、母分散(population variabnce)にも言及欲しい

    分散(Wikipedia)


    ここにも言及がある不偏分散(unbiased variance)とのそれぞれの関わりが分からないなぁ。(SQLの前に)

    標準偏差とは?

    [用語]
    p.573 Cognos, InformixはすでにIBMが買収している。

    Brioは2003年にハイペリオンに買収され、ハイペリオンはオラクルに買収されたが、2015年4月にPremiere Support切れになっている。
    MicroStrattegyは継続してBIツールを提供している

    Enterprise Magagine : 英語版Wikipediaによると2007年ごろ停止したらしい。

    [人物]
    p.573
    ハミッド・ピラヘシュ(Hamid pirahesh) 引き続きIBMで働いているようです。IBM Fellow, ACM Fellow。こんなのもあった

    アンディ・ウィコウスキ(Andy Witkowski) 引き続きOracleで働いているようです。

    後述の「Introduction to OLAP functions」も参照のこと。

    p.580
    アナトニー・アブラモヴィッチ(Anatoly Abramovich)
    イェリーナ・アレクサンドロヴァ(Yelena Alexandrova)
    オイゲン・ビルガー(Eugene Birger)
    Yelena以外は、以下の書籍の著者。MS SQL Server関連の人のようだ。
    ptimizing Transact-SQL: Advanced Programming Techniques
    Yelenaは同姓同名のスケーターもいるので、うまく検索できないが、やはりMS SQL Server関連の人ではないか? まぁ検索してもセルコのこの本関連の検索結果しかでないが。。。

    p.581
    ケン・ヘンダーソン(Ken Henderson) MS SQL Serverの著作あり。以下の二冊は木村が所蔵。
    The Guru’s Guide to Transact-SQL
    The Guru’s Guide to SQL Server Architecture and Internals
    「故」と書いてあるとおり、2008年に40そこそこでなくなっている。R.I.P. 私と同じ1967年生まれなのに。

    マイケル・シーハン(Michael Sheehan) 同性同名の方が63才で亡くなっているが違う人のように見える。
    リチャード・レムレー(Richard Romley) : 27.3 レムレーの除算(Romley's Division)にも登場。
    ソロモンスミスバーニー(Salomon Smith Barney)勤務(だったらしい)。セルコがここのコメントでふれている。

    セルコの著作、SQLパズルのPUZZLE 10 WAGES OF SINのAnser #2や、Thinging in Sets: の 18.3 Data Constraint Approachなどでも、名前が確認できる。

    p.585
    ペソ(Peso) 普通に検索すると通貨のペソしかヒットしない。以下のセルコ関連のコメントにペソ自身がコメント付けている。
    アダム・マニチャク(Adam Machanic) Machanicはマカニックでは??
    こんな人らしいボストンを拠点としたSQL Server関連開発、執筆、講演者


    [ドキュメント・書籍]

    p.573 「Introduction to OLAP functions」

    (6) C.J.Dateの参照書籍。Textbooks(Date, 1983, 1995a) yuyabuさんの見立てでは以下の記述がありましたが、

    >Dateに関して1983年と1995年の著作は以下のものがあるらしい
    >
    > Relational Database Writings 1991-1994 [FACSIMILE], 1995, Addison Wesley Longman, ISBN 978-0201824599
    > Database : a primer, Addison Wesley, 1983, ISBN 978-0201113587
    >
    >おそらくこの2冊からの引用ということだろう。

    実はこの二冊は木村が持っているので内容確認したところ、厳密にそのような定義ではなく、以下の部分で使用・説明されている部分をさしているらしい。

    Database : a primer : 18 Database Design (Continued) p.231 the suppliers-and-parts example.
    Relational Database Writings 1991-1994 p.270 3. THE SUPPUERS-AND-PARTS DATABASEのp.271 Fig.1 The suppliers-and-parts database (data definition)

    ただ、primerのほうは具体的なCREATE TABLEがないし、Database Writingsのほうは
    DOMAINを使ってdata_typeを指定してなく、表名やカラム名が違うので、ググってもでてこないんですね。

    同等の定義、データは、デイトが「標準SQL(An introduction to the SQL Standard)」の初版(p.24)から使っている表だと思います。

    オリジナルはこちら。MySQL用に最適化してますが、定義とデータはオリジナルのままです。
    (「標準SQL」では、CREATE TABLEが掲載されていますし、DOMAINは使われていません)

    CREATE DATABASE TED;
    use TED;

    drop table if exists S;

    CREATE TABLE S
    (SNO CHAR(5) NOT NULL,
    SNAME CHAR(20),
    STATUS DECIMAL(3),
    CITY CHAR(15),
    UNIQUE(SNO));

    drop table if exists P;

    CREATE TABLE P
    (PNO CHAR(6) NOT NULL,
    PNAME CHAR(20),
    COLOR CHAR(6),
    WEIGHT DECIMAL(3),
    CITY CHAR(15),
    UNIQUE(PNO));

    drop table if exists SP;

    CREATE TABLE SP (SNO CHAR(5) NOT NULL,
    PNO CHAR(6) NOT NULL,
    QTY DECIMAL(5),
    UNIQUE(SNO,PNO));

    insert into S values
    ('S1','Smith',20, 'London'),
    ('S2','Jones',10,'Paris'),
    ('S3','Blake',30,'Paris'),
    ('S4','Clark',20,'London'),
    ('S5','Adams',30,'Athens');

    insert into P values
    ('P1','Nut','Red',12, 'London'),
    ('P2','Bolt','Green',17,'Paris'),
    ('P3','Screw','Blue',17,'Rome'),
    ('P4','Screw','Red',14,'London'),
    ('P5','Cam','Blue',12,'Paris'),
    ('P6','Cog','Red',19,'London');

    insert into SP values
    ('S1','P1',300),
    ('S1','P2',200),
    ('S1','P3',400),
    ('S1','P4',200),
    ('S1','P5',100),
    ('S1','P6',100),
    ('S2','P1',300),
    ('S2','P2',400),
    ('S3','P2',200),
    ('S4','P2',200),
    ('S4','P4',300),
    ('S4','P5',400);


    [所感]

    メジアン(Median)の下りが長いが、実はSQL:2008で定められたPERCENTILE_CONTにて実現できるため、
    「Firebird, MySQL, SQLite」以外では、「PERCENTILE_CONT( 0.5 ) WITHIN GROUP( ORDER BY expression )」で求められる。
    つまり、p.577-587のくだりは、歴史的な意味しかないです。。。。

    p.578 [訳注1] 中央値は、OracleがMEDIANという関数名でサポートしている。とありますが、現状他のRDBMSでは以下。
    なお、Oracleも後述するPERCENTILE_CONTで求めることもできます

    Db2 対応している。(DB2 11.1以降)説明には「PERCENTILE_CONT( 0.5 ) WITHIN GROUP( ORDER BY expression )と同値となっている」

    MS SQL Server 直接本体では対応していない。(2017時点)
    MDXでは対応している
    MS SQL Server 2019でも未サポートのようです。(新機能に見当たらない)

    ただし、IBMの別解と同様にPERCENTILE_CONTを使ってもとめることができる。(SQL Server 2012以降)

    PostgreSQLも直接は対応していないが、PERCENTILE_CONTで対応可能(PostgreSQL 9.4以降)。

    MySQLは8.0でも、median, percentile_cont共に未対応。
    Medianの機能リクエストはある。
    これを参照する形でPERCENTILE_CONTをリクエストしておくか。。。。

    MariaDBは10.3.3でmedian, percentile_contに対応。

    Firebird 3.0で未対応。Medianのリクエストがでている。これも。PERCENTILE_CONTのリクエストはでてないので、これもリクエストせねば。

    SQLiteはHistory of src/include/nodes/relation.hの変更で、SQL:2008にもとづき実装されたふう。。。

    2013-12-23 21:11
    Support ordered-set (WITHIN GROUP) aggregates. This patch introduces generic support for ordered-set and hypothetical-set aggregate functions, as well as implementations of the instances defined in SQL:2008 (percentile_cont(), percentile_disc(), rank(), dense_rank(), percent_rank(), cume_dist()). We also added mode() though it is not in the spec, as well as versions of percentile_cont() and percentile_disc() that can compute multiple percentile values in one pass over the data.
    https://www.sqlite.org/postgresql/finfo?name=src/include/nodes/relation.h&m=029349bc7e4721b3

    しかし、ドキュメントにはみあたらん(ここから参照できるcore, agg, windowみた)。relation.hを実際みてみるか。。。。

    ここでMariaDB 10.3時点の他のRDBMSの対応状況を知ることができる。

    なお、最後の例で:in_averageとなっているのはOracleのプレースホルダ。

    MySQLだと@in_averageとコロンをアットマークにかえて、ユーザ定義変数で代用できる。



    [次回]
    二週間後の2018-10-18(thr)にしようと思ったら、その日はRISC-V Day in Tokyo 2018なんだよなぁ。お休みにするか、前後の日にずらすかもしれません

    結局金曜日にずらしました。間違えて木曜日にきた人スミマセン。。。。

    JUGEMテーマ:コンピュータ

    | meijik | データベース | 23:36 | comments(0) | trackbacks(0) | - | - |
    TECH PLAYのSQL講座(非エンジニア・マーケター向け)にメンター参加&BigQuery補足
    0
      首記の件、TECH PLAYの小沢さんがFBで募集していたので、気軽にメンターに申し込んだ。

      ミックさんと初心者向けの本を書いているので、初心者・非技術者がどこらへんでわからなくなるか興味があったのだ。

      【満席御礼&メンターさんが増えたので増枠20→40名】ワンランク上のマーケターになれる!− データ分析ができるマーケターになろう!SQL講座 −(非エンジニア・マーケター向け講座)

      資料は上記リンクからたどれます。

      開始前に資料を読んでTypo指摘して、あわよくばクエリ自体にも突っ込もうと思ったけど、それは無理で
      本番突入。淡々とハンズオンをこなして、無事終了しました。

      やはりこう、初心者にはGROUP BYとHAVINGが巨大な壁として立ちはだかりましたね。
      ここらへんはやはりミックさんのキリン本がお勧めです。わかりやすいので。

      それ以外はSQLの「方言」今回はGoogle の BigQueryを使ってました。
      おそらくは「レガシーSQL」を利用したもので、特にどんなRDBMSを利用しても、同じクエリが使える、
      というものではないので、本ブログエントリで少し補足しておきます。

      (1) limit
      (2) contains
      (3) strftime_utc_usec
      (4) timestamp関数
      (5) exact_count_distinct

      以下順に説明していきます。

      (1) limit
      クエリの結果行から指定行数を表示する指定です。LIMIT句はSQL標準ではありませんが、
      もともと実装していたMySQLに加えて、その後PostgreSQL、IBMのMySQL互換モードONにて利用できるようになりました。SQL標準ではSQL2008にてFETCH FIRST m ROWSの構文が定められRDBMSの商用御三家で利用できます。詳しくは
      以下のエントリをご参照ください。

      [#ouchidbボツ原稿シリーズ]縦横に長い表を画面表示に収めるには?(キムラデービーブログ)

      (2) contains
       元々はRDBMSの商用御三家で利用できる自然言語検索拡張です。使い方もBigQueryでは、以下ですが、

      WHERE title contains('ボーカロイド')

       商用御三家では以下のようになりますし、拡張機能が本体とは別に必要だったり(DB2, Oracle), Express版では使えなかったり(Microsoft SQL Server)します。

      WHERE contains(title, 'ボーカロイド')

      標準SQLでは以下のクエリが同じ結果を返しますが、通常中間一致ではインデックスが利用できないため、遅くなります。

      WHERE title like '%ボーカロイド%';

      (3) strftime_utc_usec
       timestamp型の列を書式指定して、文字列にします。strftimeは元々Cの関数からきていて、そこからPHPとか
      RDBでいうとSQLiteなどでstrftimeとして使えます。
       RDBMSの世界ではOracle DBがTO_CHARを利用していたせいで、同様の構文がIBM DB2, PostgreSQLで利用できます。
      なのでBigQueryのこれ、

      STRFTIME_UTC_USEC(upload_time,"%Y-%m")

      書式は大文字小文字の意味が違う以下、つまり2017-11のような形式になります。

      %y西暦の下2桁 (世紀部分を含まない年) (00-99)。
      %Y世紀部分を含めた ( 4 桁の) 西暦年。
      %m月 (10 進数表記)。 (01-12)
      %M分 (10 進数表記) (00-59)

      これは、以下の方法でいけるはず。

      TO_CHAR(upload_time,'YYYY-MM')

      Microsoft SQL Serverはいろいろ難しいけど、2012以降ならFORMAT関数で以下のようにいけるはず

      FORMAT(upload_time, 'YYYY-MM')

      参考: T-SQL 日付をフォーマットされた文字列に変換する(SQL Server入門)

      実は日付指定フォーマットがBigQueryと他のRDBMSでは違うんですよね。%Y -> YYYY, %m -> MM

      ただし、MySQLではdate_formatを使ってBigQuery(strftime)と同じように使えます。

      mysql< select date_format(now(),'%Y-%m');
      +----------------------------+
      | date_format(now(),'%Y-%m') |
      +----------------------------+
      | 2017-11 |
      +----------------------------+
      1 row in set (0.00 sec)

      (4) timestamp関数
      文字列からtimestamp型に変換するために利用しています。TO_CHARがサポートされているRDBMSでは
      その逆となる(?) TO_TIMESTAMPが用意されているのでそれが使えます。
       またMySQLのように暗黙のキャストがされるばあいは、文字列そのままでもかまいません。
      SQL標準にのってるならCAST関数が使えますが、timestamp型が変換先に用意されているとは限りません。

       ただ、元々のtimestamp型がみんなが想像するtimestamp型とは違うMicrosoft SQL Serverの例もありますし、

      TIMESTAMP型の位置づけ(HHeLiBeXの日記 正道編)

      MySQLも元々、類似のMicrosoft SQL Server類似の機能をtimestamp型のデフォルトにしていたのに、MySQL 5.6からは
      それから離れて、単にTZ(Time Zone)コンシャスな型によせてきた、とう経緯もあります。

      11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能(MySQL5.6日本語マニュアル)

       BigQueryの場合もレガシーSQLと標準SQLでは違いがあり、その違いsafeな、safe_castなるcastがあったりします。

      TIMESTAMPの違い(標準 SQL への移行)

      (5) exact_count_distinct
      これはSQL標準ではcount(distinct カラム名)になります。ただ、BigQueryでは、精度ではなく、速度をとっているので、countには第二引数で精度が指定できますし、内部的な最適化をするため(?) にそれようの関数を用意しています。

      COUNT([DISTINCT] field [, n])(クエリリファレンス)

      この(1)-(5)を意識すると、BigQuery以外のRDBMSでも、同じようなクエリが利用できると思います。

      JUGEMテーマ:コンピュータ



      | meijik | データベース | 23:49 | comments(0) | trackbacks(0) | - | - |
      「理論から学ぶデータベース実践入門」韓国語発売!
      0

        左が原著、右が韓国語翻訳版です。なぜ動物ポリゴンのレインボーカラーなのか、というと、オライリー本のようにこの装丁が出版社のシリーズとなっているとのことです。

        韓国人の同僚が帰国の際に入手したものを写真とらせてもらいましたー。やはり著者名しか読めません(笑)

        ちなみに、ミックさんとの共著の韓国語版も出てます。以下のブログエントリをご参照ください。

        「おうちで学べるデータベースのきほん」韓国語発売!
        | meijik | データベース | 17:27 | comments(0) | trackbacks(0) | - | - |
        「おうちで学べるデータベースのきほん」韓国語発売!
        0
          .temp.jpg

          原著は3刷まででた「おうちで学べるデータベースのきほん」一応版権がとられていた韓国語版発売になりました。

          まぁ私は韓国語の素養がないので、読んでもちんぷんかんぷんなのですが、実際に本になると感慨深いですね。
          判形も大きくなり図は読みやすいです。

          韓国人のお友達がいたらぜひ、ご紹介ください :)

          | meijik | データベース | 10:07 | comments(0) | trackbacks(0) | - | - |
          翔泳社で2016/10/02(sun)まで電子書籍40%割引セール! DB界隈の人はこれを買え!読め!!
          0
            電子書籍セール特設サイト
            http://www.shoeisha.co.jp/campaign/fes/20160926

            ということで、本日含めても、もう六日しかないセールで
            DB界隈の人が買うべき本をチェーック!!

            # 木村はkindle派なので、リンクはkindleです

            ■まずはDB界のオトコ、奥野さんの新刊だよ。

            詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド

            爆発的に機能が増えた5.7について、詳しく説明した良書。
            私も査読しましたけど、査読というより、単純に勉強させていただきましたm(_ _)m

            ■次にDB界のセルキアン(!) ミックさんの新刊2冊と、既刊3冊だよ。

            プログラマのためのSQLグラフ原論 リレーショナルデータベースで木と階層構造を扱うために

            『Joe Celko's Trees and Hierarchies in SQL for Smarties, 2nd Edition』の邦訳です。
            数あるセルコ本からこれを選ぶとはミックさんの趣味と興味がうかがえます。
            #これも査読させていただきました。

            SQL 第2版 ゼロからはじめるデータベース操作

            好評「キリン本」の第二版。対象とするデータベースのバージョンがあがったのと、あらたにJDBCによる接続の章が追加され、さらにパワーアップ。初心者にオススメです。

            既刊の三冊は「達人シリーズ」と歴史的名著「プログラマのためのSQL」

            達人に学ぶ SQL徹底指南書
            達人に学ぶDB設計 徹底指南書
            プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに

            ■そしてそのほかチェックすべきものは次の本だよ。

            グラス片手にデータベース設計 販売管理システム編 第2版

            今は亡き、DB Magazineの好評連載をまとめた三部作、そのうちのひとつが第二版となって復活!

            SAP SQL Anywhere 17 自己管理型RDBMS入門ガイド
            SQL Anywhere本、久しぶりの登場。

            Oracleデータベースセキュリティ セキュアなデータベース構築・運用の原則
            小田さんが監修した、Oracleのセキュリティ本。詳しくはこちら

            ちなみに上記で紹介した本のうち、もってないのはSAP SQL Anywhere本だけです。。。。。

            その他、DB資格関連の本もセールだけど、そこらへんは疎いから紹介はしないよ!!
            では、また、DBの世界で会いましょう。

            JUGEMテーマ:コンピュータ




            | meijik | データベース | 23:41 | comments(0) | trackbacks(0) | - | - |
            [#ouchidbボツ原稿シリーズ]データベースファイルからの差分バックアップ生成
            0
              ミックさんとの共著「おうちで学べるデータベースのきほん」はおかげさまで好評です。

              ただ執筆時には原稿を盛り込みすぎてボツになったものもあり、本シリーズ(?) では、そのうちのいくつかをひろって供養したいと思います。

              今回は「関西DB第3回」で話があがった、バックアップ・リカバリについて。

               MySQLの例だけみると「フルバックアップとバイナリログがあればリカバリできるじゃん」と思われた方も多いと思います。それはそのとおりですが、バイナリログを時系列に適用していくと、その適用に時間がかかり、想定した時間内にリカバリが終わらない、といった事態も起こりえます。またこのようなバイナリログの適用は同じ行のデータを何度も更新するような場合には、最後の一度以外は無駄な作業であることが想像できます。たとえば次のようなケースです。

              (0) -- 最初の状態
              (1) UPDATE t1 SET v2 = 'AAA' WHERE i1 = 1;
              (2) UPDATE t1 SET v2 = 'BBB' WHERE i1 = 1;
              (3) UPDATE t1 SET v2 = 'CCC' WHERE i1 = 1;
              (4) UPDATE t1 SET v2 = 'DDD' WHERE i1 = 1;
              (5) UPDATE t1 SET v2 = 'EEE' WHERE i1 = 1;
              (6) UPDATE t1 SET v2 = 'FFF' WHERE i1 = 1;
              (7) -- 最新の状態

              このような場合、実は最後の(6)が実行できれば、その前の(1)〜(5)は不要な気がします。つまり「最終的な値がわかれば、その過程は不要」というものです。多くのRDBMSでは(0) 最初の状態、と(7) 最新の状態を比較して差分情報を抽出することができます。これはデータが格納されているディスクファイル(もしくはディスクファイルを構成するブロック)を比較して更新されていた場合に差分として抽出するものです。この機能があればフルバックアップを(0) 最初の状態として、任意のタイミングで差分バックアップを生成できます。差分バックアップのイメージでは現在のデータベースは以下のように表現できます。

              現在のデータベース = フルバックアップしたデータ + 現在までの差分バックアップ

              以下主要なRDBMSのバックアップ機能と名称について一覧にまとめましたので、ご参照ください。

              ----------------------------------------------------------------------------------------------------------------------------
              名称          フルバックアップ    差分バックアップ            増分バックアップ
              ----------------------------------------------------------------------------------------------------------------------------
              対象データ       すべて         前回のフルバックアップからの差分    前回の任意バックアップからの差分
              バックアップ総処理時間 大           中                   小
              リカバリの総処理時間  小           中                   大
              メリット        運用が簡単。      運用は普通。              運用が難しい。
              デメリット       RDBMSの停止が必要。  差分抽出に時間がかかることも。     リカバリ時全ての増分ファイルが必要
              各DBMSでの呼び名
              Oracle         全体バックアップ    累積増分バックアップ          差分増分バックアップ
              Microsoft SQL Server 完全バックアップ    差分バックアップ            トランザクションログバックアップ
              IBM DB2        フルバックアップ    増分バックアップ            デルタバックアップ
              PostgreSQL      ベースバックアップ    ×                   増分バックアップ(トランザクションログ使用)
              MySQL         フルバックアップ     ×*6                 増分バックアップ(バイナリログ使用)
              Firebird         フルバックアップ     差分バックアップ           増分バックアップ
              ----------------------------------------------------------------------------------------------------------------------------

              注6: Enterprise Editionに含まれるMEB(MySQL Enterprise Backup)のincremental指定により可能。

              JUGEMテーマ:コンピュータ

              | meijik | データベース | 16:46 | comments(0) | trackbacks(0) | - | - |
              [#ouchidbボツ原稿シリーズ]縦横に長い表を画面表示に収めるには?
              0
                ミックさんとの共著「おうちで学べるデータベースのきほん」はおかげさまで好評です。

                ただ執筆時には原稿を盛り込みすぎてボツになったものもあり、本シリーズ(?) では、そのうちのいくつかをひろって供養したいと思います。

                ■縦に長い表編
                クエリの結果の行数が何行になるかは、クエリを実行してみないとわかりません。また事前にcount(*)を発行して行数を知って、それが画面表示に収まらないとわかったとしても、行数を指定して取得する方法は長らくSQL標準にはありませんでした。MySQLでは古くからLIMIT句を用いてクエリの結果行から指定行数を表示することができるようになっています。たとえば先ほどのクエリの最後にLIMIT 5を追加することによってクエリの結果行の先頭から5行だけ表示することができます。

                mysql> select district, count(*) from city where countrycode = 'JPN' group by district having count(*) > 4 order by count(*) desc limit 5;
                +----------+----------+
                | district | count(*) |
                +----------+----------+
                | Osaka | 22 |
                | Saitama | 21 |
                | Chiba | 19 |
                | Tokyo-to | 18 |
                | Aichi | 15 |
                +----------+----------+
                5 rows in set (0.00 sec)

                LIMIT句は先頭行から指定行数だけではなく、先頭からn行スキップしてm行表示ということもLIMIT n,mという形で指定して実行できます。

                mysql> select district, count(*) from city where countrycode = 'JPN' group by district having count(*) > 4 order by count(*) desc limit 5,5;
                +-----------+----------+
                | district | count(*) |
                +-----------+----------+
                | Kanagawa | 15 |
                | Hyogo | 11 |
                | Hokkaido | 10 |
                | Shizuoka | 9 |
                | Yamaguchi | 6 |
                +-----------+----------+
                5 rows in set (0.01 sec)

                これを繰り返すことでページング的な動作、つまり5行ずつ表示するためのデータを、同一のクエリの最後の部分を「LIMIT 0,5」「LIMIT 5,5」「LIMIT 10,5」と変えていくだけで実現できます。LIMITはクエリによる結果セットに対して適用するものですので、順番として一番最後、つまりORDER BYの後に指定することになります。

                1.SELECT
                2.FROM
                3.WHERE
                4.GROUP BY
                5.HAVING
                6.ORDER BY
                7.LIMIT

                LIMIT句はSQL標準ではありませんが、MySQLとPostgreSQL、IBMのMySQL互換モードONにて利用できます。
                SQL標準ではSQL2008にてFETCH FIRST m ROWSの構文が定められ、同構文を元々利用していたIBM DB2と、
                DB2以外の商用RDBMSの最近のバージョン(MS SQL Server 2012, Oracle 12c)でサポートされています。
                このように主要なRDBMSではかならポピュラーな実装になってきましたが、ここで言及した以外のRDBMSや
                古いバージョンの場合には独自実装により実現していたり、代替クエリにて実現している場合があります。
                次のブログエントリに主要なRDMBSでの指定方法がまとめられていますので、ご参照ください。

                「指定した範囲の行を取得するクエリ(HHeLiBeXの日記 正道編)」

                [2015-05-26 追記] 上記のブログエントリは少し古いので、最近の主要なRDBMSについては、次のエントリもご参照ください。

                部分結果(USE THE INDEX, LUKE)

                なおDB2はFETCH FIRSTはサポートしている(というかもともとDB2の方言)のですが、SQL標準のOFFSETはサポートしていません(!) これも含めてDB2でのびっくりしたことは以下のブログエントリ(英語)をご参照ください。

                Seven Surprising Findings About DB2(USE THE INDEX, LUKE)

                ■横に長い表編
                 一般的なRDBMSではMySQLにmysqlコマンドクライアントが存在するように、CUIにて対話的にサーバとやりとりするためのツールが添付されています。
                ただ、そのようなクライアントは横幅が少ないため、カラム数の多い表を表示しようとすると、一行が画面内で何度も折り返されて大変読みにくく表示されてしまいます。
                たとえば、カラム数の多いcountry(国)テーブルをLIMIT句を用いて二行表示されると以下のような表示になってしまいます。

                mysql> select * from country limit 2;
                +------+-------------+---------------+---------------------------+-------------+
                -----------+------------+----------------+---------+--------+-------------------
                ----+----------------------------------------------+---------------+---------+--
                -----+
                | Code | Name | Continent | Region | SurfaceArea |
                IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName
                | GovernmentForm | HeadOfState | Capital | C
                ode2 |
                +------+-------------+---------------+---------------------------+-------------+
                -----------+------------+----------------+---------+--------+-------------------
                ----+----------------------------------------------+---------------+---------+--
                -----+
                | ABW | Aruba | North America | Caribbean | 193.00 |
                NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba
                | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | A
                W |
                | AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 |
                1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanes
                tan | Islamic Emirate | Mohammad Omar | 1 | A
                F
                +------+-------------+---------------+---------------------------+-------------+
                -----------+------------+----------------+---------+--------+-------------------
                ----+----------------------------------------------+---------------+---------+--
                -----+
                2 rows in set (0.01 sec)

                一部のコマンドクライアントツールにはSELECT文で得られる結果セット表示を行ごとに縦表示にする機能があります。
                (この機能には特に定まった呼称はないですがPostgreSQLでは展開表示(Expanded display)機能と呼んでいるようです)
                なお、先ほど説明したLIMITはSQLの一部であり、DBMSからの結果が戻ってくるときにすでにデータが削減されていますが、
                本機能は単純にCUIでの表示の機能だけでり、発行されるSQLやそれによって戻ってくるデータが変わってくるわけではありません。

                MySQLでは通常デリミタとしてつかう;の代わりに¥Gを利用することにより、行ごとに縦表示にできます。先ほどと同じ
                クエリを¥Gにすると次のような見易い形で表示できます。

                mysql> select * from country limit 2¥G
                *************************** 1. row ***************************
                Code: ABW
                Name: Aruba
                Continent: North America
                Region: Caribbean
                SurfaceArea: 193.00
                IndepYear: NULL
                Population: 103000
                LifeExpectancy: 78.4
                GNP: 828.00
                GNPOld: 793.00
                LocalName: Aruba
                GovernmentForm: Nonmetropolitan Territory of The Netherlands
                HeadOfState: Beatrix
                Capital: 129
                Code2: AW
                *************************** 2. row ***************************
                Code: AFG
                Name: Afghanistan
                Continent: Asia
                Region: Southern and Central Asia
                SurfaceArea: 652090.00
                IndepYear: 1919
                Population: 22720000
                LifeExpectancy: 45.9
                GNP: 5976.00
                GNPOld: NULL
                LocalName: Afganistan/Afqanestan
                GovernmentForm: Islamic Emirate
                HeadOfState: Mohammad Omar
                Capital: 1
                Code2: AF
                2 rows in set (0.00 sec)

                MySQL以外のRDBMSではコマンドラインツールの機能としてトグルで表示を切り替えられたり(PostgreSQLの¥xやFirebirdのset list),
                スクリプトにて同様の機能をエミュレート(たとえばOracle用にTanel Poder氏が作成したTPTスクリプト内のpr.sqlなど)していたりしますので、
                コマンドラインツールを多様する方はご自分が利用されているコマンドラインツールに同様の機能がないかチェックしてみてください。Oracle用の表示については、以下のブログエントリが詳しいです。

                SQL*Plusで展開表示 - pr.sql from Tanel Poder's TPT scripts(コーソル DatabaseエンジニアのBlog)

                ★本コラムは「やってみよう! [6-3] データを更新(更新、挿入、削除)してみよう。」に配置することを想定してかかれ、ボツになったものです。。。。

                JUGEMテーマ:コンピュータ
                | meijik | データベース | 20:57 | comments(0) | trackbacks(0) | - | - |
                「SQL実践入門」勝手に補足: MySQLで実行計画を見る
                0
                  現在以下の読書会を行っています。

                  「SQL実践入門」読書会

                  そろそろ実行計画を読むところに差し掛かるのですが、本書は具体例としては
                  OracleとPostgreSQLの実行計画しか利用していません。そこで、本エントリでは
                  MySQLでの実行計画の表示の仕方と簡単な意味(を確認するリンク)を補足します。

                  まず、「SQL実践入門」のサポートページから、データをダウンロードします。

                  サポートページ

                  解凍するとcodeディレクトリ以下にcode*.txtが展開されますので、まずCode1.txtを参照します。

                  ■「図1.8::店舗テーブルのサンプル」を作成、のcreate tableはそのまま実行できます。

                  テーブルスキャンとインデックススキャンは、それぞれ頭にEXPLAINをつけて実行します。


                  mysql> explain SELECT * FROM Shops;
                  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
                  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
                  | 1 | SIMPLE | Shops | NULL | ALL | NULL | NULL | NULL | NULL | 60 | 100.00 | NULL |
                  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
                  1 row in set, 1 warning (0.00 sec)

                  mysql> explain SELECT *
                  -> FROM Shops
                  -> WHERE shop_id = '00050';
                  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
                  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
                  | 1 | SIMPLE | Shops | NULL | const | PRIMARY | PRIMARY | 15 | const | 1 | 100.00 | NULL |
                  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
                  1 row in set, 1 warning (0.00 sec)


                  上記はMySQL 5.7で実行した結果ですが、filtered以外は、おくのさんの以下のエントリを見ると意味がわかります。

                  MySQLのEXPLAINを徹底解説!!(漢のコンピュータ道)

                  ■「図1.14::予約管理テーブルのサンプル」を作成
                  も、MySQLで問題なく実行できます。

                  mysql> explain SELECT shop_name
                  -> FROM Shops S INNER JOIN Reservations R
                  -> ON S.shop_id = R.shop_id;
                  +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
                  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
                  +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
                  | 1 | SIMPLE | R | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
                  | 1 | SIMPLE | S | NULL | eq_ref | PRIMARY | PRIMARY | 15 | test.R.shop_id | 1 | 100.00 | NULL |
                  +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
                  2 rows in set, 1 warning (0.00 sec)

                  統計情報は

                  ANALYZE Shops;
                  ANALYZE Reservations;

                  ではなく

                  ANALYZE table Shops;
                  ANALYZE table Reservations;

                  で実行します。私の場合testスキーマ(データベース)に作成しているので、以下のようになります。

                  mysql> ANALYZE table Shops;
                  +------------+---------+----------+----------+
                  | Table | Op | Msg_type | Msg_text |
                  +------------+---------+----------+----------+
                  | test.shops | analyze | status | OK |
                  +------------+---------+----------+----------+
                  1 row in set (0.02 sec)

                  mysql> ANALYZE table Reservations;
                  +-------------------+---------+----------+----------+
                  | Table | Op | Msg_type | Msg_text |
                  +-------------------+---------+----------+----------+
                  | test.reservations | analyze | status | OK |
                  +-------------------+---------+----------+----------+
                  1 row in set (0.01 sec)

                  とりあえずこれで第1章は大丈夫かな。

                  もっと細かくオプティマイザの動作を見るには、以下のおくのさんのエントリを参照してみてください。

                  オプティマイザトレースによるちょっとディープな快適チューニング生活(漢のコンピュータ道)

                  GUIで確認したい場合はVisual Explainを使うといいでしょう。

                  MySQLでVisual Explain(漢のコンピュータ道)

                  では、隔週水曜日ねこじゃらし様の会場でお会いしましょう!

                  JUGEMテーマ:コンピュータ
                  | meijik | データベース | 18:01 | comments(0) | trackbacks(0) | - | - |
                  3刷出来! 「おうちで学べるデータベースのきほん」
                  0
                    IMG_20160418_050401.jpg

                    昨年(2015年)の10月に増刷がかかった首記の本、今年(2016年)にめでたく三刷がでました。
                    共著者ミックさんのこの業界への影響力の底力を感じている今日この頃です。

                    これまでにわかっている正誤表のほとんどが反映済みになりました。

                    ちょうどkindle本のセールがあり、いまなら半額の1188円にて購入できますので、未読の人はぜひチェックしてみてください。ちなみにkindleではほかの人がどのくらいの時間で読み終えたか、の情報がわかります。この本は大体8時間強、つまり、一章1時間弱にてさっくり読まれているようです。





                    | meijik | データベース | 05:06 | comments(0) | trackbacks(0) | - | - |
                    「理論から学ぶ データベース実践入門」読書会(20)覚書-2
                    0
                      一昨日の続きです。

                      トリガーはデータベースリファクタリングによく使われる。
                      結構闇がある。。。。(これはまた別途まとめたい)

                      makopi23さんが話してくれたSQLアンチパターンでの忍者屋敷おもしろかった。

                      SQLアンチパターン・レトロスペクティブ関西にいってきた(Java開発のんびり日記)

                      ■NOT NULL制約の導入
                       たとえばフォームなどであらかじめNULLが予定され、その数が知れているような場合は、
                      そのままひとつのテーブルにすることもあるけど、不定や数がわからないような場合は
                      表分割するね。

                      NULLについてはOracleのVARCHAR2に長さゼロの文字列をいれるとNULL扱いという方言がある
                      詳しくは以下のスライドを参照。

                      NULLとの戦いRDBMS実装編

                       ちょっとはずれるけど「データのクレンジング」も必要。文字列型に見えない文字(半角スペースや、

                      タブ、コントロールコード、全角スペース(!)を入れて一致しないとかある。

                      たしかきたみりゅうじさんの漫画でもあったけど、NULLのかわりに'NULL'という文字列を入れている
                      人とか。。。。(ちなみに漫画の公開は最新五本だけなので、この話は現在みれません)
                      http://gihyo.jp/dev/serial/01/funny-play/

                      NULLは値ではないので、NULL値と呼ぶの禁止。当然↑のようなNULL値も禁止 w

                      ■主キーの定義変更
                       ・主キーが既約ではない場合。

                      「既約」ってナニ(?)
                      →読書会の後みつけた。p.79の真ん中あたり。本の索引にないので、ほしい。

                       MySQLでは主キーは必ずクラスターインデックス(他のRDBMSでは必ずしもそうでない)なので、結構変更には時間がかかる。

                      ■番外: 制約使う? 使わない。
                       アプリ側ではさまざまなレベルの人がいるので、アプリ側て担保するのは難しい。
                      使えるならRDBMSの機能の制約を使う。

                       SQLアンチパターンの和田さんいわく「悩んだら寿命の長い方にあわせる」ということらしい。
                      DBとミドルで悩んだらDB, ミドルとアプリで悩んだらミドル。ウワモノは頻繁に変わるけれども
                      バックエンド、特にデータはなかなか変わらない、ので。(人づての伝聞なので詳細違うかも。。。)

                      ■おまけ: トリガーと外部参照制約を使った2テーブルの同期
                       これは実装にあたりいろいろと考慮点がありそうなので、また別の機会に。

                      次回は2016-03-02(wed)予定です。

                      JUGEMテーマ:コンピュータ


                      | meijik | データベース | 00:49 | comments(0) | trackbacks(0) | - | - |
                            1
                      2345678
                      9101112131415
                      16171819202122
                      23242526272829
                      3031     
                      << December 2018 >>
                      + 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