데이터베이스 질문과 답변
by 소형철 [SQL Query] mysql pivot sp [2017.05.23 17:47:59]
안녕하세요.
오늘 하루 종일 고민 하다가 생각이 막혀 희망을 안고 질문을 올림니다.
DELIMITER //
DROP PROCEDURE IF EXISTS sp_stmt_goods_select //
CREATE PROCEDURE sp_stmt_goods_select(
IN p_client_cd VARCHAR(6),
IN p_agency_cd VARCHAR(6),
IN p_start_day VARCHAR(10),
IN p_end_day VARCHAR(10)
)
BEGIN
SET @@group_concat_max_len = 100000;
SET @SQL = NULL;
SET @p_agency_cd = p_agency_cd;
SET @p_client_cd = p_client_cd;
SET @p_start_day = p_start_day;
SET @p_end_day = p_end_day;
SET @dateF = '%Y-%c-%e';
SET @nCnt = nCnt+1;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(reg_dt = ''',
reg_dt,
''',cnt, NULL)) AS ',
CONCAT('A',DATE_FORMAT(reg_dt,'%Y%c%e'))
)
) INTO @SQL
FROM (
SELECT
DISTINCT(gds_cd),
brnch_cd,
gds_nm,
orig_cost,
sale_px,
reduce_px,
addl_tx_sbdv,
stmt_no,
qty,
reg_dt,
cnt
FROM (
SELECT
DISTINCT(A.gds_cd),
A.brnch_cd,
A.gds_nm,
A.orig_cost,
A.sale_px,
A.reduce_px,
A.addl_tx_sbdv,
B.stmt_no,
B.qty,
B.reg_dt,
COUNT(B.gds_cd) AS cnt
FROM tb_gds A, tb_order_io_det B
WHERE A.brnch_cd = B.agency_cd
AND A.gds_cd = B.gds_cd
AND A.brnch_cd = @p_agency_cd
AND B.brnch_cd = @p_client_cd
AND B.reg_dt between date_format(@p_start_day,@dateF) and date_format(@p_end_day,@dateF)
GROUP BY B.reg_dt,B.gds_cd
) C
) C;
SET @SQL = CONCAT(' SELECT
DISTINCT(gds_cd),
brnch_cd,
gds_nm,
orig_cost,
sale_px,
reduce_px,
addl_tx_sbdv,
stmt_no,
qty,
reg_dt, ',
@SQL,
' FROM (
SELECT
DISTINCT(A.gds_cd),
A.brnch_cd,
A.gds_nm,
A.orig_cost,
A.sale_px,
A.reduce_px,
A.addl_tx_sbdv,
B.stmt_no,
B.qty,
B.reg_dt,
COUNT(B.gds_cd) AS cnt
FROM tb_gds A, tb_order_io_det B
WHERE A.brnch_cd = B.agency_cd
AND A.gds_cd = B.gds_cd
AND A.brnch_cd = @p_agency_cd
AND B.brnch_cd = @p_client_cd
AND B.reg_dt between date_format(@p_start_day,@dateF) and date_format(@p_end_day,@dateF)
GROUP BY B.reg_dt,B.gds_cd
) C
GROUP BY gds_cd
');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;
위에 쿼리의 결과는 첨부된 그림 파일 처럼 결과가 나옵니다.
mysql 의 PREPARE stmt 로 만들어 프로시저로 감싸주었습니다.
그리고 외부 에서 call 하면 첨부된 그림과 같은 결과가 됩니다.
제가 하려는 요구 조건은 이러 합니다.
1. 특정 테이블의 상품을 가로로 일자별로 상품갯수을 보여주는 것입니다.
예을 들자면
|상품명 || A20170113 || A20170115 || A20170120 || N... || N...
| A상품 || 12 || 25 || 30 || N... || N...
| B상품 || 20 || 0 || 29 || N... || N...
쿼리 상으로는 해결이 된것 같지만, 화면상(Font단;UI단)에서 데이터을 가져와 표출 할때, 문제가
있습니다.
gds_cd ~ reg_dt 까지는 아래와 같은 형태로 가져 올수 있습니다.
Map
~로직 생략~
map.get("gds_cd");
map.get("reg_dt");
JSON
~로직생략~
data[i].gds_cd;
data[i].reg_dt;
A20170113,A20170115,A20170120 또한 가져 올수 있습니다.
Map
~로직 생략~
map.get("A20170113");
map.get("A20170115");
JSON
~로직생략~
data[i].A20170113;
data[i].A20170115;
하지만 날짜가 바뀌게 되면 A20170113,A20170115,A20170120 컬럼명이
A2017-02-13,A2017-05-15,A2017-06-20 <= '-' 은 이해을 돕기위해 넣었습니다.
으로 변경 되어 컬럼명을 가져 올수 없습니다.
로직 또한 아래와 같이 변경 이 되어져야 결과값을 가져 올수 있습니다.
Map
~로직 생략~
map.get("A20170213");
map.get("A20170515");
JSON
~로직생략~
data[i].A20170213;
data[i].A20170515;
제가 질문을 하고자 하는 것은
1. A20170113,A20170115,A20170120 처럼 고정 할수 있는 컬럼을 만든 방법
2. 제가 만든 프로시저 쿼리로는 제가 원하는 결과을 도출 할수 있는지가 궁금합니다.
아.. 지금 글쓰는 이순간 에도 어떤 방법으로 해야 되는지 떠오르지가 않아요.
음... 질문 내용을 정리 하면 mysql 에서 pivot 테이블을 사용 하는데 동적인 컬럼 을 만들어서
화면에 표출 하고 싶습니다.
쿼리 상에
SET @nCnt = nCnt+1;
이부분은
제가 A1, A2,A3... 이렇게 고정으로 항상 가져 올려고 시도 했던것인데
안됬습니다. 테스트 끝나고 지우질 않았습니다.
GROUP BY gds_cd 하면서 gds_cd 가 아닌 나머지 항목들을 함께 조회하는 것은 무의미합니다.
mySQL 에서는 에러 안나지만 다른 DB 에서는 허용되지 않는 구문입니다.
Distinct 사용도 무의미하구요.
a 와 조인을 하였지만 b 만 가지고 결과 충분히 가져올 수 있구요.
조건에 맞는 날짜만 Distinct 해서 가져온뒤 group_concat 하시면 됩니다.
SELECT GROUP_CONCAT(
CONCAT(' COUNT(CASE reg_dt WHEN ''', dt, ''' THEN 1 END) "', dt, '"')
ORDER BY dt)
INTO @SQL
FROM (SELECT DISTINCT DATE_FORMAT(reg_dt, '%Y%m%d') dt
FROM tb_order_io_det
WHERE agency_cd = @p_agency_cd
AND brnch_cd = @p_client_cd
AND reg_dt BETWEEN DATE_FORMAT(@p_start_day, @dateF) AND DATE_FORMAT(@p_end_day, @dateF)
) a
;
SET @SQL = CONCAT( 'SELECT gds_cd,'
, @SQL,
' FROM tb_order_io_det
WHERE agency_cd = @p_agency_cd
AND brnch_cd = @p_client_cd
AND reg_dt BETWEEN DATE_FORMAT(@p_start_day, @dateF) AND DATE_FORMAT(@p_end_day, @dateF)
GROUP BY gds_cd
' );
) 버튼을 클릭하여 작성 하시면 됩니다.