Hatena::ブログ(Diary)

crazyuplog このページをアンテナに追加 RSSフィード

2009-05-25

DataBase vol.14

  • 復習

WHEREとHAVING

  • HAVINGはSUMなどが付いている場合が多い。
    • GROUP BYでだいたい集約されている。

結合とは?

products

1バナナ
2みかん
3マンゴー

sales

salesidproduct_idquantity
135
2110
328
4215
5320
611
  • これを1対nという。

products

1バナナ
2みかん
3マンゴー

products_additional_info

idproduct_idcategory_id
113
223
333
  • これを1対1という。

communities

1moto
2nyo

com_groups

11dino
21はんばーぐ

members

1Aさん
2Bさん
3Cさん
4Dさん
  • これをn対nという。
    • 多数対多数
    • これらは1対nに直せる。

UPDATE文等を扱う時は・・・

トランザクションを忘れない。

  • BEGIN;
  • COMMIT;
  • ROLLBACK;

InnoDBのときしか使えない。(MySQL)

TimeStamp

'2007' <= SaleDate×数値型で解釈される(マイクロ秒で比較する)
'2007-01' <= SaleDate-があるので一応文字列で比較される?
'2007-01-01' <= SaleDate
'2007-1-1' <= SaleDate
2007-1-1 <= SaleDate×
SaleDate LIKE('2007%')×MySQLではWarningが出ながらも表示

2009-05-22

DataBase vol.13

  • 復習テスト

問題1

社員テーブル(Employees)と、売上テーブル(Sales)から社員の2007年分の売上総数を

その社員ごとのIDと名前とともに表示しなさい。

SELECT
        e.EmployeeID,
        e.EmployeeName,
        COUNT(Quantity)
FROM
        Employees AS e,
        Sales AS s
WHERE
        e.EmployeeID = s.EmployeeID
GROUP BY
        s.EmployeeID
;
回答
SELECT
        e.EmployeeID,
        e.EmployeeName,
        SUM(s.Quantity)
FROM
        Sales AS s,
        Employees AS e
WHERE
        s.EmployeeID = e.EmployeeID AND
        s.SaleDate >= '2007-01-01' AND s.SaleDate < '2008-01-01'
GROUP BY
        s.EmployeeID
;
  • まず、なんでCOUNTしているのだろう。自分は。
  • あと、期間が書かれていない。

問題2

部署移動が1回以上あった社員をIDと名前とともに表示しなさい。

SELECT
        e.EmployeeID AS "社員ID",
        e.EmployeeName AS "社員名",
        COUNT(b.DepartmentID) AS "移動回数"
FROM
        Employees AS e
         JOIN
        BelongTo AS b
         ON (e.EmployeeID = b.EmployeeID)
         JOIN
        Departments AS d
         ON (b.DepartmentID=d.DepartmentID)
GROUP BY
        b.DepartmentID
HAVING
        COUNT(b.DepartmentID) > 1
;
回答
SELECT
        e.EmployeeID,
        e.EmployeeName
FROM
        Employees e,
        BelongTo b,
        Departments d
WHERE
        e.EmployeeID = b.EmployeeID
        AND
        b.DepartmentID = d.DepartmentID
GROUP BY
        e.EmployeeID
HAVING
        COUNT(e.EmployeeID) > 1
ORDER BY
        e.EmployeeID
;

問題3

在職中の社員について2006年度に支払われた給料合計を社員毎に表示しなさい。

ただし2006年度に在籍していなかった社員の給料合計は0として表示する。

SELECT
        e.EmployeeName AS "社員名",
        SUM(
          CASE
              WHEN s.EmployeeID IS NULL THEN 0
              ELSE s.Amount
          END
         ) AS "給料合計"
FROM
        Employees AS e
         JOIN
        Salary AS s
         ON (e.EmployeeID=s.EmployeeID)
WHERE
        s.PayDate BETWEEN "2006-01-01" AND "2007-01-01"
GROUP BY
        e.EmployeeID
;
回答
SELECT
        e.EmployeeID,
        e.EmployeeName AS "社員名",
        SUM(
          CASE
              WHEN s.Amount IS NULL THEN 0
              ELSE s.Amount
          END
         ) AS "給料合計"
FROM
        Employees AS e
         LEFT OUTER JOIN
        Salary AS s
         ON e.EmployeeID=s.EmployeeID AND '2006-01-01' <= s.PayDate AND s.PayDate < '2007-01-01'
GROUP BY
        e.EmployeeID
;

問題4

2007年度顧客別売上ランキングを表示しなさい。

ただし、表示の際は企業種別(個人/法人)と企業名をつなげて表示しなさい。

(例:法人ディノ) ※売上のない顧客は対象外とする。

SELECT
       SUM(p.Price) AS "売上",
       CONCAT(cc.CustomerClassName,c.CustomerName) AS "お客様名"
FROM
       Sales AS s,
       Customers AS c,
       CustomerClasses AS cc,
       Products AS p
WHERE
       s.CustomerID = c.CustomerID
       AND
       c.CustomerClassID = cc.CustomerClassID
       AND
       s.ProductID = p.ProductID
       AND
       s.SaleDate BETWEEN "2007-01-01" AND "2008-01-01"
GROUP BY
       s.ProductID
ORDER BY
       SUM(p.Price) DESC
;
回答
SELECT
       SUM(p.Price*s.Quantity) AS "売上",
       CONCAT(cc.CustomerClassName,c.CustomerName) AS "お客様名"
FROM
       Sales AS s,
       Customers AS c,
       CustomerClasses AS cc,
       Products AS p
WHERE
       s.CustomerID = c.CustomerID
       AND
       c.CustomerClassID = cc.CustomerClassID
       AND
       s.ProductID = p.ProductID
       AND
       s.SaleDate BETWEEN "2007-01-01" AND "2008-01-01"
GROUP BY
       s.CustomerID
ORDER BY
       SUM(p.Price*s.Quantity) DESC
;
  • 数量が入っていない。
  • やけに安いからおかしいと思ったらそりゃそうだ。

問題5

個人事業主の顧客向けに販売された商品について、商品カテゴリ別平均売上を高い順に表示せよ。

ただし、売上が計上されていない場合は0として扱う。

また、商品カテゴリ別平均売上の小数点以下は切り上げとする。

回答
SELECT
        c.CategoryID,
        c.CategoryName,
        CEIL(AVG(s.Quantity*p.Price)) AS '商品売上平均'
FROM
        Categories c
          LEFT JOIN
        Products p ON c.CategoryID = p.CategoryID
          LEFT JOIN
        Sales s ON p.ProductID = s.ProductID
GROUP BY
        c.CategoryID
;

2009-05-21

DataBase vol.12

  • 複数のテーブル結合を行う
  • 自己結合を使う

複数のテーブル結合を行う

問題3-1

テーブルSales、Products、Categoriesを連結し、SalesをCategoryIDでグループ化、

CategoryID,CategoryName,Quantityの合計を表示しなさい。

SELECT
        p.CategoryID
        ,c.CategoryName AS "カテゴリ名"
        ,SUM(s.Quantity) AS "数量合計"
FROM
        Sales AS s
           JOIN
        Products AS p
           ON s.ProductID = p.ProductID
           JOIN
        Categories AS c
           ON c.CategoryID = p.CategoryID
GROUP BY
        c.CategoryID
;        
問題4-1

テーブルCustomers、Salesを外部結合してCustomerIDごとのQuantity合計を求めて

CustomerNameと合計を表示しなさい。ただし、Salesデータが存在しないCustomerIDの合計は0を表示しなさい。

SELECT
        c.CustomerName,
        SUM(
            CASE
                WHEN s.CustomerID IS NULL THEN 0
                ELSE s.Quantity
            END
            ) AS "販売数量合計"
FROM
        Customers AS c
          LEFT JOIN
        Sales AS s
          ON c.CustomerID = s.CustomerID
GROUP BY
        c.CustomerID
