sql理解

 重要イメージを持つ
1対多の場合、多につられて1の表示が複数回
テーブル設計
1対1・1対多・ 多対多を実装するイメージがつく

JOIN
JOIN, LEFT JOIN
LEFT JOIN して結合テーブルがNULLになるイメージ



基本構文をスラスラ実装できる

 テーブル設計単一
1対1
外部キー + uniq 制約

1対多
起点のテーブルを1にするときは、多のレコード分増える
起点テーブルが「多」の場合は、レコード数は増えない
ex)
ユーザー ・権限テーブル


多対多
ユーザー・グループ
ユーザーグループ(中間テーブル)
ユーザー1で、中間テーブルは複数紐づく
中間テーブルからもう一つのテーブルは 起点テーブルが多になるので、レコード数これ以上増えない


-- 1対1のケース
-- user・user_profileがあった場合

-- profileが複数紐づいていれば、u.idが複数でて、 first_name, last_namega
複数でる
-- 複数を発生したくない場合は、uniqをuser_idにつける
select u.id, u.first_name, u.last_name FROM users u
  JOIN user_profile up
    ON up.user_id = u.id;


-- 1対多で、distincを必要とするケース
-- 注文をしているユーザーを数える場合など

SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id;


 テーブルの分割イメージ企業などの情報を分割してるケース
企業テーブル(company)
id

企業情報(profile)
企業情報

企業管理(admin)
サービス運営者だけ利用するやつ


事実・アクションなどでも分けるのか

 パフォーマンス外部キー
インデックスがあれば

アルゴリズム
選択するメソッド

 マイグレーションリリースに影響するケースがある

 よく使う構文group by
-- 各ユーザーごとの注文数を取得
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;
having
-- 3回以上注文したユーザー
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 3;
exists
大体でJOINも。

-- 注文が1件でもあるユーザーを取得
SELECT *
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 代替案
-- 注文があるユーザー
SELECT u.*
FROM users u
JOIN orders o ON o.user_id = u.id;

-- 注文がないユーザー
-- LEFT JOINだ！RIGHT JOINではない
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

 イメージjoin or left join で結合、1対多、多対多 の観点で結合する。1対多の場合親のレコードは複数
left joinはデータがなければ結合テーブルがnull
join(inner join)の場合は結合テーブルにデータがないとレコードがない

条件を絞る 進捗のステータス where history.status = 1
group by c.id などで切り分ける
havingを利用して切り分けたデータに処理を加える

 結合イメージ

content id
title
item id
title
content_id
id
item_id
user_id



SNS
イントロ

1

1
1


SNS
イントロ

1

1
2


プログラミング
html

3

3
3


プログラミング
js

null
null
null
null


 joinテーブル連結のイメージselect * from テーブル
1対多 inner join

多のレコード数になる
1対多 left join

1のレコード数になる
多対多 のinner join

1対多(中間テーブル)

多(中間テーブル)対1みたいな感じになる

title	item id	content_id	id	item_id	user_id
SNS	イントロ	1		1	1
SNS	イントロ	1		1	2
プログラミング	html	3		3	3
プログラミング	js	null	null	null	null

engineer reborn

compose

version: '3'

services:
  # MySQL
  db:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test_database
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      TZ: 'Asia/Tokyo'
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
    - mysql_data:/var/lib/mysql
    - ./db/my.cnf:/etc/mysql/conf.d/my.cnf
    - ./db/sqls:/docker-entrypoint-initdb.d
    ports:
    - 3306:3306
volumes:
  mysql_data:

サンプルデータ

テーブル
- 外部キーをはる場合、「Mysql」の場合は自動でインデックスが貼られるらしい
  - ただし、複合キーなどの場合は明示的に記載が必要
構成
- 企業
  - 企業ユーザー
  - グループ
    - ユーザー
    - コンテンツ
      - コンテンツアイテム
    - コンテンツ履歴

-- Companies
CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    phone_number VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
);

-- CompanyUsers
CREATE TABLE company_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    user_type INT NOT NULL DEFAULT 0, -- 0: 一般、1: 管理者
    company_id INT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (company_id) REFERENCES companies(id)
);

-- CompanyGroups
CREATE TABLE company_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    company_id INT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (company_id) REFERENCES companies(id)
);

-- Users
CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    company_id INT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (company_id) REFERENCES companies(id)
);

-- GroupUsers
CREATE TABLE group_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_group_id INT,
    user_id INT,
    FOREIGN KEY (company_group_id) REFERENCES company_groups(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Contents
CREATE TABLE contents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    lecture_num INT,
    amount INT,
    created_at DATETIME,
    updated_at DATETIME
);

-- CompanyGroupContents (中間テーブル: グループとコンテンツの多対多)
CREATE TABLE company_group_contents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_group_id INT,
    content_id INT,
    created_at DATETIME,
    FOREIGN KEY (company_group_id) REFERENCES company_groups(id),
    FOREIGN KEY (content_id) REFERENCES contents(id)
);

