복붙노트

[SQL] 고유 한 열 (ID 및 쉼표로 구분 된 목록) - 2 열만큼의 관계를 보이기

SQL

고유 한 열 (ID 및 쉼표로 구분 된 목록) - 2 열만큼의 관계를 보이기

나는이 2 SO 질문에 비슷한이 필요하지만, Informix의 SQL 구문을 사용하여.

이 같은 모습에 오는 내 데이터 :

id     codes

63592  PELL
58640  SUBL
58640  USBL
73571  PELL
73571  USBL
73571  SUBL

나는 이런 식으로 돌아와보고 싶어 :

id     codes 

63592  PELL
58640  SUBL, USBL
73571  PELL, USBL, SUBL

또한 인포믹스에서 GROUP_CONCAT ()를 참조하십시오.

해결법

  1. ==============================

    1.나는 당신이 필요로하는 답이 비슷한 사용자 정의 집계이라고 생각 :

    나는 당신이 필요로하는 답이 비슷한 사용자 정의 집계이라고 생각 :

    CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || ',' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    IF partial1 IS NULL OR partial1 = '' THEN
        RETURN partial2;
    ELIF partial2 IS NULL OR partial2 = '' THEN
        RETURN partial1;
    ELSE
        RETURN partial1 || ',' || partial2;
    END IF;
END FUNCTION;

CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);

    열이라고 함유 이름 (충분히 재미있게) 요소 이름 및 다른 열이라고 atomic_number와 요소 (라는 요소)의 테이블 주어이 질의는이 결과를 생성한다 :

    SELECT group_concat(name) FROM elements WHERE atomic_number < 10;

Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine

    질문에 적용, 당신은 당신이에서 필요한 답을 얻어야한다 :

    SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id;

    CREATE TEMP TABLE anonymous_table
(
    id      INTEGER NOT NULL,
    codes   CHAR(4) NOT NULL,
    PRIMARY KEY (id, codes)
);

INSERT INTO anonymous_table VALUES(63592, 'PELL');
INSERT INTO anonymous_table VALUES(58640, 'SUBL');
INSERT INTO anonymous_table VALUES(58640, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'PELL');
INSERT INTO anonymous_table VALUES(73571, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'SUBL');
INSERT INTO anonymous_table VALUES(73572, 'USBL');
INSERT INTO anonymous_table VALUES(73572, 'PELL');
INSERT INTO anonymous_table VALUES(73572, 'SUBL');

SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id
    ORDER BY id;

    그것의 출력은 :

    58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 PELL,SUBL,USBL

    삽입 순서가 결과에 영향 여부 데이터의 추가 세트는 테스트에 첨가 하였다; (즉 위하여 나는 확실히 바꿀 수있는 방법이 있는지 모르겠어요 - - 역 코드가 정렬 된 순서에) 그렇게 할 수 없습니다 나타납니다.

    노트:

  2. ==============================

    2.나는 Informix의 SQL에 대해 잘 모르겠지만, MSSQL이나 오라클에, 당신은이 작업을 수행 할 수

    나는 Informix의 SQL에 대해 잘 모르겠지만, MSSQL이나 오라클에, 당신은이 작업을 수행 할 수

    DECODE 또는 CASE 키워드, 그들을 함께 연결하여. 그러나,이 취성이다, 당신은 사전에 모든 잠재적 인 가치를 알고 필요합니다.

    나는 인포믹스를 지원하지 않기 때문에 당신이 물건 키워드 좋아하지 않는 이유가 있으리라 믿고있어?

    오라클은 다시 작동하지만 것 CONNECT BY 키워드, 인포믹스에서 지원되지 않을 수 있습니다 지원합니다.

    아마 가장 좋은 대답은 질의 후, 클라이언트 / 데이터 계층에서이 출력을 구축하는 것입니다. 이것은 쿼리에서 수행해야합니다 왜 특별한 이유가 있나요?

  3. ==============================

    3.인포믹스는 사용자 기능을 만들 수 있습니다 경우에도, 당신은 연결된 값으로 문자열을 반환하는 함수를 만들 수 있습니다.

    인포믹스는 사용자 기능을 만들 수 있습니다 경우에도, 당신은 연결된 값으로 문자열을 반환하는 함수를 만들 수 있습니다.

  4. ==============================

    4.조나단 Leffler의 예에 및 Informix 12.10FC8DE를 사용하여 연결된 값의 순서에 대한 RET의 의견을 구축, 나는 다음과 같은 사용자 집계 함께했다 :

    조나단 Leffler의 예에 및 Informix 12.10FC8DE를 사용하여 연결된 값의 순서에 대한 RET의 의견을 구축, 나는 다음과 같은 사용자 집계 함께했다 :

    CREATE FUNCTION mgc_init
(
    dummy VARCHAR(255)
)
RETURNING
    SET(LVARCHAR(2048) NOT NULL);

    RETURN SET{}::SET(LVARCHAR(2048) NOT NULL);

END FUNCTION;

CREATE FUNCTION mgc_iter
(
    p_result SET(LVARCHAR(2048) NOT NULL)
    , p_value VARCHAR(255)
)
RETURNING
    SET(LVARCHAR(2048) NOT NULL);

    IF p_value IS NOT NULL THEN
        INSERT INTO TABLE(p_result) VALUES (TRIM(p_value));
    END IF;

    RETURN p_result;

END FUNCTION;

CREATE FUNCTION mgc_comb
(
    p_partial1 SET(LVARCHAR(2048) NOT NULL)
    , p_partial2 SET(LVARCHAR(2048) NOT NULL)
)
RETURNING
    SET(LVARCHAR(2048) NOT NULL);

    INSERT INTO TABLE(p_partial1)
        SELECT vc1 FROM TABLE(p_partial2)(vc1);

    RETURN p_partial1;

END FUNCTION;

CREATE FUNCTION mgc_fini
(
    p_final SET(LVARCHAR(2048) NOT NULL)
)
RETURNING
    LVARCHAR;

    DEFINE l_str LVARCHAR(2048);
    DEFINE l_value LVARCHAR(2048);

    LET l_str = NULL;

    FOREACH SELECT vvalue1 INTO l_value FROM TABLE(p_final) AS vt1(vvalue1) ORDER BY vvalue1
        IF l_str IS NULL THEN
            LET l_str = l_value;
        ELSE
            LET l_str = l_str || ',' || l_value;
        END IF;
    END FOREACH;

    RETURN l_str;

END FUNCTION;
GRANT EXECUTE ON mgc_fini TO PUBLIC;

CREATE AGGREGATE m_group_concat
WITH
(
    INIT = mgc_init
    , ITER = mgc_iter
    , COMBINE = mgc_comb
    , FINAL = mgc_fini
);

    연결 한 문자열 값에는 중복을주지 않습니다 및 정렬됩니다.

    나는 다소 간단한 코드를 유지하려고하는, 인포믹스 컬렉션, 중복 값을 허용하지 않는, 즉 SET를 사용했다.

    방법은 최종 SET의 순서 붙이고 된 값에서 최종 빌드에서 (그리고 중복 제거) 및 중간 결과를 유지하기 위해 연결된 문자열을 SET의를 사용하는 것입니다.

    다음 SET 요소 LVARCHAR의 사용은 처음에 내가 VARCHAR를 사용 되었으나하면 메모리 소비가 매우 높은 있다는 사실 때문이다. 내부적으로 인포믹스는 CHAR에 VARCHAR 캐스팅 될 수있는 문서 힌트. 나는 변화를 만들어가 메모리 소비를 낮출 사실 않았다 (하지만 여전히 높다).

    그러나,이 총 메모리 소비는 약 2 배 느린 (000 약 300 행이 테이블을 사용하여) 제가 실시 된 시험에서 높은 조나단의 이상 크기의 2 개 주문 주위와.

    그래서 신중하게 사용합니다. 그것은 많은 메모리를 소모하며 광범위하게 (가 메모리 어딘가에 누수 될 수 있습니다) 테스트되지 않은 상태입니다.

    편집 1 :

    내 앞의 코드는 메모리 구조 어딘가에 누수해야합니다 (또는 내부적으로 인포믹스는 컬렉션 주위 테이블을 도출 유지하고, 그 많이 생성 할 수 있습니다).

    그래서, 아직 여기, 코드 C의 집계 기능을 갖는 피하려고하는 것은 훨씬 적은 메모리를 사용하고 조금 빠를 것이다 기능 내장 인포믹스 BSON을 사용하여 또 다른 대안입니다.

    CREATE FUNCTION m2gc_init
(
    dummy VARCHAR(255)
)
RETURNING
    BSON;

    RETURN '{"terms":[]}'::JSON::BSON;

END FUNCTION;

CREATE FUNCTION m2gc_iter
(
    p_result BSON
    , p_value VARCHAR(255)
)
RETURNING
    BSON;

    DEFINE l_add_array_element LVARCHAR(2048);

    IF p_value IS NOT NULL THEN
        LET l_add_array_element = '{ $addToSet: { terms: "' || TRIM(p_value) || '" } }';
        LET p_result = BSON_UPDATE(p_result, l_add_array_element);
    END IF;

    RETURN p_result;

END FUNCTION;

