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

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

ただいまの
回答率

88.78%

NOT EXISTS中のwhere句をcase when で分岐させると取得に時間がかかりすぎる。

受付中

回答 4

投稿

  • 評価
  • クリップ 1
  • VIEW 314

shakesuke

score 6

前提・実現したいこと

以下のテーブルから
・同一レコードからcreate_dateが最新のものだけを取得したい
・同一レコードかどうかの判断は、
house_codeに値が入っていればhouse_codeが同じもの
house_codeがnullか''なら、product_nameが同じもの。

CREATE TABLE tbl_products (
    product_id integer NOT NULL,
    product_name character varying (100),
    house_code character varying (48),
    create_date timestamp without time zone DEFAULT now() NOT NULL
);

例えばこのようなレコードから

ID  product_name  house_code  create_date
1 1111 2020-02-18 12:46:37
2      2020-02-19 12:46:37
3 2222 2020-02-20 12:46:37
4      2020-02-21 12:46:37
5 3333 2020-02-22 12:46:37
6 3333 2020-02-23 12:46:37
7      2020-02-24 12:46:37

こういった結果のデータを取得したいと思っています。

ID  product_name  house_code  create_date
2      2020-02-19 12:46:37
4      2020-02-21 12:46:37
6 3333 2020-02-23 12:46:37
7      2020-02-24 12:46:37

そこで現在こちらのsqlを作成したのですが、件数が10000で取得に5分以上かかります。

SELECT *
FROM tbl_products AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM tbl_products AS b
  WHERE
  (CASE 
        WHEN length(a.house_code) > 0 THEN a.product_name = b.product_name
        ELSE a.house_code = b.house_code
        END
  )
  AND a.create_date < b.create_date
);


**NOT EXISTS中のwhere句をcase when で分岐させている**のが遅くさせている原因だと思うのですが、他にやり方が分からず立ち止まっている状態です。
何かほかに良いsqlがあれば教えていただけないでしょうか?

試したこと

サブクエリでgroup byを使って取得するsqlで上手くいくのではないかと、現在試行錯誤しています。

補足情報(FW/ツールのバージョンなど)

現在postgresql9.6で開発をしています。

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • m.ts10806

    2020/02/28 10:51

    Analyze もつけたほうがどこに問題があるか時間かかっているか観れますので、そちらを。

    キャンセル

  • shakesuke

    2020/02/28 10:54

    >>m.ts10806さん

    アドバイスありがとうございます。
    やってみます。

    キャンセル

  • yureighost

    2020/02/28 12:22

    取得結果で分かりづらいのが、「い」や「は」は一つだけなのに、
    「へ」は二つ取れているところですね。
    複数のproduct_nameに跨るhouse_codeの場合はproduct_nameが複数取得されるのは容認するってことでしょうか。

    キャンセル

回答 4

+1

通常は別テーブルとして持つべきマスターテーブルとしての情報と履歴テーブルとしての情報をいっしょくたに持っていませんか?そのために同一キー(今回はproduct_name)の中で最新のデータを取得しなければならないのが負荷になっているのでは?

質問にインデックスの情報も追記した方が良いです。

CASE
    WHEN length(a.house_code) > 0 THEN a.product_name = b.product_name
    ELSE a.house_code = b.house_code
END


見るからにパフォーマンスが悪そう。

実行計画 PostgreSQLの実行計画について調べてみた が効率的かも確認しましょう。

EXISTS, NOT EXISTS などの副問合せを多用しなければならない場合もテーブル設計の正規化が間違っている場合が多いです。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

https://www.postgresql.jp/document/9.4/html/tutorial-window.html

rank関数を使えばできそうな気がします(PostgreSQLは持っていないので動作未検証ですが)。

-- 同一house_codeで、create_dateが大きい1件を取得する(house_code がnullでも''でもない場合)
SELECT ID, product_name, house_code, create_date
FROM
  (SELECT ID, product_name, house_code, create_date,
          rank() OVER (PARTITION BY house_code ORDER BY create_date DESC) AS pos
     FROM tbl_products 
     WHERE (house_code IS NOT NULL AND house_code <> '')
  ) AS house_code_rank
WHERE pos = 1

-- これの上と下のSELECT結果を連結する
UNION ALL

-- 同一product_nameで、create_dateが大きい1件を取得する(house_code がnullか''の場合)
SELECT ID, product_name, house_code, create_date
FROM
  (SELECT ID, product_name, house_code, create_date,
          rank() OVER (PARTITION BY product_name ORDER BY create_date DESC) AS pos
     FROM tbl_products 
     WHERE (house_code IS NULL OR house_code = '')
  ) AS product_name_rank
WHERE pos = 1;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2020/02/28 13:35

    URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先のタイトル](http...) に修正してください。

    キャンセル

0

10000件ではテストできておりませんが。例題とは同じ結果

with house_tbl AS (select a.* from tbl_products a join
(select house_code, max(create_date) create_date from tbl_products where house_code is not null group by house_code having(count(*)>1)) b
using (house_code,create_date)
),    name_tbl AS (select a.* from tbl_products a join
(select product_name, max(create_date) create_date from tbl_products where house_code is null group by 1) b
using (product_name,create_date)
)
select * from house_tbl
union all
select name_tbl.* from name_tbl left join house_tbl using(product_id) where house_tbl.product_id is null
order by 1;

WHERE句のCASEにて、こんな書き方できるんですね。

SELECT a.* FROM tbl_products AS a,
tbl_products AS b
WHERE
  CASE WHEN a.house_code is NULL THEN a.product_name = b.product_name
       ELSE a.house_code = b.house_code
  END
  AND a.create_date>b.create_date
;

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

0

主な理由としては履歴テーブルと混在してるせいで処理速度が落ちてる以外なんでもない

実行計画もなにもためさんけどインデックス処理ができるSQLを組みましょう
以下のように二つに分けたほうが早い場合が多いです。

SELECT *
FROM tbl_products AS a
WHERE 

NOT EXISTS (
  SELECT 1
  FROM tbl_products AS b
  WHERE a.product_name = b.product_name
  AND a.house_code IS NULL
  AND a.create_date < b.create_date -- 履歴テーブルは基本別にしましょう。遅い原因の一つです。
  )

AND 

NOT EXISTS (
  SELECT 1
  FROM tbl_products AS b
  WHERE a.house_code = b.house_code
  AND a.create_date < b.create_date -- 同上
  )
);

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

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

関連した質問

同じタグがついた質問を見る