SQLの未解決問題(坂井調べ)。解けたらIQ180! さぁキミはできるかな!?
・・・・とくだらないサイトの煽り文句のような出だしですが、「SQLで書けそうなのにうまく書けない」テーマがあります。ウデに自信がある人も自信がない人も、頭の体操として取り組んでいただき、教えて戴けると、わたしがとっても助かります(笑)。
書けそうなのになぁ・・・なにか私がちょっとしたところで勘違いしちゃっているだけのような気がするんだけどなぁ・・・・
追記:このエントリでは、「面を構成する辺」に注目して書いていますが、実際には(今回の目的である)MOJデータでは辺IDは共有されていないことがわかりました。そのため一番下のサンプルデータでは「面を構成する点」に話を置き換えていますが、本質的には違いはありません。
データ構造に関する説明
基本的な要素
「辺」に囲まれた「面」をあらわすデータです。面にはIDがあり、また、各辺にもそれぞれIDが付けられています。
以下の図は、面1 が、 辺101,102,103,104,105に囲まれて成り立っているものです。
データにするとこんな感じ。
面ID 辺ID 1 101 1 102 1 103 1 104 1 105
面はたくさんある
面は、辺を共有する形で複数(たくさん)存在しています。
先ほどの面1のデータに加えて、こんな感じ。
面ID 辺ID 2 104 2 106 2 107 2 108 4 105 4 110 4 111 4 112 4 109 :
(面3は図の中に辺IDをひとつ書き忘れてしまったので気にしないでください。。面3と面6で共有している線ね)
「島」の存在
さて、上の例(面1~面6までがある状態)のようにひとかたまりになった状態を「島」と呼びます。実はこのデータは、すべてがひとつの「島」になっているわけではなく、多数の「島」に分かれています。以下の図のように。
データは、これまで例示したように淡々と、面IDと辺IDのセットが格納されています。
さて問題です
ここで、出題です!
本質的には同じ課題のような気がしますが、いくつかの設問にしてみました。すべてSQLで回答してください。
【問1】データはいくつの島から成り立っていますか(いくつの島に分かれていますか)
【問2】任意に「島ID」を決めて、面ID+島IDのリストを返してください。つまり一つの島の中にどの面が含まれているのかを知ることができる結果となります。島IDは構成する面のなかからMAXやMINなどで適当に決めたり、適当に連番にしてみたり等、なんでも構いません。
これはなに
お気づきの方もいるかもしれませんが、この設問、MOJ-XMLデータ(登記所備付地図のデータ)で遊ぼうとしたときにぶち当たってしまった課題でした。できると思っていたのになぁ(まだ言ってるw)。
実際のデータは、この「面」が数億件あります。
今回の「パズル」は課題をシンプル化しましたが、実際には各「面」にはその情報が書かれていた「ファイル」があって、そのファイルごとに「島の数」を求めたいのでした。その辺は、このパズルが解決すれば応用できることでしょう。
ということで、面白おかしく書いていますが、今日1時間くらい考えてこれを解決できなかったことが本気で悔しくてしょうがないので、解決できた方はぜひ披露してください!
サンプルデータ
実際のデータがないと萌えないよ、という人がほとんどだと思うので、用意しました(6月末までの期間限定公開)。5459件のデータです。
データに誤りがありました。というか、このエントリで説明してきた「辺IDが共有されている」は正しくなかったです(同じ辺でも別のIDが振られていた)。
実際に試して、ご指摘くださった皆さんありがとうございました。
ということで、改めてテーマを微修正します。面を構成する辺ではなく、面を構成する「点」に注目することにします。点IDは共有されていることを確認済みです。
(今回のデータでは例えば、F000000007と008で共有点が2つ、F000000006と007で共有点が2つ、などちゃんと重なっています!)
CREATE TABLE と INSERT文ですが、さくっと作成しただけで自分では動作確認していないので、誤りがあったらすいません適当に修正して使ってください(データは間違いないです→データは正しかったのだけどそもそも「辺IDが共有される」という発想自体が間違っていた...)。
surface_idが「面」のID、curve_idが「辺」のIDです。numはその面の中でのその線の順番を表すもので、今回は気にしなくて良いです。
面と点の関係にしたデータダウンロード(データセット2 :益城)ただしMAPPLEビューワの情報と変化している可能性あり
https://www.dropbox.com/scl/fi/9bce437lldnhuhm8o4z53/sql_quiz_moj_data_20240430b.txt?rlkey=6hxui0za4l67iv8iitygqyz35&dl=0
面と点の関係にしたデータダウンロード(データセット3 両国):
https://www.dropbox.com/scl/fi/fm5gl36pu1m356vhai2bx/sql_quiz_moj_data_20240430c.txt?rlkey=j0mi9kepm0lpqc7o4p7g96byn&dl=0
このデータが表す地域は、以下のような島を表しています (MAPPLE法務局地図ビューワより*1)。
ただし、MAPPLEさんのビューワは現時点で 202308データのため、今回の 202404データとID等は異なっている可能性があります。
両国はたぶん変わってない。