努力したWiki

推敲の足りないメモ書き多数

ユーザ用ツール

サイト用ツール

トレース:
documents:other:memo02:other-040

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
documents:other:memo02:other-040 [2025/11/20 09:31] – ↷ documents:other:other-040 から documents:other:memo02:other-040 へページを移動しました。 k896951documents:other:memo02:other-040 [2026/05/08 12:19] (現在) – ↷ 移動操作に合わせてリンクを書き換えました。 k896951
行 1: 行 1:
 +====== ライブツアーのセットリストを公演毎に横に並べる ======
  
 +
 +2015/12/07\\
 +　とりあえず公開
 +
 +2015/12/08\\
 +　某『手を抜いたでしょ？』\\
 +　私『…はい』\\
 +　という事でテーブル構成等々を手直し。
 +
 +
 +こちらのサイト http://www.seven-ripe.sakura.ne.jp/nml/ の作成者と思われる方のツイッターアカウントをたまたま見つけつぶやきを見てしまいました。\\
 +うちのドキュメント　[[documents:proglang:sql:sql-0001|ＳＱＬでデータベーステーブルの縦･横変換]]　を見て、おそらく各ツアーの公演毎のセットリストを横に並べて表示させたかったんじゃないかな、と想像しました。
 +
 +<WRAP 70%>
 +{{:documents:other:other-040-002.png|}}
 +</WRAP>
 +
 +せっかく見てもらったのに解決しなかったのは残念だな、と思いましたので、私なりの実装方法を書いていってみます。
 +なお、私はPHP使いではないのでPerlのCGIで実装します。
 +
 +===== データの取得 =====
 +
 +
 +実装するにしてもデータがないとどうにもなりません。先のサイトを少しの間眺めて、以下のPerlコードを書きました。
 +サイトの左側フレームにはツアータイトルと公演回のリンクがリストになっていて、クリックすると右側フレームにその公演の情報が表示されます。リンクを見た様子では、URLのパラメタcategoryが1～148のコンテンツを表示できるようです。
 +
 +<code perl getnanadb.pl>
 +use Encode;
 +use WWW::Mechanize;
 +use Web::Scraper;
 +use JSON;
 +
 +my $baseUrl  = 'http://www.seven-ripe.sakura.ne.jp/nml/index.php?category=%s';
 +my $mech     = WWW::Mechanize->new(autocheck=>1);
 +my $json     = JSON->new();
 +my $pageResult;
 +my @tours    = ();
 +
 +my $contentParse = scraper {
 +      process '//div[@id="contents"]/div[@id="main"]/h2[1]',                              'tour_name'=>"TEXT";
 +      process '//div[@id="contents"]/div[@id="main"]/dl[@class="information"]/dd[1]/div', 'date'     =>"TEXT";
 +      process '//div[@id="contents"]/div[@id="main"]/dl[@class="information"]/dd[2]/div', 'place'    =>"TEXT";
 +      process '//div[@id="contents"]/div[@id="main"]/dl[@class="information"]/dd[3]/div', 'venue'    =>"TEXT";
 +      process '//div[@id="contents"]/div[@id="main"]/ol[1]//li', 'setlist[]'     => "TEXT";
 +      process '//div[@id="contents"]/div[@id="main"]/ol[2]//li', 'encorelist[]'  => "TEXT";
 +      process '//div[@id="contents"]/div[@id="main"]/ol[3]//li', 'wencorelist[]' => "TEXT";
 +};
 +
 +  $mech->agent_alias("Windows Mozilla");
 +  foreach my $category ( 1 .. 148 )
 +  {
 +    $mech->get( sprintf($baseUrl,$category) );
 +    $pageResult = $contentParse->scrape( $mech->content );
 +    push(@tours, $pageResult);
 +  }
 +
 +  $json->pretty();
 +  print encode('utf-8',$json->encode(\@tours));
 +</code>
 +
 +このコードを
 +<code tt>
 +$ perl getnanadb.pl > nana.json
 +</code>
 +
 +な感じで実行すると、JSONドキュメント nana.json が取得されます。以下はgetnanadb.plの
 +<code perl>foreach my $category ( 1 .. 148 )</code>
 +を
 +<code perl>foreach my $category ( 1 .. 3 )</code>
 +にして最初の３つの公演情報を取り出したものです。
 +
 +<code json nana.json>
 +[
 +   {
 +      "setlist" : [
 +         "Dear. Friend",
 +         "Send You My Love",
 +         "小さな秋の恋～コスモス～",
 +         "Yo･A･Ke",
 +         "夕立",
 +         "空と心と…",
 +         "冬の淡雪(CMソング)",
 +         "Bird's Eye View",
 +         "Our Song",
 +         "かもめ哀歌",
 +         "Autunm Moon",
 +         "Twiligt Highway Star",
 +         "natural",
 +         "テルミドール"
 +      ],
 +      "date" : " 2000-01-23",
 +      "encorelist" : [
 +         "つばさを広げて",
 +         "NANA色のように"
 +      ],
 +      "place" : " 東京",
 +      "venue" : " 銀座ヤマハホール",
 +      "tour_name" : "20th Birthday Anniversary Live"
 +   },
 +   {
 +      "venue" : " ヤクルトホール",
 +      "tour_name" : "水樹奈々 21ANNIVERSARY CONCERT \"HAPPY\"",
 +      "wencorelist" : [
 +         "WINDOW OF HEART"
 +      ],
 +      "place" : " 東京",
 +      "date" : " 2001-01-21",
 +      "setlist" : [
 +         "Love's Wonderland",
 +         "Heaven Knows",
 +         "想い",
 +         "Summer Sweet",
 +         "ピアス",
 +         "水中の青空",
 +         "夕やけ雲のむこうに",
 +         "Shining★star",
 +         "アノネ～まみむめ☆もがちょ～",
 +         "Look Away(Acoustic)",
 +         "七夕の夜(Acoustic)",
 +         "NANA色のように",
 +         "空と心と…",
 +         "VOICE"
 +      ],
 +      "encorelist" : [
 +         "想い",
 +         "アノネ～まみむめ☆もがちょ～"
 +      ]
 +   },
 +   {
 +      "encorelist" : [
 +         "TRANSMIGRATION",
 +         "アノネ～まみむめ☆もがちょ～"
 +      ],
 +      "date" : " 2001-12-23",
 +      "setlist" : [
 +         "Love's Wonderland",
 +         "Heaven Knows",
 +         "LOOKING ON THE MOON",
 +         "オルゴールとピアノと",
 +         "想い",
 +         "水中の青空",
 +         "Lost in Your Eyes",
 +         "Look Away",
 +         "WINDOW OF HEART",
 +         "NANA色のように",
 +         "シアワセ大将",
 +         "supersonic girl",
 +         "The place of happiness"
 +      ],
 +      "place" : " 東京",
 +      "wencorelist" : [
 +         "真冬の観覧車"
 +      ],
 +      "venue" : " 原宿アストロホール",
 +      "tour_name" : "水樹奈々 X’mas LIVE \"supersonic girl\""
 +   }
 +]
 +</code>
 +
 +148の公演情報を取り出しできたら、このnana.jsonの内容をデータベースへ格納していきます。
 +
 +
 +===== テーブル定義 =====
 +
 +データベースは PostgreSQL を使っています。
 +PostgreSQL上にデータベース NANAMIZUKI を作成し7つのテーブルを定義しました。
 +
 +{{:documents:other:other-040-003.png?nolink|}}
 +
 +各テーブル定義では項目idに Serial を使っているので、シーケンス(SEQUENCE)オブジェクトも同時に定義されます。\\
 +私が使っているpgAdminⅡのGUIグリッドでテーブルの編集を行いたいので項目idは主キーに使っています。
 +
 +
 +==== ツアーテーブル ====
 +
 +<code sql tours_ddl.sql>
 +create table tours(
 +  id          serial
 + ,tour_name   varchar(200)
 + ,tour_start  date
 + ,tour_end    date
 + ,comments    text
 + ,primary key (id)
 +);
 +</code>
 +
 +==== 公演テーブル ====
 +
 +<code sql events_ddl.sql>
 +create table events(
 +  id          serial
 + ,tour_id     integer
 + ,event_date  date
 + ,place       varchar(100)
 + ,venue       varchar(100)
 + ,comments    text
 + ,primary key(id)
 +);
 +</code>
 +
 +==== 演目テーブル ====
 +
 +<code sql setlists_ddl.sql>
 +create table setlists(
 +  id          serial
 + ,event_id    integer
 + ,list_type   integer
 + ,order_index integer
 + ,song_id     integer
 + ,comments    text
 + ,primary key(id)
 +);
 +</code>
 +
 +==== 演目ラベルテーブル ====
 +
 +<code sql setlisttypes_ddl.sql>
 +create table setlist_types(
 +  id          integer not null
 + ,list_name   varchar(100)
 + ,primary key(id)
 +);
 +</code>
 +
 +==== 曲テーブル ====
 +
 +<code sql songs_ddl.sql>
 +create table songs(
 +  id          serial
 + ,song_title  varchar(500)
 + ,comments    text
 + ,primary key(id)
 + ,unique (song_title)
 +);
 +</code>
 +
 +==== 場所テーブル ====
 +
 +<code sql places_ddl.sql>
 +create table places(
 +  id       serial
 + ,place    varchar(100)
 + ,primary  key(id)
 +);
 +</code>
 +
 +==== 会場テーブル ====
 +
 +<code sql venues_ddl.sql>
 +create table venues(
 +  id      serial
 + ,venue   varchar(200)
 + ,primary key(id)
 +);
 +</code>
 +
 +
 +===== データ取り込み =====
 +
 +nana.jsonはJSON形式データとなっているので、PHPやPerlはじめいろいろな言語やツールで参照できるデータ形式になります。\\
 +
 +
 +==== JSONデータからSQL生成 ====
 +
 +perlでnana.jsonを読みSQLを生成させました。使ったPerlのコードは泥臭くこんな感じ。
 +
 +<code perl gen.pl>
 +use Encode;
 +use JSON;
 +
 +my $d;
 +
 +  while(<STDIN>)
 +  {
 +    $d .= decode('utf-8',$_);
 +  }
 +
 +my $json = JSON->new()->decode($d);
 +
 +  foreach my $event ( @{$json} )
 +  {
 +    my $date  = $event->{date};
 +    my $place = $event->{place};
 +    my $venue = $event->{venue};
 +    my $tour_name = $event->{tour_name};
 +
 +    $tour_name =~ s/'/''/g;
 +
 +    my $orderindex = 1;
 +    foreach my $song_title ( @{ $event->{setlist} } )
 +    {
 +      $song_title =~ s/'/''/g;
 +      print encode('utf-8',sprintf("insert into setlists(list_type, order_index, comments) values(1,%d,'1:[%s]2:[%s]3:[%s]4:[%s]5:[%s]');\n", $orderindex, $song_title, $date, $place, $venue, $tour_name));
 +      $orderindex++;
 +    }
 +    $orderindex = 1;
 +    foreach my $song_title ( @{ $event->{encorelist} } )
 +    {
 +      $song_title =~ s/'/''/g;
 +      print encode('utf-8',sprintf("insert into setlists(list_type, order_index, comments) values(2,%d,'1:[%s]2:[%s]3:[%s]4:[%s]5:[%s]');\n", $orderindex, $song_title, $date, $place, $venue, $tour_name));
 +      $orderindex++;
 +    }
 +    $orderindex = 1;
 +    foreach my $song_title ( @{ $event->{wencorelist} } )
 +    {
 +      $song_title =~ s/'/''/g;
 +      print encode('utf-8',sprintf("insert into setlists(list_type, order_index, comments) values(3,%d,'1:[%s]2:[%s]3:[%s]4:[%s]5:[%s]');\n", $orderindex, $song_title, $date, $place, $venue, $tour_name));
 +      $orderindex++;
 +    }
 +  }
 +</code>
 +
 +
 +==== 演目テーブルにデータ取り込み ====
 +
 +Perlのコード gen.pl から生成されたSQLはこんな感じ。
 +
 +<code sql>
 +$ cat nana.json| perl gen.pl > setlists.sql
 +$ cat setlists.sql | head -30
 +insert into setlists(list_type, order_index, comments) values(1,1,'1:[Dear. Friend]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,2,'1:[Send You My Love]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,3,'1:[小さな秋の恋～コスモス～]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,4,'1:[Yo･A･Ke]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,5,'1:[夕立]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,6,'1:[空と心と…]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,7,'1:[冬の淡雪(CMソング)]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,8,'1:[Bird''s Eye View]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,9,'1:[Our Song]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,10,'1:[かもめ哀歌]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,11,'1:[Autunm Moon]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,12,'1:[Twiligt Highway Star]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,13,'1:[natural]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,14,'1:[テルミドール]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(2,1,'1:[つばさを広げて]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(2,2,'1:[NANA色のように]2:[ 2000-01-23]3:[ 東京]4:[ 銀座ヤマハホール]5:[20th Birthday Anniversary Live]');
 +insert into setlists(list_type, order_index, comments) values(1,1,'1:[Love''s Wonderland]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,2,'1:[Heaven Knows]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,3,'1:[想い]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,4,'1:[Summer Sweet]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,5,'1:[ピアス]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,6,'1:[水中の青空]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,7,'1:[夕やけ雲のむこうに]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,8,'1:[Shining★star]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,9,'1:[アノネ～まみむめ☆もがちょ～]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,10,'1:[Look Away(Acoustic)]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,11,'1:[七夕の夜(Acoustic)]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,12,'1:[NANA色のように]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,13,'1:[空と心と…]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +insert into setlists(list_type, order_index, comments) values(1,14,'1:[VOICE]2:[ 2001-01-21]3:[ 東京]4:[ ヤクルトホール]5:[水樹奈々 21ANNIVERSARY CONCERT "HAPPY"]');
 +$
 +</code>
 +
 +このSQLを実行してテーブルsetlists に最初のデータを取り込みします。
 +
 +==== 演目テーブルから各テーブルへのデータ格納 ====
 +
 +以下はテーブルsetlistsに格納されたデータを基に残りのテーブルへ格納していくSQLです。
 +
 +<code sql convert.sql>
 +
 +-- 演目テーブルの項目commentsから曲タイトルを抜き出して曲テーブルへ格納します。
 +insert into songs(song_title)
 +  select regexp_replace(comments,'^1:\[(.*?)\].+$','\1') as "song_title"
 +     from setlists
 +     group by "song_title"
 +     order by "song_title"
 +;
 +
 +-- 演目テーブルの項目commentsから場所を抜き出して場所テーブルへ格納します。
 +insert into places(place)
 +  select TRIM(regexp_replace(setlists.comments,'^.+3:\[(.*?)\].+$','\1')) as "place"
 +     from setlists
 +     group by "place"
 +     order by "place"
 +;
 +
 +-- 演目テーブルの項目commentsから会場を抜き出して会場テーブルへ格納します。
 +insert into venues(venue)
 +  select TRIM(regexp_replace(setlists.comments,'^.+4:\[(.*?)\].+$','\1')) as "venue"
 +     from setlists
 +     group by "venue"
 +     order by "venue"
 +;
 +
 +-- 演目テーブルの項目commentsからツアーのタイトルを抜き出してツアーテーブルへ格納します。
 +insert into tours(tour_title)
 +  select TRIM(regexp_replace(setlists.comments,'^.+5:\[(.*?)\]$','\1')) as "tour_title"
 +     from setlists
 +     group by "tour_title"
 +     order by "tour_title"
 +;
 +
 +-- 演目テーブルの項目song_idに曲テーブルの項目idを設定します。
 +update setlists
 +   set song_id = songs.id
 +  from songs
 + where songs.song_title = regexp_replace(setlists.comments,'^1:\[(.*?)\].+$','\1')
 +;
 +
 +-- 演目テーブルから集約処理で邪魔になる情報(曲タイトル)を項目commentsから取り除きます。
 +update setlists
 +   set comments = TRIM(regexp_replace(setlists.comments,'^.+(2:\[.*\])$','\1'))
 +;
 +
 +-- 演目テーブルから公演テーブルへ格納します。
 +-- 演目テーブルの曲情報が無い状態で集約すると、1レコードが1公演の情報、となります。
 +insert into events(comments)
 +  select comments 
 +     from setlists
 +     group by comments 
 +     order by comments 
 +;
 +
 +-- 公演テーブルの項目event_dateに項目commentsの公演日を転記します。
 +update events
 +   set event_date = to_date(regexp_replace(comments,'^2:\[(.*?)\].+$','\1'), 'YYYY-MM-DD')
 +;
 +
 +-- 演目テーブルの項目event_idに公演テーブルの項目idを設定します。
 +update setlists
 +   set event_id = events.id
 +  from events
 + where events.comments = setlists.comments
 +;
 +
 +-- 公演テーブルの項目commentsを使って公演テーブルのtour_idにツアーテーブルの項目idを設定します。
 +update events
 +   set tour_id = tours.id
 +  from tours
 + where tour_title = trim(regexp_replace(events.comments,'^.+?5:\[(.*?)\]$','\1'))
 +;
 +
 +-- 公演テーブルの項目commentsを使って公演テーブルのplace_idに場所テーブルの項目idを設定します。
 +update events
 +   set place_id = places.id
 +  from places
 + where place = trim(regexp_replace(events.comments,'^.+?3:\[(.*?)\].+$','\1'))
 +;
 +
 +-- 公演テーブルの項目commentsを使って公演テーブルのvenue_idに会場テーブルの項目idを設定します。
 +update events
 +   set venue_id = venues.id
 +  from venues
 + where venue = trim(regexp_replace(events.comments,'^.+?4:\[(.*?)\].+$','\1'))
 +;
 +
 +-- このテーブルへは手書きで追加します。このテーブルはなくてもかまいません。
 +insert into setlist_types(id,list_name) values(1,'セットリスト');
 +insert into setlist_types(id,list_name) values(2,'アンコール');
 +insert into setlist_types(id,list_name) values(3,'Wアンコール');
 +
 +-- テーブルtours にはツアー開始、終了の項目があります。
 +-- この項目をテーブルeventsのデータで設定します。
 +update tours
 +   set tour_start = mindate
 +      ,tour_end   = maxdate
 +  from (select tour_id
 +              ,min(event_date) as mindate
 +              ,max(event_date) as maxdate
 +         from events
 +         group by tour_id ) T
 + where tours.id = T.tour_id
 +;
 +
 +-- 各テーブルの項目 comments には取り込みと関連付け作業で使ったデータが残ったままですのでこれを削除します。
 +update tours set comments = null;
 +update songs set comments = null;
 +update events set comments = null;
 +update setlists set comments = null;
 +
 +-- 結果をコミット
 +commit;
 +
 +</code>
 +
 +データ取り込みはこれで終わりです。
 +
 +
 +===== クエリーの作成 =====
 +
 +今回のような、横に並ぶ数が可変となるような(ツアー毎に公演回数が異なる)場合は基本的にクエリを書けないです。\\
 +最近ですとPivotとかcrosstabとかの構文を備えているRDBMSも出てきていますが、そのクエリを発行した側が動的な戻り値に対応できていなくてはなりません。
 +
 +もし、すべてのクエリの結果項目が同じ型に落とし込めるのであれば、UNION演算子で複数クエリを縦に繋ぐ方法も考えられなくはないのですが、面倒ばかりが多くあまりいい結果にならないかもしれないです。
 +
 +ここではクエリで横につなげるのではなく、クエリ結果を横につなげる方法で実装してみます。
 +
 +==== ツアー一覧 ====
 +
 +メニューで使うであろうツアー一覧を作成するクエリ。
 +
 +<code sql>
 +select id
 +      ,tour_title
 +      ,(select count(*) from events where events.tour_id=tours.id) as "stages"
 +  from tours
 +  order by tour_start,id
 +</code>
 +
 +結果はこうなります。
 +<code sql>
 +nanamizuki=> select id
 +nanamizuki->       ,tour_title
 +nanamizuki->       ,(select count(*) from events where events.tour_id=tours.id) as "stages"
 +nanamizuki->   from tours
 +nanamizuki->   order by tour_start,id;
 + id  |                  tour_title                  | stages
 +-----+----------------------------------------------+--------
 + 207 | 20th Birthday Anniversary Live               |      1
 + 234 | 水樹奈々 21ANNIVERSARY CONCERT "HAPPY"       |      1
 + 235 | 水樹奈々 X’mas LIVE "supersonic girl"       |      1
 + 208 | NANA MIZUKI "LIVE ATTRACTION 2002"           |      4
 + 227 | NANA MIZUKI LIVE SENSATION 2003              |     10
 + 228 | NANA MIZUKI LIVE SKIPPER COUNTDOWN 2003-2004 |      1
 + 229 | NANA MIZUKI LIVE SPARK 2004 -summer-         |      7
 + 225 | NANA MIZUKI LIVE RAINBOW 2004-2005           |      3
 + 226 | NANA MIZUKI LIVE ROCKET 2005～summer～       |      6
 + 233 | NANA MIZUKI LIVEDOM 2006-BIRTH-              |      1
 + 232 | NANA MIZUKI LIVE UNIVERSE 2006 ～summer～    |      8
 + 224 | NANA MIZUKI LIVE MUSEUM 2007                 |      1
 + 219 | NANA MIZUKI LIVE FORMULA 2007-2008           |      7
 + 215 | NANA MIZUKI LIVE FIGHTER 2008 ～BLUE SIDE～  |      1
 + 216 | NANA MIZUKI LIVE FIGHTER 2008 ～RED SIDE～   |      1
 + 214 | NANA MIZUKI LIVE FEVER 2009                  |      7
 + 213 | NANA MIZUKI LIVE DIAMOND 2009                |      1
 + 209 | NANA MIZUKI LIVE ACADEMY 2010                |      7
 + 220 | NANA MIZUKI LIVE GAMES 2010                  |      2
 + 221 | NANA MIZUKI LIVE GRACE 2011 -ORCHESTRA-      |      2
 + 223 | NANA MIZUKI LIVE JOURNEY 2011                |     14
 + 211 | NANA MIZUKI LIVE CASTLE 2011                 |      2
 + 231 | NANA MIZUKI LIVE UNION 2012                  |     14
 + 222 | NANA MIZUKI LIVE GRACE 2013 -OPUSII-         |      2
 + 212 | NANA MIZUKI LIVE CIRCUS 2013                 |     11
 + 217 | NANA MIZUKI LIVE FLIGHT 2014                 |     11
 + 218 | NANA MIZUKI LIVE FLIGHT 2014+                |      3
 + 230 | NANA MIZUKI LIVE THEATER 2015 -ACOUSTIC-     |      2
 + 210 | NANA MIZUKI LIVE ADVENTURE 2015              |     14
 +(29 行)
 +
 +nanamizuki=>
 +</code>
 +
 +==== 公演リスト ====
 +
 +指定のツアーの公演情報を一覧します。
 +
 +<code sql>
 +select t1.id
 +      ,t1.event_date
 +      ,t2.place
 +      ,t3.venue
 +  from events t1 left outer join places t2 on(t1.place_id = t2.id)
 +                 left outer join venues t3 on(t1.venue_id = t3.id)
 + where tour_id = ? -- tour_id
 +  order by event_date, id
 + ;
 +</code>
 +
 +tour_id に 227（NANA MIZUKI LIVE SENSATION 2003 のid）を指定すると、このような出力になります。
 +<code sql>
 +nanamizuki=> select t1.id
 +nanamizuki->       ,t1.event_date
 +nanamizuki->       ,t2.place
 +nanamizuki->       ,t3.venue
 +nanamizuki->   from events t1 left outer join places t2 on(t1.place_id = t2.id)
 +nanamizuki->                  left outer join venues t3 on(t1.venue_id = t3.id)
 +nanamizuki->  where tour_id = 227 -- NANA MIZUKI LIVE SENSATION 2003 tour_id
 +nanamizuki->   order by event_date, id
 +nanamizuki-> ;
 + id  | event_date | place  |         venue
 +-----+------------+--------+------------------------
 + 299 | 2003-07-19 | 宮城   | Zepp Sendai
 + 300 | 2003-07-21 | 北海道 | Zepp Sapporo
 + 301 | 2003-07-26 | 大阪   | Zepp Osaka
 + 302 | 2003-07-27 | 福岡   | Zepp Fukuoka
 + 303 | 2003-08-03 | 東京   | Zepp Tokyo 　
 + 304 | 2003-08-22 | 埼玉   | 川口リリアメインホール
 + 305 | 2003-08-24 | 愛知   | 愛知県勤労会館
 + 306 | 2003-08-25 | 京都   | 京都会館第二ホール
 + 307 | 2003-08-27 | 愛媛   | 松山市民会館中ホール
 + 308 | 2003-08-31 | 東京   | 渋谷公会堂
 +(10 行)
 +
 +nanamizuki=>
 +</code>
 +
 +==== セットリスト ====
 +
 +指定の公演のセットリストを一覧します。
 +
 +<code sql>
 +select t1.id
 +      ,t2.list_name
 +      ,t1.order_index
 +      ,t3.song_title
 +  from setlists t1 left outer join setlist_types t2 on( t1.list_type = t2.id)
 +                   left outer join songs         t3 on( t1.song_id = t3.id)
 + where event_id = ? -- event_id
 +  order by t1.list_type, t1.order_index
 + ;
 +</code>
 +
 +テーブルsetlist_typesを作成していないならこのクエリで。
 +<code sql>
 +select t1.id
 +      ,t1.list_type
 +      ,t1.order_index
 +      ,t3.song_title
 +  from setlists t1 left outer join songs t3 on( t1.song_id = t3.id)
 + where event_id = ? -- event_id
 +  order by t1.list_type, t1.order_index
 + ;
 +</code>
 +
 +
 +event_id に 299,303,304 を指定してみます。
 +
 +<code sql>
 +nanamizuki=> select t1.id
 +nanamizuki->       ,t2.list_name
 +nanamizuki->       ,t1.order_index
 +nanamizuki->       ,t3.song_title
 +nanamizuki->   from setlists t1 left outer join setlist_types t2 on( t1.list_type = t2.id)
 +nanamizuki->                    left outer join songs         t3 on( t1.song_id = t3.id)
 +nanamizuki->  where event_id = 299 -- 2003-07-19 宮城 Zepp Sendai
 +nanamizuki->   order by t1.list_type, t1.order_index
 +nanamizuki->  ;
 +  id  |  list_name   | order_index |            song_title
 +------+--------------+-------------+----------------------------------
 + 6765 | セットリスト |           1 | TRANMIGRATION
 + 6766 | セットリスト |           2 | アノネ～まみむめ☆もがちょ～
 + 6767 | セットリスト |           3 | Heaven Knows
 + 6768 | セットリスト |           4 | リプレイマシン -custom-
 + 6769 | セットリスト |           5 | HONEY FLOWER
 + 6770 | セットリスト |           6 | 恋してる…
 + 6771 | セットリスト |           7 | 二人のMemory
 + 6772 | セットリスト |           8 | 想い
 + 6773 | セットリスト |           9 | POWER GATE
 + 6774 | セットリスト |          10 | What cheer？
 + 6775 | セットリスト |          11 | Year！めっちゃホリディ(松浦亜弥)
 + 6776 | セットリスト |          12 | 空と心と…
 + 6777 | セットリスト |          13 | フリースタイル
 + 6778 | セットリスト |          14 | ジュリエット
 + 6779 | セットリスト |          15 | still in the groove
 + 6780 | セットリスト |          16 | supersonic girl
 + 6781 | セットリスト |          17 | Suddenly～巡り合えて～
 + 6782 | セットリスト |          18 | New Sensation
 + 6783 | セットリスト |          19 | refrain
 + 6784 | アンコール   |           1 | The place of happiness
 + 6785 | アンコール   |           2 | PROTECTION
 +(21 行)
 +
 +nanamizuki=> select t1.id
 +nanamizuki->       ,t2.list_name
 +nanamizuki->       ,t1.order_index
 +nanamizuki->       ,t3.song_title
 +nanamizuki->   from setlists t1 left outer join setlist_types t2 on( t1.list_type = t2.id)
 +nanamizuki->                    left outer join songs         t3 on( t1.song_id = t3.id)
 +nanamizuki->  where event_id = 303 -- 2003-08-03 東京 Zepp Tokyo 　
 +nanamizuki->   order by t1.list_type, t1.order_index
 +nanamizuki->  ;
 +  id  |  list_name   | order_index |          song_title
 +------+--------------+-------------+------------------------------
 + 6850 | セットリスト |           1 | TRANMIGRATION
 + 6851 | セットリスト |           2 | アノネ～まみむめ☆もがちょ～
 + 6852 | セットリスト |           3 | Heaven Knows
 + 6853 | セットリスト |           4 | リプレイマシン -custom-
 + 6854 | セットリスト |           5 | HONEY FLOWER
 + 6855 | セットリスト |           6 | 恋してる…
 + 6856 | セットリスト |           7 | 二人のMemory
 + 6857 | セットリスト |           8 | 想い
 + 6858 | セットリスト |           9 | POWER GATE
 + 6859 | セットリスト |          10 | What cheer？
 + 6860 | セットリスト |          11 | VALENTI(BoA)
 + 6861 | セットリスト |          12 | 空と心と…
 + 6862 | セットリスト |          13 | フリースタイル
 + 6863 | セットリスト |          14 | ジュリエット
 + 6864 | セットリスト |          15 | still in the groove
 + 6865 | セットリスト |          16 | supersonic girl
 + 6866 | セットリスト |          17 | Suddenly～巡り合えて～
 + 6867 | セットリスト |          18 | New Sensation
 + 6868 | セットリスト |          19 | refrain
 + 6869 | アンコール   |           1 | The place of happiness
 + 6870 | アンコール   |           2 | PROTECTION
 + 6871 | Wアンコール  |           1 | New Sensation
 +(22 行)
 +
 +nanamizuki=> select t1.id
 +nanamizuki->       ,t2.list_name
 +nanamizuki->       ,t1.order_index
 +nanamizuki->       ,t3.song_title
 +nanamizuki->   from setlists t1 left outer join setlist_types t2 on( t1.list_type = t2.id)
 +nanamizuki->                    left outer join songs         t3 on( t1.song_id = t3.id)
 +nanamizuki->  where event_id = 304 -- 2003-08-22 埼玉 川口リリアメインホール
 +nanamizuki->   order by t1.list_type, t1.order_index
 +nanamizuki->  ;
 +  id  |  list_name   | order_index |           song_title
 +------+--------------+-------------+--------------------------------
 + 6872 | セットリスト |           1 | TRANSMIGRATION
 + 6873 | セットリスト |           2 | The place of happiness
 + 6874 | セットリスト |           3 | おんなになあれ
 + 6875 | セットリスト |           4 | リプレイマシン -custom-
 + 6876 | セットリスト |           5 | HONEY FLOWER
 + 6877 | セットリスト |           6 | 恋してる…
 + 6878 | セットリスト |           7 | 二人のMemory
 + 6879 | セットリスト |           8 | STAND
 + 6880 | セットリスト |           9 | POWER GATE
 + 6881 | セットリスト |          10 | What cheer？
 + 6882 | セットリスト |          11 | 明日へのbrilliant road(angela)
 + 6883 | セットリスト |          12 | テルミドール
 + 6884 | セットリスト |          13 | Heaven Knows
 + 6885 | セットリスト |          14 | 想い
 + 6886 | セットリスト |          15 | still in the groove
 + 6887 | セットリスト |          16 | supersonic girl
 + 6888 | セットリスト |          17 | Suddenly～巡り合えて～
 + 6889 | セットリスト |          18 | New Sensation
 + 6890 | セットリスト |          19 | refrain
 + 6891 | アンコール   |           1 | アノネ～まみむめ☆もがちょ～
 + 6892 | アンコール   |           2 | PROTECTION
 +(21 行)
 +
 +nanamizuki=>
 +</code>
 +
 +本家様とも一致していることがわかりました。
 +
 +
 +===== CGIの作成 =====
 +
 +作成したクエリを基に、HTMLコンテンツを生成するCGIを作成します。
 +
 +==== ツアーリスト ====
 +
 +ツアーのリストをHTMLで出力します。
 +ツアー名にリンクを張り、b.plを呼び出せるようにします。
 +<WRAP>
 +
 +<WRAP 60% left>
 +<code perl a.pl>
 +use Encode;
 +use CGI;
 +use DBI;
 +
 +my $dbname="nanamizuki";
 +my $dbhost="your.database.host.addr";
 +my $dbuser="db user id";
 +my $dbpass="db password";
 +
 +my $encstr = "utf-8";
 +
 +my $cgi = CGI->new();
 +my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost",$dbuser,$dbpass) or die  "cannot connect database.";
 +my $sth = $dbh->prepare('SELECT id, tour_title FROM tours ORDER BY tour_start'  );
 +my $id;
 +my $tour_title;
 +
 +  print $cgi->header({charset=>'utf-8'});
 +
 +  print $cgi->start_html( {title=>'Live List', charset=>'utf-8'} );
 +
 +  print $cgi->h1( {class=>"h1"}, "Live");
 +
 +  print $cgi->start_ol({});
 +
 +  $dbh->{AutoCommit}=0;
 +  $sth->execute();
 +  $sth->bind_col(  1, \$id         );
 +  $sth->bind_col(  2, \$tour_title );
 +
 +  while( $sth->fetchrow_hashref() != undef )
 +  {
 +    $id         = encode($encstr, $id        );
 +    $tour_title = encode($encstr, $tour_title);
 +    print $cgi->li( $cgi->a({class=>'leftlink', href=>"b.cgi?id=$id"},$tour_title) );
 +  }
 +  $sth->finish();
 +  $dbh->commit();
 +
 +  print $cgi->end_ol();
 +
 +  print $cgi->end_html();
 +
 +  $dbh->disconnect();
 +</code>
 +</WRAP>
 +<WRAP 30% left>
 +{{:documents:other:other-040-001.png?nolink|}}
 +</WRAP>
 +
 +</WRAP>
 +
 +==== 公演情報とセットリスト ====
 +
 +ツアーの公演会場を横に並べたテーブルを作り、その中にセットリストを埋め込みします。
 +
 +先のa.pl で生成されたリンクから呼び出される想定で作られていて、URLパラメタ id=xxx で示すツアーの公演情報を横並びに表示、情報中にセットリストが配置されます。
 +
 +例は、id=227の"NANA MIZUKI LIVE SENSATION 2003"を選択した場合の結果です。
 +
 +<WRAP>
 +<WRAP 60% left>
 +<code perl b.pl>
 +use Encode;
 +use CGI;
 +use DBI;
 +
 +my $dbname="nanamizuki";
 +my $dbhost="your.database.host.addr";
 +my $dbuser="db user id";
 +my $dbpass="b password";
 +
 +my $encstr = "utf-8";
 +
 +my $cgi = CGI->new();
 +my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost",$dbuser,$dbpass) or die  "cannot connect database.";
 +
 +my $sth1 = $dbh->prepare('SELECT t1.id
 +                                ,t1.event_date
 +                                ,t2.place
 +                                ,t3.venue
 +                            FROM events t1 left outer join places t2 on( t1.place_id = t2.id)
 +                                           left outer join venues t3 on( t1.venue_id = t3.id)
 +                           WHERE tour_id = ?
 +                           ORDER BY event_date, id');
 +
 +my $sth2 = $dbh->prepare('SELECT t2.song_title
 +                            FROM setlists t1 LEFT OUTER JOIN songs t2 ON( t1.song_id = t2.id)
 +                           WHERE event_id = ?
 +                             AND list_type = ?
 +                           ORDER BY t1.order_index');
 +
 +my $event_id;
 +my $event_date;
 +my $place;
 +my $venue;
 +my $song_title;
 +my $cgi_id = $cgi->param('id');
 +
 +  $dbh->{AutoCommit}=0;
 +
 +  print $cgi->header({charset=>'utf-8'});
 +  print $cgi->start_html( {title=>encode($encstr,decode('euc-jp','公演情報')), charset=>'utf-8'} );
 +
 +  print $cgi->start_table({border=>1});
 +
 +  ############ 開催場所、会場情報展開
 +
 +  print $cgi->start_Tr();
 +
 +  $sth1->bind_param( 1, $cgi_id);
 +  $sth1->execute();
 +  $sth1->bind_col(  1, \$event_id   );
 +  $sth1->bind_col(  2, \$event_date );
 +  $sth1->bind_col(  3, \$place      );
 +  $sth1->bind_col(  4, \$venue      );
 +
 +  while( $sth1->fetchrow_hashref() != undef )
 +  {
 +    $event_id   = encode($encstr, $event_id  );
 +    $event_date = encode($encstr, $event_date);
 +    $place      = encode($encstr, $place     );
 +    $venue      = encode($encstr, $venue     );
 +
 +    print $cgi->td( {nowrap=>'nowrap'}, $cgi->p( encode($encstr, decode('euc-jp','公演日：')).$event_date ),
 +                                        $cgi->p( encode($encstr, decode('euc-jp','開催地：')).$place      ),
 +                                        $cgi->p( encode($encstr, decode('euc-jp','　会場：')).$venue      )   );
 +  }
 +  $dbh->commit();
 +
 +  print $cgi->end_Tr();
 +
 +
 +  ############ セットリストの展開
 +
 +  print $cgi->start_Tr();
 +
 +  $sth1->bind_param( 1, $cgi_id);
 +  $sth1->execute();
 +  $sth1->bind_col(  1, \$event_id   );
 +  $sth1->bind_col(  2, \$event_date );
 +  $sth1->bind_col(  3, \$place      );
 +  $sth1->bind_col(  4, \$venue      );
 +
 +  while( $sth1->fetchrow_hashref() != undef )
 +  {
 +    $event_id   = encode($encstr, $event_id);
 +
 +    print $cgi->start_td({valign=>'top'});
 +
 +    ## セットリスト
 +    $sth2->bind_param( 1, $event_id);
 +    $sth2->bind_param( 2, 1        );
 +    $sth2->execute();
 +    $sth2->bind_col( 1, \$song_title );
 +
 +    print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','セットリスト')));
 +    print $cgi->start_ol();
 +    while( $sth2->fetchrow_hashref() != undef )
 +    {
 +      $song_title = encode($encstr, $song_title);
 +      print $cgi->li({}, $song_title);
 +    }
 +    print $cgi->end_ol();
 +    $dbh->commit();
 +
 +    ## アンコール
 +    $sth2->bind_param( 1, $event_id);
 +    $sth2->bind_param( 2, 2        );
 +    $sth2->execute();
 +    $sth2->bind_col( 1, \$song_title );
 +
 +    print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','アンコール')));
 +    print $cgi->start_ol();
 +    while( $sth2->fetchrow_hashref() != undef )
 +    {
 +      $song_title = encode($encstr, $song_title);
 +      print $cgi->li({}, $song_title);
 +    }
 +    print $cgi->end_ol();
 +    $dbh->commit();
 +
 +    ## Wアンコール
 +    $sth2->bind_param( 1, $event_id);
 +    $sth2->bind_param( 2, 3        );
 +    $sth2->execute();
 +    $sth2->bind_col( 1, \$song_title );
 +
 +    print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','Wアンコール')));
 +    print $cgi->start_ol();
 +    while( $sth2->fetchrow_hashref() != undef )
 +    {
 +      $song_title = encode($encstr, $song_title);
 +      print $cgi->li({}, $song_title);
 +    }
 +    print $cgi->end_ol();
 +    $dbh->commit();
 +
 +  }
 +  $sth1->finish();
 +  $sth2->finish();
 +  $dbh->commit();
 +
 +  print $cgi->end_td();
 +  print $cgi->end_Tr();
 +
 +  print $cgi->end_table();
 +
 +  print $cgi->end_html();
 +
 +  $dbh->disconnect();
 +</code>
 +</WRAP>
 +<WRAP 35% left>
 +{{:documents:other:other-040-002.png|}}
 +</WRAP>
 +</WRAP>
 +
 +===== データメンテナンス =====
 +
 +なんで曲や場所、会場、ツアーを別々にしたのか。
 +それはデータのメンテナンスを行いやすくするためです。
 +
 +たとえば、会場の情報を一覧してみます。
 +<code sql>
 +nanamizuki=> select id,'['||venue||']' from venues order by 2;
 + id  |                      ?column?
 +-----+----------------------------------------------------
 + 179 | [Club Diamond Hall]
 + 180 | [ON AIR OSAKA]
 + 181 | [QVCマリンフィールド（千葉マリンスタジアム）]
 + 182 | [Resorts World Theatre, Resorts World Sentosa]
 + 183 | [Zepp Fukuoka]
 + 184 | [Zepp Nagoya]
 + 185 | [Zepp Osaka]
 + 186 | [Zepp Sapporo]
 + 187 | [Zepp Sendai]
 + 189 | [Zepp Tokyo 　]
 + 188 | [Zepp Tokyo]
 + 190 | [さいたまスーパーアリーナ]
 + 191 | [さぬき市野外音楽広場テアトロン]
 + 192 | [とりぎん文化会館]
 + 193 | [ひめぎんホール]
 + 194 | [びわ湖ホール]
 + 195 | [やまぎんホール(山形県県民会館)]
 + 196 | [アクトシティ浜松]
 + 197 | [アスティとくしま]
 + 198 | [エコパアリーナ]
 + 199 | [グランキューブ大阪　メインホール]
 + 200 | [グリーンドーム前橋]
 + 201 | [セキスイハイムスーパーアリーナ（グランディ・21）]
 + 202 | [ゼビオアリーナ仙台]
 + 203 | [ダイヤモンドホール]
 + 204 | [ニトリ文化ホール]
 + 205 | [パシフィコ横浜 国立大ホール]
 + 206 | [パシフィコ横浜・国立大ホール]
 + 207 | [ヤクルトホール]
 + 208 | [リンクステーションホール青森（青森市文化会館）]
 + 209 | [三重県営サンアリーナ]
 + 210 | [京都会館第二ホール]
 + 211 | [仙台サンプラザホール]
 + 212 | [佐賀市文化会館]
 + 213 | [倉敷市民会館]
 + 214 | [北上市文化交流センター さくらホール]
 + 215 | [北九州メディアドーム]
 + 216 | [原宿アストロホール]
 + 217 | [名古屋国際会議場　センチュリーホール]
 + 218 | [名古屋国際会議場センチュリーホール]
 + 219 | [名古屋市民会館]
 + 220 | [周南市文化会館]
 + 221 | [国立代々木競技場第一体育館]
 + 222 | [大阪NHKホール]
 + 223 | [大阪厚生年金会館]
 + 224 | [大阪城ホール]
 + 225 | [大阪市中央体育館]
 + 226 | [天母體育館]
 + 227 | [宇都宮市文化会館]
 + 228 | [宮崎市民文化ホール]
 + 229 | [富士急ハイランド コニファーフォレスト]
 + 230 | [島根県民会館]
 + 231 | [川口リリアメインホール]
 + 232 | [幕張メッセ イベントホール]
 + 233 | [広島アステールプラザ　大ホール]
 + 234 | [愛媛県武道館]
 + 236 | [愛媛県県民文化会館 サブホール]
 + 235 | [愛媛県県民文化会館]
 + 237 | [愛知厚生年金会館]
 + 238 | [愛知県勤労会館]
 + 239 | [新居浜市市民文化センター大ホール]
 + 240 | [新潟県民会館]
 + 241 | [日本ガイシホール]
 + 242 | [日本武道館]
 + 243 | [日比谷公園大音楽堂(日比谷野外大音楽堂)]
 + 244 | [本多の森ホール]
 + 245 | [東京エレクトロンホール宮城]
 + 246 | [東京ドーム]
 + 247 | [東京厚生年金会館]
 + 248 | [東京国際フォーラム ホールC]
 + 249 | [松山市民会館　大ホール]
 + 250 | [松山市民会館中ホール]
 + 251 | [森のホール２１大ホール(松戸市文化会館）]
 + 252 | [横浜アリーナ]
 + 253 | [横浜スタジアム]
 + 254 | [渋谷公会堂]
 + 255 | [福井フェニックスプラザ]
 + 256 | [福岡サンパレス ホール]
 + 257 | [福岡国際会議場メインホール]
 + 258 | [秋田県民会館]
 + 259 | [西武ドーム]
 + 260 | [西武プリンスドーム]
 + 261 | [銀座ヤマハホール]
 + 262 | [長崎ブリックホール]
 + 263 | [長野ビッグハット]
 + 264 | [須賀川市文化センター]
 + 265 | [高岡市民会館]
 + 266 | [鹿児島市民文化ホール 第1ホール]
 + 267 | [ＮＨＫ大阪ホール]
 +(89 行)
 +
 +nanamizuki=>
 +</code>
 +
 +以下の組ははたぶん同じ場所ですよね。
 +<code tt>
 + 189 | [Zepp Tokyo 　]
 + 188 | [Zepp Tokyo]
 +
 + 205 | [パシフィコ横浜 国立大ホール]
 + 206 | [パシフィコ横浜・国立大ホール]
 +
 + 217 | [名古屋国際会議場　センチュリーホール]
 + 218 | [名古屋国際会議場センチュリーホール]
 +</code>
 +
 +NHK大阪ホールの表記が正しそうだけど実際がわからないので保留。
 +<code>
 + 222 | [大阪NHKホール]
 + 267 | [ＮＨＫ大阪ホール]
 +</code>
 +
 +こういうデータの揺れはよくあります。このidがどのくらい使われているか公演テーブルを見てみましょう。
 +<code sql>
 +nanamizuki=> select events.id,venue_id,venue from events left outer join venues on(venue_id=venues.id) where venue_id in(189,188,205,206,217,218);
 + id  | venue_id |                venue
 +-----+----------+--------------------------------------
 + 336 |      217 | 名古屋国際会議場　センチュリーホール
 + 347 |      218 | 名古屋国際会議場センチュリーホール
 + 348 |      218 | 名古屋国際会議場センチュリーホール
 + 314 |      188 | Zepp Tokyo
 + 303 |      189 | Zepp Tokyo 　
 + 298 |      188 | Zepp Tokyo
 + 313 |      188 | Zepp Tokyo
 + 333 |      205 | パシフィコ横浜 国立大ホール
 + 334 |      205 | パシフィコ横浜 国立大ホール
 + 325 |      206 | パシフィコ横浜・国立大ホール
 +(10 行)
 +
 +nanamizuki=>
 +</code>
 +
 +もしID=188,206,217 の記述が正しいなら、それ以外は間違っていることになるので訂正しなくてはなりません。
 +苦労してテーブルを分けた結果がこういう時に報われます。
 +
 +<code sql>
 +nanamizuki=> update events set venue_id = 188 where venue_id = 189;
 +UPDATE 1
 +nanamizuki=> update events set venue_id = 206 where venue_id = 205;
 +UPDATE 2
 +nanamizuki=> update events set venue_id = 217 where venue_id = 218;
 +UPDATE 2
 +nanamizuki=> select events.id,venue_id,venue from events left outer join venues on(venue_id=venues.id) where venue_id in(189,188,205,206,217,218);
 + id  | venue_id |                venue
 +-----+----------+--------------------------------------
 + 336 |      217 | 名古屋国際会議場　センチュリーホール
 + 314 |      188 | Zepp Tokyo
 + 298 |      188 | Zepp Tokyo
 + 313 |      188 | Zepp Tokyo
 + 303 |      188 | Zepp Tokyo
 + 333 |      206 | パシフィコ横浜・国立大ホール
 + 334 |      206 | パシフィコ横浜・国立大ホール
 + 347 |      217 | 名古屋国際会議場　センチュリーホール
 + 348 |      217 | 名古屋国際会議場　センチュリーホール
 + 325 |      206 | パシフィコ横浜・国立大ホール
 +(10 行)
 +
 +nanamizuki=>
 +</code>
 +テーブルeventsの項目venue_idの差し替えだけで、公演場所の誤記が訂正されました。
 +
 +曲テーブルもざっと眺めてみます。
 +<code sql>
 +nanamizuki=> select id,song_title from songs order by 2;
 + id  |                     song_title
 +-----+-----------------------------------------------------
 + 641 | 1986年のマリリン
 + 642 | 76th Star
 + 643 | 7COLORS
 +
 +-- 中略 --
 +
 + 791 | Synchrogazer
 + 792 | TRANMIGRATION
 + 793 | TRANSMIGRATION
 + 794 | TRANSMIGRATION 2007
 + 795 | TRY AGAIN
 + 796 | Take a Shot
 +
 +-- 以下略 --
 +(320 行)
 +
 +nanamizuki=>
 +</code>
 +
 +ちょっと怪しげ。
 +<code tt>
 + 792 | TRANMIGRATION
 + 793 | TRANSMIGRATION
 + 794 | TRANSMIGRATION 2007
 +</code>
 +
 +誤表記をワザと掲載している訳でないなら、ID=792は誤りでID=793が正解ですね。
 +
 +<code sql>
 +nanamizuki=>  select id, event_id, song_id from setlists where song_id=792;
 +  id  | event_id | song_id
 +------+----------+---------
 + 6723 |      296 |     792
 + 6704 |      295 |     792
 + 6744 |      297 |     792
 + 6764 |      298 |     792
 + 6765 |      299 |     792
 + 6786 |      300 |     792
 + 6808 |      301 |     792
 + 6829 |      302 |     792
 + 6850 |      303 |     792
 +(9 行)
 +
 +nanamizuki=>
 +</code>
 +9つの公演のセットリストに誤りがあることがわかります。
 +修正しちゃいましょう。
 +<code sql>
 +nanamizuki=> update setlists
 +nanamizuki->    set song_id=793
 +nanamizuki->       ,comments='change 792 to 793 '||now()
 +nanamizuki->  where song_id=792
 +nanamizuki-> ;
 +UPDATE 9
 +nanamizuki=> select id, event_id, song_id, comments
 +nanamizuki->   from setlists
 +nanamizuki->  where song_id in (792,793)
 +nanamizuki-> ;
 +  id  | event_id | song_id |                    comments
 +------+----------+---------+-------------------------------------------------
 + 6744 |      297 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6764 |      298 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6765 |      299 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6786 |      300 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6808 |      301 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6829 |      302 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6850 |      303 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6723 |      296 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6704 |      295 |     793 | change 792 to 793 2015-12-08 15:04:12.189903+00
 + 6872 |      304 |     793 |
 + 6893 |      305 |     793 |
 + 6915 |      306 |     793 |
 + 6937 |      307 |     793 |
 + 6959 |      308 |     793 |
 + 6985 |      309 |     793 |
 + 7013 |      310 |     793 |
 + 7033 |      311 |     793 |
 + 7093 |      314 |     793 |
 + 7113 |      315 |     793 |
 + 7133 |      316 |     793 |
 + 7157 |      317 |     793 |
 + 7176 |      318 |     793 |
 + 7198 |      319 |     793 |
 + 7221 |      320 |     793 |
 + 7241 |      321 |     793 |
 + 7261 |      322 |     793 |
 + 7281 |      323 |     793 |
 + 7301 |      324 |     793 |
 + 7321 |      325 |     793 |
 + 7383 |      328 |     793 |
 + 7447 |      331 |     793 |
 + 7467 |      332 |     793 |
 + 7489 |      333 |     793 |
 + 7739 |      344 |     793 |
 + 7961 |      353 |     793 |
 + 7985 |      354 |     793 |
 + 8010 |      355 |     793 |
 + 8036 |      356 |     793 |
 + 8085 |      358 |     793 |
 + 8110 |      359 |     793 |
 + 8532 |      378 |     793 |
 + 8922 |      394 |     793 |
 + 8942 |      395 |     793 |
 + 9405 |      415 |     793 |
 + 6683 |      294 |     793 |
 + 6667 |      294 |     793 |
 + 7053 |      312 |     793 |
 + 7073 |      313 |     793 |
 +(48 行)
 +
 +nanamizuki=>
 +</code>
 +
 +コメントを残しておくと後からやったことを思い出すのに役立ちます。
 +
 +===== 追記 =====
 +
 +ツアータイトル「水樹奈々 X’mas LIVE "supersonic girl"」の公演情報が足りないよ、と連絡があった。
 +見ると、このツアーは同日に２回、同じ場所同じ会場で実施されたものらしく、公演テーブル上へのデータ取り込み時に1レコードに集約されてしまっていた。\\
 +これに対処する。
 +
 +まず公演テーブルに項目stageを追加。その公演は第何回目なのか明示的に表すことにする。
 +<code sql>
 +ALTER TABLE events
 +  ADD COLUMN stage integer NOT NULL DEFAULT 1;
 +</code>
 +
 +Window関数 row_number() でツアー毎に公演回数を計算して項目stageへ格納する。
 +
 +
 +以降は対処のクエリ実行ログ。
 +
 +<code sql>
 +nanamizuki=> select * from tours
 +nanamizuki-> ;
 + id  |                  tour_title                  | tour_start |  tour_end  | comments
 +-----+----------------------------------------------+------------+------------+----------
 + 207 | 20th Birthday Anniversary Live               | 2000-01-23 | 2000-01-23 |
 + 208 | NANA MIZUKI "LIVE ATTRACTION 2002"           | 2002-11-23 | 2003-01-05 |
 + 209 | NANA MIZUKI LIVE ACADEMY 2010                | 2010-02-14 | 2010-03-27 |
 + 210 | NANA MIZUKI LIVE ADVENTURE 2015              | 2015-07-04 | 2015-09-19 |
 + 211 | NANA MIZUKI LIVE CASTLE 2011                 | 2011-12-03 | 2011-12-04 |
 +
 +-- 中略 --
 +
 + 233 | NANA MIZUKI LIVEDOM 2006-BIRTH-              | 2006-01-21 | 2006-01-21 |
 + 234 | 水樹奈々 21ANNIVERSARY CONCERT "HAPPY"       | 2001-01-21 | 2001-01-21 |
 + 235 | 水樹奈々 X’mas LIVE "supersonic girl"       | 2001-12-23 | 2001-12-23 |
 +(29 行)
 +
 +nanamizuki=> select * from events where tour_id=235;
 + id  | tour_id | event_date | venue_id | place_id | comments | stage
 +-----+---------+------------+----------+----------+----------+-------
 + 294 |     235 | 2001-12-23 |      216 |      380 |          |     1
 +(1 行)
 +
 +nanamizuki=> insert into events(tour_id, event_date, venue_id, place_id)
 +nanamizuki-> select tour_id
 +nanamizuki->       ,event_date
 +nanamizuki->       ,venue_id
 +nanamizuki->       ,place_id
 +nanamizuki->   from events
 +nanamizuki->  where tour_id=235
 +nanamizuki-> ;
 +INSERT 0 1
 +nanamizuki=> select * from events where tour_id=235;
 + id  | tour_id | event_date | venue_id | place_id | comments | stage
 +-----+---------+------------+----------+----------+----------+-------
 + 437 |     235 | 2001-12-23 |      216 |      380 |          |     1
 + 294 |     235 | 2001-12-23 |      216 |      380 |          |     1
 +(2 行)
 +
 +nanamizuki=> select * from setlists where event_id=294 order by id,event_id, list_type, order_index;
 +  id  | event_id | list_type | order_index | song_id | comments
 +------+----------+-----------+-------------+---------+----------
 + 6654 |      294 |         1 |           1 |     721 |
 + 6655 |      294 |         1 |           2 |     696 |
 + 6656 |      294 |         1 |           3 |     710 |
 + 6657 |      294 |         1 |           4 |     861 |
 + 6658 |      294 |         1 |           5 |     926 |
 + 6659 |      294 |         1 |           6 |     936 |
 + 6660 |      294 |         1 |           7 |     718 |
 + 6661 |      294 |         1 |           8 |     716 |
 + 6662 |      294 |         1 |           9 |     812 |
 + 6663 |      294 |         1 |          10 |     733 |
 + 6664 |      294 |         1 |          11 |     865 |
 + 6665 |      294 |         1 |          12 |     833 |
 + 6666 |      294 |         1 |          13 |     799 |
 + 6667 |      294 |         2 |           1 |     793 |
 + 6668 |      294 |         2 |           2 |     854 |
 + 6669 |      294 |         3 |           1 |     945 |
 + 6670 |      294 |         1 |           1 |     721 |
 + 6671 |      294 |         1 |           2 |     696 |
 + 6672 |      294 |         1 |           3 |     710 |
 + 6673 |      294 |         1 |           4 |     861 |
 + 6674 |      294 |         1 |           5 |     926 |
 + 6675 |      294 |         1 |           6 |     936 |
 + 6676 |      294 |         1 |           7 |     718 |
 + 6677 |      294 |         1 |           8 |     716 |
 + 6678 |      294 |         1 |           9 |     812 |
 + 6679 |      294 |         1 |          10 |     733 |
 + 6680 |      294 |         1 |          11 |     865 |
 + 6681 |      294 |         1 |          12 |     833 |
 + 6682 |      294 |         1 |          13 |     799 |
 + 6683 |      294 |         2 |           1 |     793 |
 + 6684 |      294 |         2 |           2 |     854 |
 + 6685 |      294 |         3 |           1 |     945 |
 +(32 行)
 +
 +nanamizuki=> update setlists
 +nanamizuki->    set event_id = 437
 +nanamizuki->  where id >= 6670
 +nanamizuki->    and id <= 6685
 +nanamizuki-> ;
 +UPDATE 16
 +nanamizuki=> select * from setlists where event_id=294 order by id,event_id, list_type, order_index;
 +  id  | event_id | list_type | order_index | song_id | comments
 +------+----------+-----------+-------------+---------+----------
 + 6654 |      294 |         1 |           1 |     721 |
 + 6655 |      294 |         1 |           2 |     696 |
 + 6656 |      294 |         1 |           3 |     710 |
 + 6657 |      294 |         1 |           4 |     861 |
 + 6658 |      294 |         1 |           5 |     926 |
 + 6659 |      294 |         1 |           6 |     936 |
 + 6660 |      294 |         1 |           7 |     718 |
 + 6661 |      294 |         1 |           8 |     716 |
 + 6662 |      294 |         1 |           9 |     812 |
 + 6663 |      294 |         1 |          10 |     733 |
 + 6664 |      294 |         1 |          11 |     865 |
 + 6665 |      294 |         1 |          12 |     833 |
 + 6666 |      294 |         1 |          13 |     799 |
 + 6667 |      294 |         2 |           1 |     793 |
 + 6668 |      294 |         2 |           2 |     854 |
 + 6669 |      294 |         3 |           1 |     945 |
 +(16 行)
 +
 +nanamizuki=> select * from setlists where event_id=437 order by id,event_id, list_type, order_index;
 +  id  | event_id | list_type | order_index | song_id | comments
 +------+----------+-----------+-------------+---------+----------
 + 6670 |      437 |         1 |           1 |     721 |
 + 6671 |      437 |         1 |           2 |     696 |
 + 6672 |      437 |         1 |           3 |     710 |
 + 6673 |      437 |         1 |           4 |     861 |
 + 6674 |      437 |         1 |           5 |     926 |
 + 6675 |      437 |         1 |           6 |     936 |
 + 6676 |      437 |         1 |           7 |     718 |
 + 6677 |      437 |         1 |           8 |     716 |
 + 6678 |      437 |         1 |           9 |     812 |
 + 6679 |      437 |         1 |          10 |     733 |
 + 6680 |      437 |         1 |          11 |     865 |
 + 6681 |      437 |         1 |          12 |     833 |
 + 6682 |      437 |         1 |          13 |     799 |
 + 6683 |      437 |         2 |           1 |     793 |
 + 6684 |      437 |         2 |           2 |     854 |
 + 6685 |      437 |         3 |           1 |     945 |
 +(16 行)
 +
 +nanamizuki=> with t as (
 +nanamizuki(> SELECT id
 +nanamizuki(>       ,event_date
 +nanamizuki(>       ,tour_id
 +nanamizuki(>       ,place_id
 +nanamizuki(>       ,venue_id
 +nanamizuki(>       ,row_number() over(partition by tour_id order by event_date, id) as stage
 +nanamizuki(>   FROM events
 +nanamizuki(> )
 +nanamizuki-> update events
 +nanamizuki->    set stage = t.stage
 +nanamizuki->   from t
 +nanamizuki->  where events.id         = t.id
 +nanamizuki->    and events.event_date = t.event_date
 +nanamizuki->    and events.tour_id    = t.tour_id
 +nanamizuki->    and events.place_id   = t.place_id
 +nanamizuki->    and events.venue_id   = t.venue_id
 +nanamizuki-> ;
 +UPDATE 146
 +nanamizuki=> select * from events order by tour_id, event_date;
 + id  | tour_id | event_date | venue_id | place_id | comments | stage
 +-----+---------+------------+----------+----------+----------+-------
 + 292 |     207 | 2000-01-23 |      261 |      380 |          |     1
 + 295 |     208 | 2002-11-23 |      180 |      365 |          |     1
 + 296 |     208 | 2002-11-28 |      179 |      378 |          |     2
 + 297 |     208 | 2002-11-30 |      248 |      380 |          |     3
 + 298 |     208 | 2003-01-05 |      188 |      380 |          |     4
 + 353 |     209 | 2010-02-14 |      257 |      386 |          |     1
 + 354 |     209 | 2010-02-20 |      239 |      377 |          |     2
 + 355 |     209 | 2010-03-07 |      211 |      366 |          |     3
 + 356 |     209 | 2010-03-13 |      252 |      384 |          |     4
 + 357 |     209 | 2010-03-14 |      252 |      384 |          |     5
 + 358 |     209 | 2010-03-20 |      224 |      365 |          |     6
 + 359 |     209 | 2010-03-27 |      241 |      378 |          |     7
 +
 +-- 中略 --
 +
 + 326 |     233 | 2006-01-21 |      242 |      380 |          |     1
 + 293 |     234 | 2001-01-21 |      207 |      380 |          |     1
 + 294 |     235 | 2001-12-23 |      216 |      380 |          |     1
 + 437 |     235 | 2001-12-23 |      216 |      380 |          |     2
 +(146 行)
 +
 +nanamizuki=>
 +</code>
 +----
 +{{tag> 技術資料 SQL www perl json スクレイピング}}
documents/other/memo02/other-040.txt · 最終更新: by k896951
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki