코드몽키 찬스님의 모바일, 웹

이 포스팅은 

아래 책의 일부 내용을 그대로 옴겨 적었습니다.

SQL 코딩의 기술 Effective SQL

Best Way 1 모든 테이블에 기본키가 있는지 확인

• 모든 테이블에는 하나 또는 일련의 컬럼으로 구성된 기본키가 있어야한다.
• 키가 아닌 컬럼에 중복값이 들어갈까 봐 걱정이 된다면 해당 컬럼에 유일 인덱스를 정의해 무결성을 확보할 수 있다.
• 형태가 가능한 간단하고 값을 갱신할 필요가 없는 컬럼을 키로 사용한다.

Best Way 2. 중복으로 저장된 데이터 항목을 제거하자

• 데이터베이스 정규화의 목표는 중복 데이터를 제거해 데이터를 처리할 때 사용되는 자원을 최소화하는 것이다.
• 중복 데이터를 제거하면 비정상적인 삽입, 갱신, 삭제를 막을 수 있다.
• 중복 데이터를 제거하면 일관성 없는 데이터 발생을 최소화할 수 있다.

* 참고문헌

설계 관련

• <가장 쉬운 데이터베이스 설계책 : 적절한 데이터베이스 디자인을 위한 지침서 >  Database Design for MereMortalsz> 
• <Handbook of Relational Database Design >

Best Way 3. 반복 그룹을 제거하자 

• 데이터 베이스 정규화 목표는 데이터의 반복 그룹을 제거하고 스키마 변경을 최소화하는 것이다.
• 데이터의 반복 그룹을 제거하면 이덱싱을 사용해 데이터 중복을 방지할 수 있고 쿼리도 간소화할 수 있다.
• 데이터의 반복 그룹을 제거하면 테이블 설계가 더 유연해진다. 새로운 그룹을 추가할 때 테이블 설계를 바꿔서 새 컬럼을 추가하는 것이 아니라 단순히 또 다른 로우만 추가하면 되기 때문이다.

Best Way 4. 컬럼당 하나의 특성만 저장하자

• 올바른 테이블 설계는 개별 특성을 자체 컬럼에 할당한다. 한 컬럼에 여러 특성이 포함되어 있으면 검색이나 그루핑 작업이 가능하다고 해도 어렵기 때문이다.
• 일부 애플리케이션에서는 주소나 전화번호 같은 컬럼의 데이터 일부를 걸러 내려면 최소 소준의 데이터 조각으로 분할해야 한다.
• 보고서나 목록을 뽑으려고 특성들을 재결합 때는 SQL의 문자열 연결 기능을 사용한다.

Best Way 5. 왜 계산 데이터를 저장하면 좋지 않은지 이해하자

• 많은 시스템에서 테이블을 정의할 때 계산 컬럼을 정의할 수 있지만 성능을 고려해야한다. 특히 *비결정적 표현식이나 함수를 사용할 때는 더욱 그렇다.
• 트러기를 사용해 계산 컬럼을 일반 컬럼처럼 정의할 수 있지만 작성해야 할 코드가 복잡하다.
• 계산 컬럼은 데이터베이스 시스템에 추가적인 부하를 일으키므로 계산 컬럼으로 얻는 혜택이 부하를 일으키는 비용보다 클 때만 사용한다.
• 대부분의 경우 저장 공간이 증가하고 데이터 갱신이 느린 대신 일부 혜택을 보려고 계산 컬럼에 인덱스를 만들고 싶을 것이다.
• 인덱스 적용이 어려울때는 테이블에 계산결과를 저장해 놓는 방법 대신 뷰를 이용해 수행할 계산을 정의하는 방법을 종종 사용한다.

비결정적함수 ( Nondeterministic )

특정 값 집합이 입력되더라도 매번 다른 값을 반환할 수도 있다.

GETDATE() 함수와 같이 실핼할 때마다 매번 다른 값을 반환.

Best Way 6. 참조 무결성을 보호하려면 외래키를 정의하자

