キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
<< Effective SQL 日本語版が発売されます | main | PostgreSQL9.6以降同期レプリケーションは名前どおりのモード(synchronous_commit=remote_apply)あり!@PGECons勉強会 #1 >>
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) | - | - |









    http://blog.kimuradb.com/trackback/877505
       1234
    567891011
    12131415161718
    19202122232425
    2627282930  
    << November 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