ABC分析
配点 : 40点難易度 : 4
制限実行時間 : 1000ms制限メモリ使用量 : 128 MB
SQLiteの注意点

TOPSIC SQLでは、SQLの実行環境にSQLiteを使用しています。SQLite以外のRDBMSの文法を使用することはできません。
問題を解く際に注意すべきSQLiteの制約について、以下に記載してありますのでご確認下さい。

  1. NUMERIC、INTEGER、REALは、自身のクラスへの変換が推奨されていますが、変換できなかった場合でも、そのまま格納されます。
  2. 新しくデータを追加した場合、既に格納されているデータの中で ROWID の値が最大のものを探し、それに1を加えた値が新しく追加されるデータの ROWID の値として保存されます。
  3. 外部結合は、左外部結合を行う LEFT OUTER JOIN 句のみ使用できます。
    ※2022年12月以降、LEFT / RIGHT / FULL OUTER JOIN が使用可能となりました。
  4. 指定した日付と時刻のタイムゾーンはUTCとして扱われます。
  5. 日付関数や文字列関数については、SQLiteで固有の関数があります。
  6. テーブル項目の論理名が「◯◯日」となっている場合は、日付項目の時分秒は設定されていません。論理名が「◯◯日時」となっている場合は、日付項目の時分秒まで設定されています。
    • 「受注日」の場合は「2021-01-01」
    • 「受注日時」の場合は「2021-01-01 15:15:15」

その他のSQLiteの制約については、SQLiteの公式ドキュメントをご確認ください。
また、ER図の読み方やSQLiteの関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

売上データ(SALES)、売上明細データ(SALES_DTL)を集計した売上金額を指標として、売上金額の累積構成比でABC分析を行いなさい。集計する売上日(SALES_DATE)の期間は2023年6月1日から2023年6月30日の１カ月を対象とする。

商品ごとの累積売上構成比40%以下をランクA、40%より大きく80%以下をランクB、それ以外をランクCとする。また、累計売上構成比は小数点以下第2位で四捨五入とする。

ABC分析とは、重点分析と呼ばれることもあり、製品の売上・コスト・在庫など重視する指標を決めて、ウェイトが大きい順に並べて分類し、管理する方法である。複数ある商品に対して重要度や優先度を決めることで効果的に管理できる。また、累計構成比とは、複数のデータを合計した後に、その合計が全体に対してどの程度の割合を占めるかを示すものである。

ABC分析の手順例

  1. 売上金額（UNITPRICE × SALES_QTY）を集計し、商品を売上金額の降順に並べる。
  2. 商品ごとの売上金額を対象期間の合計売上金額で割って売上構成比を算出し、売上構成比の降順に並べる。
  3. 売上構成比1位の商品から、順番に商品毎の売上金額を足しこんだ値（累積集計した売上金額）を合計売上金額で割って、商品毎の累積売上構成比を算出する。表示フォーマットのように、集計した売上金額が同じ場合は、それぞれの金額の合計を累積集計の売上金額として計算する。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)

  • ITEM_CODE → CODE
  • ITEM_NAME → NAME
  • 集計した売上金額 → SAL_AMT
  • 累積集計した売上金額 → CML_AMT
  • 算出した売上構成比に'％'を付加 → SAL_COMP
  • 算出した累計構成比に'％'を付加 → TTL_COMP
  • 振り分けたランク → RANK

表示順

  1. 集計した売上金額の降順
  2. ITEM_CODEの降順

表示フォーマット

※あくまでフォーマットを示すもので、正解例ではありません。行数も正解とは異なります。

CODE NAME SAL_AMT CML_AMT SAL_COMP TTL_COMP RANK
P005 カラーピグメントカットソー 5000 5000 31.3% 31.3% A
P004 Vネックカットソー 4000 9000 25.0% 56.3% B
P003 オフショルダーブラウス 3000 12000 18.8% 75.0% B
P002 フリルハイネックブラウス 2000 16000 12.5% 100.0% C
P001 フリルブラウスRock 2000 16000 12.5% 100.0% C

ER図 (半角:物理名、全角:論理名)


テーブル定義

テーブル名: ITEM

主キー 列名 データ型 必須 デフォルト値
✔︎ ITEM_CODE VARCHAR ✔︎ NULL
ITEM_NAME VARCHAR ✔︎ NULL
ITEM_TYPE INTEGER ✔︎ NULL
STOCK_MANAGEMENT_TYPE INTEGER ✔︎ NULL
TAX_TYPE VARCHAR ✔︎ NULL
TAX_NO INTEGER ✔︎ NULL
SHORT_DESCRIPTION VARCHAR NULL
SALE_START_DATE VARCHAR NULL
SALE_END_DATE VARCHAR NULL
DISCOUNT_PRICE_START_DATETIME VARCHAR NULL
DISCOUNT_PRICE_END_DATETIME VARCHAR NULL
DELIVERY_TYPE_NO INTEGER NULL
RECOMMEND_ITEM_RANK INTEGER NULL
ITEM_POPULAR_RANK INTEGER NULL
UPDATE_USER_NAME VARCHAR NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

テーブル名: SALES

