質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

4回答

3191閲覧

SQLチューニング方法について 3年分の売上から各年月に振り分ける!

seesaajira-

総合スコア107

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

1グッド

0クリップ

投稿2019/02/15 08:13

売上テーブルからグループ毎に各月の金額を推移表として表示したいです。
店舗グループ事に年度別、月別に集計を行う。

店舗グループテーブルと売上テーブルをJOINする方法が思い浮かばず
店舗グループテーブルを読み込みながら、売上テーブルを集計する方法を取っています。

集計に時間がかかってしまうので、チューニングしたいのですが、何か良い方法がございますでしょうか?

言語:VB.NET
DB:SQLSERVER 2012

【出力イメージ】

|グループコード|店舗名|年度|4月|5月|6月|・・・|1月|2月|3月|
|:--|:--:|
|1|Aスーパーグループ|2019年度|249695
|||2018年度|||||||293470
|||2017年度|4185|1540|335575||||1760
|2|B商店グループ|2019年度|
|||2018年度|
|||2017年度|
|3|Cスーパーグループ|2019年度|1250
|||2018年度|
|||2017年度|
|4|Dスーパーグループ|2019年度|2420
|||2018年度|
|||2017年度|

【売上テーブル】

売上日店舗コード売上金額
2017/04/0100014185
2017/05/0100011540
2017/06/010002335575
2018/01/0200012385
2018/02/020001244175
2018/03/0200011760
2019/01/0300011320
2019/02/0300013310
2019/03/030001293470
2019/04/040001249695
2019/05/0400012420
2019/04/0400071250
2019/04/0701012420

【店舗グループテーブル】

|グループコード|店舗コード|店舗名|
|:--|:--:|
|1|0001,0002,0003|Aスーパーグループ|
|2|0004,0005,0008|B商店グループ|
|3|0006,0007,0009|Cスーパーグループ|
|4|0101,0102,0103|Dスーパーグループ|

店舗グループテーブルを読み込み
グループ毎に以下のサブルーチンに店舗コードをパラメータで渡し、
3年分の売上金額を集計した結果を取得しています。

