T-SQL で連続する日ごとのデータを日付範囲のデータにまとめる

連続する日ごとのデータを日付範囲のデータにまとめあげる

日ごとのデータがあって、同じ値が連続する期間を、日付範囲のデータにまとめあげたいような時ありませんか？

例えば、日ごとに値段が変動するような商品があって、商品（ItemID）の販売日（SalesDate）ごとの値段（Price）と手数料（HandlingFee)を持つ、次のようなItemPrice テーブルがあるとします。

商品ごとに値段と手数料が同じ期間を同じグループとして、連続する販売日でまとめた以下のようなデータを取得したいような状況です。

今回はそんな時に使えるクエリーをご紹介したいと思います。

連続する日ごとのデータを日付範囲のデータにまとめるクエリー

まず、今回使う ItemPrice テーブルとデータを生成するスクリプトをは次の通りです。

CREATE TABLE ItemPrice(
    ItemID INT NOT NULL,
    SalesDate DATE NOT NULL,
    Price MONEY NOT NULL,
    HandlingFee MONEY NOT NULL,
    PRIMARY KEY (ItemID, SalesDate)
);

INSERT INTO ItemPrice 
    (ItemID, SalesDate, Price, HandlingFee)
VALUES
    (1, '2018-04-01', 1000, 10),
    (1, '2018-04-02', 1000, 10),
    (1, '2018-04-03', 1000, 10),
    (1, '2018-04-04', 1010, 10),
    (1, '2018-04-05', 1010, 10),
    (1, '2018-04-06', 1010, 8),
    (1, '2018-04-09', 1010, 8),
    (1, '2018-04-10', 1010, 8),
    (2, '2018-04-01', 2000, 20),
    (2, '2018-04-02', 2000, 20),
    (2, '2018-04-03', 2000, 20),
    (2, '2018-04-04', 2000, 20),
    (2, '2018-04-08', 2000, 20),
    (2, '2018-04-09', 2000, 20),
    (2, '2018-04-10', 2000, 20);

まず、値段は関係なく、商品ごとに連続した日付のみ取得してみましょう。

SELECT  ItemID,
        MIN(SalesDate) AS SalesDateFrom,
        MAX(SalesDate) AS SalesDateTo
FROM
    (SELECT  ItemID,
             SalesDate,
             ROW_NUMBER() OVER (PARTITION BY ItemID 
                                ORDER BY     SalesDate) AS RowNo1
     FROM    ItemPrice) AS T

GROUP BY    ItemID, 
            DATEDIFF(dd, RowNo1, SalesDate)

ORDER BY    ItemID,
            MIN(SalesDate);

[ 実行結果 ]

クエリーのポイントを見ていきましょう。

まず青で囲まれた部分ですが、サブクエリーの中で ItemID でパティションわけして、 SalesDate 順で ROW_NUMBER を生成しておきます（RowNo1）。これが基準の番号となります。

そして、緑で囲まれた部分で RowNo1 と SalesDate の差分(DATEDIFF)でグルーピングすることによって、連続している日付が同じグループになります。

次に、商品ごとに値段と手数料が同じ期間を同じグループとして、連続する販売日の日付範囲でまとめたデータを取得するクエリーはこちらです。

SELECT  ItemID,
        MIN(SalesDate) AS SalesDateFrom,
        MAX(SalesDate) AS SalesDateTo,
        Price,
        HandlingFee
FROM
    (SELECT  ItemID,
             SalesDate,
             Price,
             HandlingFee,
             ROW_NUMBER() OVER (PARTITION BY ItemID 
                                ORDER BY     SalesDate) AS RowNo1,
             ROW_NUMBER() OVER (PARTITION BY ItemID, 
                                             Price, 
                                             HandlingFee 
                                ORDER BY     SalesDate) AS RowNo2
     FROM    ItemPrice) AS T

GROUP BY    ItemID, 
            Price,
            HandlingFee,
            DATEDIFF(dd, RowNo1, SalesDate),
            RowNo1 - RowNo2

ORDER BY    ItemID,
            MIN(SalesDate);

[ 実行結果 ]

先ほどの、RowNo1 に加えて、ItemID と同じ値の場合に連続して欲しいカラム(今回は Price と HandlingFee)でパティションわけして、 SalesDate 順で ROW_NUMBER　を生成しておきます（RowNo2）。

そして、RowNo1 と RowNo2 の差分でグルーピングすると、値が揃っている時に同じグループになります。

ItemID と、この両方の差分でグルーピングすることによって、連続の日付で指定したカラムのデータが同じ期間が、同じグループになるようにしています。

わかりやすいように、グルーピングされていないデータも載せておきます。

SELECT  ItemID,
        SalesDate,
        Price,
        HandlingFee,
        RowNo1,
        DATEDIFF(dd, RowNo1, SalesDate) AS Diff1,
        RowNo2,
        RowNo1 - RowNo2 AS Diff2
FROM
    (SELECT  ItemID,
             SalesDate,
             Price,
             HandlingFee,
             ROW_NUMBER() OVER (PARTITION BY ItemID 
                                ORDER BY     SalesDate) AS RowNo1,
             ROW_NUMBER() OVER (PARTITION BY ItemID, 
                                             Price, 
                                             HandlingFee 
                                ORDER BY     SalesDate) AS RowNo2
     FROM    ItemPrice) AS T

ORDER BY    ItemID,
            SalesDate;

[ 実行結果 ]