【初SQL】8 グループ化と集約化

そろそろSQL文が長くなってくるので、sql-mysqlモードを使いはじめることにする。
これはEmacs本体の配布に同梱されていて、M-x sql-mysqlで立ち上がる。使い勝手が悪いようなら、他の選択肢を探す。

• 8.1 グループ化
  
  
  
  • なんかこのあたり、構文と意味がぐちゃぐちゃ。SQLは汚ないな。
    
  
  
• 8.2 集約関数
  
  
  
  • この本を読んでいくなかで、徐々に徐々に違和感というか居心地の悪さが増していったのだが、その理由がわかった。やはり、スキーマというかサンプルデータベースの構造に関する説明なしに、データ取得用のSQLをどんどん繰り出されても、暗闇に何かをなげたら何かがかえってくるみたいな話になってしまっているからだ。なので、ここらへんで、勝手ながらサンプルデータベースの構造の理解を進めてみる。
    
  • Webでいろいろ調べてみることにする。米国の金融用語についてはまったく素人なので、間違っている可能性が少からずです。ご注意ください。
    
    
    
    • 付録AのER図を眺める。まあ、ER図はあまり役に立たないものだ。
      
    • LearningSQLExample.sqlを眺める。ポイントは次のとおり。
      
      
      
      • department(部門)とbranch(支社)とtitle(役職)は独立した概念である。
        
      • employeeのtitleは列挙型ではなく、varcharである。現況を調べる必要あり。
        
        
        
        • 現況。
          

          
          mysql> select title from employee group by title;
          +--------------------+
          | title              |
          +--------------------+
          | Head Teller        |
          | Loan Manager       |
          | Operations Manager |
          | President          |
          | Teller             |
          | Treasurer          |
          | Vice President     |
          +--------------------+
          

          
          
        • President, Vice Presidentはわかる。
          
        • Teller: 出納係;出納主任;窓口係;計算係; と辞書にはある。これじゃ言葉の意味として多様すぎないか?
          
        • Head Teller: Tellerのリーダのことだろう。
          
        • Treasurer: 会計[出納]係, 経理[財務]部長; と辞書にはある。しかし、会計係と財務部長じゃずいぶん違わないか???
          
        • Loan Manager: 融資の責任者、だろう。
          
        • Operations Manager: このOperationは投機取引のことなのか、業務運用のことなのか、、、
          
        • 役職の階層構造をみる。
          

          
          mysql> select it.title, sup.title from employee it inner join  employee sup on it.superior_emp_id = sup.emp_id;
          +--------------------+--------------------+
          | title              | title              |
          +--------------------+--------------------+
          | Vice President     | President          |
          | Treasurer          | President          |
          | Operations Manager | Treasurer          |
          | Loan Manager       | Operations Manager |
          | Head Teller        | Operations Manager |
          | Teller             | Head Teller        |
          | Teller             | Head Teller        |
          | Teller             | Head Teller        |
          | Head Teller        | Operations Manager |
          | Teller             | Head Teller        |
          | Teller             | Head Teller        |
          | Head Teller        | Operations Manager |
          | Teller             | Head Teller        |
          | Teller             | Head Teller        |
          | Head Teller        | Operations Manager |
          | Teller             | Head Teller        |
          | Teller             | Head Teller        |
          +--------------------+--------------------+
          17 rows in set (0.00 sec)
          

          
          役職と部門の関係もみる。
          

          
          mysql> select it.title, dep.name from employee it inner join department dep on it.dept_id = dep.dept_id;
          +--------------------+----------------+
          | title              | name           |
          +--------------------+----------------+
          | Operations Manager | Operations     |
          | Head Teller        | Operations     |
          | Teller             | Operations     |
          | Teller             | Operations     |
          | Teller             | Operations     |
          | Head Teller        | Operations     |
          | Teller             | Operations     |
          | Teller             | Operations     |
          | Head Teller        | Operations     |
          | Teller             | Operations     |
          | Teller             | Operations     |
          | Head Teller        | Operations     |
          | Teller             | Operations     |
          | Teller             | Operations     |
          | Loan Manager       | Loans          |
          | President          | Administration |
          | Vice President     | Administration |
          | Treasurer          | Administration |
          +--------------------+----------------+
          18 rows in set (0.00 sec)
          

          
          ああ、わかった。
          

          
          President (社長)
            ->Vice President (副社長)
            -> Treasurer (財務担当役員)
              -> Operations Manager (本部長)
                -> Loan Manager (融資部長)
                -> Head Teller (出納係統括)
                  -> Teller (出納係)
          

          
          
        
        ということだ。
        
      • product_typeのnameは列挙型ではなく、varcharである。
        

        
        mysql> select * from product_type;
        +-----------------+-------------------------------+
        | product_type_cd | name                          |
        +-----------------+-------------------------------+
        | ACCOUNT         | Customer Accounts             |
        | LOAN            | Individual and Business Loans |
        | INSURANCE       | Insurance Offerings           |
        +-----------------+-------------------------------+
        3 rows in set (0.00 sec)
        

        
        これはまんまだな。
        
      • productのproduct_cdもvarchar。調べる。
        

        
        mysql> select * from product;
        +------------+-------------------------+-----------------+--------------+--------------+
        | product_cd | name                    | product_type_cd | date_offered | date_retired |
        +------------+-------------------------+-----------------+--------------+--------------+
        | CHK        | checking account        | ACCOUNT         | 2000-01-01   | NULL         |
        | SAV        | savings account         | ACCOUNT         | 2000-01-01   | NULL         |
        | MM         | money market account    | ACCOUNT         | 2000-01-01   | NULL         |
        | CD         | certificate of deposit  | ACCOUNT         | 2000-01-01   | NULL         |
        | MRT        | home mortgage           | LOAN            | 2000-01-01   | NULL         |
        | AUT        | auto loan               | LOAN            | 2000-01-01   | NULL         |
        | BUS        | business line of credit | LOAN            | 2000-01-01   | NULL         |
        | SBL        | small business loan     | LOAN            | 2000-01-01   | NULL         |
        +------------+-------------------------+-----------------+--------------+--------------+
        8 rows in set (0.00 sec)
        

        
        checking account (account): 当座預金 (手形や小切手の支払いを決済するためのもの)。transactional accountの北米名。
        savings account (account): 普通預金 (自由に預入・払戻ができる預金)
        money market acccout (account): 金融市場預金 (当座預金の一種。ただし、比較的ハイリスクハイリターン。銀行は金融市場への投資運用に利用する)
        cerificate of deposit (account):譲渡性預金証書 (定期預金のひとつ。預金者がこれを金融市場で事由に譲渡できる)
        home mortgage (loan): 住宅ローン
        auto loan (loan): 自動車ローン
        business line of credit (loan): 法人クレジットカード?
        small business loan (loan): 中小企業融資
        
      • officerって何?
        
        
        
        • どうやら、business(法人)における役員のこと。
          
        
        
      • accountのpending_balanceって何?
        
        
        
        • available balance: 利用可能残高? (払戻し等、利用できる金額)
          
        • pending balance: 銀行が処理待ちで保持しているものも勘案した残高? (小切手などを即時処理せずに、銀行が信用調査するなど?)
          
        
        
      • transactionのtxn_type_cdの'DBT'と'CDT'は何?
        
        
        
        • 簿記の、debit(借方)、credit(貸方)のことかな?
          
        
        
      • transactionのfunds_avail_dateは何?
        
        
        
        • そのtransaction(取引)の結果として利用可能と処理された資金が実際に利用可能となる日時かな。
          
        
        
      
      
    
    
  • PCLのときも書いたが、なぜ、オブジェクトやエンティティの説明のときに銀行や預金を持ち出すのか？　以上の用語の説明は、グーグル、Yahoo!、Wikipediaや金融機関サイトなどをいろいろ検索しまくって、それらかの情報をつぎはぎにしてやっと構成したものだ。用語体系として、または、概念体系として、英語のサイトですらちゃんと説明できているところが簡単には見付からないということだ。例えば、図書館の貸出システムとかの方が、例としてはマシなんじゃないか？
    
  • さて、進める。
    
  • 集約関数は、groupに対して作用する。
    
  
  
