第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う （5）断絶区間を求める | gihyo.jp
図7 欠番のカタマリ gap_start ～ gap_end --------- ---- ------- 2 ～ 2 5 ～ 6 10 ～ 11 集合指向的な解法 方法はいくつかありますが、ここでは『SQLパズル第2版』の「パズル9 席空いてますか？」で紹介されているR・レムレーのアイデアを詳しく解説しましょう。 手続き型言語であれば、1行ずつテーブルから読み出し、当該の行と次の行の数値の差分が1でなければ、その間には欠番がある、という行レベルの判定処理を記述します。 一方、伝統的なSQLでは当該の行を起点に考えるのは同じですが、処理の単位が「集合」になります。リスト14でN2.numは「ある行のN1.numの数値より大きな数値の集合（上界集合）」（注5）として条件づけされています（ON N2.num > N1.num）。この集合群を一覧表示すると、表11のようになります（リ
第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う （4）連番の生成 | gihyo.jp
連番の生成～どうしてもループが嫌と言うならしかたない～ これまで、あらかじめ複数行を保持するテーブルを対象に連番を割り当てていました。今度は動的に連番を生成する方法を考えます。 ループを使わずに1行のデータをn行に増やす 表7のような1行だけデータを持つテーブルがあります。データの中身は重要ではないので気にしないでください。 表7 OneRow みなさんに考えてほしいのは、この1行のデータを3行に増やしてもらうことです。しかもその際、図6のように連番を付与します。 図6 連番列「seq」が追加されている seq col1 col2 col3 --- ---- ---- ---- 1 test data col 2 test data col 3 test data col ここで条件が1つあります。それは、3行に限らずn行に簡単に一般化できる方法であることです。もしこの問題を手続き型言語で
第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う （3）メジアンを求める | gihyo.jp
集合指向的な解法 昔から知られている集合指向的な解法では、テーブルを上位集合と下位集合に分割してその共通部を取ります（リスト9）。この解法のポイントは、HAVING句にあります。CASE式で表現された2つの特性関数によって、母集合Weightsを上位集合と下位集合に分割しているのです（図4）。これはこれで「SQLらしい」解答ではあります。 リスト9 メジアンを求める（集合指向型）：母集合を上位と下位に分割する SELECT AVG(weight) FROM (SELECT W1.weight FROM Weights W1, Weights W2 GROUP BY W1.weight --S1（下位集合）の条件 HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END) >= COUNT(*) / 2 --S2（
第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う （2）行集合に連番を割り当てる | gihyo.jp
まず、ROW_NUMBERを使う場合には、特に悩む必要はありません。ORDER BYのキーに追加しましょう（リスト3）。実行結果は図2です。 リスト3 主キーが複数列の場合(ROW_NUMBER) SELECT class, student_id, ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq FROM Weights2; 図2 リスト3、4の実行結果 class student_id seq ----- ------------ --- 1 100 1 1 101 2 1 102 3 2 100 4 2 101 5 2 102 6 2 103 7 一方、相関サブクエリの場合はどうでしょう。いくつか方法があるのですが、私が最もシンプルと思うのは、複合キーを文字列とみなして連結し、強引に1列のキーに作り変えてしまうことです。そ
第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う （1）SQLと連番 | gihyo.jp
はじめに この連載では、これまでC言語やPerl、Javaなどの手続き型言語（またそれに基礎を持つ言語）を主に使用してきたプログラマやエンジニア（要するに、私たちのほとんど全員、ということになるでしょうが）を対象に、データベースという特有の世界―集合論と述語論理のロジックが支配する世界―について、ケーススタディを交えながらわかりやすく解説していくことを目的としています。 「手続き型プログラマの視点から初めて集合指向（setoriented）の世界を見た場合」を想定して、多くの方にとっては馴染みの薄い集合や述語といった概念を、なるべく慣れ親しんだ手続き型の考え方にひきつけて解き明かしてみせよう、というわけです。もちろん、SQL初心者だけではなく、プロフェッショナルなDBエンジニアにも楽しんでもらえるトピックを盛り込んでいこうと考えています。どうぞよろしくお付き合いください。 SQLと連番 さ
1
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く
