SQL 最新レコードとの結合方法

Question

顧客別品名マスタ(以下、品名M)と顧客品名変更履歴ファイル(以下、履歴F)があります。
（履歴Fは品名Mを変更する都度、レコードが増えていきます。）
※履歴レコードは品名Mに対して存在しない場合もあります

＜ユニークキー＞
品名M:顧客コード、品名コード
履歴F:顧客コード、品名コード、No

以下の条件でSQLを作成したのですが、問題ないでしょうか？
他に良い案があれば教えて下さい。

＜絞込条件＞
・指定した顧客のみの品名を絞込む
・品名Mとリンクする履歴Fのレコードが存在する場合、最新レコードのみ結合する

(使用DB:SQLSERVER)

SELECT 
       品名,変更者,変更日,変更理由 
         FROM 品名M
         LEFT JOIN 履歴F ON 品名M.顧客コード = 履歴F.顧客コード
                        AND 品名M.品名コード = 履歴F.品名コード  
                        AND 履歴F.No = 
                            (SELECT MAX(No) FROM 履歴F R2
                                            WHERE 品名M.顧客コード = R2.顧客コード
                                              AND 品名M.品名コード = R2.品名コード
                                         GROUP BY R2.顧客コード,R2.品名コード)   

Answer 1

SQLServerの分析関数の使用例の「4. 最大値の行の取得」
https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html#1-4
を見ながら作ってみました :-)

with tmp履歴F as(
select 顧客コード,品名コード
  from (select 顧客コード,品名コード,No,
        max(No) over(partition by 顧客コード,品名コード) as MaxNo
        from 履歴F) a
 where No = MaxNo)
select 品名,変更者,変更日,変更理由
  from 品名M Left Join tmp履歴F
    on 品名M.顧客コード = tmp履歴F.顧客コード
   AND 品名M.品名コード = tmp履歴F.品名コード

Outer Applyを使う方法もあるようですが、
まだ勉強中なのであった。

Comments

[退会済みユーザー]

別名定義（WITH句）で指定している名称と　LEFT JOIN している 名称が違う気が

[AketiJyuuzou]

名称が間違ってたので、修正しました。

[seesaajira-]

AketiJyuuzouさん、回答ありがとうございます。
「SQLServerの分析関数の使用例」のリンクありがとうございます。
非常に詳しく説明されているので勉強になります。
SQLって奥が深いんですね。
初心者の私には思いつきもしないSQL文です。

Answer 2

つっこんだので私なりの回答

SELECT    M1.品名
    ,    F1.変更者
    ,    F1.変更日
    ,    F1.変更理由
FROM
	品名M M1     
	LEFT JOIN 履歴F F1 
		ON  M1.顧客コード = F1.顧客コード
		AND M1.品名コード = F1.品名コード
		-- グルーピング項目で絞ってるため GROUP BY 不要
		-- LEFT JOIN の条件として指定することで 存在しない場合でも マスターは表示される
		AND F1.No = ( 
			SELECT MAX(F2.No) 
	        FROM   履歴F F2 
	        WHERE  F2.顧客コード = F1.顧客コード 
	        AND F2.品名コード = F1.品名コード 
		)
;

ふと思った別案 SQLServer で対応可能かは知らないが

WHERE NOT EXISTS ( 
    SELECT 1
    FROM   履歴F F2 
    WHERE  F2.顧客コード = F1.顧客コード 
    AND    F2.品名コード = F1.品名コード 
    AND    F2.No        > F1.NO      
)

でも行けそうな予感

Comments

[退会済みユーザー]

まあ　履歴テーブルに lasted (最終更新）というカラムを追加したほうが性能はいいです。

※サブクエリを行わないでいいため

[seesaajira-]

haruka-kanataさん、回答ありがとうございます。
試したところ、確かにGroup byは要らなかったですね。
成程、lasted(最終更新)の案、その手がありましたね。
難しく考えすぎて思いつきませんでした。

[Panzer_vor]

> haruka-kanataさん
> seesaajira-さん
テーブル定義の変更が可能ならそれが一番すっきりしますね。

その場合だと履歴作成時に、
最終履歴の更新し直しも発生するので、
ストアド化されていないのなら、ストアド化するのが更新ミスを抑制する上でも良い気がします。

Answer 3

パフォーマンス上問題が発生していないのであれば、
掲示されておられるコードが一番やりたいことを明確に表現したクエリとなっていますので特に変更はいらないかと思われます。

敢えて何か助言するとしましたら、
履歴テーブルが2度出てくるのでテーブル別名を付けてあげるとか、
SELECT句のカラムにどちらのテーブルから取得しているかが一目で分かるようにテーブル名、またはテーブル別名を指定した方が親切かなといったぐらいですかね。

以下蛇足ですが、
仮に上記のクエリでパフォーマンス上に問題が出ているなどがある場合は、
顧客コード、品名コード、履歴No.の最大値を取得するサブクエリを、
外部結合でくっつけるという方法の方が速度が出るかもしれません。
(※こればかりは実行計画の違いがでるかどうか確認しないと最終的な判断はできませんが^^;)

SELECT 
    M.品名
,   H.変更者
,   H.変更日
,   H.変更理由 
FROM 
    品名M M
    LEFT JOIN(
    SELECT 
        H1*
        H2.MAX_NO
    FROM
        履歴F H1
        INNER JOIN (
        SELECT
            顧客コード
        ,   品名コード
        ,   MAX(NO) AS MAX_NO
        FROM
            履歴F
        GROUP BY
            顧客コード
        ,   品名コード
        ) H2
        ON H1.顧客コード = H2.顧客コード
        AND H1.品名コード = H2.品名コード
        AND H1.NO = H2.MAX_NO
    ) H
    ON M.顧客コード = H.顧客コード
    AND M.品名コード = H.品名コード

書いてる途中にこっちの方がダメそうな気がしてきた・・・

Comments

[seesaajira-]

KotoriMaturiさん、回答ありがとうございます。
テーブル別名にすると、確かに分かり易いですね。
サブクエリを外部結合でくっつけるという方法もあるのですね。
パフォーマンスに影響が出てきたら試してみたいと思います。

Answer 4

SQL Serverの微妙な文法の違いはよくわかりませんが
私はこんな感じでよくやります

SELECT 品名,変更者,変更日
FROM 品名M
LEFT JOIN 履歴F 
ON 品名M.顧客コード = 履歴F.顧客コード
AND 品名M.品名コード = 履歴F.品名コード  
AND (履歴F.No,履歴F.顧客コード,履歴F.品名コード) IN 
(SELECT MAX(No),顧客コード,品名コード FROM 履歴F 
GROUP BY 顧客コード,品名コード)   

#結局
冗長になりますがこうなんでしょうね・・・

SELECT 品名,変更者,変更日
FROM 品名M
LEFT JOIN 
(SELECT 変更者,変更日,履歴F.顧客コード,履歴F.品名コード 
FROM 履歴F INNER JOIN  
(SELECT MAX(No) AS No,顧客コード,品名コード FROM 履歴F 
GROUP BY 顧客コード,品名コード) AS SUB
ON 履歴F.No=SUB.No
AND 履歴F.顧客コード=SUB.顧客コード
AND 履歴F.品名コード=SUB.品名コード) AS SUB
ON 品名M.顧客コード = SUB.顧客コード
AND 品名M.品名コード = SUB.品名コード ;

ちなみに、INの複数指定ができないということはUSINGの複数してもできないのでしょうか？

SELECT 品名,変更者,変更日
FROM 品名M
LEFT JOIN 
(SELECT 変更者,変更日,顧客コード,品名コード 
FROM 履歴F INNER JOIN  
(SELECT MAX(No) AS No,顧客コード,品名コード FROM 履歴F 
GROUP BY 顧客コード,品名コード) AS SUB
USING(No,顧客コード,品名コード)) AS SUB
USING(顧客コード,品名コード);

Comments

[seesaajira-]

なるほど、Inを使用する方法があるのですね。
早速、試してみます！

[seesaajira-]

回答、ありがとうございました。

[Panzer_vor]

> yambejpさん
SQL Serverは行値構成子の書き方をサポートしていなかった記憶が・・・。
勘違いだったらすみません。

SQL99から標準の仲間入りしてるので、まだサポートされてない状況なら対応してほしかったりしますが^^;

[yambejp]

KotoriMaturiさん、ご指摘ありがとうございます
勉強になります。
なにせMySQL使いなもので文法違いについては配慮いたしますが
方言の吸収については各自個別対応ということでご容赦ください

[seesaajira-]

yambejpさん、KotoriMaturiさんコメントありがとうございます。
やはりSQLSERVERだと複数項目にINを使うとエラーになりました。
勉強になりました。

[Panzer_vor]

> seesaajira-さん
やはりまだ未サポートだっとんですね^^;

ただ標準SQL(SQL仕様の定義みたいなもの)では行値構成子は定義されてますし、
Microsoft製のDBMS以外の主要なDBMS(Oracle、MySQL、PostgreSQLなど)はサポート済なので、
よほどのことがない限りはいずれサポートされるものかと思います。

[yambejp]

動かないままソースを晒しておくのもなんなんで、普通に結合するソースを追記しておきました。INがだめならUSINGもだめでしょうね・・・

[Panzer_vor]

> yambejpさん
僕の方が普段USING句を使う習慣がなかったので分からないのと、サポート状況は気になったので少しググってみました。
→ https://en.m.wikipedia.org/wiki/Join_(SQL)#cite_ref-5

英語サイトで申し訳ないのですが、「Natural Join」の節のすぐ上で申し訳程度にUSING句に触れているのですが、
それを見るとUSING句自体SQL Serverではサポート外のようです。(それとSybase)

Answer 5

問題ありません。
HAVING句やEXISTS句や
ORDER BY ＋ Limit 1を使って抽出も可能ですが、
私はseesaajira-さんが記載してあるSQL文が好みです。
強いて言えば、JOIN の向きを逆にするかな、というくらいです。

SELECT	M1.品名
	,	F1.変更者
	,	F1.変更日
	,	F1.変更理由
FROM	履歴F F1
			LEFT JOIN 品名M M1 ON (
					M1.顧客コード = F1.顧客コード
				AND	M1.品名コード = F1.品名コード
			)
WHERE F1.No =	(
					SELECT	MAX(F2.No)
					FROM	履歴F F2
					WHERE	F2.顧客コード	= F1.顧客コード
						AND	F2.品名コード	= F1.品名コード
					GROUP BY F2.顧客コード
						,	F2.品名コード
				)
;

Comments

[seesaajira-]

回答、ありがとうございます。
JOINの向きが逆との事ですが、履歴Fのレコードは品名Mのレコードに対して有ったり無かったりするのですが、やはり逆なのでしょうか？
試してみたいと思います。

[tomari_perform]

おっと。。。失礼しました。
履歴Fのテーブルにあったりなかったりする場合、逆にしてはダメです。
なので、私もseesaajira-さんが記載してあるSQL文にします。

ちなみに、今回のケースにおいて、
IN句はデータ量が多いと性能が出ないため、
私なら使いませんが、試してみてくださいな。

[seesaajira-]

早速のコメントありがとうございます。
やはり逆はまずいですよね。
私の質問もまずかったので、後で訂正しておきます。
ありがとうございました！

[tomari_perform]

質問の仕方はまずくないです！
申し訳ないですが、私の見落としです。
ちゃんと、データ例にて表現されていますので、問題ありません！

[退会済みユーザー]

WHERE F.カラム = ( サブクエリ ) ってしてる場合に 履歴テーブルがない M レコードがでてこないから
WHERE F.カラム = ( サブクエリ ) OR F.カラム IS NULL じゃないかなとおもったり

[Panzer_vor]

凄まじく蛇足となりますが、以下参考までに。
SQL ServerではLimit句というものはないので、
ORDER BY + LIMITの戦略でいく場合は、
同様機能TOP句を使う方法となります。

SELECT TOP(1) t.hoge FROM table t ORDER BY t.hoge

[tomari_perform]

haruka-kanata 様
補足ありがとうございます。
根本的に私のSQLは間違っておりまして、すみません。
　FROM 履歴F F1
の時点で、F.カラム IS NULL　という行自体が出てこないため、
私のSQLでは、JOINの順番がおかしいんです。（汗

KotoriMaturi 様
補足ありがとうございます。
SQL Serverであることを見落としてました。。
ご指摘の通りでございますm(__)m