とりあえず半歩

学んだことを1日1個、簡単なことでも良いから記録していきたい。

日時条件によって結合するレコードを変更する

目標

『○月×日以降はレコードAと結合するけれど、□月△日以降はレコードBと結合する』のように、日時条件(特に開始日時しかないような場合)で結合するレコードを変更する。

動機

1対nの関係を想定したテーブルだったが、結合キーに日時を使っていたため実際にはn対nの関係になっていて、そのまま結合すると余計な結合が発生してしまうため

課題

テーブル定義の変更を行わず、クエリのみの対応で1対nの関係に直して結合結果を得る

実施

環境

作業

まず、例として用いるテーブルを用意する。

-- グループ変更ログ.
CREATE TABLE [#ChangeGroupLog] (
    [GROUPNAME] VARCHAR(2) NULL
  , [ID] VARCHAR(4) NOT NULL
  , [CHANGEDATETIME] DATETIME NOT NULL
  , CONSTRAINT [PK_ChangeGroupLog] PRIMARY KEY NONCLUSTERED (
        [ID]
      , [CHANGEDATETIME]
    )
);

INSERT INTO
  [#ChangeGroupLog] (
      [GROUPNAME], [ID], [CHANGEDATETIME]
  )
VALUES
    ( 'A', '0001', '2016/03/01 08:00:00' )
  , ( 'C', '0002', '2016/03/01 08:00:00' )
  , ( 'B', '0002', '2016/03/05 09:45:00' );
  
-- 編集ログ
CREATE TABLE [#EditLog] (
      [ID] VARCHAR(4) NOT NULL
    , [EDITDATETIME] DATETIME NOT NULL
    , [COMMENT] VARCHAR(50) NULL
    , CONSTRAINT [PK_EditLog] PRIMARY KEY NONCLUSTERED (
          [ID]
        , [EDITDATETIME]
      )
);

INSERT INTO
  [#EditLog] (
    [ID], [EDITDATETIME], [COMMENT]
  )
VALUES
    ( '0001', '2016/03/01 10:00:00', 'First Edit.' )
  , ( '0001', '2016/03/02 13:30:00', 'Second Edit.' )
  , ( '0001', '2016/03/08 08:00:00', 'Last Edit.' )
  , ( '0002', '2016/03/02 13:30:00', 'I like kapibara san.' )
  , ( '0002', '2016/03/03 14:55:00', 'I like white san too.' )
  , ( '0002', '2016/03/05 10:00:00', 'Oh! I like kapi ji too!' )
  , ( '0002', '2016/03/12 07:45:00', 'kyuruppi :)' );

上のクエリを実行すると次のようなテーブルが出来上がる。

GROUPNAME ID CHANGEDATETIME
A 0001 2016/03/01 08:00:00
C 0002 2016/03/01 08:00:00
B 0002 2016/03/05 09:45:00
ID EDITDATETIME COMMENT
0001 2016/03/01 10:00:00 First Edit.
0001 2016/03/02 13:30:00 Second Edit.
0001 2016/03/08 08:00:00 Last Edit.
0002 2016/03/02 13:30:00 I like kapibara san.
0002 2016/03/03 14:55:00 I like white san too.
0002 2016/03/05 10:00:00 Oh! I like kapi ji too!
0002 2016/03/12 07:45:00 kyuruppi :)

ChangeGroupLog, EditLogテーブルはどちらもブログの編集ログを記録するテーブルとする。

ChangeGroupLogテーブルは編集者がブログのカテゴリを変更した記録したテーブルで、EditLogテーブルは編集者がブログを編集した記録のテーブルのつもり。

両テーブルのID列が編集者番号、ChangeGroupLogテーブルのCHANGEDATETIMEとEditLogテーブルのEDITDATETIMEが変更・編集日時の記録、ChangeGroupLogのGROUPNAMEがカテゴリ、EditLogのCOMMENTが編集コメント。

ここで、ブログを編集した記録に編集時のカテゴリを結合したいとしよう。

テーブルの作成者はChangeGroupLogテーブルとEditLogテーブルを(ID, DATETIME)列で結合したとき、1対nの関係であることを想定していた。

