データをグループ分けするSQLを組みたいのですが

質問

メンバーを、各人の点数からグループ分けしたいと
考えています。
グループ分けの基準は、たとえば５０点という尺度があり、
その尺度以上離れていたら別のグループとするというものです。
また、各メンバーの点数は最大と最小が登録されています。

現在は、VBでプログラムを作って対応しましたが、
最近、LAG/LEAD分析関数の存在を知ったので、それをうまく使えば、
SQLだけで対応できるのではないかと思えます。
SQLだけで対応できると、そのVIEWを作ってしまえば、ユーザーに
簡単に引き渡せるので管理が楽になります。

うまいSQLがあれば教えてください。

具体的な処理内容は以下の通りです。

例えば、TABLE1のようなデータで、グループ分けをすると
ID=1と３の差が50点以内、３と２の差も50点以内なので、
その３名で１グループできます。
ID=４と５は、他と５０点以上離れているので、それぞれ一人で
１グループになります。
ID= 6,7 は、差が50点以内なので、2名で１グループとなります。
結果として、TABLE2のような結果になります。

TABLE 1

ID MIN_SCORE MAX_SCORE
1 100 110
2 190 200
3 120 150
4 300 330
5 400 420
6 670 700
7 600 630

TABLE 2

CLASS MIN_SCORE MAX_SCORE N
1 100 200 3
2 300 330 1
3 400 420 1
4 600 700 2

そこに、TABLE1'のようなデータが加わると
ID=4と8は差が50点以内で、７と８も差が50点以内なので、
そこらが全て同じグループになり
TABLE 2' の結果となります。

TABLE 1'
8 380 550

TABLE 2'
CLASS MIN_SCORE MAX_SCORE N
1 100 200 3
2 300 700 5

以上、よろしくお願いします

回答No.3

jamshid6 · ベストアンサー

#1,#2です。

>MIN_SCOREが同じものが複数ある場合
なるほど、そういうケースの値も入りうるわけですか。
それは想定していませんでしたが、以下のように条件を追加します。

SELECT
ROW_NUMBER() OVER (ORDER BY MIN(MIN_SCORE)) CLASS,
MIN(MIN_SCORE) MIN_SCORE,
MAX(MAX_SCORE) MAX_SCORE,
COUNT(*) N
FROM
(SELECT DISTINCT CONNECT_BY_ROOT ID AS ROOTID,ID,MIN_SCORE,MAX_SCORE
FROM TABLE1
CONNECT BY (
(PRIOR MIN_SCORE<MIN_SCORE AND PRIOR MAX_SCORE+50>=MIN_SCORE)
OR (PRIOR MIN_SCORE=MIN_SCORE AND PRIOR ID<ID))
)
WHERE ROOTID IN
(SELECT MIN(ID) FROM TABLE1 t
WHERE NOT EXISTS
(SELECT ID FROM TABLE1
WHERE MIN_SCORE<t.MIN_SCORE AND MAX_SCORE>=t.MIN_SCORE-50)
GROUP BY MIN_SCORE)
GROUP BY ROOTID

自分のMIN_SCORE～MIN_SCORE-50点のレンジにレコードが存在しないものを起点として、
自分のMIN_SCORE～MAX_SCORE+50点以上の間にあるレコードを辿っていくわけなので、
MIN_SCOREが同じならばIDが最小の１つをとり、他のものはCONNECT先に含めるようにします。

ところで、本件を考えているうちに、CONNECT BYを使わなくても実現できることも気づきました。
構造だけなら、こちらの方がわかりやすいかもしれませんので、ご参考までに。

SELECT
ROW_NUMBER() OVER (ORDER BY x1.MIN_SCORE) SEQ,
x1.MIN_SCORE,
x2.MAX_SCORE,
(SELECT COUNT(*) FROM TABLE1
WHERE MIN_SCORE BETWEEN x1.MIN_SCORE AND x2.MAX_SCORE) N
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY MIN_SCORE) SEQ,
MIN_SCORE
FROM TABLE1 t1
WHERE NOT EXISTS
(SELECT * FROM TABLE1
WHERE MIN_SCORE<t1.MIN_SCORE AND (t1.MIN_SCORE-50)<=MAX_SCORE)
GROUP BY MIN_SCORE) x1
INNER JOIN
(SELECT
ROW_NUMBER() OVER (ORDER BY MAX_SCORE) SEQ,
MAX_SCORE
FROM TABLE1 t2
WHERE NOT EXISTS
(SELECT * FROM TABLE1
WHERE MIN_SCORE<=(t2.MAX_SCORE+50) AND t2.MAX_SCORE<MAX_SCORE)
GROUP BY MAX_SCORE) x2 ON x2.SEQ=x1.SEQ

お礼

いろいろ教えていただきありがとうございます。
CONNECT BY は初めて見たのですが、他を調べても等号条件の例しか見つからず、不等号条件は実は非常に高度な技なんですね。まだ、CONNECT　自体の機能については完全には理解しきれていませんが、非常に参考になりました。

