Zチャート
配点 : 30点難易度 : 3
制限実行時間 : 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の関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

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件以上存在しているものとする。

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

  • 集計した年月(2023-01から2023-12まで表示する) → YEAR_MONTH
  • 集計した月次売上金額 → MONTH_AMT
  • 集計した売上累計金額 → CUML_AMT
  • 集計した移動年計金額 → YEAR_MOVE_AMT

表示順

  1. 集計した年月の昇順

表示フォーマット

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

YEAR_MONTH MONTH_AMT CUML_AMT YEAR_MOVE_AMT
2023-01 20600 20600 680600
2023-02 7970 28570 578570
2023-03 17000 45570 495570
2023-04 21290 66860 426860
2023-05 130000 196860 476860

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


テーブル定義

テーブル名: 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

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
S001 NULL 2022-12-01 1 D100 C100 E100 10000 1000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S002 NULL 2022-11-01 1 D200 C200 E200 20000 2000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S003 NULL 2022-10-01 1 D100 C100 E100 30000 3000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S004 NULL 2022-09-01 1 D200 C200 E200 40000 4000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S005 NULL 2022-08-01 1 D200 C200 E200 50000 5000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S006 NULL 2022-07-01 1 D200 C200 E200 60000 6000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S007 NULL 2022-06-01 1 D100 C100 E100 70000 7000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S008 NULL 2022-05-01 1 D200 C200 E200 80000 8000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S009 NULL 2022-04-01 1 D200 C200 E200 90000 9000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S010 NULL 2022-03-01 1 D200 C200 E200 100000 10000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S011 NULL 2022-02-01 1 D100 C100 E100 110000 11000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S012 NULL 2022-01-01 1 D200 C200 E200 120000 12000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S101 NULL 2023-01-01 1 D100 C100 E100 15000 1500 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S102 NULL 2023-02-01 1 D200 C200 E200 8990 899 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S103 NULL 2023-03-01 1 D100 C100 E100 8000 800 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S104 NULL 2023-04-01 1 D200 C200 E200 7000 700 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S105 NULL 2023-05-01 1 D200 C200 E200 6000 600 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S106 NULL 2023-06-01 1 D300 C500 E800 3000 300 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S107 NULL 2023-07-01 1 D100 C100 E100 1000 100 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S108 NULL 2023-08-01 1 D200 C200 E200 3000 300 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S109 NULL 2023-09-01 1 D200 C200 E200 4000 400 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S110 NULL 2023-10-01 1 D300 C500 E800 3000 300 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S111 NULL 2023-11-01 1 D100 C100 E100 3000 300 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S112 NULL 2023-12-01 1 D200 C200 E200 3000 300 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S201 NULL 2023-01-02 1 D100 C100 E100 5600 560 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S202 NULL 2023-02-02 1 D200 C200 E200 3980 398 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S203 NULL 2023-03-02 1 D100 C100 E100 9000 900 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S204 NULL 2023-04-02 1 D200 C200 E200 8790 879 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S205 NULL 2023-05-02 1 D200 C200 E200 124000 12400 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S206 NULL 2023-06-02 1 D300 C500 E800 13980 1398 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S207 NULL 2023-07-02 1 D100 C100 E100 90000 9000 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S208 NULL 2023-08-02 1 D200 C200 E200 890240 89024 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S209 NULL 2023-09-02 1 D200 C200 E200 18900 1890 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S210 NULL 2023-10-02 1 D300 C500 E800 111000 11100 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S211 NULL 2023-11-02 1 D100 C100 E100 24500 2450 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S212 NULL 2023-12-02 1 D200 C200 E200 892300 89230 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S301 NULL 2023-02-02 2 D200 C200 E200 -2000 -200 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S302 NULL 2023-02-02 2 D200 C200 E200 -3000 -300 NULL NULL NULL NULL NULL SYSTEM 2023-12-31 12:12:12
S303 NULL 2023-03-03 1 D200 C200 E200 5000 500 NULL NULL NULL S304 NULL SYSTEM 2023-12-31 12:12:12
S304 NULL 2023-04-03 1 D200 C200 E200 -5000 -500 NULL NULL NULL S303 NULL SYSTEM 2023-12-31 12:12:12
S305 NULL 2023-04-03 1 D200 C200 E200 5500 550 NULL NULL NULL NULL S303 SYSTEM 2023-12-31 12:12:12

Sorry, Japanese Only.