簡単なSQLから手順を追って複雑なSQLに書き換える

概要

• 試合のチームごとの集計を行うために、簡単なSQLから手順を追って複雑なSQLに書き換えていく

追記（2023/3/30）

• 各テーブルのCREATE文とINSERT文をChatGPTを使って作成。

テーブルの一覧

• scores：試合毎の選手の得点

create table sample_scores.scores (
  member_id int not null,
  match_id int not null,
  score int,
  primary key (member_id, match_id)
);

/*
以下は、チームAからチームEまでの各チームの5人全員が1試合ずつプレイした場合の30レコード分の例です。
それぞれの試合で1チームが0点から5点のランダムな得点を獲得し、
もう1チームが逆転することもあるという想定で記述しています。
*/

insert into sample_scores.scores (member_id, match_id, score) values
-- Match 1
(2001, 1001, 3), (2002, 1001, 5), (2003, 1001, 0), (2004, 1001, 4), (2005, 1001, 2),
(2006, 1001, 1), (2007, 1001, 2), (2008, 1001, 4), (2009, 1001, 5), (2010, 1001, 1),
-- Match 2
(2001, 1002, 2), (2002, 1002, 0), (2003, 1002, 1), (2004, 1002, 5), (2005, 1002, 4),
(2006, 1002, 3), (2007, 1002, 1), (2008, 1002, 2), (2009, 1002, 4), (2010, 1002, 0),
-- Match 3
(2011, 1003, 0), (2012, 1003, 3), (2013, 1003, 4), (2014, 1003, 2), (2015, 1003, 1),
(2016, 1003, 4), (2017, 1003, 5), (2018, 1003, 2), (2019, 1003, 1), (2020, 1003, 3),
-- Match 4
(2011, 1004, 1), (2012, 1004, 3), (2013, 1004, 5), (2014, 1004, 0), (2015, 1004, 4),
(2021, 1004, 5), (2022, 1004, 1), (2023, 1004, 2), (2024, 1004, 0), (2025, 1004, 3),
-- Match 5
(2011, 1005, 3), (2012, 1005, 4), (2013, 1005, 2), (2014, 1005, 1), (2015, 1005, 5),
(2021, 1005, 4), (2022, 1005, 2), (2023, 1005, 3), (2024, 1005, 1), (2025, 1005, 0),
-- Match 6
(2001, 1006, 3), (2002, 1006, 2), (2003, 1006, 5), (2004, 1006, 0), (2005, 1006, 1),
(2006, 1006, 4), (2007, 1006, 2), (2008, 1006, 1), (2009, 1006, 3), (2010, 1006, 5),
-- Match 7
(2011, 1007, 5), (2012, 1007, 4), (2013, 1007, 3), (2014, 1007, 1), (2015, 1007, 2),
(2016, 1007, 0), (2017, 1007, 2), (2018, 1007, 1), (2019, 1007, 5), (2020, 1007, 4),
-- Match 8
(2011, 1008, 2), (2012, 1008, 5), (2013, 1008, 4), (2014, 1008, 1), (2015, 1008, 3),
(2021, 1008, 3), (2022, 1008, 0), (2023, 1008, 2), (2024, 1008, 4), (2025, 1008, 5),
-- Match 9
(2011, 1009, 1), (2012, 1009, 2), (2013, 1009, 0), (2014, 1009, 5), (2015, 1009, 3),
(2021, 1009, 5), (2022, 1009, 2), (2023, 1009, 3), (2024, 1009, 4), (2025, 1009, 1),
-- Match 10
(2001, 1010, 4), (2002, 1010, 2), (2003, 1010, 5), (2004, 1010, 1), (2005, 1010, 0),
(2006, 1010, 3), (2007, 1010, 1), (2008, 1010, 2), (2009, 1010, 4), (2010, 1010, 5);

• members ：選手の一覧

create table sample_scores.members (
  id int not null,
  member_name varchar(255) not null,
  team_id int not null,
  primary key (id)
);

insert into sample_scores.members (id, member_name, team_id) values
  -- Team A
  (2001, '太郎', 1001), (2002, '次郎', 1001), (2003, '花子', 1001),
  (2004, '一郎', 1001), (2005, '二郎', 1001),
  -- Team B
  (2006, '三郎', 1002), (2007, '四郎', 1002), (2008, '美智子', 1002),
  (2009, '五郎', 1002), (2010, '六郎', 1002),
  -- Team C
  (2011, '恵子', 1003), (2012, '夏子', 1003), (2013, '春子', 1003),
  (2014, '秋子', 1003), (2015, '冬子', 1003),
  -- Team D
  (2016, '武', 1004), (2017, '真由美', 1004), (2018, '弘明', 1004),
  (2019, '英二', 1004), (2020, '知子', 1004),
  -- Team E
  (2021, '悠斗', 1005), (2022, '大樹', 1005), (2023, '怜央', 1005),
  (2024, '愛美', 1005), (2025, '美桜', 1005);

