最終回 OLAP関数で強力な統計処理を実現！―手続き型から理解するSQL （5）集合指向と手続き型 | gihyo.jp
OLAP関数は、この表の右側（手続き型）に属します。「ソート」に関して補足しておくと、ORDER BY句があることで、OLAP関数は必ずソートを行います（そうしないとレコードのランキングなどは計算できません）。SQLも、集約関数や集合演算子など、暗黙にソートを行う機能はこれまでも持っていましたが、明示的にソートを記述する機能はありませんでした（例外はSELECT文の最後に付けるORDER BY句ですが、これは厳密にはSQLの演算子ではなくカーソル定義の一部とされてきました）。 このようにOLAP関数というのは、集合指向と手続き型の考えをミックスした機能だと言うことができます。おそらく、今後もSQLは手続き型やオブジェクト指向の良いところを積極的に取り入れて、より高度で使いやすい言語へ進化を遂げていくことでしょう。 終わりに 最後に、OLAP関数のポイントをもう一度まとめておきまし
最終回 OLAP関数で強力な統計処理を実現！―手続き型から理解するSQL （4）OLAP関数と集約関数を組み合わせる：SQLアタマアカデミー｜gihyo.jp … 技術評論社
この結果にランキングをつければよいわけです。それでは、2つの軸に沿って考えましょう。 第一の軸（カット）：どんなキーでこの結果をカットするべきか？ 結果全体を一つのウィンドウとみなすため、カットは不要。したがってPARTITION BY句も不要 第二の軸（走査順序）：どんなキーで走査するべきか？ もちろん、処理金額の合計の降順。つまりSUM(prc_amt)の降順 ここまでわかれば、問題は解けたも同然です。最終的な答えはリスト7のようになります。 ORDER BY句にSUM(prc_amt)という集約関数を指定しているのが見た目に違和感があるかもしれませんが、論理的にこれで正しいことは、もう読者のみなさんもおわかりでしょう。SELECT句に指定可能な項目ならば、OLAP関数のPARTITION BY句とORDER BY句のキーとして利用できるのです。 リスト7 処理日ごとの処理金額の合計で
最終回 OLAP関数で強力な統計処理を実現！―手続き型から理解するSQL （3）OLAP専用関数 | gihyo.jp
これらOLAP専用の関数は、集約関数としての用途はありません。使うときは自動的にOLAP関数になります（だから必ずOVER句が必要）。 構文は先ほど説明したものとまったく同じですが、1点だけ注意があります。それを、コードを見ながら説明しましょう。口座ごとに入金の多い日の順番で、ランキングを出してみます。 では、先ほど言ったように2つの軸に沿って考えましょう。 第一の軸（カット）：どんなキーでこの結果をカットするべきか？ 口座ごとにカットするので、account_id 第二の軸（走査順序）：どんなキーで走査するべきか？ 入金の多い順なので、処理金額の降順。つまりprc_amtの降順 従って、リスト3、図4のようになります。 リスト3 入金の多い日の順番で、ランキングを表示（OLAP専用関数は引数を持たないことに注意） -- My SQL以外 SELECT account_id, reco
最終回 OLAP関数で強力な統計処理を実現！―手続き型から理解するSQL （2）OLAP関数の基本構文 | gihyo.jp
さて、まずはこのテーブルから、口座IDごとの処理金額の平均を「過去からその当日まで」のデータを使って出力してみます（リスト1、図2）。 リスト1 口座IDごとの処理金額の平均を出力 --My SQL以外（ただしSQL ServerではORDER BY句を除外すること） SELECT account_id, record_date, AVG(prc_amt) OVER (PARTITION BY account_id ORDER BY record_date) AS cumulative_avg FROM Accounts; --My SQL SELECT account_id, record_date, prc_amt, (SELECT AVG(prc_amt) FROM Accounts A2 WHERE A1.account_id = A2.account_id AND A1.re
最終回 OLAP関数で強力な統計処理を実現！―手続き型から理解するSQL （1）OLAP関数とは何か | gihyo.jp
SQLアタマアカデミー 最終回 OLAP関数で強力な統計処理を実現！―手続き型から理解するSQL （1）OLAP関数とは何か はじめに この連載では、SQLの独特な考え方に慣れてもらうため、その集合指向的な側面を強調して取り上げてきました。これはほかの言語が持たない特性のため、慣れないうちはそのパラダイムが奇妙に感じられます。その違和感を軽減することが、本連載の目的の一つでした。 一方最近では、SQLの側でも時代のニーズに対応するため、さまざまな機能を追加しています。その一端で、実はかなり手続き型言語の考え方を取り入れるようになっています。そのため、むしろ新機能のほうが一般的なプログラマにとっては「とっつきやすい」と思えるところがあります。 今回取り上げるのは、そうした新機能の一つで、OLAP関数と呼ばれるものです（分析関数とかウィンドウ関数という名前でも呼ばれます）。標準SQLでは2
第10回 結合大全 （3）外部結合 | gihyo.jp
外部結合～何の「外部」なのか～ 外部結合とは 外部結合（outer join）は、内部結合の次によく使われるタイプの結合です。「内部」と「外部」という名称が示唆するように、これは内部結合と対になる演算です。「内部」が「直積の部分集合」という含意を持っていたことからもある程度、類推がつくと思いますが、「外部」とはどういう意味でしょう。 そう、おわかりですね。答えは「直積の部分集合にならない」です。誤解しないでもらいたいのは、常に部分集合にならないわけではなく、「データの状態によってそういうこともある」という点です。実際にコードを実行して、結果を確認してみましょう。 外部結合には、次の3種類があります。 左外部結合 右外部結合 完全外部結合 このうち左外部結合と右外部結合は実質的には同じ機能を持っています。ただ、マスタとなるテーブルを左に書くなら左外部結合、右に書くなら右外部結合、とい
第10回 結合大全 （2）内部結合：SQLアタマアカデミー｜gihyo.jp … 技術評論社
内部結合～何の「内部」なのか～ 内部結合とは 内部結合（inner join）は、一番よく使われる結合の種類です。ほとんどのSQLの参考書では、結合といえば最初に内部結合から話を始めるものです。構文についてはすでにご存じの方も多いでしょうが、一応ここで紹介しておきましょう。 今、図1の社員テーブルだけ見ると、社員の部署名はわかりません（わかるのは部署IDだけ）。部署名を知るためには、部署テーブルの部署名（dept_name）列の情報を持ってこなくてはなりません。このときの結合キーはもちろん、どちらのテーブルにも存在している部署ID（dept_id）列になります（リスト2、図3）。 リスト2 内部結合を実行 SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name FROM Employees E INNER JOIN Departmen
第10回 結合大全 （1）クロス結合 | gihyo.jp
はじめに SQLを使っていて、結合（join）という演算を使わないことはありません。リレーショナルデータベースでは、設計のセオリーとして正規化というプロセスを踏む場合が多いため、必然的にテーブルの数は複数になります。そうすると、複数のテーブルに散在するデータを統―いわば「逆正規化」―したり、あるいは結果として得たいデータのフォーマットを作るために、私たちはさまざまな種類の結合を利用します。 結合演算は、SQLの演算の中では比較的イメージをつかみやすいものです（少なくとも、相関サブクエリとかCASE式に比べれば）。しかし他方、結合は種類のバリエーションがかなり多く、どういう場合にどういう結合を使うのが適切なのか、迷うことも少なくありません。本稿では、SQLで利用される結合について、どのような種類の結合がどういう動作を行うか、理論的な整理を行いたいと思います[1]。そして、結合という演算
第9回 SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～ （3）便利で危険な相関サブクエリ① | gihyo.jp
裏を返すと、社員テーブルに小川さんと長谷川さんのどちらか1人だけしか存在しなかった場合には、上のUPDATE文はエラーなく実行できます。したがって、このエラーは、テーブルの状態によって出たり出なかったりする幽霊のように厄介な存在です。ときどき、試験のときにデータのバリエーションが不足していてこのエラーを見過ごし、実運用に入ってから出現することがあるので注意が必要です。 対処法 この幽霊に対処する方法は、大きく2通りあります。 ① 情報の登録方法をINSERTに変えてしまう ② 小川さんと長谷川さんのうち、適当にどちらか1人だけを代表に選んで登録する どちらを取るのがよいのかは、その場その場の判断です。①INSERTを使った洗い替え方式は、論理的ですっきりしていますが、常に全行DELETE（またはTRUNCATE）して全行INSERTする必要があるので、更新コストが多くかかります。一方、②代
第9回 SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～ （2）相関サブクエリ | gihyo.jp
SQLアタマアカデミー 第9回SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～ （2）相関サブクエリ 相関サブクエリ では次に、単純に会社全体で一番高齢の社員を抽出する代わりに、男女別に最高齢の社員を抽出することを考えます。 手続き型言語で解く場合 もしEmployeesテーブルがCSV（Comma Separated Values：カンマ区切り）や固定長といった、ただのフラットファイル[1]で、手続き型言語でこの問題を解くとすれば、次のようなアルゴリズムを使うことになるでしょう。 max_ageという整数型の変数を用意し、0で初期化する レコードを1行ずつ走査する カレントレコードのsexが「男」ならば、ageとmax_ageを比較し、ageのほうが大きければ、max_ageにageを代入する すべてのレコードについて走査したら、max_ageを男性の最高齢値として返
第9回 SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～ （1）サブクエリ | gihyo.jp
SQLアタマアカデミー 第9回SQLでループ！ 相関サブクエリの使い方～切れ過ぎるナイフにご用心～ （1）サブクエリ はじめに SQLを使い始めたとき、多くのプログラマが例外なく難儀するのが、SQLで制御構造をうまく書けないことです。ここで言う制御構造とは、平たく言えば条件分岐とループです。といっても別に、SQLに制御構造を書くための機能が備わっていないとか、貧弱というわけではありません。SQLでも、通常の手続き型言語と同等の制御構造を記述することができます。ただ、そのやり方が一風変わっているので、うまく「SQLアタマ」に切り替えられないと戸惑ってしまうのです。 そういう「普通の」プログラマやSEの違和感を軽減するための橋渡しをするのが、本連載の目的の1つでもあるので、これまでにもSQLで制御構造を記述する方法については折に触れて取り上げてきました。しかし、条件分岐のためのCASE式に対し
第8回 SQLにおける論理演算～なぜ真理を隠すのか～ （3）HAVING句で論理演算を行おう | gihyo.jp
ある人材紹介会社に登録されている就業希望者と、その仕事のスキルの組み合わせを表現したものです。本当は、スキルコードも「01102」とかコードで表すのがよりリアリティがありますが、わかりやすさのためスキルの名前をそのまま使うことにします。このテーブルによれば、たとえば100番の人は会計、在庫整理、製造という3つの分野の技術を持っており、300番の人は製造のみの技術を持つ、ということになります。 さて、この会社にはいつもひっきりなしにクライアント企業から人材についての問い合わせが入ります。みなさん、この会社の対応オペレータになったつもりで、クライアントからの問い合わせ条件に合致する人材がいるかどうか、検索してみてください。 ①クライアントAからの問い合わせ 次のような条件であるとします。 条件：「製造」スキルを持っている人材 これは簡単ですね。スキルが1つだけであれば、WHERE句で条件を記述
第8回 SQLにおける論理演算～なぜ真理を隠すのか～ （2）SQLでブール式を使うと | gihyo.jp
もしSQLがSELECT句でブール式の評価をサポートしていれば、リスト1のクエリは問題なく通るはずです（図4）。 リスト1 君、日本に住んでいる？ SELECT name, (state = 'Japan') AS Livin_in_Japan FROM Names; 図4 リスト1の結果 name Livin_in_Japan --------- ---------------- 太郎 true ジョン false カール false ギャビー false 源三郎 true パオロ false 真理値型をサポートしていないDBMSの場合 しかし、ご想像のとおり、このクエリが通るのはPostgreSQLとMySQLのみです（MySQLの場合、結果の表示が0/1で行われます）。残るDBは真理値をサポートしていない以上、結果セットの中に真理値が含まれることを許容するわけもないので、エラ
第8回 SQLにおける論理演算～なぜ真理を隠すのか～ （1）各DBの真理値型のサポート | gihyo.jp
はじめに 真理値と論理演算の重要性 プログラマやSEのみなさんは、真理値（真偽値）というものをご存じのことと思います。ほとんどの言語が持っている基本的なデータ型で、数学者George Booleの名前にちなんで「BOOL型」とか「BOOLEAN型」とも呼ばれています。コンピュータを利用していると、BOOLEAN型が持つtrue（真）とfalse（偽）の2つの値と、NOT、AND、ORという3つの演算子だけから成るシンプルな演算（論理演算）を行うことが多くあります。これはプログラミング言語だけでなく、コンピュータの回路の基礎にもなっている演算です。 SQLにおける真理値と論理演算 SQLにも、真理値と論理演算が存在します。存在するどころか、WHERE句やHAVING句は、それ全体が論理演算のカタマリです。だから真理値はSQLの根幹をなす重要な要素と言っていいのですが、それにもかかわらず、SQ
第4回 行か列か、それが問題だ～スカラサブクエリの使い方 （3）存在の階層 | gihyo.jp
しかし、残念！ このクエリは、My SQL以外ではエラーになります（My SQLの場合も、結果は図10のようにはなりません）。エラーになる理由は、item_nameが集約キー（GROUP BY句に指定される列）ではないからです。 本誌Vol.44の「SQLアタマ養成講座」でも述べましたが、GROUP BY句を使用した場合、SELECT句に書ける要素は次の3つに制限されます。 定数 集約キー 集約関数 リスト5のクエリにおけるitem_nameは、このどれにも当てはまらないため、エラーになるわけです。では、なぜそもそもこの3種類以外の要素をSELECT句に記述することが許されないのでしょうか。 一言で言うと、これは存在の階層の差に起因するものです。GROUP BY句を使うということは、テーブルを小分けにして、文字通りいくつかのグループ（集合）を作るということです。そして、SQLにおいては
第4回 行か列か、それが問題だ～スカラサブクエリの使い方 （2）逆から考える | gihyo.jp
ここで、各プロジェクトにリーダーは必ず1人だけ存在する必要があります。一方、サブリーダーとメンバーは、0人のこともあれば、複数人存在することもあります。 さて、このテーブルからプロジェクトごとの集計を行うことは、とても簡単です。たとえば、プロジェクト単位でそれぞれの職位の参加人数を抽出したいならば、リスト1のようなクエリで実現できます。結果は図3のとおり。 リスト1 プロジェクトごとの参加人数を抽出するクエリ SELECT project_id, COUNT(*) AS all_cnt, SUM(CASE WHEN leader_flg = '2' THEN 1 ELSE 0 END) AS sub_leader_cnt, SUM(CASE WHEN leader_flg = '0' THEN 1 ELSE 0 END) AS member_cnt FROM ProjectEmps GRO
第4回 行か列か、それが問題だ～スカラサブクエリの使い方 （1）スカラ値とは何か | gihyo.jp
はじめに SQLでテーブルのデータを操作するとき、データが行として表現されているか、列として表現されているかによって、同じ結果を得るにも、SQLの効率が大きく変わってきます。そのことは、前回の「列持ちテーブル」でも説明したとおりです。 このとき重要になるのが、行と列を変換する技術です。SQLはこのための便利な機能をいくつか用意しています。本稿では、その中でもCASE式と並んで重要なスカラサブクエリという機能を中心に解説します。 私たちがSQLにおいてサブクエリを使うとき、通常、構造的にはテーブルと同じであると考えています。だから、FROM句でテーブルの代わりにサブクエリを使うこともできます。違うのは、ただデータの保存形式だけであり、それはSQLの構文には影響を与えません。 しかし、実はサブクエリがテーブル、すなわち「関係」としての構造をとらない例外的なケースがひとつだけあります。それが今回
第3回 テーブル設計のグレーゾーン～毒と薬は紙一重 （4）サロゲートキーVSナチュラルキー | gihyo.jp
サロゲートキーVSナチュラルキー DBエンジニアの方なら、サロゲートキー（代理キー）という言葉をご存じでしょう。これは、テーブルへの入力データにある列を主キーとせずに、システム側で独自に割り当てるキーのことです（一般的には連番が使われます）。これに対して、入力データ自体の列を主キーにする場合はナチュラルキー（自然キー）と呼びます。 サロゲートキーは、基本的には不要なものです。入力データに一意なキーが存在していればそれを主キーとして使うことで、普通は問題ありませんし、オートナンバリングの機能も長らく標準SQLには存在していなかったからです（そのため、今でも実装ごとにやり方はバラバラです）。しかし、以下のような業務要件の場合には、サロゲートキーを使うことを考えます。 ① そもそも入力データに主キーにできる項目がなく、データが重複している場合 ② 主キーの値が使いまわされる場合 ③ 主キ
第3回 テーブル設計のグレーゾーン～毒と薬は紙一重 （3）集計用のキー列をテーブルに持つべきか | gihyo.jp
このテーブルをもとにして、各商品を「A：台所用品」「B：食品」「C：オフィス用品」の3グループに分類して、グループごとの平均価格を示すレポートを算出するには、どうすればよいでしょう。分類は具体的に次のようにします。 Aグループ：洗剤、しゃもじ、コップ、箸 Bグループ：パン、クッキー、ビール Cグループ：ボールペン、はさみ すると求めたい結果は次のようになります。 結果 テーブルに存在する列では集計のキーになりませんから、キーを新たに作る必要があることは明らかです。 そこで、一番単純に考えるなら図9のように集計キーを列として追加する方法が考えられます。 図9 集計用のキーを追加したテーブル 列を追加したら、この新しい列に値を入れるには、リスト2のようなUPDATE文ひとつでできます[4]。 リスト2 集計キーの更新を行うクエリ UPDATE Items SET item_grp = C
第3回 テーブル設計のグレーゾーン～毒と薬は紙一重 （2）列持ちテーブル | gihyo.jp
第2回を読まれた方は、この両者を変換するSQLを紹介したことを覚えているでしょう。そのSQLを利用すれば、「列持ち」⇔「行持ち」の変換を行うことが可能なので、最悪、設計時点でどちらかのモデルを選択したあとに、「やはりうまくいかなかった」ということでもう一方のモデルへチェンジすることも、できないわけではありません（アプリケーション側の修正など、相応の工数は覚悟せねばなりませんが）。しかし、最初は可能な限り「行持ち」を選択するべきです。 その理由は、列持ちモデルの拡張性と保守性の低さです。実際、今は2008年なので年度ごとに作られた列も2008年まで用意していればよいとして、来年になったらどうすればよいのでしょう？ 列をもう1つ追加するほかありません。すると毎年テーブルの構造を変えなければなりませんし、このテーブルへアクセスするSELECT文から結果を受け取るホスト言語まで、ほとんどシ
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く
