어떻게 SQL과 "이적 그룹"레이블을?

I는 "A t의 B"및 "B T는 C" "A t C"의 경우, 전이되는 관계 t,에 ID 쌍과 테이블을 갖는다. 견본:

  table T1
  ID1 | ID2 
  1   | 2
  1   | 5
  4   | 7
  7   | 8
  9   | 1

따라서 두 그룹이,

나는 "순수하고 표준 SQL"새 테이블 또는 뷰에 의해, 생산에 필요

  table T2
  ID1 | ID2 | LABEL 
  1   | 2   | 1
  1   | 5   | 1
  4   | 7   | 2
  7   | 8   | 2
  9   | 1   | 1

PS-1 : 우리는에 의해 "이적 단체"를 나열 할 수 있습니다

  SELECT DISTINCT label, id   
  FROM (SELECT id1 as id, * FROM T2) UNION (SELECT id2 as id, * FROM T2)
  ORDER BY 1,2;

PS-2 : 나는 PostgreSQL을 9.1을 사용하고 있지만, "표준 SQL"과 해결책이 있다면 내가 선호합니다.

해결법

==============================
1.당신은 포스트 그레스에서이 작업을 수행 할 수 있습니다; 당신은 모든 데이터베이스에서이 작업을 수행 할 수 없습니다. 다음 쿼리는 다음과 같습니다

당신은 포스트 그레스에서이 작업을 수행 할 수 있습니다; 당신은 모든 데이터베이스에서이 작업을 수행 할 수 없습니다. 다음 쿼리는 다음과 같습니다
```
with 
    recursive cte(id1, id2) as (
     select id1, id2, 1 as level
     from t
     union all
     select t.id1, cte.id2, cte.level + 1
     from t join
          cte
          on t.id2 = cte.id1
  )
select id1, id2,
       dense_rank() over (order by grp) as label
from (select id1, id2,
             least(min(id2) over (partition by id1), min(id1) over (partition by id2)) as grp,
             level
      from cte
     ) t
where level = 1;
```
여기에 SQL 바이올린 함께.

당신은 (사이클 그런데이 특정 버전에 문제가 포즈 수) 라벨을 지정하기 위해 트리 구조를 걷고있다. 포스트 그레스, 당신은 명시 적으로 재귀 CTE를 사용하여이 작업을 수행 할 수 있습니다. SQL Server에서 암시 적으로 "재귀 적"인 CTE하여이 작업을 수행 할 수 있습니다 (키 단어가 사용되지 않습니다). 오라클에서는 연결하여이 작업을 수행 할 수 있습니다.

재귀 CTE는 서로 연결되어있는 모든 쌍을 가져옵니다. 주요 질의는 서로 연결되어있는 모든 쌍을 식별하기 위해, 한 쌍에 ID1과 ID2의 최소 값을 지정합니다. 마지막 레이블은 바로 GRP에 순차적으로 값을 할당하여 생산된다.

편집하다:

에고는 아주 좋은 지적을합니다. 상기 가정은 상기 작은 값으로 식별자 "이 하강"이. 다음 버전은 대신 (정말 무엇을하는 것은 의도 됨) 그룹에 대한 각 ID에 대한 가장 높은 수준을 사용합니다 :
```
with 
    recursive cte(id1, id2) as (
     select id1, id2, 1 as level
     from t
     union all
     select t.id1, cte.id2, cte.level + 1
     from t join
          cte
          on t.id2 = cte.id1
    --  where not exists (select 1 from cte cte2 where cte2.id1 = t.id1 and cte2.id2 = t.id2) 
  ) 
select id1, id2,
       dense_rank() over (order by topvalue) as label
from (select id1, id2,
             first_value(id2) over (partition by id1 order by level desc) as topvalue,
             level
      from cte
     ) t
where level = 1;
```
믹스 II :

에고의 두 번째 의견에 대응. 이 데이터는 원래의 문제에 대한 약간의 문제가있다. 두 조각으로 다음 휴식을 :
```
with 
    recursive cte as (
     select id1, id2, id2 as last, id1||','||id2 as grp, 1 as level
     from t
     where id2 not in (select id1 from t)
     union all
     select t.id1, t.id2, cte.last, cte.grp, cte.level + 1
     from t join
          cte
          on t.id2 = cte.id1
    --  where not exists (select 1 from cte cte2 where cte2.id1 = t.id1 and cte2.id2 = t.id2) 
  ) 
select *
from cte;
```
즉 원래 원한 경우, 그것은 분명하지 않다. 첫 번째 열에 결코 두 번째 열에 셋 식별자가 있기 때문에, 중복 세 그룹으로 원본을 깰 것이다. 여기에서 문제는 교환 법칙에 관한 것입니다.

==============================

2.이제 2013 년에 새로운 수요, 나는 10000 itens 작업해야 : GordonLinoff의 우아한 솔루션 @ (위)을 사용하여, 1000 itens 2000 필요 일일 1 초를 필요로 ... 좋은 성능을 가지고하지 않습니다. 성능의 문제는 여기에 기억 된

이제 2013 년에 새로운 수요, 나는 10000 itens 작업해야 : GordonLinoff의 우아한 솔루션 @ (위)을 사용하여, 1000 itens 2000 필요 일일 1 초를 필요로 ... 좋은 성능을 가지고하지 않습니다. 성능의 문제는 여기에 기억 된

(이 너무 빨리, 가장 좋은 방법입니다!) 원본과 교훈적인 설명을 참조하십시오. 여기서, 테이블 T1 질문 텍스트와 동일하며, 제 (임시) 테이블 R을 처리하고 그 결과를 표시하는 데 사용되는,

 CREATE TABLE R (
   id integer NOT NULL, -- PRIMARY KEY,
   label integer NOT NULL DEFAULT 0
 );
 CREATE FUNCTION t1r_labeler() RETURNS void AS $funcBody$
   DECLARE
      label1 integer;
      label2 integer;
      newlabel integer;
      t t1%rowtype;
   BEGIN
       DELETE FROM R;
       INSERT INTO R(id) 
           SELECT DISTINCT unnest(array[id1,id2]) 
           FROM T1 ORDER BY 1;
      newlabel:=0;
      FOR t IN SELECT * FROM t1
      LOOP   -- --  BASIC LABELING:  -- --
         SELECT label INTO label1 FROM R WHERE id=t.id1;
         SELECT label INTO label2 FROM R WHERE id=t.id2;
         IF label1=0 AND label2=0 THEN 
              newlabel:=newlabel+1;
              UPDATE R set label=newlabel WHERE ID in (t.id1,t.id2);
         ELSIF label1=0 AND label2!=0 THEN 
              UPDATE R set label=label2 WHERE ID=t.id1;
         ELSIF label1!=0 AND label2=0 THEN 
              UPDATE R set label=label1 WHERE ID=t.id2;
         ELSIF label1!=label2 THEN -- time consuming
              UPDATE tmp.R set label=label1 WHERE label = label2;
         END IF;
      END LOOP;
    END;
 $funcBody$ LANGUAGE plpgsql VOLATILE;

준비 및 실행,

 -- same CREATE TABLE T1 (id1 integer, id2 integer);
 DELETE FROM T1; 
 INSERT INTO T1(id1,id2)  -- populate the standard input
 VALUES (1, 2), (1, 5), (4, 7), (7, 8), (9, 1);
   -- or  SELECT id1, id2 FROM table_with_1000000_items;

 SELECT t1r_labeler();       -- run
 SELECT * FROM R ORDER BY 2; -- show

최악의 경우 다루기

마지막 조건, 때 LABEL1! = 라벨 2, 가장 시간이 많이 걸리는 작업입니다, 그리고 피해야한다 또는 최악의 것들 높은 연결성의 경우, 분리 할 수 있습니다.

경고의 어떤 종류를보고하려면이 절차는 마지막 조건을 실행하는 것을 시간의 비율을 셀 수, 및 / 코르는 마지막 업데이트를의 separete 수 있습니다.

당신은 별도의 경우, 분석하고 그들과 함께 더 나은 약간을 처리 할 수 그래서, 마지막 ELSIF를 제거하고 첫 번째 루프 수표와 두 번째 루프 이후에 추가 :

      -- ... first loop and checks here ...
      FOR t IN SELECT * FROM tmp.t1 
      LOOP   -- --  MERGING LABELS:  -- --
         SELECT label INTO label1 FROM R WHERE id=t.id1;
         SELECT label INTO label2 FROM R WHERE id=t.id2;
         IF label1!=0 AND label2!=0 AND label1!=label2 THEN
             UPDATE R set label=label1 WHERE label=label2;
         END IF;
      END LOOP;
      -- ...

최악의 경우의 예 : "10 라벨 그룹 당"(코어) 및 코어를 연결하는 단지 몇 경로의 평균 길이 만 개 노드에 1000 개 이상의 (연결) 노드 그룹.

