Puzzle19 - SQLパズル演習

問題

SalesData:売上データ
(district_nbr:地区番号, sales_person:セールスマン, sales_id:セールスID, sales_amt:売上高)

「各地域で上位３位の好成績を上げたセールスマンのレポートを作成する」

サンプルデータ：
  district_nbr |  sales_person   | sales_id | sales_amt
 --------------+-----------------+----------+-----------
             1 | カーリー        |        5 |      3.00
             1 | ハーポ          |       11 |      4.00
             1 | ラリー          |        1 |     50.00
             1 | ラリー          |        2 |     50.00
             1 | ラリー          |        3 |     50.00
             1 | モー            |        4 |      5.00
             2 | ディック        |        8 |      5.00
             2 | フレッド        |        7 |      5.00
             2 | ハリー          |        6 |      5.00
             2 | トム            |        7 |      5.00
             3 | アーヴィン      |       10 |      5.00
             3 | メルヴィン      |        9 |      7.00
             4 | ジェニー        |       15 |     20.00
             4 | ジェシー        |       16 |     10.00
             4 | メアリー        |       12 |     50.00
             4 | オプラ          |       14 |     30.00
             4 | サリー          |       13 |     40.00

yukaさんの回答

fumikoの回答

SQL文

 CREATE VIEW ss (district_nbr,sales_person,sum_sales_amt) AS 
 SELECT district_nbr,sales_person,sum(sales_amt)as sum_sales_amt
 FROM salesdata
 GROUP BY sales_person,district_nbr
 ORDER BY district_nbr ,sum_sales_amt desc

 CREATE VIEW ss2 (district_nbr,sales_person,sum_sales_amt) AS
 SELECT  s1.district_nbr,
         s1.sales_person,
      (SELECT (case when count(s2.sum_sales_amt) = 0 then s1.sum_sales_amt 
                    when count(s2.sum_sales_amt) = 1 then s1.sum_sales_amt 
                    when count(s2.sum_sales_amt) = 2 then s1.sum_sales_amt 
                    else null end)
       FROM ss as s2
       WHERE s1.sum_sales_amt < s2.sum_sales_amt 
                and s1.district_nbr = s2.district_nbr) as sum_sales_amt
 FROM ss as s1
 GROUP BY s1.district_nbr,s1.sales_person,s1.sum_sales_amt
 ORDER BY s1.district_nbr,s1.sum_sales_amt desc) as ss2
 

 SELECT district_nbr,sales_person,sum_sales_amt
 FROM ss2
 WHERE sum_sales_amt IS NOT NULL

結果

  district_nbr |  sales_person   | sum_sales_amt
 --------------+-----------------+---------------
             1 | ラリー          |        150.00
             1 | モー            |          5.00
             1 | ハーポ          |          4.00
             2 | ハリー          |          5.00
             2 | フレッド        |          5.00
             2 | ディック        |          5.00
             2 | トム            |          5.00
             3 | メルヴィン      |          7.00
             3 | アーヴィン      |          5.00
             4 | メアリー        |         50.00
             4 | サリー          |         40.00
             4 | オプラ          |         30.00

考え

• ビュー作成：personごとに売上合計を集計したテーブル

  district_nbr |  sales_person   | sum_sales_amt
 --------------+-----------------+---------------
             1 | ラリー          |        150.00
             1 | カーリー        |          3.00
             1 | ハーポ          |          4.00
             1 | モー            |          5.00
             2 | フレッド        |          5.00
             2 | トム            |          5.00
             2 | ディック        |          5.00
             2 | ハリー          |          5.00
             3 | アーヴィン      |          5.00
             3 | メルヴィン      |          7.00
             4 | サリー          |         40.00
             4 | オプラ          |         30.00
             4 | ジェニー        |         20.00
             4 | ジェシー        |         10.00
             4 | メアリー        |         50.00

• 相関副問い合わせ＆自己結合
  • 自己結合:売上合計が一方より大きく、地区番号が等しいという条件で

　　　　例：地区番号1の場合

   sales_person   | sum_sales_amt |  sales_person   | sum_sales_amt
 -----------------+---------------+-----------------+---------------
  ハーポ          |          4.00 | ラリー          |        150.00
  ハーポ          |          4.00 | モー            |          5.00
  カーリー        |          3.00 | ハーポ          |          4.00
  カーリー        |          3.00 | ラリー          |        150.00
  カーリー        |          3.00 | モー            |          5.00
  モー            |          5.00 | ラリー          |        150.00

• 地区番号、セールスマンごとにグループ化しする
• count(s2.sales_person)が0～2の場合は上位三位である