SUB 売上テーブル集計サブルーチン(店舗コードパラメータ) SELECT グループコード ,SUM(当年4月売上金額),SUM(前年4月売上金額),SUM(前々年4月売上金額)・・省略・・,SUM(当年3月売上金額),SUM(前年3月売上金額),SUM(前々年3月売上金額) FROM ( SELECT グループコード,SUM(売上金額) AS 当年4月売上金額,0 AS 前年4月売上金額,0 AS 前々年4月売上金額・・省略・・0 AS 当年3月売上金額,0 AS 前年3月売上金額,0 AS 前々年3月売上金額 FROM 売上テーブル WHERE 売上日 BETWEEN '2019/04/01' AND '2019/04/30' AND 店舗コード IN(店舗コードパラメータ) UNION ALL SELECT グループコード,0 AS 当年4月売上金額,0 AS 前年4月売上金額,0 AS 前々年4月売上金額, SUM(売上金額) AS 当年5月売上金額,0 AS 前年5月売上金額,0 AS 前々年5月売上金額・・省略・・0 AS 当年3月売上金額,0 AS 前年3月売上金額,0 AS 前々年3月売上金額 FROM 売上テーブル WHERE 売上日 BETWEEN '2019/05/01' AND '2019/05/31' AND 店舗コード IN(店舗コードパラメータ) '省略、3月分までUNIONでクエリをつなぐ ) AS TMP GROUP BY TMP.グループコード
bochan2👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tanat

2019/02/15 08:40

店舗グループテーブルの設計を見直すのが真っ当なチューニングかと思いますが、テーブル構造を変更せずにSQLだけで何とかしたいという意図の質問ですか?
seesaajira-

2019/02/15 08:54

やはり店舗コードをレコード化するべきですか? 調べたところIN句よりもJoinの方がパフォーマンスアップのようですね? 既に完成しているシステムなので、この店舗グループテーブルでどうにかなりませんでしょうか?
sazi

2019/02/15 13:33

店舗グループテーブルの店舗コードの型は何ですか?
guest

回答4

0

ベストアンサー

店舗グループテーブル店舗コードの分解はSTRING_SPLIT を使用します。
クロス集計部分はUNIONにしなくてもsum()内でcase文判定すれば簡潔にできそうです。
※結果の年度について不足があったので追加

SQL

1select グループコード, 店舗グループ, t1.年度 2 , sum(case 月度 when 4 then 売上金額 else 0 end) as 売上43 , sum(case 月度 when 5 then 売上金額 else 0 end) as 売上54 , sum(case 月度 when 6 then 売上金額 else 0 end) as 売上65/* 6 , sum(case 月度 when 7 then 売上金額 else 0 end) as 売上7月 7 , sum(case 月度 when 8 then 売上金額 else 0 end) as 売上8月 8 , sum(case 月度 when 9 then 売上金額 else 0 end) as 売上9月 9 , sum(case 月度 when 10 then 売上金額 else 0 end) as 売上10月 10 , sum(case 月度 when 11 then 売上金額 else 0 end) as 売上11月 11 , sum(case 月度 when 12 then 売上金額 else 0 end) as 売上12月 12*/ 13 , sum(case 月度 when 1 then 売上金額 else 0 end) as 売上114 , sum(case 月度 when 2 then 売上金額 else 0 end) as 売上215 , sum(case 月度 when 3 then 売上金額 else 0 end) as 売上316from ( 17 select グループコード, 店舗名 as 店舗グループ, value as 店舗コード, 年度 18 from 店舗グループ CROSS APPLY STRING_SPLIT(店舗コード, ',') 19 cross join ( 20 select cast(value as int) as 年度 from STRING_SPLIT('2017,2018,2019',',') 21 ) 対象年度 22 23 ) as t1 left join ( 24 select *, year(dateadd(month,-3,売上日)) as 年度 , month(売上日) as 月度 from 売上 25-- where 売上日 between '2017/04/01' and '2019/03/31' 26 ) t2 27 on t1.店舗コード=t2.店舗コード and t1.年度=t2.年度 28group by グループコード, 店舗グループ, t1.年度 29order by グループコード, t1.年度 desc 30

結果

グループコード店舗グループ年度4月5月6月1月2月3月
1Aスーパーグループ201924969524200000
1Aスーパーグループ201800013203310293470
1Aスーパーグループ20174185154033557523852441751760
2B商店グループ2019000000
2B商店グループ2018000000
2B商店グループ2017000000
3Cスーパーグループ2019125000000
3Cスーパーグループ2018000000
3Cスーパーグループ2017000000
4Dスーパーグループ2019242000000
4Dスーパーグループ2018000000
4Dスーパーグループ2017000000
```DATA
create table 売上(売上日 date, 店舗コード varchar(4), 売上金額 int);
insert into 売上 values
('2017/04/01', '0001', 4185 )
,('2017/05/01', '0001', 1540 )
,('2017/06/01', '0002', 335575 )
,('2018/01/02', '0001', 2385 )
,('2018/02/02', '0001', 244175 )
,('2018/03/02', '0001', 1760 )
,('2019/01/03', '0001', 1320 )
,('2019/02/03', '0001', 3310 )
,('2019/03/03', '0001', 293470 )
,('2019/04/04', '0001', 249695 )
,('2019/05/04', '0001', 2420 )
,('2019/04/04', '0007', 1250 )
,('2019/04/07', '0101', 2420 )
;
create table 店舗グループ(グループコード integer, 店舗コード varchar(255),店舗名 varchar(255));
insert into 店舗グループ values
(1, '0001,0002,0003', 'Aスーパーグループ' )
,(2, '0004,0005,0008', 'B商店グループ' )
,(3, '0006,0007,0009', 'Cスーパーグループ' )
,(4, '0101,0102,0103', 'Dスーパーグループ' )
;
```
追記
--
SQLServer2012という事で、失礼しました。
一時テーブルなら2012でも対応可能かと思いますので、検討してみて下さい。
店舗グループを正規化した状態の一時テーブルであれば、インデックスも作れますので。
一時テーブル(#テーブル)を使用する

投稿2019/02/15 14:20

編集2019/02/18 00:22
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

seesaajira-

2019/02/16 00:09

sazi様、ありがとうございます。 こんなに簡潔に書けるんですね。 感動しました。 月曜日に試してみたいと思います。
sazi

2019/02/16 01:21

チューニングが目的ということなので、上記のSTRING_SPLITで展開したデータ件数によっては効果が得られない場合もあるかと思います。 実行計画を確認して、店舗グループを展開した一時テーブルをインデックス付きで作成する方法も視野に入れてみて下さい。
seesaajira-

2019/02/18 00:01

SQLSERVER 2012なのでSQLSER 2016に上げて STRING_SPLIT関数が使用できるようにします。 検証データまで載せて頂きありがとうございます。 SAZIさんのSQLを参考に進めていきます。
sazi

2019/02/18 00:23

SQLSERVER 2012について見落としてました。すみません。 追記したので、確認下さい。
seesaajira-

2019/02/18 00:37

saziさんありがとうございます。 2016に上げる予定だったので、勉強になりました。 やはりパフォーマンスアップを上げる為、一時テーブルに挑戦してみます。 また質問する事があると思いますが宜しくお願い致します。
guest

0

売上テーブルをグループコードで集計するのであれば
売上テーブルにグループコードを埋め込むのが一般的です

投稿2019/02/15 09:53

yambejp

総合スコア114583

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

seesaajira-

2019/02/15 11:10

そうなんですよね~。 最初からグループコードのカラム追加しておけば問題なかったんですよね。 非常に反省しています。
guest

0

インデックスがなければ、インデックスを作成してみてはどうでしょう。
インデックスが作成済みなら、統計情報の更新を試してみてもいいかもしれません。

https://blogs.msdn.microsoft.com/jpsql/2013/09/03/sql-server-5/

投稿2019/02/15 09:40

編集2019/02/18 05:46
ware

総合スコア27

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

seesaajira-

2019/02/15 11:11

ありがとうございます。 統計情報の更新やってみます。
ware

2019/02/18 05:47

ご指摘ありがとうございます。 リンク修正しました。
guest

0

SQLをシンプルにパフォーマンス良く実行できるように、データベースのテーブルはデータベースの正規化 に沿って設計します。
通常、第3正規化まで行います。第1正規化で繰り返しを排除します。
通常、第3正規化まで行います。第1正規化で繰り返し(今回は配列になっている店舗コード)を個々にバラします。

投稿2019/02/15 09:09

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

seesaajira-

2019/02/15 11:19

やはり配列はまずかったですね。 現在この店舗グループテーブルを多方面で使用中ですので、修正可能か確認してみます。 SQL初心者としてはWhere の IN句に配列コードでグループ分け出来た時は非常に感動したのですが、 勉強不足でした。 反省しております。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問