• 8.3 グループの生成
  
  
  
  • この節、翻訳がいまいち。
    
  • roll-up:巻き上げ式
    
  • ドリルダウン:データの集計レベルを1つずつ掘り下げて集計項目をさらに詳細にする操作。
    
  • うーん。5.1では、with rollupの結果とwith cubeの結果が同じだ。。。
    
  
  
• 8.4 グループのフィルタ条件
  
  
  
  • 集約関数など、グループ化された後に生成される値をフィルタ条件に用いるにはhaving節を使う。
    
  
  

こつこつ。

【初SQL】7 データの生成、変換、操作

前回は、MySQLが5.1になってもSQL標準にあまり準拠していなくて、へこんだ。
気をとりなおして、進む。

• 前説
  
  
  
  • おお、文字列、数値、時間データの生成、変換、操作などについては、SQL言語には含まれていないんだ。。。
    
  
  
• 7.1 文字列データの操作
  
  
  
  • 処理が進んじゃって、別途Warningがでる、というのはいやーな感じ。
    
  • お、quoteだ。
    
  • DBでは文字列のインデックスは1始まりなんだ。
    
  
  
• 7.2 数値データの操作
  
  
  
  • 特になし。
    
  
  
• 7.3 時間データの操作
  
  
  
  • 特になし。
    
  
  
