sql문 예제, 윈도우 함수 실습

csv 파일 추가 

sales.csv

5.44MB

product.csv

0.72MB

 

 

테이블 형식

 

-- product 테이블 전체 조회
SELECT * 
from product;

-- product 테이블에서 주요 컬럼만 조회 (컬럼에 별명 부여)
select [Product ID] as 제품id,
       Category     as 대분류,
       [Sub-Category] as 소분류,
       [Product name] as 제품명
from product;

-- product 테이블에 테이블 별명(prd) 부여
select [Product ID] as 제품id, --컬럼에 대한 별명 
       Category  as 대분류,
       [Sub-Category] as 소분류,
       [Product name] as 제품명
from product prd; --테이블에 대한 별명 


-- sales 테이블에서 margin(매출-배송비) 계산 컬럼 생성
select rowid,
       sales, Profit, [Shipping cost], Quantity,
       sales-[Shipping cost] as margin --새로운 컬럼 생성 
from sales;


-- 뷰 생성: 아프리카 지역 판매 데이터만 추출
CREATE VIEW Vw_AfricaSales
As
select Region,sales,profit,[Shipping cost], Quantity
from sales 
where region='Africa'
order by Region;

-- 생성한 뷰 조회
select * from Vw_AfricaSales; --뷰를 만들어야 하는 이유 : 정보 보안 


-- sales에서 매출 상위 10개 출력
SELECT rowid,[Order id], sales
from sales 
order by sales desc
limit 10; --상위 10개

-- 11번째부터 10개 출력
SELECT rowid,[Order id], sales
from sales 
order by sales desc
limit 10,10; --11번째부터 10개 


-- 집계함수 (sales, profit, shipping cost 합계)
SELECT sum(sales), sum(profit), sum([shipping cost])
from sales ;

-- 합계 sales가 100000 이상인 결과만 반환
SELECT region, Segment,
       sum(sales), sum(profit), sum([shipping cost])
from sales 
group by Region,Segment
having sum(sales)>=100000; 

-- sales≥1000 조건 + profit 합계 ≥10000 조건 동시 적용
SELECT region, Segment,
       sum(sales), sum(profit), sum([shipping cost])
from sales 
where sales>=1000
group by Region,Segment
having sum(profit)>=10000; 


-- SQL 실행 순서 기억하기
-- SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT


/* 문제 예시:
   sales 테이블에서 market별, ship mode별로
   shipping cost의 합계와 평균값 집계
   조건: shipping cost ≥ 0.5
        평균 shipping cost ≥ 1
   최종: 평균 shipping cost 높은 순으로 10개 */
select market, [ship mode],
       sum([shipping cost]), avg([shipping cost])
from sales 
where [shipping cost]>=0.5
group by market, [ship mode]
having avg([shipping cost])>=1
order by avg([shipping cost]) desc 
limit 10;


-- 행 개수 세기
SELECT count(*) from sales;               -- 전체 행 수
select count(rowid) from sales;           -- rowid 기준 개수
select count([Postal code]) from sales;   -- Postal code NOT NULL 개수


-- Product id별 판매 건수 집계
select [Product id], count(*)
from sales 
group by [Product id]
order by count(*) desc;

-- 매출 최댓값, 최솟값, 평균값
select max(sales), min(sales), avg(sales)
from sales; 


-- profit 값에 따른 판정 (이익/손해/0)
select 
    profit,
    case 
        when profit > 0 then '이익'
        when profit < 0 then '손해'
        when profit = 0 then '0'
    end as 이익판정
from sales;


-- 현재 날짜/시간 확인
select strftime('%Y-%m-%d %H:%M:%S','now');
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;


-- 주문일자에서 연-월 추출
select orderdate,
       strftime('%Y-%m',orderdate) as yearmonth
from sales;

-- 연도별 매출 집계
select strftime('%Y',orderdate) as year,
       count(*), sum(sales)
from sales
group by strftime('%Y',orderdate);


-- 뷰 생성: 2023년 아프리카 지역 매출
create view africaview
As 
select region, strftime('%Y-%m',orderdate) as year,
       count(*), sum(sales)
