実現したいこと

75期(前期:2022年4月～2023年3月)、76期(今期:2023年4月～2024年3月)それぞれの売上金額を合計した項目を作成したい。最終的には今期売上金額と前期売上金額を出して前期比を計算させたいが、まずは76期(今期)の合計金額を出力するクエリを教えてほしい。また期が変わった際には最新の期を今期として計算させたい。

前提

期首が毎年4月であり1期の期間は4月～翌年の3月である。
テーブル名はURIAGE_DATA(下記がデータです)とします。
SHIWAKE_KI,SHIWAKE_YY,SHIWAKE_MONTHは文字項目、
URIAGE_GAKは数字項目です。

SHIWAKE_KI SHIWAKE_YY SHIWAKE_YYMM SHIWAKE_MONTH URIAGE_GAK
75 2023 202301 01 10
75 2023 202302 02 11
75 2023 202303 03 12
76 2023 202304 04 13
76 2023 202305 05 14
76 2023 202306 06 15
76 2023 202307 07 16
76 2023 202308 08 17
76 2023 202309 09 18
76 2023 202310 10 19
76 2023 202311 11 20
76 2023 202312 12 21
76 2024 202401 01 22
76 2024 202402 02 23
76 2024 202403 03 24
77 2024 202404 04 25
77 2024 202405 05 26
77 2024 202406 06 27
77 2024 202407 07 28
77 2024 202408 08 29
77 2024 202409 09 30
77 2024 202410 10 31
77 2024 202411 11 32
77 2024 202412 12 33
77 2025 202501 01 34
77 2025 202502 02 35
77 2025 202503 03 36
78 2025 202504 04 37
78 2025 202505 05 38

発生している問題・エラーメッセージ

SELECT SUM(CASE WHEN　SHIWAKE_KI = 76 THEN URIAGE_GAK ELSE 0 END) AS 今期売上金額　FROM URIAGE_DATAで実現できるのだが、例えば2024年4月に期がかわると77期が今期となるのでそのたびに76の部分を変更しなくてはならないので、76の部分をリテラルでは書きたくないです。

SELECT SUM( CASE WHEN (SHIWAKE_MONTH IN ('04', '05', '06', '07', '08', '09', '10', '11', '12') AND CAST(SHIWAKE_KI AS NUMERIC) = CAST(SHIWAKE_YY AS NUMERIC) - 1947)
OR (SHIWAKE_MONTH IN ('01', '02', '03') AND CAST(SHIWAKE_KI AS NUMERIC) = CAST(SHIWAKE_YY AS NUMERIC) - 1948) THEN URIAGE_GAK
ELSE 0
END
) AS 今期売上金額
FROM URIAGE_DATA
だと2023年1月～3月(75期)の売上金額も今期売上金額に合計されてしまう。
2023年1月の場合(SHIWAKE_MONTH IN ('01', '02', '03') AND CAST(SHIWAKE_KI AS NUMERIC) = CAST(SHIWAKE_YY AS NUMERIC) - 1948)に入っていいくがSHIWAKE_KIが75でSHIWAKE_YYが2023なのでCAST(SHIWAKE_YY AS NUMERIC) - 1948)の計算結果が75となりTURUなので今期(76期)でないが合計金額の対象となり計算されてしまっていると思われます。

システム日付を利用したらできるのかなど考えましたが、
現状では実現したいことができませんでした。
ご教授頂きますようお願いいたします。

該当のソースコード

SELECT SUM(CASE WHEN　SHIWAKE_KI = 76 THEN URIAGE_GAK ELSE 0 END) AS 今期売上金額
FROM URIAGE_DATA

試したこと

発生している問題・エラーメッセージに記載

補足情報（FW/ツールのバージョンなど）

logres_Fan

2023/08/19 01:09

期ごとにグループ化して集計しないの？

hqf00342

2023/08/19 02:24

「期が変わった際には」というのは「期」をシステム時刻（今日の日付）で計算したいということでしょうか？

fire50

2023/08/20 00:04

logres_Fanさんご質問ありがとうございます。 group by で確かにできるとは思うのですが最終的に今期・前期の項目を出して計算させたいです。 ↓イメージのクエリです(～の部分で指定させたいです)。 SELECT　～　　as 今期売上合計金額, 　　　　　～　　as 前期売上合計金額, 　　　　　今期売上合計実績/前期売上合計実績 as 前期比 from URIAGE_DATA

fire50

