Hatena::ブログ(Diary)

さり海馬 このページをアンテナに追加 RSSフィード

2009-06-01

教えてえらい人:複数行を連結するSQL - 解決編

教えてえらい人:複数行を連結するSQL より

この本読んでたら、まさにそれに近いスクリプトが載っていたので、今回のケースに合わせてアレンジしたものを記載しておきます。本を持っている人は

の p.118、「レシピ6.10 テーブル行から区切りリストを作成する」を参照してください。なおこのスクリプトOracle専用です。

SELECT
  作業番号
  ,LTRIM(SYS_CONNECT_BY_PATH( 製品名 || ' ' || 予定日 || ' ' || 実績日, ','), ',' ) as 作業一覧
FROM(
    SELECT
        製品名
        ,ROW_NUMBER() OVER
          (PARTITION BY 作業番号 ORDER BY 作業番号) rn
        ,COUNT(*) OVER
          (PARTITION BY 作業番号) cnt
    FROM
      工程表
    )
WHERE
  LEVEL = cnt
  START WITH rn = 1
  CONNECT BY PRIOR 作業番号 = 作業番号 AND PRIOR rn = rn-1

本来は階層構造を表現するための関数である SYS_CONNECT_BY_PATH() を使うあたりがスゲエと思いました。SQL仕事で使う人は、読んでおいて決して損のない本だと思います(…ちょっと高いけど)。

でびぃでびぃ 2009/06/02 07:46 ほぉ
しかしいま仕事でいやいやつかってるのは安物のsql*server(しくしく)

TareObjectsTareObjects 2009/06/02 13:11 SYS_CONNECT_BY_PATHなんて初めてみたw

thalionthalion 2009/06/02 13:49 いらっしゃいませ
>でびぃ
私もあんまりRDB方面は詳しくない、というか今のお仕事を振られるまではほとんど触れたことがないんで、どーにも、という感じです(笑)。

>TareObjects
俺も初めて見ました。というかこの本、ものすごくためになりますよ。強くお勧め。「こんなこともできるんだー!?」ってびっくりします。でも何でもかんでもSQLでやろうとするのは効率が悪いよね、とも思います。

2009-05-26

教えてえらい人:複数行を連結するSQL

SQLで困ってます。ターゲットは Oralce 9i ぐらい。

作業番号製品予定日実績日
001労働1号2009/3/142009/3/15
002労働2号2009/4/212009/4/18
003労働3号2009/4/302009/5/10
001労働1号2009/6/142009/6/15
002労働3号2009/7/212009/9/18
003労働2号2009/12/52009/12/10

というテーブル「工程表」があるとき、以下のような結果を得たければ、どういうクエリを作ればいいんでしょうか?

  • 作業番号をキーとして
  • 同じ作業番号を持つデータの「製品名、予定日、実績日」をスペースでつなぎ、コンマで連結する

求める結果はこんな感じです。なお、同じ作業番号を持つ製品の数は決まっていません。

作業番号作業一覧
001労働1号 2009/3/14 2009/3/15,労働1号 2009/6/14 2009/6/15
002労働2号 2009/4/21 2009/4/18,労働3号 2009/7/21 2009/9/18
003労働3号 2009/4/30 2009/5/10,労働2号 2009/12/5 2009/12/10

でびぃでびぃ 2009/05/26 21:41 行のエレメントの数が一定じゃないから一発クエリは無理じゃないかな?
PL/SQLでコード書いてループ文でがっちんがっちんくっつけたのを吐くしかねぇんでねえべかなぁ

TareObjectsTareObjects 2009/05/27 06:26 control breakでデリミタ入れて…とかつい手続きで考えちゃうなぁ。
セルコ本に出てたような記憶はあるんだけど。

びじうびじう 2009/05/28 08:10  小川一水の「第六大陸」でも月面居住区の建築には頭の良い自律型のドローンが使われていたよねぇ。それで核融合発電所の建築とかができたら一気にペイするって話しだったけど・・・。

thalionthalion 2009/05/28 16:51 いらっしゃいませ
>でびぃ
やっぱそうなりますよね。
SELECT
MAX(...),
とかで繋げる手も考えたんですが、やっぱ該当する製品数が可変というのがダメでして。

>TareObjects
で、そうなるとやっぱりプロシージャ方面に行かざるを得ないなぁ、なんて。

