[SQL 쿡북] 7. 숫자작업(18)

7.1 평균 계산하기

 

7.2 열에서 최댓값, 최솟값 찾기

SELECT 절에서 집계 함수만 나열되어 있더라도 테이블의 다른 열로 그룹화할 수 있습니다.

7.3 열의 값 집계하기

부서별 사원급여

null인 group도 가질 수 있다.(테이블 3개 생성안되 책과 값이 다름)

 

7.4 테이블의 행 수 계산하기 

COUNT 함수는 열 이름을 인수로 전달하면 NULL을 무시하지만, * 문자나 상수를 전달하면 NULL을 포함한다는 점에 유의해야 합니다.(테이블 3개 생성안되 책과 값이 다름)

 

7.5 열의 값 세어보기

 

7.6 누계 생성하기

윈도우 함수 SUM OVER를 사용하면 간단하게 누계를 생성할 수 있습니다. ORDER BY절에는 SAL 열뿐만 아니라 누계에서 중복값을 방지하기 위해 EMPNO 열(기본 키)도 포함합니다.

 

7.7 누적곱 생성하기

1. 각각의 자연로그를 계산합니다.

2. 로그를 합산합니다.

3. 결과를 상수 e의 거듭제곱으로 몰립니다(EXP 함수 사용).

 

7.8 일련의 값 평활화하기

select date1, sales,lag(sales,1) over(order by date1) as salesLagOne,
lag(sales,2) over(order by date1) as salesLagTwo,
(sales
+ (lag(sales,1) over(order by date1))
+ lag(sales,2) over(order by date1))/3 as MovingAverage
from sales

select date1, sales,lag(sales,1) over(order by date1),
lag(sales,2) over(order by date1),
((3*sales)
+ (2*(lag(sales,1) over(order by date1)))
+ (lag(sales,2) over(order by date1)))/6 as SalesMA
from sales

판매 데이터의 변동성 때문에 근본적인 추세를 파악하기가 어렵습니다. 따라서 현재 상황을 제대로 파악하려면 며칠 동안의 데이터를 평활화해야 합니다. 

 

윈도우 함수 LAG를 사용하여 이동 평균을 생성합니다.

 

7.9 최빈값 계산하기
select sal
  from emp
 where deptno = 20
 order by sal
--------------------------------------------------------------------
<DB2, MySQL, PostgreSQL, SQL Server>
  select sal
    from (
  select sal,
         dense_rank()over( order by cnt desc) as rnk
    from (
  select sal, count(*) as cnt
    from emp
   where deptno = 20
  group by sal
        ) x
        ) y
  where rnk = 1

7.10 중앙값 계산하기
select sal
  from emp
 where deptno = 20
 order by sal


<MySQL>
with rank_tab (sal, rank_sal) as
(
select sal, cume_dist() over (order by sal)
            from emp
            where deptno=20
),

inter as
(
        select sal, rank_sal from rank_tab
        where rank_sal>=0.5
union
        select sal, rank_sal from rank_tab
        where rank_sal<=0.5
)

        select avg(sal) as MedianSal
               from inter
               
7.11 총계에서의 백분율 알아내기
<MySQL와 PostgreSQL>
 select (sum(
          case when deptno = 10 then sal end)/sum(sal)
         )*100 as pct
   from emp
   
 <MySQL와 PostgreSQL>
select sum(case when deptno = 10 then sal end) as d10,
       sum(sal)
from emp
--------------------------------------------------------------------
select (cast(
         sum(case when deptno = 10 then sal end)
            as decimal)/sum(sal)
        )*100 as pct
  from emp

7.12 Null 허용 열 집계하기
 select avg(coalesce(comm,0)) as avg_comm
   from emp
  where deptno=30
--------------------------------------------------------------------
select avg(comm)
  from emp
 where deptno=30
--------------------------------------------------------------------
select ename, comm
  from emp
 where deptno=30
order by comm desc

7.13 최댓값과 최솟값을 배제한 평균 계산하기
<MySQL과 PostgreSQL>
 select avg(sal)
   from emp
  where sal not in (
     (select min(sal) from emp),
     (select max(sal) from emp)
  )

<DB2, Oracle, SQL Server>
 select avg(sal)
   from (
 select sal, min(sal)over() min_sal, max(sal)over() max_sal
   from emp
        ) x
  where sal not in (min_sal,max_sal)
--------------------------------------------------------------------
<MySQL과 PostgreSQL>
select (sum(sal)-min(sal)-max(sal))/(count(*)-2)
  from emp
<DB2, Oracle, SQL Server>
select sal, min(sal)over() min_sal, max(sal)over() max_sal
  from emp

 

7.14 영숫자 문자열을 숫자로 변환하기

MySQL은  TRANSLATE 함수를 지원하지 않으므로 해법이 제공되지 않습니다. 

 

7.15 누계에서 값 변경하기

create view V (id,amt,trx)
as
select 1, 100, 'PR' from t1 union all
select 2, 100, 'PR' from t1 union all
select 3, 50,  'PY' from t1 union all
select 4, 100, 'PR' from t1 union all
select 5, 200, 'PY' from t1 union all
select 6, 50,  'PY' from t1
--------------------------------------------------------------------
  select * from V
--------------------------------------------------------------------
  select case when trx = 'PY'
              then 'PAYMENT'
              else 'PURCHASE'
          end trx_type,
          amt,
          sum(
           case when trx = 'PY'
              then -amt else amt
           end
        ) over (order by id,amt) as balance
 from V
--------------------------------------------------------------------
select case when trx = 'PY'
            then 'PAYMENT'
            else 'PURCHASE'
       end trx_type,
       case when trx = 'PY'
            then -amt else amt
       end as amt
  from V

7.16 중위절대편차로 특잇값 찾기

편차는 중앙값과 각 값 사이의 절대 차입니다. 중위절대편차는 이 값의 중앙값이므로 중앙값을 다시 계산해야 합니다.

7.17 벤포드의 법칙으로 이상 징후 찾기

 

https://github.com/devcys22/SQLCookbook/blob/main/ch7%EC%88%AB%EC%9E%90%EC%9E%91%EC%97%85.sql

GitHub - devcys22/SQLCookbook: SQL쿡북을 학습합니다.