泥庭

2013年7月4日

最大連勝、連敗を抽出するSQL

Filed under: SQL, 勉強会 — yone64 @ 11:46 PM

平日開催SQLWorld2回目に参加してきました。今回の問題はこちら

で、問題3のお話。勝敗日程表から連勝連敗を取得するSQLです。

image

まずの方針として、連続する0とか1を何かをキーにしてグループ化できれば良さそうな気がします。

じゃあ、何を使うかということで、私が考えたのは直前の勝敗値が異なる日付でした。
#「1」だったら直前の「0」のものの日付、「0」だったら直前の「1」のものの日付。

これは、下記SQLで比較的簡単に取得出来ます。

SELECT 日付,
       勝敗値,
       (SELECT max(日付)
        FROM   試合結果 AS b
        WHERE  a.勝敗値 <> b.勝敗値
               AND b.日付 < a.日付) AS 直前日
FROM   試合結果 AS a;

image

これを、直前日でグループ化かけたのち、Countをとればよさそうです。なので、SQLを次のように修正しました。

SELECT   min(日付) AS 日付,
         max(勝敗値) AS 勝敗値,
         count(*) AS 連続数
FROM     (SELECT 日付,
                 勝敗値,
                 (SELECT max(日付)
                  FROM   試合結果 AS b
                  WHERE  a.勝敗値 <> b.勝敗値
                         AND b.日付 < a.日付) AS 直前日
          FROM   試合結果 AS a) AS tempA
GROUP BY 直前日;

image

ここまで来ればあと一息、勝敗値でグループかけて、連続数の最大を撮ればOKですね。

SELECT   勝敗値,
         max(連続数) AS 連続数
FROM     (SELECT   min(日付) AS 日付,
                   max(勝敗値) AS 勝敗値,
                   count(*) AS 連続数
          FROM     (SELECT 日付,
                           勝敗値,
                           (SELECT max(日付)
                            FROM   試合結果 AS b
                            WHERE  a.勝敗値 <> b.勝敗値
                                   AND b.日付 < a.日付) AS 直前日
                    FROM   試合結果 AS a) AS tempA
          GROUP BY 直前日) AS tempB
GROUP BY 勝敗値;

image

ゴリゴリ感は否めませんが、とりあえず抽出できました。
#当日は連勝/連敗の開始日も出したかったので、もう一回結合してました(死

で、ここからが、今回の職人仕事の紹介。

最初のグループ化キーを作成するために、私は試合結果テーブルにもう一回問い合わせたわけですが、これをrow_number()関数を使用する事により省略出来てます。
とりあえず、一番ベースのSQLわかりやすいかたちに変形してみました。

SELECT   日付,
         勝敗値,
         row_number() OVER (ORDER BY 日付) AS 日付順,
         row_number() OVER (PARTITION BY 勝敗値 ORDER BY 日付) AS 勝敗別,
         row_number() OVER (ORDER BY 日付) - row_number() OVER (PARTITION BY 勝敗値 ORDER BY 日付) AS 差
FROM     試合結果 AS s
ORDER BY 日付;

image

結果の表の勝敗別カラムを見やすいように、右寄せ・左寄せで分けてみました。

何が重要かというと、日付順に付けた行番号と勝敗値別日付順に付けた行番号の差は、連勝・連敗中は同じ値だし、連続になっていない勝敗同士では同じ値になる事は無いと言うこと。つまり、この表を勝敗値と差でグループ化すれば、勝敗値と連続数の表が取得出来るんですね。

絶対思いつかないよ!!

広告

1件のコメント »

  1. […] 以前の、連勝数を数えるSQLに近い感じですね。 […]

    ピンバック by 条件に合致する連続するデータをグループ化する(LINQ) | 泥庭 — 2014年12月3日 @ 11:18 PM


RSS feed for comments on this post. TrackBack URI

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

WordPress.com で無料サイトやブログを作成.

%d人のブロガーが「いいね」をつけました。