質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

2回答

1938閲覧

【SQL】総当たり戦でホーム・アウェイを同数にするには?

yuki26s

総合スコア8

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2018/10/13 23:27

編集2022/01/12 10:55

DBの技術本「Effective SQL RDMSのパフォーマンスを最大限引き出す61の手法と思考」の内容から質問です。

この場で質問すべきかどうか迷ったのですが、該当のコードがGitHubのpublicで公開されているため、掲載いたしました。

チームの一覧からCLOSS JOINで総当たり戦を作成し、かつホーム・アウェイを同数にするという課題からの質問です。

コードは以下になります。(PostgreSQL)

【CREATE】

CREATE TABLE Teams ( TeamID int NOT NULL DEFAULT 0 , TeamName varchar (50) NOT NULL , CaptainID int NULL );

【INSERT】
合計10のTeamを作成します。

INSERT INTO Teams (TeamID, TeamName, CaptainID) VALUES (1, 'Marlins', 2); INSERT INTO Teams (TeamID, TeamName, CaptainID) VALUES (2, 'Sharks', 5);

【SELECT】

WITH TeamPairs AS (SELECT ROW_NUMBER() OVER (ORDER BY Teams1.TeamID, Teams2.TeamID) AS GameSeq, Teams1.TeamID AS Team1ID, Teams1.TeamName AS Team1Name, Teams2.TeamID AS Team2ID, Teams2.TeamName AS Team2Name FROM Teams AS Teams1 CROSS JOIN Teams AS Teams2 WHERE Teams2.TeamID > Teams1.TeamID) SELECT TeamPairs.GameSeq, CASE MOD(ROW_NUMBER() OVER (PARTITION BY TeamPairs.Team1ID ORDER BY GameSeq),2 ) WHEN 0 THEN CASE MOD(RANK() OVER (ORDER BY TeamPairs.Team1ID), 3) WHEN 0 THEN 'Home' ELSE 'Away' END ELSE CASE MOD(RANK() OVER (ORDER BY TeamPairs.Team1ID), 3) WHEN 0 THEN 'Away' ELSE 'Home' END END AS Team1PlayingAt, TeamPairs.Team1ID, TeamPairs.Team1Name, TeamPairs.Team2ID, TeamPairs.Team2Name FROM TeamPairs ORDER BY TeamPairs.GameSeq;

わからないのはこの部分です。

CASE MOD(ROW_NUMBER() OVER (PARTITION BY TeamPairs.Team1ID ORDER BY GameSeq),2 ) WHEN 0 THEN CASE MOD(RANK() OVER (ORDER BY TeamPairs.Team1ID), 3) WHEN 0 THEN 'Home' ELSE 'Away' END ELSE CASE MOD(RANK() OVER (ORDER BY TeamPairs.Team1ID), 3) WHEN 0 THEN 'Away' ELSE 'Home' END END AS Team1PlayingAt,

解説はこのようになっていました。

・メインのクエリを1つおきに調べて(MOD 2)1つ目のチームにホーム・アウェイのどちらを割り当てるか決定している。
・各チームの最初のゲームには、ホームが割り当てられる傾向にある。
・そのため3つ目の行を調べて割り当てが逆の順序になるようにしている。(そうしないとホームが25、アウェイが20になってしまう。)

特にわからないのは3つ目の行です。MOD 3の部分だと思いますが、この判定が何を意味しているのかわかりません。

解決のヒントがあれば、ご回答いただければ幸いです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Orlofsky

2018/10/14 00:03

質問にCREATE TABLE文と数件で良いからINSERT文も載せた方が適切なコメントが付き易いです。
yuki26s

2018/10/14 00:22

ご指摘ありがとうございました!先ほどCREATE文、INSERT文を追記しました。説明不備で申し訳ありませんでした。
guest

回答2

0

1~4の4チームの対戦表を考えてみましょう。結局各チームが1試合か2試合をホームでする訳です。
ソート済みの対戦表クエリ(TeamPairs)は、

