以下のSQLをViewに変更するとしたら？

Question

###『変更元のSQL』の説明
PostgreSQL9.2でViewを作成しようとしています。

ユーザーの日々の購入履歴を年月毎に集計して出力する場合を想定しています。
１つの購入履歴テーブルに複数のマスターをJoinしているのですが、
マスターは年月で履歴管理しているデータで膨大な件数とします。

現状は、初めに年月で絞り込みをしてからJoinするようにしているのですが、
以下のSQLをView化するにはどのようにすればいいでしょうか？

###変更元のSQL
購入履歴テーブル：利用者ID、購入年月日、金額
支払方法マスター：利用者ID、支払方法、有効開始年月、有効終了年月 ※年月でユニークになる
お届け先マスター：利用者ID、住所、有効開始年月、有効終了年月 ※年月でユニークになる
↓
欲しいView：利用者ID、購入年月、金額合計、支払方法、住所

--購入履歴テーブルを利用者と年月で絞り込んで合計を算出
with 購入履歴@ as (
  select 利用者ID, sum(金額) from 購入履歴
  where to_char(購入年月日, 'yyyy/mm') = {yyyy/mm}  ←画面から入力された年月
    and 利用者ID in ('xxx,xxx,xxx,xxx,xxx')  ←画面から入力された複数のユーザID
  group by 利用者ID, to_char(購入年月日, 'yyyy/mm')
),
--支払方法マスターを利用者と年月で絞り込む
支払方法@ as (
  select * from 支払方法
  where {yyyy/mm} between 有効開始年月 and 有効終了年月  ←画面から入力された年月
    and 利用者ID in ('xxx,xxx,xxx,xxx,xxx')  ←画面から入力された複数のユーザID
), 
--お届け先マスターを利用者と年月で絞り込む
お届け先@ as (
  select * from お届け先
  where {yyyy/mm} between 有効開始年月 and 有効終了年月  ←画面から入力された年月
    and 利用者ID in ('xxx,xxx,xxx,xxx,xxx')  ←画面から入力された複数のユーザID
)
--それぞれのテーブルをJoinでつなぐ
select * from 購入履歴@
  left join 支払方法@
  on 購入履歴.利用者ID = 支払方法@.利用者ID
  left join お届け先@
  on 購入履歴.利用者ID = お届け先@.利用者ID

Comments

[ps13zier]

使うテーブルのキーや主要項目とその型はどうなっていますか？あと、ER図なんかもあればすぐ答えが付きそうに思います。ただ、そもそも View のような小手先での対処ではなく、設計を精査すべき問題に感じるのは私だけでしょうか？もし、背景や制限もあれば先に挙げておくべきでしょう。

[commabee]

設計の変更は難しいので、現状でなんとかしたいと思い質問投稿しました。
質問文を修正したので、これで多少見やすくなっていればいいのですが。

[lib]

回答ではないですが、pgsqlでなんかするんですかね？ありがちな有効開始日と有効終了日あるけど。あと遡及的に情報とってくる必要もあるのかな？

Answer 1

見れば見るほど View を使うという考えに至った経緯やら理解に苦しむのですが、質問者様は SQL チューニングの基礎などは齧っておられるのでしょうか？もし、齧ってないのであれば、即勉強しましょう。実践的なスキルですし、勘所がつかみやすくなります。

現状の SQL だと、購入年月日の型がよくわかりませんが to_char() で変換した条件で検索してしまうと、テーブルに対してのフルスキャンが発生するため、レコード数に比例して確実に遅くなります。利用者ID での絞り込みも併用した単体のクエリで検索するのであれば、利用者ID の範囲に絞られたレコード群に対して、購入年月日の項目変換と比較が個別に発生するため、まだマシにはなりますが、意図としている結果を View にするのであれば、利用者ID の条件も取り外した巨大な結果を用意しなければならないでしょう。しかし、一般的にはこのような状況や構造には不向きのため View は使わないと思います。もし、誰かこのパターンで効果的に使える方法があれば後学のために教えていただきたいです。

以下、質問の View に変更することとは全く関係ない回答となります。

単純に SQL をより速くしたいのであれば、対象を絞り、項目を加工せずに極力等価で比較することです。インデックスも併用できれば更に良いでしょう。例えば、{yyyy/mm} から開始年月日 {yyyy/mm/01} と終了年月日 {yyyy/mm/31} は事前に用意できるでしょうから、２つの値と購入年月日を範囲で比較する形に条件を変えれば、テーブルが持つ項目を加工しない分、同じ結果を今までより少し速く得られるでしょう。

新規のテーブル追加とレコードの整備が可能なのであれば、購入履歴テーブルと対になる年月から単純に参照させる検索補助用の参照テーブルを別途用意し、参照テーブル経由で結合して結果を得るようにすれば、更に速くなるでしょう。

以上、ご参考になれば幸いです。

Comments

[commabee]

おっしゃる通りだと思います。例題を示そうとしたばかりに質問の趣旨がブレ過ぎました。。。本来の趣旨は大きなSQLの使い回しについて聞きたかったです。もう一度質問を検討して、質問立てなおしてみます。ありがとうございました。