SQL 데이터 값 검증 방법
•
‘내가 추출한 값 = 비교할 값’ 이 맞는지 확인
•
내가 요약한 테이블에서 하나의 값(기준)을 잡아서 원천 테이블(단일 테이블 또는 여러 테이블로 간단한 join)로 비교하면 된다.
•
비교할 기준, 지표는 무엇인지 → 가장 대표적으로 검증하는 기준 중에 하나가 카운팅, sum 등의 기본적인 통계값들이다
•
더 나아가서는 로우단으로 풀어 헤치면서 더 들어가서 검증할 수도 있다. (ex. 고객 한명을 잡고 검증을 한다든지)
필수 과제1
1.
제품을 기준으로 중요하게 생각하는 지표를 5개 이상은 만들어서 쿼리로 추출
2.
내가 생각하는 마트의 쿼리와 검증 쿼리를 비교해서 수치 검증 완료된 것도 함께 진행
3.
검증 쿼리에 대한 설명까지도 각주로 달아둘 것
1-1. 테이블 구조 확인
select * from products limit 10; -- 제품정보
select * from productlines limit 10; -- 프로덕트 카테고리 정보
SQL
복사
productCode | productName | productLine | productScale | productVendor | productDescription | quantityInStock | buyPrice | MSRP |
S10_1678 | 1969 Harley Davidson Ultimate Chopper | Motorcycles | 1:10 | Min Lin Diecast | This replica features working kickstand, front suspension, gear-shift lever, footbrake lever, drive chain, wheels and steering. All parts are particularly delicate due to their precise scale and require special care and attention. | 7933 | 48.81 | 95.70 |
S10_1949 | 1952 Alpine Renault 1300 | Classic Cars | 1:10 | Classic Metal Creations | Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis. | 7305 | 98.58 | 214.30 |
S10_2016 | 1996 Moto Guzzi 1100i | Motorcycles | 1:10 | Highway 66 Mini Classics | Official Moto Guzzi logos and insignias, saddle bags located on side of motorcycle, detailed engine, working steering, working suspension, two leather seats, luggage rack, dual exhaust pipes, small saddle bag located on handle bars, two-tone paint with chrome accents, superior die-cast detail , rotating wheels , working kick stand, diecast metal with plastic parts and baked enamel finish. | 6625 | 68.99 | 118.94 |
S10_4698 | 2003 Harley-Davidson Eagle Drag Bike | Motorcycles | 1:10 | Red Start Diecast | Model features, official Harley Davidson logos and insignias, detachable rear wheelie bar, heavy diecast metal with resin parts, authentic multi-color tampo-printed graphics, separate engine drive belts, free-turning front fork, rotating tires and rear racing slick, certificate of authenticity, detailed engine, display stand
, precision diecast replica, baked enamel finish, 1:10 scale model, removable fender, seat and tank cover piece for displaying the superior detail of the v-twin engine | 5582 | 91.02 | 193.66 |
S10_4757 | 1972 Alfa Romeo GTA | Classic Cars | 1:10 | Motor City Art Classics | Features include: Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis. | 3252 | 85.68 | 136.00 |
S10_4962 | 1962 LanciaA Delta 16V | Classic Cars | 1:10 | Second Gear Diecast | Features include: Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis. | 6791 | 103.42 | 147.74 |
S12_1099 | 1968 Ford Mustang | Classic Cars | 1:12 | Autoart Studio Design | Hood, doors and trunk all open to reveal highly detailed interior features. Steering wheel actually turns the front wheels. Color dark green. | 68 | 95.34 | 194.57 |
S12_1108 | 2001 Ferrari Enzo | Classic Cars | 1:12 | Second Gear Diecast | Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis. | 3619 | 95.59 | 207.80 |
S12_1666 | 1958 Setra Bus | Trucks and Buses | 1:12 | Welly Diecast Productions | Model features 30 windows, skylights & glare resistant glass, working steering system, original logos | 1579 | 77.90 | 136.67 |
S12_2823 | 2002 Suzuki XREO | Motorcycles | 1:12 | Unimax Art Galleries | Official logos and insignias, saddle bags located on side of motorcycle, detailed engine, working steering, working suspension, two leather seats, luggage rack, dual exhaust pipes, small saddle bag located on handle bars, two-tone paint with chrome accents, superior die-cast detail , rotating wheels , working kick stand, diecast metal with plastic parts and baked enamel finish. | 9997 | 66.27 | 150.62 |
productLine | textDescription | htmlDescription | image |
Classic Cars | Attention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking for classic muscle cars, dream sports cars or movie-inspired miniatures, you will find great choices in this category. These replicas feature superb attention to detail and craftsmanship and offer features such as working steering system, opening forward compartment, opening rear trunk with removable spare wheel, 4-wheel independent spring suspension, and so on. The models range in size from 1:10 to 1:24 scale and include numerous limited edition and several out-of-production vehicles. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office. | NULL | NULL |
Motorcycles | Our motorcycles are state of the art replicas of classic as well as contemporary motorcycle legends such as Harley Davidson, Ducati and Vespa. Models contain stunning details such as official logos, rotating wheels, working kickstand, front suspension, gear-shift lever, footbrake lever, and drive chain. Materials used include diecast and plastic. The models range in size from 1:10 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. All models come fully assembled and ready for display in the home or office. Most include a certificate of authenticity. | NULL | NULL |
Planes | Unique, diecast airplane and helicopter replicas suitable for collections, as well as home, office or classroom decorations. Models contain stunning details such as official logos and insignias, rotating jet engines and propellers, retractable wheels, and so on. Most come fully assembled and with a certificate of authenticity from their manufacturers. | NULL | NULL |
Ships | The perfect holiday or anniversary gift for executives, clients, friends, and family. These handcrafted model ships are unique, stunning works of art that will be treasured for generations! They come fully assembled and ready for display in the home or office. We guarantee the highest quality, and best value. | NULL | NULL |
Trains | Model trains are a rewarding hobby for enthusiasts of all ages. Whether you're looking for collectible wooden trains, electric streetcars or locomotives, you'll find a number of great choices for any budget within this category. The interactive aspect of trains makes toy trains perfect for young children. The wooden train sets are ideal for children under the age of 5. | NULL | NULL |
Trucks and Buses | The Truck and Bus models are realistic replicas of buses and specialized trucks produced from the early 1920s to present. The models range in size from 1:12 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. Materials used include tin, diecast and plastic. All models include a certificate of authenticity from their manufacturers and are a perfect ornament for the home and office. | NULL | NULL |
Vintage Cars | Our Vintage Car models realistically portray automobiles produced from the early 1900s through the 1940s. Materials used include Bakelite, diecast, plastic and wood. Most of the replicas are in the 1:18 and 1:24 scale sizes, which provide the optimum in detail and accuracy. Prices range from $30.00 up to $180.00 for some special limited edition replicas. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office. | NULL | NULL |
1-1. 제품 관련 지표 리스트업
제품 성과 지표•
판매량(Units Sold): 특정 기간 동안 판매된 제품의 총 수량을 측정하여 각 제품의 시장 수요를 파악합니다. → 확인가능
-- productCode를 기준으로 orderdetails 테이블과 products 테이블을 join
## orderDate가 아닌, requiredDate
select o.requiredDate,
p.productLine,
count(p.productCode) as UnitSold
from products p
left join orderdetails od on p.productCode = od.productCode
join orders o on od.orderNumber = o.orderNumber
where o.status = 'Shipped'
group by p.productLine, o.requiredDate
order by o.requiredDate, p.productLine;
SQL
복사
결과
•
매출 총이익률(Gross Profit Margin): 제품 판매로 얻은 수익에서 매출 원가를 차감한 후, 이를 총 매출로 나눈 비율로, 제품의 수익성을 평가합니다. -> 확인불가, 매출 원가 데이터 없음
SQL
복사
재고 관리 지표•
재고 회전율: 특정 기간 동안 재고가 판매되고 교체되는 빈도를 측정. 높은 재고 회전율은 재고 관리의 효율성을 나타내며, 과도한 재고 보유로 인한 비용을 최소화합니다. → 확인불가
•
판매 대비 재고 비율 (I/S 비율): 판매액에 대한 재고 가치를 측정하여, 적절한 재고 수준을 유지하는 데 도움을 줍니다. 일반적으로 0.167~0.25 사이의 비율이 최적으로 간주됩니다. → 확인가능
SQL
복사
제품 수명 주기 지표•
제품 수명 주기 단계 : 각 제품이 도입, 성장, 성숙, 쇠퇴 중 어느 단계에 있는지를 파악하여 마케팅 및 재고 전략을 조정합니다.
•
신제품 채택률(New Product Adoption Rate): 신제품 출시 후 일정 기간 내에 고객들이 해당 제품을 얼마나 빠르게 구매하는지를 측정합니다.
제품 품질 지표•
반품률(Return Rate): 판매된 제품 중 고객에 의해 반품된 비율을 측정하여 제품 품질이나 고객 만족도를 평가합니다. → orders 테이블의 ‘status’ 가 disputed, cancelled 인 제품 라인군 비율을 비교 → 확인가능
SQL
복사
•
결함률(Defect Rate): 검사 과정에서 발견된 결함이 있는 제품의 비율을 측정하여 품질 관리의 효과성을 판단합니다.