先日、売上データを管理している業務システムから、初回注文の売上データを抽出する場面があったのだが、やや躓いたので備忘録としてまとめておく。

結論、「DISTINCT」を使って重複を取り除けば、狙ったデータを簡単に抽出することができた。

DISTINCTの使用例

では実際の使用例を見ていこう。

まず、売上テーブルと売上明細テーブルを結合し、伝票単位の売上金額を集計する。

SELECT u.売上id, u.法人id, sum(d.金額) as 金額, u.売上日
FROM t_売上 as u
LEFT JOIN t_売上明細 as d
	ON u.売上id = d.売上id
GROUP BY u.売上id, u.法人id, u.売上日
ORDER BY u.売上日
;

次に、集計した表に対し「DISTINCT」を使って重複を取り除く。
今回は顧客の重複を無くしたいので「法人id」を対象とする。

また、DISTINCTを使う場合、ORDER BY句に指定する最初のカラム名は「DISTINCT ON」で指定したカラム名でないといけないというルールがある。

SELECT DISTINCT ON (u.法人id) u.売上id, u.法人id, sum(d.金額) as 金額, u.売上日
FROM t_売上 as u
LEFT JOIN t_売上明細 as d
	ON u.売上id = d.売上id
GROUP BY u.売上id, u.法人id, u.売上日
ORDER BY u.法人id
;

この場合、ORDER BY句には法人idを指定しなければいけないのだが、できれば売上idや売上日で並び替えたい。

サブクエリを利用して並び替えを行う

ORDER BY句での並び替えはサブクエリを利用し、後から行えば良い。

SELECT *
FROM (
	SELECT DISTINCT ON (u.法人id) u.売上id, u.法人id, sum(d.金額) as 金額, u.売上日
	FROM t_売上 as u
	LEFT JOIN t_売上明細 as d
		ON u.売上id = d.売上id
	GROUP BY u.売上id, u.法人id, u.売上日
	ORDER BY u.法人id
) as tmp
ORDER BY u.売上id
;

なお、サブクエリ内で行っている法人idの並び替えも、初回注文のみを抽出するために必要となる。

これがないと初回注文のデータが重複として除かれてしまうため注意が必要だ。