RDB, SQL

SQLの書き方 SQL書き方ドリルを通して

| by 優

 

SQL 書き方ドリル

表紙だけ見るとかなりぬるそうなのだけれど本格的！

秀逸な本？ソフトウェア。

 

  • 同梱されているソフトウェアで演習できるのですが、クオリティが高く問題数も多くて、教材としてすごく良くできてるのですよね。
  • 本屋でぱらぱら見ると『SQLを紙に書く本？』に見えてしまって魅力がわからないと思うのですが、メインはCDに収録されているソフトです。
  • 段階的に暗記せずにSQLが組めるように考えられた問題が収録されています。
    単元のはじめは補助輪つきの穴埋めのなような問題から、後半は白紙の状態で書く問題になります。

新人研修に使ってみてはどうでしょうか？

 

[amazon_link asins=’4774180661′ template=’ProductCarousel’ store=’izayoi55-22′ marketplace=’JP’ link_id=’44a5757f-6af8-49e7-a443-03dfff401c81′]

 

 

 

バックエンド = RDB

SQLやORMでのデータ取得といった部分は、正確かつ瞬殺したいよね(｡- .•)

  • 絶対にSQLやORMでのデータ取得は正確でなくてはいけない。
  • 正確な知識を持ち、精度100%のクエリが書ける自信がなければバグを生む
    ・LEFT JOIN(LEFT OUTER JOIN), JOIN(INNER JOIN)の違い
    ・LEFT JOIN(LEFT OUTER JOIN)での結合レコードのnullの現れ方
    ・LEFT JOINとON, AND, WHERE、副問い合わせでの現れ方の差異
    ・WHEREの記述箇所によりデータの絞り込みの変化
    SQLの最後のWHEREと結合時での副問い合わせでのWHEREは異なる
    ・不等号、BETWEENの理解
  • 正確であれば瞬殺できる。
    ・最短かつ脳味噌5%ぐらいの負荷でSQLが組めないとまずい
    ・バックエンドでの最重要技術

 

ここらへんの周辺情報は下記の本が詳しいです。

[amazon_link asins=’4297107171′ template=’ProductCarousel’ store=’izayoi55-22′ marketplace=’JP’ link_id=’bc7ce707-06b6-46fb-914a-1908e4e17f6c’]

 

 

この本の良いところ

プログラミング全般そうだと思うのですが、アウトプットしやすい教材が良いなと考えています。

インプットだけだとちゃんと記憶に定着しないし、自信にも繋がらない。それにITのお仕事はカンニングOKなので、何ができるかを覚えておけば後から自分のメモを見返して実装することもできるので。

  • ハンズオン形式でSQL実行が行える専用ソフトが同梱
  • Windows10, Mac対応
    Windowsだけっていうのがありがちですが、どちらにも対応！
  • 演習用ソフトがよくできている
    自分が打ち出したクエリ結果と正解での行数の際など教えてくれたりします。
  • 内容も中盤から実践的
    結合や副問い合わせの演習問題が充実している。
    入門系の本はここが物足りないのですが、実践に耐えうるコンテンツが充実しています。クロス集計とか、バッチ処理でよく行う集計処理なども収録。
  • 問題の質が良い
    初心者本は初心者向けでしかなかったりする。中級以上向けの演習が豊富です。
  • ProgateのSQLが楽しかったが物足りない
    同じような画面で、たくさんの演習ができます。

プログラムやSQLは書いてなんぼだと思うのですが、この環境構築やSQLで操作する為のデータを自分で用意する必要がない、というのが良いですね。SQL学習に専念することができます。

 

クエリの書き順(とらえ方)

上から下に向けてクエリを作成しようとするのはあまりよくないかなと。私がクエリ作成の際に記述する順番です。このように捉えています。

 

基本の捉え方

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
    ・GROUP BYでグルーピングしたものの絞り込みを行います
    ・COUNT(*) < 5
    ・MAX(Amount)など
  5. SELECT
    ・このタイミングでSELECTで指定されるカラムを抽出する
  6. ORDER BY

 

例題から考える

>テーブルProductsのPriceが1,000以下のデータをCategoryIDでグループ化し、レコード数が5未満のデータをCategoryIDで昇順に並べCategoryID、レコード数を表示しなさい。第2章 15-3

 

完成形

SELECT 
  CategoryID
, COUNT(*) AS 商品数
FROM
  Products
WHERE
  Price <= 1000
GROUP BY
  CategoryID
HAVING
  COUNT(*) < 5
ORDER BY
  CategoryID ASC
;

これをSELECTからORDER BYまで上から下の順で書いていくのはよくない。

 

こう書いていきます。

①FROM

どのテーブルを？

SELECT

FROM
  Products

 

②WHERE

どの範囲で

SELECT

FROM
  Products

WHERE
  Price <= 1000

 

③GROUP BY

どのグループでまとめるの？

SELECT

FROM
  Products

WHERE
  Price <= 1000

GROUP BY
  CategoryID

 

③’ 

SELECTにカラムを指定し抽出する

SELECT
　　CategoryID　AS 商品数
FROM
  Products

WHERE
  Price <= 1000

GROUP BY
  CategoryID

 

④ HAVING

さらに集計関数でデータを絞り込む。

GROUP BYでまとめてSELECTで抽出した後に、HAVINGを利用して上位〜のとか、件数が●未満とかの集計関数で絞り込みます。

>レコード数が5未満のデータを

SELECT 
  CategoryID
, COUNT(*) AS 商品数

FROM
  Products

WHERE
  Price <= 1000

GROUP BY
  CategoryID

HAVING
  COUNT(*) < 5

GROUP BY とSELECTでまとめれた後のものに対してHAVING句+集計関数で絞り込みをするので、HAVINGにカラムの指定は必要ないです。

 

④ ORDER BY

どのような順番で？

SELECT 
  CategoryID
, COUNT(*) AS 商品数

FROM
  Products

WHERE
  Price <= 1000

GROUP BY
  CategoryID

HAVING
  COUNT(*) < 5

ORDER BY
  CategoryID ASC

 

完成♪( ´▽｀)

SELECT 
  CategoryID
, COUNT(*) AS 商品数
FROM
  Products
WHERE
  Price <= 1000
GROUP BY
  CategoryID
HAVING
  COUNT(*) < 5
ORDER BY
  CategoryID ASC
;

 

 

 

グループと集計

 

テーブルEmployeesをBloodTypeでグループ化したとき、Heightが165以上の社員データを5件以上持つBloodType（別名は「血液型」）、データ数（別名は「データ件数」）を表示しなさい。※第2章 13-4

 

>テーブルEmployeesを

FROM
  Employees

 

>BloodTypeでグループ化したとき

FROM
  Employees

GROUP BY
  BloodType

 

>Heightが165以上の社員データを

FROM
  Employees
WHERE
  Height >= 165
GROUP BY
  BloodType

 

>5件以上持つBloodType

FROM
  Employees
WHERE
  Height >= 165
GROUP BY
  BloodType   ---①
HAVING
  COUNT(*) >= 5 ---②

 

>BloodType（別名は「血液型」）、データ数（別名は「データ件数」）を表示

SELECT
  BloodType AS 血液型,   ---①
  COUNT(*)  AS データ件数   ---②
FROM
  Employees
WHERE
  Height >= 165
GROUP BY
  BloodType   ---①
HAVING
  COUNT(*) >= 5    ---②
;

 

クロス集計

 

テーブルSalesをCustomerID（顧客ID）でグループ化したとき、SaleDate（販売日）の月を9月・10月・11月の3つに区分けして、それぞれの合計販売個数を表示しなさい。なお、抽出条件として販売日の年は2006年のみとします。※第2章 14-3

 

>テーブルSales

SELECT

FROM Sales

 

>CustomerID（顧客ID）でグループ化したとき

SELECT

CustomerID AS 顧客ID

FROM Sales

GROUP BY
  CustomerID

 

>なお、抽出条件として販売日の年は2006年のみとします。

SELECT

CustomerID AS 顧客ID

FROM Sales

WHERE
  YEAR( SaleDate ) = 2006

GROUP BY
  CustomerID

 

>SaleDate（販売日）の月を9月・10月・11月の3つに区分けして、それぞれの合計販売個数を表示

SELECT

CustomerID AS 顧客ID

, SUM( CASE
           WHEN MONTH( SaleDate ) = 9 THEN Quantity
           ELSE 0
        END
) AS "9月"
, SUM( CASE
           WHEN MONTH( SaleDate ) = 10 THEN Quantity
           ELSE 0
        END
) AS "10月"
, SUM( CASE
           WHEN MONTH( SaleDate ) = 11 THEN Quantity
           ELSE 0
        END
) AS "11月"

FROM Sales

WHERE
  YEAR( SaleDate ) = 2006

GROUP BY
  CustomerID

 

完成

SELECT
CustomerID AS 顧客ID
, SUM( CASE
           WHEN MONTH( SaleDate ) = 9 THEN Quantity
           ELSE 0
        END
) AS "9月"
, SUM( CASE
           WHEN MONTH( SaleDate ) = 10 THEN Quantity
           ELSE 0
        END
) AS "10月"
, SUM( CASE
           WHEN MONTH( SaleDate ) = 11 THEN Quantity
           ELSE 0
        END
) AS "11月"
FROM Sales
WHERE
  YEAR( SaleDate ) = 2006
GROUP BY
  CustomerID
;

 

※個数の集計でなく、レコード数を求めたい場合

- WHEN MONTH( SaleDate ) = 10 THEN Quantity
+ WHEN MONTH( SaleDate ) = 10 THEN 1

 

CASE区でELSEは必ず書く

書かないと暗黙のELSEによってその他はNULLになり、バグが発生する原因になります。

 

副問い合わせ(サブクエリ)

 

抽出？って呼ぶのかはしらない

第3章 1-1
テーブルEmployeesから各EmployeeIDについて、SalaryのAmountの最高が300,000以上のデータを取り出し、EmployeeID、EmployeeNameを表示しなさい。

 

完成系

SELECT
  EmployeeID
, EmployeeName
FROM
  Employees
WHERE
   EmployeeID IN
  (
   SELECT
     EmployeeID
   FROM
     Salary
   GROUP BY
     EmployeeID
   HAVING
     MAX(Amount) >= 300000
  )
;

ちょっと複雑？

 

単純化するよ！

SELECT
  EmployeeID
, EmployeeName
FROM
  Employees
WHERE
   EmployeeID IN　( 1, 5, 8 )
;

WHERE カラム名 IN (抽出キー)

  • WHERE EmployeeID = 1
  • WHERE EmployeeID = 5
  • WHERE EmployeeID = 8

とやっていることは同じなのですが、サブクエリを使ってまとめてあげると高速化するのでWHERE 〜 IN ()の形にする。IN ()に入る値をSELECT〜WHEREで条件をつけて抽出しています。

 

外部キーから値を参照する

第3章 1-2
テーブルSalesのQuantityが100以上のレコードを取り出し、SaleID、Quantity、CustomerID、CustomerNameを表示しなさい。

SELECT
  SaleID
, Quantity
, CustomerID
, (
   SELECT
     CustomerName
   FROM
     Customers
   WHERE
    Sales.CustomerID = CustomerID
  ) AS 顧客名
FROM
  Sales
WHERE
  Quantity >= 100

 

単純化

SELECT
  SaleID
, Quantity
, CustomerID
, CustomerName AS 顧客名 ←●単純化
FROM
  Sales
WHERE
  Quantity >= 100

願望としてはCustomerNameをぱぱっと取得したいけれど、Salesテーブルにそんなものはないからできない。

外部キーとしてCustomerIDは持っているので、SalesテーブルのCustomerIDとCustomersテーブルのCustomerIDが共通するCustomerNameを引っ張ってくる。

, (
   SELECT
     CustomerName
   FROM
     Customers
   WHERE
    Sales.CustomerID = CustomerID
  ) AS 顧客名

それがこれ。

 

組み合わせて完成系になる

SELECT
  SaleID
, Quantity
, CustomerID
, (
   SELECT
     CustomerName
   FROM
     Customers
   WHERE
    Sales.CustomerID = CustomerID
  ) AS 顧客名
FROM
  Sales
WHERE
  Quantity >= 100

 

商品名もProductsテーブルを参照して取得したくなってきた！

SELECT
  SaleID
, Quantity
, CustomerID
, (
   SELECT
     CustomerName
   FROM
     Customers
   WHERE
     CustomerID = Sales.CustomerID
  ) AS 顧客名
, ( SELECT
      ProductName
    FROM
      Products
    WHERE
      ProductID = Sales.ProductID
) AS 商品名
FROM
  Sales
WHERE
  Quantity >= 100
;

こうなる。

複雑に見えても分解するとシンプルなのだ。

 

JOIN(INNER JOIN)内部結合とLEFT JOIN(LEFT OUTER JOIN)外部結合の違い

外部サイトですが、下記がまとまっていてわかりやすいです！

 

内部結合 JOIN(INNER JOIN)

 

 

基本形

SELECT
  EmployeeName
, PayDate
, Amount
FROM
  Salary AS A
    JOIN
  Employees AS B
    ON A.EmployeeID = B.EmployeeID
ORDER BY
  A.EmployeeID ASC
;

 

内部結合のとらえかた

SELECT
  A.ProductID
, ProductName
, SUM(A.Quantity) AS 数量合計
FROM
  Sales AS A
   JOIN
  Products AS B
    ON A.ProductID = B.ProductID
GROUP BY
  A.ProductID
, B.ProductID
HAVING
 SUM(A.Quantity) >= 300
;

 

 

①テーブルとテーブルの結合、結合条件

SELECT

FROM
  Sales AS A
   JOIN
  Products AS B
    ON A.ProductID = B.ProductID

 

②GROUP BY

SELECT

  EmployeeName
, PayDate
, Amount

FROM
  Salary AS A
    JOIN
  Employees AS B
    ON A.EmployeeID = B.EmployeeID

GROUP BY
  A.ProductID
, B.ProductID

 

③HAVING

SELECT

  EmployeeName
, PayDate
, Amount

FROM
  Salary AS A
    JOIN
  Employees AS B
    ON A.EmployeeID = B.EmployeeID

GROUP BY
  A.ProductID
, B.ProductID

HAVING
 SUM(A.Quantity) >= 300

 

④SELECT

SELECT
  A.ProductID
, ProductName
, SUM(A.Quantity) AS 数量合計

FROM
  Sales AS A
   JOIN
  Products AS B
    ON A.ProductID = B.ProductID

GROUP BY
  A.ProductID
, B.ProductID

HAVING
 SUM(A.Quantity) >= 300

これで完成！

 

 

複雑系？

SELECT
  A.Quantity
, B.CustomerName
, C.ProductName
, D.EmployeeName
FROM
  Sales AS A
    JOIN
  Customers AS B
    ON A.CustomerID = B.CustomerID
      JOIN
   Products  AS C
      ON A.ProductID = C.ProductID
        JOIN
       Employees AS D
         ON A.EmployeeID = D. EmployeeID
WHERE
  A.Quantity >= 200

やっていることは基本形で結合するテーブルの数が増えただけ。

注意したいのは、このWHEREの意味

  • 結合した検索結果に対して最後に絞りこんでいること。
  • 条件指定しての結合は下記のようにする
    ON A.EmployeeID = D. EmployeeID
    AND D. EmployeeID > 1000

バグの元になるので注意したい。

 

複雑系を副問い合わせ(サブクエリ)で表現

SELECT
  Quantity
, (
  SELECT
    CustomerName
  FROM
    Customers
  WHERE
    Sales.CustomerID = Customers.CustomerID
) AS CustomerName
, (
  SELECT
    ProductName
  FROM
    Products
  WHERE
    Sales.ProductID = Products.ProductID
) AS ProductName
, (
  SELECT
    EmployeeName
  FROM
    Employees
  WHERE
    Sales.EmployeeID = Employees.EmployeeID
) AS EmployeeName
FROM
   Sales
WHERE
  Sales.Quantity >= 200
;

 

第3章 2-5
テーブルCustomers、Prefecturals、CustomerClassesをWHERE句を使って結合し、PrefecturalID昇順で並べ、CustomerName、PrefecturalName、CustomerClassNameを表示しなさい。

 

WHEREで結合してみた

SELECT
  A.CustomerName
, B.PrefecturalName
, C.CustomerClassName
FROM
  Customers AS A
, Prefecturals AS B
, CustomerClasses AS C
WHERE
  A.PrefecturalID = B.PrefecturalID
    AND
      A.CustomerClassID = C.CustomerClassID
ORDER BY
  B.PrefecturalID ASC
;

 

結合でも表現してみた

SELECT
  A.CustomerName
, B.PrefecturalName
, C.CustomerClassName
FROM
  Customers AS A
JOIN
  Prefecturals AS B
    ON A.PrefecturalID = B.PrefecturalID
      JOIN
        CustomerClasses AS C
          ON A.CustomerClassID = C.CustomerClassID
ORDER BY
  B.PrefecturalID ASC
;

JOINはコストがかかるので、WHEREの方が良いのでしょうね。

 

外部結合

外部結合させると、対応していないNULLのレコードが出る。

  • 連結した時に結合となるキーが存在しないところはNULLになる特徴がある
  • 外部結合で生じたNULLに対するカラムに対して値を操作できる
  • NULLで処理すると計算がおかしくなる、だから0を設定する。

 

第3章 4-2

テーブルEmployees、Salesを外部結合し、EmployeeIDごとのSalesレコード数を求めてEmployeeID、社員名と販売件数を表示しなさい。ただし、Salesデータが存在しない場合は0を表示しなさい。

SELECT
  A.EmployeeID
  ,MAX(A.EmployeeName) AS 社員名
  ,SUM(
             CASE
                 WHEN B.EmployeeID IS NULL THEN 0
                 ELSE 1
             END
  )AS 販売件数
FROM
  Employees AS A
    LEFT JOIN
      Sales AS B
        ON A.EmployeeID = B.EmployeeID
GROUP BY
   A.EmployeeID
;

MAX(A.EmployeeName)

何で名前なのにMAX()？

→GROUP BYした場合のSELECTでのカラムは、

  1. GROUP BYのキーに指定したカラム
  2. 集合関数のカラム

この2つだからです。string型だから意味がないけれど、表示させる為にMAX()で行っています。

 

 

階層のある外部結合

 

第4章 5-5
2007年8月25日までにCustomerClassID=1の顧客に対して
売上のある従業員の2007年8月25日支払分給与を10％値上げしなさい。

UPDATE
  Salary
SET
  Amount = Amount * 1.1
WHERE
  PayDate = '2007-08-25'
  AND
  EmployeeID IN (
    SELECT
      EmployeeID
    FROM
      Sales AS S
    JOIN
      Customers AS C
    ON S.CustomerID = C.CustomerID
    JOIN
      CustomerClasses AS CL
    ON C.CustomerClassID = CL.CustomerClassID
    WHERE
      SaleDate < '2007-08-25'
      AND
      C.CustomerClassID = 1
  )
;

 

 

 

 

結合条件の絞り込み

ONの後にANDで指定します。

SELECT
  MIN(A.EmployeeName) AS EmployeeName
, SUM(
       CASE
           WHEN B.Amount IS NULL THEN 0
           ELSE Amount
       END
)AS 支給額
FROM
  Employees AS A
    LEFT OUTER JOIN
      Salary AS B
        ON A.EmployeeID = B.EmployeeID
          AND B.PayDate = '2007-02-25' ←●これ
GROUP BY
  A.EmployeeID
;

 

GROUP BYの癖

SELECT
  C.CustomerClassID
, MAX(A.Quantity) AS 最大数量
, MIN(C.CustomerClassName) AS 顧客クラス名
FROM
  Sales AS A
  JOIN
    Customers AS B
    ON A.CustomerID = B.CustomerID
  JOIN
    CustomerClasses AS C
  ON B.CustomerClassID = C.CustomerClassID
GROUP BY
  C.CustomerClassID
;

GROUP BYを利用した場合にSELECTで指定できるカラムは

  1. GROUP BYでグループ化に利用したキーとなるカラム
  2. SUM(), COUNT(), MAX()などの集合関数が適用されたカラム

この２種類。

上記の『MIN(C.CustomerClassName) AS 顧客クラス名』は表示させる為に、文字列に対してMIN()をかけています。表示させたい為でありMIN()自体に意味はないです。

 

自己結合

下記サイトがわかりやすい。

 

カテゴリー同士を組み合わせて、カテゴリー名をそれぞれ表示しなさい（別名は「カテゴリー1」「カテゴリー2」）。第3章 5-4

SELECT
  c1.CategoryName AS カテゴリ名1
, c2.CategoryName AS カテゴリ名2
FROM
  Categories AS c1
    JOIN
  Categories AS c2
    ON c1.CategoryID < c2.CategoryID
;

 

この不等号は何？

ON c1.CategoryID < c2.CategoryID
  • 一度出現したレコードを２度と出現させない為のもの
  • <>にすると同じ組み合わせが何度も出現する

 

 

相関副問い合わせ(相関サブクエリ)

SQLの中でのループ。

 

WHERE EXIXT()句は下記がわかりやすい。

 

相関サブクエリで利用される WHERE EXISTS (SELECT 〜)は

  • WHERE <カラム名> IN (要素A, 要素B, …)
  • JOIN <テーブル名> ON <結合条件>

これらでも表現できる。

 

WHERE EXISTS()

第3章 6-2
テーブルProductsから、テーブルSalesにあるProductIDとそのProductNameを表示しなさい

SELECT
  A.ProductID
, A.ProductName
FROM
  Products AS A
WHERE
  EXISTS
  (
   SELECT
     'X'
   FROM
     Sales AS B
   WHERE
     A.ProductID = B.ProductID
  )
;

サブクエリ内でASのスコープは別れている。

 

WHERE NOT EXISTS()

第3章 6-3
テーブルProductsから、テーブルSalesにないProductIDとそのProductNameを表示しなさい

SELECT
  A.ProductID
, A.ProductName
FROM
  Products AS A
WHERE
  NOT EXISTS
  (
   SELECT
     'X'
   FROM
     Sales AS B
   WHERE
     A.ProductID =  B.ProductID
  )
;

 

 

 

 

第3章 6-1

テーブルSalesでProductIDごとのQuantityの最大値を求め、ProductID、ProductName、最大QuantityをProductID昇順で表示しなさい。

SELECT DISTINCT
  A.ProductID
, ProductName
, MAX(Quantity) AS Quantity
FROM
  Sales AS A 
    JOIN
  Products AS B 
    ON A.ProductID = B.ProductID
WHERE
  A.Quantity = 
  (
   SELECT
     MAX( Quantity )
   FROM
     Sales AS C
   WHERE
    A.SaleID = C.SaleID
  )
GROUP BY
  A.ProductID
ORDER BY
  A.ProductID ASC
;

 

ポイント

  A.Quantity =
  (
   SELECT
     MAX( Quantity )
   FROM
     Sales AS C
   WHERE
    A.SaleID = C.SaleID
  )

A.Quantity >= (サブクエリ)

これでMAX(Quantity)を抽出している。

 

重要！ここで外側と内側を繋げてあげる

   WHERE
    A.SaleID = C.SaleID

これを指定しないと、サブクエリ部分が意味をなさず死ぬ。

 

 

INとEXISTSの使い分け

 

  • 従属側テーブルの選択性が高い場合
    →IN + サブクエリ
  • 従属側テーブルの選択性が低い場合
    →EXISTS + サブクエリ
  • 主テーブルも従属側テーブルも選択性が低い場合
    →EXISTS + サブクエリ

 

WHERE EXISTS

SELECT
  *
FROM
  Members
WHERE
  EXISTS (
    SELECT
      'X' ←なんでも良い
    FROM
      Monsters
    WHERE
      Members.Tokugi = Monsters.Tokugi
  )
;

 

WHERE
  EXISTS (
・・
    WHERE
      Members.Tokugi = Monsters.Tokugi
  • Members.Tokugiで有効になっているTokugiがあるレコードを抽出する
  • Members.TokugiにINDEXを張る

 

 

WHERE <カラム名> IN ()

SELECT
  *
FROM
  Members
WHERE
  Member_id IN (
    SELECT
      Member_id
    FROM
      Monsters
    WHERE
      Monsters.SkillName = Members.SkillName
  )
;
  • INに入るMember_idを集める形。
  • Monsters.SkillNameにINDEXを張ること

 

 

UNNION

複数の結果をまとめるのに使う

 

基本形

SELECT
  EmployeeID AS ID
, EmployeeName AS 名前
FROM
  Employees
UNION
SELECT
  EmployeeID AS ID
, EmployeeName AS 名前
FROM
  Employees
ORDER BY
  ID
;

異なるテーブルで、IDと名前でまとめる

 

複雑系？　異なる期間複数をまとめる

第3章 8-4
テーブルSalesの中から、SaleDateが’2006-10-01′ と ‘2006-12-31’の間で
Quantityが10以上のデータと、SaleDateが’2007-01-01’ と ‘2007-03-31’の間でQuantityが10以上のデータと、SaleDateが’2007-04-01’ と ‘2007-06-30’の間でQuantityが10以上のデータをUNIONで1つにまとめなさい。

SELECT
  CustomerID
, ProductID
FROM
  Sales
WHERE
  SaleDate BETWEEN  '2006-10-01' AND '2006-12-31'
    AND
      Quantity >= 10
UNION
SELECT
  CustomerID
, ProductID
FROM
  Sales
WHERE
  SaleDate BETWEEN  '2007-01-01' AND '2007-03-31'
    AND
      Quantity >= 10
UNION
SELECT
  CustomerID
, ProductID
FROM
  Sales
WHERE
  SaleDate BETWEEN  '2007-04-01' AND '2007-06-30'
    AND
      Quantity >= 10
ORDER BY
  CustomerID
, ProductID
;

 

 

 

INTERSECT

重複を見つける

 

第3章 9-4
テーブルSalesの中から、
SaleDateが’2006-10-01′ と ‘2006-12-31’の間でQuantityが10以上のデータと、
SaleDateが’2007-01-01’ と ‘2007-03-31’の間でQuantityが10以上のデータと、
SaleDateが’2007-04-01’ と ‘2007-06-30’の間でQuantityが10以上のデータを
INTERSECTで1つにまとめなさい。
CustomerIDとProductIDを昇順に並べて表示しなさい

SELECT
  CustomerID
, ProductID
FROM
  Sales
WHERE
  SaleDate BETWEEN '2006-10-01' AND '2006-12-31'
    AND
      Quantity >= 10
INTERSECT
SELECT
  CustomerID
, ProductID
FROM
  Sales
WHERE
  SaleDate BETWEEN '2007-01-01' AND '2007-03-31'
    AND
      Quantity >= 10
INTERSECT
SELECT
  CustomerID
, ProductID
FROM
  Sales
WHERE
  SaleDate BETWEEN '2007-04-01' AND '2007-06-30'
    AND
      Quantity >= 10
ORDER BY
  CustomerID
, ProductID
;

 

 

EXCEPT

差集合

第3章 10-4
テーブルSalesの中の、
CustomerClassID=1（法人）の顧客でQuantityが100以上のデータのProductIDから、
CustomerClassID=2（個人）の顧客でQuantityが10以上のデータのProductIDを差し引いた
残りのProductIDをその順に表示しなさい

SELECT
  ProductID
FROM
  Sales
JOIN
  Customers
ON
  Sales.CustomerID = Customers.CustomerID
WHERE
  Customers.CustomerClassID = 1
    AND
      Quantity >= 100
EXCEPT
SELECT
  ProductID
FROM
  Sales
JOIN
  Customers
ON
  Sales.CustomerID = Customers.CustomerID
WHERE
  Customers.CustomerClassID = 2
    AND
      Quantity >= 10
ORDER BY
  ProductID
;

 

 

 

副問い合わせ(サブクエリ)を利用したINSERT

 

SELECTした結果をINSERTできます。

INSERT INTO 
  users AS U1
(
  name
)
SELECT
  customer_name AS name
From
  customers AS C1
WHERE
  C1.age >　19
;

 

カラム名が異なってもASで設定することができる

カラム名をINSERT先テーブルのカラムに合わせることで、データ挿入が可能です。

 

副問い合わせと更新

 

第4章 6-1
顧客テーブルの住所の前に、対応する都道府県の都道府県名を連結しなさい。

UPDATE
  Customers
SET
  Address
  =
 (
  SELECT
    PrefecturalName
  FROM
    Prefecturals
  WHERE
    Customers.PrefecturalID = Prefecturals.PrefecturalID
  )
  || Customers.Address

WHERE
  EXISTS
  (
  SELECT
    'X'
  FROM
    Prefecturals
  WHERE
    Customers.PrefecturalID = Prefecturals. PrefecturalID
  )
;

 

単純化するとこう

UPDATE
  A
SET
  A_Culum
  =
  (
    SELECT
      B_Culum
    FROM
      B
    WHERE
      A.B_ID = B.B_ID
  )
WHERE
  EXISTS
  (
   SELECT
     'X' ←なんでも良い
    FROM
      B
    WHERE
      A.B_ID = B.B_ID
  )
;

外側と内側で条件を合わせることで連結ができる

 

 

UPDATE
  Salary
SET
  Amount
  = Amount +
 (
  SELECT
    SUM( Sales.Quantity * Products.Price ) * 0.03
  FROM
    Sales
      JOIN
    Products
      ON Sales.ProductID = Products.ProductID
  WHERE
    Sales.SaleDate < '2007-08-25'
    AND
    Salary.EmployeeID = Sales.EmployeeID
 )
WHERE
  Salary.PayDate = '2007-08-25'
  AND
  EXISTS
 (
  SELECT
    'X'
  FROM
    Sales
  WHERE
    Sales.SaleDate < '2007-08-25'
    AND
    Salary.EmployeeID = Sales.EmployeeID
 )
;

 

SUM()を利用した場合

第4章 6-5

販売個数の累計が500個以上の商品について、
ProductsテーブルのProductNameを次の文字列連結を使って修正しなさい。
『’n個爆売『<商品名>』なお、
n個の「n」には実際の販売個数の累計値をセットしなさい。

UPDATE
  Products
SET
  ProductName
  =
  (
  SELECT
    SUM(Quantity)
  FROM
    Sales
  WHERE
    Products.ProductID = Sales.ProductID
    )
    || '個爆売『' || ProductName || '』'
WHERE
  (
  SELECT
    SUM(Quantity)
  FROM
    Sales
  WHERE
    Products.ProductID = Sales.ProductID
    ) >= 500
;

これも外と内を合わせる。

 

 

更新と場合わけ

 

第4章 4-5
テーブルProductsで、CategoryID=7の場合、
現在のPriceが2000以上の場合は20%値下げ、
現在のPriceが1000以上の場合は10%値下げに変更しなさい。
現在の値が1000未満の場合は変更しないようにしなさい。

UPDATE
  Products
SET
  Price =
     CASE
       WHEN Price >= 2000 THEN Price - (Price * 0.2)
       WHEN Price >= 1000 THEN Price - (Price * 0.1)
       WHEN Price < 1000 THEN Price
       ELSE Price END
WHERE
  CategoryID = 7
;

 

第4章 5-1

一度も売上のない商品を3％値下げしなさい。

UPDATE
  Products
SET
  Price =  Price * 0.97
WHERE
  productId NOT IN
 (
  SELECT
    ProductId
  FROM
    Sales
 )
;

 

 

文字の追加

 

第4章 3-4
テーブルDepartmentsのDepartmentNameの末尾に’部’を付けなさい。

UPDATE
  Departments
SET
  DepartmentName = DepartmentName || '部'
;

 

第4章 3-5
テーブルCustomers、CustomerNameにCustomerClassID=1の場合、
‘御中’、2の場合’様’を追加しなさい。

UPDATE
  Customers
SET
  CustomerName =
    CASE
      WHEN CustomerClassID = 1 THEN CustomerName || '御中'
      WHEN CustomerClassID = 2 THEN  CustomerName || '様'
      ELSE NULL
    END
;

 

INDEXの貼りどころ

  • WHERE A.列名 = B.列名
    A.カラムにINDEXを貼ります。
  • 結合でのONで利用される列名
    ON A.A_ID = B.A_ID
  • ORDER BY 列名
    ORDER BY でソートする列名にINDEXを貼りましょう
  • NULLが多い列名
  • LIKEで文字列が先頭の時
    LIKE 列名 ‘名前%’
  • MIN(), MAX()で利用される列名

 

INDEXを貼らない方が良い場合

  1. 更新・追加・削除の多いカラム
  2. 将来的にレコード数が500にも増えないテーブル
  3. LIKE検索に利用されるカラム
    LIKE 列名 ‘%名前’

 

 

JOINは使わない方が良い？

  • 「JOINを使うとクエリが複雑になる(？)んだよ！禁止だ！禁止！」
  • 「デッドロック！デッドロック！誰だJOIN使ったやつ＞＜」

という声があります。

JOINを使わないでどうビジネスを表現するかというと、プログラムのループ処理といったことになる。SQLで実装すれば数行でも、プログラムで実装すると数百行に、ループのループにループさせてと複雑化してめちゃめちゃ遅くなります。

データをとりまとめて操作することはSQLが得意です。データの集計や結合し抽出するといった処理はSQLで行うようにします。

JOINのメリット

  • JOINを使うとプログラムが単純化するので、保守開発工数が低く済む
  • サーバ負荷も減るので、サーバコストが安くなる。
    →分散化したり、スケールアウトするタイミングが先になる。

 

デッドロックを回避するには？

JOINの弊害。うまく回避して賢くJOINを利用しよ〜！

  • テーブルのロックの順番を揃える
  • IDをソートしてから更新する
  • 更新する範囲をSELECT 〜 FOR UPDATEでMySQLに教えてあげる
    ロック範囲をせまくすることができる

 

RDBか、NoSQLか？

俺か、俺以外か———。

基本はRDBで、データの用途でNoSQLに向いているものに持たせる。

RDBが向いている

  • トランザクションを利用した課金、注文処理
  • テーブル間のリレーションを利用したデータ処理

 

NoSQLが向いている

1CPU, オンメモリ。揮発性(保存もできるけど)。MySQLよりだいたい4倍早い。

  • ユーザのアクションに対して1レコードだけ参照するシステム
  • ログ
  • オブジェクトキャッシュ
  • ランキング
    Redisによる。
  • 一時データ

 

ORMかSQLか

ORMは1+N(N+1)問題とまではいかずも、細かなクエリが生成されがちなので性能問題に陥る場合がある。

→ストアドプロシージャを利用する。
→MySQLではCASE式が速い。ストアドアクションがプリコンパイルされない為。

ソシャゲのように同時処理で処理速度が重要なケース

  • SQLで処理
  • ストアドプロシージャの活用

 

同時処理、処理速度がそこまで求められない

オブジェクト指向と相性の良いORM

→

  • 生成されるSQLをモニタリング
    →細かなクエリが複数生まれていないか
  • 1+Nは許さない
    ぐるぐるになっていないかチェック。
    Laravelならイーガーローディングのwith()で対応する
    →with()を利用することで、1+Nのぐるぐる系からWHERE カラム IN ()の形に修正される

 

@see

 

 

大量のレコードを処理したい

GCP Bigquery

  • Googleが１０００億レコードフルスキャンで２０秒と宣伝している。
  • 更新と削除がない場合のみ利用

 

@see

 

 

 

[amazon_link asins=’4774180661′ template=’ProductCarousel’ store=’izayoi55-22′ marketplace=’JP’ link_id=’44a5757f-6af8-49e7-a443-03dfff401c81′]

 

 

[amazon_link asins=’4297107171′ template=’ProductCarousel’ store=’izayoi55-22′ marketplace=’JP’ link_id=’bc7ce707-06b6-46fb-914a-1908e4e17f6c’]

 

 

[amazon_link asins=’4798110663′ template=’ProductCarousel’ store=’izayoi55-22′ marketplace=’JP’ link_id=’a7cd9207-8819-4069-8d4b-edcf29626e8b’]

 

 

[amazon_link asins=’4873115892,4297104083,4798124702′ template=’ProductCarousel’ store=’izayoi55-22′ marketplace=’JP’ link_id=’a4e1dca0-3385-4804-9ee5-1069ccaee503′]

 

 

 

関連記事 - More from my site -

“SQLの書き方 SQL書き方ドリルを通して”への2件のコメント

    1. >生島様

      有難うございます！
      勉強させて頂いている著者さんにコメント貰えるとは思っておらず、
      嬉しく感じています。
      参考の連載も大変興味深く、
      これからお客様への提案や実装に活かさせて頂きますね〜！

      返信

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）