• 명시적으로 외래키를 만들면 부모 테이블에 없는 로우를 가리키는 자식 테이블 로우가 없음을 보장할 수 있으므로 관련된 테이빌 간에 데이터 무결성을 혹인하는 데 좋다.
• 이미 데이터가있는 테이블에 FOREIGN KEY 제약 조건을 추가할 때 이 제약 조건을 위반하는 데이터가 있다면 제약조건을 생성하는 작업은 실패할 것이다.
• 일부 시스템에서는 FOREIGN KEY 제약 조건을 정의하면 자동으로인덱스를 만들어 주므로 조인 성능이 향상될 수 있다. 다른 시스템은 FOREIGN KEY 제약 조건이 걸린 컬럼에서 수동으로 인덱스를 만들어야 한다. 일부시스템은 인덱스 없이도 옵티마이저가 해당 컬럼을 특별 취급해 더 나은 쿼리 실행 계획을 세우기도 한다.

Best Way 7. 테이블 간 관계를 명확히 하자.

• 컬럼이 비슷한 테이블을 서로 병합해 관계를 간소화하는 것이 정말 타당한지 면밀히 검토한다.
• 데이터 타입이 일치한다면 두 테이블에 있는 컬럼 간 조인을 생성할 수 있는데, 이 관계는 해당 컬럼이 같은 돔인에 있을때만 성립한다. 하지만 조인에 사용되는 두 컬럼의 데이터 타입은 동일한 것이 가장 좋다.
• 데이터 모델에서 실제로 정형 데이터를 다루고 있는지 확인한다. 다루는 데이터가 반정현이라면 필요한 대응ㅇ책을 마련한다.
• 일반적으로 데이터 모델의 목표를 며확히 식별하면, 주어진 설계의 가소화와 데이터 모델을 사용하는 애플리케이션의 설계에 기인한 복잡성이나 이상 동작을 정당화하는지 판단하는데 도움이 된다. 
  
  

Best Way 8. 제 3정규화로도 부족하다면 더 정규화하자 

• 대부분의 데이터 모델에는 이미 더 높은 정규화가 적용되어 있을 가능성이 높다. 따라서 더 높은 정규화 형식을 명확히 위반하는지 면밀히 관찰해야 한다. 특히 복합키를 사용하거나 여러 다대다 관계에 참여하는 테이블일 때는 더욱 그렇다.
• 한 엔티티에서 관계가 없는 두 속성으로 가능한 모든 조합을 해당 에니티에 열거해야 하는 특수한 경우에는 제 4정규화를 위반할 수 이다.
• 제5정규화는 후보키가 모든 조인 의존성을 함축하는지 확인하는 것이다. 즉, 개별 요소에 근거해 후보키에 유효한 값이 무엇인지 제약할 수 있어야 한다는 말이다. 이것은 키가 복합키일 때만 발생한다.
• 제 6정규화는 일반적으로 관계를 키가 아닌 속성 하나로만 죽이는 것이다. 따라서 테이블 개수가 급격히 늘어나지만 넒 허용 컬러을 정의할 필요가 없다 .
• 무손실 분할 테스트는 해당 테이블이 더 높은 정규화 형식을 위반하는지 감지하는 효과적인 도구가 될 수 있다.
  
  

Best Way 9. 데이터 웨어하우스에는 역정규화를 사용하자

• 중복으로 저장할 데이터와 그 이유를 정한다.
• 데이터를 일치된 상태로 유지할 계획을 세운다.
• 역정규화된 필드를 사용하도록 쿼리를 리팩토링한다. 

" GROUP BY표현식이 아닙니다. "

SELECT절에 그룹함수를 제외한 컬럼들은

GROUP BY 절과 일치해야함. 오라클 한정

예 ) 올바른 GROUP BY 표현식이 아닌 경우

SELECT KEY1, KEY2, COUNT(*)
FROM TABLE
GROUP BY KEY1

아래와 같이 SELECT, GROUP BY절에 컬럼들이 일치해야한다.

SELECT KEY1, KEY2, COUNT(*)
FROM TABLE
GROUP BY KEY1, KEY2

