07장. 숫자 작업
🎨 7.1 평균 계산하기
- 계산하고자 하는 열에 AVG 함수를 적용한다.
- WHERE절을 제외하면 NULL이 아닌 모든 값에 대한 평균이 계산된다.
- 더 넓은 범위의 평균을 계산하기 위해서는 GROUP BY절을 사용해
각 그룹을 생성한 뒤, 계산한다.
🎨 7.2 열에서 최댓값, 최솟값 찾기
- 최소, 최댓값을 찾을 경우에는 각각 MIN, MAX 함수를 사용한다.
- 더 넓은 범위에서 검색할 때에는 GROUP BY절과 함께 MIN, MAX를 사용한다.
- 검색 시 전체 테이블이 그룹 또는 윈도일 경우, GROUP BY절을 사용하지 않고,
원하는 열에 MIN 또는 MAX함수를 적용한다. - MIN, MAX는 NULL을 무시하며 그룹의 열에 대한 NULL값 뿐만 아니라 NULL 그룹을 가질수도 있다.
🎨 7.3 열의 값 집계하기
- 전체 테이블이 그룹 또는 윈도인 합계를 계산할 때는 GROUP BY절을 사용하지 않고, 관심 있는 열에 SUM 함수를 적용한다.
- 여러 그룹 또는 데이터 윈도를 만들 때는 GROUP BY 절과 함꼐 SUM을 사용한다.
- SUM은 NULL을 무시하지만, NULL 그룹을 가질수도 있다.
🎨 7.4 테이블의 행 수 계산하기
- 전체 테이블이 그룹 또는 윈도인 행을 계산할 때 * 문자와 함께 COUNT 함수를 사용한다.
- COUNT 함수는 열 이름으로 인수를 전달하면 NULL을 무시한다.
- COUNT 함수는 * 문자나 상수를 전달하면 NULL을 포함한다.
- COUNT에 전달된 열의 모든 행이 null 이거나 테이블이 비어 있으면
COUNT는 0을 반환한다. - SELECT 절에 집계 함수 이외의 다른 함수가 지정되어 있지 않더라도, 테이블의 다른 열로 그룹화 할 수 있다.
🎨 7.5 열의 값 세어보기
- COUNT()에서와 같이 을 계산할 때, 실제로는 행을 세게 된다.
(실젯값과 관계없이 NULL 및 NULL이 아닌 값이 있는 행을 모두 포함하여 계산한다.) - 그러나 열을 지정하여 집계하면 해당 열에서 null이 아닌 갓의 수만 세게 된다.
🎨 7.6 누계 생성하기
- 윈도우 함수 SUM OVER을 사용하면 간단하게 누계를 생성할 수 있다.
🎨 7.7 누적곱 생성하기
- 윈도우 함수 SUM OVER를 사용하고 로기를 추가하여 곱셈을 시뮬레이션 할 수 있다.
SELECT empno, ename, sal,
exp(sum(ln(sal))over(order by sal, empno)) as running_prod
FROM emp
WHERE deptno = 10
EMPNO ENAME SAL RUNNING_PROD
----- ----- --- ------------
7934 MILLER 1300 1300
7782 CLARK 2450 3185000
7839 KING 5000 15925000000
- 각각의 자연 로그를 계산한다.
- 로그를 합산한다.
- 결과를 상수 e의 거듭제곱으로 올린다.
🎨 7.8 일련의 값 평활화하기
- 수식에 간단한 가중지츨 적용하는 방식으로, 최근의 값에 더 많은 가중치를
부여함으로써 이 작업을 더 타당하게 만든다. - 윈도우 함수 LAG를 사용하여 이동평균을 생성한다.
💎 가중이동평균?
- 가중이동평균은 시계열 데이터(특정 시간 간격에 나타나는 데이터)를 분석하는 간단한 방법 중 하나이다.
- 이동평균을 계산하는 한 가지 방법이다.
- 평균으로 파티션을 사용할 수 있다.
🎨 7.9 최빈값 계산하기
✔️ 방법 1
💎 DB2, MySQL, PostgreSQL, SQL Server
- 윈도우 함수 DENSE_RANK를 사용해 최반값을 추출한다.
💎 Oracle
- 집계 함수 MAX에 대한 KEEP 확장을 사용해 최빈값 SAL을 찾을 수 있다.
- 동점이 있는 경우, KEEP에서 가장 높은 값 하나만 유지한다.
✔️ 방법2
💎 DB2와 SQL Server
- 인라인 뷰 X는 각 SAL과 발생 횟수를 반환한다.
- 인라인 뷰 Y는 윈도우 함수 DENSE_RANK를 사용해 결과를 정렬한다.
💎 Oracle
- SAL과 발생 횟수를 반환한다.
- 집계 함수 MAX의 KEEP 확장을 사용해 최빈값을 찾는다.
keep(dense_rank first order by cnt desc
이렇게 하면 최빈값을 쉽게 찾을 수 있다.
🎨 7.10 중앙값 계산하기
💎 DB2와 PostgreSQL
- 윈도우 함수 PERCENTILE_CONT를 사용해 중앙값을 찾는다.
💎 SQL Server
- 윈도우 함수 PERCENTILE_CONT를 사용해 중앙값을 찾는다.
- SQL Server는 같은 원리로 작동하지만, OVER 절이 필요하다.
💎 MySQL
- MySQL에는 PERCENTILE_CONT 함수가 없으므로 다른 방법이 필요하다.
- CTE와 함께 CUME_DIST 함수를 사용해 PERCENTILE_CONT함수의 효과를 낸다.
💎 Oracle
- MEDIAN 또는 PERCENTILE_CONT 함수를 사용한다.
🎨 7.11 총계에서의 백분율 알아내기
💎 MySQL과 PostgreSQL
- DEPTNO 10의 급여 합계를 모든 급여의 합산한 값으로 나눈다.
SELECT (sum(
case when deptno = 10 then sal end))/sum(sal)
)*100 as pct
FROM emp💎 DB2, Oracle, SQL Server
- 윈도우 함수 SUM OVER와 함께 인라인 뷰를 사용해 DEPTNO 10의 급여 합계와
함께 전체 급여 합계를 찾는다. 그 후 외부쿼리에서 나누기와 곱하기를 수행한다.
SELECT distinct (d19/total)*100 as pct
FROM (
SELECT deptno,
sum(sal)over() total,
sum(sal)over(partition by deptno) d10
FROM emp
) x
WHERE deptno = 10🎨 7.12 null 허용 열 집계하기
- COALESCE 함수를 사용해 NULL을 0으로 변환해 집계에 포함한다.
- 단, 집계 함수 작업 시, NULL이 무시된다는 점을 유의하자.
🎨 7.13 최댓값과 최솟값을 배제한 평균 계산하기
💎 MySQL과 PostgreSQL
- 서브쿼리를 사용해 최댓값과 최솟값을 제외한다.
💎 DB2, Oracle, SQL Server
- 윈도우 함수 MAX OVER 및 MIN OVER와 함께 인라인 뷰를 사용해 높은 값과
낮은 값을 쉽게 제거할 수 있는 결과셋을 생성한다.
🎨 7.14 영숫자 문자열을 숫자로 변환하기
💎 DB2
- TRANSLATE 및 REPLACE 함수를 사용해 영숫자 문자열에서 숫자 문자를 추출한다.
💎 Oracle, SQL Server, PostgreSQL
- TRANSLATE 및 REPLACE 함수를 사용해 영숫자 문자열에서 숫자 문자를 추출한다.
💎 MySQL
- TRANSLATE 함수를 지원하지 않으므로 해법이 X
🎨 7.15 누계에서 값 변경하기
- 윈도우 함수 SUM OVER을 사용해 거래 유형을 결정하는 CASE 표현식과 함께 누계를 생성한다.
🎨 7.16 중위절대편차로 특잇값 찾기
- 중앙값을 찾는다.
- 추가 쿼리에서 사용할 수 있으려면 이 쿼리르 CTE에 넣어야한다.
💎 SQL Server
- SQL Server에는 중앙값을 쉽게 찾을 수 있는 PERCENTILE_CONT 함수가 있다.
- 두개의 다른 중앙값을 찾아 조작해야하므로 일련의 CTE가 필요하다.
💎 PostgreSQL과 DB2
- 전체 패턴은 동일하지만, PERCENTILE_CONT를 윈도우 함수가 아닌 집계 함수로 취급하므로 해당 구문이 다르다.
💎 Oracle
- 중앙값 함수의 존재로 인해 Oracle 사용자의 레시피가 간소화 되었다.
- 그러나 여전히 CTE를 사용해 편차의 스칼라값을 처리해야한다.
💎 MySQL
- MEDIAN, PERCENTILE_CONT 함수가 없으므로 중앙값은 CTE의 두 개의 서브쿼리이다.
🎨 7.17 밴포드의 법칙으로 이상 징후 찾기
- 밴포드의 법칙을 사용하려면 예상되는 자릿수 분포를 계산한 다음, 비교할 실제 분포를 계산해야한다.
- 밴포드의 법칙에 따라 예측된 빈도와 실제 데이터 빈도를 비교한다.
- 밴포드 법칙에 따라 예측된 첫 번째 숫자의 빈도, 실제 빈도 등 네 개의 열이 필요하다.
꾸준함의 가치를 믿는 개발자