;
  • SUMと(の間にスペースを入れたらエラーが出た。
問題4-3

テーブルPrefecturals、Customersを外部結合し、PrefecturalIDごとのCustomersレコード数を求め、

PrefecturalNameとレコード数を表示しなさい。ただしCustomersデータが存在しない場合は0を表示しなさい。

SELECT
        p.PrefecturalName,
        COUNT(c.CustomerID) AS "顧客数" 
FROM
        Prefecturals AS p
         LEFT JOIN
        Customers AS c
          ON p.PrefecturalID = c.PrefecturalID
GROUP BY
        p.PrefecturalID
;
  • COUNTはMySQLの場合、非NULL値をカウントする。

自己結合を使う

  • 自分自身と比較するものを自己結合と言う。
SELECT
        p1.ProductName AS "商品名1",
        p2.ProductName AS "商品名2",
        (p1.Price+p2.Price) AS "セット価格"
FROM
        Products AS p1,
        Products AS p2
WHERE
        p1.ProductID < p2.ProductID
        AND
        p1.ProductID <> p2.ProductID
        AND
        (p1.Price+p2.Price) >= 2500
;
  • 絶対に別名を付ける
  • <>はNOTイコール

2009-05-15

DataBase vol.11

  • 複数のテーブルの結合を行う(1)続き
  • 外部結合を使う

複数のテーブルの結合を行う(1)続き

問題2-1

テーブルSalaryとEmployeesを結合してEmployeeName、PayDate、Amountを

EmployeeID昇順で表示しなさい。

SELECT
        e.EmployeeID
        ,e.EmployeeName
        ,PayDate
        ,Amount
FROM
        Salary AS s
        ,Employees AS e
WHERE
        s.EmployeeID = e.EmployeeID
ORDER BY
        e.EmployeeID
        ,PayDate
;

問題2-3

テーブルSalesとProductsを結合し、SalesをProductIDでグループ化、Quantityの合計が

300以上のデータについてQuantity合計、ProductID、ProductNameを表示しなさい。

SELECT
        p.ProductID
        ,p.ProductName
        ,SUM(s.Quantity) AS "数量合計"
FROM
        Sales AS s
        ,Products AS p
WHERE
        s.ProductID = p.ProductID
GROUP BY
        s.ProductID
HAVING
        SUM(s.Quantity) >= 300
;

問題3-3

テーブルSales、Customers、CustomerClassesを結合し、CustomerClassIDでグループ化、

Quantityの最大値、CustomerClassID、CustomerClassNameを表示しなさい。

  • 翻訳:法人と個人毎に売り上げた最大数量を求めなさい。
SELECT
        MAX(s.Quantity) AS "最大数量"
        ,cc.CustomerClassID
        ,cc.CustomerClassName AS "顧客クラス名"
FROM
        Sales AS s
        ,Customers AS c
        ,CustomerClasses AS cc
WHERE
        c.CustomerClassID = cc.CustomerClassID
        AND
        s.CustomerID = c.CustomerID
GROUP BY
        cc.CustomerClassID
;
  • 何を出したいかを意識して描くこと。
  • 命名規則を意識する。

外部結合を使う

片方にしか値が入っていないものがある場合に結合を行いたい場合に使う。

  • 先ほどの結合方法だと双方に値がないものはスルーされる。

LEFT OUTER JOIN

  • OUTERがあってもなくてもよい。
  • あってもなくてもくっつける
  • 結合相手が居ない場合はNULLがでる。
    • 数値を演算するものの場合、NULLを0として扱う処理が必要。
  • 絶対に出さなければならない部分を大元にする。
Products //大元
LEFT JOIN
Sales //子
ON (条件)

User

IDName
1obt
2araki
3enomoto
4fukata

User_Mail

IDUser_IDMail_Add
11obt@example.com
21obata@example.com
32araki@example.com
42ark@example.com
54fukata@example.com
64fkt@example.com

の場合(LEFT JOIN部分のみ)

User AS U
LEFT JOIN
User_Mail AS UM
ON (U.ID = UM.User_ID)

2009-05-13

DataBase vol.10

  • テーブルに別名をつける
  • 複数テーブルの結合を行う(1)

テーブルに別名をつける

その前に・・・

複数のテーブルを使うときに同じ列名があると問題が起こるので

  • テーブル名.列名

というように修飾する。

SELECT
        e.EmployeeID
FROM
        Employees AS e //ここでeはEmployeesということにする
;

複数テーブルの結合を行う(1)

SELECT
        列名
FROM
        主体テーブル名
        ,結合するテーブル名
WHERE
        結合条件
;
  • 副問い合わせよりもこちらのほうが圧倒的に使われる。
  • どこがベースになるのか見極めないといけない。
Copyright © crazyup. All rights reserved.