SQLよる変更履歴テーブルの集計について

Question

お世話になります。
SQL Server にて以下のような集計を行いたいと考えております。
しかしあまりSQLに詳しくなく、実装方法が思いつかず手が止まってしまったため投稿させていただきました。

行いたいこと

ある keyword に紐づいた値の変更履歴を保存しているテーブルがあったとき、
keyword ごとに初期値と最新の値を取得し一行に結果をまとめて出力する。

集計元となる変更履歴テーブル

keyword	old_value	new_value	change_at
'key1'	'aaaa'	'bbbb'	2019-01-01 00:00:00.0000000
'key1'	'bbbb'	'cccc'	2019-01-01 00:01:00.0000000
'key1'	'cccc'	'dddd'	2019-01-01 00:02:00.0000000
'key2'	'wwww'	'xxxx'	2019-01-01 00:03:00.0000000
'key2'	'xxxx'	'yyyy'	2019-01-01 00:04:00.0000000
'key2'	'yyyy'	'zzzz'	2019-01-01 00:05:00.0000000

CREATE TABLE change_history(
	keyword nvarchar(64) NOT NULL,
	old_value nvarchar(64),
	new_value nvarchar(64),
	change_at datetime2(7) NOT NULL
)

insert into change_history(keyword,old_value,new_value,change_at)
values('key1','aaaa','bbbb','2019/01/01 00:00:00')
,('key1','bbbb','cccc','2019/01/01 00:01:00')
,('key1','cccc','dddd','2019/01/01 00:02:00')
,('key2','wwww','xxxx','2019/01/01 00:03:00')
,('key2','xxxx','yyyy','2019/01/01 00:04:00')
,('key2','yyyy','zzzz','2019/01/01 00:05:00')

集計結果として取得したいデータ

keyword	old_value	new_value
'key1'	'aaaa'	'dddd'
'key2'	'wwww'	'zzzz'

環境

SQL Server 2016

以上、よろしくお願いいたします。

Answer 1

経験的にSQL Serverなら、軸を作って結合するのが高速です。

select base.keyword, lt.old_value, gt.new_value
from (
        select  keyword, min(change_at) as min_change_at, max(change_at) as max_change_at
        from change_history
        group by  keyword
    ) base  
    inner join change_history lt
    on base.keyword=lt.keyword and base.min_change_at=lt.change_at
    inner join change_history gt
    on base.keyword=gt.keyword and base.max_change_at=gt.change_at

Comments

[uttne]

ご回答いただきありがとうございます!
確かにご回答いただいたクエリの方が早かったです!
100000件のデータを登録して比較した結果をキャプチャしましたのでアップしておきます。
https://imgur.com/a/zNyuayA

[sazi]

性能だけを言うなら、(keyword,change_at)のインデックスがあればさらに高速だと思います。

[uttne]

追加情報をいただきありがとうございます。
実際の環境では(keyword,change_at)に対応するフィールドにインデックスが張られているのでこちらの実装を参考にさせていただこうと思います。

Answer 2

そこまで履歴が大切なのか微妙ですが

change_atが最小のold_valueと最大のnew_valueを表示すれば良いでしょう

create table tbl(keyword  varchar(20),old_value varchar(20),new_value varchar(20),change_at datetime);
insert into tbl values
('key1','aaaa','bbbb','2019-01-01 00:00:00'),
('key1','bbbb','cccc','2019-01-01 00:01:00'),
('key1','cccc','dddd','2019-01-01 00:02:00'),
('key2','wwww','xxxx','2019-01-01 00:03:00'),
('key2','xxxx','yyyy','2019-01-01 00:04:00'),
('key2','yyyy','zzzz','2019-01-01 00:05:00');

select t2.keyword,t2.old_value,t4.new_value
from (
select keyword,old_value from tbl as t1 where not exists(select 1 from tbl where keyword=t1.keyword and change_at<t1.change_at)
) as t2 inner join (
select keyword,new_value from tbl as t3 where not exists(select 1 from tbl where keyword=t3.keyword and change_at>t3.change_at)
) as t4 on t2.keyword=t4.keyword

Comments

[uttne]

ありがとうございます!
現在、帳票系のシステムに関わっており、その中で履歴を質問のように集計する処理が必要であったためどのように行えばよいか悩んでおりました。
素早いご回答をいただき本当にありがとうございます!

Answer 3

したいことを教えてください。

keywordごとに一番古い「old_value」と一番新しい「new_value」を
一覧表示したいということですか？

Comments

[uttne]

はい、その通りです。
行いたいことは変更履歴テーブルから「集計結果として取得したいデータ」のようなデータを表示するということです。

[Kaiser]

承知しましたが、他の方が回答されたみたいですね。
お返事のみさせてもらいます。