Pandas와 SQL 5~10번 문제

5. 문제: 고객별 최대 구매 제품 조회

• 고객별로 가장 많은 금액을 지출한 제품과 해당 금액을 조회하세요. 단, 전체 평균 구매 금액보다 큰 금액을 지출한 경우에만 결과에 포함시킵니다.

PANDAS

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
purchases_data = {
    'PurchaseID': [1, 2, 3, 4, 5,6],
    'CustomerID': [101, 102, 101, 103, 102,101],
    'ProductName': ['Laptop', 'Smartphone', 'Tablet', 'Laptop', 'Earphones','Earphones'],
    'Amount': [1200.00, 800.00, 600.00, 1200.00, 150.00, 800],
    'PurchaseDate': ['2024-01-01', '2024-01-03', '2024-01-05', '2024-01-07', '2024-01-09','2024-01-09']
}

customers_data = {
    'CustomerID': [101, 102, 103],
    'CustomerName': ['John Doe', 'Jane Smith', 'Emily Davis']
}

purchases_df = pd.DataFrame(purchases_data)
customers_df = pd.DataFrame(customers_data)​

 

#방법1 : 결합 -> 고객별 최고가 상품 구하기 -> 해당 상품이 평균보다 큰지 확인
# 결합
pc_df = pd.merge(purchases_df,customers_df, on = 'CustomerID')
# 고객별 최고가 상품 구하기
max = pc_df.groupby(['CustomerID','ProductName']).agg({'Amount':'max'}).reset_index()
# 평균 구하기
mean = pc_df['Amount'].mean()
# 최고가 상품이 평균보다 크고, 금액별 내림차순으로 정렬하여 중복ID 제거
result = max[max['Amount'] >mean].sort_values(by = 'Amount', ascending=False).drop_duplicates(subset=['CustomerID'])
# 고객이름 컬럼 추가
result_df = pd.merge(result,customers_df[['CustomerName','CustomerID']], on = 'CustomerID')
result_df 

# 방법2: 결합 -> 고객별 최고가 컬럼 추가
# 결합
pc_df = pd.merge(purchases_df,customers_df, on = 'CustomerID')
# 각 행에 고객별 최고가 금액 추가
pc_df['MaxAmount'] = pc_df.groupby(['CustomerID'])['Amount'].transform('max')
# 'CustomerID'로 그룹화한 후, 각 그룹 내에서 'Amount' 컬럼의 최댓값을 계산하고 transform 함수를 사용하면 최댓값을 그룹의 모든 행에 적용하여 반환
# 전체 금액의 평균값 구하기
mean = pc_df['Amount'].mean()
# 최고가 상품이평균보다 크고 내림차순으로 정렬하여 중복ID 제거
result_df2 = pc_df[pc_df['MaxAmount'] > mean].sort_values(by='Amount', ascending = False).drop_duplicates(subset=['CustomerID'])
result_df2

 

1. transform() =  Pandas에서 제공하는 함수 , 그룹별로 연산을 수행한 결과를 원본 데이터프레임과 동일한 크기로 반환

-> [ 'Amount'].transform('max')는 'CustomerID'로 그룹화한 후, 각 그룹 내에서 'Amount' 컬럼의 최댓값을 계산 후 transform 함수를 사용하면 최댓값을 그룹의 모든 행에 적용하여 반환

 

2.df.drop_duplicates(subset=['column1', 'column2'], keep='first', inplace=False)는 Pandas 데이터프레임에서 중복된 행을 제거하는 작업을 수행하는 매서드. 

• subset: 중복 여부를 확인할 열(들)을 지정합니다. 기본값은 None이며, 이 경우 모든 열의 값이 동일한 행을 중복으로 간주합니다.
• keep: 중복된 행 중 어떤 행을 유지할지를 지정합니다. 기본값은 'first'로, 첫 번째로 등장한 행을 유지합니다. 다른 옵션으로 'last'를 지정하면 마지막으로 등장한 행을 유지할 수 있습니다.
• inplace: 기본값은 False로, 중복된 행을 제거한 결과를 반환합니다. True로 지정하면 원본 데이터프레임을 직접 수정하고 반환하지 않습니다.

-> 여기서는 최고가를 제외한 중복ID제거하고 싶기 때문에 amout를 기준으로 내림차순 해서 첫번째 값을 유지하고 제거

 

SQL

#ORACLE

