none
再帰クエリで解けませんか? RRS feed

  • 質問

  • お世話になります。SQL Server 2005で、以下のコードで再帰クエリのアンカーまで書いたのですが、再帰の部分でどうにも先が続かなくなってしまいました。よろしくお願いします。
    IF OBJECT_ID('dbo.TEST') IS NOT NULL
    DROP TABLE dbo.TEST;
    GO
    CREATE TABLE dbo.TEST
    (ID int NOT NULL PRIMARY KEY, 
    Code nchar(1) NOT NULL, 
    Data nvarchar(max) NOT NULL);
    INSERT INTO dbo.TEST VALUES(1, 'a', 'AA');
    INSERT INTO dbo.TEST VALUES(2, 'a', 'BBBB');
    INSERT INTO dbo.TEST VALUES(3, 'b', 'CCC');
    INSERT INTO dbo.TEST VALUES(4, 'a', 'D');
    INSERT INTO dbo.TEST VALUES(5, 'a', 'EEEE');
    INSERT INTO dbo.TEST VALUES(6, 'b', 'FF');
    INSERT INTO dbo.TEST VALUES(7, 'b', 'GG');
    INSERT INTO dbo.TEST VALUES(8, 'a', 'H');
    WITH CTE(KEYCOL, CODECOL, DATACOL) AS
    (	 SELECT	T1.ID, T1.Code, T1.Data 
    	   FROM	(SELECT	MIN(ID) AS MINID, Code AS MINCODE 
    			   FROM	dbo.TEST GROUP BY Code) AS T0 
     INNER JOIN	dbo.TEST AS T1 
    		 ON	T0.MINID = T1.ID
    )
    SELECT * FROM CTE;
    欲しい結果は以下のとおりです。
    KEYCOL      CODECOL DATACOL
    ----------- ------- -------------------
    1           a       AABBBBDEEEEH
    3           b       CCCFFGG
    本来一行で表現されるべきレコードがいくつかに分割されているのをマージしたいという意味です。

    2010年3月10日 12:26