-- ContentItems (コンテンツに紐づくレクチャー)
CREATE TABLE content_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    video_url VARCHAR(255),
    video_length FLOAT,
    content_id INT,
    order_num INT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (content_id) REFERENCES contents(id)
);

-- ContentItemHistory (ユーザーごとのitem視聴履歴)
CREATE TABLE content_item_histories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content_item_id INT,
    content_id INT, -- ここは冗長かもしれない by GTP パフォーマンスの目的で意図的に作るケースもある
    user_id INT,
    status INT, -- 0: 未完了, 1: 完了
    progress INT, -- 再生進捗率(％)
    order_num INT, -- 次レクチャー順
    created_at DATETIME,
    FOREIGN KEY (content_item_id) REFERENCES content_items(id),
    FOREIGN KEY (content_id) REFERENCES contents(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE (content_item_id, user_id)
);

-- Companies
INSERT INTO companies (name, address, phone_number, created_at, updated_at) VALUES
('Company A', 'Tokyo', '03-1234-5678', NOW(), NOW()),
('Company B', 'Osaka', '06-2345-6789', NOW(), NOW());

-- CompanyUsers
INSERT INTO company_users (name, email, user_type, company_id, created_at, updated_at) VALUES
('Admin A', 'adminA@example.com', 1, 1, NOW(), NOW()),
('User B', 'userB@example.com', 0, 1, NOW(), NOW()),
('Admin C', 'adminC@example.com', 1, 2, NOW(), NOW());

-- CompanyGroups
INSERT INTO company_groups (name, company_id, created_at, updated_at) VALUES
('Group Alpha', 1, NOW(), NOW()),
('Group Beta', 1, NOW(), NOW()),
('Group Gamma', 2, NOW(), NOW());

-- Users
INSERT INTO users (name, email, company_id, created_at, updated_at) VALUES
('User1', 'user1@example.com', 1, NOW(), NOW()),
('User2', 'user2@example.com', 1, NOW(), NOW()),
('User3', 'user3@example.com', 2, NOW(), NOW());

-- GroupUsers
INSERT INTO group_users (company_group_id, user_id) VALUES
(1, 1), -- Group Alpha - User1
(1, 2), -- Group Alpha - User2
(2, 2), -- Group Beta - User2
(3, 3); -- Group Gamma - User3

-- Contents
INSERT INTO contents (title, lecture_num, amount, created_at, updated_at) VALUES
('Content 101', 5, 10000, NOW(), NOW()),
('Content 102', 3, 8000, NOW(), NOW()),
('Content 201', 7, 15000, NOW(), NOW());

-- CompanyGroupContents (コンテンツとグループの関連)
INSERT INTO company_group_contents (company_group_id, content_id, created_at) VALUES
(1, 1, NOW()), -- Group Alpha に Content 101
(2, 2, NOW()), -- Group Beta に Content 102
(3, 3, NOW()); -- Group Gamma に Content 201

-- ContentItems
INSERT INTO content_items (title, description, video_url, video_length, content_id, order_num, created_at, updated_at) VALUES
('Lecture 1', 'Desc 1', 'http://example.com/video1', 30.5, 1, 1, NOW(), NOW()),
('Lecture 2', 'Desc 2', 'http://example.com/video2', 25.0, 1, 2, NOW(), NOW()),
('Lecture 3', 'Desc 3', 'http://example.com/video3', 45.0, 2, 1, NOW(), NOW()),
('Lecture 4', 'Desc 4', 'http://example.com/video4', 40.0, 3, 1, NOW(), NOW());

-- ContentItemHistory (履歴：完了/未完了)
INSERT INTO content_item_histories (content_item_id, content_id, user_id, status, progress, order_num, created_at) VALUES
(1, 1, 1, 1, 100, 2, NOW()), -- User1 完了
(2, 1, 1, 0, 50, 3, NOW()), -- User1 未完了
(3, 2, 2, 1, 100, 1, NOW()), -- User2 完了
(4, 3, 3, 0, 30, 1, NOW()); -- User3 未完了

engineer reborn

sqlを実際に投げる場合を想定

group by 検証

group by にidを指定=> ok

select con.id, count(*) as count, count(*) * con.amount as amount from Contents con
  JOIN GroupTable g on g.id = con.group_id
  JOIN ContentItems ci on ci.content_id = con.id
  LEFT JOIN ContentItemHistory history on history.content_item_id = ci.id
  -- where g.company_id = 1
  where history.status = 1
  group by con.id
  having count(ci.id) = count(history.id);

titleを指定 => エラー
title とamontで一位にできないから

mysql> select con.title, count(*) as count, count(*) * con.amount as amount from Contents con
    ->   JOIN GroupTable g on g.id = con.group_id
    ->   JOIN ContentItems ci on ci.content_id = con.id
    ->   LEFT JOIN ContentItemHistory history on history.content_item_id = ci.id
    ->   -- where g.company_id = 1
    ->   where history.status = 1
    ->   group by con.title
    ->   having count(ci.title) = count(history.id);
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.con.amount' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