主キー 列名 データ型 必須 デフォルト値
✔︎ SALES_NO VARCHAR ✔︎ NULL
ORDER_NO VARCHAR NULL
SALES_DATE VARCHAR ✔︎ NULL
SALES_TYPE INTEGER 1
DEPT_CODE VARCHAR ✔︎ NULL
CUST_CODE VARCHAR ✔︎ NULL
EMP_CODE VARCHAR ✔︎ NULL
SALES_AMT INTEGER ✔︎ 0
CMP_TAX INTEGER ✔︎ 0
SLIP_COMMENT VARCHAR NULL
INVOICED_DATE VARCHAR NULL
INVOICE_NO VARCHAR NULL
UPDATED_NO VARCHAR NULL
ORGNL_NO VARCHAR NULL
UPDATE_USER_NAME VARCHAR NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

テーブル名: SALES_DTL

主キー 列名 データ型 必須 デフォルト値
✔︎ SALES_NO VARCHAR ✔︎ NULL
✔︎ LINE_NO INTEGER ✔︎ NULL
ITEM_CODE VARCHAR ✔︎ NULL
UNITPRICE INTEGER ✔︎ 0
DELIVERED_QTY INTEGER 0
CMP_TAX_RATE INTEGER NULL
SALES_QTY INTEGER ✔︎ 1
DISCOUNT INTEGER ✔︎ 0
INVOICED_DATE VARCHAR NULL
INVOICE_NO VARCHAR NULL
INVOICE_DELAY_TYPE INTEGER NULL
UPDATE_USER_NAME VARCHAR NULL
USER_UPDATE_DATETIME VARCHAR CURRENT_TIMESTAMP

サンプルデータ

テーブル名: ITEM

ITEM_CODE ITEM_NAME ITEM_TYPE STOCK_MANAGEMENT_TYPE TAX_TYPE TAX_NO SHORT_DESCRIPTION SALE_START_DATE SALE_END_DATE DISCOUNT_PRICE_START_DATETIME DISCOUNT_PRICE_END_DATETIME DELIVERY_TYPE_NO RECOMMEND_ITEM_RANK ITEM_POPULAR_RANK UPDATE_USER_NAME USER_UPDATE_DATETIME
P001 フリルブラウスRock 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 20 0 1 SYSTEM 2023-01-01 12:12:12
P002 フリルハイネックブラウス 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 10 0 1 SYSTEM 2023-01-01 12:12:12
P003 オフショルダーブラウス 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 20 0 4 SYSTEM 2023-01-01 12:12:12
P004 Vネックカットソー 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 10 0 2 SYSTEM 2023-01-01 12:12:12
P005 カラーピグメントカットソー 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 20 0 2 SYSTEM 2023-01-01 12:12:12
P006 ボーダーカットソー 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 10 0 5 SYSTEM 2023-01-01 12:12:12
P007 半袖鹿の子ポロシャツ 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 20 0 7 SYSTEM 2023-01-01 12:12:12
P008 ラインチビポロシャツ 1 0 1 2019 NULL 2023-01-21 NULL NULL NULL 10 0 10 SYSTEM 2023-01-01 12:12:12
P009 リブタンクトップ 1 0 1 2019 NULL 2023-01-21 NULL NULL NULL 20 0 8 SYSTEM 2023-01-01 12:12:12
P010 ロングタンクトップ 1 1 1 2019 NULL 2023-01-21 NULL NULL NULL 10 0 7 SYSTEM 2023-01-01 12:12:12

テーブル名: SALES

SALES_NO ORDER_NO SALES_DATE SALES_TYPE DEPT_CODE CUST_CODE EMP_CODE SALES_AMT CMP_TAX SLIP_COMMENT INVOICED_DATE INVOICE_NO UPDATED_NO ORGNL_NO UPDATE_USER_NAME USER_UPDATE_DATETIME
S101 NULL 2023-06-01 1 D100 C100 E100 1000 100 NULL NULL NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S102 NULL 2023-06-02 1 D200 C200 E200 3000 300 NULL NULL NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S103 NULL 2023-06-03 1 D100 C100 E100 3000 300 NULL NULL NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S104 NULL 2023-06-04 1 D200 C200 E200 5250 525 NULL NULL NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S105 NULL 2023-06-05 1 D200 C200 E200 3750 375 NULL NULL NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S106 NULL 2023-05-31 1 D300 C500 E800 1000 100 NULL NULL NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S107 NULL 2023-07-01 1 D100 C100 E100 1000 100 NULL NULL NULL NULL NULL SYSTEM 2023-05-30 06:20:14

テーブル名: SALES_DTL

SALES_NO LINE_NO ITEM_CODE UNITPRICE DELIVERED_QTY CMP_TAX_RATE SALES_QTY DISCOUNT INVOICED_DATE INVOICE_NO INVOICE_DELAY_TYPE UPDATE_USER_NAME USER_UPDATE_DATETIME
S101 1 P001 1000 1 10 1 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S102 1 P002 500 4 10 4 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S102 2 P001 1000 1 10 1 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S103 1 P003 1500 2 10 2 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S104 1 P004 2000 2 10 2 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S104 2 P005 1250 1 10 1 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S105 1 P005 1250 3 10 3 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S106 1 P001 1000 1 10 1 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
S107 1 P001 1000 1 10 1 0 NULL NULL NULL SYSTEM 2023-05-30 06:20:14
Sorry, Japanese Only.