none
連続値を取得するクエリ RRS feed

  • 質問

  • T-SQLで、テーブルを参照せず、1から30までの雨に連続する値のをもつ30行を取得するには、どのようなクエリになりますでしょうか?

    やりたいことは、今日から過去30日間の集計データを表にするということで、そのために、今日から過去30日の日付の行をもつ日付列を用意して、Left Joinしようとしています。

    よろしくお願いします。

    2011年5月5日 9:18

回答

  • 一応、SQL Server や Oracle で使える再帰クエリの例を1つ

    -- 日付のみ
    WITH DAYS AS 
    (SELECT CURRENT_TIMESTAMP AS YMD
     UNION ALL
     SELECT DATEADD(Y, -1, YMD)
      FROM DAYS
     WHERE DAYS.YMD >= DATEADD(y, -30, CURRENT_TIMESTAMP)
    )
    SELECT * FROM DAYS;
    
    -- JOIN する場合
    WITH DAYS AS 
    (SELECT CURRENT_TIMESTAMP AS YMD
     UNION ALL
     SELECT DATEADD(Y, -1, YMD)
      FROM DAYS
     WHERE DAYS.YMD >= DATEADD(y, -30, CURRENT_TIMESTAMP)
    )
    SELECT * FROM Table1 LEFT JOIN DAYS ON ...;
    
    
    
    2011年5月6日 4:03
  • 再帰SQLを使ってみました。
    with rec(Val,Rest) as(
    select cast(GetDate() as date),30
    union all
    select DateAdd(day,-1,Val),Rest-1
     from rec
     where Rest-1>0)
    select * from rec
    go
    
    Val     Rest
    ---------- ----
    2011-05-06  30
    2011-05-05  29
    2011-05-04  28
    2011-05-03  27
    2011-05-02  26
    2011-05-01  25
    2011-04-30  24
    2011-04-29  23
    2011-04-28  22
    2011-04-27  21
    2011-04-26  20
    2011-04-25  19
    2011-04-24  18
    2011-04-23  17
    2011-04-22  16
    2011-04-21  15
    2011-04-20  14
    2011-04-19  13
    2011-04-18  12
    2011-04-17  11
    2011-04-16  10
    2011-04-15   9
    2011-04-14   8
    2011-04-13   7
    2011-04-12   6
    2011-04-11   5
    2011-04-10   4
    2011-04-09   3
    2011-04-08   2
    2011-04-07   1
    
    
    2011年5月6日 21:10
  • これだと再帰無しでいけちゃったりします

     

     

    SELECT TOP 30 
    	ROW_NUMBER() OVER (ORDER BY object_id) AS [RowNumber],
    	DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id) * -1 ,GETDATE()) AS [Date]
    FROM
    	sys.all_columns	 
    
    	
    

     


    • 編集済み Tetsuaki.Uchida 2011年5月7日 14:18 誤字修正「再起→再帰」、プロファイル変更
    • 回答としてマーク 山本春海Moderator 2011年5月20日 6:32
    2011年5月7日 14:08

すべての返信

  • WHERE句で過去30日分を抽出すればJOINは不要になると思います。

     

    SELECT
     *
    FROM
     [集計データテーブル]
    WHERE
     DATEDIFF(day, [日付列], GETDATE()) <= 30
    

     


    Blog:プログラマーな日々 http://d.hatena.ne.jp/JHashimoto/
    2011年5月5日 10:13
  • おそらく、データのない日付も 0 や NULL に集計したいので、LEFT JOIN が必要になるのでしょう。

    なぜテーブルを参照したくないかがわからないのですが、日付の候補を保持したテーブルを作成するのが手軽ですよ。
    (一応、"SQL カレンダー 作成" などで検索すると、山のように方法が出てきます。)

     

    2011年5月6日 3:52
  • 一応、SQL Server や Oracle で使える再帰クエリの例を1つ

    -- 日付のみ
    WITH DAYS AS 
    (SELECT CURRENT_TIMESTAMP AS YMD
     UNION ALL
     SELECT DATEADD(Y, -1, YMD)
      FROM DAYS
     WHERE DAYS.YMD >= DATEADD(y, -30, CURRENT_TIMESTAMP)
    )
    SELECT * FROM DAYS;
    
    -- JOIN する場合
    WITH DAYS AS 
    (SELECT CURRENT_TIMESTAMP AS YMD
     UNION ALL
     SELECT DATEADD(Y, -1, YMD)
      FROM DAYS
     WHERE DAYS.YMD >= DATEADD(y, -30, CURRENT_TIMESTAMP)
    )
    SELECT * FROM Table1 LEFT JOIN DAYS ON ...;
    
    
    
    2011年5月6日 4:03
  • 再帰SQLを使ってみました。
    with rec(Val,Rest) as(
    select cast(GetDate() as date),30
    union all
    select DateAdd(day,-1,Val),Rest-1
     from rec
     where Rest-1>0)
    select * from rec
    go
    
    Val     Rest
    ---------- ----
    2011-05-06  30
    2011-05-05  29
    2011-05-04  28
    2011-05-03  27
    2011-05-02  26
    2011-05-01  25
    2011-04-30  24
    2011-04-29  23
    2011-04-28  22
    2011-04-27  21
    2011-04-26  20
    2011-04-25  19
    2011-04-24  18
    2011-04-23  17
    2011-04-22  16
    2011-04-21  15
    2011-04-20  14
    2011-04-19  13
    2011-04-18  12
    2011-04-17  11
    2011-04-16  10
    2011-04-15   9
    2011-04-14   8
    2011-04-13   7
    2011-04-12   6
    2011-04-11   5
    2011-04-10   4
    2011-04-09   3
    2011-04-08   2
    2011-04-07   1
    
    
    2011年5月6日 21:10
  • これだと再帰無しでいけちゃったりします

     

     

    SELECT TOP 30 
    	ROW_NUMBER() OVER (ORDER BY object_id) AS [RowNumber],
    	DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id) * -1 ,GETDATE()) AS [Date]
    FROM
    	sys.all_columns	 
    
    	
    

     


    • 編集済み Tetsuaki.Uchida 2011年5月7日 14:18 誤字修正「再起→再帰」、プロファイル変更
    • 回答としてマーク 山本春海Moderator 2011年5月20日 6:32
    2011年5月7日 14:08
  • ありがとうございます。

    この回答は要件を満たしています。とても参考になりました。

    2011年5月9日 6:58
  • ありがとうございます。

    この回答は要件を満たしています。とても参考になりました。

    2011年5月9日 6:58
  • ありがとうございます。

    この方法は、非常に楽に使用できますね。

    2011年5月9日 6:59
  • 元の質問の意図が不明確なので sys.all_columns を参照しているのは、「T-SQLで、テーブルを参照せず、」という前提を満たしてないかもしれません。また、object 系のメタデータビューは SELECT または INSERT の権限のある対象しか抽出されないので、(多くの場合で実質的に大丈夫な行数なのでしょうけど) all_columns から30行以上の結果が返るかどうかも環境によっては保障されないことに注意が必要ですね。「T-SQLで、テーブルを参照せず、」というのが、そういった小さな権限で接続して集計を実施することが原因になっている場合には、sys.all_columns を参照すると30行未満の結果しか帰らない可能性が高まりますね。
    2011年5月9日 7:20
  • 質問者です。

    補足ありがとうございます。

    環境を限定できない場合は、再帰クエリを使用したほうがよいですね。

    「テーブルを参照せず」の意図しては、「連続値を値とするテーブルを用意することなく」ということです。

    2011年5月11日 4:03