서브 쿼리, 연산자, 인라인 뷰 (*요일/인구 분석)

서브쿼리와 연산자

 

단일 행 서브쿼리

SELECT *
FROM  employees A
WHERE A.salary = (
                  SELECT salary
                  FROM  employees
                  WHERE last_name = 'De Haan'
                  );

WHERE - = ()

(좌)서브쿼리 (우)메인쿼리

 

다중 행 서브쿼리

SELECT *
FROM employees A
WHERE A.salary IN (
                   SELECT MIN(salary) 최저급여
                   FROM   employees
                   GROUP BY department_id
                  )
ORDER BY A.salary DESC;

WHERE - IN ()

(좌)서브쿼리 (우)메인쿼리

 

다중 열 서브쿼리

SELECT *
FROM employees A그
WHERE A.salary IN
(
  SELECT job_id, MIN(salary) 그룹별급여
  FROM employees
  GROUP BY job_id
)
ORDER BY salary DESC;

WHERE (열1,열2) IN (열1,열2)

 

(좌)서브쿼리 (우)메인쿼리

 

인라인 뷰(INLINE VIEW)

-> FROM 절 서브쿼리

SELECT *
FROM employees A,
     ( SELECT  department_id
       FROM    departments
       WHERE   department_name = 'IT') B
WHERE A.department_id = B.department_id;

인라인 뷰 : 테이블로 취급

(좌)서브쿼리 (우)메인쿼리

 

--요일분석
SELECT  SUBSTR(reserv_date,1,6) AS BASE_DATE,  
           A.product_name       AS ITEM_NAME,
           SUM(DECODE(A.WEEK,'1',A.sales,0)) AS SUN,
           SUM(DECODE(A.WEEK,'2',A.sales,0)) AS MON,
           SUM(DECODE(A.WEEK,'3',A.sales,0)) AS TUE,
           SUM(DECODE(A.WEEK,'4',A.sales,0)) AS WEN,
           SUM(DECODE(A.WEEK,'5',A.sales,0)) AS THU,
           SUM(DECODE(A.WEEK,'6',A.sales,0)) AS FRI,
           SUM(DECODE(A.WEEK,'7',A.sales,0)) AS SAT   
FROM
      (
        SELECT A.reserv_date,
               C.product_name,
               TO_CHAR(TO_DATE(A.reserv_date, 'YYYYMMDD'),'d') AS WEEK,
               B.sales
        FROM reservation A, order_info B, item C
        WHERE A.reserv_no = B.reserv_no
        AND   B.item_id   = C.item_id
        AND   B.item_id = 'M0005'
        AND   A.reserv_date BETWEEN '20170101' AND '20171231'
      ) A
GROUP BY SUBSTR(reserv_date,1,6), A.product_name
ORDER BY SUBSTR(reserv_date,1,6);

TO_CHAR(TO_DATE(),’d’) : → 날짜 형태→ 숫자 형태 (일요일=1)

FROM : 인라인 조건 테이블 삽입

WHERE - AND : 조건절 이너조인

(좌)서브쿼리 (우)메인쿼리

- 일,화,토 요일에 매출이 많이 발생한다

- 12월(또는 연말)에 스테이크 매출 비중이 높다

 

--인구 통계 분석
SELECT
        COUNT(A.customer_id) AS CUST_CNT, 
        SUM(DECODE(A.sex_code,'M',1,0)) AS MALE, 
        SUM(DECODE(A.sex_code,'F',1,0)) AS FEMALE,
        ROUND(AVG(MONTHS_BETWEEN(TO_DATE('20171231','YYYYMMDD'),TO_DATE(A.birth,'YYYYMMDD'))/12),1) AS AVG_AGE,
        ROUND(AVG(MONTHS_BETWEEN(TO_DATE('20171231','YYYYMMDD'),A.first_reg_date)),1) AS AVG_DEAL_DATE             
 FROM      
        (
            SELECT DISTINCT A.customer_id, A.sex_code, A.birth, A.first_reg_date
            FROM  customer A, reservation B, order_info C
            WHERE A.customer_id = B.customer_id
            AND   B.reserv_no   = C.reserv_no
            AND   B.cancel = 'N'
            AND   C.item_id = 'M0005'
            AND    B.reserv_date BETWEEN '20170101' AND '20171231'
        ) A;

MONTH_BETWEEN(TO_DATE(’기준날짜’),TO_DATE(’생일’))/12) : 나이 구하기

DISTINCT : 반복되는 행 값 제거

WHERE - AND : 조건절 이너조인

 

(좌)스테이크 매출 (우)전체 매출

서브쿼리, TO_DATE (주석처리로 전체 매출과 스테이크 매출 비교)

- 평균 나이대는 기본적으로 ‘젊은 연령대’, 스테이크는 평균보다 약간 더 낮다

- 평균 거래기간은 스테이크가 평균보다 약간 더 길다

- 남자의 비율이 더 크다

 

 

 

 

(출처1: 핵심만 쉽게, 모두의 SQL 데이터 분석)