再帰クエリで解けませんか？

Question

お世話になります。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

本来一行で表現されるべきレコードがいくつかに分割されているのをマージしたいという意味です。

Answer 1

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

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/

Answer 2

お世話になります。アンカーから始めて色々の組み合わせでつないで行った結果、最も階層の深くなったものを取り出すという発想ですね。勉強になります。

実はその後下記のようなコードを書いてみたのですが、再帰部分では集約関数が使えないという制約に引っかかってしまいました。アンカーから初めて最小上界を順に繋いでいきたかったのですが...

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' の再帰部分では許可されません。

Answer 3

実はその後下記のようなコードを書いてみたのですが、再帰部分では集約関数が使えないという制約に引っかかってしまいました。アンカーから初めて最小上界を順に繋いでいきたかったのですが...

そうなんですよ。CTEで集約関数が使えると便利なんですけどね。ちなみに外部結合などもできません。詳しくは以下に書かれています。今後、改善されるといいですね。

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

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/

Answer 4

お世話になります。やはりダメなんですね。となると、再帰部分はご提案の方法を使わせていただくとして、それを取り出す方法に専念した方が良さそうですね。

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

Answer 5

ちょっと間が空いてしまいましたが、一旦締めたいと思います。また分からないことがありましたら質問に上がります。ありがとうございました。

Answer 6

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の連結順序の指定ができないはず・・・

Answer 7

そもそも、SQLServer2005なら、group_concatもどきを使えばシンプルなSQLになりそうですが

http://blog.livedoor.jp/oolv/archives/370268.html

ただし、dataの連結順序の指定ができないはず・・・

お世話になります．連結順序はIDの昇順になっているのでdataの連結順序が指定できないとなると，ちょっと厳しいですね．もともと第一正規形になってないテーブルを正規化するための手順ですので，どうしても手続き的な処理が必要だったのです．そのためには再帰クエリでの処理が良いのではないかと考えた次第です．

Answer 8

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を指定すれば文字列の連結順序を指定できるようですね。

 

 

 

Answer 9

このデータ構造には、重複値がないので、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

 

 

Answer 10

お世話になります．分析関数は使用経験がなくて半分も理解できてないんですが，勉強になります．とりあえず下記のコードで求める結果は出ます．

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