キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
「PostGIS入門の入門」に入門してきた。
0
    RDBMS-GIS(地理情報・位置情報) Advent Calendar 2023の二日目のエントリです。

    GIS関連、どこから始めるか、というのは永遠の課題のような気がしますが、
    PostgreSQLにGIS拡張をほどこしたPostGISというのは有力な選択肢のひとつです。

    以前Let's POSTGRESに「PostGISとは?」というエントリを書いた寺元さんが
    今年のポスグレカンファレンスで発表した【T4】PostGIS入門の入門が最新版の
    入門ということで入門してみました。以下のリンクの講演スライドから見れますので、
    入門がまだな方は是非入門してみてください。

    PostgreSQL Conference Japan 2023■プログラム

    私は寺元さんの発表で「計算量では以下ですので、

    GEOMETRY < GEOGRAPHY

    PostGIS マニュアルは GEOGRAPHYおしてますが、
    登壇者は(無暗に計算量を増やしたくないので)GEOMETRY推し。
    必要なときにGEOGRAPHYにキャスト」というのが耳に残りました。

    JUGEMテーマ:コンピュータ



    | meijik | データベース | 21:52 | comments(0) | - | - | - |
    SQL:2023 Firebirdの場合: UNIQUE null treatment (F292)
    0
      本エントリはFirebird Advent Calendar 2023の二日目のエントリです。

      今日から約二週間分のエントリの元ネタは以下のエントリです。

      SQL:2023 is finished: Here is what's new(Peter Eisentraut)

      一つのカラムに対してUNIQUEインデックスをつけると、NULL以外の値はユニークですが、
      NULLは重複登録可能です!

      と言いたいところですが、IBM Db2とMicrosoft SQL Serverは違います。
      NULLもあたかも値のように一つしか登録できません。詳細は以下のエントリをご参照ください。

      DB2での一意インデックス(Unique index)の実装について

      では、マルチカラムのUNIQUEインデックスはどうでしょうか?

      IBM Db2とMicrosoft SQL Serverはわかりやすいです。
      値と同様にユニークネスを担保するために、二回目の? のINSERTはエラーになります。

      CREATE TABLE t1 (
      a int,
      b int,
      c int,
      UNIQUE (a, b, c)
      );
      and

      INSERT INTO t1 VALUES (1, NULL, NULL);
      INSERT INTO t1 VALUES (1, NULL, NULL); -- ?

      ややこしいのは、Firebird, Oracle DB, PostgreSQLです。
      これらのRDBMSは複合UNIQUEインデックスのときには、NULLを値のように扱います。
      つまり、二回目の? のINSERTはエラーになるのです。

      そう考えるとMySQLの実装は理にかなっています。
      二回目の? のINSERTはエラーなく実行できます。

      私がこの挙動の違いに気づいたのは、松信さんが書いた以下の本のp.55のColumn「一意インデックスとNULL値」でした。
      もう十年以上も前ですね。

      現場で使えるMySQL(翔泳社)

      その後以下の拙書にて、MySQL以外のRDBMS動作をエミュレートするトリガーをMySQLに実装するサンプルを
      「p.211 ■他社RDBMSインデックスのエミュレート」として掲載しています。

      プロになるためのデータベース技術入門(技術評論社)

      UNIQUE null treatment (F292)は、このような実装ごとの動作の違いを
      明確にしたもので、MySQLのように二回目の? のINSERTがエラーなく実行できるようなUNIQUE制約を
      UNIQUE NULLS DISTINCTで定義、MySQL以外のデータベース(Firebird)のようなふるまいを
      UNIQUE NULLS NOT DISTINCTで定義するように定めました。

      Firebirdでの一意インデックス(Unique index)の実装について

      # IBM Db2以外のデータベースでは一意制約と一意インデックスは同じです。(一意制約実現のために一意インデックスを張るので) Db2では一意制約と一意インデックスは違うものになります。詳細は冒頭の「DB2での一意インデックス」のエントリをご参照ください。

      JUGEMテーマ:コンピュータ



      | meijik | データベース | 16:05 | comments(0) | - | - | - |
      Firebird 2023年の振り返り
      0
        本エントリはFirebird Advent Calendar 2023の一日目のエントリです。

        「しばしばリリース」はかなえられませんでしたが、今年は以下のリリースがありました。

        2023-08-04 Firebird 3.0.11
        2023-08-04 Firebird 4.0.3
        2023-11-08 Firebird 4.0.4

        サブリリースのため特に目立った拡張はありません。
        そのため、本年のFirebird Advent Calendarでは、リリースされたSQL標準(SQL:2023)について
        Firebirdの視点から解説してみようと思います。

        SQL:2023 is finished: Here is what's new(Peter Eisentraut)

        JUGEMテーマ:コンピュータ



        | meijik | データベース | 12:45 | comments(0) | - | - | - |
        最近のRDBMSとGISザッピング2020
        0
          最近のRDBMSとGISザッピング

          この日記は、RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2020 の15日目の記事です。
          # 遅れて実際は2021-01-01に書いています。

          最近はJSONが熱い! と思ってましたが、まさかのGISが熱いRDBMS界隈です。

          なんかいろいろ試してみたかった本ブログエントリですが、ネタとそれにかける時間がなく、
          テキトーにRDBMS & GIS でザッピングしてみましたー。

          1.Oracle DB
           去年のエントリどおりOracle DatabaseのすべてのエディションでSpatial and Graphが追加コストなしに利用できるようになりました。

          当然オラクルクラウドでも利用できるので、以下のブログエントリのようにキャッキャウフフすることが可能です。

          ドラクエウォークのおみやげを集めるのに最寄り駅を調べたい

          東京上空を飛ぶ航空機のデータ(の一部)をOracle Spatial Studio 20.1で可視化してみた

          2.Microsoft SQL Server
           SQL Server 2008からSpartial Data Typeをサポートしています。

           その機能はクラウド(Azure)でも利用でき、SQL Serverで利用できる機能は、ほとんどAzure SQL データベースで利用できるように見えます。

          geometry インスタンスの OGC メソッド

           どの関数がどのプラットフォームで利用できるのか、については各関数のサポート状況で該当するプラットフォームの有無(Azure SQL Managed Instance, Azure Synapse Analytics, Parallel Data Warehouse)をご参照ください。

          3.PostgreSQL(PostGIS)
           オープンソースで使えるRDBMS GIS機能としては、最古参かついわばデファクトスタンダードであるPostgreSQL, エクステンションであるPostGISを追加すると、幅広く手堅い機能が利用できます。
           PostGISの最新バージョンは3.0.3. PostgreSQL 13.1, と GEOS 3.8.1, pgRouting 3.1.1で最高のサービスを提供する、とあります。
           PostgreSQLベースの製品・サービスはPostGISを利用できるものもあります。例えばGreenplumでPostGIS利用できます
           クラウドではPostGISが有効にされているか、されてない場合には有効にできるかどうか、ご確認ください。例えばPostgreSQLベースのAuroraやRDS for PostgreSQLでは、機能を有効にすれば使えるようです。(PostGISのバージョンは、それぞれのPostgreSQL(やAurara)のバージョンに縛られるみたいですが。

          4.MySQL 8.0
           MySQLではGIS機能がビルトインで提供されています。そのため、オンプレでもクラウドでは、機能が利用できないということはありません。便利ですね!
          クラウドでは現在MDSとMAS(MySQL Analytics Service)が提供されていますが、MASではHeatWaveという仕組みでDWHに対応しています。これは巷のDWHに比べると以下の点が良いです。

          (1) オリジナルのMySQLと同機能を利用できる。
          (2) DWHに対応できる高速動作の仕組み。
          (3) レプリケーションでデータを持ってこれる。

          意外に(1),(3), とくに(3)はMySQL独特でいいとおもうんですけどねー。

          ただ今のところはHeatWaveの制限で、GIS関連は利用できません。今後に期待。

          This section lists functions, data types, variables, JOIN types, SQL modes, and other expressions and functionality that are not supported by HeatWave

          Functions:
          ・XML, JSON, Spatial, and other domain specific functions.

          Data types:
          ・Spatial data types. See Spatial Data Types.

          本体のGIS機能自体はさかいさん、やまさきさんをはじめ、関連する方々の活動のおかげで改善がすすんでいます!

          5.Oh! その他と突撃となりの晩御飯
          Db2についても、古くからSpartial Extenderで対応してますし、クラウドでもきっと。。。。使えるようですね。

          Getting Started with SQL Query Geospatial

           Google BigQueryでもサポート始まったようです(標準SQLのみ)

          Amazon Redshiftでは、PostGISではなく独自実装(?)で利用できます。

           当然これらのライバル(?)のSnowflakeでもGIS機能はサポートされているようです。

          もう一つの大きな流れは、RDBMSに格納するのではなく、膨大なデータに対して、スキーマ定義をかぶせて、それに対してSQLクエリを発行できるようにするものです。そして、それにGISのクエリが使えるものがあります。

          AmazonのAtenaは、S3でのデータの直接分析を簡易化するインタラクティブなクエリサービスを提供しています。

          地理空間データのクエリ

          また、OSSではPrestoがあります。Hadoopに対してSQLを発行できるようになります。
          PrestではST_ではじまるGeospatial Functionsが利用できます。
          みなさんおなじみのUberでは、全体的にそのような仕組みを使っているようです。

          A deep dive into Uber's engineering effort to optimize geospatial queries in Presto.(O'REILLY)

          他にもグラフデータベース(?) とかいろいろなワザを使っているようです。Uber

          Food Discovery with Uber Eats: Building a Query Understanding Engine(Uber Engineering)

           ちなみにFirebirdには「まったくありません」機能リクエストはあがっていますが、放置。。。。

          JUGEMテーマ:コンピュータ



          | meijik | データベース | 07:03 | comments(0) | - | - | - |
          プログラマのためのSQL第四版読書会(37) 38.5 その他の時間関数:君のDOW(だう)はどう?
          0
            本日は以下の勉強会でした。

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

            38.5 その他の時間関数(Other Temporal Functions)で、DOW(Day Of Week)という曜日を数値で返す関数の説明があり、
            とりあえず翻訳でSQL Anywhereから差し替えられた[PostgreSQL]説明が間違っているのと、各データベースの違いが顕著だったので、ここにまとめておきます。

            [誤] 2〜8の値を返し、8が日曜日である。
            [正] 0〜6の値を返し、0が日曜日である。

            なんかぜんぜんちがうね。。。。

            なお、数値が日曜日スタートか、月曜日スタートか、数値のはじまりが0か1かですげー混乱すると思うんですが。。。。

            ちなみにISOでは、月曜日スタートで、月曜日が1、日曜日が7になります。

            ISO 8601(wikipedia)
            D は曜日を表し、月曜日が 1、日曜日は 7 である。「8」「9」は表記数値とされない(エラー扱い)。

            上記例のPostgreSQLにもisodowという関数があり、ISOと同様な動作になります。

            ちなみにODBCでは、日曜日スタートで、日曜日が1, 土曜日が7になります。
            (プログラマのためのSQLではSQL AnywhereのDOWがとりあげられていて、これと同じ仕様です)
            MySQLのDAYOFWEEKは、このODBC標準に準拠しています。
            MySQLには曜日を求めるもうひとつの関数WEEKDAYがあり、こちらは月曜日スタートで月曜日が0、日曜日が6になります。二つを比較した次のブログエントリがわかりやすいです。

            MySQLで曜日を調べる(xykの日記)

            MySQLにはISO準拠のものがありませんが、WEEKDAYで求めた結果に+1するとISO準拠となります。

            [IBM Db2]
            Db2には1〜7を返す関数が二つあり、
            DAYOFWEEKは日曜日はじまりで、日曜日が1、土曜日が7を返します。
            DAYOFWEEK_ISOは月曜日はじまりで、月曜日が1、日曜日が7を返します。

            [MS SQL Server]

            SQL Serverでは、まず何曜日はじまりか、を設定する変数DATEFIRSTがあり、SET DATEFIRSTで開始曜日を通常7(つまり日曜日スタート)にするか1(つまり月曜日スタート)にするか決定できます。(@@DATEFIRSTで設定内容を確認できます)
            関数としてはDATEPART(weekday, 日付)もしくは短縮形のDATEPART(dw, 日付)で指定し返す値の範囲は1〜7です。
            前者の設定、つまり日曜日スタートでは、ODBC互換。
            後者の設定、つまり月曜日スタートでは、ISO互換になります。

            [Oracle]
            TO_CHAR(日付,'D')
            NLS_TERRITORYで、日曜日はじまりか、月曜日はじまりになるらしい。
            日本、アメリカでは日曜日スタートで、ODBC互換
            欧州では月曜日スタートで、ISO互換になります。

            [SQLite]
            strftime(日付,'%w')日曜日はじまりで、ゼロスタート。日曜日が0, 土曜日が6です。
            +1するとODBC互換になりますね。

            sqlite> select current_date;
            2019-03-01
            sqlite> select strftime('%w',current_date);
            5
            sqlite> select strftime('%w',current_date)+1;
            6

            ISO互換にするには、0を返すときに7を返せばいいので、以下のような感じかな。

            select case strftime(日付,'%w) when 0 then 7 else strftime(日付,'%w) end;

            modifier指定のweekdayは、weekdayを取得するのではなく、元の日付に対してweekday Nで
            指定した日付を返します。2019-03-01は金曜日で5ですので、以下のように4を指定すると木曜日、
            6を指定すると土曜日を返します。木曜日は2019-02-28ではなく、2019-03-07を返すところに注意。

            sqlite> select date(current_date,'weekday 4');
            2019-03-07
            sqlite> select date(current_date,'weekday 6');
            2019-03-02

            [Firebird]
            FirebirdではSQL標準(SQL-99)のEXTRACTを使います。
            EXTRACT(WEEKDAY FROM 日付)
            戻す値はSQLiteと同じです。

            EXTRACTは標準SQLなので、実はOracle, IBM Db2, MySQL, PostgreSQLがサポートしています
            なら、全部EXTRACTで統一すればイイとおもうのですが、実はWEEKDAY, YEARDAYはFirebirdしか
            サポートしていない
            のです。(PostgreSQLはWEEKDAYの代わりにdow, YEARDAYの代わりにdoyをサポートしています)そのためSQL標準だからといって、あまり強く押すことができません。

            JUGEMテーマ:コンピュータ



            | meijik | データベース | 02:00 | comments(0) | trackbacks(0) | - | - |
            プログラマのための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) | - | - |
                           12
                      3456789
                      10111213141516
                      17181920212223
                      24252627282930
                      31      
                      << March 2024 >>
                      + 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標準(SQL:2023)とFirebird/MySQL/PostgreSQL
                        kitayama_t (12/24)
                      • オープンセミナー2008@岡山
                        meijik (10/15)
                      • 2022年のFirebird
                        tetsuyat@ac.wakwak.com (01/24)
                      • 内藤 陽哉(ないとうようや)先生を偲んで
                        高村京夏 (11/07)
                      • 「エイリアンズ」から入る人のための極私的「キリンジ/KIRINJI」入門
                        meijik (05/02)
                      • 「エイリアンズ」から入る人のための極私的「キリンジ/KIRINJI」入門
                        k (05/01)
                      • Firebird 4.0のレプリケーション(6) 非同期レプリケーション
                        小林正樹 (12/02)
                      • MySQL ProとMySQL Expertを立ち読みする+α
                        MEIJI KIMURA (09/14)
                      • たまたま「プリントヘッドの種類が違います」エラーから復活@MG6230
                        meijik (11/20)
                      • [ミュージシャンに聴かせたい・弾かせたい!] (4) 山田元気様
                        meijik (07/27)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE