DataBase vol.5

Study DB
  • グループ単位で集計する
  • グループ単位で集計した結果を絞り込む(1)
  • グループ単位で集計した結果を絞り込む(2)
  • 並び替えを行う

グループ単位で集計する

SELECT
        グループ化列名
        ,集合関数
FROM
        テーブル名
GROUP BY
      グループ化列名
グループ化を行う場合、選択リストで許可されるのは
  • グループ化のキーとなる列名か、
  • あるいは集合関数のみ。

→個別の情報は分からない。MySQL以外はエラーになる。
（現段階では個別情報を取り出せない。より学習が必要。）

問題11-3

テーブルSalesをCustomerID、ProductIDでグループ化し、
CustomerID、ProductIDごとのQuantity合計を求めなさい。

SELECT
        CustomerID AS "顧客ID"
        ,ProductID AS "商品ID"
        ,SUM(Quantity) AS "数量"
FROM
        Sales
GROUP BY
      CustomerID
      ,ProductID
;

問題11-2

テーブルSalaryをEmployeeIDでグループ化し、EmployeeIDごとのAmount合計を求めなさい。

SELECT
        EmployeeID AS "社員ID"
        ,SUM(Amount) AS "合計"
FROM
        Salary
GROUP BY
      EmployeeID
;

グループ単位で集計した結果を絞り込む(1)

SELECT
        グループ化列数
        ,集合関数
FROM
        テーブル名
GROUP BY
        グループ化列名
HAVING
        条件
;
  • HAVINGはGROUP BYがあるときのみ使える。
  • GROUP BYされた結果を絞り込む。

問題12-2

テーブルCustomersをPrefecturlIDでグループ化し、
複数レコードを持つPrefecturalIDを表示しなさい。

SELECT
        PrefecturalID AS "県ID"
        ,COUNT(*) AS "顧客数"
FROM
        Customers
GROUP BY
        PrefecturalID
HAVING
        COUNT(*) > 1
;

問題12-5

テーブルSalesをProductIDでグループ化したとき、
Quantityの合計が100以上かつ200以下のProductID、Quantity合計を表示しなさい。

SELECT
        ProductID AS "商品ID"
        ,SUM(Quantity) AS "合計"
FROM
        Sales
GROUP BY
        ProductID
HAVING
        SUM(Quantity) BETWEEN 100 AND 200
;

グループ単位で集計した結果を絞り込む(2)

WHEREとHAVINGの組み合わせ

SELECT
        グループ化列名
        ,集合関数
FROM
        テーブル名
WHERE
        グループ化される前の個別のレコードに対する条件
GROUP BY
        グループ化列名
HAVING
        グループ化した結果に対する条件
;
  • WHEREで先に絞り込まれて、HAVINGで後に絞り込まれる

問題13-2

テーブルSalaryからレコード数が12以上、EmployeeIDが20以上の
EmployeeIDとレコード数を取り出しなさい。

SELECT
        EmployeeID AS "社員ID"
        ,COUNT(*) AS "支給回数"
FROM
        Salary
WHERE
        EmployeeID >= 20
GROUP BY
        EmployeeID
HAVING
        COUNT(*) >= 12
;

OBTさんの特別問題
社員IDを10から100までの人でサラリー平均支給額が1000以上の人の社員IDを出す。

SELECT
        EmployeeID AS "社員ID"
        ,COUNT(Amount) AS "支給額"
FROM
        Salary
WHERE
        EmployeeID >= 10
        AND
        EmployeeID <= 100
GROUP BY
        EmployeeID
HAVING
        AVG(Amount) >= 1000
;

並び替えを行う

ORDER BYを使う

SELECT
        列名
FROM
        テーブル名
ORDER BY
        列名
;
  • どんなときでも使用することがある
ASC 昇順(デフォルト)
DESC 降順

問題15-3

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

SELECT
        CategoryID AS "カテゴリID"
        ,COUNT(*) AS "商品数"
FROM
        Products
WHERE
        Price <= 1000
GROUP BY
        CategoryID
HAVING
        COUNT(*) < 5
ORDER BY
        CategoryID
;