CREATE TABLE Purchases ( 
   PurchaseID INTEGER, 
   CustomerID INTEGER, 
  ProductName VARCHAR2(50), 
   Amount DECIMAL(10,2), 
   PurchaseDate DATE 
);

CREATE TABLE Customers (
    CustomerID NUMBER,
    CustomerName VARCHAR2(255)
);

INSERT INTO Purchases (PurchaseID, CustomerID, ProductName, Amount, PurchaseDate)
SELECT 1, 101, 'Laptop', 1200.00, TO_DATE('2024-01-01', 'YYYY-MM-DD') FROM dual UNION ALL
SELECT 2, 102, 'Smartphone', 800.00, TO_DATE('2024-01-03', 'YYYY-MM-DD') FROM dual UNION ALL
SELECT 3, 101, 'Tablet', 600.00, TO_DATE('2024-01-05', 'YYYY-MM-DD') FROM dual UNION ALL
SELECT 4, 103, 'Laptop', 1200.00, TO_DATE('2024-01-07', 'YYYY-MM-DD') FROM dual UNION ALL
SELECT 5, 102, 'Earphones', 150.00, TO_DATE('2024-01-09', 'YYYY-MM-DD') FROM dual;

INSERT INTO Customers (CustomerID, CustomerName)
SELECT 101, 'John Doe' FROM dual UNION ALL
SELECT 102, 'Jane Smith' FROM dual UNION ALL
SELECT 103, 'Emily Davis' FROM dual;​

 

SELECT c.CustomerName, p.ProductName, MAX(p.Amount) AS MaxAmount
FROM Purchases p
JOIN Customers c ON p.CustomerID = c.CustomerID
GROUP BY c.CustomerName,p.ProductName
having MAX(p.Amount) > (select avg(amount)
    					from Purchases)
-- 만약 여기서 고객당 여러개의 상품이 있다면 window rank 이용하면 될듯


--  정답 -> 그러나: SELECT절에 GROUP BY의 기준이 되는 컬럼이 없기 떄문에 오류(not a GROUP BY expression)
SELECT c.CustomerName, p.ProductName, MAX(p.Amount) AS MaxAmount
FROM Purchases p
JOIN Customers c ON p.CustomerID = c.CustomerID
GROUP BY p.CustomerID
HAVING MAX(p.Amount) > (SELECT AVG(Amount) FROM Purchases)
ORDER BY MaxAmount DESC;

 

---

7. 문제: 고객별 최대 구매액 제품 조회

• 고객별로 총 구매액이 가장 높은 제품의 이름과 해당 구매액을 조회하세요. 단, 구매액이 전체 주문의 평균 총 구매액보다 높아야 합니다.

PANDAS

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
orders_data = {
    'OrderID': [1, 2, 3, 4],
    'CustomerID': [101, 102, 101, 103],
    'ProductID': [1, 2, 3, 4],
    'OrderDate': ['2024-01-01', '2024-01-03', '2024-01-05', '2024-01-07'],
    'Quantity': [2, 1, 3, 1],
    'TotalPrice': [1999.98, 899.99, 89.97, 120.00]
}
products_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']
}

orders_df = pd.DataFrame(orders_data)
products_df = pd.DataFrame(products_data)​

 

# 방법1
# 결합
op_df = pd.merge(orders_df, products_df, how= 'inner', on='ProductID')
# 고객별 최고금액 구하기
op_df['max'] = op_df.groupby(['CustomerID'])['TotalPrice'].transform('max')
#평균가격 구하기
mean = op_df['TotalPrice'].mean()
# 최고가가 평균보다 큰가?
op_df = op_df[op_df['max'] > mean].sort_values(by = 'TotalPrice', ascending= False).drop_duplicates(subset = 'CustomerID')

# 방법2
# 데이터 결합
merged_df = pd.merge(orders_df, products_df, on='ProductID')
# 고객별 최대 구매액 계산
merged_df['MaxPurchase']= merged_df.groupby('CustomerID')['TotalPrice'].transform('max')
merged_df 
# 전체 평균 구매액보다 큰 데이터 필터링
avg_total_price = merged_df['TotalPrice'].mean()
result_df = merged_df[(merged_df['TotalPrice'] == merged_df['MaxPurchase']) & (merged_df['TotalPrice'] > avg_total_price)]
# 중복 제거 및 결과 출력
result_df = result_df.drop_duplicates(subset=['CustomerID', 'MaxPurchase'])
print(result_df[['CustomerID', 'ProductName', 'TotalPrice']])