이 다른 솔루션 (무차별 알고리즘입니다) 느린,하지만 당신은 배열을 처리하는 직접 필요로 할 때 util을 할 수 있고, 너무 빨리 솔루션이 필요 없습니다 (그리고 "최악의 경우"가 없습니다).

@ peter.petrov 및 @RBarryYoung가 더 적절한 데이터 구조를 사용하는 것이 좋습니다으로 ... 나는 "더 적절한 데이터 구조"로 내 배열을 다시했다. 결국, 좋은 속도까지 아래의 솔루션 (GordonLinoff의 알고리즘 @로 comparating)가있다 (!).

첫 번째 단계는 우리가 새로운 프로세스를 계산할 수있는 임시 하나, transgroup1에 질문 텍스트의 테이블 t1을 번역하는 것입니다,

 -- DROP table transgroup1;
 CREATE TABLE transgroup1 (
   id serial NOT NULL PRIMARY KEY,
   items integer[], -- two or more items in the transitive relationship
   dels integer[] DEFAULT array[]::integer[]
 );
 INSERT INTO transgroup1(items)
   SELECT array[id1, id2] FROM t1; -- now suppose t1 a 10000 items table;

그들은이 두 가지 기능을 우리는 문제를 해결할 수 있습니다,

 CREATE FUNCTION array_uunion(anyarray,anyarray) RETURNS anyarray AS $$
     -- ensures distinct items of a concatemation
    SELECT ARRAY(SELECT  unnest($1) UNION SELECT  unnest($2))
 $$ LANGUAGE sql immutable;


  CREATE FUNCTION transgroup1_loop() RETURNS void AS
  $BODY$
    DECLARE
       cp_dels integer[];
       i integer;
       max_i integer;
    BEGIN
       i:=1;
       max_i:=10; -- or 100 or more, but need some control to be secure
       LOOP
           UPDATE transgroup1
           SET items = array_uunion(transgroup1.items,t2.items),
               dels =  transgroup1.dels || t2.id
           FROM transgroup1 AS t1, transgroup1 AS t2
           WHERE transgroup1.id=t1.id AND t1.id>t2.id AND t1.items && t2.items;

           cp_dels := array(
               SELECT DISTINCT unnest(dels) FROM transgroup1
           ); -- ensures all itens to del
           EXIT WHEN i>max_i OR array_length(cp_dels,1)=0;

           DELETE FROM transgroup1 WHERE id IN (SELECT unnest(cp_dels));
           UPDATE transgroup1 SET dels=array[]::integer[];
           i:=i+1;
       END LOOP;
       UPDATE transgroup1         -- only to beautify
       SET items = ARRAY(SELECT unnest(items) ORDER BY 1 desc);
     END;
  $BODY$ LANGUAGE plpgsql VOLATILE;

물론, 실행하고 결과를 볼 수, 당신은 사용할 수 있습니다

 SELECT transgroup1_loop(); -- not 1 day but some hours!     
 SELECT *, dense_rank() over (ORDER BY id) AS group from transgroup1;

결과

 id |   items   | ssg_label | dels | group 
----+-----------+-----------+------+-------
  4 | {8,7,4}   | 1         | {}   |     1
  5 | {9,5,2,1} | 1         | {}   |     2

from https://stackoverflow.com/questions/18033115/how-to-label-transitive-groups-with-sql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 각 카테고리에서 MYSQL 하나를 선택 랜덤 기록 (0)	2020.07.09
[SQL] SQL Server의 조항에 해당하여 ORACLE 연결 (0)	2020.07.09
[SQL] SQL 서버의 칼럼에서 변환 행 값 (PIVOT) (0)	2020.07.08
[SQL] 지금 () 함수로부터 시간 빼기 (0)	2020.07.08
[SQL] INT (10) 및 INT (12) 사이에 (내 코드에 적용되는 경우)의 차이점은 무엇입니까? (0)	2020.07.08

복붙노트

[SQL] 어떻게 SQL과 "이적 그룹"레이블을?

어떻게 SQL과 "이적 그룹"레이블을?

해결법

1.당신은 포스트 그레스에서이 작업을 수행 할 수 있습니다; 당신은 모든 데이터베이스에서이 작업을 수행 할 수 없습니다. 다음 쿼리는 다음과 같습니다

2.이제 2013 년에 새로운 수요, 나는 10000 itens 작업해야 : GordonLinoff의 우아한 솔루션 @ (위)을 사용하여, 1000 itens 2000 필요 일일 1 초를 필요로 ... 좋은 성능을 가지고하지 않습니다. 성능의 문제는 여기에 기억 된

'SQL' 카테고리의 다른 글

티스토리툴바