from sales
where strftime('%Y',orderdate)='2023'
  and region='Africa'
group by region, year
order by region, year;

-- 뷰 조회
select * from africaview;


-- 가장 이익이 많이 남는 제품 id 10개 추출
select [Product id], sum(profit) as 이익합계
from sales
group by [Product id]
order by 이익합계 desc
limit 10;


-- sales와 product 테이블 조인 (전체 조회)
select *
from sales
join Product 
on sales."Product id" = Product."product id";

-- sales와 product 조인 (제품명 출력)
select sales.[Product id], product.[product name]
from sales
join Product 
on sales.[Product id] = Product.[product id];

-- 테이블에 별명 부여 후 조인
select s.[Product id], p.[product name]
from sales s
join product p
on s.[Product id] = p.[product id];

 

--

이제는 윈도우 함수를 배웁니다. . . 

 

일반 집계 함수 는 그룹 전체 결과만 반환.

윈도우 함수 는 집계 결과와 '개별 행' 정보를 동시에 보여주며: PARTITION BY 로 그룹핑 / ORDER BY 로 정렬 기준 지정 가능/  LAG(), LEAD() 등으로 전월/전년 비교 분석 가능

ex) 윈도우 함수 : SUM(Sales) OVER(PARTITION BY Country ORDER BY OrderDate) -> 기준에 대한 sum 값이 나온다

 

언제  많이 쓰이냐? 는 해보면 안다네요 ㄲㅈ

 

*윈도우 함수의 주요 종류

1. 순위 함수 - 동일 파티션 내에서 행의 순위를 결정하는 함수 / ROW_NUMBER(), RANK(), DENSE_RANK()

2. NTILE함수 - 데이터를 지정된 수의 그룹으로 균등하게 분할하는 함수 / NTILE(4) - 데이터를 4개 구간으로 나눔

3. 집계함수 - 윈도우 내에서의 합계, 평군, 개수 등을 계산하는 함수 / SUM() OVER(), AVG() OVER(), COUNT() OVER()

4. 시계열 함수- 이전 행이나 이후 행의 값을 참조할 수 있는 함수 / LAG(), LEAD() - 전월/전년 대비 분석에 활용

 

문제: 국가 별로 매출이 높은 순서대로 고객에게 순위를 매겨라 

SELECT Country, [Customer Name] , Sales, RANK() OVER(PARTITION BY Country ORDER BY Sales DESC) AS SalesRank FROM Sales;

 

문제: 각 고객(Customer ID)별로 주문일자 순으로 누적 매출(Sales)을 계산하라.

SELECT CustomerID, OrderDate, Sales, SUM(Sales) OVER( PARTITION BY CustomerID ORDER BY OrderDate ) AS RunningTotal FROM Sales;

 

 

< 코드 예제> 

SELECT strftime('%Y',OrderDate) AS Year
, strftime('%m',OrderDate) AS Month
, SUM(Sales) AS MonthlySales
, LAG(SUM(Sales)) OVER( ORDER BY strftime('%Y',OrderDate), strftime('%m',OrderDate) ) AS PrevMonthSales
, (SUM(Sales) - LAG(SUM(Sales)) 
OVER( ORDER BY strftime('%Y',OrderDate) , strftime('%m',OrderDate) )) 
/ NULLIF(LAG(SUM(Sales)) OVER( ORDER BY strftime('%Y',OrderDate), strftime('%m',OrderDate) ),0) * 100 AS MoM_GrowthRate 
FROM sales 
GROUP BY strftime('%Y',OrderDate), strftime('%m',OrderDate)
ORDER BY Year, Month;

 

코드설명 :

1️⃣ strftime('%Y', OrderDate) AS Year

• OrderDate에서 **연도(YYYY)**를 추출합니다.
• 예: 2025-09-10 → 2025
• 결과 컬럼 이름은 Year로 지정.

2️⃣ strftime('%m', OrderDate) AS Month