CREATE FUNCTION m2gc_comb
(
    p_partial1 BSON
    , p_partial2 BSON
)
RETURNING
    BSON;

    DEFINE l_array_elements LVARCHAR(2048);
    DEFINE l_an_element LVARCHAR(2048);
    DEFINE l_guard INTEGER;

    LET l_array_elements = NULL;
    LET l_guard = BSON_SIZE(p_partial2, 'terms.0');

    IF l_guard > 0 THEN
        WHILE l_guard > 0
            LET l_an_element = BSON_VALUE_LVARCHAR(p_partial2, 'terms.0');
            IF l_array_elements IS NULL THEN
                LET l_array_elements = '"' || l_an_element || '"';
            ELSE
                LET l_array_elements = l_array_elements || ', "' || l_an_element || '"';
            END IF;
            LET p_partial2 = BSON_UPDATE(p_partial2, '{ $pop: { terms: -1 } }');
            LET l_guard = BSON_SIZE(p_partial2, 'terms.0');
        END WHILE;
        LET l_array_elements = '{ $addToSet: { terms: { $each: [ ' || l_array_elements || ' ] } } }';        
        LET p_partial1 = BSON_UPDATE(p_partial1, l_array_elements);
    END IF;

    RETURN p_partial1;

END FUNCTION;


CREATE FUNCTION m2gc_fini
(
    p_final BSON
)
RETURNING
    LVARCHAR;

    DEFINE l_str_agg LVARCHAR(2048);
    DEFINE l_an_element LVARCHAR(2048);
    DEFINE l_iter_int INTEGER;
    DEFINE l_guard INTEGER;

    LET l_str_agg = NULL;
    LET l_guard = BSON_SIZE(p_final, 'terms.0');

    IF l_guard > 0 THEN
        LET p_final = BSON_UPDATE(p_final, '{ $push: { terms: { $each: [], $sort: 1 } } }');    
        LET l_iter_int = 0;
        WHILE l_guard > 0
            LET l_an_element = BSON_VALUE_LVARCHAR(p_final, 'terms.' || l_iter_int);
            IF l_str_agg IS NULL THEN
                LET l_str_agg = TRIM(l_an_element);
            ELSE
                LET l_str_agg = l_str_agg || ',' || TRIM(l_an_element);
            END IF;
            LET l_iter_int = l_iter_int + 1;
            LET l_guard = BSON_SIZE(p_final, 'terms.' || l_iter_int);
        END WHILE;
    END IF;
    RETURN l_str_agg;

END FUNCTION;

CREATE AGGREGATE m2_group_concat
WITH
(
    INIT = m2gc_init
    , ITER = m2gc_iter
    , COMBINE = m2gc_comb
    , FINAL = m2gc_fini
)
;

    집계 된 반환 값은 주문과 중복이 될 것입니다.

    다시, 이것은 제대로 테스트되지 않았습니다. 그것은 단지 POC이다.

    문제 중 하나는 입력 값을 살균되지 않는 것입니다. 기능을 조작 BSON 중 일부는 문자열이 아닌 문자가 이러한 매개 변수를 깰 수 탈출 연결하여 건설되고 매개 변수를받을 수 있습니다. 예를 들어,에 따옴표 문자열 값 : 'I "BrokeIt')는 (어설 장애 포함) 오류의 구색을 자극 할 수 있습니다.

    그리고 난 다른 문제가있는 특정입니다.

    그러나, 이러한 구현의 메모리 소비 조나단의 예에서 동일한 정도의 크기이며, 약 60 % 느린 (다시, 매우 기본적인 테스트를 수행 하였다).

  5. ==============================

    5.내가 스택 오버플로 다른 비슷한 질문에 당신이 대답을 지적하고 싶습니다. 당신은 MySQL을의 GROUP_CONCAT () 함수 같은 것을 찾고 있습니다.

    내가 스택 오버플로 다른 비슷한 질문에 당신이 대답을 지적하고 싶습니다. 당신은 MySQL을의 GROUP_CONCAT () 함수 같은 것을 찾고 있습니다.

    6. from https://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] NVARCHAR (최대)의 경우 난 단지 SQL에서 4000 문자는 무엇입니까?  (0) 2020.04.05
[SQL] MySQL의에 제한을 사용하여 여러 행을 업데이트?  (0) 2020.04.04
[SQL] 자연 SQL 서버에 참여  (0) 2020.04.04
[SQL] 왜 (어떻게) master..spt_values를 사용하여 열을 분할하려면?  (0) 2020.04.04
[SQL] 엑셀 날짜 일련 번호 정기적으로 날짜로 변환  (0) 2020.04.04
,

티스토리툴바