MySQL 열 데이터를 행으로 피벗하는 방법: SQL 쿼리 접근 방식?

MySQL 열 데이터를 행으로 피벗

MySQL을 사용할 때 일반적인 작업은 데이터를 열 형식에서 행 형식으로 변환하는 것입니다. . 피버팅이라고 하는 이 프로세스는 더욱 사용자 친화적이고 쉽게 소화할 수 있는 방식으로 데이터를 표시하는 데 특히 유용할 수 있습니다.

다음 시나리오를 고려하세요.

세 개의 테이블이 있습니다.

• 질문 테이블(id, 질문)
• 결과 테이블(id, user_id, job_id)
• 답변 테이블(id, Answer, fk_question_id, fk_result_id)

질문 테이블에는 가능한 질문 목록이 포함되어 있습니다. 결과 테이블은 사용자를 작업 결과와 연결합니다. 답변 테이블에는 각 결과에 대한 질문에 대한 사용자 응답이 저장됩니다.

질문 답변을 각 결과 집합의 열로 표시하는 것이 목표입니다. 원하는 결과는 다음과 같습니다.

+-----------+---------+--------+-------------+----------+----------+
| result_id | user_id | job_id | Is it this? | Or this? | Or that? |
+-----------+---------+--------+-------------+----------+----------+
| 1         | 1       | 1      | Yes         | No       | Maybe    |
| 2         | 1       | 3      | Maybe       | No       | Maybe    |
| 3         | 2       | 3      | Yes         | Yes      | No       |
+-----------+---------+--------+-------------+----------+----------+

이 변환을 달성하려면 다음 MySQL 쿼리를 활용할 수 있습니다.

SELECT  a.ID,
        a.user_ID,
        a.job_id,
        MAX(CASE WHEN c.question = 'Is it this?' THEN b.answer END) 'Is it this?',
        MAX(CASE WHEN c.question = 'Or this?' THEN b.answer END) 'Or this?',
        MAX(CASE WHEN c.question = 'Or that? ' THEN b.answer END) 'Or that? '
FROM    Results a
        INNER JOIN Answers b
            ON a.id = b.fk_result_id
        INNER JOIN Question c
            ON b.fk_question_id = c.ID
GROUP   BY a.ID,
        a.user_ID,
        a.job_id

이 쿼리는 INNER JOIN 문과 MAX( ) 및 CASE 함수를 사용하여 각 질문 및 결과 조합에 대한 답변을 집계합니다. 데이터를 열 형식에서 행 형식으로 효과적으로 피벗하여 답변을 열로 시각화할 수 있습니다.

이 쿼리를 실행하려면 FROM 및 JOIN 절의 테이블 및 열 이름을 실제 테이블로 바꾸세요. 그리고 열 이름. 일단 실행되면 쿼리는 원하는 형식으로 피벗된 데이터를 반환합니다.

또는 질문 수가 많은 경우 쿼리의 동적 SQL 버전을 사용하여 알 수 없는 수의 질문을 처리할 수 있습니다. 이 동적 접근 방식은 피벗된 결과의 열 개수를 알 수 없거나 자주 변경되는 경우에 특히 유용합니다. 동적 SQL 버전은 다음과 같습니다.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN c.question = ''',
      question,
      ''' then b.answer end) AS ',
      CONCAT('`',question,'`')
    )
  ) INTO @sql
FROM Question;

SET @sql = CONCAT('SELECT  a.ID,
                            a.user_ID,
                            a.job_id, ', @sql, ' 
                    FROM    Results a
                            INNER JOIN Answers b
                                ON a.id = b.fk_result_id
                            INNER JOIN Question c
                                ON b.fk_question_id = c.ID
                    GROUP   BY a.ID,
                            a.user_ID,
                            a.job_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

위 내용은 MySQL 열 데이터를 행으로 피벗하는 방법: SQL 쿼리 접근 방식?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!