• 7.4 変換関数
  
  
  
  • ベンダー固有関数ではなくcastを使おう。
    
  
  

この章の内容をどう捉えるか。
まず、RDBMSが提供するSQL+αで情報の表示をカスタマイズするのには使える。また、その道具立てで、どう情報をRDBにいれるかというときにも使える。そうか、データの入出力関係、ということか。CLでいうと、基本的なデータ型のリテラルとそれらを引数とする関数、ということか。

こつこつ。

【初SQL】6 集合

• 6.1 集合理論：入門
  
  
  
  • ベン図で集合のunionなどを紹介。
    
  
  
• 6.2 集合理論：実践
  
  
  
  • ああ、なるほど。異なるエンティティどうしでも、内部構造が同じなら集合演算できるんだ。
    
  • 何か、新鮮。こんなSQL書いたことなかった。
    

    
    mysql> select 1 num, 'abc' str
        -> union
        -> select 9 num, 'xyz' str;
    +-----+-----+
    | num | str |
    +-----+-----+
    |   1 | abc |
    |   9 | xyz |
    +-----+-----+
    2 rows in set (0.00 sec)
    

    
    
  
  
• 6.3 集合演算子
  
  
  
  • union all
    
  • union
    
  • intersect (なんとMySQL5.1でもintersectに対応していない。どうなってるの???)
    
  • intersect all (MySQL5.1 非対応)
    
  • except (MySQL5.1 非対応)
    
  • except all (MySQL5.1 非対応)
    
  • いや、MySQLまずいんじゃないか、これじゃ。Postgreはちゃんと対応している。しかしACLがMySQLよりみたいだからしょうがなし。
    
  
  
• 6.4 集合演算のルール
  
  
  
  • ルールの紹介。
    
  
  

こつこつ。

【初SQL】5 複数テーブルからのデータの取得

• 5.1 結合とは何か
  
  
  
  • 2つのtableから1つのtableを生み出す方法。2引数1返却値の手続きみたいなもんだな。
    
  • 結合構文は、SQL92のものを使うべしだよ。
    
  
  
• 5.2 3つ以上のテーブルの結合
  
  
  
  • 内部結合の結合状態はfrom節ではなくon節が決める。
    
  • サブクエリでつくったテーブルもinner joinの対象にできる。そうするとサブクエリ時にそれらのテーブルに関する絞り込みができる。まあそれはそうなのだが、これは何のためにするのだろう？ inner joinしちゃってwhere節で書けばよいように思うが。
    
  • 同じテーブルを複数回inner joinするにはエイリアスを変えればよいよ。
    
  
  
• 5.3 自己結合
  
  
  
  • 同じテーブル、というのは自分自身でもよい。
    
  
  
• 5.4 等結合と非等結合
  
  
  
  • おお。等号じゃなくても結合できるんだ。そして外部リレーションキーをもたなくても結合できるんだ。
    
  
  
• 5.5 結合条件とフィルタ条件
  
  
  
  • ANSIの構文は柔軟なので、where節とかon節が柔軟にかけるよ。だけど、もっとも正式なというかわかりやすく書くべきだよ。
    
  
  

こつこつ。

【初SQL】4 フィルタリング

• 前説
  
  
  
  • 要旨：insert以外のSQLデータ文はwhere節によって処理対象をフィルタリングできる。
    
  
  
• 4.1 条件の評価
  
  
  
  • 要旨：where節のAND・OR・NOTの評価方法の紹介。
    
  
  
• 4.2 条件の構築
  
  
  
  • う、構文、複雑。
    
  
  
• 4.3 条件の種類
  
  
  
  
  
  • 等価条件
    
  • 範囲条件
    
  • メンバーシップ条件
    
  • 一致条件
    
  
  
  • 正規表現はSQL標準ではなさそうだ。
    
  
  
• 4.4 null
  
  
  
  • nullはCLのnilとはずいぶん違う。
    
  
  

こつこつ。

【初SQL】3 クエリの入門

• 3.1 クエリの仕組み
  
  
  
  • クライアントサーバ型だけど、mysqlコマンドとサーバはREPLみたいなものを成しているのだな。
    
  
  
• 3.2 クエリの節
  
  
  
  • 特になし。
    
  
  
• 3.3 select節
  
  
  
  • select節はCLでいうとformat節なんだ。で、その返却値はリストのリストみたいな。
    
  • select節で指定したdirectiveが、対象としてる(from節で指定したtable)をどう食べるかを指定している。
    
  
  
• 3.4 from節
  
  
  
  • SQLではselectの結果で返されるもの(result set)などはfrom節において、ファーストクラスらしい。
    
  • それによって多段の(関数?)適用ができる仕組みのようだ。
    
  • ビュー関係のサンプルは、5.1ではそのままで実行成功した。
    
  • テーブルエイリアスも5.1はOK。MySQL便利になったんだなぁ。
    
  
  