コンテンツごとに、すべてのレクチャー（ContentItems）を完了したユーザー数」を集計

考えかた

分割して考える方が良いかも

コンテンツごとに、完了したレクチャーを集計
group byなどで、企業ごとだったり、ユーザーごと割る

group byとhaving のサブクエリ

サンプル

content id	title	item id	content_id	id	item_id	user_id
1	SNS	イントロ	1		1	1
1	SNS	イントロ	1		1	2
2	プログラミング	html	3		3	3
2	プログラミング	js	null	null	null	null

group by content idをすると
- 行はコンテンツ 1, 2の2つになる
havingのサブクエリ
- con.idに1 と2が入り、合致した値ががいる

SELECT 
  con.id AS content_id,
  con.title,
  COUNT(DISTINCT history.user_id) AS completed_user_count,
  COUNT(DISTINCT history.user_id) * con.recture_num AS total_amount  -- amountがなければ仮でrecture_num使う
FROM 
  Contents con
JOIN 
  GroupTable g ON g.id = con.group_id
JOIN 
  ContentItems ci ON ci.content_id = con.id
LEFT JOIN 
  ContentItemHistory history ON history.content_item_id = ci.id
WHERE 
  g.company_id = 1
  AND history.status = 1
GROUP BY 
  con.id
HAVING 
  (
    SELECT COUNT(*) 
    FROM ContentItems ci2 
    WHERE ci2.content_id = con.id
  ) = (
    SELECT COUNT(*) 
    FROM ContentItemHistory h2 
    WHERE h2.user_id = history.user_id
      AND h2.status = 1
      AND h2.content_item_id IN (SELECT id FROM ContentItems WHERE content_id = con.id)
  )
;

with句あり

WITH content_item_count AS (
  SELECT 
    content_id, 
    COUNT(*) AS item_count
  FROM 
    ContentItems
  GROUP BY 
    content_id
),
user_item_complete AS (
  SELECT 
    ci.content_id,
    history.user_id,
    COUNT(ci.id) AS completed_items  -- DISTINCTなくてOK！
  FROM 
    ContentItems ci
  LEFT JOIN 
    ContentItemHistory history 
    ON history.content_item_id = ci.id 
    AND history.status = 1
  GROUP BY 
    ci.content_id, history.user_id
)

SELECT 
  uic.content_id,
  COUNT(uic.user_id) AS completed_user_count
FROM 
  user_item_complete uic
JOIN 
  content_item_count cic 
  ON cic.content_id = uic.content_id
WHERE 
  uic.completed_items = cic.item_count
GROUP BY 
  uic.content_id
;

with句なし

SELECT 
  uic.content_id,
  COUNT(uic.user_id) AS completed_user_count
FROM (
  SELECT 
    ci.content_id,
    history.user_id,
    COUNT(ci.id) AS completed_items
  FROM 
    ContentItems ci
  LEFT JOIN 
    ContentItemHistory history 
    ON history.content_item_id = ci.id 
    AND history.status = 1
  GROUP BY 
    ci.content_id, history.user_id
) AS uic
JOIN (
  SELECT 
    content_id, 
    COUNT(*) AS item_count
  FROM 
    ContentItems
  GROUP BY 
    content_id
) AS cic
ON uic.content_id = cic.content_id
WHERE 
  uic.completed_items = cic.item_count
GROUP BY 
  uic.content_id
;

engineer reborn

sql めも

admin
- 企業一覧
- 企業詳細
  - 企業名
  - 住所
  - 電話番号
  - 担当者一覧
    - 担当者名 email
企業管理
- 企業ユーザー一覧
- 企業ユーザー詳細
- コンテンツ一覧
  - コンテンツ名レクチャー数 xx個
- コンテンツ詳細
  - コンテンツ名
  - 説明
  - レクチャー一覧
    - レクチャー名
    - 時間
- グループ一覧
  - グループ名 xxx人 xxコンテンツ
- グループ詳細
  - グループ名 xx人
  - コンテンツ xx個
- 受講履歴一覧
  - コンテンツ名、受講生xx人完了率xx %
- 受講履歴詳細
  - コンテンツ名
  - 受講生一覧
    - ユーザー名 xx%
ユーザー
- top
  - 学習中のコンテンツ一覧
  - 全体の進捗
    - 完了数 / 受講中のコンテンツ * item数
- コンテンツ詳細
  - コンテンツ名
  - 進捗
    - コンテンツ完了数 / コンテンツ数

sqlのパターン

シンプル
- [x]企業一覧
1対多のケース
- 企業詳細
  - 企業情報
  - 担当者一覧
複雑
- 受講履歴一覧
- 受講履歴詳細
- コンテンツ詳細
  - 進捗

