성남시 설문조사 결과 조회하기
2018년 12월 1~2주차 업무내용을 정리하여 공유하고자 작성하였습니다.
목차
$ 설문 내용
설문 대상 그룹은 크게 세 가지 경우이며,
기획의도에 따라 몇 가지 조건들을 기준으로 나누어 각각 그룹 설정을 하였다.
-
- 기본
- 지역
- 학교급 (중학교/고등학교)
- 공립/사립
- 교직 경력
- 추가 요청
- 신규 기준 정의
- 수정구, 중원구 / 중학교 교사
- 수정구, 중원구 / 고등학교 교사
- 분당구 / 중학교 교사
- 분당구 / 고등학교 교사
- 신규 기준 정의
- 기본
-
- 기본
- 지역
- 학년 (중학교 - 1/2/3학년)
- 성별
- 기본
-
- 기본
- 지역
- 학교급
- 장래희망 인지 여부
- 추가 요청
- 신규 기준 정의
- 수정구,중원구 / 중학생 자녀를 둔 학부모
- 분당구 / 중학생 자녀를 둔 학부모
- 그 외 분류
- 신규 기준 정의
- 기본
$ 설문 문항 타입
- 주관식
- 주관식(기타)
- 객관식 1개 고르기
- 객관식 모두 고르기
- 객관식 n개 고르기
$ 쿼리 조회를 어떻게 할 것인가?
아이디어
임시로 활용할 테이블을 하나 생성해서
raw데이터를 정리하여 넣고, 그 테이블 기준으로 조회하자.
하지만, 쿼리의 조건이 제법 많은 편.
수많은 서브쿼리 작성은 가독성이 떨어져서 개인적으로 Common Table Expression을 많이 활용하는 편이다.
<참고 링크>
$ 데이터 정리
데이터를 정리해놓고 조회할 때 기준이 될 테이블을 생성한다.
/* 설문응답 기준 테이블 생성 */
USE betea;
DROP TABLE IF EXISTS `t_temp_member_answer`;
CREATE TABLE IF NOT EXISTS `t_temp_member_answer`
( rec_uf_g_idx INT NOT NULL -- 설문 대상 그룹 인덱스
, member_idx INT NOT NULL -- 설문자 고유 번호
, order_idx INT NOT NULL -- 문항 노출 순서
, rec_uf_q_idx INT NOT NULL -- 문항 인덱스
, question_text VARCHAR(1024) NOT NULL -- 문항 내용
, rec_uf_q_ex_idx INT NOT NULL -- 문항 보기 인덱스
, ex_idx INT NOT NULL -- 문항 보기 노출 순서
, question_type INT NOT NULL -- 문항 타입 정의
, member_answer VARCHAR(1024) NOT NULL -- 설문자 답변 내용
, PRIMARY KEY(rec_uf_g_idx, member_idx, rec_uf_q_idx, rec_uf_q_ex_idx)
);
설문응답 데이터를 정리해서 넣는 쿼리는 아래와 같다.
INSERT INTO `t_temp_member_answer` (rec_uf_g_idx, member_idx, order_idx, rec_uf_q_idx, question_text, rec_uf_q_ex_idx, ex_idx, question_type, member_answer)
(
WITH cte_short_answer_question AS /* 주관식 문항들 */
(
SELECT rec_uf_g_idx, rec_uf_q_idx
FROM
(
SELECT A.rec_uf_g_idx, A.rec_uf_q_idx, count(ex_idx) as cnt
FROM `t_recommend_userfactor_question` A
JOIN `t_recommend_userfactor_question_example` B
ON A.rec_uf_q_idx = B.rec_uf_q_idx
WHERE rec_uf_g_idx IN (27, 28, 29)
AND A.type = 1
GROUP BY A.rec_uf_g_idx, A.rec_uf_q_idx
) AS A
WHERE cnt = 1 -- 주관식은 객관식과 type값이 같지만 보기가 1개
)
, cte_member_data AS /* 멤버 데이터 */
(
SELECT rec_uf_g_idx, member_idx, rec_uf_q_idx, rec_uf_q_ex_idx, `text`
FROM `t_recommend_member_answer_detail`
WHERE rec_uf_g_idx IN (27, 28, 29)
AND rec_uf_idx IS NOT NULL
GROUP BY rec_uf_g_idx, member_idx, rec_uf_q_idx, rec_uf_q_ex_idx, `text`
-- 설문 데이터에 동일한 내용의 중복데이터가 있어서 그룹핑으로 제거
)
, cte_define_question_type AS /* 문항 타입 정의 */
(
SELECT A.rec_uf_g_idx
, A.order_idx
, A.rec_uf_q_idx
, A.subject
, B.rec_uf_q_ex_idx
, B.ex_idx
, B.`text`
, CASE
WHEN C.rec_uf_q_idx IS NOT NULL THEN
-1 -- 주관식
WHEN A.`type` = 1 THEN
1 -- 객관식 1개 고르기
WHEN A.`type` = 2 AND A.need_cnt = 0 THEN
0 -- 객관식 모두 고르기
ELSE
A.need_cnt -- 객관식 n개 고르기
END AS question_type
FROM `t_recommend_userfactor_question` A
JOIN `t_recommend_userfactor_question_example` B
ON A.rec_uf_q_idx = B.rec_uf_q_idx
LEFT JOIN cte_short_answer_question C
ON A.rec_uf_g_idx = C.rec_uf_g_idx
AND A.rec_uf_q_idx = C.rec_uf_q_idx
)
, cte_select_main_answer AS /* 주된 내용들 종합하여 조회 */
(
SELECT
A.rec_uf_g_idx
, B.member_idx
, A.order_idx
, A.rec_uf_q_idx
, A.subject AS question_text
, A.rec_uf_q_ex_idx
, A.ex_idx
, A.question_type
, CASE
WHEN A.question_type = -1 THEN
CONCAT('(주관식) ', IFNULL(B.`text`, ''))
ELSE
A.`text`
END AS member_answer
FROM cte_define_question_type A
JOIN cte_member_data B
ON A.rec_uf_g_idx = B.rec_uf_g_idx
AND A.rec_uf_q_idx = B.rec_uf_q_idx
AND (A.rec_uf_q_ex_idx = B.rec_uf_q_ex_idx OR
(B.rec_uf_q_ex_idx = 0 AND question_type = -1))
-- 주관식이거나 객관식이면서 0(이상한 값) 이 아닌 것들만..
)
, cte_etc_answer_question AS /* 객관식 기타 항목의 주관식을 가진 설문 데이터 */
(
SELECT A.rec_uf_g_idx
, A.rec_uf_q_idx
, A.order_idx
, A.subject AS question_text
, B.member_idx
, A.rec_uf_q_ex_idx
, ex_idx
, CONCAT('(기타) ', IFNULL(B.`text`, '')) AS member_answer
FROM
(
-- 객관식 기타 항목의 주관식을 가진 설문 문항
SELECT A.rec_uf_g_idx, A.rec_uf_q_idx, A.order_idx, A.subject, B.rec_uf_q_ex_idx, B.ex_idx
FROM `t_recommend_userfactor_question` A
JOIN `t_recommend_userfactor_question_example` B
ON A.rec_uf_q_idx = B.rec_uf_q_idx
WHERE A.rec_uf_g_idx IN (27,28,29) AND `text` = '기타'
) A
JOIN t_recommend_member_answer_detail B
ON A.rec_uf_q_idx = B.rec_uf_q_idx AND B.rec_uf_q_ex_idx = 0
WHERE B.rec_uf_idx IS NOT NULL
)
, cte_select_etc_answer AS /* 기타 주관식 응답 조회 */
(
SELECT rec_uf_g_idx
, member_idx
, order_idx
, rec_uf_q_idx
, question_text
, rec_uf_q_ex_idx
, ex_idx
, 1 AS question_type -- 객관식 1개 고르기
, member_answer
FROM cte_etc_answer_question
GROUP BY rec_uf_g_idx, member_idx, order_idx
, rec_uf_q_idx
, question_text
, rec_uf_q_ex_idx, ex_idx, member_answer
)
SELECT * FROM cte_select_main_answer
UNION ALL
SELECT * FROM cte_select_etc_answer
)
다음은 기준이 되는 조건들을 정의한 테이블에 데이터를 세팅하는 쿼리이다.
DROP TABLE IF EXISTS `t_temp_need_condition`;
CREATE TABLE IF NOT EXISTS `t_temp_need_condition`
(
rec_uf_g_idx INT NOT NULL
, rec_uf_q_idx INT NOT NULL
, PRIMARY KEY(rec_uf_g_idx, rec_uf_q_idx)
);
INSERT INTO `t_temp_need_condition` (rec_uf_g_idx, rec_uf_q_idx)
VALUES
(27, 240),(27, 241),(27, 244),(27, 246),
(28, 303),(28, 304),(28, 305),
(29, 366),(29, 367),(29, 368);
추가로, 작업 중 raw data에서 버그 현상을 발견해서 조치가 필요했다.
아래는 하위문항의 데이터를 체크해서 상위문항의 답변을 올바르게 바꿔주는 쿼리이다.
UPDATE `t_recommend_member_answer_detail` A
JOIN
(
WITH cte_has_parent_question_list AS
(
/* 설문자가 고른 선택의 부모 문항과 기타정보 */
SELECT B.rec_uf_g_idx
, B.member_idx
, A.rec_uf_q_idx
, rec_uf_q_ex_idx -- 고른 선택
, parent_q_idx -- 부모 문항번호
, need_example -- 필요한 선택
, subject
FROM
(
-- 부모 문항이 있는 설문 문항
SELECT rec_uf_q_idx, parent_q_idx, need_example, subject
FROM `t_recommend_userfactor_question`
WHERE rec_uf_g_idx IN (27,28,29)
AND parent_q_idx IS NOT NULL
) A
JOIN `t_recommend_member_answer_detail` B
ON A.rec_uf_q_idx = B.rec_uf_q_idx
WHERE rec_uf_g_idx IN (27,28,29)
AND B.rec_uf_idx IS NOT NULL
)
, cte_check_wrong_choice AS
(
SELECT A.*, B.rec_uf_q_ex_idx AS wrong_choice
FROM
(
SELECT rec_uf_g_idx, member_idx, parent_q_idx, need_example
FROM cte_has_parent_question_list
GROUP BY rec_uf_g_idx, member_idx, parent_q_idx, need_example
) A
JOIN t_recommend_member_answer_detail B
ON A.rec_uf_g_idx = B.rec_uf_g_idx
AND A.member_idx = B.member_idx
AND A.parent_q_idx = B.rec_uf_q_idx
WHERE A.need_example != B.rec_uf_q_ex_idx
)
SELECT * FROM cte_check_wrong_choice
) B
on A.rec_uf_g_idx = B.rec_uf_g_idx
and A.member_idx = B.member_idx
and A.rec_uf_q_idx = B.parent_q_idx
SET A.rec_uf_q_ex_idx = B.need_example;
현재까지 나온 쿼리들은 최초 1회만 실행한다.
(데이터 세팅의 목적)
$ 조회 쿼리
교사, 학생, 학부모의 분류 기준 조회쿼리 중 교사 기본 조건에 대한 쿼리 내용만 살펴보자.
WITH cte_condition_check AS /* 조건 개수에 해당하는 설문을 모두 한 설문자만 추려냄 */
(
SELECT A.rec_uf_g_idx, A.member_idx
FROM
(
SELECT A.rec_uf_g_idx, A.member_idx, COUNT(1) AS cnt
FROM `t_temp_member_answer` A
JOIN `t_temp_need_condition` B
ON A.rec_uf_g_idx = B.rec_uf_g_idx
AND A.rec_uf_q_idx = B.rec_uf_q_idx
WHERE A.rec_uf_g_idx = 27
GROUP BY rec_uf_g_idx, member_idx
) A
JOIN
(
SELECT rec_uf_g_idx, COUNT(1) AS cnt
FROM `t_temp_need_condition`
GROUP BY rec_uf_g_idx
) B
ON A.rec_uf_g_idx = B.rec_uf_g_idx
AND A.cnt = B.cnt
)
, cte_full_data AS /* 설문 분석 자격이 된 설문자의 모든 설문 내용 */
(
SELECT A.rec_uf_g_idx, A.member_idx, B.rec_uf_q_idx, B.question_text, B.order_idx, B.ex_idx
-- 여러 타입의 고등학교를 하나로 통합하여 그룹핑
, CASE WHEN B.rec_uf_q_ex_idx IN (871, 934, 964, 995) THEN 871 ELSE B.rec_uf_q_ex_idx END AS rec_uf_q_ex_idx_temp
, CASE WHEN B.rec_uf_q_ex_idx IN (871, 934, 964, 995) THEN '고등학교' ELSE B.member_answer END AS member_answer_temp
FROM cte_condition_check A
JOIN `t_temp_member_answer` B
ON A.rec_uf_g_idx = B.rec_uf_g_idx
AND A.member_idx = B.member_idx
GROUP BY rec_uf_g_idx, member_idx, rec_uf_q_idx, rec_uf_q_ex_idx_temp, member_answer_temp
)
, cte_ready_data AS /* 모든 문제에 대한 답변과 기준 문항의 답변을 묶어줌 */
(
SELECT A.*
, CASE WHEN B.rec_uf_q_ex_idx IN (871, 934, 964, 995) THEN 871 ELSE B.rec_uf_q_ex_idx END AS rec_uf_q_ex_idx
, CASE WHEN B.rec_uf_q_ex_idx IN (871, 934, 964, 995) THEN '고등학교' ELSE B.member_answer END AS member_answer
FROM cte_full_data A
JOIN
(
SELECT rec_uf_g_idx, member_idx, rec_uf_q_ex_idx, member_answer
FROM `t_temp_member_answer`
WHERE rec_uf_g_idx = 27
AND rec_uf_q_idx IN (240,241,244,246)
) B
ON A.rec_uf_g_idx = B.rec_uf_g_idx
AND A.member_idx = B.member_idx
)
, cte_pivot_data AS
(
SELECT
question_text
, member_answer_temp
, rec_uf_q_idx
, rec_uf_q_ex_idx_temp
, SUM(IF(rec_uf_q_ex_idx IN (select rec_uf_q_ex_idx from t_recommend_userfactor_question_example where rec_uf_q_idx = 240), 1, NULL)) AS `240`
, COUNT(IF(rec_uf_q_ex_idx=807, 1, NULL)) AS `807`
, COUNT(IF(rec_uf_q_ex_idx=870, 1, NULL)) AS `870`
, COUNT(IF(rec_uf_q_ex_idx=933, 1, NULL)) AS `933`
, SUM(IF(rec_uf_q_ex_idx IN (select rec_uf_q_ex_idx from t_recommend_userfactor_question_example where rec_uf_q_idx = 241), 1, NULL)) AS `241`
, COUNT(IF(rec_uf_q_ex_idx=808, 1, NULL)) AS `808`
, COUNT(IF(rec_uf_q_ex_idx=871, 1, NULL)) AS `871` -- (871, 934, 964, 995) 을 871 로 합침
, SUM(IF(rec_uf_q_ex_idx IN (select rec_uf_q_ex_idx from t_recommend_userfactor_question_example where rec_uf_q_idx = 244), 1, NULL)) AS `244`
, COUNT(IF(rec_uf_q_ex_idx=811, 1, NULL)) AS `811`
, COUNT(IF(rec_uf_q_ex_idx=874, 1, NULL)) AS `874`
, SUM(IF(rec_uf_q_ex_idx IN (select rec_uf_q_ex_idx from t_recommend_userfactor_question_example where rec_uf_q_idx = 246), 1, NULL)) AS `246`
, COUNT(IF(rec_uf_q_ex_idx=813, 1, NULL)) AS `813`
, COUNT(IF(rec_uf_q_ex_idx=876, 1, NULL)) AS `876`
, COUNT(IF(rec_uf_q_ex_idx=937, 1, NULL)) AS `937`
, COUNT(IF(rec_uf_q_ex_idx=966, 1, NULL)) AS `966`
, COUNT(IF(rec_uf_q_ex_idx=997, 1, NULL)) AS `997`
FROM cte_ready_data
GROUP BY rec_uf_q_idx
, question_text
, rec_uf_q_ex_idx_temp
, member_answer_temp
)
SELECT
DENSE_RANK() OVER(ORDER BY rec_uf_g_idx, order_idx) AS num
, IFNULL(B.question_text, A.subject) AS question_text
, IFNULL(B.member_answer_temp, A.text) AS member_answer_temp
, '-' AS '-'
, IFNULL(`240`,0) AS `1.지역`
, IFNULL(`807`,0) AS `수정구`
, IFNULL(`870`,0) AS `분당구`
, IFNULL(`933`,0) AS `중원구`
, '-' AS '-'
, IFNULL(`241`,0) AS `2. 학교급`
, IFNULL(`808`,0) AS `중학교`
, IFNULL(`871`,0) AS `고등학교`
, '-' AS '-'
, IFNULL(`244`,0) AS `3. 공/사립`
, IFNULL(`811`,0) AS `공립`
, IFNULL(`874`,0) AS `사립`
, '-' AS '-'
, IFNULL(`246`,0) AS `4. 교직경력`
, IFNULL(`813`,0) AS `5년 미만`
, IFNULL(`876`,0) AS `5~10년`
, IFNULL(`937`,0) AS `10~14년`
, IFNULL(`966`,0) AS `15~19년`
, IFNULL(`997`,0) AS `20년 이상`
FROM -- 모든 문항과 카운트 비교 (선택된 보기가 있으면 0값으로 표현하기 위해)
(
SELECT rec_uf_g_idx, rec_uf_q_idx, order_idx, subject, rec_uf_q_ex_idx, ex_idx, text
FROM
(
SELECT A.rec_uf_g_idx
, A.rec_uf_q_idx
, A.order_idx
, A.subject
, CASE WHEN B.rec_uf_q_ex_idx IN (871, 934, 964, 995) THEN 871 ELSE B.rec_uf_q_ex_idx END AS rec_uf_q_ex_idx
, CASE WHEN B.rec_uf_q_ex_idx IN (871, 934, 964, 995) THEN 2 ELSE B.ex_idx END AS ex_idx
, CASE WHEN B.rec_uf_q_ex_idx IN (871, 934, 964, 995) THEN '고등학교' ELSE B.text END AS text
FROM `t_recommend_userfactor_question` A
JOIN `t_recommend_userfactor_question_example` B
ON A.rec_uf_q_idx = B.rec_uf_q_idx
WHERE A.rec_uf_g_idx IN (27)
) A
GROUP BY rec_uf_g_idx, rec_uf_q_idx, order_idx, subject, rec_uf_q_ex_idx, ex_idx, text
) A
LEFT JOIN cte_pivot_data B
ON A.rec_uf_q_idx = B.rec_uf_q_idx
AND A.rec_uf_q_ex_idx = B.rec_uf_q_ex_idx_temp
ORDER BY num ASC, ex_idx ASC
$ 멤버별 모든 문항 답변 raw 데이터 구하기
모든 설문자의 문항별 응답이 잘 정리된 raw 데이터가 필요했다.
위에서 생성한 기준테이블을 pivot 해야 원하는 결과셋이 나온다고 판단,
MariaDB의 table pivot을 찾아봤지만 sql-server처럼 지원하는 기능은 없다고한다.
어떻게든 sql로 해결하고 싶어서, 웹 상에 돌아다니는 건 Stored Procedure 뿐인데 기능이 한정적이었다.
그래서 그거 주워다가 파라미터 몇 개 추가해서 커스텀해봤다.
- Grouping 결과를 묶어줄 특정 함수 입력 가능하도록 추가
- 컬럼 리스트 정렬 옵션 입력 가능하도록 추가
- Total 컬럼은 Grouping 집계함수에 따라 유무 판단
DROP PROCEDURE IF EXISTS `MyPivot`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `MyPivot`(
IN tbl_name TEXT,
IN group_by_func VARCHAR(99),
IN base_cols VARCHAR(99),
IN pivot_col VARCHAR(64),
IN pivot_col_order_by VARCHAR(99),
IN tally_col VARCHAR(64),
IN where_clause TEXT,
IN order_by VARCHAR(99)
)
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
/*
<parameter>
-- table name (or db.tbl)
-- using function after <group by> (SUM, COUNT, GROUP_CONCAT, ...)
-- column(s) on the left, separated by commas
-- name of column to put across the top
-- empty string or "BY order_idx ASC, ex_idx ASC, ..."; order by pivoting columns
-- name of column to SUM up
-- empty string or "WHERE ..."
-- empty string or "ORDER BY ..."; usually the base_cols
*/
-- Find the distinct values
-- Build the SUM()s
SET @pcob = IF(pivot_col_order_by='', '1', pivot_col_order_by);
SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ', ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY ', @pcob);
-- select @subq;
-- 숫자를 다룰 때만 SUM을 추가.
SET @sum_total = '\n';
SET @elseval = 'NULL';
IF group_by_func IN ('SUM', 'COUNT') THEN
SET @sum_total = CONCAT(',\n SUM(', tally_col, ') AS Total');
SET @elseval = '0';
END IF;
SET @cc0 = "CONCAT('&gbf(IF(&p = ', &v, ', &t, &e)) AS ', &v)";
SET @cc1 = REPLACE(@cc0, '&e', @elseval);
SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
SET @cc3 = REPLACE(@cc2, '&t', tally_col);
-- select @cc2, @cc3;
SET @qval = CONCAT("'\"', val, '\"'");
-- select @qval;
SET @cc4 = REPLACE(@cc3, '&v', @qval);
-- select @cc4;
SET @cc5 = REPLACE(@cc4, '&gbf', group_by_func);
-- select @cc5;
SET SESSION group_concat_max_len = 10000; -- just in case
SET @stmt = CONCAT(
'SELECT GROUP_CONCAT(', @cc5, ' SEPARATOR ",\n") INTO @sums',
' FROM ( ', @subq, ' ) AS top');
select @stmt;
PREPARE _sql FROM @stmt;
EXECUTE _sql; -- Intermediate step: build SQL for columns
DEALLOCATE PREPARE _sql;
-- Construct the query and perform it
SET @stmt2 = CONCAT(
'SELECT ',
base_cols, ',\n',
@sums,
@sum_total,
'\n FROM ', tbl_name, ' ',
where_clause,
' GROUP BY ', base_cols,
-- '\n WITH ROLLUP',
'\n', order_by
);
select @stmt2; -- The statement that generates the result
PREPARE _sql FROM @stmt2;
EXECUTE _sql; -- The resulting pivot table ouput
DEALLOCATE PREPARE _sql;
-- For debugging / tweaking, SELECT the various @variables after CALLing.
END//
DELIMITER ;
이 Pivot SP를 아래처럼 호출하면 원하는 결과셋이 나온다.
/* 설문자 별 전체 답변 내용 - (교사) */
CALL MyPivot(
'betea.t_temp_member_answer',
'GROUP_CONCAT',
'member_idx', -- group by
'question_text', -- column
'order_idx ASC', -- columns ordering option
'member_answer', -- grouping value
'WHERE rec_uf_g_idx = 27 and member_idx > 0',
'');
/* 설문자 별 전체 보기 선택 내용 - (교사) */
/* 클러스터링 시 벡터 데이터로 활용 */
CALL MyPivot(
'betea.t_temp_member_answer',
'COUNT',
'member_idx', -- group by
'rec_uf_q_ex_idx', -- column
'order_idx ASC, ex_idx ASC',
1, -- column value
'WHERE rec_uf_g_idx = 27 AND question_type > -1', -- 주관식 제외
'');
$ 시행착오에 대한 후기
같은 실수를 반복하지않기 위해서 적어두는 작업을 하면서 느낀 점 몇 가지.
- 여러 통계를 작성, 조회하기 전에 기준 데이터 테이블에 있는 데이터 검증을 최우선으로 하자.
- 요청받은 결과셋에 대한 확인을 철저하게 (추가로, 논리적 오류가 없진 않은지) 이해한 후 데이터를 추출하자.
- 기획에 참여할 기회가 있다면 검증단계에서의 편의성을 고려해보자.