• 3.5 where節
  
  
  
  • where節はCLでいうと探索系の関数に与える比較関数みたいなものだな。
    
  
  
• 3.6 group by節とhaving節
  
  
  
  • これは8章でやるよ。
    
  
  
• 3.7 order by節
  
  
  
  • order by節は、select節に食わせる前に対象をsortする指定だな。
    
  
  

CLとの対比でみていくと面白い。
こつこつ。

【初SQL】2 データベースの作成と設定

• 2.1 MySQLデータベースの作成
  
  
  
  • MySQL自体は導入済みなので、ここの指示にしたがって設定を実施する。
    
  • rootのパスワードを設定しようとするがだめ。
    

    
    mysql> set password for root=password('xxxxxxxxxxxxxxxxxxx');
    ERROR 1133 (42000): Can't find any matching row in the user table
    

    
    ん? と調べると、
    

    
    mysql> select user from user;
    +------+
    | user |
    +------+
    | root |
    |      |
    | root |
    |      |
    | root |
    +------+
    5 rows in set (0.00 sec)
    

    
    いるけど。しょうがないからmysqladminからやってみる。
    

    
    $ /usr/local/mysql/bin/mysqladmin -u root password xxxxxxxxxxxxxxxxxxx
    $ sudo /usr/local/mysql/bin/mysqladmin reload
    Password:
    /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
    error: 'Access denied for user 'root'@'localhost' (using password: NO)'
    

    
    あれ? mysqlをためす。
    

    
    $ /usr/local/mysql/bin/mysql -u root
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    

    
    となぜかパスワードがかかっている。mysqlに入ってuser tableをみてみる。
    

    
    mysql> select user, host, password from user;
    +------+------------+-------------------------------------------+
    | user | host       | password                                  |
    +------+------------+-------------------------------------------+
    | root | localhost  | *B40BADE4ACCE28CAA2A785AB5BFCBDEDDB985F11 |
    | root | hoge.local |                                           |
    | root | 127.0.0.1  |                                           |
    |      | localhost  |                                           |
    |      | hoge.local |                                           |
    +------+------------+-------------------------------------------+
    5 rows in set (0.00 sec)
    

    
    う、いちおうhost=localhostには入っている。不審だ、、、まあいいや。
    
  • ユーザ'lrngsql'を作成。
    
  • bankデータベースを作成。
    
  • えっと、ExamplesのURLは変わってた。http://examples.oreilly.com/learningsql/からDL。
    
  • これをsourceで読込み。
    
  • よかった！とりあえず設定できた！
    
  
  
• 2.2 mysqlコマンドラインユーティリティの使用
  
  
  
  • 問題なし。
    
  
  
• 2.3 MySQLのデータ型
  
  
  
  • この本では、基本である3つのデータ型(文字・日付・数値)のみ取扱う。
    
  • えっと、このように「この型のレンジはこう」とか言う話のときは、自身のシステムでそれを確認する方法も示して欲しい。
    
  • 時間が秒までしかないのが、やはり衝撃。あとカレンダーの概念がないのも。
    
  
  
• 2.4 テーブルの作成
  
  
  
  • 指示通り操作を実施。
    
  
  
• 2.5 テーブルへのデータの挿入と変更
  
  
  
  • 指示通り操作を実施。
    
  
  
• 2.6 文も使い方次第
  
  
  
  • あれ? 外部キー制約のエラーがでない。
    

    
    mysql> insert into favorite_food (person_id, food)
        -> values (999, 'lasagma');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from favorite_food;
    +-----------+---------+
    | person_id | food    |
    +-----------+---------+
    |         1 | cookies |
    |         1 | nachos  |
    |         1 | pizza   |
    |       999 | lasagma |
    +-----------+---------+
    4 rows in set (0.00 sec)
    

    
    
  • とりあえず、先へ進むことにする。
    
  
  
• 2.7 bankスキーマ
  
  
  
  • 特になし。
    
  
  

やっぱPrologとSQL(RDB?)は似てるよなぁ。
こつこつ。

【初SQL】1 背景情報

この章はほんとに背景情報。2章からの具体的な作業に入る前に、初心者が頭に入れておくべきものごとをざっと整理したもの。
特記事項はなし。

こつこつ。

【初SQL】初めてのSQLを読む

「初めてのSQL」を読むことにした。

関係モデルというのはやはりハマるときは強力なモデルだと思うからだ。
オブジェクトモデルとの相性はケースバイケースだが、データモデルとして無視できないものだろう。

また、Prologの理解を深めていくためにもRDBをもっと深く理解したい。

この本を選んだのは、MySQLを土台にしているからだ。ACLにはMySQLインターフェイスが用意されているので、先々CLからRDBをいじるときに有利だ。