試合番号、組合せ
(1),12
(2),13
(3),14
(4),23
(5),24
(6),34

この6試合内の偶数番の試合は(2)13,(4)23,(6)34の3試合で、この試合はホームつまり先番の球場で試合です。
奇数番の試合において、試合番号が3で割り切れる試合は、(3)14でアウェイ(後番の4の球場で試合)です。
同じく奇数番の試合のおいて、試合番号が3で割り切れない試合は、(1)12,(5)24でホーム(先番の球場で試合)です。
全部組合せが並んだので数えると、
1球場 (1)12,(2)13
2球場 (5)24
3球場 (6)34
4球場 (3)14
のように各球場で1または2試合が振られます。

帰納的に示したので、5チームの場合、6チームの場合・・・とあてはめてやってみましょう。

投稿2018/10/14 03:27

seastar3

総合スコア2285

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuki26s

2018/10/14 12:24 編集

丁寧なご回答をありがとうございました。頂いた解説で少しずつですがロジックが見えてきました。 何度も申し訳ありませんが、まだ不明瞭な点があります。 割り算の対象値である「ROW_NUMBER() OVER (PARTITION BY TeamPairs.Team1ID ORDER BY GameSeq)」のイメージですが、これで合っているのでしょうか? ROW_NUMBER() / GameSeq / 組合せ (1) (1) 12 (2) (2) 13 (3) (3) 14 (1) (4) 23 (2) (5) 24 (1) (6) 34 同じく「RANK() OVER (ORDER BY TeamPairs.Team1ID)」 RANK() / team1ID /組合せ (1) 1 12 (1) 1 13 (1) 1 14 (4) 2 23 (4) 2 24 (6) 3 34 ご回答いただいた内容は、おそらくGemeSeqを基準にされていると思います。そうすると、ROW_NUMBERとRANKがどこで使われているのか、疑問が残っています。 申し訳ないのですが、お教えいただけると幸いです。
guest

0

ケース分けを読み解くと以下のような意味だと思われます。

10チームの9試合の割り当てを決めるのですから、第1試合でホームで試合をしたチームは、あとの割り振りでホームの回数が少なくなるように仕向けます。チーム番号順に試合番号が付くので、早い番号のチームはホームの試合を先取りすることになります。
10を3で割れば整数の商は3で、偶数チームはこれにあたる3試合分をホームでやれば、4回がホームの試合です。
奇数チームは、10のうちの3で割り切れない(1,2,4,5,7,8,10)で自分の番以外との試合が5回ですから、これをホームの試合にすれば、半分に近似します。

ということで、偶数チームは、ホーム4試合アウェイ5試合、奇数チームはホーム5試合アウェイ4試合の配分ができます。

投稿2018/10/14 01:57

編集2018/10/14 03:36
seastar3

総合スコア2285

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuki26s

2018/10/14 02:55 編集

ご回答ありがとうございます! おかげで処理の流れは見えてきました。しかし、もしかしたら自分の関数の読み方が間違っていたのかもしれません。 CASE文で評価される値については、以下の解釈でよろしいのでしょうか? MOD(ROW_NUMBER() OVER (PARTITION BY TeamPairs.Team1ID ORDER BY GameSeq),2 ) ・Team1IDごとにグループを作成し、チーム組合わせの識別となるGameSeq順に並べる。 ・上記にROW_NUMBER()で連番を振り、それが2の倍数かどうか確認する(グループごとに1~の連番が振られているイメージ?) MOD(RANK() OVER (ORDER BY TeamPairs.Team1ID), 3) ・TeamID順に並べる(GameSeqによる並べ替えは必要ないのか?) ・RANK関数で番号をつける。(同じTeam1IDが何度も出てくるため、番号の重複や飛びが発生するのでは?) ・この番号が3の倍数かどうか確認する お答えいただければ幸いです。
seastar3

2018/10/14 03:32

最初のMod()は試合番号で2分するのですね。 済みません、よく読み取ってなかったです。 回答を手直しさせていただきます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問