PostgreSQL에서 사용자 이메일 기록 행을 열로 바꾸는 방법은 무엇입니까?

사용자 이메일 기록 재구성: PostgreSQL의 행에서 열로

각 사용자의 여러 이메일 주소를 저장하는 테이블을 상상해 보세요. 이 문서에서는 이 행 기반 데이터를 열 형식으로 효율적으로 변환하는 방법, 특히 최신 이메일 주소를 고유한 열로 정렬하는 방법을 보여줍니다. PostgreSQL의 tablefunc 모듈과 강력한 crosstab() 기능을 사용하여 이를 달성하겠습니다.

crosstab() 솔루션

crosstab() 모듈 내의 tablefunc 기능은 사용자별로 행을 열로 바꾸는 데 매우 적합합니다. 다음 쿼리는 해당 응용 프로그램을 보여줍니다.

<code class="language-sql">SELECT *
FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn <= 3
   $$,
   $$VALUES (1),(2),(3)$$
) AS ct (user_id INT, user_name TEXT, email_1 TEXT, email_2 TEXT, email_3 TEXT);</code>

쿼리 구성 요소 이해

• crosstab()의 첫 번째 인수는 하위 쿼리입니다. 이 하위 쿼리는 사용자 ID, 사용자 이름, 행 번호(rn) 및 이메일 주소를 검색합니다.
• row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST)은 각 사용자의 이메일 주소에 고유한 순위(rn)를 할당하며 생성 날짜순으로 정렬됩니다(가장 최근 순).
• WHERE 절(WHERE rn <= 3)은 결과를 사용자당 가장 최근 이메일 주소 3개로 제한합니다.
• crosstab()의 두 번째 인수($$VALUES (1),(2),(3)$$)는 새 열의 잠재적인 값(이메일 주소 순위를 나타냄)을 지정합니다.

잠재적 과제 해결

원본 테이블에는 각 이메일 주소에 대한 고유 키가 없기 때문에 대리 키를 생성하기 위해 row_number()를 사용해야 했습니다. 또한 사용자당 다양한 이메일 수로 인해 출력을 외부 SELECT 문의 3개 열로 제한하고 두 번째 crosstab() 매개변수

에서 가능한 키 값의 범위를 정의해야 했습니다.

이 접근 방식은 사용자별 최신 이메일 주소를 별도의 열로 효과적으로 변환하여 사용자 이메일 기록에 대한 더 깔끔하고 체계적인 보기를 제공합니다.

위 내용은 PostgreSQL에서 사용자 이메일 기록 행을 열로 바꾸는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!