シンプル

企業一覧

-- 結合する
SELECT c.id, c.name, cu.id, cu.name FROM Companies c
  JOIN CompanyUsers cu on cu.company_id = c.id -- cuが複数あるとcuの数でレコードが作成される
 ;
-- 企業名と最初ユーザー1人を表示
SELECT c.name, Min(cu.name) FROM Companies c
  JOIN CompanyUsers cu on cu.company_id = c.id -- cuが複数あるとcuの数でレコードが作成される
  group by c.name, cu.name;

-- idが一番若いユーザーを表示

SELECT 
  c.name AS company_name,
  cu.name AS user_name
FROM 
  Companies c
JOIN 
  CompanyUsers cu 
  ON cu.company_id = c.id
JOIN 
  (
    SELECT 
      company_id, 
      MIN(id) AS min_user_id
    FROM 
      CompanyUsers
    GROUP BY 
      company_id
  ) min_cu 
  ON cu.company_id = min_cu.company_id 
 AND cu.id = min_cu.min_user_id;


-- 考え方
-- 1. 連結したすべてのデータが出てくる => 1対多なので、company.idは複数回でて
-- 2. group byしたテーブルを作成すると company_idは1つしかないので、
-- 3. joinでcompany _idと min user idを指定すれば、レコードは1つに絞られる

mysql> SELECT c.id, c.name, cu.id, cu.name FROM Companies c
    ->   JOIN CompanyUsers cu on cu.company_id = c.id -- cucu
    ->  ;
+----+------------+----+--------------+
| id | name       | id | name         |
+----+------------+----+--------------+
|  1 | Alpha Corp |  1 | Taro Manager |
|  1 | Alpha Corp |  2 | Hanako Admin |
|  2 | Beta Inc   |  3 | Jiro Staff   |
+----+------------+----+--------------+
3 rows in set (0.01 sec)

mysql> 
mysql> select company_id, min(id) from CompanyUsers 
    -> group by company_id;
+------------+---------+
| company_id | min(id) |
+------------+---------+
|          1 |       1 |
|          2 |       3 |
+------------+---------+
2 rows in set (0.00 sec)

-- 結果
-- 企業 と複数のユーザーで複数レコードが作成する
select c.name, cu.name
   FROM Companies c
   JOIN CompanyUsers cu on c.id = cu.company_id
   -- 企業id と最小のユーザーid (group byで)
  JOIN (
    select company_id, min(id) as min_user_id
       from CompanyUsers
       group by company_id
  ) min_cu
  -- 複数のidでjoinしたテーブルから絞る
  on min_cu.company_id = c.id
  and min_cu.min_user_id = cu.id;

企業詳細
- GROUP_CONCATで多の部分を表示

select c.name, c.address,c.phone_number, GROUP_CONCAT(cu.name, cu.email) from Companies c
   JOIN CompanyUsers cu on c.id = cu.company_id
   group by c.name,  c.address, c.phone_number;


mysql> select c.name, c.address,c.phone_number, GROUP_CONCAT(cu.name, cu.email) from Companies c
    ->    JOIN CompanyUsers cu on c.id = cu.company_id
    ->    group by c.name,  c.address, c.phone_number;
+------------+---------+--------------+---------------------------------------------------------+
| name       | address | phone_number | GROUP_CONCAT(cu.name, cu.email)                         |
+------------+---------+--------------+---------------------------------------------------------+
| Alpha Corp | Tokyo   | 03-1111-2222 | Taro Managertaro@alpha.com,Hanako Adminhanako@alpha.com |
| Beta Inc   | Osaka   | 06-3333-4444 | Jiro Staffjiro@beta.com                                 |
+------------+---------+--------------+---------------------------------------------------------+
2 rows in set (0.01 sec)

複雑なケース
- 受講履歴一覧
- 受講履歴詳細
- コンテンツ詳細

コンテンツごとに受講生数の数 一覧 -- joinして group byでできそう
-- 完了率
-- 完了数: ユーザーが item length と、history lengthが一致しているかず   / ユーザー数

select c.id, c.name, count(u.id),  完了数 / count(u.id) from
  from Contents c
  JOIN CompanyGroupContents cgc on cgc.id = c.content_id
  JOIN CompanyGroups cg on cg.id = cgc.company_group_id
  JOIN ComapnyGroupUsers cgu on cgu.group_id = cg.id
  JOIN Users u on cgu.user_id = u.id
  group by c.id;
 -- これでやれはグループ毎 ユーザー数までは出る

-- with句あり
WITH content_users AS (
  -- コンテンツごとに対象ユーザーを集める
  SELECT
    c.id AS content_id,
    gu.user_id
  FROM
    Contents c
    JOIN CompanyGroupContents cgc ON cgc.content_id = c.id
    JOIN CompanyGroups cg ON cg.id = cgc.company_group_id
    JOIN GroupUsers gu ON gu.company_group_id = cg.id
),
user_item_status AS (
  -- 各ユーザーが、そのコンテンツ内で何個itemを完了しているか
  SELECT
    cu.content_id,
    cu.user_id,
    COUNT(DISTINCT ci.id) AS total_items,
    COUNT(DISTINCT CASE WHEN cih.status = 1 THEN cih.content_item_id END) AS completed_items
  FROM
    content_users cu
    JOIN ContentItems ci ON ci.content_id = cu.content_id
    LEFT JOIN ContentItemHistory cih ON cih.content_item_id = ci.id AND cih.user_id = cu.user_id
  GROUP BY
    cu.content_id, cu.user_id
),
completed_users AS (
  -- コンテンツごとに「すべて完了しているユーザー数」を数える
  SELECT
    content_id,
    COUNT(*) AS completed_user_count
  FROM
    user_item_status
  WHERE
    total_items = completed_items
  GROUP BY
    content_id
)
SELECT
  c.id AS content_id,
  c.title AS content_title,
  COUNT(DISTINCT cu.user_id) AS total_users,
  IFNULL(cu2.completed_user_count, 0) AS completed_users,
  CASE
    WHEN COUNT(DISTINCT cu.user_id) = 0 THEN 0
    ELSE ROUND(IFNULL(cu2.completed_user_count, 0) / COUNT(DISTINCT cu.user_id) * 100, 2)
  END AS completion_rate_percentage
FROM
  Contents c
  LEFT JOIN content_users cu ON cu.content_id = c.id
  LEFT JOIN completed_users cu2 ON cu2.content_id = c.id
GROUP BY
  c.id, c.title, cu2.completed_user_count
ORDER BY
  c.id;

⭐️カンパニーグループの中間テーブルグループユーザー命名変更、カンパニーグループユーザー

手順
1. コンテンツとグループ、ユーザーの集合
2. ユーザーの完了した集合
3. left joinで結合する
理解できない。下記の2つの理解度不足かも、分解して調べる
- left join 複数結合
  - left join && group by をすると、全体の集計と、対象の集計ができるだろうな
  - join にすると全体の集計は取れなくなるはず
- group by
  - group byをして left joinをする
    - これは、集計結果のテーブルを結合してるんだな
      - content, 複数のレコード
    - group by で複数で集計する場合
      - contentのみ
        
        content 1,2,3で表示される
      - contentとuser
        
        content 1, user, 1,2,3みたい感じになりレコード数は増える

-- with句なし

SELECT
  con.id AS content_id,
  con.title AS content_title,
  IFNULL(user_stats.total_users, 0) AS total_users,
  IFNULL(user_stats.completed_users, 0) AS completed_users,
  CASE
    WHEN IFNULL(user_stats.total_users, 0) = 0 THEN 0
    ELSE ROUND(user_stats.completed_users / user_stats.total_users * 100, 2)
  END AS completion_rate_percentage
FROM
  Contents con
LEFT JOIN (
  SELECT
    c.id AS content_id,
    COUNT(DISTINCT gu.user_id) AS total_users,
    SUM(
      CASE
        WHEN user_completion.total_items = user_completion.completed_items THEN 1
        ELSE 0
      END
    ) AS completed_users
  FROM
    Contents c
    JOIN CompanyGroupContents cgc ON cgc.content_id = c.id
    JOIN CompanyGroups cg ON cg.id = cgc.company_group_id
    JOIN GroupUsers gu ON gu.company_group_id = cg.id
    LEFT JOIN (
      SELECT
        ci.content_id,
        gu2.user_id,
        COUNT(DISTINCT ci.id) AS total_items,
        COUNT(DISTINCT CASE WHEN cih.status = 1 THEN cih.content_item_id END) AS completed_items
      FROM
        GroupUsers gu2
        JOIN CompanyGroups cg2 ON cg2.id = gu2.company_group_id
        JOIN CompanyGroupContents cgc2 ON cgc2.company_group_id = cg2.id
        JOIN Contents c2 ON c2.id = cgc2.content_id
        JOIN ContentItems ci ON ci.content_id = c2.id
        LEFT JOIN ContentItemHistory cih ON cih.content_item_id = ci.id AND cih.user_id = gu2.user_id
      GROUP BY
        ci.content_id, gu2.user_id
    ) AS user_completion ON user_completion.content_id = c.id AND user_completion.user_id = gu.user_id
  GROUP BY
    c.id
) AS user_stats ON user_stats.content_id = con.id
ORDER BY
  con.id;

engineer reborn

sql ミック本 SQL 基本

select の実行順番

ORDER BYが一番最後なんだ
- FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY

実行計画 ⭐️

explain

2章

select
- 出力したい列名を指定
別名 p115

定数

商品が出力値で、as がカラム名

SELECT '商品' as mojiretu FROM Shohin;

重複 p115

mysql> select name from users;
+-------+
| name  |
+-------+
| User1 |
| User2 |
| User3 |
| User3 |
+-------+

