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

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

ただいまの
回答率

91.01%

  • SQL

    2011questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    867questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

  • Treasure Data

    11questions

    Treasure Dataは、米国トレジャーデータ社の提供するクラウド型のデータマネジメントサービスです。ビッグデータ処理システムを備え、AWS上にセットアップされているため、データの集収、保管、分析を容易に行うことができます。

親IDをたくさん持つテーブルを、親ID(name)が左から順に並んでいるような表の形で抽出したい

解決済

回答 1

投稿

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

9nom

score 30

親IDを持つこれらを、親のnameから順に左から並んでいるテーブルを作成したいです
より子供の方からleftjoinでparent_idを紐付けてくっつけていくと、親の階層が揃いません。
親の方から数えると、aとgは同じ階層になるはずですが、親をひたすらたどるクエリを書くと、階層数が異なるため、崩れた表になってしまいます。どのように書いたらいいでしょうか

id parent_id name
1 null a
2 3 b
3 4 c
4 1 d
5 null e
6 7 f
7 null g
8 6 h
9 null i
10 null j
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • mts10806

    2017/10/06 09:15

    今現在組まれているSQLをご提示ください。また、親・子のテーブル定義とサンプルレコードがあればそれもあわせてお願いします。

    キャンセル

回答 1

checkベストアンサー

+2

こういった自己結合によるツリー形式のテーブル(ナイーブツリー)にはwith recursiveによる再帰読出を使用すると、階層の深さなどを条件として意識する必要がありません。
但し、自己結合の関係(今回であれば、idとparent_id)がループするデータがあると無限ループします。
階層の深さで制限を掛けるなどしてデータの確認を行って下さい。
※サンプルでは階層のレベル(=lvl)も付加してあります。

データ

drop table if exists list;
create table list (id int, parent_id int, name text);
insert into list values
 (1,null,'a') 
,(2,3,'b') 
,(3,4,'c') 
,(4,1,'d') 
,(5,Null,'e') 
,(6,7,'f') 
,(7,null,'g') 
,(8,6,'h') 
,(9,null,'i') 
,(10,null,'j') 
;

問い合わせ(Postgres)

with recursive tree as (
  select *
       , 1 ::int as lvl
       , array[name] as lvl_name 
  from list
  where  parent_id is null
 union all
  select list.*
       , tree.lvl+1
       , array_append(tree.lvl_name, list.name)
  from list inner join tree on list.parent_id=tree.id
)
select id, parent_id, name
     , lvl_name[1] as name1
     , lvl_name[2] as name2
     , lvl_name[3] as name3
     , lvl_name[4] as name4
from tree
-- where lvl<=4
order by lvl_name

追記 問い合わせ(Hive)

親を辿る方向で外部結合し、その状況から何階層目かを求めます(ここポイント)
その位置情報から取得すべき階層を判定し求めます。
※元データにこの何階層目かを判断できる情報があれば、その分簡潔になります。

select id, parent_id, name
     , case lvl when 4 then name_nest4 when 3 then name_nest3 when 2 then name_nest2 when 1 then name_nest1 end as name1 
     , case lvl when 4 then name_nest3 when 3 then name_nest2 when 2 then name_nest1 end as name2 
     , case lvl when 4 then name_nest2 when 3 then name_nest1 end as name3 
     , case lvl when 4 then name_nest1 end as name4 
from (
  select t1.*
       , t1.name as name_nest1
       , t2.name as name_nest2
       , t3.name as name_nest3
       , t4.name as name_nest4
       , case when t4.id is not null then 4 
           else case when t3.id is not null then 3
             else case when t2.id is not null then 2 else 1 end
         end end as lvl
  from list t1 left join list t2 on t2.id=t1.parent_id
       left join list t3 on t3.id=t2.parent_id
       left join list t4 on t4.id=t3.parent_id
) step1

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/10/06 15:57

    回答ありがとございます!
    Treasuredataを使っているのですが、(基本的にpostgresと同じですが)トレジャーデータではwith recursive treeがサポートされていないみたいでした、、、

    キャンセル

  • 2017/10/06 17:17 編集

    追記しました。Hiveは扱ったことが無く、ネットの情報のみで記述しているのでエラーになるかもしれません。
    並びは名前順でNulls First にしようとしましたが、可能かどうか分からなかったので、割愛。
    階層はサンプルに合わせて4階層としているので、実際の階層数に合わせて下さい。

    キャンセル

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

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

関連した質問

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

  • SQL

    2011questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    867questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

  • Treasure Data

    11questions

    Treasure Dataは、米国トレジャーデータ社の提供するクラウド型のデータマネジメントサービスです。ビッグデータ処理システムを備え、AWS上にセットアップされているため、データの集収、保管、分析を容易に行うことができます。