Pandas와 SQL 11~13번 문제

11. 문제 : 고급 제품 데이터 분석

• 문제: 각 카테고리에서 평균 가격보다 비싼 제품의 이름과 가격을 조회하세요.

 

PANDAS

import pandas as pd

# 예시 데이터 생성
data = {
    'ProductID': [1, 2, 3, 4],
    'ProductName': ['Apple iPhone 13', 'Samsung Galaxy S22', 'Logitech Mouse', 'Nike Running Shoes'],
    'Price': [999.99, 899.99, 29.99, 120.00],
    'Category': ['Electronics', 'Electronics', 'Accessories', 'Apparel']
}

products_df = pd.DataFrame(data)

# 카테고리별 평균값 구하기
mean = products_df.groupby(['Category'])['Price'].mean().reset_index()

# 카테고리별 상품가격과 평균가격 비교하기
df = pd.merge(products_df ,mean, on='Category', suffixes=('','_avg'))
df[df['Price']>df['Price_avg']]

SQL

CREATE TABLE Product (
    ProductID NUMBER,
    ProductName VARCHAR2(100),
    Price NUMBER,
    Category VARCHAR2(50)
);

INSERT INTO Product (ProductID, ProductName, Price, Category)
VALUES (1, 'Apple iPhone 13', 999.99, 'Electronics');
INSERT INTO Product (ProductID, ProductName, Price, Category)
VALUES (2, 'Samsung Galaxy S22', 899.99, 'Electronics');
INSERT INTO Product (ProductID, ProductName, Price, Category)
VALUES (3, 'Logitech Mouse', 29.99, 'Accessories');
INSERT INTO Product (ProductID, ProductName, Price, Category)
VALUES (4, 'Nike Running Shoes', 120.00, 'Apparel');

select a.Category, ProductName, a.price
from Product a
join (select category, avg(price) avgprice
    from product 
    group by category) b
on a.Category = b.Category
and a.price > b.avgprice

 

12.문제: 최근 13개월간 가장 많이 팔린 제품 조회

• 최근 13개월 동안 가장 많이 팔린 제품의 이름과 총 판매 수량을 조회합니다.

PANDAS

import pandas as pd
from datetime import datetime

# Sales 데이터 생성
sales_data = {
    'SaleID': [1, 2, 3, 4],
    'ProductID': [2, 1, 4, 3],
    'SaleDate': [datetime(2023, 3, 15), datetime(2023, 3, 17), datetime(2023, 3, 19), datetime(2023, 3, 21)],
    'Quantity': [3, 1, 2, 1],
    'PricePerUnit': [299.99, 999.99, 59.99, 15.99]
}

sales_df = pd.DataFrame(sales_data)

# Products 데이터 생성
products_data = {
    'ProductID': [1, 2, 3, 4],
    'ProductName': ['Product A', 'Product B', 'Product C', 'Product D'],
    'Price': [999.99, 899.99, 29.99, 120.00],
    'Category': ['Electronics', 'Electronics', 'Accessories', 'Apparel']
}

products_df = pd.DataFrame(products_data)​

 

#최근 13개월 구하기
_13months_ago = pd.Timestamp.now() - pd.DateOffset(months = 13)
_13months_ago
#최근 13개월 판매데이터
recent_sales_df = sales_df[sales_df['SaleDate']>_13months_ago]
# 판매데이터와 제품정보 결합
join = pd.merge(recent_sales_df , products_df,on='ProductID', how = 'inner')
# 상품별 판매량
top1 = join.groupby(['ProductName'])['Quantity'].sum().sort_values(ascending=False).reset_index()
top1.head(1)

 

1. pd.Timestamp.now()

현재시스템 기반의 타임스탬프를 생성

pd.Timestamp->날짜와 시간 정보 생성, 날짜연산, 시간대변환하는 클래스

 

2. pd.DateOffset()

날짜연산을 수행 클래스

 

