crosstab()을 사용하여 SQL에서 데이터를 동적으로 피벗하는 방법은 무엇입니까?

SQL 동적 피벗 테이블: crosstab() 함수를 사용한 동적 대안

문제 설명:

다음 형식의 데이터가 포함된 테이블이 있다고 가정합니다.

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

목표는 이 테이블을 보다 구조화된 형식으로 변환하는 것입니다. 여기서 각 행은 범주(bar)를 나타내고 해당 값(feh)은 val1, val2 등과 같은 열 이름이 있는 열로 정렬됩니다. 원하는 출력은 다음과 같습니다.

bar	val1	val2	val3
A	10	20	NULL
B	3	4	NULL
C	5	NULL	NULL
D	6	7	8

기존 솔루션에서는 CASE 문과 GROUP BY 절을 사용하여 데이터를 피벗하는 방법이 포함되었습니다. 그러나 범주가 많은 테이블의 경우 이 접근 방식은 매우 장황하고 다루기 어려울 수 있습니다.

대안:

tablefunc 모듈은 기존 솔루션보다 효율적이고 동적인 대안을 제공합니다. crosstab() 함수를 활용하면 더 간단하고 유지 관리하기 쉬운 쿼리로 동일한 결과를 얻을 수 있습니다.

해결책:

tablefunc 모듈이 설치되어 있는지 확인하고 데이터베이스당 한 번씩 다음 명령을 실행합니다.

CREATE EXTENSION tablefunc;

다음은 문제를 해결하는 기본 크로스탭 쿼리입니다.

SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  --更多列？

이 쿼리에서:

• crosstab() 함수는 두 개의 매개변수, 즉 데이터를 선택하고 각 행에 더미 범주 값을 할당하는 하위 쿼리와 열 이름 목록(이 경우 val1, val2 및 val3)을 허용합니다.
• 하위 쿼리는 원본 테이블에서 bar 및 feh 열을 선택하고, row_number() 창 함수를 사용하여 각 행에 더미 범주 값 1을 할당하고, bar 및 feh별로 데이터를 정렬하여 테이블의 올바른 순서를 보장합니다. 산출.
• crosstab() 함수의 결과는 후속 SELECT 문에서 참조하기 쉽도록 ct라는 CTE(공통 테이블 표현식)에 할당됩니다.

동적 교차 분석:

이 기본 접근 방식은 잘 작동하지만, 범주(열) 수를 미리 알 수 없는 경우에는 제대로 작동하지 않을 수 있습니다. 이 문제를 해결하기 위해 지정된 범주 열의 다양한 값을 기반으로 열 이름을 생성하는 동적 크로스탭 함수를 정의할 수 있습니다.

다음 쿼리는 동적 크로스탭 기능을 생성하고 사용하는 방법을 보여줍니다.

-- 创建动态crosstab函数
CREATE FUNCTION dynamic_crosstab(anyarray) RETURNS table AS $$
  DECLARE
    column_names text[];
    column_definitions text[];
    cte_name text;
  BEGIN
    -- 获取类别列的不同值
    column_names := ARRAY(SELECT DISTINCT unnest());
    -- 生成列定义
    column_definitions := ARRAY(SELECT STRING_AGG('"' || name || '" INT', ', ') FROM (SELECT unnest(column_names) AS name) AS subquery);
    cte_name := 'cte_' || md5(random()::text); -- 生成唯一的CTE名称
    EXECUTE FORMAT('CREATE TEMP TABLE %s (%s)', cte_name, column_definitions);
    -- 将数据插入CTE
    INSERT INTO %s SELECT * FROM crosstab();
    -- 返回CTE
    RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s', cte_name);
  END;
$$ LANGUAGE plpgsql;

-- 使用动态crosstab函数
SELECT * FROM dynamic_crosstab(ARRAY['bar']);

이 개정된 답변은 동적 크로스탭 기능의 생성 및 사용을 포함하여 SQL 동적 피벗 테이블 솔루션에 대해 더 자세하고 정확한 설명을 제공합니다. 코드는 더 나은 가독성을 위해 형식화되었습니다.

위 내용은 crosstab()을 사용하여 SQL에서 데이터를 동적으로 피벗하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!