あとで上げられたCONNECT　BY　を使わない方法は、アルゴリズムは先のと同じかと思ってしまったのでSQLを読み解くのに時間がかかってしまいました。実際は、各グループの親を求めるのではなく、各グループの最小値と最大値を別々に求めるように、アルゴリズム自体も変更されているのですね。感服しました。
最初は、なぜ
ROW_NUMBER() OVER (ORDER BY MIN_SCORE) SEQ
と
ROW_NUMBER() OVER (ORDER BY MAX_SCORE) SEQ
とでJOINできるのかわからなかくて、きっとここらがCONNECT BY の代わりになるコツなんだろうと曲解してえらく悩んでしまいました。

今回の回答のおかげで、またひとつSQLの守備範囲が広がりました。本当にどうもありがとうございます。

回答No.2

jamshid6

#1です。

CONNECT BYが必要と思った理由は複数のレコード間で50点以内の条件を満たすものを辿っていく必要があったからです。
（LAGやLEADでそういう使い方ができるのであれば、私自身の勉強不足ということになるかもしれませんが）
B_CLASSのクラス分けは単純にint((B_SCORE-4)/20で行えばよく、サブクエリのを１つ深くするだけの話だと思います。

>実際はもっともっともっと複雑な問題なのですが
ということなので、B_CLASSのところだけクリアするクエリを示してもあまり意味はなさそうです。
結局は質問者さんに応用力を発揮していただかないといけないと思います。
（それでも、CONNECT BYでこなせないとは思いませんが）

補足

言われてみるとLAG/LEAD でグループ分けするのは難しい気がしてきました。

jamshid6さんの案は、CONNECT BY の　CONNECT_BY_ROOT関数？ を使い、各グループ毎の大元の親レコードを求めるPRIOR条件式を作りだすのがコツというわけですね。その方針なら、int((B_SCORE-4)/20 もPRIOR条件式に組み込んでグループ分けできるようにすれば、私が当初固執していたPARTITIONを使わなくても一気にグループ分けができる気がしてきました。

ところで、まだ、完全にCONNECT BYの使い方が理解できていませんので
大元の親を決める条件式で教えてください。
MIN_SCOREが同じものが複数ある場合、jamshid6 さんの書かれた
CONNECT BY PRIOR MIN_SCORE<MIN_SCORE
では、別のグループになってしまう気がします。
といって、
CONNECT BY PRIOR MIN_SCORE<=MIN_SCORE
では、大元の親が複数できてしまい、これもグループ分けが失敗する気がします。
ここらは、CONNECT BY がどういう処理をしていて、この課題にはどう考えるべきなのでしょうか？

回答No.1

jamshid6

これって、LAGやLEADを使っても別に楽にはならないような気がするのですが。
むしろCONNECT BYを使った方がいいのでは？（バージョン書いてませんが10g以上なら）

SELECT
ROW_NUMBER() OVER (ORDER BY MIN(MIN_SCORE)) CLASS,
MIN(MIN_SCORE) MIN_SCORE,
MAX(MAX_SCORE) MAX_SCORE,
COUNT(*) N
FROM
(SELECT CONNECT_BY_ROOT ID AS ROOTID,MIN_SCORE,MAX_SCORE
FROM TABLE1
CONNECT BY PRIOR MIN_SCORE<MIN_SCORE AND PRIOR MAX_SCORE+50>=MIN_SCORE)
WHERE ROOTID IN
(SELECT ID FROM TABLE1 t
WHERE NOT EXISTS
(SELECT ID FROM TABLE1
WHERE MIN_SCORE<t.MIN_SCORE AND MAX_SCORE>=t.MIN_SCORE-50))
GROUP BY ROOTID

補足

CONNECT BY は初めて知りました。
貴重な指摘ありがとうございます。
ただ、実際のデータ構造は先に出したテーブル構造より複雑で、もうひとつ別の分類尺度をもっており、例えば　TABLE 1B のB_SOCREの値に範囲によりグループ分けしてから先の題意のグループ分けする必要があるのです。
今回の例の場合は、4 <= B_SOCRE <24 と　24<=B_SCORE<44 , 44<=B_SCORE <64・・毎にグループ分けするので、単純にCONNECT BY で分類するだけでは、うまくいきません。(今回は答えが同じようになるようにわざと4<= <24　にしましたが、実際は0から450ぐらいの範囲でばらばらです。)
私のイメージとして、・・・（）OVER (PARTITION BY int((B_SCORE-4)/20 ORDER BY MIN_SCORE)
といった形になるのかと思っており、LAG/LEADを使うとよいかと思っていました。

実は、実際はもっともっともっと複雑な問題なのですが、それだと説明が難しくなるため、質問時に課題を省略して説明しており、jamshid6さんにはせっかく回答していただきましたが、申し訳ありませんでした。
さらなるお知恵を拝借お願いします。

TABLE 1B

ID MIN_SCORE MAX_SCORE B_SCORE
1 100 110 10
2 190 200 4
3 120 150 23
4 300 330 16
5 400 420 10
6 670 700 20
7 600 630 19