回答

  • とりあえず、こんな感じかなぁ?

    WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS
    (	 SELECT	T1.ID, T1.Code, T1.Data, 1 
    	   FROM	(SELECT	MIN(ID) AS MINID, Code AS MINCODE
    			   FROM	dbo.TEST GROUP BY Code) AS T0 
     INNER JOIN	dbo.TEST AS T1 
    		 ON	T0.MINID = T1.ID
    UNION ALL
    SELECT 
            T2.id, 
            T2.Code, 
            CTE.DATACOL + T2.Data,
            CTE.DATALEVEL + 1
        FROM dbo.TEST T2 
        INNER JOIN CTE ON T2.Code = CTE.CODECOL AND T2.id > CTE.KEYCOL
    )
    --select * from CTE
    select (select min(T1.KEYCOL) from CTE T1 where T1.CODECOL = T0.CODECOL),
            T0.CODECOL,
            (select top 1 T2.DATACOL from CTE T2 where T2.CODECOL = T0.CODECOL order by T2.DATALEVEL desc)
            from CTE T0 group by T0.CODECOL
    

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク じふ 2010年3月19日 3:52
    2010年3月11日 8:26
  • with work(ID,Code,Data) as(
    select 1, 'a', 'AA' union
    select 2, 'a', 'BBBB' union
    select 3, 'b', 'CCC' union
    select 4, 'a', 'D' union
    select 5, 'a', 'EEEE' union
    select 6, 'b', 'FF' union
    select 7, 'b', 'GG' union
    select 8, 'a', 'H' union
    select 9, 'c', 'II' union
    select 10, 'd', 'JJ')
    select min(ID) as KEYCOL,Code,
    (select '' + Data
     from work b
     where b.Code=a.Code
    order by ID
    FOR XML PATH('')) as DATACOL
    from work a
    group by Code;
    
    KEYCOL Code DATACOL
    ------ ---- ------------
      1 a AABBBBDEEEEH
      3 b CCCFFGG
      9 c II
     10 d JJ
    

    SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
    http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
    をふまえて作ってみました :-)

    Create A Comma Delimited List From a Column
    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList
    によると、FOR XML PATHを使うサブクエリでorder byを指定すれば文字列の連結順序を指定できるようですね。

     

     

     

    • 回答の候補に設定 Aketi Jyuuzou 2010年4月22日 12:14
    • 回答としてマーク じふ 2010年4月26日 18:17
    • 編集済み Aketi Jyuuzou 2010年11月3日 2:53
    2010年4月22日 12:10
  • このデータ構造には、重複値がないので、Row_Numberが1大きいものが最小上界であることに注目しつつ、
    さらに、Row_Numberの最大値は分析関数のcount関数で求まるということで、

    再度、SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
    http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
    をふまえて、SQLServer2008で再帰SQLを使う方法で作ってみました :-)

    with work(ID,Code,Data) as(
    select 1, 'a', 'AA' union
    select 2, 'a', 'BBBB' union
    select 3, 'b', 'CCC' union
    select 4, 'a', 'D' union
    select 5, 'a', 'EEEE' union
    select 6, 'b', 'FF' union
    select 7, 'b', 'GG' union
    select 8, 'a', 'H' union
    select 9, 'c', 'II' union
    select 10, 'd', 'JJ'),
    tmp(ID,Code,Data,rn,recCnt) as(
    select ID,Code,Data,Row_Number() over(partition by Code order by ID),
    count(*) over(partition by Code)
     from work),
    rec(KeyCol,Code,rn,DATACOL,recCnt) as(
    select ID,Code,rn,
    cast(Data as varchar(20)),recCnt
     from tmp
     where rn=1
    union all
    select a.KeyCol,a.Code,b.rn,
    cast(a.DATACOL + b.Data as varchar(20)),a.recCnt
     from rec a,tmp b
     where a.Code=b.Code
     and a.rn+1=b.rn)
    select *
     from rec
     where rn=recCnt
    order by Code;
    
    KeyCol Code rn DATACOL  recCnt
    ------ ---- -- ------------ ------
      1 a  5 AABBBBDEEEEH  5
      3 b  3 CCCFFGG   3
      9 c  1 II     1
     10 d  1 JJ     1
    


     

     

    • 回答の候補に設定 Aketi Jyuuzou 2010年4月26日 11:56
    • 回答としてマーク じふ 2010年4月26日 18:17
    • 編集済み Aketi Jyuuzou 2010年11月3日 2:52
    2010年4月26日 11:56