SQL

 

-- orders 테이블 생성
CREATE TABLE orders (
    OrderID NUMBER,
    CustomerID NUMBER,
    ProductID NUMBER,
    OrderDate DATE,
    Quantity NUMBER,
    TotalPrice NUMBER
);

-- products 테이블 생성
CREATE TABLE products (
    ProductID NUMBER,
    ProductName VARCHAR2(50),
    Price NUMBER,
    Category VARCHAR2(50)
);

-- orders_data 삽입
INSERT INTO orders (OrderID, CustomerID, ProductID, OrderDate, Quantity, TotalPrice)
VALUES (1, 101, 1, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 2, 1999.98);

INSERT INTO orders (OrderID, CustomerID, ProductID, OrderDate, Quantity, TotalPrice)
VALUES (2, 102, 2, TO_DATE('2024-01-03', 'YYYY-MM-DD'), 1, 899.99);

INSERT INTO orders (OrderID, CustomerID, ProductID, OrderDate, Quantity, TotalPrice)
VALUES (3, 101, 3, TO_DATE('2024-01-05', 'YYYY-MM-DD'), 3, 89.97);

INSERT INTO orders (OrderID, CustomerID, ProductID, OrderDate, Quantity, TotalPrice)
VALUES (4, 103, 4, TO_DATE('2024-01-07', 'YYYY-MM-DD'), 1, 120.00);

-- products_data 삽입
INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (1, 'Apple iPhone 13', 999.99, 'Electronics');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (2, 'Samsung Galaxy S22', 899.99, 'Electronics');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (3, 'Logitech Mouse', 29.99, 'Accessories');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (4, 'Nike Running Shoes', 120.00, 'Apparel');​

 

 

방법1 : 고객별 평균총가격보다 큰 최고주문액을 구한다(임시테이블) -> 고객별 최고 주문액과 동일한 상품명,총가격, 고객아이디를 조회한다

with max as (select CUSTOMERID, max(totalprice) maxprice
from Orders o join Products p
on o.ProductID = p.ProductID
group by CUSTOMERID
having max(totalprice) > (select avg(totalprice)
    					from Orders)
    )
select o.CUSTOMERID,PRODUCTNAME,TOTALPRICE
from max m join  Orders o
on m.maxprice = o.TOTALPRICE
join Products p
on o.ProductID = p.ProductID

# 방법2 : ProductID를 기준으로 Products와 Orders를 조인하고 조딘된 테이블과 고객별 최고가를 그한 테이블을 고객아이디와 최고가를 기준으로 조인한다
# -> 조인된 테이블에서 평균총가격보다 큰 경우를 필터링하여 고객아이디, 상품명, 총가격을 조회한다

SELECT c.CustomerID, p.ProductName, o.TotalPrice
FROM Orders o INNER JOIN Products p ON o.ProductID = p.ProductID
INNER JOIN (SELECT CustomerID, MAX(TotalPrice) AS MaxPrice
            FROM Orders
            GROUP BY CustomerID) c 
    ON o.CustomerID = c.CustomerID AND o.TotalPrice = c.MaxPrice
WHERE o.TotalPrice > (SELECT AVG(TotalPrice) FROM Orders)
ORDER BY o.TotalPrice DESC;

 

 

---

8. 문제: 제품 및 재고 현황 조회 시스템

• 모든 제품의 이름, 가격, 카테고리, 그리고 재고 수량을 조회하세요. 제품이 **Inventories**에 없는 경우에도 조회 결과에 포함되어야 하며, 이 경우 재고 수량은 NULL로 표시됩니다.

PANDAS

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
products_df = pd.DataFrame({
    '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']
})

inventories_df = pd.DataFrame({
    'InventoryID': [1, 2, 3, 5],
    'ProductID': [1, 2, 3, 5],
    'StockQuantity': [50, 40, 100, 20]
})​

 

#결합
pi_df = pd.merge(products_df, inventories_df, on = 'ProductID', how = 'left')
pi_df[['ProductName','Price','Category','StockQuantity']]

SQL

CREATE TABLE products (
    ProductID NUMBER,
    ProductName VARCHAR2(100),
    Price NUMBER,
    Category VARCHAR2(100)
);
CREATE TABLE inventories (
    InventoryID NUMBER,
    ProductID NUMBER,
    StockQuantity NUMBER
);
INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (1, 'Apple iPhone 13', 999.99, 'Electronics');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (2, 'Samsung Galaxy S22', 899.99, 'Electronics');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (3, 'Logitech Mouse', 29.99, 'Accessories');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (4, 'Nike Running Shoes', 120.00, 'Apparel');

INSERT INTO inventories (InventoryID, ProductID, StockQuantity)
VALUES (1, 1, 50);

INSERT INTO inventories (InventoryID, ProductID, StockQuantity)
VALUES (2, 2, 40);

INSERT INTO inventories (InventoryID, ProductID, StockQuantity)
VALUES (3, 3, 100);

INSERT INTO inventories (InventoryID, ProductID, StockQuantity)
VALUES (5, 5, 20);

select ProductName, Price, Category
from Products p left join Inventories i
on p.ProductiD = i.ProductID

 

 

---

9. 문제: 판매 분석 보고서

SalesData 테이블을 사용하여 다음 정보를 조회하세요:

1. 총 판매 거래 수(COUNT)
2. 판매된 총 제품 수량(SUM)
3. 평균 판매 가격(AVG)
4. 가장 높은 판매 가격(MAX)
5. 가장 낮은 판매 가격(MIN)
6. 카테고리별 판매된 제품의 총 수량

PANDAS

import pandas as pd

# 예시 데이터를 DataFrame으로 생성
sales_data = pd.DataFrame({
    'SaleID': [1, 2, 3, 4, 5],
    'ProductID': [101, 102, 103, 104, 105],
    'SaleDate': ['2024-01-01', '2024-01-03', '2024-01-05', '2024-01-07', '2024-01-09'],
    'Quantity': [2, 1, 3, 1, 2],
    'SalePrice': [500.00, 150.00, 300.00, 1200.00, 600.00]
})

# 가정: Products DataFrame에 카테고리 정보가 있다고 가정
products_data = pd.DataFrame({
    'ProductID': [101, 102, 103, 104, 105],
    'Category': ['Electronics', 'Accessories', 'Electronics', 'Apparel', 'Electronics']
})​

 

totalorders = len(sales_data)
totalQ = sales_data['Quantity'].sum()
avgsale = sales_data['SalePrice'].mean()
highestprice = sales_data['SalePrice'].max()
lowestprice = sales_data['SalePrice'].min()
print(totalorders,totalQ , avgsale, highestprice,lowestprice)

#결합
sp_df = pd.merge(sales_data, products_data, how= 'inner', on = 'ProductID')
category_amount = sp_df.groupby(['Category'])['Quantity'].sum()
print(category_amount )

SQL

-- sales_data 테이블 생성
CREATE TABLE sales_data (
    SaleID NUMBER,
    ProductID NUMBER,
    SaleDate DATE,
    Quantity NUMBER,
    SalePrice NUMBER
);

-- products_data 테이블 생성
CREATE TABLE products_data (
    ProductID NUMBER,
    Category VARCHAR2(100)
);

INSERT INTO sales_data (SaleID, ProductID, SaleDate, Quantity, SalePrice)
VALUES (1, 101, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 2, 500.00);

INSERT INTO sales_data (SaleID, ProductID, SaleDate, Quantity, SalePrice)
VALUES (2, 102, TO_DATE('2024-01-03', 'YYYY-MM-DD'), 1, 150.00);

INSERT INTO sales_data (SaleID, ProductID, SaleDate, Quantity, SalePrice)
VALUES (3, 103, TO_DATE('2024-01-05', 'YYYY-MM-DD'), 3, 300.00);

INSERT INTO sales_data (SaleID, ProductID, SaleDate, Quantity, SalePrice)
VALUES (4, 104, TO_DATE('2024-01-07', 'YYYY-MM-DD'), 1, 1200.00);

