SQL_Aplied5.sql

# 5주차 수업
USE classicmodels;

select * from customers;

## where - 집계 전 필터
## group by 데이터 집계,요약-> 다양한 함수를 가지고 분석을 한다.
## having - 집계 후 필터

## 중복에 대한 유무 판단할 경우 자주 사용
select 
	customerNumber,
    count(*) 
from
	customers
group by
	customerNumber
having 
	count(*) > 1; # 중복인 경우
    

## 조건부 집계
## sum(case when ... then 1 else 0 end)
-- Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'classicmodels.customers.customerNumber'; this is incompatible with sql_mode=only_full_group_by

    
# 1. 대표적인 집계 예시 
# 일자별 매출을 집계하자!
select * from orders;    
select * from orderdetails;    

## th1. CTE로 만들기
with
	line_revenue as ( 	#  (1) 행 별 수익 
		select 
			od.orderNumber,
			(od.priceEach * od.quantityOrdered) as line_amount # 레코드 당 하나의 product
		from orderdetails as od
        ),
order_revenue as ( 		# (2) 일자 별 수익
	select
		o.orderDate as order_date,
       sum(line_amount) as revnue  
	from orders as o
    inner join line_revenue as lr
		on o.orderNumber = lr.orderNumber
	group by 
		o.orderDate)
        
select *
from order_revenue;
        
# 2. 아래와 같은 사고의 흐름(고민)을 가져볼 수 있다.

## 데이터가 어떤 식으로 적재되고 있나? 
## "데이터 숫자가 달라질 수 있다" -> 주문 취소를 할 수 있다.  ("매출 말고, 영업이익으로 본다면," -> 영업이익을 구성하는 매출원가 등의 것들이 반영이 된다.)
## 이 숫자의 정산은 10월 1일 정산하지 않는다. 10월 달을 -> 10월 말에 한다.  11월 초 / 마감
## 마감하는 날짜가 지나고 나서 주문이 취소되면, 계산된 매출과 수치가 달라져 버린다.(문제) -> 이걸 어떻게 해결해야 할까??

### th1. 데이터를 조회할 때 "전체 조회"할 필요가 있나? 
### 	지금 10월 기준 , 9월은 이미 정산이 끝났다. 과거 데이터는 크게 바꿀 이유가 없다. 
### 	그러나 지금인 10월은 바뀔 수 있어 ->  조회는 현 기준 월로만 조회하는 게 가장 이상적 

### th2. "데이터의 조회 기준"은 어디까지 해야 하는 것인가? 
### 	현 월의 10월이 지금 현재 월이면 -> 매일 일적재로 그룹바이해서 집계해야 한다. 

### th3. 매월 다시 재집계를 꼭 해야 합니까?
### 	10월 1일, 10월 2일 ... 10월 28일 오늘  -> // 내일 10월 29일  
### 	전제 다 집계할 필요 없이 -> 다음 날인 "하루만" 집계해서 적재하면 된다. 
### 	10월이 지난 후 마감 일자 매월 2일 -> 2일 이후에 다시 한 번 재집계 전월을 해야 한다.(정산이나, 세금 등등 으로 마감 후 수치가 달라 질 수 있기 때문이다.)
###		11월 2일 이후인 11월 3일에 -> 10월 데이터 전체를 재집계 쿼리 실행 (갱신)

## 결론 : 실무적으로 '날짜'도 고려해서, 집계를 어떻게 할지 스케줄링할 필요성도 있다.


# 3. 날짜 기준의 group by -> 집계 예시
## 최근 90일 동안의 '일별 매출의 일주일 누계'에 대한 데이터 
## 주차별로 데이터를 볼 수도 있고, 특정 날짜 기준에 따라서 볼 수 있다.

## Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(), interval 90 day)         group by    date(o.orderDate)     ) select   d,    ' at line 9

### th1. 90일 만큼 자른다.
WITH
  daily AS ( # 90일 동안의 일별 매출
    SELECT
      DATE(o.orderDate) AS d,
      SUM(od.quantityOrdered * od.priceEach) AS revenue # 같은 orderDate인 레코드 별 매출의 합
    FROM orders AS o
    INNER JOIN orderdetails AS od
      ON o.orderNumber = od.orderNumber
    WHERE o.status IN ('Shipped', 'Resolved') ## o.status 의 값이 'Shipped(배송완료)'이거나 'Resolved(해결됨)' 이면 통과
			## o.status = 'Shipped' OR o.status = 'Resolved')
      AND o.orderDate >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) ## 최근 90일 이내의 주문 레코드만 통과
			## CURDATE() : 오늘 날짜(today) 를 반환  ex.2025-10-07
            ## DATE_SUB(CURDATE(), INTERVAL 90 DAY) : '오늘 날짜에서 90일을 뺀 날짜' 계산 ex.2025-07-09
    GROUP BY
      DATE(o.orderDate)
  )
SELECT
  d,
  revenue,
  SUM(revenue) OVER ( # 7일의 합계
    ORDER BY d
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d
FROM daily
ORDER BY d ASC;

# 4. segment 기준으로 group by 예시
## rank 순위  -> 전체 매출에서 groupby 각 고객별로 매출 순위 
## 1~100 -> 1등~ 100등에 대한 분포 
## 파레토, 세그먼트 기준을 나눠서 case when vip , A , B 
## 이탈가능성이 있는 고객들 -> 세그먼트 group by 
select * from customers;

# 5. 필수과제
## 상위 20% 고객의 고개들에 대한 매출 순위, 고객의 넘버랑 고객의 revenue, 이 고객들의 세그먼트 (20% , 80% 기준으로 나눠서 각 유저별로 20%, 80%)