SQL Hacks ―データベースを自由自在に操るテクニック- †

  • 作者: Andrew Cumming (著), Gordon Russell (著), 西沢 直木 (翻訳)
  • 日付: 2010-01-27

Google Books でも一部読めます。

↑

SQL Hacks memo †

1章 SQLの基本
1.コマンドラインからSQLを実行するMS SQL Server, Oacle, MySQL, MS Access, PostgreSQL, D2 へのコマンドラインからの接続。
2.プログラムからデータベースに接続するC#, Java, PHP, Perl, Ruby からデーターベースに接続する。
3.条件付きINSERTコマンドを実行するINSERT ... VALUES と, INSERT ... SELECT。
4.データベースの更新UPDATE における、データーの交換ではテンポラリ領域は不要。
5.SQLを利用してクロスワードパズルを解く
6.同じ計算を繰り返さないサブクエリーや VIEW を使用して、SQL をシンプルにする方法。
2章 JOIN、UNION、VIEW
7.既存のクエリに手を付けずにデータ構造を変更するテーブル変更や、VIEW を使用する方法。よく使用される手法。
8.行と列を絞り込むPerl, Java, Ruby, C#, PHP でのバインド変数の使用方法。
9.インデックスで絞り込むカーディナリティと、複合インデックス作成のコツ。
10.サブクエリをJOINに変更する「サブクエリに集約関数が含まれていない場合、サブクエリは必要ない。」は名言。NOT IN を外部結合に書き替えるは使える。
11.集計用のサブクエリをJOINに変更するサブクエリを GROUP BY HAVING を使用して JOIN に変更する方法。
12.複雑なデータ更新を単純化する複雑な条件での UPDATE を記述する方法。
13.適切なJOIN形式を選択する外部結合と内部結合の組み合わさった連鎖結合の場合、内部結合を外部結合に変更する必要がある。星形結合の場合は混在したままで良い。
14.組み合わせを作成するCROSS JOIN を使用して、組み合わせを作成する。
3章 文字列の処理
15.LIKEを使わずにキーワードを検索するMySQL → FULLTEXT インデックス、PostgreSQL → Tsearch2 モジュール、MS SQL Server → MS フルテキストエンジン、Oracle → ctxapp (Oracle Text)
16.複数の列から文字列を検索する複数の列を CONCAT で連結させて LIKE 検索。遅そう。
17.アナグラムを解く線形ハッシュ関数, 二次ハッシュ関数, 指数ハッシュ関数の作成方法。
18.メールアドレスをソートするメールアドレスの分離方法。計算列(ファンクション)インデックスの作成方法。
4章 日付の処理
19.文字列を日付に変換するOracle なら TO_DATE 一発。
20.データの傾向をつかむログデーターの移動平均を取る方法。
21.日付単位でレポートを作成する月別に集計。日付の丸め。
22.四半期レポートを作成するOracle で MONTH, YEAR 関数はないが、EXTRACT 関数で代替できる。
23.第2火曜日を取得するOracle で曜日を求めるには TO_CHAR(date, 'D')を使用する。
5章 数値の処理
24.行をまたいで乗算するEXP(SUM(LN(val))) を使用して、複利計算などの行をまたいだ乗算ができる。
25.残高を求める自己完結(セルフジョイン)による、累積値の算出。Oracle では OVER句の使用で簡潔になる。
26.JOINが返さない行も結果に含める外部結合。(またはユニオン。)
27.重なる範囲を特定する日付範囲同士のオーバーラップを調査する。LEAST()とGREATEST()関数の使用で簡潔になる。
28.ゼロ除算を避けるNULLIF() 関数を使用。
29.COUNT関数を使わずに件数を数えるCASE を使用した条件付きのカウント。
30.複数列の最大値を求めるGREATEST()関数(最小値の場合はLEAST()関数)を使用する。
31.集約された値をバラバラにする宿泊開始日と宿泊日数から、宿泊トランザクションを作成。整数テーブルを使用するのがポイント。
32.「丸め誤差」に対処する丸めてからサマリーするか、サマリーしてから丸めるか、どちらかに統一する。
33.明細と小計を一度で取得する明細と小計をUNIONで結合。または、GROUPING SETS句を使用。
34.中央値を求めるテンポラリーテーブルの使用。
35.集計結果を図で表現するSQL で棒グラフを描く。
36.2点間の距離を計算する三角関数を使用し、二点の GPS 情報から距離を計算する。
37.請求書と振込額の帳尻を合わせる消し込みのために、一致するデーターと一致しそうなデーターを見つける方法。
38.ミスによる桁の入れ替わりを発見する２桁の入れ替わりは、差が ９ の倍数になる。(10*a+b)-(10*b+a)=9*(a-b) これを利用し、ミスした可能性の高いデーターを見つける。
39.累進課税を適用する収入と累進税率表から、累進税率を求める。
40.順位を求めるOracle では、RANK()関数を使用する。
6章 オンラインアプリケーション
41.Webサイトをテーブルに取り込むXSLT で HTML からデーターを抜き出す。
42.SVGを使用してデータからグラフを作成するデーターベースから XML(SVG)を生成する方法。 Oracle では XMLElement()関数とXMLAttributes()関数を使用すると簡潔。
43.Webアプリケーションにページナビゲーションを追加するA～Z の目次を作成する。
44.Microsoft. AccessからMySQLに接続するSSH と MySQL ODBC Connector を使用する。
45.Webサーバのログを処理するhttp の access.log を perl を使用してデーターベースに取り込む。リンク切れ、ページ内移動の調査。
46.データベースに画像を保存するBLOBを使用する方法。ファイル名を格納する方法。
47.SQLインジェクションの脆弱性とはSQL インジェクション攻撃の例。
48.SQLインジェクション攻撃を防ぐPerl, C#, PHP, Java でのエスケープ方法。DBアカウントの権限を制限。
7章 さまざまなデータ構成
49.過去のデータも保存しておく価格変更情報を持っている価格マスターから現在や指定日の価格を取得する方法。
50.構造の異なるテーブルを結合するUNION の使用。
51.行を列として表示する縦持ちを横持ちに変換する方法。自己結合か CASE 文を使用する。
52.列を行として表示する横持ちを縦持ちに変換する方法。UNION を使用する。
53.矛盾するデータを削除する正規化されていないデーターの矛盾を GROUP BY HAVING COUNT()>1 で見つける。
54.テーブルを非正規化するINSERT SELECT JOIN で問い合わせ結果をテーブルに出力する。
55.外部データをインポートする外部から取り込んだ更新できない表と、自分で更新する部分のみ表を分ける。
56.二人の仲を取り持つ縦持ちしている属性同士のマッチング。NOT IN (SELECT) を使用する。遅そう。
57.一意の連番を作成するMax()＋１、Oracle の SEQUECE オブジェクト。※人工的な主キーは避けるべき？
8章 小さいデータの格納
58.データベースにパラメータを保存する１レコードしか格納できないテーブルの作成。１レコードしかないので CROSS JOIN しても問題ない。
59.ユーザごとのパラメータを定義するデーターベースのユーザーを(Oracle の場合) USER で取得し、その値で絞り込む。
60.ユーザごとのパラメータリストを作成する上記 59. のバリエーション。
61.行単位でセキュリティを設定するデーターベースユーザーを使用した、アクセスコントロールの例。
62.テーブルを参照せずにクエリを実行する(Oacle 場合) dual 表を使用。SYS_GUID()は使いどころがありそう。
63.テーブルを使わずに行を生成するUNION で定数を返す SELECT を連結し、サブクエリーで表のように使用する。テーブルの作成権限がないがクエリーのために小さな表を作成したい場合に使用出来そう。
9章 ロックとパフォーマンス
64.分離レベルを決定するアイソレーションの話。ファントムリード、反復不可能読み取り、ダーティーリード。
65.悲観的ロックを使用する変更する予定のレコードだけを FOR UPDATE でロックする。
66.楽観的ロックを使用するロックは使用せずに、更新時に他者が更新していないフラグで絞り込む、更新結果の件数が予定どおりなら OK、そうでないならロールバックする。
67.トランザクションで暗黙的にロックする複数のデーター更新を 1 行の UPDATE で実現する。(SET に CASE 文で条件を記述。) これならロックは不要。
68.送信ボタンの連続クリックに対処するステータスでコントロールする。
69.データベース内で各処理を実行するプログラムで処理するか、データーベースで処理するか。
70.クエリの実行結果を結合するUNION でデーターベースへの問い合わせ回数を減らす。
71.大量の行を取得するデーター検索を、データーベースとプログラムで分担する方法。
72.指定件数の結果を取得するページング処理。例では FETCH を使用せず、ROWNUM で絞り込んでいるので、ひどく重い SQL になっている。
73.データの保存先としてファイルも利用する同じディレクトリにファイルを置きすぎない工夫は良いと思うが、SQL としては見るべきものなし。
74.テーブルを比較して同期をとるdiff みたいなものを SQLで実現。
75.1対多形式のJOINが返すデータ量を減らすヘッダーと明細のジョインは、ヘッダー部分の重複があるので、ヘッダーと明細を UNION で結合し、ネットワークトラフィックを下げる例。
76.大きなデータは圧縮するperl の Compress::Zlib でテキストを圧縮して保存する方法。当然ながら検索対象にできない。
10章 レポートの作成
77.データのないカテゴリもピボットテーブルに含めるExcel のピボットテーブル用に欠測値を生成する。外部結合や UNION を使用。
78.データを範囲ごとに区切る年齢を20代や30代、日付を今月や来月のように、集計単位を変更する。かなり重いSQL。
79.更新する行を特定するDBMS の機能でなく、SQL でレプリケーションを実現する方法。使わない方が無難では？
80.「ケビン・ベーコンの6次」を表現する知り合いの知り合いをたどっていくと6人でどんな人にもたどり着くというやつ。
81.ディシジョンテーブルを作成する送料を決定するためのテーブル(ディシジョンテーブル)を作成し、送料を算出する例。
82.連続値や欠測値を生成する整数が格納された整数表、連続した日付の格納された日付表の作成方法と、外部結合での欠損値の調査。
83.各グループの上位の値を取得するOracle なら ROWNUM を使用すれば良いが、使用できない場合の SQL。
84.列にカンマ区切りリストを保存するカンマで区切ったデーターをそのまま DB に保持する方法。
85.ツリーを横断する例は家系図だが、組織表や BOM などに使用可能なテクニック。Oracle の階層問い合わせも紹介されている。
86.データベースでキューを利用するFIFO キューの実現。
87.カレンダーを生成するカレンダーを SQL で作成する。
88.サブクエリの抽出条件に2つの値を使う副問い合わせに WHERE (Col1, Col2)=(SELECT Col1, Col2 ...) と記述する。
89.5つの項目から3つを選択する求職者とスキル表から、少なくとも求める 5 つのスキルのうち三つを満たしている求職者を検索する方法。GROUP BY HAVING を使用する。
11章 ユーザと管理
90.ユーザアカウントを実装するパスワードのハッシュ作成方法が興味深い。Oracle では、dbms_crypto ライブラリを使用する。
91.テーブル定義のエクスポートとインポートOracle だと、imp と exp。
92.アプリケーションを配置する本番環境設定時の注意点。
93.データベースユーザを自動作成するUnix で、Unix のアカウントと DB のアカウントを同時に作成する方法。
94.一般ユーザと管理者を作成するDB 管理者ユーザー(DBA)作成時のポイント。
95.自動更新を実行するトリガーの作成。
96.行動監視用のログを記録するトリガーで更新ログを取る。
12章 幅広いアクセス
97.匿名アクセスを許可する許可する前に、アカウントを制限する。
98.長時間実行されているクエリに対処するOracle では V$SESSION 表を参照する。
99.ディスクを使い果たさないようにするテンポラリー領域の使い果たし、大きなテーブルの調査。
100.ブラウザからSQLを実行するOracle では iSQL*Plus。
選択肢 投票
参考になった 2  
参考にならなかった 0  
トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2010-03-09 (火) 17:00:27 (5164d)