카테고리별 수익: 카테고리별 수익을 계산하는 쿼리.

카테고리별 수익 계산은 각 상품 범주의 매출 성과를 이해하고, 효과적인 재고 관리 및 마케팅 전략을 수립하는 데 중요합니다. 다음은 카테고리별 수익을 계산하는 데 사용할 수 있는 SQL 쿼리 예제와 각 예제의 간단한 설명입니다:


예제 1: 전체 카테고리별 수익 계산
SELECT Products.Category, SUM(OrderDetails.Quantity * OrderDetails.Price) AS TotalRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.Category;
모든 카테고리의 총 수익을 계산합니다.

예제 2: 연도별 카테고리 수익
SELECT Products.Category, YEAR(Orders.OrderDate) AS Year, SUM(OrderDetails.Quantity * OrderDetails.Price) AS AnnualRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
GROUP BY Products.Category, YEAR(Orders.OrderDate);
각 카테고리의 연도별 총 수익을 계산합니다.

예제 3: 월별 카테고리 수익
SELECT Products.Category, YEAR(Orders.OrderDate) AS Year, MONTH(Orders.OrderDate) AS Month, SUM(OrderDetails.Quantity * OrderDetails.Price) AS MonthlyRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
GROUP BY Products.Category, YEAR(Orders.OrderDate), MONTH(Orders.OrderDate);
각 카테고리의 월별 총 수익을 계산합니다.

예제 4: 최고 수익 카테고리
SELECT TOP 1 Products.Category, SUM(OrderDetails.Quantity * OrderDetails.Price) AS TotalRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.Category
ORDER BY TotalRevenue DESC;
가장 높은 수익을 기록한 카테고리를 조회합니다.

예제 5: 할인을 고려한 카테고리별 수익
SELECT Products.Category, SUM(OrderDetails.Quantity * (OrderDetails.Price - OrderDetails.Discount)) AS TotalRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.Category;
할인을 고려하여 각 카테고리의 총 수익을 계산합니다.

예제 6: 특정 기간 카테고리별 수익
SELECT Products.Category, SUM(OrderDetails.Quantity * OrderDetails.Price) AS TotalRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.OrderDate BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY Products.Category;
지정된 기간 동안의 각 카테고리별 총 수익을 계산합니다.

예제 7: 상품별 수익률
SELECT ProductID, Category, (Price - Cost) AS ProfitPerUnit
FROM Products;
각 상품의 단위당 수익률을 계산합니다.

예제 8: 분기별 카테고리별 수익
SELECT Products.Category, QUARTER(Orders.OrderDate) AS Quarter, SUM(OrderDetails.Quantity * OrderDetails.Price) AS QuarterlyRevenue
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
GROUP BY Products.Category, QUARTER(Orders.OrderDate);
각 분기별 카테고리별 총 수익을 계산합니다.

예제 9: 각 카테고리별 평균 판매 가격
SELECT Products.Category, AVG(OrderDetails.Price) AS AverageSalePrice
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.Category;
각 카테고리별 평균 판매 가격을 계산합니다.

예제 10: 특정 카테고리의 시간대별 판매 추세
SELECT HOUR(Orders.OrderTime) AS Hour, SUM(OrderDetails.Quantity * OrderDetails.Price) AS Revenue
FROM OrderDetails
JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE Products.Category = 'Electronics'
GROUP BY HOUR(Orders.OrderTime);
특정 카테고리('Electronics')에서 시간대별 판매 추세를 분석합니다.

관련 전문용어 설명
수익(Revenue): 상품이나 서비스의 판매로부터 발생하는 총 금액입니다.
수익률(Profit Margin): 상품 판매 가격에서 제조 또는 구매 비용을 제외한 이익의 비율입니다.
카테고리(Category): 비슷한 특성을 가진 상품들을 분류하는 그룹입니다.