INSERT INTO sales_data (SaleID, ProductID, SaleDate, Quantity, SalePrice)
VALUES (5, 105, TO_DATE('2024-01-09', 'YYYY-MM-DD'), 2, 600.00

INSERT INTO products_data (ProductID, Category)
VALUES (101, 'Electronics');

INSERT INTO products_data (ProductID, Category)
VALUES (102, 'Accessories');

INSERT INTO products_data (ProductID, Category)
VALUES (103, 'Electronics');

INSERT INTO products_data (ProductID, Category)
VALUES (104, 'Apparel');

INSERT INTO products_data (ProductID, Category)
VALUES (105, 'Electronics');​

 

-- 1. 총 판매 거래 수
-- 2. 판매된 총 제품 수량
-- 3. 평균  판매 가격
-- 4. 가장 높은 판매 가격
-- 5. 가장 낮은 판매 가격
select count(*) as totalorders,
    sum(Quantity) as totalQ,
    avg(SalePrice) as avgsale,
    max(SalePrice) as highestprice,
    min(SalePrice) as lowestprice
from sales_data

-- 카테고리별 판매된 제품의 총 수량
select Category, sum(Quantity) as total
from sales_data s join products_data p
on s.ProductID = p.ProductID
group by Category;

 

 

10. 문제 정의: 제품 데이터 분석

• 각 카테고리에서 가장 비싼 제품과 가장 저렴한 제품의 정보(제품 이름, 가격)와 이들 간의 가격 차이를 조회하세요.

PANDAS

import pandas as pd

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

products_df = pd.DataFrame(data)

 

# 카테고리별 최대값, 최저값 계산
extremes_df = porducts_df.groupby(['Category']).agg(MaxPrice = ('Price','max'), MinPrice = ('Price','min')).reset_index()

# 카테고리별 최대값, 최저값의 상품정보
max_price_info = pd.merge(extremes_df,porducts_df, left_on = ['Category','MaxPrice'], right_on = ['Category','Price'], how = 'inner')
min_price_info = pd.merge(extremes_df,porducts_df, left_on = ['Category','MinPrice'], right_on = ['Category','Price'], how = 'inner')

# 카테고리별 최대값, 최저값의 상품정보를 결합하기
price_diff = pd.merge(max_price_info[['Category','ProductName','MaxPrice']],max_price_info[['Category','ProductName','MinPrice']], on = 'Category',suffixes=('_Max', '_Min'))
# 가격의 차이를 구한 컬럼 추가
price_diff['diff'] = price_diff['MaxPrice'] - price_diff['MinPrice']

 

1. merge의 매개변수

1. right 및 left : 병합할 두 데이터프레임
2. how : 병합 방법- 'inner', 'outer', 'left', 'right'.
3. on : 병합할 기준이 되는 열(들)의 이름
4. left_on 및 right_on : 병합할 때 왼쪽 데이터프레임과 오른쪽 데이터프레임의 기준이 되는 열 이름
5. suffixes : 동일한 열 이름이 충돌할 경우 열 이름 뒤에 붙일 접미사

2. suffixes의 기능 :

예) suffixes=('_Max', '_Min')

결합하는 df의 동일한 컬럼 ProductName이 있기 때문에 순서에 따라 _X, _Y로 구분된다	먼저 나오는 것 컬럼명 뒤에 '_Max'를 붙이기, 뒤에 나오는 컬럼에는 '_Min' 붙이기

 

3. agg(MaxPrice = ('Price','max'), MinPrice = ('Price','min'))

각 집계 함수에 대해 별도의 별칭(alias)을 지정하는 방식

 

4. agg({'Price':'max', 'Price':'min'})

집계 함수와 집계할 열을 딕셔너리 형태로 지정하는 방식SQL

딕셔너리는 키가 중복되면 마지막 키-값 쌍을 사용하게 되므로, 실제로는 'Price':'min'만 적용

-- products 테이블 생성
CREATE TABLE products (
    ProductID NUMBER,
    ProductName VARCHAR2(100),
    Price NUMBER,
    Category VARCHAR2(100)
);

-- 데이터 삽입
INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (1, 'Apple iPhone 13', 999.99, 'Electronics');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (2, 'Samsung Galaxy S22', 899.99, 'Electronics');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (3, 'Logitech Mouse', 29.99, 'Accessories');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (4, 'Nike Running Shoes', 120.00, 'Apparel');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (5, 'Adidas Soccer Ball', 25.00, 'Apparel');

INSERT INTO products (ProductID, ProductName, Price, Category)
VALUES (6, 'Sony Headphones', 150.00, 'Electronics');

with diff as
    (
    select CATEGORY, max(PRICE) as highest, min(PRICE) as lowest, (max(PRICE)-min(PRICE)) as diff
	from Products
	group by CATEGORY
    )
select p.CATEGORY,p.PRODUCTNAME ,p.PRICE, d.DIFF
from diff d join Products p
on d.highest = p.PRICE and d.CATEGORY = p.CATEGORY
union
select p.CATEGORY,p.PRODUCTNAME ,p.PRICE, d.DIFF
from diff d join Products p
on d.lowest = p.PRICE and d.CATEGORY = p.CATEGORY