• teams：選手が所属するチームの一覧

create table sample_scores.teams (
  pos int not null,
  id int not null,
  team_name varchar(255) not null,
  primary key (id)
);

insert into sample_scores.teams (pos, id, team_name) values
  (1, 1001, 'Team A'),
  (2, 1002, 'Team B'),
  (3, 1003, 'Team C'),
  (4, 1004, 'Team D'),
  (5, 1005, 'Team E');

• matches：試合の開催一覧

create table sample_scores.matches (
  id int not null,
  match_on date not null,
  primary key (id)
);

insert into sample_scores.matches (id, match_on) values
  (1001, '2022-06-01'),
  (1002, '2022-06-02'),
  (1003, '2022-06-03'),
  (1004, '2022-06-04'),
  (1005, '2022-06-05'),
  (1006, '2022-06-06'),
  (1007, '2022-06-07'),
  (1008, '2022-06-08'),
  (1009, '2022-06-09'),
  (1010, '2022-06-10');

【1】各テーブルをそれぞれ出力します

• scores からメンバー毎の試合数とスコアの集計
• 01_select_scores.sql

select
  member_id,
  sum(score) as score_sum,
  count(match_id) as match_count,
  count(distinct match_id) as match_unique_count
from sample_scores.scores
group by member_id

• teams からチームの一覧の集計
• 02_select_teams.sql

select pos, id, team_name from sample_scores.teams order by pos

• membersからメンバーの一覧の集計
• 03_select_members.sql

select id, member_name, team_id from sample_scores.members

• matchesから該当の期間の試合
• 06_select_matches_between.sql

select id
from sample_scores.matches matches
where match_on between '2022-06-01' and '2022-06-30'

テーブルを結合する手順

【2】teamsとmembersをつなぎます

• チーム毎のメンバー一覧
• 04_select_teams[f:id:tetsuwan30:20230328013833j:plain]_join_members.sql

select
  teams.pos as team_pos,
  teams.team_name,
  members.id as member_id,
  members.member_name
from
  sample_scores.teams teams
  inner join sample_scores.members members on teams.id = members.team_id

【3】teamsとmembersとscoresをつなぎます

• チーム毎のメンバーの試合とスコア
• 05_select_teams_join_members_left_join_scores.sql

select
  teams.pos as team_pos,
  teams.team_name,
  members.id as member_id,
  members.member_name,
  scores.match_id,
  scores.score
from
  sample_scores.teams teams
  inner join sample_scores.members members on teams.id = members.team_id
  left join sample_scores.scores scores on scores.member_id = members.id

【4】scoresとmatchesをつなぎます

• 該当の期間の試合のスコア
• 07_select_scores_matches.sql

select
  scores.member_id,
  scores.match_id,
  scores.score
from
  sample_scores.scores scores
  inner join sample_scores.matches matches on matches.id = scores.match_id
where matches.match_on between '2022-06-01' and '2022-06-30'

【5】teamsとmembersとscoresとmatchesをつなぎます

• 期間中のチーム毎のメンバーの試合とスコア
• 08_select_teams_join_member_left_join_target_score.sql

select
  teams.pos as team_pos,
  teams.team_name,
  members.id as member_id,
  members.member_name,
  target_score.match_id,
  target_score.score
from
  sample_scores.teams teams
  inner join sample_scores.members members on teams.id = members.team_id
  left join (
    select
      scores.member_id,
      scores.match_id,
      scores.score
    from
      sample_scores.scores scores
      inner join sample_scores.matches matches on matches.id = scores.match_id
    where matches.match_on between '2022-06-01' and '2022-06-30'
  ) target_score on target_score.member_id = members.id

【6】teamsごとに集計します

• 期間中のチーム毎のメンバーの試合とスコアを集計
• 09_select_teams_join_members_join_target_score_group_by_teams.sql

select
  teams.pos as team_pos,
  teams.team_name,
  count(target_score.match_id) as match_count,
  sum(target_score.score) as score_sum
from
  sample_scores.teams teams
  inner join sample_scores.members members on teams.id = members.team_id
  left join (
    select
      scores.member_id,
      scores.match_id,
      scores.score
    from
      sample_scores.scores scores
      inner join sample_scores.matches matches on matches.id = scores.match_id
    where matches.match_on between '2022-06-01' and '2022-06-30'
  ) target_score on target_score.member_id = members.id
group by teams.pos, teams.team_name
order by teams.pos