階層構造データの効率的な検索方法とは？

前へ 次へ
このQ&Aのポイント
  • 階層構造データを効率的に検索する方法について探しています。現在は10回のSQLを発行しているため、パフォーマンスが低下しています。
  • PostgreSQL8.2やMySQL5を使用しているアプリケーションで、階層構造データを1回のSQLで検索する方法を知りたいです。
  • 階層構造データを含む部品や製品の構成を効率的に取得する方法をご教示いただけると幸いです。
回答を見る
  • ベストアンサー

階層構造データの効率的な検索

　ある製品の部品、その部品を構成する部品、そしてその……というデータをDBに格納しています。 　それぞれの部品にはIDがついていて、ツリーで見るとこんなかんじです。 　 100 +120 　+300 　　+250　 　　+290 　これは、100というIDを持った部品にはIDが120の部品があり、120の部品には300という部品があり、、、、ということを表しています。 　これを以下のようなテーブルに持っています。 items id | child_id | ---------- 100 | 120 | 120 | 300 | 300 | 250 | 300 | 290 | 　ここで、ある部品のIDをキーにして、その部品を構成する部品全て（つまり、子部品、孫部品、ひ孫部品……）のIDを検索するようなSQLを書きたいと思っていますが、巧い方法が思い浮かびません。（今は10階層あったら、10回SQLを発行しています） 　これを1回のSQLで、全てを検索することはできないでしょうか。言うまでもありませんが、10回のSQLよりもパフォーマンスは良くないと困りますが。。。 　周りの人はみんなできないんじゃないかと言っていますが、もしできる、という方法をご存じの方はご教示いただければと思います。 　DBはPostgreSQL8.2ですが、MySQL5を使っているアプリでも同様の問題があるので、そちらでも使えればうれしいです。 　よろしくお願いいたします。

  • annyG
  • お礼率70% (67/95)

質問者が選んだベストアンサー

  • ベストアンサー
回答No.1

PostgreSQLでは、Oracleに似た「階層問い合わせ」という機能が、標準搭載ではないですが、実装されています。 http://www.thinkit.co.jp/free/marugoto/2/1/23/1.html MySQLでは、まだこの辺の機能は実装されていません。階層を制限した形で実装するとか、ストアドプロシジャで作りこむといった対処になると思います。 標準SQLでは、SQL99で「再帰クエリ」(with句を使用）が規定されており、MySQLでも将来的には実装するのではないかと思います。

annyG
質問者

お礼

ありがとうございます。 まったく予想外のご回答だったのでちょっと驚いてしまいました。そんなのがあるのですね。 しかし、このような機能が追加されなければならない、ということはやはり簡単には、少なくともSQL一発で実現するのは難しいか無理、ということでしょうか。 現在はJavaのメソッドを再帰で呼んで実現しているのですが、これだと「在庫が50以下」というような条件があったときでも、それを素直にwhereに書くと、親部品の在庫が50以上あると引っかからず、その部品の子どもに検索にいかなくなってしまうので、条件がつけられないんですよね。。。 とても勉強になりました。ありがとうございました。

annyG
質問者

補足

お礼に書き忘れましたが、顧客に尋ねたところ、標準搭載ではない機能を使うのはサポートの点で難しい、とのことでした。 一応補足です。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. PostgreSQL

