TPC-H 데이터로 스노우플레이크 맛보기
Published in
34 min readJul 12, 2023
안녕하세요.
오늘은 간단하게 SQL 찍먹을 해 볼 수 있는 스크립트를 준비 하였습니다.
개인마다 차이가 있겠지만 스노우플레이크 계정 생성 후 어디서 부터 무엇을 해야할지 감이오지 않을때, 혹은 어떻게 해야하는지 막막할 때가 있습니다.
그런 상황에서 스노우플레이크를 찍먹을 해 볼 수 있도록 맛보기 SQL을 준비 하였습니다.
SQL을 다양하게 사용해보려면 먼저 데이터가 필요 한데 스노우플레이크 에서는 계정을 생성하면 기본적으로 TPC-H 데이터가 제공 됩니다.(다행)
TPC-H 란?
《TPC-H는 결정 지원 벤치마크로, 비즈니스 지향 애드혹 쿼리 및 동시 데이터 수정 모음으로 구성됩니다.
데이터베이스를 채우는 쿼리와 데이터는 광범위한 업계 전반에서 연관성을 갖도록 선택되었습니다.
이 벤치마크는 대규모 데이터를 검사하고 매우 복잡한 쿼리를 실행하며
중요한 비즈니스 질문에 대한 답변을 제공하는 의사결정 지원 시스템을 보여줍니다.》데이터베이스 및 스키마
TPC-H는 다양한 배율 인수를 테스트하기 위해 다양한 크기의 데이터 세트와 함께 제공됩니다. 설명을 위해 Snowflake는 4가지 버전의 TPC-H 데이터를 제공합니다. 데이터는 SNOWFLAKE_SAMPLE_DATA 공유 데이터베이스의 다음 스키마에서 제공됩니다.
- TPCH_SF1: 기본 행 크기로 구성됩니다(수백만 개의 요소).
- TPCH_SF10: 기본 행 크기 x 10으로 구성됩니다.
- TPCH_SF100: 기본 행 크기 x 100으로 구성됩니다(수억 개의 요소).
- TPCH_SF1000: 기본 행 크기 x 1000으로 구성됩니다(수십억 개의 요소).
데이터베이스 항목, 관계 및 특성
TPC-H의 구성 요소는 별도의 개별 테이블 8개(기본 테이블)로 구성됩니다. 이러한 테이블에서 열 사이의 관계에 대한 설명은 다음 ER 다이어그램에서 제공됩니다.
아래는 TPC-H에서 요구하는 SQL을 스노우플레이크 에서 수행할 수 있도록 변환한 SQL 입니다.
/*
- 2.4.1 가격 요약 보고서 쿼리(Q1)
- 이 쿼리는 청구, 배송 및 반환된 비즈니스 금액을 보고합니다.
- 2.4.1.1 비즈니스 질문
- 가격 책정 요약 보고서 쿼리는 지정된 날짜에 배송된 모든 라인 항목에 대한 요약 가격 책정 보고서를 제공합니다.
- 날짜는 데이터베이스에 포함된 가장 큰 배송 날짜로부터 60 - 120일 이내입니다.
- 쿼리는 확장 가격, 할인 확장 가격, 할인 확장 가격 + 세금, 평균 수량, 평균 확장가격 및 평균 할인에 대한 합계를 나열합니다.
- 이러한 집계는 RETURNFLAG 및 LINESTATUS별로 그룹화되고 RETURNFLAG 및 LINESTATUS의 오름차순으로 나열됩니다.
- 각 그룹의 광고 항목 수가 포함됩니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT L_RETURNFLAG
, L_LINESTATUS
, SUM(L_QUANTITY) AS SUM_QTY
, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE
, SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS SUM_DISC_PRICE
, SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT) * (1+L_TAX)) AS SUM_CHARGE
, AVG(L_QUANTITY) AS AVG_QTY
, AVG(L_EXTENDEDPRICE) AS AVG_PRICE
, AVG(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS AVG_DISC
, COUNT(*) AS COUNT_ORDER
FROM LINEITEM
WHERE L_SHIPDATE <= DATEADD(DAY, -90, TO_DATE('1998-12-01'))
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG, L_LINESTATUS
;
/*
- 2.4.2 최소 비용 공급자 쿼리(Q2)
- 이 쿼리는 주어진 지역에서 주어진 부품을 주문하기 위해 어떤 공급업체를 선택해야 하는지 찾습니다.
- 2.4.2.1 비즈니스 질문
- 최소 비용 공급자 쿼리는 주어진 지역에서 특정 유형 및 크기의 각 부품에 대해 최소 비용으로 공급할 수 있는 공급자를 찾습니다.
- 해당 지역의 여러 공급업체가 동일한(최소) 비용으로 원하는 부품 유형 및 크기를 제공하는 경우 쿼리는 계정 잔액이 가장 높은 100개 공급업체의 부품을 나열합니다.
- 각 공급자에 대해 쿼리는공급자의 계정 잔액, 이름 및 국가를 나열합니다.
- 부품 번호 및 제조업체 공급업체의 주소, 전화번호 및 의견 정보.
*/
--Return the first 100 selected rows
use schema snowflake_sample_data.tpch_sf1;
SELECT S_ACCTBAL
, S_NAME
, N_NAME
, P_PARTKEY
, P_MFGR
, S_ADDRESS
, S_PHONE
, S_COMMENT
FROM PART
, SUPPLIER
, PARTSUPP
, NATION
, REGION
WHERE P_PARTKEY = PS_PARTKEY
AND S_SUPPKEY = PS_SUPPKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_REGIONKEY = R_REGIONKEY
AND R_NAME ='EUROPE'
AND P_SIZE = 15
AND P_TYPE LIKE '%BRASS'
AND PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST)
FROM PARTSUPP, SUPPLIER, NATION, REGION
WHERE P_PARTKEY = PS_PARTKEY
AND S_SUPPKEY = PS_SUPPKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_REGIONKEY = R_REGIONKEY
AND R_NAME = 'EUROPE'
)
ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY
LIMIT 100
;
/*
- 2.4.3 배송 우선 순위 쿼리(Q3)
- 이 쿼리는 값이 가장 높은 10개의 배송되지 않은 주문을 검색합니다.
- 2.4.3.1 비즈니스 질문
- 배송 우선 순위 쿼리는 주어진 날짜에 배송되지 않은 주문 중 가장 큰 수익을 올린 주문의 배송 우선 순위와 잠재적 수익을 l_extendedprice * (1-l_discount)의 합계로 정의합니다.
- 주문은 수익의 내림차순으로 나열됩니다.
- 배송되지 않은 주문이 10개 이상 있는 경우 수익이 가장 큰 주문 10개만 나열됩니다.
*/
--Return the first 10 selected rows
use schema snowflake_sample_data.tpch_sf1;
SELECT L_ORDERKEY
, SUM(L_EXTENDEDPRICE * (1- L_DISCOUNT)) AS REVENUE
, O_ORDERDATE
, O_SHIPPRIORITY
FROM CUSTOMER
, ORDERS
, LINEITEM
WHERE C_CUSTKEY = O_CUSTKEY
AND O_ORDERKEY = L_ORDERKEY
AND C_MKTSEGMENT = 'BUILDING'
AND O_ORDERDATE < TO_DATE('1995-03-15')
AND L_SHIPDATE > TO_DATE('1995-03-15')
GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY
ORDER BY REVENUE DESC, O_ORDERDATE
LIMIT 10
;
/*
- 2.4.4 주문 우선 순위 확인 쿼리(Q4)
- 이 쿼리는 주문 우선 순위 시스템이 얼마나 잘 작동하는지 확인하고 고객 만족도를 평가합니다.
- 2.4.4.1 비즈니스 질문
- 주문 우선 순위 확인 쿼리는 해당 연도의 특정 분기에 주문한 주문 수를 계산하며 그 중 고객이 약속한 날짜보다 늦게 하나 이상의 항목을 수신했습니다.
- 쿼리는 오름차순 우선 순위로 정렬된 각 주문 우선 순위에 대한 이러한 주문 수를 나열합니다
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT O_ORDERPRIORITY
, COUNT(*) AS ORDER_COUNT
FROM ORDERS
WHERE O_ORDERDATE >= TO_DATE('1993-07-01')
AND O_ORDERDATE < DATEADD(MONTH, 3, TO_DATE('1993-07-01'))
AND EXISTS (SELECT *
FROM LINEITEM
WHERE O_ORDERKEY = L_ORDERKEY
AND L_COMMITDATE < L_RECEIPTDATE
)
GROUP BY O_ORDERPRIORITY
ORDER BY O_ORDERPRIORITY
;
/*
- 2.4.5 로컬 공급업체 볼륨 쿼리(Q5)
- 이 쿼리는 로컬 공급업체를 통해 수행된 매출 규모를 나열합니다.
- 2.4.5.1 비즈니스 질문
- 지역 공급업체 볼륨 쿼리는 부품을 주문하는 고객과 부품을 채우는 공급업체가 모두 해당 국가 내에 있는 품목 거래에서 발생한 수익 볼륨을 지역의 각 국가에 대해 나열합니다.
- 쿼리는 주어진 지역에 지역 유통 센터를 설립할지 여부를 결정하기 위해 실행됩니다.
- 쿼리는 해당 연도에 주문된 부품만 고려합니다.
- 쿼리는 국가와 매출 규모를 매출 내림차순으로 표시합니다.
- 특정 국가의 모든 적격 광고 항목에 대한 수익 규모는 sum(l_extendedprice * (1 - l_discount)).
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT N_NAME
, SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS REVENUE
FROM CUSTOMER
, ORDERS
, LINEITEM
, SUPPLIER
, NATION
, REGION
WHERE C_CUSTKEY = O_CUSTKEY
AND O_ORDERKEY = L_ORDERKEY
AND S_SUPPKEY = L_SUPPKEY
AND C_NATIONKEY = S_NATIONKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_REGIONKEY = R_REGIONKEY
AND R_NAME = 'ASIA'
AND O_ORDERDATE >= TO_DATE('1994-01-01')
AND O_ORDERDATE < DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY N_NAME
ORDER BY REVENUE DESC
;
/*
- 2.4.6 수익 변경 쿼리 예측(Q6)
- 이 쿼리는 주어진 연도에 지정된 백분율 범위에서 회사 전체의 특정 할인을 제거함으로써 발생했을 수익 증가 금액을 수량화합니다.
- 이러한 유형의 "만약에" 쿼리를 사용하여 수익을 늘릴 수 있는 방법을 찾을 수 있습니다.
- 2.4.6.1 비즈니스 질문
- 예측 수익 변경 쿼리는 DISCOUNT-0.01에서 DISCOUNT+0.01 사이의 할인으로 지정된 연도에 배송된 모든 품목을 고려합니다.
- 쿼리는 l_quantity가 수량 보다 작은 품목에 대해 이러한 할인이 제거된 경우 총 수익이 증가했을 금액을 나열합니다.
- 잠재적인 수익 증가는 적격 범위의 할인 및 수량을 가진 모든 품목에 대한 [l_extendedprice * l_discount]의 합계와 같습니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT SUM(L_EXTENDEDPRICE * L_DISCOUNT) AS REVENUE
FROM LINEITEM
WHERE L_SHIPDATE >= TO_DATE('1994-01-01')
AND L_SHIPDATE < DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
AND L_DISCOUNT BETWEEN 0.06 - 0.01 AND 0.06 + 0.01
AND L_QUANTITY < 24
;
/*
- 2.4.7 대량 배송 쿼리(Q7)
- 이 쿼리는 배송 계약의 재협상을 돕기 위해 특정 국가 간에 배송되는 상품의 가치를 결정합니다.
- 2.4.7.1 비즈니스 질문
- Volume Shipping Query는 지정된 두 국가에 대해 1995년과 1996년에 한 국가의 공급업체에서 다른 국가의 고객에게 부품을 배송한 품목에서 파생된 총 할인 수익을 찾습니다.
- 쿼리는 공급자 국가, 고객 국가, 연도 및 해당 연도에 발생한 선적 수익을 나열합니다.
- 쿼리는 공급자 국가, 고객 국가 및 연도(모두 오름차순)별로 답변을 정렬합니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT NS.N_NAME AS SUPP_NATION
, NC.N_NAME AS CUST_NATION
, EXTRACT(YEAR FROM L_SHIPDATE) AS L_YEAR
, SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS REVENUE
FROM SUPPLIER
, LINEITEM
, ORDERS
, CUSTOMER
, NATION NC
, NATION NS
WHERE S_SUPPKEY = L_SUPPKEY
AND L_ORDERKEY = O_ORDERKEY
AND O_CUSTKEY = C_CUSTKEY
AND NC.N_NATIONKEY = C_NATIONKEY
AND NS.N_NATIONKEY = S_NATIONKEY
AND L_SHIPDATE BETWEEN TO_DATE('1995-01-01') AND TO_DATE('1996-12-31')
AND (
(NS.N_NAME = 'FRANCE' AND NC.N_NAME = 'GERMANY')
OR (NS.N_NAME = 'GERMANY' AND NC.N_NAME = 'FRANCE')
)
GROUP BY SUPP_NATION, CUST_NATION, L_YEAR
ORDER BY SUPP_NATION, CUST_NATION, L_YEAR
;
/*
- 2.4.8 국가 시장 점유율 쿼리(Q8)
- 이 쿼리는 주어진 지역 내에서 주어진 국가의 시장 점유율이 주어진 부품 유형에 대해 2년 동안 어떻게 변했는지 확인합니다.
- 2.4.8.1 비즈니스 질문
- 주어진 지역 내에서 주어진 국가에 대한 시장 점유율은 해당 지역의 공급자가 공급한 특정 유형의 제품에서 얻은 수익의 비율, [l_extendedprice * (1- l_discount)]의 합계로 정의됩니다.
- 주어진 민족. 쿼리는 이 순서로 표시된 1995년과 1996년에 대해 이를 결정합니다.
*/
use schema snowflake_sample_data.tpch_sf1;
WITH ALL_NATIONS AS (
SELECT EXTRACT(YEAR FROM O_ORDERDATE) AS O_YEAR
, L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME
, NS.N_NAME AS NATION
FROM PART
, SUPPLIER
, LINEITEM
, ORDERS
, CUSTOMER
, NATION NC
, NATION NS
, REGION
WHERE P_PARTKEY = L_PARTKEY
AND S_SUPPKEY = L_SUPPKEY
AND L_ORDERKEY = O_ORDERKEY
AND O_CUSTKEY = C_CUSTKEY
AND C_NATIONKEY = NC.N_NATIONKEY
AND S_NATIONKEY = NS.N_NATIONKEY
AND NC.N_REGIONKEY = R_REGIONKEY
AND R_NAME = 'AMERICA'
AND O_ORDERDATE BETWEEN TO_DATE('1995-01-01') AND TO_DATE('1996-12-31')
AND P_TYPE = 'ECONOMY ANODIZED STEEL'
)
SELECT O_YEAR
, SUM(CASE WHEN NATION = 'BRAZIL' THEN VOLUME
ELSE 0
END) / SUM(VOLUME) AS MKT_SHARE
FROM ALL_NATIONS
GROUP BY O_YEAR
ORDER BY O_YEAR
;
/*
- 2.4.9 제품 유형 이익 측정 쿼리(Q9)
- 이 쿼리는 공급업체 국가 및 연도별로 분류된 주어진 부품 라인에서 얼마나 많은 이익이 발생하는지 확인합니다.
- 2.4.9.1 비즈니스 질문
- Product Type Profit Measure Query는 각 국가 및 연도에 대해 이름에 지정된 하위 문자열이 포함되고 해당 국가의 공급업체가 채운 해당 연도에 주문한 모든 부품의 이익을 찾습니다.
- 이익은 지정된 라인의 부품을 설명하는 모든 라인 항목에 대해 [(l_extendedprice*(1-l_discount)) - (ps_supplycost * l_quantity)]의 합계로 정의됩니다.
- 쿼리는 국가를 알파벳 오름차순으로 나열하고 각 국가에 대해 연도 및 이익을 연도별로 내림차순(가장 최근 것부터)으로 나열합니다.
*/
use schema snowflake_sample_data.tpch_sf10;
SELECT N_NAME AS NATION
, EXTRACT(YEAR FROM O_ORDERDATE) AS O_YEAR
, SUM((L_EXTENDEDPRICE * (1 - L_DISCOUNT)) - (PS_SUPPLYCOST * L_QUANTITY)) AS SUM_PROFIT
FROM PART
, SUPPLIER
, LINEITEM
, PARTSUPP
, ORDERS
, NATION
WHERE S_SUPPKEY = L_SUPPKEY
AND PS_SUPPKEY = L_SUPPKEY
AND PS_PARTKEY = L_PARTKEY
AND P_PARTKEY = L_PARTKEY
AND O_ORDERKEY = L_ORDERKEY
AND S_NATIONKEY = N_NATIONKEY
AND P_NAME LIKE '%green%'
GROUP BY NATION, O_YEAR
ORDER BY NATION, O_YEAR DESC
;
/*
- 2.4.10 반품된 품목 보고 쿼리(Q10)
- 쿼리는 배송된 부품에 문제가 있을 수 있는 고객을 식별합니다.
- 2.4.10.1 비즈니스 질문
- 반품된 품목 보고 쿼리는 부품을 반품한 특정 분기의 수익 손실에 대한 영향 측면에서 상위 20명의 고객을 찾습니다.
- 쿼리는 지정된 분기에 주문된 부품만 고려합니다.
- 쿼리는 고객의 이름, 주소, 국가, 전화번호, 계정 잔액, 댓글 정보 및 손실된 수익을 나열합니다.
- 고객은 손실된 수익의 내림차순으로 나열됩니다.
- 손실된 수익은 모든 적격 광고 항목에 대해 sum(l_extendedprice*(1-l_discount))으로 정의됩니다.
*/
--Return the first 20 selected rows
use schema snowflake_sample_data.tpch_sf1;
WITH BASE AS (
SELECT O_CUSTKEY
, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE
FROM ORDERS
, LINEITEM
WHERE O_ORDERKEY = L_ORDERKEY
AND L_RETURNFLAG = 'R'
AND O_ORDERDATE >= TO_DATE('1993-10-01')
AND O_ORDERDATE < DATEADD(MONTH, 3, TO_DATE('1993-10-01'))
GROUP BY O_CUSTKEY
)
SELECT C_CUSTKEY
, C_NAME
, REVENUE
, C_ACCTBAL
, N_NAME
, C_ADDRESS
, C_PHONE
, C_COMMENT
FROM NATION
, CUSTOMER
, BASE
WHERE N_NATIONKEY = C_NATIONKEY
AND C_CUSTKEY = O_CUSTKEY
ORDER BY REVENUE DESC
LIMIT 20
;
/*
- 2.4.11 중요 주식 식별 쿼리(Q11)
- 이 쿼리는 주어진 국가에서 공급업체 재고의 가장 중요한 하위 집합을 찾습니다.
- 2.4.11.1 비즈니스 질문
- 중요 재고 식별 쿼리는 주어진 국가에서 공급업체의 사용 가능한 재고를 스캔하여 사용 가능한 모든 부품의 총 가치에서 상당한 비율을 차지하는 모든 부품을 찾습니다.
- 쿼리는 부품 번호와 해당 부품의 값을 값의 내림차순으로 표시합니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT PS_PARTKEY
, SUM(PS_SUPPLYCOST * PS_AVAILQTY) AS VALUE
FROM PARTSUPP
, SUPPLIER
, NATION
WHERE PS_SUPPKEY = S_SUPPKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'GERMANY'
GROUP BY PS_PARTKEY
HAVING SUM(PS_SUPPLYCOST * PS_AVAILQTY) > (
SELECT SUM(PS_SUPPLYCOST * PS_AVAILQTY) * 0.0001
FROM PARTSUPP
, SUPPLIER
, NATION
WHERE PS_SUPPKEY = S_SUPPKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'GERMANY')
ORDER BY VALUE DESC
;
/*
- 2.4.12 배송 모드 및 주문 우선 순위 쿼리(Q12)
- 이 쿼리는 더 저렴한 배송 모드를 선택하는 것이 약정 날짜 이후에 고객이 더 많은 부품을 받게 함으로써 중요 우선 순위 주문에 부정적인 영향을 미치는지 여부를 결정합니다.
- 2.4.12.1 비즈니스 질문
- 배송 모드 및 주문 우선 순위 쿼리는 지정된 연도에 고객이 실제로 받은 라인 항목에 대해 배송 모드별로 l_receiptdate가 지정된 두 가지 배송 모드에 대해 l_commitdate를 초과하는 주문에 속하는 라인 항목 수를 계산합니다.
- l_commitdate 이전에 실제로 배송된 항목만 고려됩니다.
- 늦은 광고 항목은 우선순위가 URGENT 또는 HIGH인 항목과 URGENT 또는 HIGH가 아닌 우선순위가 있는 항목의 두 그룹으로 분할됩니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT L_SHIPMODE
, SUM(CASE WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH' THEN 1
ELSE 0
END) AS HIGH_LINE_COUNT
, SUM(CASE WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH' THEN 1
ELSE 0
END) AS LOW_LINE_COUNT
FROM ORDERS
, LINEITEM
WHERE O_ORDERKEY = L_ORDERKEY
AND L_SHIPMODE IN ('MAIL','SHIP')
AND L_COMMITDATE < L_RECEIPTDATE
AND L_SHIPDATE < L_COMMITDATE
AND L_RECEIPTDATE >= TO_DATE('1994-01-01')
AND L_RECEIPTDATE < DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY L_SHIPMODE
ORDER BY L_SHIPMODE
;
/*
- 2.4.13 고객 배포 쿼리(Q13)
- 이 쿼리는 고객과 주문 크기 간의 관계를 찾습니다.
- 2.4.13.1 비즈니스 질문
- 이 쿼리는 과거 또는 현재 주문 기록이 없는 고객을 포함하여 고객이 주문한 수에 따라 고객 분포를 결정합니다.
- 주문이 없는 고객 수, 1, 2, 3 등의 고객 수를 계산하고 보고합니다.
- 계산된 주문이 몇 가지 특별 주문 범주 중 하나에 속하지 않도록 확인합니다.
- 특정 패턴을 찾아 주문 설명 열에서 특수 범주를 식별합니다.
*/
use schema snowflake_sample_data.tpch_sf1;
WITH C_ORDERS AS (
SELECT C_CUSTKEY
, COUNT(O_ORDERKEY) AS C_COUNT
FROM CUSTOMER
LEFT JOIN ORDERS
ON C_CUSTKEY = O_CUSTKEY
AND O_COMMENT NOT LIKE '%special%requests%'
GROUP BY C_CUSTKEY
)
SELECT C_COUNT
, COUNT(*) AS CUSTDIST
FROM C_ORDERS
GROUP BY C_COUNT
ORDER BY CUSTDIST DESC, C_COUNT DESC
;
/*
- 2.4.14 프로모션 효과 쿼리(Q14)
- 이 쿼리는 TV 광고 또는 특별 캠페인과 같은 프로모션에 대한 시장 반응을 모니터링합니다.
- 2.4.14.1 비즈니스 질문
- 판촉 효과 쿼리는 특정 연도 및 월의 수익 중 판촉 부품에서 파생된 비율을 결정합니다.
- 쿼리는 해당 월에 실제로 배송된 부품만 고려하고 백분율을 제공합니다.
- 수익은 (l_extendedprice * (1-l_discount))로 정의됩니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT 100.00
*
SUM(CASE WHEN P_TYPE LIKE 'PROMO%' THEN L_EXTENDEDPRICE *(1-L_DISCOUNT)
ELSE 0
END)
/
SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS PROMO_REVENUE
FROM LINEITEM
, PART
WHERE L_PARTKEY = P_PARTKEY
AND L_SHIPDATE >= TO_DATE('1995-09-01')
AND L_SHIPDATE < DATEADD(MONTH ,1, TO_DATE('1995-09-01'))
;
/*
- 2.4.15 상위 공급업체 쿼리(Q15)
- 이 쿼리는 보상을 받거나 더 많은 비즈니스를 제공하거나 특별한 인정을 받기 위해 식별될 수 있도록 상위 공급업체를 결정합니다.
- 2.4.15.1 비즈니스 질문
- 상위 공급업체 쿼리는 해당 연도의 특정 분기 동안 출하된 부품의 전체 수익에 가장 많이 기여한 공급업체를 찾습니다.
- 동점인 경우 쿼리는 기여도가 최대값과 동일한 모든 공급업체를 공급업체 번호 순서로 나열합니다.
*/
use schema snowflake_sample_data.tpch_sf1;
--create view revenue[STREAM_ID] (supplier_no, total_revenue) as
WITH REVENUE AS (
SELECT L_SUPPKEY AS SUPPLIER_NO
, SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS TOTAL_REVENUE
FROM LINEITEM
WHERE L_SHIPDATE >= TO_DATE('1996-01-01')
AND L_SHIPDATE < DATEADD(MONTH, 3, TO_DATE('1996-01-01'))
GROUP BY L_SUPPKEY
)
SELECT S_SUPPKEY
, S_NAME
, S_ADDRESS
, S_PHONE
, TOTAL_REVENUE
FROM REVENUE
, SUPPLIER
WHERE S_SUPPKEY = SUPPLIER_NO
AND TOTAL_REVENUE = (SELECT MAX(TOTAL_REVENUE) FROM REVENUE)
ORDER BY S_SUPPKEY
;
/*
- 2.4.16 부품/공급자 관계 쿼리(Q16)
- 이 쿼리는 주어진 속성으로 부품을 공급할 수 있는 공급업체의 수를 찾습니다.
- 예를 들어, 주문량이 많은 부품에 대해 충분한 수의 공급업체가 있는지 확인하는데 사용할 수 있습니다.
- 2.4.16.1 비즈니스 질문
- 부품/공급업체 관계 쿼리는 특정 고객의 요구 사항을 충족하는 부품을 공급할 수 있는 공급업체의 수를 계산합니다.
- 고객은 8가지 다른 크기의 부품에 관심이있습니다.
- 단, 해당 부품이 특정 유형이 아니고, 특정 브랜드가 아니며, Better Business Bureau에 불만 사항이 등록된 공급업체가 아닙니다.
- 결과는 내림차순 개수 및 오름차순 브랜드, 유형 및 크기로 표시되어야 합니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT P_BRAND
, P_TYPE
, P_SIZE
, COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT
FROM PARTSUPP
, PART
WHERE PS_PARTKEY = P_PARTKEY
AND P_BRAND <> 'Brand#45'
AND P_TYPE NOT LIKE 'MEDIUM POLISHED%'
AND P_SIZE IN (49,14,23,45,19,3,36,9)
AND PS_SUPPKEY NOT IN (SELECT S_SUPPKEY
FROM SUPPLIER
WHERE S_COMMENT LIKE '%Customer%Complaints%')
GROUP BY P_BRAND, P_TYPE, P_SIZE
ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE
;
/*
- 2.4.17 소량 주문 수익 쿼리(Q17)
- 이 쿼리는 소량의 주문이 더 이상 채워지지 않을 경우 손실될 평균 연간 수익을 결정합니다.
- 특정 부품의 수량. 이것은 더 큰 선적에 판매를 집중함으로써 간접비를 줄일 수 있습니다.
- 2.4.17.1 비즈니스 질문
- 소량 주문 수익 쿼리는 주어진 브랜드의 부품과 주어진 컨테이너 유형을 고려하고 7년 데이터베이스의 모든 주문(과거 및 보류 중)에 대해 주문된 해당 부품의 평균 품목 수량을 결정합니다.
- 이 평균의 20% 미만의 수량으로 이러한 부품에 대한 주문이 더 이상 이루어지지 않으면 연간 평균 총(할인되지 않은) 매출 손실은 얼마입니까?
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY
FROM LINEITEM
, PART
WHERE P_PARTKEY = L_PARTKEY
AND P_BRAND = 'Brand#23'
AND P_CONTAINER = 'MED BOX'
AND L_QUANTITY < (SELECT 0.2 * AVG(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = P_PARTKEY)
;
/*
- 2.4.18 대량 고객 쿼리(Q18)
- 대용량 고객 쿼리는 대량 주문을 한 고객을 기준으로 고객의 순위를 지정합니다. 대량 주문은 총 수량이 일정 수준 이상인 주문으로 정의됩니다.
- 2.4.18.1 비즈니스 질문
- 대용량 고객 쿼리는 대량 주문을 한 적이 있는 상위 100명의 고객 목록을 찾습니다.
- 쿼리는 고객 이름, 고객 키, 주문 키, 날짜 및 총 가격, 주문 수량을 나열합니다.
*/
--Return the first 100 selected rows
use schema snowflake_sample_data.tpch_sf1;
SELECT C_NAME
, C_CUSTKEY
, O_ORDERKEY
, O_ORDERDATE
, O_TOTALPRICE
, SUM(L_QUANTITY)
FROM CUSTOMER
, ORDERS
, LINEITEM
WHERE C_CUSTKEY = O_CUSTKEY
AND O_ORDERKEY = L_ORDERKEY
AND O_ORDERKEY IN (SELECT L_ORDERKEY
FROM LINEITEM
GROUP BY L_ORDERKEY
HAVING SUM(L_QUANTITY) > 300)
GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE
ORDER BY O_TOTALPRICE DESC, O_ORDERDATE
LIMIT 100
;
/*
- 2.4.19 할인된 수익 쿼리(Q19)
- 할인된 수익 쿼리는 특정 방식으로 처리된 선택한 부품의 판매로 인한 총 할인 수익을 보고합니다.
- 이 쿼리는 데이터 마이닝 도구에서 프로그래밍 방식으로 생성 할 수 있는 코드의 예입니다.
- 2.4.19.1 비즈니스 질문
- 할인된 수익 쿼리는 항공으로 배송되고 직접 배송된 세 가지 유형의 부품에 대한 모든 주문에 대한 총 할인 수익을 찾습니다.
- 부품은 특정 브랜드, 용기 목록 및 다양한 크기의 조합을 기반으로 선택됩니다.
*/
use schema snowflake_sample_data.tpch_sf1;
SELECT SUM(L_EXTENDEDPRICE * (1-L_DISCOUNT)) AS REVENUE
FROM LINEITEM
, PART
WHERE P_PARTKEY = L_PARTKEY
AND
((
p_brand = 'Brand#12'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 1 and l_quantity <= 1 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#23.'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 10 and l_quantity <= 10 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#34'
and p_container in ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 20 and l_quantity <= 20 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
))
;
/*
- 2.4.20 잠재적 부품 프로모션 쿼리(Q20)
- 잠재적 부품 판촉 쿼리는 판촉 제안에 대한 날짜가 될 수 있는 부품을 선택한 특정 국가의 공급업체를 식별합니다.
- 2.4.20.1 비즈니스 질문
- 잠재적 부품 판촉 쿼리는 사용 가능한 특정 부품을 초과한 공급업체를 식별합니다.
- 초과는 해당 국가에 대해 해당 연도에 공급업체가 선적한 해당 부품과 같은 부품의 50% 이상으로 정의됩니다.
- 이름이 특정 명명 규칙을 공유하는 부분만 고려됩니다.
*/
use schema snowflake_sample_data.tpch_sf1;
WITH BASE AS (
SELECT PS_SUPPKEY
FROM PARTSUPP
WHERE PS_PARTKEY IN (SELECT P_PARTKEY FROM PART WHERE P_NAME LIKE 'forest%')
AND PS_AVAILQTY > (SELECT 0.5*SUM(L_QUANTITY)
FROM LINEITEM
WHERE L_PARTKEY = PS_PARTKEY
AND L_SUPPKEY = PS_SUPPKEY
AND L_SHIPDATE >= TO_DATE('1994-01-01')
AND L_SHIPDATE < DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
)
)
SELECT S_NAME
, S_ADDRESS
FROM SUPPLIER
, NATION
WHERE S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'CANADA'
AND S_SUPPKEY IN (SELECT PS_SUPPKEY FROM BASE)
ORDER BY S_NAME
;
/*
- 2.4.21 주문 대기 쿼리를 유지한 공급업체(Q21)
- 이 쿼리는 필요한 부품을 적시에 배송하지 못한 특정 공급업체를 식별합니다.
- 2.4.21.1 비즈니스 질문
- 주문 대기 중인 공급업체 쿼리는 지정된 국가에 대해 해당 제품이 약속된 배송 날짜를 충족하지 못한
유일한 공급업체인 다중 공급업체 주문(현재 상태가 'F'임) 의 일부인 공급업체를 식별합니다.
*/
--Return the first 100 selected rows.
use schema snowflake_sample_data.tpch_sf1;
SELECT S_NAME
, COUNT(*) AS NUMWAIT
FROM SUPPLIER
, LINEITEM L1
, ORDERS
, NATION
WHERE S_SUPPKEY = L1.L_SUPPKEY
AND O_ORDERKEY = L1.L_ORDERKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'SAUDI ARABIA'
AND O_ORDERSTATUS = 'F'
AND L1.L_RECEIPTDATE > L1.L_COMMITDATE
AND EXISTS (SELECT *
FROM LINEITEM L2
WHERE L2.L_ORDERKEY = L1.L_ORDERKEY
AND L2.L_SUPPKEY <> L1.L_SUPPKEY
)
AND NOT EXISTS (SELECT *
FROM LINEITEM L3
WHERE L3.L_ORDERKEY = L1.L_ORDERKEY
AND L3.L_SUPPKEY <> L1.L_SUPPKEY
AND L3.L_RECEIPTDATE > L3.L_COMMITDATE
)
GROUP BY S_NAME
ORDER BY NUMWAIT DESC, S_NAME
;
/*
- 2.4.22 글로벌 영업 기회 쿼리(Q22)
- Global Sales Opportunity Query는 구매할 가능성이 있는 고객이 있는 지역을 식별합니다.
- 2.4.22.1 비즈니스 질문
- 이 쿼리는 특정 국가 코드 범위 내에서 7년 동안 주문을 하지 않았지만 평균 "양수" 계정 잔액보다 많은 고객이 몇 명인지 계산합니다.
- 그것은 또한 그 균형의 크기를 반영합니다.
- 국가 코드는 c_phone의 처음 두 문자로 정의됩니다.
*/
use schema snowflake_sample_data.tpch_sf1;
WITH CUSTSALE AS (
SELECT SUBSTRING(C_PHONE, 1, 2) AS CNTRYCODE
, C_ACCTBAL
FROM CUSTOMER
WHERE SUBSTRING(C_PHONE, 1, 2) IN ('13','31','23','29','30','18','17')
AND C_ACCTBAL > (SELECT AVG(C_ACCTBAL)
FROM CUSTOMER
WHERE C_ACCTBAL > 0.00
AND SUBSTRING(C_PHONE, 1, 2) IN ('13','31','23','29','30','18','17')
)
AND NOT EXISTS (SELECT *
FROM ORDERS
WHERE O_CUSTKEY = C_CUSTKEY
)
)
SELECT CNTRYCODE
, COUNT(*) AS NUMCUST
, SUM(C_ACCTBAL) AS TOTACCTBAL
FROM CUSTSALE
GROUP BY CNTRYCODE
ORDER BY CNTRYCODE
;
