今回の初心者訓練所#12は、初心者にはちょっと難易度が上がってしまうと思いますが、「TOPSIC SQL CONTEST（TSC）」にチャレンジⅡということで、先日開催されたTSC第11回目の問題について解説していきます。まだ、TSCの11回目にチャレンジしてない皆さんは、是非、TSCにチャレンジしてから、復習、確認を兼ねて本記事をご覧いただければと思います。(会員登録がまだの方は、簡単に出来ますので登録してチャレンジしてみてください!)
問題１「在庫整理」
難易度1の問題です。在庫データの削除処理の問題になります。
【問題】
在庫データを整理することとなった。
在庫テーブル(STOCK)から、最終出荷日(LAST_DELIVERY_DATE)が2023年1月1日から2023年1月31日の期間内で、かつ、実在庫数(ACTUAL_AMT)がゼロのデータを削除しなさい。
【使用テーブル】
【解答例】
|
DELETE |
【解説】（解答例のコメント番号に対応した説明になります）
DELETE文で在庫データよりデータを削除します。
最終出荷日と実在庫数の条件をAND条件で記述します。
① BETWEENを使用して、最終出荷日の範囲指定をおこないます。
【解答記入】
問題２「ドメイン名」
難易度2となる次の問題は、関数を組合せて使用し文字列を切り出す問題となっています。
【問題】
会員テーブル(MEMBER_MST)のメールアドレス(EMAIL)より、ドメイン名をドメイン拡張子無しで抜き出して表示しなさい。
ただし、メールアドレスのドメイン拡張子は、「.co.jp」のみ登録されているものとする。
また、これらの拡張子と同じ文字列がメールアドレスやドメイン名の途中に含まれることはないものとする。(「aaa.co.jp@bb.co.jpccc.co.jp」等はないものとする)
例えば、メールアドレス「aaa@bbb.co.jp」の場合、ドメイン名からドメイン拡張子「.co.jp」を省いて、「bbb」と表示する。
【使用テーブル】
【解答例】
| SELECT MEMBER_CODE AS MEMBER , EMAIL AS EMAIL , SUBSTR( -- ① , INSTR(EMAIL, '@') + 1 -- ② , (INSTR(EMAIL, '.co.jp') - 1) - INSTR(EMAIL, '@') -- ③ ) AS DOMAIN FROM MEMBER_MST ORDER BY DOMAIN DESC , MEMBER DESC; |
【解説】
① SUBSTRを使用して、@マークの次の文字から、ドメイン拡張子の前の文字を切り出します。
② INSTRを使用して、取得した@マークの位置に1をプラスし切り出しの先頭位置を求めます。
③ INSTRを使用して、ドメイン拡張子の位置から1をマイナスし、そこから@マークまでの位置をマイナスして切り出す文字数を求めます。
[例]
メールアドレスが「1234@6789.123.co.jp」の場合、「6789.123」が切り出されます。
・「INSTR(EMAIL, '@') + 1」= 5 + 1 = 6
・「INSTR(EMAIL, '.co.jp') – 1」= 14 - 1
→ 「SUBSTR(EMAIL , 6 , (13 – 5))」= 「6789.123」
【解答記入】
【コードテスト結果】
問題３「Zチャート」
難易度3のこの問題は、Zチャートを作成する際に必要な3つの指標を求める問題になります。
SUM() OVERを利用して累計値を求める方法がポイントになります。
【問題】
Zチャートで2023年の1年間の売上推移を確認したい。
そのために、売上データ(SALES)から、Zチャートに必要な「月次売上」「売上累計」「移動年計」の3つの指標を集計しなさい。
売上データの売上区分(SALES_TYPE) = 2 (売上返品)の売上金額合計(SALES_AMT)は、マイナス値がセットされているので、そのままマイナス値で計算すること。また、集計対象とするデータは、赤黒伝票番号(UPDATED_NO)の値がNULL値の黒伝のみを対象とすること。
Zチャートとは、商品の売上や出荷数など、物事の推移を分析するための折れ線グラフの一種である。
値の推移、値の累積、値の移動合計の3つのデータをそれぞれ折れ線グラフで表したもので、それぞれの3つの線が「Z」の字を描いているように見えることからZチャートと呼ばれている。
指標の集計方法
・月次売上 = 売上金額合計(SALES_AMT)を月別に集計※
・売上累計 = 2023年1月から該当月までの月次売上を合計
・移動年計 = 該当月の月次売上に過去11カ月の売上累計を合計
※集計対象となる売上データは各月に最低1件以上存在しているものとする。
【使用テーブル】
【解答例】
| WITH SALES_YM AS ( -- ① SELECT STRFTIME('%Y-%m', SALES_DATE) AS YM -- ② , SUM(SALES_AMT) AS MON_AMT FROM SALES WHERE YM >= '2022-01' -- ③ AND UPDATED_NO IS NULL -- ④ GROUP BY YM ) , Z_CHART AS ( -- ⑤ SELECT YM AS YEAR_MONTH , MON_AMT AS MONTH_AMT , SUM(MON_AMT) OVER ( -- ⑥ ORDER BY -- ⑦ YM ROWS BETWEEN 11 PRECEDING AND CURRENT ROW ) AS YEAR_MOVE_AMT FROM SALES_YM ) SELECT YEAR_MONTH , MONTH_AMT , SUM(MONTH_AMT) OVER ( ORDER BY YEAR_MONTH) AS CUML_AMT -- ⑧ , YEAR_MOVE_AMT FROM Z_CHART WHERE YEAR_MONTH BETWEEN '2023-01' AND '2023-12' -- ⑨ ORDER BY YEAR_MONTH ASC; |
【解説】
① 月毎に売上金額を集計して、中間テーブルを作成します。
② 売上日を売上年月(YYYY-MM)の形式に変換し、売上金額を集計します。
③ 移動年計を求めるため、1年前からのデータを集計対象とします。
④ 黒伝のみを対象とするため、赤黒伝票番号がNULLの条件を記述します。
⑤ 該当年月から過去11カ月の売上集計金額を集計した、中間テーブルを作成します。
⑥ SUM() OVERを使用し、①で求めた各月の売上集計金額の累計を取得します。
⑦ 年月でソートを行い、「ROWS BETWEEN 11 PRECEDING」で 11行前(11カ月前)までのデータを対象とします。
⑧ SUM() OVERを使用し、該当月までの売上累計金額を取得します。
⑨ BETWEENを使用し、2023年のデータを絞り込みます。
【解答記入】
【コードテスト結果】
【参考情報】
・①の中間テーブルの内容は以下となります。
・⑤の中間テーブルの内容は以下となります。
問題４「アソシエーション分析」
それでは最後に難易度4の問題になります。こちらも、問題3と同様に、
購買履歴データからアソシエーション分析に必要な3つの指標を求める問題になります。
テーブルの自己結合を利用して、商品の組合せを取得する方法がポイントになります。
【問題】
あるECサイトの購買履歴データ(PURCHASE_HISTORY)から、アソシエーション分析に必要な3つの指標を求めて、商品の組合せ毎に一覧で表示しなさい。
商品の組合せは、商品Aと商品Bの2種類の組合せのみとし、また、購買履歴データには、各会員コードの購入履歴は1セッションIDのみ存在するものとする。
アソシエーション分析とは、顧客の購買履歴などから行動パターンを分析する手法のことで、「データマイニング」の1分野である「相関ルール抽出」の代表的な手法のひとつである。有名なものに「おむつとビール」の事例がある。
アソシエーション分析では、以下の3つの指標を使って分析する。
・「支持度(Support)」 : すべての購買データのうち、商品Aと商品Bが購買された割合を表す
・「信頼度(Confidence)」: 商品Aを買った人のうち、商品Bも同時に購買した人の割合を表す
・「リフト値(Lift)」 : すべての購買データのうち、商品Aがあることで、商品Bの購買率がどのくらい引き上げられているかを表す
リフト値が高いほど、商品Aがあることで商品Bも購買されたという相関関係が強いと評価できる。
指標の計算方法(※指標の計算結果は、小数点第6位を四捨五入すること)
・支持度 = 商品Aと商品Bを購買した顧客数 ÷ 全顧客数 × 100
・信頼度 = 商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数 × 100
・リフト値 = (商品Aと商品Bを購買した顧客数 ÷ 商品Aを購買した顧客数)
÷ (商品Bを購買した顧客数 ÷ 全顧客数)
【使用テーブル】
【解答例】
| WITH SUB1 AS ( -- ① SELECT PH.MEMBER_CODE , MC.MEMBER_CNT , PH.ITEM_CODE , COUNT(1) OVER (PARTITION BY PH.ITEM_CODE) AS ITEM_CNT -- ② FROM PURCHASE_HISTORY AS PH CROSS JOIN ( -- ③ SELECT -- ④ COUNT(DISTINCT MEMBER_CODE) AS MEMBER_CNT FROM PURCHASE_HISTORY ) AS MC ) , SUB2 AS ( -- ⑤ SELECT S1.ITEM_CODE AS ITEM_A , S2.ITEM_CODE AS ITEM_B , S1.ITEM_CNT AS A_CNT , S2.ITEM_CNT AS B_CNT , COUNT(1) AS AB_CNT , S1.MEMBER_CNT AS M_CNT FROM SUB1 AS S1 INNER JOIN SUB1 AS S2 -- ⑥ ON S1.MEMBER_CODE = S2.MEMBER_CODE WHERE S1.ITEM_CODE <> S2.ITEM_CODE -- ⑦ GROUP BY ITEM_A , ITEM_B , A_CNT , B_CNT , M_CNT ) SELECT -- ⑧ ITEM_A , ITEM_B -- ⑨ , ROUND(100.0 * AB_CNT / M_CNT, 5) AS SUPPORT , ROUND(100.0 * AB_CNT / A_CNT, 5) AS CONFIDENCE , ROUND( (1.0 * AB_CNT / A_CNT) / (1.0 * B_CNT / M_CNT) , 5 ) AS LIFT FROM SUB2 ORDER BY LIFT DESC , SUPPORT DESC , CONFIDENCE DESC , ITEM_A DESC; |
【解説】
① 商品毎の購買顧客件数を集計した、中間テーブルを作成します。
② COUNT() OVERを利用して、商品毎の購買顧客件数をカウントします。
③ 交差結合で、副問合せで取得した会員数合計を各レコードに持たせます。
④ 副問合せで重複なしの会員数を取得します。
⑤ 商品A、Bが同じ組合せの購買顧客数を集計した中間テーブルを作成します。
⑥ ①の中間テーブルを会員コードで自己結合して、商品A、Bの組合せを作成します。
⑦ 商品A、Bの組合せを作成したいので、同一の商品コードの組合せのレコードは対象外にします。
⑧ ⑤の中間テーブルより各指数を算出して表示します。
⑨ 各指標を計算しROUNDで小数点第6位を四捨五入します。
【解答記入】
【コードテスト結果】
【参考情報】
・①の中間テーブルの内容は以下となります。
・⑥の中間テーブルの内容は以下となります。
解説は以上となります。
おすすめの学習コンテンツ
本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください！
SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
SQLiteとは？他のデータベースとの違いも解説
SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
「SQLの教科書」ビックデータ時代の必須科目
SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
TOPSIC-SQL無料トライアルテスト
SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
過去に開催されたコンテストの問題にもチャレンジできます。
TOPSIC SQL CONTEST
SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
SQL-BOOT CAMP #1
ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう！」
あとがき
初心者訓練所の第12弾となる今回は、TSC11回目の解答について解説を行いました。
今回は、WINDOW関数やテーブルの自己結合など、解答のポイントになる箇所が盛り沢山で難易度が高かったと思います。しかし、これで皆さんのSQLの新たな引き出しも増えたのではないでしょうか。
今回のチャレンジした「TOPSIC SQL CONTEST（TSC）」は、会員になると過去のコンテスト問題にも挑戦できます。さらにスキルを磨くチャンスですので、是非、トライしてみてください。
(株)システムインテグレータ TOPSICチーム 元鬼軍曹 久保 司