すべての返信

  • とりあえず、こんな感じかなぁ?

    WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS
    (	 SELECT	T1.ID, T1.Code, T1.Data, 1 
    	   FROM	(SELECT	MIN(ID) AS MINID, Code AS MINCODE
    			   FROM	dbo.TEST GROUP BY Code) AS T0 
     INNER JOIN	dbo.TEST AS T1 
    		 ON	T0.MINID = T1.ID
    UNION ALL
    SELECT 
            T2.id, 
            T2.Code, 
            CTE.DATACOL + T2.Data,
            CTE.DATALEVEL + 1
        FROM dbo.TEST T2 
        INNER JOIN CTE ON T2.Code = CTE.CODECOL AND T2.id > CTE.KEYCOL
    )
    --select * from CTE
    select (select min(T1.KEYCOL) from CTE T1 where T1.CODECOL = T0.CODECOL),
            T0.CODECOL,
            (select top 1 T2.DATACOL from CTE T2 where T2.CODECOL = T0.CODECOL order by T2.DATALEVEL desc)
            from CTE T0 group by T0.CODECOL
    

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    • 回答としてマーク じふ 2010年3月19日 3:52
    2010年3月11日 8:26
  • お世話になります。アンカーから始めて色々の組み合わせでつないで行った結果、最も階層の深くなったものを取り出すという発想ですね。勉強になります。
    実はその後下記のようなコードを書いてみたのですが、再帰部分では集約関数が使えないという制約に引っかかってしまいました。アンカーから初めて最小上界を順に繋いでいきたかったのですが...

    WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS
    (    SELECT T1.ID, T1.Code, T1.Data, 1 
    	   FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE
                   FROM dbo.TEST 
                  GROUP BY Code) AS T0 
     INNER JOIN dbo.TEST AS T1 
             ON T0.MINID = T1.ID
    UNION ALL 
         SELECT T2.ID, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1
           FROM CTE 
     INNER JOIN dbo.TEST AS T2 
             ON CTE.CODECOL = T2.Code 
            AND CTE.KEYCOL = (SELECT MIN(ID) FROM dbo.TEST AS T3 WHERE CTE.KEYCOL < T3.ID) 
    )
    SELECT * FROM CTE ORDER BY DATALEVEL, CODECOL;
    /*SELECT (SELECT MIN(T3.KEYCOL) FROM CTE AS T3 WHERE T3.CODECOL = T5.CODECOL),
            T5.CODECOL,
           (SELECT TOP 1 T4.DATACOL FROM CTE AS T4 WHERE T4.CODECOL = T5.CODECOL ORDER BY T4.DATALEVEL DESC)
      FROM CTE AS T5 
     GROUP BY T5.CODECOL;*/
    メッセージ 467、レベル 16、状態 1、行 15
    GROUP BY、HAVING、または集計関数は、再帰共通テーブル式 'CTE' の再帰部分では許可されません。
    

    2010年3月13日 3:33
  • 実はその後下記のようなコードを書いてみたのですが、再帰部分では集約関数が使えないという制約に引っかかってしまいました。アンカーから初めて最小上界を順に繋いでいきたかったのですが...
    そうなんですよ。CTEで集約関数が使えると便利なんですけどね。ちなみに外部結合などもできません。詳しくは以下に書かれています。今後、改善されるといいですね。

    WITH common_table_expression (Transact-SQL)
    http://msdn.microsoft.com/ja-jp/library/ms175972.aspx



    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2010年3月15日 12:18
  • お世話になります。やはりダメなんですね。となると、再帰部分はご提案の方法を使わせていただくとして、それを取り出す方法に専念した方が良さそうですね。
    IF OBJECT_ID('dbo.TEST') IS NOT NULL
    DROP TABLE dbo.TEST;
    GO
    CREATE TABLE dbo.TEST
    (ID int NOT NULL PRIMARY KEY, 
    Code nchar(1) NOT NULL, 
    Data nvarchar(max) NOT NULL);
    INSERT INTO dbo.TEST VALUES(1, 'a', 'AA');
    INSERT INTO dbo.TEST VALUES(2, 'a', 'BBBB');
    INSERT INTO dbo.TEST VALUES(3, 'b', 'CCC');
    INSERT INTO dbo.TEST VALUES(4, 'a', 'D');
    INSERT INTO dbo.TEST VALUES(5, 'a', 'EEEE');
    INSERT INTO dbo.TEST VALUES(6, 'b', 'FF');
    INSERT INTO dbo.TEST VALUES(7, 'b', 'GG');
    INSERT INTO dbo.TEST VALUES(8, 'a', 'H');
    INSERT INTO dbo.TEST VALUES(9, 'c', 'II');
    INSERT INTO dbo.TEST VALUES(10, 'd', 'JJ');
    WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS
    (	 SELECT	T1.ID, T1.Code, T1.Data, 1 
    	   FROM	(SELECT	MIN(ID) AS MINID, Code AS MINCODE
    			   FROM	dbo.TEST 
    			  GROUP	BY Code) AS T0 
     INNER JOIN	dbo.TEST AS T1 
    		 ON	T0.MINID = T1.ID
    UNION ALL
    	 SELECT	T2.ID, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1
    	   FROM	dbo.TEST AS T2 
     INNER JOIN	CTE 
    		 ON	CTE.CODECOL = T2.Code 
    		AND	CTE.KEYCOL < T2.ID 
    )
    SELECT T6.KEYCOL, T6.CODECOL, T6.DATACOL, T6.DATALEVEL
      FROM (SELECT T4.CODECOL AS T4CODE, MAX(DATALEVEL) AS MAXLEVEL 
              FROM CTE AS T4 
             GROUP BY T4.CODECOL) AS T5 
     INNER JOIN CTE AS T6
        ON T5.T4CODE = T6.CODECOL 
       AND T5.MAXLEVEL = T6.DATALEVEL;
    上記クエリの結果は以下のようになります。
    KEYCOL      CODECOL DATACOL             DATALEVEL
    ----------- ------- ------------- -----------
    9           c       II             1
    10          d       JJ            1
    7           b       CCCFFGG       3
    8           a       AABBBBDEEEEH  5
    dbo.TESTを以下のようにUPDATEできればその後は重複削除のみになるので、とりあえずこの方向で行ってみたいと思います。
    1            a      AABBBBDEEEEH
    2            a      AABBBBDEEEEH
    3            b      CCCFFGG
    4            a      AABBBBDEEEEH
    5            a      AABBBBDEEEEH
    6            b      CCCFFGG
    7            b      CCCFFGG
    8            a      AABBBBDEEEEH
    9            c      II
    10           d      JJ
    2010年3月16日 4:07
  • ちょっと間が空いてしまいましたが、一旦締めたいと思います。また分からないことがありましたら質問に上がります。ありがとうございました。
    2010年3月19日 3:53
  • PostgreSQL8.4で解いてみました :-)

    with recursive tmp(ID,Code,Data) as(
    values(1, 'a', 'AA'),
          (2, 'a', 'BBBB'),
          (3, 'b', 'CCC'),
          (4, 'a', 'D'),
          (5, 'a', 'EEEE'),
          (6, 'b', 'FF'),
          (7, 'b', 'GG'),
          (8, 'a', 'H'),
          (9, 'c', 'II'),
          (10, 'd', 'JJ')),
    wk (ID,Code,Data,rn,recCnt) as(
    select ID,Code,Data,Row_Number() over(partition by Code order by ID) as rn,
    count(*) over(partition by Code) as recCnt
      from tmp),
    rec(KeyCol,Code,rn,DATACOL,recCnt) as(
    select ID,Code,rn,Data,recCnt
      from wk
     where rn=1
    union all
    select a.KeyCol,a.Code,b.rn,a.DATACOL || b.Data,a.recCnt
      from rec a,wk b
     where a.Code=b.Code
       and a.rn+1=b.rn)
    select *
      from rec
     where rn=recCnt
    order by Code;
    
     keycol | code | rn |   datacol    | reccnt
    --------+------+----+--------------+--------
          1 | a    |  5 | AABBBBDEEEEH |      5
          3 | b    |  3 | CCCFFGG      |      3
          9 | c    |  1 | II           |      1
         10 | d    |  1 | JJ           |      1
    
    

    そもそも、SQLServer2005なら、group_concatもどきを使えばシンプルなSQLになりそうですが
    http://blog.livedoor.jp/oolv/archives/370268.html
    ただし、dataの連結順序の指定ができないはず・・・

    2010年3月23日 10:57
  • そもそも、SQLServer2005なら、group_concatもどきを使えばシンプルなSQLになりそうですが
    http://blog.livedoor.jp/oolv/archives/370268.html
    ただし、dataの連結順序の指定ができないはず・・・
    お世話になります.連結順序はIDの昇順になっているのでdataの連結順序が指定できないとなると,ちょっと厳しいですね.もともと第一正規形になってないテーブルを正規化するための手順ですので,どうしても手続き的な処理が必要だったのです.そのためには再帰クエリでの処理が良いのではないかと考えた次第です.
    2010年3月24日 17:08
  • with work(ID,Code,Data) as(
    select 1, 'a', 'AA' union
    select 2, 'a', 'BBBB' union
    select 3, 'b', 'CCC' union
    select 4, 'a', 'D' union
    select 5, 'a', 'EEEE' union
    select 6, 'b', 'FF' union
    select 7, 'b', 'GG' union
    select 8, 'a', 'H' union
    select 9, 'c', 'II' union
    select 10, 'd', 'JJ')
    select min(ID) as KEYCOL,Code,
    (select '' + Data
     from work b
     where b.Code=a.Code
    order by ID
    FOR XML PATH('')) as DATACOL
    from work a
    group by Code;
    
    KEYCOL Code DATACOL
    ------ ---- ------------
      1 a AABBBBDEEEEH
      3 b CCCFFGG
      9 c II
     10 d JJ
    

    SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
    http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
    をふまえて作ってみました :-)

    Create A Comma Delimited List From a Column
    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList
    によると、FOR XML PATHを使うサブクエリでorder byを指定すれば文字列の連結順序を指定できるようですね。

     

     

     

    • 回答の候補に設定 Aketi Jyuuzou 2010年4月22日 12:14
    • 回答としてマーク じふ 2010年4月26日 18:17
    • 編集済み Aketi Jyuuzou 2010年11月3日 2:53
    2010年4月22日 12:10
  • このデータ構造には、重複値がないので、Row_Numberが1大きいものが最小上界であることに注目しつつ、
    さらに、Row_Numberの最大値は分析関数のcount関数で求まるということで、

    再度、SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
    http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
    をふまえて、SQLServer2008で再帰SQLを使う方法で作ってみました :-)

    with work(ID,Code,Data) as(
    select 1, 'a', 'AA' union
    select 2, 'a', 'BBBB' union
    select 3, 'b', 'CCC' union
    select 4, 'a', 'D' union
    select 5, 'a', 'EEEE' union
    select 6, 'b', 'FF' union
    select 7, 'b', 'GG' union
    select 8, 'a', 'H' union
    select 9, 'c', 'II' union
    select 10, 'd', 'JJ'),
    tmp(ID,Code,Data,rn,recCnt) as(
    select ID,Code,Data,Row_Number() over(partition by Code order by ID),
    count(*) over(partition by Code)
     from work),
    rec(KeyCol,Code,rn,DATACOL,recCnt) as(
    select ID,Code,rn,
    cast(Data as varchar(20)),recCnt
     from tmp
     where rn=1
    union all
    select a.KeyCol,a.Code,b.rn,
    cast(a.DATACOL + b.Data as varchar(20)),a.recCnt
     from rec a,tmp b
     where a.Code=b.Code
     and a.rn+1=b.rn)
    select *
     from rec
     where rn=recCnt
    order by Code;
    
    KeyCol Code rn DATACOL  recCnt
    ------ ---- -- ------------ ------
      1 a  5 AABBBBDEEEEH  5
      3 b  3 CCCFFGG   3
      9 c  1 II     1
     10 d  1 JJ     1
    


     

     

    • 回答の候補に設定 Aketi Jyuuzou 2010年4月26日 11:56
    • 回答としてマーク じふ 2010年4月26日 18:17
    • 編集済み Aketi Jyuuzou 2010年11月3日 2:52
    2010年4月26日 11:56
  • お世話になります.分析関数は使用経験がなくて半分も理解できてないんですが,勉強になります.とりあえず下記のコードで求める結果は出ます.

    http://social.msdn.microsoft.com:80/Forums/ja-JP/transactsql/thread/09be2b0e-033d-4ca6-99a3-b0b91d72b651

    WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS
    (	 SELECT T1.ID, T1.Code, T1.Data, 1 
    	  FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE
    		  FROM dbo.TEST 
    		 GROUP BY Code) AS T0 
       INNER JOIN	dbo.TEST AS T1 
    	   ON T0.MINID = T1.ID
    UNION ALL
    	 SELECT	T2.ID, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1
    	  FROM	dbo.TEST AS T2 
       INNER JOIN	CTE 
    	   ON	CTE.CODECOL = T2.Code 
    	  AND	CTE.KEYCOL < T2.ID 
    ),CTE2 AS
    (
    SELECT T6.KEYCOL, T6.CODECOL, T6.DATACOL, T6.DATALEVEL
     FROM (SELECT T4.CODECOL AS T4CODE, MAX(DATALEVEL) AS MAXLEVEL 
         FROM CTE AS T4 
         GROUP BY T4.CODECOL) AS T5 
     INNER JOIN CTE AS T6
      ON T5.T4CODE = T6.CODECOL 
      AND T5.MAXLEVEL = T6.DATALEVEL
     )
     UPDATE T
     SET Data = C2.DataCol
     FROM dbo.Test AS T
    	INNER JOIN CTE2 AS c2 ON c2.CodeCol = T.Code

    2010年4月26日 18:17