関連するQ&A

  • Perlでツリー構造を生成

    質問です。 Perlで以下のような処理をしたい場合どういうコードを書けばよいでしょうか 処理の内容を簡単に説明しますと、 店舗情報を格納しているDBがあるとします。 DBから取得した、X件の情報を、子がN店ずつのツリー構造にした上で、DBの取得してきたレコードに親の店舗IDなりを更新するスクリプトです。 親が1店舗からはじまり、子がN店の構成です、子はその下に孫としてまたN店もちます。 分かりづらいですね・・・もう少し細かく説明します。 1.DBから店舗情報を取得してくる 2.10件の店舗があったとする、IDがshop1,shop2,shop3...shop10と続く 3.その中からランダムで一件決める。今回はshop3とする 4.ランダムでshop3の子をN件決める。今回はshop3の子をshop1とshop2とする(N = 2とする) 6.今度はshop1とshop2の子を決める(残りのshop4～10より決める) 7.4の処理に戻り、孫世代、ひ孫世代を決定する 8.端数は一人っ子ができても問題なし、子なしに落ち着くまで繰り返す 9.すべての店をチェックして親をレコードに更新する。 といったような流れの処理になります。 処理の流れは浮かんでいるのですが、Perlでどう書けばいいのか四苦八苦しています。 (自分のIDと親のIDを結びつけつつ保持し続けておくのがとくにわかりません・・・) 以上になりますがよろしくおねがいします

  • 効率のよい検索

    処理ロジックについてご教授願いたいです。 点１－－－点２－－－点３－－－点４ 　　　　線１　　　　線２　　　　　線３ ◆線マスタ 線１は、点１・点２で構成 線２は、点２・点３で構成 線３は、点３・点４で構成 上記のように、点１～点４までは３つの線・６つの点で構成されているとします。 以下のように線２が欠落して情報が無い場合に線１、線３と線マスタを使用し 線２を補いたいのです。線形検索で順に検索すれば可能ですが、 何か効率のよい検索方法をどなたかしっていたら教えてください。 Javaのバッチで実装しようと思っています。 点１－－－点２　　　　点３－－－点４ 　　　　線１　　　　　　　　　　　線３ データ量が数十万件あるため何か効率のよい方法がないか考えている最中です。 考えた方法 (1)単純にDBを線形検索 →データ量が多いので非効率 (2)DBより点２以上点３以下を抽出し、配列に格納し配列内を検索 →点情報（数値）がシーケンスになっていないため抽出対象件数が多く配列が膨大になり 　　メモリが不安 (3)配列のインデックスを点情報（数値）にし線を検索 →点情報（数値）が万単位になるためメモリが不安 以上

  • データの検索の為に列を増やすのは正しいのか

    現在mySQLの勉強中です。 テーブルtableにidとnameとunixtimeが格納されています。 登録件数30000件。idは、登録申請時に順に割り振られています。 unixtimeはnameに対応する人間の登録確定時を表したものです。 したがって、idとunixtimeの順番は必ずしも一致しません このデータから、PHPを使って登録確定日別でid、nameのリストをhtmlで作成したいと思います。 たとえば20001224.htmlには2000年12月24日に登録確定した人のリストが入っているようにしたいです。 このような出力を効率的に行うために、unixtimeからyearとmonthとdateを作成しました。データ型はすべてintです。 あるデータのunixtimeが2011/12/31の場合、yearは2011、monthは12、dateは31となります。 htmlを作成するにあたり、データベースからデータを出力しなければいけません。そのため、 まずdateが条件に一致するデータを抽出して配列にidとmonthとyearを格納し、 次にmonthが条件に一致するものを抽出して配列にidとyearに格納し、 最後にyearが一致するデータのidを抽出し、idに該当するデータをechoしようと考えています。 が、この方法は正しいのでしょうか。 SQLは検索にいろいろテクニックが必要で、方法を誤ると途端に検索速度が速くなったり遅くなったりすると聞きました。 列を増やすことで、メモリの使用量が増えて検索が遅くなったり、もしかしたらunixtimeで上手に検索する方法があるのかもしれないと思います。 どなたかこういう時はどういう検索を行うのが正しいのか、ご教示ください。 どうかよろしくお願いします。

    • ベストアンサー
    • MySQL
  • 重複データを一つづつ表示したいです。

    mysqlの命令文等いろいろと試行錯誤しています。 添付画像のように、attachment_nameは画像なのですが、item_idが重複されていますので どれでもよろしいですので、各一つづつ表示させたいです。 mysql文はいろいろと結合していますが、下記の通りです。 function getfeatured01($sales_type='rental'){ $db = Database::instance(); //$rsfeat = $db->query("select item_id from items"); $sql = "SELECT *, attachment.name as attachment_name, ldk_type.id as ldkid, ldk_type.name as ldkname, caution_money_num.name as cautionmoneynum_name, reward_num.name as rewardnum_name FROM items INNER JOIN ldk_type ON ldk_type.id = items.ldk_type INNER JOIN caution_money_num ON caution_money_num.id = items.caution_money_num INNER JOIN reward_num ON reward_num.id = items.reward_num INNER JOIN attachment ON attachment.item_id = items.item_id WHERE sales_type = 'rental' and status = '1' order by price asc LIMIT 0,20 "; $rsfeat = $db->query($sql); return $rsfeat; } いろいろと検索してみますが、基本がなっていないせいか、理解ができません。 今回MySQLをご指導いただきたく質問いたしました。 申し訳ございませんが、ご指導のほど、宜しくお願いいたします。

    • ベストアンサー
    • MySQL
  • sqlで日付が一番古いデータの月を取得する方法

    言語：php DB：postgresql 質問させて頂きます。 現在DBに入ったデータから一番古い年月を取得しようとしています。 「DBの中身（仮）」 [id][date] 1 2012-05-18 18:20:16.064554 2 2012-04-17 18:43:12.732035 この際古いのは2個目のデータなので、 これを判断して「201204」という形で取得したいと考えているのですが、 この処理を行うにはどのようなSQLを実行すればよいのでしょうか？ SQLのみで判断できるのか、一旦全データを取得したうえでPHPで判断する必要があるのか、 もじ方法をご存知の方がいらっしゃいましたらよろしくお願い致します。 また、質問に不備がありましたら、すぐに修正致しますのでご指摘お願いします。

    • ベストアンサー
    • PHP
  • VB.NETでDataTableにデータ追加したい

    VisualStugio.Net2003でソースコードを記述しており、コード内でSQLのSELECT文を実行して、実行結果をDataTableに格納する処理をしています。 今回、とあるテーブルに対し２パターンのSQLを実行し、それぞれの実行結果を１つのDataTableに格納したいのですが、どのようにすれば良いでしょうか？ 自分で調べたところ、先に実行したSQL結果に、後から実行したSQL結果を１行ずつ追加するといった方法は見つかりましたが、１行ずつではなく、一括で追加する方法が知りたいです。 前提として、２回のSQL実行では、同じテーブルの同じ項目をSELECTしております。 下記に簡単ですがコード記述の概要を補足しておきます。 ・コード記述概要 【変数宣言】 Dim DB1 As DataTable Dim DB2 As DataTable 【SQL文1】 SELECT A,B,C FROM テーブル１ WHERE 条件文１ DB1 = SQL文1の実行結果 【SQL文2】 SELECT A,B,C FROM テーブル１ WHERE 条件文２ DB2 = SQL文2の実行結果 【DB1にDB2のデータを追加】←ここのやり方を知りたいです DB1 = DB1 + DB2 よろしくお願いいたします。

  • SQL文にて・・・

    質問があります。PostgreSQLです。 テーブル（test_tbl)があるとします。 テーブル構成は --------------------------- id ===== char(16) [英数文字格納] point ==== int2 add_date ==== timestamp --------------------------- このテーブルから idが2文字目から'di6ek68dh5ls7g'のレコードを取得したいと考えています。 レコード数がかなりおおいので パフォーマンスを重視したいのですが、 検索ＳＱＬがわかりません。 select * from test_tbl where id like '%di6ek68dh5ls7g'だとでると おもうのですが、 これ以上にパフォーマンスがあがる ＳＱＬがわかる方お願いいたします。

  • PostgreSQLの配列項目のデータ展開の方法がわかりません

    PostgreSQL7.4 + PHP4.3 + Pearの環境です。 配列の項目から要素を展開しようとしたところでハマりました。 $sql = "select * from T"; $result = $db->query($sql); $row = $result->fetchRow(DB_FETCHMODE_OBJECT)) $id = $row->id; $a1 = $row->a1; a1は配列項目で、0,1,2,3 のように要素が入っているのですが、上のソースだと、 $a1[1]='{' $a1[2]='0' $a1[3]=',' $a1[4]='1' のように展開されてしまいました。select文からみるとa1項目は {0,1,2,3} という文字列で入っているのがわかりました。 SQL文が select a1[1],a1[2],a1[3],a1[4] from T; のような形だと正しく取得できますが、実際は配列の要素数が不定なのでこのようなSQLは使えません（長くなるし） 色々調べてみましたが、 $a1='{0,1,2,3}' を $a1[1]='0' $a1[2]='1' $a1[3]='2' $a1[4]='3' に展開する関数が見当たらないみたいです。PostgreSQLの配列項目を扱ったのは初めてなのでとまどっているのですが、簡単にarray変数に展開する方法はないでしょうか。

    • 締切済み
    • PHP
  • フラグがたっているデータがあったら検索かけたい・・。

    Ａテーブルにグループ番号、ＩＤ番号、氏名、ステータスフラグが格納されており、ＢテーブルにＩＤ番号、氏名、電話番号、住所が格納されているとします。検索したい条件は、同じグループの中で、ステータスフラグがたっているレコードが１件でもあれば、ＩＤ番号を使って、Ｂテーブルと結合してグループ番号でGROUP BYしてデータがとりたいのです。例えば、Ａテーブルにグループ番号００１のグループ番号を持つレコードが５レコードあり、そのうちステータスフラグがたっているレコードが１レコード。００２のグループ番号を持つレコードが３レコードあり。ステータスフラグがたってるのが０レコードだとします。結果として、００１のグループはステータスフラグがたっているレコードがあるから、検索対象になるが、００２はステータスフラグがたっているレコードがないので、検索対象にならないといった具合なんですが・・・。よろしくお願いします。自分で考えたＳＱＬは次のものです。SELECT * FROM A, B WHERE A.ID_NUMBER=B.ID_NUMBER GROUP BY A.GROUP_NUMBER HAVING COUNT(A.STATUS=1)>0　こんな感じです。ただCOUNT関数ではこれではダメみたいで・・・。まだ、１年目の新人なので、どなたかご教授お願いします。

  • RedHatLinux＋PostgreSQLを使ったWebシステムのパ

    RedHatLinux＋PostgreSQLを使ったWebシステムのパフォーマンスチューニングについて。 RedHatLinux＋PostgreSQLを使ったWebシステムをある会社に開発してもらったのですが、 検索のパフォーマンスがとても悪く問題になっています。 まずは、システムのどの部分がボトルネックになっているかを調査したいのですが、どのような ソフト、またはコマンドを使って調査すればよいのでしょうか？ それと、ボトルネックになっている部分をどのように改善するかを教えていただけますでしょうか。 また、開発してもらったソースは公開されておらず、オブジェクトでの提供になっており SQL文が分かりません。 実行されたSQL文を解析する方法はありますでしょうか？ よろしくお願いいたします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する