解答
CHAPTER 6
016
SQL文（クエリ）
WITH master AS
(SELECT product_id
, SUM(quantity) AS sum_qty
, SUM(revenue) AS sum_revenue
, SUM(revenue) / SUM(quantity) AS avg_unit_price
FROM impress_sweets.sales
GROUP BY product_id
)
SELECT *
, (SELECT AVG(avg_unit_price) FROM master)
AS avg_unit_price_by_product
FROM master
WHERE avg_unit_price >
(SELECT AVG(avg_unit_price) FROM master)
ORDER BY 4 DESC
結果テーブル
解説
［sales］テーブルを［product_id］でグループ化し、販売個数合計、販売金額合計、それらを割って求めた平均単価のテーブルを作ったものを、WITH句で「master」という名前の仮想テーブルとして利用しています。［master］テーブルの作成時に、商品ID別のグループ化と集計を行っているため、メインのSQLでの集計は不要です。商品ID別の平均単価の平均について、1つの値を戻り値とするサブクエリで取り出して、5番目のフィールドに加えると同時に、WHERE句の絞り込みに利用しています。
017
SQL文（クエリ）
WITH master AS
(SELECT *
FROM impress_sweets.customers
WHERE prefecture IN
(SELECT prefecture
FROM impress_sweets.customers
GROUP BY prefecture
HAVING SUM(IF (is_premium IS TRUE, 1, 0)) >= 4
))
SELECT * FROM master
WHERE birthday = (SELECT MIN(birthday) FROM master)
結果テーブル
解説
WITH句では［customers］テーブルから「プレミアム顧客が4名以上いる都道府県」に合致するレコードを取得しています。WHERE句にて、IN演算子で比較する対象にサブクエリを記述し、そのサブクエリでは、条件に該当する都道府県のリストを返しています。表形式の戻り値を「master」として仮想テーブルで利用します。
「もっとも高齢の顧客を取得する」ということは、誕生日（birthday）が［master］テーブル全体の最小値と一致する、ということなので、「WHERE birthday = (SELECT MIN(birthday) FROM master)」で個別の顧客の誕生日（birthday）と［master］テーブル全体の最小の［birthday］を比較して、同じ顧客だけに絞り込んでいます。
なお、メインのクエリは以下でもよいでしょう。
SELECT * FROM master
WHERE birthday IS NOT NULL
ORDER BY birthday
LIMIT 1
018
SQL文（クエリ）
SELECT COUNT(*) AS session
, SUM(pageviews) AS sum_pageviews
FROM (
SELECT SUM(
IF(event_name = "page_view", 1, 0)) AS pageviews
FROM impress_sweets.web_log
GROUP BY user_pseudo_id, ga_session_number
)
結果テーブル
解説
「手ごわいクエリを記述する必要があるのでは？」と心配した人もいるかもしれませんが、実際は上記の通り数行で取得できます。まずは4行目のサブクエリから見ていきましょう。セッション数は「ユニークな［user_pseudo_id］と［ga_session_number］の組み合わせ」と定義されたので、それらでグループ化します。
SECTION 4-1で学んだ通り、グループ化によりフィールドの構成要素（この場合は［user_pseudo_id］と［ga_session_number］）が1レコードになるからです。ページビュー数は［event_name］が"page_view"に等しいレコード数と定義されているので、IF文を使い条件が合致した場合に1 を記録しています。
1～2行目の本体のクエリでは、セッションの総数を求めるためにレコード数をCOUNT(*)で集計し、ページビュー数の合計を求めるためにSUM関数で［pageviews］を集計しています。なお、テーブル全体を集計しているのでグループ化は不要です。そのため、GROUP BY句は記述していません。
019
SQL文（クエリ）
SELECT
sum_proper_revenue - sum_non_proper_revenue
AS revenue_diff
FROM (
SELECT
(SELECT SUM(revenue) FROM impress_sweets.sales
WHERE is_proper IS TRUE)
AS sum_proper_revenue
, (SELECT SUM(revenue) FROM impress_sweets.sales
WHERE is_proper IS FALSE)
AS sum_non_proper_revenue
)
結果テーブル
解説
［sales］テーブルから1つだけの値を返すサブクエリとして、まずは定価で販売された場合の販売金額の合計を求めます。この問題のポイントは、4～5行目にある以下のサブクエリです。
SELECT SUM(revenue) FROM impress_sweets.sales
WHERE is_proper IS TRUE
これが記述できれば、WHERE句の条件を変えて「割引価格で販売された場合の販売金額の合計」も求めることができます。
4行目から10行目のサブクエリでフィールド［sum_proper_revenue］として定価で販売された場合の販売金額合計を求め、フィールド［sum_non_proper_revenue］として割引価格で販売された販売金額合計を求めています。外側のフィールドでは、この2つのフィールドの値を引き算して、解答を求めています。
以下が別解となります。サブクエリで、いわゆる縦持ちの形のまま［is_proper］の値ごとに［revenue］を合計で集計しています。2～3行目の本クエリでは、サブクエリで作成された仮想テーブルが［is_prroper］フィールドが、［true］のレコードと［false］のレコードに分かれていて、そのままでは引き算できないので、集計関数MAXを利用して1行にまとめ、前者から後者を差し引く形で解答を求めています。MAXでなくとも値を1つにまとめる、MIN、AVGでも同じ結果となります。
SELECT
MAX(IF(is_proper IS TRUE, revenue,NULL)) -
MAX(IF(is_proper IS FALSE, revenue,NULL)) AS revenue_diff
FROM (
SELECT is_proper, SUM(revenue) AS revenue
FROM impress_sweets.sales
GROUP BY is_proper
)
020
SQL文（クエリ）
SELECT SUM(lost_revenue) AS sum_lost_revenue
FROM (
SELECT *
, quantity * (proper_unit_price - discount_unit_price)
AS lost_revenue
FROM (
SELECT *, revenue / quantity AS discount_unit_price
, (
SELECT
SUM(revenue) / SUM(quantity)
FROM impress_sweets.sales
WHERE product_id = 1 AND is_proper IS TRUE
) AS proper_unit_price
FROM impress_sweets.sales
WHERE product_id = 1 AND is_proper IS FALSE
)
)
結果テーブル
解説
［product_id］が「1」の商品について、数量（quantity）、定価の単価（proper_unit_price）、割引価格の単価（discount_unit_price）を1つのテーブルにまとめて、「数量×（定価の単価－割引価格の単価）」を計算した結果を合計するのが、基本的な考え方です。
9～13行目のクエリでは「product_id = 1 AND is_proper IS TRUE」という条件で、商品IDが「1」の定価での販売にレコードを絞り込んでいます。そのうえで［revenue］の合計額と［quantity］の合計数を割り算して求めた「1つの値」を［proper_unit_price］として取得しています。この値が商品ID（product_id）が「1」の定価の単価です。
次に、その外側のクエリ（7～8行目、14～15行目）では、「product_id = 1 AND is_proper IS FALSE」で、割引販売された商品IDが「1」のレコードに絞り込んだうえで、もともと存在していたすべてのフィールドを「*」で取得し、かつ［revenue］を［quantity］で割った割引販売の単価（discount_unit_price）のフィールドと、9～13行目のクエリで取得した［proper_unit_price］を追加しています。
結果的に、7～15行目では、もともとの全フィールド、割引販売の単価（discount_unit_price）、定価での単価（proper_unit_price）が1つのテーブルにそろったことになります。あとは「数量×（定価の単価－割引価格の単価）」で、定価で販売していたら得られた売上を計算しています。
