# Phase 3: 비즈니스 쿼리 개발 ## Query 1: 카테고리별 상품 조회 ```cypher // Electronics 카테고리의 모든 상품 (하위 카테고리 포함) MATCH (root:Category {name: 'Electronics'})-[:PARENT_OF*0..]->(sub:Category) MATCH (p:Product)-[:IN_CATEGORY]->(sub) RETURN sub.name AS category, collect(p.name) AS products, count(p) AS productCount ORDER BY productCount DESC ``` --- ## Query 2: 고객 주문 이력 ```cypher // 특정 고객의 모든 주문과 상품 MATCH (c:Customer {customerId: 'p3w2-CUST-001'})-[:PLACED]->(o:Order) MATCH (o)-[:CONTAINS]->(oi:OrderItem)-[:FOR_PRODUCT]->(p:Product) RETURN o.orderId AS order, o.orderDate AS date, o.status AS status, collect({product: p.name, quantity: oi.quantity, price: oi.unitPrice}) AS items, o.totalAmount AS total ORDER BY o.orderDate DESC ``` --- ## Query 3: 함께 구매한 상품 추천 ```cypher // "이 상품을 구매한 고객이 함께 구매한 상품" MATCH (target:Product {productId: 'p3w2-PROD-001'}) // target을 구매한 주문 찾기 MATCH (target)<-[:FOR_PRODUCT]-(:OrderItem)<-[:CONTAINS]-(o:Order) // 같은 주문에 포함된 다른 상품 찾기 MATCH (o)-[:CONTAINS]->(:OrderItem)-[:FOR_PRODUCT]->(other:Product) WHERE other <> target // 빈도 집계 WITH other, count(DISTINCT o) AS copurchaseCount ORDER BY copurchaseCount DESC LIMIT 5 RETURN other.name AS recommendedProduct, copurchaseCount AS purchasedTogether ``` --- ## Query 4: VIP 고객 분석 (RFM) ```cypher // RFM 분석: Recency, Frequency, Monetary MATCH (c:Customer) OPTIONAL MATCH (c)-[:PLACED]->(o:Order) WITH c, // Recency: 최근 주문 후 경과일 CASE WHEN max(o.orderDate) IS NULL THEN 999 ELSE duration.inDays(max(o.orderDate), date()).days END AS recency, // Frequency: 총 주문 수 count(o) AS frequency, // Monetary: 총 구매 금액 coalesce(sum(o.totalAmount), 0) AS monetary // RFM 점수 (1-5점) WITH c, recency, frequency, monetary, CASE WHEN recency <= 30 THEN 5 WHEN recency <= 60 THEN 4 WHEN recency <= 90 THEN 3 WHEN recency <= 180 THEN 2 ELSE 1 END AS R, CASE WHEN frequency >= 10 THEN 5 WHEN frequency >= 5 THEN 4 WHEN frequency >= 3 THEN 3 WHEN frequency >= 1 THEN 2 ELSE 1 END AS F, CASE WHEN monetary >= 5000000 THEN 5 WHEN monetary >= 2000000 THEN 4 WHEN monetary >= 1000000 THEN 3 WHEN monetary >= 500000 THEN 2 ELSE 1 END AS M RETURN c.name AS customer, c.tier AS tier, recency AS daysSinceLastOrder, frequency AS orderCount, monetary AS totalSpent, (R + F + M) AS rfmScore, CASE WHEN (R + F + M) >= 12 THEN 'VIP' WHEN (R + F + M) >= 9 THEN 'Loyal' WHEN (R + F + M) >= 6 THEN 'Regular' ELSE 'At-Risk' END AS segment ORDER BY rfmScore DESC ``` --- ## Query 5: 월별 매출 트렌드 ```cypher // 월별 주문 수, 매출, 평균 주문 금액 MATCH (o:Order) WHERE o.orderDate >= date('2024-01-01') WITH o.orderDate.year AS year, o.orderDate.month AS month, o.totalAmount AS amount RETURN year, month, count(*) AS orderCount, sum(amount) AS totalRevenue, avg(amount) AS avgOrderValue ORDER BY year, month ``` --- ## Query 6: 상품별 리뷰 통계 ```cypher // 상품별 평균 평점, 리뷰 수 MATCH (p:Product) OPTIONAL MATCH (p)<-[:FOR_PRODUCT]-(r:Review) WITH p, count(r) AS reviewCount, avg(r.rating) AS avgRating RETURN p.name AS product, p.price AS price, reviewCount, coalesce(round(avgRating * 10) / 10, 0) AS avgRating ORDER BY reviewCount DESC, avgRating DESC LIMIT 10 ``` --- ## Query 7: 카테고리 매출 분석 ```cypher // 카테고리별 매출 MATCH (cat:Category {level: 1}) MATCH (cat)-[:PARENT_OF*0..]->(sub) MATCH (p:Product)-[:IN_CATEGORY]->(sub) MATCH (p)<-[:FOR_PRODUCT]-(oi:OrderItem)<-[:CONTAINS]-(o:Order) WITH cat.name AS category, sum(oi.quantity * oi.unitPrice) AS revenue, count(DISTINCT o) AS orderCount RETURN category, revenue, orderCount, revenue / orderCount AS avgOrderValue ORDER BY revenue DESC ```