-- distinc
mysql> select distinct(name) from users;
+-------+
| name  |
+-------+
| User1 |
| User2 |
| User3 |
+-------+

絞り込み where p115

where =, inなどがある

select shohin_mei
  FROM Shohin
  WHERE shohin_bunnrui = '衣服';

算術・比較演算 p140

日付
- 日付は文字列で比較できる
  - 日にちレベルの比較
  - 時間レベルの比較

mysql> select * from content_item_histories;
+----+-----------------+------------+---------+--------+----------+-----------+---------------------+
| id | content_item_id | content_id | user_id | status | progress | order_num | created_at          |
+----+-----------------+------------+---------+--------+----------+-----------+---------------------+
|  1 |               1 |          1 |       1 |      1 |      100 |         2 | 2025-06-09 20:47:54 |
|  2 |               2 |          1 |       1 |      0 |       50 |         3 | 2025-06-09 20:47:54 |
|  3 |               3 |          2 |       2 |      1 |      100 |         1 | 2025-06-09 20:47:54 |
|  4 |               4 |          3 |       3 |      0 |       30 |         1 | 2025-06-11 20:47:54 |
+----+-----------------+------------+---------+--------+----------+-----------+---------------------+
4 rows in set (0.00 sec)

--日にちレベル
mysql> select * from content_item_histories where created_at > '2025-06-10';
+----+-----------------+------------+---------+--------+----------+-----------+---------------------+
| id | content_item_id | content_id | user_id | status | progress | order_num | created_at          |
+----+-----------------+------------+---------+--------+----------+-----------+---------------------+
|  4 |               4 |          3 |       3 |      0 |       30 |         1 | 2025-06-11 20:47:54 |
+----+-----------------+------------+---------+--------+----------+-----------+---------------------+
1 row in set (0.00 sec)

-- 時間レベル
mysql> select * from content_item_histories where created_at > '2025-06-11 21:00:00';
Empty set (0.01 sec)

nullの扱い p140

whereはtrueなったモノを返す
- nullとの比較は unknown になるはず

mysql> select * from contents
    -> ;
+----+-------------+-------------+--------+---------------------+---------------------+
| id | title       | lecture_num | amount | created_at          | updated_at          |
+----+-------------+-------------+--------+---------------------+---------------------+
|  1 | Content 101 |           5 |  10000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  2 | Content 102 |           3 |   8000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  3 | Content 201 |           7 |  15000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  4 | NULL        |           2 |   NULL | NULL                | NULL                |
+----+-------------+-------------+--------+---------------------+---------------------+
4 rows in set (0.01 sec)

mysql> select * from contents where amount > 9000;
+----+-------------+-------------+--------+---------------------+---------------------+
| id | title       | lecture_num | amount | created_at          | updated_at          |
+----+-------------+-------------+--------+---------------------+---------------------+
|  1 | Content 101 |           5 |  10000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  3 | Content 201 |           7 |  15000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
+----+-------------+-------------+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> select * from contents where amount > 9000 OR amount IS NULL;
+----+-------------+-------------+--------+---------------------+---------------------+
| id | title       | lecture_num | amount | created_at          | updated_at          |
+----+-------------+-------------+--------+---------------------+---------------------+
|  1 | Content 101 |           5 |  10000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  3 | Content 201 |           7 |  15000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  4 | NULL        |           2 |   NULL | NULL                | NULL                |
+----+-------------+-------------+--------+---------------------+---------------------+
3 rows in set (0.00 sec)

論理演算子 p153

not, and, orなどがある
()つけて優先度を上げる

集約 p176

複数行を1行にまとめる
- count, avgなど


mysql> select * from contents;
+----+-------------+-------------+--------+---------------------+---------------------+
| id | title       | lecture_num | amount | created_at          | updated_at          |
+----+-------------+-------------+--------+---------------------+---------------------+
|  1 | Content 101 |           5 |  10000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  2 | Content 102 |           3 |   8000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  3 | Content 201 |           7 |  15000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  4 | NULL        |           2 |   NULL | NULL                | NULL                |
+----+-------------+-------------+--------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select avg(amount) from contents;
+-------------+
| avg(amount) |
+-------------+
|  11000.0000 |
+-------------+
1 row in set (0.01 sec)

テーブルの切り分け p p195

group by
- 指定したカラムごとに集約する
- 集計に利用しそう
  - content_idを group byして画面に表示することはないだろうな
  - コンテンツごとの item数(コマ数)を表示したい場合などに使うかも

mysql> select * from content_items
    -> ;