SELECT KEY1, COUNT(*)
FROM TABLE
GROUP BY KEY1

이 포스팅은

Oracle SQL 실전 오라클 SQL 가이드

책을 읽고 요약하여 정리하였습니다.

그룹 함수를 포함한 조건과 일반 조건과 계산하는 과정이 상당히 다름.

일반 조건 : 컬럼의 값을 단지 조건과 비교

그룹 함수 포함 조건 : GROUP BY 절을 사용 우무에 따라 결과 값이 달라짐, 그룹함수가 포함된 경우 동일한 시점에 처리할 수 없다.

이를 위해 HAVING 절을 제공.

HAVING 절은 조건 중에 그룹 함수가 포함된 것만을 모아서 기술하는 구문

그룹 함수와 HAVING 절

- 해석상 WHERE 절과 동일.

- 조건 내용에 그룹 함수를 포함하는 것만을 포함

- HAVING 절은 GROUP BY 절 뒤에 기술

부서별 급여 평균이 3천 달러 미만인 부서의 부서번호와 평균 급여

SELECT dno, ROUND(AVG(sal))
FROM emp
GROUP BY dno
HAVING AVG(sal) < 300

- HAVING 절에 그룹함수를 포함한 조건절을 기술하는 목적으로 사용

- 그룹핑된 컬럼의 조건을 기술하는 절

- 그룹핑된 컬럼에 대한 단순 조건은 HAVING 절에 기술 가능하나 SQL 문장의 의도 파악이 힘들 수 있으므로 권장하지 않음.

ORACLE SQL 문 작성, 실행 순서

1. FROM 절 : 조회 대상 테이블

FROM CAR_MFG

2. WHERE : 일반 조건 

예 ) WHERE CAR_TYPE IN ( "스포츠카", "SUV" ) 

-> CAR_TYPE이 스포츠카, SUV가 아닌 행

-> " 4 전기차 타이칸4S 2020:02-03 20:00:00 " 제거

3. GROUP BY: 컬럼 그룹화

예 ) GROUP BY CAR_TYPE

4. HAVING : 그룹화 조건 
예) HAVING COUNT(*) > 1

5. SELECT : 데이터 추출

예) SELCT CAR_TYPE, COUNT(*) AS COUNT

6. ORDER BY : 데이터 순서 정렬

이 포스팅은

Oracle SQL 실전 오라클 SQL 가이드

책을 읽고 요약하여 정리하였습니다.

GROUP BY

그룹 함수를 GROUP BY 절에 지정된 컬럼의 값이 같은 행에 대해서 통계 정보를 계산 

~ 별로 통계값을 계산하고자 하면 반드시 GROUP BY절에 ~별로에 해당하는 컬럼을 기술해야 함.

Example ) AVG함수를 job 값이 같은 행끼리만 계산

SELECT job, ROUND(AVG(sal)), ROUND(AVG(sal*12+nvl (comm,0))) 
FROM emp
GROUP BY job

카디널리티 

- 검색되는 각 컬럼의 행의 수를 의미하거나 컬럼에 저장된 값의 종류를 의미하기도 함

- 동시에 검색되는 각 컬럼과 항목들의 행의 개수를 의미

- 그룹 함수에서 카디널리티는 동시에 검색되는 각 컬럼과 항목들의 행의 개수를 의미 

- 하나의 SELECT 문장에서 각 검색 항목들은 반드시 카디널리티가 일치해야 함.

- 이를 위해 GROUP BY 절을 이용

GROUP BY와 정렬

- 오라클은 기본적으로 GROUP BY를 수행 할 때, 정렬을 수행하지 않고 해시 함수를 이용

- 그러므로 결과 값도 GROUP BY 절에 기술된 컬럼으로 정렬되서 검색 되지 않음.

- 대부분의 경우에 정렬보다는 해시의 수행 성능이 훨씬 빠르기 때문.

- 정렬된 검색결과를 얻고자 한다면 ORDER BY 절을 기술

오라클 GROUP BY 관련 오류 

https://ckstmsla.tistory.com/entry/ORA-00979-not-a-GROUP-BY-expression