• OrderDate에서 **월(MM)**을 추출합니다.
• 예: 2025-09-10 → 09
• 결과 컬럼 이름은 Month로 지정.

3️⃣ SUM(Sales) AS MonthlySales

• 같은 연도와 월 기준으로 Sales 합계를 계산합니다.
• 즉, 월별 매출 합계를 MonthlySales 컬럼에 저장.

4️⃣ LAG(SUM(Sales)) OVER( ORDER BY strftime('%Y',OrderDate), strftime('%m',OrderDate) ) AS PrevMonthSales

• 전월 매출을 구하는 부분입니다.
• LAG() 함수는 현재 행 기준 이전 행의 값을 가져오는 윈도우 함수입니다.
• OVER(ORDER BY ...)를 통해 연도와 월 기준으로 순서를 정렬합니다.
• 결과 컬럼 이름은 PrevMonthSales (이전 달 매출).

5️⃣ (SUM(Sales) - LAG(SUM(Sales)) ... ) / NULLIF(...) * 100 AS MoM_GrowthRate

• 전월 대비 성장률(MoM, %) 계산
  • 공식: (이번 달 매출 - 이전 달 매출) ÷ 이전 달 매출 × 100
  • NULLIF(...,0) : 이전 달 매출이 0인 경우 나누기 오류를 방지하기 위해 사용.
• 결과 컬럼 이름: MoM_GrowthRate

6️⃣ FROM sales

• 매출 데이터가 있는 테이블: sales

7️⃣ GROUP BY strftime('%Y',OrderDate), strftime('%m',OrderDate)

• 연도+월 단위로 그룹핑하여 월별 합계를 계산

8️⃣ ORDER BY Year, Month

• 결과를 연도, 월 기준으로 정렬

전처리= 데이터 이해 + 제이터 정제 + 데이터 구조화

 

+

• INNER JOIN → 교집합만
• LEFT JOIN → 왼쪽 다 가져오고 오른쪽 매칭
• RIGHT JOIN → 오른쪽 다 가져오고 왼쪽 매칭
• FULL OUTER JOIN → 양쪽 다 가져옴

orderlist11.csv

2.82MB

orderlist12.csv

2.01MB

orderlist10.csv

2.47MB

 

갑자기 새로운 데이터.. 

 

-- 각 테이블의 데이터 개수 확인
select count(*) from orderlist10; -- 결과: 31394
select count(*) from orderlist11; -- 결과: 36030
select count(*) from orderlist12; -- 결과: 25680

-- orderlist10의 전체 데이터 조회
SELECT * from orderlist10; 

-- orderlist10에서 월별(년-월), 교정기관, 품목별 주문수량 합계 구하기
SELECT strftime('%Y-%m', 일자) AS 년월,  -- 일자에서 '년-월'만 추출
       교정기관,                        -- 교정기관 단위
       품목명,                          -- 품목명 단위
       SUM(주문수량) AS 총주문수        -- 주문수량 합계
FROM orderlist10
GROUP BY strftime('%Y-%m', 일자), 교정기관, 품목명;

-- 세 개의 주문 테이블을 합쳐서 하나로 조회
select *
from( 
  SELECT 일자, 교정기관, 품목명, 주문수량 from orderlist10
  UNION
  SELECT 일자, 교정기관, 품목명, 주문수량 from orderlist11
  UNION
  SELECT 일자, 교정기관, 품목명, 주문수량 from orderlist12
);

-- 더 깔끔하게 하기 위해 View 생성
CREATE view Vvorderlist
as
SELECT 일자, 교정기관, 품목명, 주문수량 from orderlist10
UNION
SELECT 일자, 교정기관, 품목명, 주문수량 from orderlist11
UNION
SELECT 일자, 교정기관, 품목명, 주문수량 from orderlist12
;

-- View를 활용해서 월별/기관별/품목별 총 주문수량 집계
SELECT strftime('%Y-%m', 일자) AS 년월,
       교정기관,
       품목명,
       SUM(주문수량) AS 총주문수
FROM Vvorderlist
GROUP BY strftime('%Y-%m', 일자), 교정기관, 품목명;