데이터베이스 질문과 답변
안녕하세요. 쿼리추출에 관련하여 조언을 구하고자합니다.
SELECT
AA.sqx AS sqx,
AA.id AS id,
AA.passwd,
AA.name,
AA.comtype,
AA.comnametype,
AA.hphone,
AA.tphone,
AA.email,
AA.level,
AA.visit,
AA.wdate,
AA.c_end,
BB.*,
(
SELECT
comkkk
FROM
history
WHERE
uid=AA.id ORDER BY comkkk DESC limit 1
) AS comkkk
FROM
member AA
INNER JOIN company BB
ON AA.id = BB.id
LEFT OUTER JOIN memo CC
ON AA.id = CC.uid
WHERE AA.gubun = 'pp'
AND AA.id != '' GROUP BY AA.id ORDER BY AA.sqx desc
위와같은 쿼리문이 있는데요.
해당쿼리문에서 매출년도와 매출액을 입력하고 검색을하면 해당조건에 만족하는 리스트가 출력이 되는건데요.
디비컬럼을 보니 매출년도와 매출액의 컬럼이 하나로 되어있고 구성은 2000|2001|2002^1000|2000|3000
저런형태로 되어있는데요.
만약 2000년도 선택, 매출액 1000라고 입력하고 검색했을때 해당값을 추출할수있을까요?
조언좀 부탁드립니다.
정규화가 안 되어있군요.. 저렇게 모델링해놓으면 검색도 불편하고 사용하기 까다로운데..
매출년도 3개 + 매출액 3개 항상 이런 구조면 그냥 구분자로 나눠서 하면 될 것 같아요.
그런데 이 경우에도 데이터가 많다면 index 사용이 불가해서 검색 성능은 좋지 않을 것이고요.
다른 시스템에서 배치같은걸로 넘어올때 저런경우가 간혹있어..
근데 그냥쓰면 안되고 다시 분해(split)시켜서 테이블에 넣어야하는데.
GROUP BY 사용법이 표준에 어긋나는 구문이네요.
그룹바이를 하면서 * 를 사용하고, 그룹바이 기준이 아닌 항목(sqx)으로 정렬하네요.
mysql 에서만 에러가 안나는 구문입니다. 에러가 안난다고 해서 올바른 구문이 아닙니다.
소스를 수정했습니다.
실질적인 소스는 수정한것과 같은데요.
추출방법이 없을까요?
그리고 jkson님께서 말씀하신 정규화가 안되어있다 라는것이 어떤의미이며 정규화 관련 sql에 관련되어서
참조할만곳이 어디있을런지요.
잘못된 부분에 대해서 지적을 해주시면 앞으로 프로그래밍에 많은도움이 될듯합니다.^^
조언바랍니다.
그룹바이 기준항목인 a.id 이외의 항목은 Select 절에서 집계함수 없이 단독 사용 불가합니다.
bb.* 도 마찬가지이구요.
집계함수 사용할 용도로 그룹바이 한게 아니라면? 그룹바이는 왜 했을까요?
그룹바이가 필요한가요?
각 테이블의 키는 무었인가요?
RDBMS에서 빠질 수 없는 개념이 정규화인데요.
한 컬럼에는 되도록 하나의 정보만 저장한다는 게 정규화룰 중에 하나예요.
아마도 기존 테이블에 부가 정보를 기록해야 하는데 컬럼을 무한정 늘이기는 힘들고
그렇다고 테이블로 따로 빼자니 개발이 힘들 것 같고.. 그래서 저렇게 한 컬럼에 여러 정보를
입력한 게 아닌가 생각됩니다.
그런데 저런 식으로 입력해놓으면 호야별리님이 지금 경험하고 있는 검색의 어려움 문제가 필연적으로
발생할 수 밖에 없지요. 검색을 하려면 LIKE 구문을 쓰거나 데이터를 가공해야 하는데
데이터를 가공하는 것 자체도 성능을 떨어뜨리는데 가공된 결과에서 데이터를 찾아야 하니
인덱스 활용도 안 됩니다.
되도록 해당 정보를 별도의 테이블로 떼어내시고 원 테이블과 조인하여 활용하시는 게 좋을 것 같아요.
그런데 이미 프로그램이 저 컬럼 기준으로 많이 개발되어있다면 그렇게 하기 어렵겠지요..
그런데 지금 바꾸지 않으면 앞으로 더더욱 힘들어질 겁니다.
jkson님 조언감사합니다.^^
마농님께서 말씀하신 GROUP BY를 왜썼냐고 물어보신것에 대해서는
단순한 생각으로 다른테이블의 데이터때문입니다.
한개의 아이디에 여러개의 데이터가 존재하는 테이블이 있기에 저렇게 사용을 했는데..
잘못된 부분인지요. ..
목적을 가지고 그룹바이 해야 합니다. 단순 중복 된다고 하면 안됩니다.
중복된걸 어떻게 풀어내야 할지를 고민해야 합니다.
그룹바이만 붙인다고 해결되는게 아닙니다.
답변감사합니다.
jkson님 말씀처럼 별도의 테이블로 구성해서 조인하는걸 고민해봐야겠네요...힘드네요.^^
해당 데이터가 유의미한 정보로 자주 사용될 가능성이 높다면 테이블 따로 분리하세요.
원테이블이
기본키 + 다른 컬럼들 + 해당 컬럼
이런 구조라면 해당 컬럼의 정보를 가지고
(기본키 + 년도로 기본키 생성) + 매출액으로 구성된 새로운 테이블을 만드시구요.
원테이블의 해당 컬럼은 지워주시면 될 것 같습니다.
이렇게 만들면 검색시에도
select *
from 원테이블 a
where exists(select 1
from 신규테이블 b
where b.기본키 = a.기본키
and b.년도 = '2000'
and b.매출액 = 1000)
이렇게 해주시면 간단하겠죠.
아마도. 원래 a,b 만 조인하던 쿼리엿는데..
c 가 추가되면서 중복이 발생한 듯 하네요.
c 는 전혀 사용되지도 않고 있구요.
c 가 말씀하신 검색조건을 적용해야 하는 테이블인가요?
c 만 가지고 검색하는 쿼리를 별도로 만든 뒤에
DISTINCT c.uid 하여 a 와 조인하세요.
마농님 답변감사합니다.^^
원래는 3개의 테이블로 구성이 되어있던 로직입니다.
member(a), company(b), history
위에서 history은 하나의 아이디의 여러데이터중 최근거 하나만 가져오는거구요.
최근에 검색조건에 메모내용을 검색하는 부분이 추가되어 memo(c) 테이블이 추가된겁니다.
근데 생각지도 못한 년도 / 매출액관련해서 검색이 또 추가되다보니 컬럼 데이터가 위와같이 구성이 되어있어서 멘붕이 오기직전이네요 ^^
c 테이블 때문에 중복이 발생되는 것 맞나요?
해당 검색이 어느 테이블의 어느 컬럼을 검색하는 건가요?
c 테이블의 컬럼이 아니라면? 사용하지도 않는 c 는 왜 조인하나요?
c테이블에 한개의 아이디가 여러개 존재합니다.
c테이블의 memo라는 컬럼을 이용합니다.^^
관심가져 주셔서감사합니다.^^
SELECT a.sqx
, a.id
, a.passwd
, a.name
, a.comtype
, a.comnametype
, a.hphone
, a.tphone
, a.email
, a.level
, a.visit
, a.wdate
, a.c_end
, b.*
, (SELECT MAX(comkkk) FROM history WHERE uid = a.id) comkkk
FROM member a
INNER JOIN company a
ON a.id = b.id
INNER JOIN
(SELECT DISTINCT uid
, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(memo, '^', 1), '|', lv), '|', -1) y
, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(memo, '^', -1), '|', lv), '|', -1) v
FROM memo
INNER JOIN
(SELECT 1 lv
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
) copy_t
ON lv <= (LENGTH(memo) - LENGTH(REPLACE(memo, '|', ''))) / 2 + 1
) c
ON a.id = c.uid
WHERE a.gubun = 'pp'
AND a.id != ''
AND c.y = '2000'
AND c.v = '1000'
ORDER BY a.sqx DESC
;
선배에게 조언을 구해 처리를 하였습니다.^^
조언을 주신 두분께 감사드립니다.
정말이지 공부할게 많네요..
그런데 답변채택시에 오류가나네요 ㅡㅡ
) 버튼을 클릭하여 작성 하시면 됩니다.