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

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

ただいまの
回答率

89.97%

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

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 3,977

Daiki-Kawanuma

score 27

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yuba

    2015/10/07 12:09

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

    キャンセル

回答 3

checkベストアンサー

+1

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

ここで1つの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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/10/08 17:54

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

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

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

    キャンセル

0

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

00:00~00:10 を 00:00
00:10~00:20 を 00:10

2つの境界になる 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桁から下を0に丸めれば・・・ってのはダメしょうか?

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/10/08 17:49

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

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

    キャンセル

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/10/08 17:51

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

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

    キャンセル

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

  • ただいまの回答率 89.97%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる