質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.23%

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

受付中

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 963

commabee

score 19

『変更元のSQL』の説明

PostgreSQL9.2でViewを作成しようとしています。

ユーザーの日々の購入履歴を年月毎に集計して出力する場合を想定しています。
1つの購入履歴テーブルに複数のマスターを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
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • ps13zier

    2016/03/22 15:10

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

    キャンセル

  • commabee

    2016/03/22 17:26

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

    キャンセル

  • lib

    2016/03/23 06:31 編集

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

    キャンセル

回答 1

+1

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

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

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/03/23 10:27

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.23%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる