2017/12/07
複数のクエリを組み合わせた抽出 -FROM,IN
例題
顧客IDごとに累計売上を合計し、1顧客あたりの平均売上、最低売上、最高売上を求める
SELECT
AVG(total_sales),
MIN(total_sales),
MAX(total_sales)
FROM
(SELECT
customer_id,
SUM(amount) AS total_sales
FROM
payment
GROUP BY
customer_id
) AS customer_payment;
例題
INを用いて、2007年5月に支払いがあった顧客のlast_nameを抽出する
JOINでやってしまうと、その分行が膨れ上がってしまい処理時間がかかってしまう。
SELECT
last_name
FROM
customer
(SELECT
customer_id
FROM
payment_p2007_05
)
;
例題
paymentテーブルから、2007年1月の売上データを抽出する
①
SELECT
SUM(amount) AS total_sales
FROM
payment_date >= ‘2007-01-01’
◯ AND payment_date <=‘2007-02-01’
☓ AND payment_date <=‘2007-01-31’
;
☓の場合だと2007-01-31:0000のものとなり、実質集計されないから
②
SELECT
SUM(amount) AS total_sales
FROM
payment
WHERE
CAST(payment_date AS DATE)
BETWEEN ‘2007-01-01’ AND ‘2007-01-31'
テーブル関連
テーブルのカラム構造+データをコピー
(この方法だと、AUTO_INCREMENTなど一部の属性はコピーされないので注意)
CREATE TABLE 新規テーブル名 WHERE * FROM 元となるテーブル名
カラム構造のみコピーして新規テーブルの作成
CREATE TABLE 新規テーブル名 LIKE 元となるテーブル名
他のテーブルのレコードをコピー
INSERT INTO テーブル名 WHERE * FROM 元となるテーブル名
WHERE と HAVINGのちがい
→適用される順番がちがう
WHERE → GROUP BY → HAVINGの順番
つまりWHEREを用いた場合、WHEREが先に適用されるため、GROUP BYで集計した結果に対して、絞り込みをすることができない
POSIX正規表現
例題
filmテーブルのdescriptionに入っている Thoughtful または Insightful の数を求める
MySQLとPostgreSQL によって若干書き方が異なる
MySQLの場合
SELECT
COUNT(*)
FROM
film
WHERE
description REGEXP ‘(Thou | Insi)ghtful’;
PostgreSQLの場合
SELECT
COUNT(*)
FROM
film
WHERE
description REGEXP ~'(Thou | Insi)ghtful’;