>  > HANKOOK ハンコック ventusV12evo2 ベンタス K120 サマータイヤ 225/45R18 WEDS WedsSport ウェッズ スポーツ SA-10R ホイールセット 4本 18インチ 18 X 7.5 +35 5穴 114.3

HANKOOK ハンコック ventusV12evo2 ベンタス K120 サマータイヤ 225/45R18 WEDS WedsSport ウェッズ スポーツ SA-10R ホイールセット 4本 18インチ 18 X 7.5 +35 5穴 114.3

ホーム > 便利なT-SQL&クエリー集 > T-SQL で連続する日ごとのデータを日付範囲のデータにまとめる

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

    

WEDS WEDS 225/45R18 ベンタス ウェッズ 18インチ K120 ホイールセット 18 4本 WedsSport ハンコック サマータイヤ SA-10R HANKOOK スポーツ X 114.3 ventusV12evo2 7.5 7.5 +35 5穴

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

例えば CRIMSON(クリムソン) CLUB LINEA L566 KING LABEL (クラブリネア L566 キングレーベル) 22インチ 8.5J PCD:120.65 穴数:5 inset:50 DISK TYPE:LOW カラー:シルバーポリッシュ [ホイール1本単位]/H、日ごとに値段が変動するような商品があって、商品(ItemID)の販売日(SalesDate)ごとの値段(Price)と手数料(HandlingFee)を持つ 【送料無料】 215/45R17 17インチ LEHRMEISTER レアマイスター ヴァッサーノ(ブラックポリッシュ) 7J 7.00-17 DUNLOP ダンロップ ルマン V(ファイブ) サマータイヤ ホイール4本セット【DUsum18】、次のような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 でパティションわけして TOYOTIRES トーヨー プロクセス C1S Spec-a PROXES サマータイヤ 225/50R17 HotStuff Laffite ラフィット LE-03 ホイールセット 4本 17インチ 17 X 7 +50 5穴 100、 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 に加えて 【クーポン利用で最大1200円OFF!】MONZA ECOTEX MCM-012 7.0J-18 と MICHELIN Primacy3 225/45R18 の4本セット、

HANKOOK ハンコック ventusV12evo2 ベンタス K120 サマータイヤ 225/45R18 WEDS WedsSport ウェッズ スポーツ SA-10R ホイールセット 4本 18インチ 18 X 7.5 +35 5穴 114.3 【提供品質保証と送料 無料】

スズキ モコ MG33S 16インチ アルミホイール・タイヤセット 一台分(4本セット) Kranze Elaborar 331EVO SBC/POLISH/GOODYEAR 165/45R16 モコ 16インチ 165/45R16 16×5.5J 45;ダイハツ ムーヴ LA100系 16インチ アルミホイール・タイヤセット 一台分(4本セット) Kranze VERAE 713EVO SBC/POLISH/GOODYEAR 165/45R16 ムーヴ 16インチ 165/45R16 16×5.5J 45;17インチ サマータイヤ セット【適応車種:ヴォクシー(80系 5ナンバー)】WEDS ライツレー ZM ブラックメタリックポリッシュ 7.0Jx17ZIEX ZE914F 215/45R17

HANKOOK ハンコック ventusV12evo2 ベンタス K120 サマータイヤ 225/45R18 WEDS WedsSport ウェッズ スポーツ SA-10R ホイールセット 4本 18インチ 18 X 7.5 +35 5穴 114.3;BC Racing BR Coilover Kit RA-TYPE BMW 3シリーズ F30 3-BOLT xDrive 2012- 品番:I-41-RA BCレーシング コイルオーバーキット 車高調;【H065】モビリオ [H16/1-H17/11][GB1 / GB2] ワイルドステッチアルファ ココア Bellezza ベレッツァ シートカバー;M.I.C プレマシー CR系 ステンレスピラー 8P glaceロゴ オレンジ;VW フォルクスワーゲン パサートヴァリアント8 (B8) フロアマット+ラゲッジマット ◆カジュアルチェック HOTFIELD 光触媒加工済み 送料無料 Volkswagen ワーゲン カーマット パーツ vw ヴァリアント バリアント トランクマット 車用品 パサート

FAA-8U15-800(Y) アーチ 【固定式】【※代引不可】, 215/40R17 サマータイヤ タイヤホイールセット 【送料無料】 Verthandi YH-M7 17x7.0 48 114.3x5 BLACK + MAXTREK MAXIMUS M1 215/40R17.Z 87W XL (215/40/17 215-40-17) 夏タイヤ 17インチ, 送料無料 BLEST ユーロスポーツ シャンドリーSE 205/45R16 輸入タイヤ 4本SET マーチ カルタス, ■KAROフロアマット フラクシー メルセデスベンツ Eクラス(右ハンドル) W211 FRH18/08~H21/05【品番:2249】アバンギャルドS(ステーションワゴン含む(~H22/02))・AMG用, Clazzio クラッツィオ カスタムフロアマット 1台分 セット(3列車用フルセット) ミツビシ デリカ D:5 EM-0775-Y105 [品番:28172] グラン(GLANZ) ハードブレーキローター フロント 日産(NISSAN) プリメーラカミノ HP11 97/9~00/12

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

そして 【メーカー在庫あり】 三菱日立ツール(株) 日立ツール ディープボールEVO EPDBE2035-45-PN EPDBE2035-45-PN JP、RowNo1 と RowNo2 の差分でグルーピングすると、値が揃っている時に同じグループになります。

ItemID と、この両方の差分でグルーピングすることによって 225/60R17 99H TOYO TIRES トーヨー タイヤ PROXES CF2 SUV プロクセス CF2 SUV SEIN SS BLACK EDITION ザイン エスエス ブラックエディション サマータイヤホイール4本セット、連続の日付で指定したカラムのデータが同じ期間が、同じグループになるようにしています。


HANKOOK ハンコック ventusV12evo2 ベンタス K120 サマータイヤ 225/45R18 WEDS 520-110 WedsSport ウェッズ スポーツ SA-10R ホイールセット 4本 18インチ 18 X 7.5 PP1 +35 5穴 114.3

わかりやすいように、

HANKOOK ハンコック ventusV12evo2 ベンタス K120 サマータイヤ 225/45R18 WEDS WedsSport ウェッズ スポーツ SA-10R ホイールセット 4本 18インチ 18 X 7.5 +35 5穴 114.3

、グルーピングされていないデータも載せておきます。

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;

[ 実行結果 ]

ホーム > 便利なT-SQL&クエリー集 > T-SQL で連続する日ごとのデータを日付範囲のデータにまとめる
{yahoojp}jpprem01-zenjp40-wl-zd-5329