しかし素直に結合すると余計な結合レコードが出来てしまう。

-- 失敗な結合.
SELECT
    ROW_NUMBER() OVER ( ORDER BY [#EditLog].[ID], [#EditLog].[EDITDATETIME] ) AS [NO]
  , [#ChangeGroupLog].[GROUPNAME]
  , [#EditLog].[ID]
  , [#EditLog].[EDITDATETIME]
  , [#EditLog].[COMMENT]
FROM
  [#EditLog]
JOIN
  [#ChangeGroupLog]
ON
  [#ChangeGroupLog].[ID] = [#EditLog].[ID]
  AND [#ChangeGroupLog].[CHANGEDATETIME] < [#EditLog].[EDITDATETIME]
ORDER BY
    [#EditLog].[ID]
  , [#EditLog].[EDITDATETIME];
NO GROUPNAME ID EDITDATETIME COMMENT
1 A 0001 2016-03-01 10:00:00.000 First Edit.
2 A 0001 2016-03-02 13:30:00.000 Second Edit.
3 A 0001 2016-03-08 08:00:00.000 Last Edit.
4 C 0002 2016-03-02 13:30:00.000 I like kapibara san.
5 C 0002 2016-03-03 14:55:00.000 I like white san too.
6 C 0002 2016-03-05 10:00:00.000 Oh! I like kapi ji too!
7 B 0002 2016-03-05 10:00:00.000 Oh! I like kapi ji too!
8 C 0002 2016-03-12 07:45:00.000 kyuruppi :)
9 B 0002 2016-03-12 07:45:00.000 kyuruppi :)

NO.6とNo.8の行が余計に結合されたレコードだ。 本当はID=0002のGROUPNAMEは、2016/03/05 09:45:00以降はBだけであって欲しい。

結果

ChangeGroupLogテーブルには各編集者がカテゴリを変更した日時が記録されているのだから、あるカテゴリに属していた期間は前回変更した日時から次に変更した日時までである。コレを利用して次のようなクエリを書くことで対応ができた。

-- 編集ログにグループ名を加える.
SELECT
    [ChangeLog].[GROUPNAME]
  , [#EditLog].[ID]
  , [#EditLog].[EDITDATETIME]
  , [#EditLog].[COMMENT]
FROM (
  SELECT
      [GROUPNAME]
    , [ID]
    , [CHANGEDATETIME] AS [DATE]
    , LEAD( [CHANGEDATETIME], 1, '9999/12/31 23:59:59.997' )
        OVER ( PARTITION BY [ID]
               ORDER BY [CHANGEDATETIME] ) AS [NEXT_DATE]
  FROM
    [#ChangeGroupLog]
) AS [ChangeLog]
JOIN
  [#EditLog]
ON (
  [#EditLog].[ID] = [ChangeLog].[ID]
  AND [ChangeLog].[DATE] < [#EditLog].[EDITDATETIME]
  AND [#EditLog].[EDITDATETIME] < [ChangeLog].[NEXT_DATE]
)
ORDER BY
    [#EditLog].[ID]
  , [#EditLog].[EDITDATETIME];
GROUPNAME ID EDITDATETIME COMMENT
A 0001 2016-03-01 10:00:00.000 First Edit.
A 0001 2016-03-02 13:30:00.000 Second Edit.
A 0001 2016-03-08 08:00:00.000 Last Edit.
C 0002 2016-03-02 13:30:00.000 I like kapibara san.
C 0002 2016-03-03 14:55:00.000 I like white san too.
B 0002 2016-03-05 10:00:00.000 Oh! I like kapi ji too!
B 0002 2016-03-12 07:45:00.000 kyuruppi :)

ChangeGroupLogを『カテゴリの変更開始日時だけが記録されたテーブル』から『カテゴリに属していた期間を記録したテーブル』に整形することでEditLogテーブルと1対nの関係に治すことが出来て、最初に想定していた結合結果を得られるようになった。

考察

テーブル定義の変更が可能な段階なら、カテゴリ列をEditLogに追加するとかChangeGroupLogテーブルに最初から開始と終了時間を記録できるようにしておくとかして対応したほうがクエリ速度や複雑さが軽減できると思われる。