+----+-----------+-------------+---------------------------+--------------+------------+-----------+---------------------+---------------------+
| id | title     | description | video_url                 | video_length | content_id | order_num | created_at          | updated_at          |
+----+-----------+-------------+---------------------------+--------------+------------+-----------+---------------------+---------------------+
|  1 | Lecture 1 | Desc 1      | http://example.com/video1 |         30.5 |          1 |         1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  2 | Lecture 2 | Desc 2      | http://example.com/video2 |           25 |          1 |         2 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  3 | Lecture 3 | Desc 3      | http://example.com/video3 |           45 |          2 |         1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  4 | Lecture 4 | Desc 4      | http://example.com/video4 |           40 |          3 |         1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
+----+-----------+-------------+---------------------------+--------------+------------+-----------+---------------------+---------------------+
4 rows in set (0.01 sec)

mysql> select content_id from content_items group by content_id;
+------------+
| content_id |
+------------+
|          1 |
|          2 |
|          3 |
+------------+
3 rows in set (0.01 sec)

mysql> select content_id, count(*) from content_items group by content_id;
+------------+----------+
| content_id | count(*) |
+------------+----------+
|          1 |        2 |
|          2 |        1 |
|          3 |        1 |
+------------+----------+
3 rows in set (0.00 sec)

コンテンツごとに itemsを確認したい
- joinすると contentが複数表示される。子要素が複数紐づいている場合

mysql> 
mysql> 
mysql> select * from contents c
    -> JOIN content_items ci
    ->   ON ci.content_id = c.id
    -> ;
+----+-------------+-------------+--------+---------------------+---------------------+----+-----------+-------------+---------------------------+--------------+------------+-----------+---------------------+---------------------+
| id | title       | lecture_num | amount | created_at          | updated_at          | id | title     | description | video_url                 | video_length | content_id | order_num | created_at          | updated_at          |
+----+-------------+-------------+--------+---------------------+---------------------+----+-----------+-------------+---------------------------+--------------+------------+-----------+---------------------+---------------------+
|  1 | Content 101 |           5 |  10000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |  1 | Lecture 1 | Desc 1      | http://example.com/video1 |         30.5 |          1 |         1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  1 | Content 101 |           5 |  10000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |  2 | Lecture 2 | Desc 2      | http://example.com/video2 |           25 |          1 |         2 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  2 | Content 102 |           3 |   8000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |  3 | Lecture 3 | Desc 3      | http://example.com/video3 |           45 |          2 |         1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  3 | Content 201 |           7 |  15000 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |  4 | Lecture 4 | Desc 4      | http://example.com/video4 |           40 |          3 |         1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
+----+-------------+-------------+--------+---------------------+---------------------+----+-----------+-------------+---------------------------+--------------+------------+-----------+---------------------+---------------------+
4 rows in set (0.02 sec)

mysql> select c.title, count(*) from contents c JOIN content_items ci   ON ci.content_id = c.id group by c.title;
+-------------+----------+
| title       | count(*) |
+-------------+----------+
| Content 101 |        2 |
| Content 102 |        1 |
| Content 201 |        1 |
+-------------+----------+
3 row

having p 214

集約した結果に対して条件指定をする場合はhaving

mysql> select c.title, count(*) from contents c JOIN content_items ci   ON ci.content_id = c.id group by c.title having count(*) > 1;
+-------------+----------+
| title       | count(*) |
+-------------+----------+
| Content 101 |        2 |
+-------------+----------+
1 row in set (0.02 sec)

サブクエリとJOIN

サブクエリ
- 駆動表に対して、全てのレコードをチェックする
  - ex) 駆動表: user 連結: todoの場合
    - user 3レコードで todo 100の場合は 3 * 100 = 300
JOIN
- 結合する場合、外部キーにインデックスを貼っていると、サブクエリより早い時がある

-- サブクエリ
SELECT id
FROM employees
WHERE department_id = (
  SELECT id FROM departments WHERE name = '営業部'
);

-- join

-- JOIN
SELECT e.id
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = '営業部';

サンプル

-- 社員テーブル
CREATE TABLE employees (
  id INT,
  name VARCHAR(100),
  department_id INT,
  salary INT
);

-- 部署テーブル
CREATE TABLE departments (
  id INT,
  name VARCHAR(100)
);

-- 平均の給料より多い従業員

SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

トランザクション p292

BEGIN TRANSACTION;

-- カッターシャツの販売単価を1000円値引き
UPDATE Shohin
SET hanbai_tanka = hanbai_tanka - 1000
WHERE shohin_mei = 'カッターシャツ';

-- Tシャツの販売単価を1000円値上げ
UPDATE Shohin
SET hanbai_tanka = hanbai_tanka + 1000
WHERE shohin_mei = 'Tシャツ';

ROLLBACK;

複雑な問い合わせ

ビュー

select文が保存されている

-- 複数のテーブルに分けたデータががあry
-- 企業テーブル
CREATE TABLE company (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  established_date DATE
);

-- 企業プロフィールテーブル
CREATE TABLE company_profile (
  company_id INT,
  ceo_name VARCHAR(255),
  employee_count INT,
  FOREIGN KEY (company_id) REFERENCES company(id)
);

-- viewの作成 select * from v_company_info;で全ての情報が取得できる
CREATE VIEW v_company_info AS
SELECT
  c.id AS company_id,
  c.name AS company_name,
  c.established_date,
  cp.ceo_name,
  cp.employee_count
FROM
  company c
JOIN
  company_profile cp ON c.id = cp.company_id;

サブクエリ p325

サブクエリのパターン
- スカラサブクエリ
  - 1つの値を返すもの
- 相関サブクエリ
スカラサブクエリ
- サブクエリの実行回数は1回

-- WHERE句に集約関数は使えない
SELECT shohin_id, shohin_mei, hanbai_tanka
FROM Shohin
WHERE hanbai_tanka > (
  SELECT AVG(hanbai_tanka) FROM Shohin
);

相関サブクエリ
- 上記との違い
  - S1.shohin_bunruiの箇所が違い
    - 駆動表の1レコードずつに対して、サブクエリも実行される
      - 駆動表が100回クエリ投げられるなら、サブクエリも100回クエリ投げられる

- 商品分類ごとに、販売単価が平均よりも大きいものだけを表示

SELECT shohin_bunrui, shohin_mei, hanbai_tanka
FROM Shohin AS S1
WHERE hanbai_tanka > (
  SELECT AVG(hanbai_tanka)
  FROM Shohin AS S2
  WHERE S1.shohin_bunrui = S2.shohin_bunrui
  GROUP BY shohin_bunrui
);

いろんな関数

nullの処理 p390

colesce nullを値変換に

mysql> select * from users;
+----+-------+-------------------+------------+---------------------+---------------------+
| id | name  | email             | company_id | created_at          | updated_at          |
+----+-------+-------------------+------------+---------------------+---------------------+
|  1 | User1 | user1@example.com |          1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  2 | User2 | user2@example.com |          1 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  3 | User3 | user3@example.com |          2 | 2025-06-09 20:47:53 | 2025-06-09 20:47:53 |
|  4 | User3 | user4@example.com |          1 | NULL                | NULL                |
+----+-------+-------------------+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select name, coalesce(created_at, '2025-06-11 20:00:00') from users;
+-------+---------------------------------------------+
| name  | coalesce(created_at, '2025-06-11 20:00:00') |
+-------+---------------------------------------------+
| User1 | 2025-06-09 20:47:53                         |
| User2 | 2025-06-09 20:47:53                         |
| User3 | 2025-06-09 20:47:53                         |
| User3 | 2025-06-11 20:00:00                         |
+-------+---------------------------------------------+
4 rows in set (

述語

IN BETWEEN EXISTS

in
between
exists

exists

-- 注文がある顧客
SELECT *
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

-- JOINでまかなえそう

SELECT *
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
);

CASE 式

case


mysql> SELECT title, amount,
    ->   CASE
    ->     WHEN amount > 12000 THEN 'high tanka'
    ->     WHEN amount > 10000 THEN 'middle'
    ->     ELSE 'cheap'
    ->   END AS price_category
    -> FROM contents;
+-------------+--------+----------------+
| title       | amount | price_category |
+-------------+--------+----------------+
| Content 101 |  10000 | cheap          |
| Content 102 |   8000 | cheap          |
| Content 201 |  15000 | high tanka     |
| NULL        |   NULL | cheap          |
+-------------+--------+----------------+
4 rows in set (0.02 sec)

集合演算

結合 JOIN ・LEFT JOIN

テーブルのイメージを
- 子供が複数の場合はcontent idが複数出る
- 起点が親でも子供でも出力レコードは変わらない
  - ⭐️1対1の場合や実質1対1の場合だけ、レコード数は同一
⭐️distinc必要な場合
⭐️group by必要な場合

-- 親起点
select * from contents c
  JOIN contents_items ci
    ON ci.content_id = c.id

-- 子供起点でのSQL
select c.id, ci.id from contents_items ci
  JOIN contents c
    ON c.id = ci.content_id;

重要

テーブル設計

テーブルの分割イメージ

パフォーマンス

マイグレーション

よく使う構文

イメージ

結合イメージ

joinテーブル連結のイメージ

compose

サンプルデータ

sqlを実際に投げる場合を想定

group by 検証

コンテンツごとに、すべてのレクチャー（ContentItems）を完了したユーザー数」を集計

考えかた

group byとhaving のサブクエリ

sql めも

sqlのパターン

シンプル

sql ミック本 SQL 基本

select の実行順番

実行計画 ⭐️

2章

定数

重複 p115

絞り込み where p115

算術・比較演算 p140

nullの扱い p140

論理演算子 p153

集約 p176

テーブルの切り分け p p195

having p 214

サブクエリとJOIN

トランザクション p292

複雑な問い合わせ

ビュー

サブクエリ p325

相関サブクエリ

いろんな関数

nullの処理 p390

述語

IN BETWEEN EXISTS

exists

CASE 式

集合演算

結合 JOIN ・LEFT JOIN