1. years: 연을 나타내는 정수값
2. months: 월을 나타내는 정수값
3. weeks: 주를 나타내는 정수값
4. days: 일을 나타내는 정수값
5. hours: 시를 나타내는 정수값

 

SQL

 

CREATE TABLE Sales (
    SaleID NUMBER,
    ProductID NUMBER,
    SaleDate DATE,
    Quantity NUMBER,
    PricePerUnit NUMBER
);

INSERT INTO Sales (SaleID, ProductID, SaleDate, Quantity, PricePerUnit)
VALUES (1, 2, TO_DATE('2023-03-15', 'YYYY-MM-DD'), 3, 299.99);
INSERT INTO Sales (SaleID, ProductID, SaleDate, Quantity, PricePerUnit)
VALUES (2, 1, TO_DATE('2023-03-17', 'YYYY-MM-DD'), 1, 999.99);
INSERT INTO Sales (SaleID, ProductID, SaleDate, Quantity, PricePerUnit)
VALUES (3, 4, TO_DATE('2023-03-19', 'YYYY-MM-DD'), 2, 59.99);
INSERT INTO Sales (SaleID, ProductID, SaleDate, Quantity, PricePerUnit)
VALUES (4, 3, TO_DATE('2023-03-21', 'YYYY-MM-DD'), 1, 15.99);

CREATE TABLE Products (
    ProductID NUMBER,
    ProductName VARCHAR2(100),
    Price NUMBER,
    Category VARCHAR2(50)
);

INSERT INTO Products (ProductID, ProductName, Price, Category)
VALUES (1, 'Product A', 999.99, 'Electronics');
INSERT INTO Products (ProductID, ProductName, Price, Category)
VALUES (2, 'Product B', 899.99, 'Electronics');
INSERT INTO Products (ProductID, ProductName, Price, Category)
VALUES (3, 'Product C', 29.99, 'Accessories');
INSERT INTO Products (ProductID, ProductName, Price, Category)
VALUES (4, 'Product D', 120.00, 'Apparel');

-- 최근 13개월, 가장많이 팔린 제품
select *
from 
(select p.PRODUCTNAME, sum(Quantity) total_amount
from Sales s join Products p
    on s.PRODUCTID = p.PRODUCTID
where SaleDate >= (select add_months(sysdate, -13) 
	from dual)
group by p.PRODUCTNAME
order by total_amount desc)
where rownum =1

 

1. ADD_MONTH 

날짜에 월을 더하는 함수

ADD_MONTH(기준날짜, 더할 개월 수)

 

2. SYSDATE : 현재 날짜와 시간을 알 수 있음

3. LIMIT와 ROWNUM(oracle)의 차이

LIMIT : 마지막 ORDER BY까지 실행한 수 해당 결과에 원하는 행을 가져오는 것

ROWNUM : 쿼리가 다 실행되기 전, 원래의 데이터 정렬의 순서에 매겨진 번호

-> LIMIT처럼 사용하기 위해서는 ORDER BY를 모두 실행후 새로운 쿼리에 작성

 

13. 문제: 연간 매출 성장률 상위 부서 조회

• 전년 대비 올해의 매출 성장률이 가장 높은 부서의 이름과 해당 성장률을 조회합니다.

PANDAS

import pandas as pd

# 예시 데이터 생성
sales_data = {
    'SaleID': [1, 2, 3, 4],
    'DeptID': [1, 2, 1, 2],
    'SaleAmount': [50000, 60000, 70000, 80000],
    'SaleYear': [2022, 2022, 2023, 2023]
}

departments_data = {
    'DeptID': [1, 2],
    'DeptName': ['Marketing', 'Development']
}

sales_df = pd.DataFrame(sales_data)
departments_df = pd.DataFrame(departments_data)​

# 방법1.
# 부서별, 전년대비, 매출성장률이 가장높은 부서이름/성장률
# 전년대비 매출 성장률 = 올해매출/전년도매출*100

