[SQL] SELECT句にWHILEを適用する方法

Question

SQL Server 2014 Management Studioを使用しています。

SELECT
	date,
	classification,
	validation,
	CASE WHEN arrivalTime BETWEEN '00:00' AND '00:10' THEN energy
		ELSE 0 END AS '00:00',
	CASE WHEN arrivalTime BETWEEN '00:10' AND '00:20' THEN energy
		ELSE 0 END AS '00:10',
	CASE WHEN arrivalTime BETWEEN '00:20' AND '00:30' THEN energy
		ELSE 0 END AS '00:20',
	・・・
  FROM Energy_date

上記クエリのように１日を10分刻みに分けたタイムスロットで条件分けをした結果を出力しています。
このクエリだと、6（1時間=10分×6）* 24（24時間）= 144個分のカラムの式を書かないといけなく、クエリがかなり長くなっています。

クエリのSELECT句にWHILEなどを適用してスッキリさせる方法はありますでしょうか？
よろしくお願いします。

Comments

[yuba]

Energy_dateは何を主キーに持つテーブルですか？
classification, validationはどういった情報を持つカラムですか？
また、細かいことですがarrivalTime のデータ型はなにになりますか？

Answer 1

WHILEを使ってSQLを書こうとすると以下のような感じで
varcharの変数に少しずつ足して動的SQLとして実行することになります。

ここで１つのvarchar変数の最大文字数は8000文字のため
複数用意して実行時に連結する必要があるので注意してください。

BEGIN

	SET QUOTED_IDENTIFIER OFF

	DECLARE @hour int
	DECLARE @min int
	DECLARE @sql1 varchar(max) = ''
	DECLARE @sql2 varchar(max) = ''
	DECLARE @sql3 varchar(max) = ''
	DECLARE @sql4 varchar(max) = ''
	DECLARE @sql5 varchar(max) = ''
	DECLARE @sql6 varchar(max) = ''
	DECLARE @start char(5)
	DECLARE @end char(5)

	SET @hour = 0
	SET @sql1 = "SELECT"
			 + ' date'
			 + ' ,classification'
			 + ' ,validation'
	WHILE @hour < 24
	BEGIN
		SET @min = 0
		WHILE @min < 60
		BEGIN
			SET @start = RIGHT("00" + CAST(@hour AS VARCHAR), 2) + ":"
						+ RIGHT("00" + CAST(@min AS VARCHAR), 2)
			SET @end = RIGHT("00" + CAST(@hour AS VARCHAR), 2) + ":"
					 + RIGHT("00" + CAST(@min + 9 AS VARCHAR), 2)
			-- 時間の重なっている部分を両方に出すならこちら
			-- SET @end = RIGHT("00" + CAST(@hour AS VARCHAR), 2) + ":"
			-- 		 + RIGHT("00" + CAST((@min + 10) % 60 AS VARCHAR), 2)
			IF @hour < 6
			BEGIN
				SET @sql2 = @sql2
						 + " ,CASE WHEN arrivalTime"
						 + " BETWEEN '" + @start + "' AND '" + @end + "'"
						 + " THEN energy"
						 + " ELSE 0"
						 + " END AS '" + @start + "'"
			END
			ELSE IF @hour < 12
			BEGIN
				SET @sql3 = @sql3
						 + " ,CASE WHEN arrivalTime"
						 + " BETWEEN '" + @start + "' AND '" + @end + "'"
						 + " THEN energy"
						 + " ELSE 0"
						 + " END AS '" + @start + "'"
			END
			ELSE IF @hour < 18
			BEGIN
				SET @sql4 = @sql4
						 + " ,CASE WHEN arrivalTime"
						 + " BETWEEN '" + @start + "' AND '" + @end + "'"
						 + " THEN energy"
						 + " ELSE 0"
						 + " END AS '" + @start + "'"
			END
			ELSE
			BEGIN
				SET @sql5 = @sql5
						 + " ,CASE WHEN arrivalTime"
						 + " BETWEEN '" + @start + "' AND '" + @end + "'"
						 + " THEN energy"
						 + " ELSE 0"
						 + " END AS '" + @start + "'"
			END
			SET @min = @min + 10
		END
		SET @hour = @hour + 1
	END

	SET @sql6 = " FROM Energy_date"

	EXEC(@sql1 + @sql2 + @sql3 + @sql4 + @sql5 + @sql6)
END

Comments

[Daiki-Kawanuma]

正に探していた方法です！！ありがとうございます。

クエリを文字列連結して発行できるとは知りませんでした。とても参考になります。
ただ、varcharが上限8000文字というのがなんとも・・・そこらへんはDBMSで解決してくれれば嬉しいですね；

ご回答を頂き誠にありがとうございました。
ベストアンサーにさせて頂きます。

Answer 2

SQLの時間データを15分単位で集計できますか？

リンク先、ちょっと古いものですが、、、^^;
こちらは15分単位ですが、その部分を10分に換算すれば出来そう。

Comments

[Daiki-Kawanuma]

ご回答ありがとうございます。

リンクを拝見させて頂きました。私の記述が不十分だったのですが、arrivalTimeは10分単位で見ても離散的な値をしておりまして、リンクの方法ですと時間軸で見たときに歯抜けのデータが出力されることになります。

Answer 3

本筋と関係ないところからで恐縮ですが、、、

00:00～00:10 を 00:00
00:10～00:20 を 00:10

２つの境界になる 00:10 は 00:00 と 00:10 のどちらとされたいですか？

恐らく時間の範囲は 00:00 ～ 23:59 だと思いますが
厳密に 00:00:00 ～ 23:59:59 で考えておいた方がよいと思いますので

00:00:00 ～ 00:09:59 を 00:00:00
00:10:00 ～ 00:19:59 を 00:10:00
・
・
・
23:50:00 ～ 23:59:59 を 23:50:00

なのかなーと思いまして

・・・というのを踏まえて

while での解決方法はさっぱりわかりませんが、時間幅（hh:m0:ss～hh:m9:ss）と欲しい結果（hh:m0:00）の関係を考えると、単純に arrivalTime（恐らくtime型？） の分の1桁から下を０に丸めれば・・・ってのはダメしょうか？

ぜんぜん見当違いのことを言ってたらご容赦ください

Comments

[Daiki-Kawanuma]

BETWEENは>= AND <=ですよね！；
複数時刻に含める意図はないので適切に境界を設定する必要がありました。
ご意見ありがとうございます。

私の記述が不十分だったのですが、arrivalTimeは10分単位で見ても離散的な値をしておりまして、arrivalTimeを用いて集計を行った場合、時間軸で見たときに歯抜けのデータが出力されてしまいます。
記述不十分で申し訳ありませんでした