bugfix> sql > 投稿

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 件
  • 最初に行うことは、日付範囲に共通のテーブル式を作成することです。各式には、データ値を持つ列と行番号値を持つ列の2つの列があります。この方法では、n番目の値で結合するのが非常に簡単になります。

    だからここに私の提案された解決策があります:

    まず、サンプルテーブルを作成してデータを入力します(お願いします 将来の質問にこのステップを保存してください)

    DECLARE @data As Table
    (
        [date] DATE,
        [value] INT
    );
    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);
    
    

    今、あなたの @currentStartDateTime を変更しました   2018-02-04 から   2018-02-03 へ 、 また、テーブルにない行も返すようにします(お願いします サンプルデータがすべての要件を満たしていることを確認してください)

    DECLARE @currentStartDateTime  datetime   = '2018-02-03 00:00:00',
            @currentEndDateTime    datetime   = '2018-02-07 23:59:59',
            @previousStartDateTime datetime   = '2018-01-01 00:00:00',
            @previousEndDateTime   datetime   = '2018-01-03 23:59:59'
    
    

    さて、すべてのステップを詳細に表示したかったので、私の解決策は非常に面倒です。 あなたはそれを単純化できるかもしれません。

    最初に、最大日付差を日数で計算します。
    次に、1からその差+ 1までの数値cteを作成します。
    次に、各範囲のカレンダーテンプレートを作成し、
    次に、範囲間の完全結合を行うための最後のcte、
    そして、その最後のcteからの選択がデータテーブルに2回結合されました。

    -- This allows us to use the smallest possible tally cte.
    DECLARE @MaxDateDiff int;
    SELECT @MaxDateDiff = MAX(d)
    FROM (
        VALUES  (DATEDIFF(DAY, @currentStartDateTime, @currentEndDateTime)), 
                (DATEDIFF(DAY, @previousStartDateTime, @previousEndDateTime))
        ) v(d) -- I like table value constructors :-)
    ;WITH Tally AS 
    (
        SELECT TOP (@MaxDateDiff + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As Number
        FROM sys.objects a
        -- if your database is very small (number of tables, procedures ect'), 
        -- you might want to unremark the next row
        -- CROSS JOIN sys.objects b
    ),
    CurrentRange AS  
    (
        SELECT DATEADD(DAY, Number-1, @currentStartDateTime) As [Date], Number
        FROM Tally
        -- we need the where clause in case the other range is bigger...
        WHERE DATEADD(DAY, Number-1, @currentStartDateTime) <= @currentEndDateTime
    ), 
    PreviousRange AS 
    (
        SELECT DATEADD(DAY, Number-1, @previousStartDateTime) As [Date], Number
        FROM Tally
        WHERE DATEADD(DAY, Number-1, @previousStartDateTime) <= @previousEndDateTime
    ), 
    BothRanges AS 
    (        
        SELECT C.Date As CurDate, P.Date As PreDate
        FROM CurrentRange As C
        FULL JOIN PreviousRange As P ON C.Number =  P.Number
    )
    SELECT CurDate, ISNULL(c.Value, 0) as CurValue, PreDate, ISNULL(p.Value, 0) as PreValue
    FROM BothRanges
    LEFT JOIN @data AS c ON CurDate = c.[Date]
    LEFT JOIN @data AS p ON PreDate = p.[Date]
    
    

    結果:( @currentStartDateTime  質問のものとは異なります)

    CurDate                 CurValue    PreDate                 PreValue
    03.02.2018 00:00:00     0           01.01.2018 00:00:00     3
    04.02.2018 00:00:00     4           02.01.2018 00:00:00     5
    05.02.2018 00:00:00     2           03.01.2018 00:00:00     8
    06.02.2018 00:00:00     7           NULL                    0
    07.02.2018 00:00:00     0           NULL                    0
    
    

    rextesterでライブデモを見ることができます。

  • 尋ねるときは、すべての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;
    
    

あなたの答え