#연도별 부서의 매출 구하기
sale_2022 = sales_df[sales_df['SaleYear']==2022]
sale_2022_amount = sale_2022.groupby(['DeptID']).agg({'SaleAmount':'sum'})
sale_2023 = sales_df[sales_df['SaleYear']==2023]
sale_2023_amount = sale_2023.groupby(['DeptID']).agg({'SaleAmount':'sum'})

# 전년대비 매출성장률 구하기
sale_rate = ((sale_2023_amount-sale_2022_amount)/sale_2022_amount*100).reset_index()

# 부서이름을 추가하며 성장률별 높은거 1개 출력하기
sale_rate_dep = pd.merge(sale_rate,departments_df, on = 'DeptID', how ='inner').sort_values(by = 'SaleAmount',ascending=False )
sale_rate_dep.head(1)


# 방법2.- pivot
# 연간 매출 계산
yearly_sales_df = sales_df.groupby(['DeptID', 'SaleYear'])['SaleAmount'].sum().reset_index()

# 성장률 계산
growth_df = yearly_sales_df.pivot(index='DeptID', columns='SaleYear', values='SaleAmount')
growth_df['GrowthRate'] = ((growth_df[2023] - growth_df[2022]) / growth_df[2022]) * 100

# 최고 성장률 부서 조회
growth_df = growth_df.reset_index()
max_growth_dept = growth_df[growth_df['GrowthRate'] == growth_df['GrowthRate'].max()]

# 부서 이름 포함
result_df = pd.merge(max_growth_dept, departments_df, on='DeptID')[['DeptName', 'GrowthRate']]

print(result_df)

SQL

-- sales_df를 오라클 테이블로 만들기
CREATE TABLE sales2 (
    SaleID NUMBER,
    DeptID NUMBER,
    SaleAmount NUMBER,
    SaleYear NUMBER
);

-- departments_df를 오라클 테이블로 만들기
CREATE TABLE departments (
    DeptID NUMBER,
    DeptName VARCHAR2(50)
);
위의 SQL 문은 sales_df를 sales 테이블로, departments_df를 departments 테이블로 만들게 됩니다. 이제 데이터를 삽입할 수 있습니다. 데이터를 삽입하는 방법은 다음과 같습니다.

sql
-- sales 테이블에 데이터 삽입
INSERT INTO sales2 (SaleID, DeptID, SaleAmount, SaleYear) VALUES (1, 1, 50000, 2022);
INSERT INTO sales2 (SaleID, DeptID, SaleAmount, SaleYear) VALUES (2, 2, 60000, 2022);
INSERT INTO sales2 (SaleID, DeptID, SaleAmount, SaleYear) VALUES (3, 1, 70000, 2023);
INSERT INTO sales2 (SaleID, DeptID, SaleAmount, SaleYear) VALUES (4, 2, 80000, 2023);

-- departments 테이블에 데이터 삽입
INSERT INTO departments (DeptID, DeptName) VALUES (1, 'Marketing');
INSERT INTO departments (DeptID, DeptName) VALUES (2, 'Development');

-- 부서별 전년대비, 매출성장률이 가장높은 부서이름/성장률
-- 전년대비 매출 성장률 = 올해매출/전년도매출*100
with temp as(
    select a.DEPTID , (b.SALEAMOUNT-a.SALEAMOUNT)/a.SALEAMOUNT*100 as rate
from
(select DEPTID,SALEYEAR,sum(SALEAMOUNT) as SALEAMOUNT
from sales2 
group by DEPTID,SALEYEAR
having SALEYEAR = 2022)a
join
(select DEPTID,SALEYEAR,sum(SALEAMOUNT) as SALEAMOUNT
from sales2 
group by DEPTID,SALEYEAR
having SALEYEAR = 2023)b
on a.DEPTID = b.DEPTID
order by rate desc)
select DEPTNAME,RATE
from temp c join departments d
on c.DeptID = d.DeptID
where rownum =1;