SQL Server

年度毎の累計値を求めるSQL文

SQLの文法

年度毎に売上累計をSQLで求める方法です。

QlikSenseやExcelなど表計算ソフトで積み上げグラフを作るのは簡単なのですがSQLで取得するにはどうしたらいいでしょうか?

意外と簡単だったので覚書しておきます!

今回の目的

年度・月度毎の売上数値を年度内累計表示する。

元のデータ

売上数値
2020 1 100
2020 2 200
2020 3 300
2021 1 200
2021 2 150
2021 3 250

ゴールのデータ

売上数値 年度内累計
2020 1 100 100
2020 2 200 300
2020 3 300 600
2021 1 200 200
2021 2 150 350
2021 3 250 600

 

SQL文

※予め、元のデータは【WK_TABLE_A】という名称で準備してあるものとします。

今回は【WK_TABLE_A】というテーブルを別名で結合してSQLを作成します。

元テーブルと複製テーブルを結合して累計元となるデータを作成する

結合の条件は、年が同じで月が元となるテーブルが複製テーブルの月以上であることです。

ちょっと分かりにくいですね。

実際のSQL文を見てみましょう。

SELECT A.年,A.月,A.売上数値,B.売上数値

FROM WK_TABLE A A

INNER JOIN WK_TABLE_A B

ON A.年=B.年 AND A.月>=B.月

実行すると以下のようなデータが取得できます。

A.売上数値 B.売上数値
2020 1 100 100
2020 2 200 100
2020 2 200 200
2020 3 300 100
2020 3 300 200
2020 3 300 300
2020 1 200 200
2020 2 150 200
2020 2 150 150
2020 3 250 200
2020 3 250 150
2020 3 250 250

Aテーブルは当月の数値のみ取得、Bテーブルからは当月以前の全データ取得となります。

累計元となるデータをグループ化して目的のデータを取得する

最後に、取得した累計元のデータを、グループ化します。

Aの数値は年月で同じなのでグループ化、Bの数値は月以前の全データを保持しているので合計すると累計になるという訳です。

SELECT A.年,A.月,A.売上数値,SUM(B.売上数値) 年度内累計

FROM WK_TABLE A A

INNER JOIN WK_TABLE_A B

ON A.年=B.年 AND A.月>=B.月

GROUP BY A.年,A.月,A.売上数値

ORDER BY A.年,A.月

注意事項

上記の方法は、年集計のケースです。

年度集計の場合は少し注意が必要です。

通常の年度の場合、4月スタートです。

1月は12月より後の月ですが条件【A.月>=B.月】に当てはまらなくなります。

この場合はあらかじめソート用に1月~3月までは12を加算して13月~15月のようなダミーフィールドを用意する必要があります。

サンプル1

年度 ソート用月 売上数値
2019 2019 10 10 100
2019 2019 11 11 200
2019 2019 12 12 300
2019 2020 1 13 200
2019 2020 2 14 150
2019 2020 3 15 250

SELECT A.年,A.月,A.売上数値,SUM(B.売上数値) 年度内累計

FROM WK_TABLE A A

INNER JOIN WK_TABLE_A B

ON A.年度=B.年度 AND A.ソート用月>=B.ソート用月

GROUP BY A.年度, A.年,A.月,A.ソート用月, A.売上数値

ORDER BY A.年度,A.ソート用月

INNER JOIN句は年度とソート用月のみでOKです!

ABOUT ME
アズビーパートナーズ
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。
今のスキルのままで大丈夫?

あなたのスキル。今のままで大丈夫ですか?

時代のニーズに合った開発スキルを身につけてあなたの価値を高めましょう。

\オンラインでも教室でも学べるおすすめのスクール/

TECH::CAMP(エンジニアスクール)

まずは基礎から・・・という方はProgateUdemyどっとインストールなどがおすすめ!

スキルアップについて詳しくはこちら