>びじう
後で聞いた話では、この会議のときに「何で二足歩行させんのかイミフw」「宇宙開発にもロマン必要かと…フヒヒ…サーセンw」的なやりとりがあったとか。

……そーだよね。二足歩行はロマンだよね。っていうかロマンしかないよね(笑)。

RadcliffeRadcliffe 2009/05/28 23:19 うんうん。二足歩行は浪漫。
でもバカなヤツらが、こんなことをパブコメで言ってるらしいよ。

http://radcliffe.at.webry.info/200905/article_12.html

thalionthalion 2009/06/01 13:10 >Radcliffe
重力などの異なる環境下で歩行させることには一定の意義があると思うし、何よりもまず「世界で初めて地球外二足歩行機器を作ったのは日本!」って言えるのはすごいと思うけどな。

……きっと「日本ならやると思ってた」とか「やっぱ日本人、未来に住んでるな」とか海外から言われそうだが。

2009-03-23

そのセミコロンは必要ない

cx_Oracle でクエリを投げるとき、SQL文末の";"は必要ない。セミコロンは SQL Plus の拡張文法だから。

…こんなことで一時間もつぶした…orz。

参考:http://logbookx.blogspot.com/2008/03/ora-00911.html

trac.db の中のテーブルを JOIN してみる

こんどは会社のSQLサーバーじゃなくて、trac の sqlite3 のデータの中身を解析することにしました。んでメモ

trac.db の中にはいろいろテーブルが入っている*1けれども、実際にチケットに直接関係しているのは ticket と ticket_custom だけ。

ticketは id を主キーとして、type, component, summary, …というように、各属性が並んでいる*2 *3

idtype.....statussummarydescription
1仕様変更.....newほげほげの仕様変更の件このままだとダメになるぞ
2仕様変更.....newほげほげの仕様変更の件2このままだとダメになるぞ2

ticket_custom はカスタム属性とその値のリストで、中身はこんな感じ

ticketnamevalue
1due_assign2009/3/10
1complete2009/3/15
1optionD
2due_assign2009/3/22
2complete2009/3/28
2optionX

こいつらをjoinして普通にtracで見える形にするには、こういう感じでクエリ組むといいと思う

SELECT
  id
  ,type
  ,summary
  ,a.value AS 'DUE_ASSIGN'
  ,c.value AS 'COMPLETE'
  ,op.value AS 'OPTION'
FROM
  ticket t
    JOIN ticket_custom f ON f.ticket = t.id AND a.name = 'due_assign'
    JOIN ticket_custom b ON b.ticket = t.id AND c.name = 'complete'
    JOIN ticket_custom op ON op.ticket = t.id AND op.name = 'option'
WHERE
 t.type = '仕様変更'
 ORDER BY t.value
;

出てくる結果はこんな感じ

idtype.....statussummarydescriptionDUE_ASSIGNCOMPLETEOPTION
1仕様変更.....newほげほげの仕様変更の件このままだとダメになるぞ2009/3/102009/3/15D
2仕様変更.....newほげほげの仕様変更の件2このままだとダメになるぞ22009/3/222009/3/28X

SQL初めてだったんですんげー悩みましたが、最終的にtracが吐き出すDEBUGログを眺めていて、同じテーブルを別のテーブルとして複数JOINしていいってことに気づいて解決しました。ログの中にはクエリがそのまま出力されてんのね。

*1:sqlite3 trac.db してから .tables すれば見られる

*2:ちなみにテーブルの中身をを確認するときは、.explain ON して、人間が理解しやすい出力にしとくと吉

*3:あと、確認するときは間違ってテーブルを破壊することのないように、trac.db をコピーして、コピーした方を使うことを強くお勧めしときます

MIZOMIZOMIZOMIZO 2009/03/23 22:34 単に「チケットをみる(report)」で、レポートの登録でやると、簡単に確認できますよ。

thalionthalion 2009/03/24 10:21 いらっしゃいませ

>MIZOMIZO
コメントありがとうございます。tracの環境下でやるならそれがいちばん簡単ですよね。実はこれ、会社の要件管理サーバー(Oracle)のデータを日々自動的に直接tracに取り込もうというプロジェクトの一環でして、pythonのスクリプトで直接 sqlite3 の db を触る必要があって、いろいろ勉強しているところなんです。

そうか、MIZOMIZOさんに聞くって手があったなぁ…。