2つの日付範囲(前の日付と現在の日付)が与えられ、これら2つの日付範囲の値を比較したい。
比較方法:
- ある日付範囲のn日目の値と別の日付範囲のn日目の値を比較します(たとえば、前の日付範囲の1日目の値は現在の日付範囲の1日目の値と比較されます)
- 日付がテーブルに存在しない場合は、値にゼロを表示します。
- 2つの日付範囲が同じ長さでない場合、ギャップを埋めるためにNULLとゼロを表示します。
例
DBテーブル「データ」:
date | value
-------------|------
2018-01-01 | 3
2018-01-02 | 5
2018-01-03 | 8
2018-01-04 | 6
2018-02-04 | 4
2018-02-05 | 2
2018-02-06 | 7
2018-02-07 | 0
指定された日付範囲:(現在)2018-02-04から2018-02-07、(前)2018-01-01から2018-01-03
望ましい出力:
curDate | curValue | preDate | preValue
-------------|----------|-------------|---------
2018-02-04 | 4 | 2018-01-01 | 3
2018-02-05 | 2 | 2018-01-02 | 5
2018-02-06 | 7 | 2018-01-03 | 8
2018-02-07 | 0 | NULL | 0
私は今、結合条件で立ち往生しており、私の現在のSQLは次のようなものです:
DECLARE @currentStartDateTime datetime = '2018-02-04 00:00:00'
DECLARE @currentEndDateTime datetime = '2018-02-07 23:59:59'
DECLARE @previousStartDateTime datetime = '2018-01-01 00:00:00'
DECLARE @previousEndDateTime datetime = '2018-01-03 23:59:59'
SELECT cur.[date] AS [curDate]
,ISNULL(cur.[total], 0) AS [curTotal]
,pre.[date] AS [preDate]
,ISNULL(pre.[total], 0) AS [preTotal]
FROM (
SELECT * FROM [data]
WHERE [date] BETWEEN @currentStartDateTime AND @currentEndDateTime
) cur
FULL OUTER JOIN (
SELECT * FROM [data]
WHERE [date] BETWEEN @previousStartDateTime AND @previousEndDateTime
) pre
ON cur.[date] = DATEADD(day, 1, pre.[date]) -- <<< Stuck in this part
回答 2 件
尋ねるときは、すべてのDDLと挿入を一緒に含めることをお勧めします。
DROP TABLE data; CREATE TABLE data ( date DATE, value INT ); GO INSERT INTO data VALUES ('2018-01-01', 3), ('2018-01-02', 5), ('2018-01-03', 8), ('2018-01-04', 6), ('2018-02-04', 4), ('2018-02-05', 2), ('2018-02-06', 7), ('2018-02-07', 0); DECLARE @currentStartDateTime DATETIME = '2018-02-04 00:00:00'; DECLARE @currentEndDateTime DATETIME = '2018-02-07 23:59:59'; DECLARE @previousStartDateTime DATETIME = '2018-01-01 00:00:00'; DECLARE @previousEndDateTime DATETIME = '2018-01-03 23:59:59'; SELECT a.date curDate, a.value curValue, b.date preDate, COALESCE(b.value, 0) preValue FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY date) rn FROM data WHERE date BETWEEN @currentStartDateTime AND @currentEndDateTime ) a LEFT JOIN ( SELECT *, ROW_NUMBER() OVER (ORDER BY date) rn FROM data WHERE date BETWEEN @previousStartDateTime AND @previousEndDateTime ) b ON a.rn = b.rn;
最初に行うことは、日付範囲に共通のテーブル式を作成することです。各式には、データ値を持つ列と行番号値を持つ列の2つの列があります。この方法では、n番目の値で結合するのが非常に簡単になります。
だからここに私の提案された解決策があります:
まず、サンプルテーブルを作成してデータを入力します(お願いします 将来の質問にこのステップを保存してください)
今、あなたの
@currentStartDateTime
を変更しました2018-02-04
から2018-02-03
へ 、 また、テーブルにない行も返すようにします(お願いします サンプルデータがすべての要件を満たしていることを確認してください)さて、すべてのステップを詳細に表示したかったので、私の解決策は非常に面倒です。 あなたはそれを単純化できるかもしれません。
最初に、最大日付差を日数で計算します。
次に、1からその差+ 1までの数値cteを作成します。
次に、各範囲のカレンダーテンプレートを作成し、
次に、範囲間の完全結合を行うための最後のcte、
そして、その最後のcteからの選択がデータテーブルに2回結合されました。
結果:(
@currentStartDateTime
質問のものとは異なります)rextesterでライブデモを見ることができます。