連続値を取得するクエリ

Question

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

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

よろしくお願いします。

Answer 1

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

 

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

 

---

Blog:プログラマーな日々 http://d.hatena.ne.jp/JHashimoto/

Answer 2

おそらく、データのない日付も 0 や NULL に集計したいので、LEFT JOIN が必要になるのでしょう。

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

 

Answer 3

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

-- 日付のみ
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 ...;

Answer 4

再帰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

Answer 5

これだと再帰無しでいけちゃったりします

 

 

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	 

	

 

Answer 6

ありがとうございます。

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

Answer 7

ありがとうございます。

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

Answer 8

ありがとうございます。

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

Answer 9

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

Answer 10

質問者です。

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

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

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