2023/08/20 00:12

hqf00342 さんご質問ありがとうございます。素人の考えではありますが内容に記載していました、76の部分が検索する時のシステム日付で変わればいいなと思っています。 ↓記載内容 SELECT SUM(CASE WHEN　SHIWAKE_KI = 76 THEN URIAGE_GAK ELSE 0 END) AS 今期売上金額　FROM URIAGE_DATA イメージ SELECT SUM(CASE WHEN　SHIWAKE_KI = 76　の「76」の部分が検索する時に変わる。例:2023年3月に検索する時は75 2023年8月に検索する時は76 2024年1月に検索する時は76 2024年4月に検索する時は77 2025年4月に検索する時は78 現状のURIAGE_DATAにほかのデータ(例えば年月日があるデータ)があれば実現可能とかあれば、ご指摘頂き、それをもとにクエリ検討頂くと助かります。よろしくお願いいたします。

行動規範の内容に同意します

回答2件

タグにMySQLとSQLServer両方が指定されていますが、どちらのDBMSをご利用ですか? またバージョン情報などはわかりますか? SQLはDBMSやバージョンごとに方言が大きく異なるので、DBMS名・バージョン名を明記していただくと、より正確な回答を得られると思います (なお以下の回答はMySQL8で稼働確認しています)

やりたいことの最終形をうまく汲み取れているかわからないのですが、SHIWAKE_KIごとにURIAGE_GAKの合計とその前年比を出したいという認識で正しいでしょうか? であれば、以下のようなSQLで実現できるかと思います。

SQL
1SELECT SHIWAKE_KI, URIAGE_GAK_SUM, URIAGE_GAK_SUM - LAG(URIAGE_GAK_SUM) OVER (ORDER BY SHIWAKE_KI) AS 前期比
2FROM (
3  SELECT SHIWAKE_KI, SUM(URIAGE_GAK) AS URIAGE_GAK_SUM
4  FROM URIAGE_DATA
5  GROUP BY SHIWAKE_KI
6) X

サンプルデータをインプットにこのSQLを実行すると、以下のような結果になります。

SHIWAKE_KI	URIAGE_GAK_SUM	前期比
75	33	NULL
76	222	189
77	366	144
78	75	-291

2023-08-19 17:20追記

「回答へのコメント」にあるイメージを実現する方法はいくつかあると思いますが、たとえば以下のようなSQLはどうでしょうか。

SQL
1SELECT SUM(URIAGE_GAK)
2FROM URIAGE_DATA
3WHERE SHIWAKE_YYMM <= DATE_FORMAT(CURDATE(), '%Y%m')
4AND SHIWAKE_KI = CASE WHEN MONTH(CURDATE())<=3 THEN YEAR(CURDATE())-1948 ELSE YEAR(CURDATE())-1947 END;

2023-08-20 13:20追記

いくつか方法あると記載頂きましたが、SELECT句の中で実現可能な方法ありますか？
SELECT ～ as 今期売上合計金額, ～ as 前期売上合計金額, 今期売上合計実績/前期売上合計実績 as 前期比 from URIAGE_DATA
としたいです。

無理やりこの形式に当てはめるのであれば、以下のようなSQLになるかと思います。

SQL
1SELECT
2    SUM(CASE WHEN SHIWAKE_YYMM <= DATE_FORMAT(CURDATE(), '%Y%m') AND SHIWAKE_KI = (YEAR(CURDATE()) - CASE WHEN MONTH(CURDATE()) <= 3 THEN 1948 ELSE 1947 END) THEN URIAGE_GAK ELSE 0 END) AS 今期売上合計金額,
3    SUM(CASE WHEN SHIWAKE_YYMM <= DATE_FORMAT(CURDATE(), '%Y%m') AND SHIWAKE_KI = (YEAR(CURDATE()) - CASE WHEN MONTH(CURDATE()) <= 3 THEN 1949 ELSE 1948 END) THEN URIAGE_GAK ELSE 0 END) AS 前期売上合計金額,
4    SUM(CASE WHEN SHIWAKE_YYMM <= DATE_FORMAT(CURDATE(), '%Y%m') AND SHIWAKE_KI = (YEAR(CURDATE()) - CASE WHEN MONTH(CURDATE()) <= 3 THEN 1948 ELSE 1947 END) THEN URIAGE_GAK ELSE 0 END)
5  / SUM(CASE WHEN SHIWAKE_YYMM <= DATE_FORMAT(CURDATE(), '%Y%m') AND SHIWAKE_KI = (YEAR(CURDATE()) - CASE WHEN MONTH(CURDATE()) <= 3 THEN 1949 ELSE 1948 END) THEN URIAGE_GAK ELSE 0 END) AS 前期比
6FROM URIAGE_DATA;

投稿2023/08/19 01:36

編集2023/08/20 04:25

neko_the_shadow

総合スコア2374

fire50

2023/08/19 03:38

早速の回答ありがとうございます。 VerticaというDWHを使用していました。情報が違っており申し訳ありません。 https://www.ashisuto.co.jp/product/category/database/vertica/ ただMySQL（バージョンわかりませんが古くないので最新だと思います）も使用しています。したいこととしては76期（2023年4月〜2024年3月）内に検索した時に今期売上金額という項目に検索時点の売上金額が表示されるイメージです。イメージ1（2023年8月に検索した時）今期売上金額 75 イメージ2（2024年2月に検索した時）今期売上金額 198 イメージ3（2025年5月に検索した時）今期売上金額 51 よろしくお願いします！

fire50

2023/08/20 00:00

解答ありがとうございます。いくつか方法あると記載頂きましたが、SELECT句の中で実現可能な方法ありますか？ SELECT　～　　as 今期売上合計金額, ～　　as 前期売上合計金額, 今期売上合計実績/前期売上合計実績 as 前期比 from URIAGE_DATA としたいです。度々で申し訳ありませんがご教授頂きますようお願いいたします。

fire50

2023/08/20 00:15

他の方のご質問の部分でも記載したのですが記載していたクエリの76の部分が検索する時のシステム日付で変われば実現可能なのかなと思っています。 ↓記載内容 SELECT SUM(CASE WHEN　SHIWAKE_KI = 76 THEN URIAGE_GAK ELSE 0 END) AS 今期売上金額　FROM URIAGE_DATA イメージ SELECT SUM(CASE WHEN　SHIWAKE_KI = 76　の「76」の部分が検索する時に変わる。例:2023年3月に検索する時は75 2023年8月に検索する時は76 2024年1月に検索する時は76 2024年4月に検索する時は77 2025年4月に検索する時は78 現状のURIAGE_DATAにほかのデータ(例えば年月日があるデータ)があれば実現可能とかあれば、ご指摘頂き、それをもとにクエリ検討頂くと助かります。よろしくお願いいたします。

行動規範の内容に同意します

単純にSHIWAKE_KI ( SHIWAKE_YYMM も？)を自動計算させたいということでしたらMySQLであれば生成列を使います

SQL
1create table tbl(
2SHIWAKE_YY int(4),
3SHIWAKE_MONTH int(2) unsigned zerofill,
4URIAGE_GAK int,
5SHIWAKE_KI int as (SHIWAKE_YY-1947-(SHIWAKE_MONTH<4)),
6SHIWAKE_YYMM int as (SHIWAKE_YY*POW(10,2)+SHIWAKE_MONTH),
7index(URIAGE_GAK,SHIWAKE_KI)
8);
9
10insert into tbl(SHIWAKE_YY,SHIWAKE_MONTH,URIAGE_GAK) values
11(2023, 1,10),
12(2023, 2,11),
13(2023, 3,12),
14(2023, 4,13),
15(2023, 5,14),
16(2023, 6,15),
17(2023, 7,16),
18(2023, 8,17),
19(2023, 9,18),
20(2023,10,19),
21(2023,11,20),
22(2023,12,21),
23(2024, 1,22),
24(2024, 2,23),
25(2024, 3,24),
26(2024, 4,25),
27(2024, 5,26),
28(2024, 6,27),
29(2024, 7,28),
30(2024, 8,29),
31(2024, 9,30),
32(2024,10,31),
33(2024,11,32),
34(2024,12,33),
35(2025, 1,34),
36(2025, 2,35),
37(2025, 3,36),
38(2025, 4,37),
39(2025, 5,38);

集計

select
1SHIWAKE_KI,
2SUM(URIAGE_GAK) as URIAGE_GAK_SUM,
3SUM(URIAGE_GAK)-(SELECT SUM(URIAGE_GAK) FROM tbl WHERE SHIWAKE_KI=t1.SHIWAKE_KI-1) as YOY
4from tbl as t1
5group by SHIWAKE_KI
6``

投稿2023/08/21 03:41