In this notebook, we explore how to reimplement SQL queries from the well-known TPC-H benchmark in Rabbit.
We start with loading the dataset.
include("../tpch.jl")
using RBT
setdb(tpch)
Region: name :: ASCIIString comment :: ASCIIString nation (inverse of Nation.region) :: Array{Nation,1} # unique, covering Nation: name :: ASCIIString region :: Region comment :: ASCIIString customer (inverse of Customer.nation) :: Array{Customer,1} # unique, covering supplier (inverse of Supplier.nation) :: Array{Supplier,1} # unique, covering Customer: name :: ASCIIString address :: ASCIIString nation :: Nation phone :: ASCIIString acctbal :: RBT.Monetary{:USD} mktsegment :: ASCIIString comment :: ASCIIString order (inverse of Order.customer) :: Array{Order,1} # unique, covering Supplier: name :: ASCIIString address :: ASCIIString nation :: Nation phone :: ASCIIString acctbal :: RBT.Monetary{:USD} comment :: ASCIIString partsupp (inverse of Partsupp.supplier) :: Array{Partsupp,1} # unique, covering lineitem (inverse of Lineitem.supplier) :: Array{Lineitem,1} # unique, covering Part: name :: ASCIIString mfgr :: ASCIIString brand :: ASCIIString type_ :: ASCIIString size :: Int64 container :: ASCIIString retailprice :: RBT.Monetary{:USD} comment :: ASCIIString partsupp (inverse of Partsupp.part) :: Array{Partsupp,1} # unique, covering lineitem (inverse of Lineitem.part) :: Array{Lineitem,1} # unique, covering Partsupp: part :: Part supplier :: Supplier availqty :: Int64 supplycost :: RBT.Monetary{:USD} comment :: ASCIIString lineitem (inverse of Lineitem.partsupp) :: Array{Lineitem,1} # unique, covering Order: customer :: Customer orderstatus :: ASCIIString totalprice :: RBT.Monetary{:USD} orderdate :: Date orderpriority :: ASCIIString clerk :: ASCIIString shippriority :: Int64 comment :: ASCIIString lineitem (inverse of Lineitem.order) :: Array{Lineitem,1} # unique, covering Lineitem: order :: Order part :: Part supplier :: Supplier partsupp :: Partsupp linenumber :: Int64 quantity :: Int64 extendedprice :: RBT.Monetary{:USD} discount :: Float64 tax :: Float64 returnflag :: ASCIIString linestatus :: ASCIIString shipdate :: Date commitdate :: Date receiptdate :: Date shipinstruct :: ASCIIString shipmode :: ASCIIString comment :: ASCIIString
The TPC-H benchmark consists of a generated dataset of customers, suppliers, products and orders as well as a collection of queries that perform various types of business analysis.
Table order
lists all orders made by customers.
@query(
order
:select(
customer.name, totalprice, orderdate))
name | totalprice | orderdate | |
---|---|---|---|
1 | Customer#000000296 | 159877.96 | 1996-01-02 |
2 | Customer#000000625 | 69858.91 | 1996-12-01 |
3 | Customer#000000988 | 183718.98 | 1993-10-14 |
4 | Customer#000001096 | 50463.93 | 1995-10-11 |
5 | Customer#000000356 | 133409.48 | 1994-07-30 |
6 | Customer#000000445 | 35731.21 | 1992-02-21 |
7 | Customer#000000314 | 229881.30 | 1996-01-10 |
8 | Customer#000001042 | 173503.97 | 1995-07-16 |
9 | Customer#000000536 | 135080.42 | 1993-10-27 |
10 | Customer#000000490 | 64891.67 | 1998-07-21 |
⋮ | ⋮ | ⋮ | ⋮ |
Individual items in each order are stored in the lineitem
table. Each item describes the ordered part, the quantity, the supplier and the pricing information.
@query(
lineitem
:select(
order.customer, linenumber, part, supplier, quantity, extendedprice, discount, tax, shipdate))
customer | linenumber | part | supplier | quantity | extendedprice | discount | tax | shipdate | |
---|---|---|---|---|---|---|---|---|---|
1 | Customer#000000296 | 1 | dodger brown ivory blue pink | Supplier#000000078 | 17 | 19435.08 | 0.04 | 0.02 | 1996-03-13 |
2 | Customer#000000296 | 2 | dodger midnight salmon drab saddle | Supplier#000000060 | 36 | 51823.08 | 0.09 | 0.06 | 1996-04-12 |
3 | Customer#000000296 | 3 | blush thistle orchid red lace | Supplier#000000031 | 8 | 11284.08 | 0.1 | 0.02 | 1996-01-29 |
4 | Customer#000000296 | 4 | turquoise indian lemon lavender misty | Supplier#000000039 | 28 | 25704.28 | 0.09 | 0.06 | 1996-04-21 |
5 | Customer#000000296 | 5 | turquoise lime royal metallic azure | Supplier#000000020 | 24 | 26236.56 | 0.1 | 0.04 | 1996-03-30 |
6 | Customer#000000296 | 6 | burnished black blue metallic orchid | Supplier#000000009 | 32 | 32835.84 | 0.07 | 0.02 | 1996-01-30 |
7 | Customer#000000625 | 1 | peach goldenrod honeydew moccasin sienna | Supplier#000000031 | 38 | 66532.30 | 0.0 | 0.05 | 1997-01-28 |
8 | Customer#000000988 | 1 | green blush tomato burlywood seashell | Supplier#000000016 | 45 | 42076.35 | 0.06 | 0.0 | 1994-02-02 |
9 | Customer#000000988 | 2 | linen frosted slate coral peru | Supplier#000000057 | 49 | 51604.35 | 0.1 | 0.0 | 1993-11-09 |
10 | Customer#000000988 | 3 | blue drab lime khaki sienna | Supplier#000000053 | 27 | 25083.54 | 0.06 | 0.07 | 1994-01-16 |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
For each customer and supplier, we track their country of origin. Table nation
contains a list of all countries partitioned by region.
@query(
nation
:select(
name,
region,
cust_count => count(customer),
supp_count => count(supplier)))
name | region | cust_count | supp_count | |
---|---|---|---|---|
1 | ALGERIA | AFRICA | 51 | 3 |
2 | ARGENTINA | AMERICA | 50 | 3 |
3 | BRAZIL | AMERICA | 63 | 1 |
4 | CANADA | AMERICA | 48 | 2 |
5 | EGYPT | MIDDLE EAST | 56 | 4 |
6 | ETHIOPIA | AFRICA | 44 | 3 |
7 | FRANCE | EUROPE | 32 | 1 |
8 | GERMANY | EUROPE | 41 | 4 |
9 | INDIA | ASIA | 52 | 5 |
10 | INDONESIA | ASIA | 56 | 4 |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
The following query definitions are taken directly from TPC-H documentation. We will show how to incrementally construct these queries in Rabbit.
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 29]
This query reports the amount of business that was billed, shipped, and returned.
The Pricing Summary Report Query provides a summary pricing report for all lineitems shipped as of a given date. The date is within 60 - 120 days of the greatest ship date contained in the database. The query lists totals for extended price, discounted extended price, discounted extended price plus tax, average quantity, average extended price, and average discount. These aggregates are grouped by RETURNFLAG and LINESTATUS, and listed in ascending order of RETURNFLAG and LINESTATUS. A count of the number of lineitems in each group is included.
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_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '[DELTA]' day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
To generate this report, we use the data from the lineitem
table.
@query(
lineitem
:select(order, linenumber, shipdate, quantity, extendedprice, discount, tax, returnflag, linestatus))
order | linenumber | shipdate | quantity | extendedprice | discount | tax | returnflag | linestatus | |
---|---|---|---|---|---|---|---|---|---|
1 | Customer#000000296 | 1 | 1996-03-13 | 17 | 19435.08 | 0.04 | 0.02 | N | O |
2 | Customer#000000296 | 2 | 1996-04-12 | 36 | 51823.08 | 0.09 | 0.06 | N | O |
3 | Customer#000000296 | 3 | 1996-01-29 | 8 | 11284.08 | 0.1 | 0.02 | N | O |
4 | Customer#000000296 | 4 | 1996-04-21 | 28 | 25704.28 | 0.09 | 0.06 | N | O |
5 | Customer#000000296 | 5 | 1996-03-30 | 24 | 26236.56 | 0.1 | 0.04 | N | O |
6 | Customer#000000296 | 6 | 1996-01-30 | 32 | 32835.84 | 0.07 | 0.02 | N | O |
7 | Customer#000000625 | 1 | 1997-01-28 | 38 | 66532.30 | 0.0 | 0.05 | N | O |
8 | Customer#000000988 | 1 | 1994-02-02 | 45 | 42076.35 | 0.06 | 0.0 | R | F |
9 | Customer#000000988 | 2 | 1993-11-09 | 49 | 51604.35 | 0.1 | 0.0 | R | F |
10 | Customer#000000988 | 3 | 1994-01-16 | 27 | 25083.54 | 0.06 | 0.07 | A | F |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
We start with extracting all lineitems in the selected date range.
@query(
lineitem
:filter(shipdate <= date("1998-12-01") - DELTA*days),
DELTA=90)
order | part | supplier | linenumber | quantity | extendedprice | discount | tax | returnflag | linestatus | shipdate | commitdate | receiptdate | shipinstruct | shipmode | comment | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Customer#000000296 | dodger brown ivory blue pink | Supplier#000000078 | 1 | 17 | 19435.08 | 0.04 | 0.02 | N | O | 1996-03-13 | 1996-02-12 | 1996-03-22 | DELIVER IN PERSON | TRUCK | egular courts above the |
2 | Customer#000000296 | dodger midnight salmon drab saddle | Supplier#000000060 | 2 | 36 | 51823.08 | 0.09 | 0.06 | N | O | 1996-04-12 | 1996-02-28 | 1996-04-20 | TAKE BACK RETURN | ly final dependencies: slyly bold | |
3 | Customer#000000296 | blush thistle orchid red lace | Supplier#000000031 | 3 | 8 | 11284.08 | 0.1 | 0.02 | N | O | 1996-01-29 | 1996-03-05 | 1996-01-31 | TAKE BACK RETURN | REG AIR | riously. regular, express dep |
4 | Customer#000000296 | turquoise indian lemon lavender misty | Supplier#000000039 | 4 | 28 | 25704.28 | 0.09 | 0.06 | N | O | 1996-04-21 | 1996-03-30 | 1996-05-16 | NONE | AIR | lites. fluffily even de |
5 | Customer#000000296 | turquoise lime royal metallic azure | Supplier#000000020 | 5 | 24 | 26236.56 | 0.1 | 0.04 | N | O | 1996-03-30 | 1996-03-14 | 1996-04-01 | NONE | FOB | pending foxes. slyly re |
6 | Customer#000000296 | burnished black blue metallic orchid | Supplier#000000009 | 6 | 32 | 32835.84 | 0.07 | 0.02 | N | O | 1996-01-30 | 1996-02-07 | 1996-02-03 | DELIVER IN PERSON | arefully slyly ex | |
7 | Customer#000000625 | peach goldenrod honeydew moccasin sienna | Supplier#000000031 | 1 | 38 | 66532.30 | 0.0 | 0.05 | N | O | 1997-01-28 | 1997-01-14 | 1997-02-02 | TAKE BACK RETURN | RAIL | ven requests. deposits breach a |
8 | Customer#000000988 | green blush tomato burlywood seashell | Supplier#000000016 | 1 | 45 | 42076.35 | 0.06 | 0.0 | R | F | 1994-02-02 | 1994-01-04 | 1994-02-23 | NONE | AIR | ongside of the furiously brave acco |
9 | Customer#000000988 | linen frosted slate coral peru | Supplier#000000057 | 2 | 49 | 51604.35 | 0.1 | 0.0 | R | F | 1993-11-09 | 1993-12-20 | 1993-11-24 | TAKE BACK RETURN | RAIL | unusual accounts. eve |
10 | Customer#000000988 | blue drab lime khaki sienna | Supplier#000000053 | 3 | 27 | 25083.54 | 0.06 | 0.07 | A | F | 1994-01-16 | 1993-11-22 | 1994-01-23 | DELIVER IN PERSON | SHIP | nal foxes wake. |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
Next, we group the lineitems by values of returnflag
and linestatus
attributes.
@query(
lineitem
:filter(shipdate <= date("1998-12-01") - DELTA*days)
:group(returnflag, linestatus),
DELTA=90)
returnflag | linestatus | lineitem | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | A | F |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2 | N | F |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
3 | N | O |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4 | R | F |
|
For each distinct combination of returnflag
and linestatus
values, we see a collection of corresponding lineitem rows. We can now use aggregate functions to summarize information about matching lineitems.
For example, to find the total number of matching lineitems, we can use count
aggregate.
@query(
lineitem
:filter(shipdate <= date("1998-12-01") - DELTA*days)
:group(returnflag, linestatus)
:select(returnflag, linestatus, count(lineitem)),
DELTA=90)
returnflag | linestatus | ||
---|---|---|---|
1 | A | F | 11958 |
2 | N | F | 288 |
3 | N | O | 23326 |
4 | R | F | 11963 |
The query output now has the shape of the report. We only need to calculate the respective fields.
@query(
lineitem
:filter(shipdate <= date("1998-12-01") - DELTA*days)
:group(returnflag, linestatus)
:select(
returnflag,
linestatus,
sum_qty => sum(lineitem.quantity),
sum_base_price => sum(lineitem.extendedprice),
sum_disc_price => sum(lineitem.(extendedprice*(1-discount))),
sum_charge => sum(lineitem.(extendedprice*(1-discount)*(1+tax))),
avg_qty => mean(lineitem.quantity),
avg_price => mean(lineitem.extendedprice),
avg_disc => mean(lineitem.discount),
count_order => count(lineitem)),
DELTA=90)
returnflag | linestatus | sum_qty | sum_base_price | sum_disc_price | sum_charge | avg_qty | avg_price | avg_disc | count_order | |
---|---|---|---|---|---|---|---|---|---|---|
1 | A | F | 303771 | 401515429.00 | 381465877.43 | 396851569.64 | 25.40316106372303 | 33577.14 | 0.05011958521491882 | 11958 |
2 | N | F | 7354 | 9721375.74 | 9247800.68 | 9616674.43 | 25.53472222222222 | 33754.78 | 0.04809027777777776 | 288 |
3 | N | O | 593340 | 787065026.86 | 748034019.78 | 777970207.05 | 25.436851581925747 | 33741.96 | 0.04988510674783497 | 23326 |
4 | R | F | 306623 | 406030555.01 | 385792652.67 | 401431970.22 | 25.630945415029675 | 33940.53 | 0.04973083674663541 | 11963 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 30]
This query finds which supplier should be selected to place an order for a given part in a given region.
The Minimum Cost Supplier Query finds, in a given region, for each part of a certain type and size, the supplier who can supply it at minimum cost. If several suppliers in that region offer the desired part type and size at the same (minimum) cost, the query lists the parts from suppliers with the 100 highest account balances. For each supplier, the query lists the supplier's account balance, name and nation; the part's number and manufacturer; the supplier's address, phone number and comment information.
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 p_size = [SIZE]
and p_type like '%[TYPE]'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = '[REGION]'
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 = '[REGION]')
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;
This report extracts data from tables part
, supplier
as well as table partsupp
that relates each supplier to their inventory of parts. We start with getting a list of parts of the given size and type in the inventories of all suppliers.
@query(
partsupp
:filter(
(part.size == SIZE) &
contains(part.type_, TYPE)),
SIZE=8, TYPE="BRASS")
part | supplier | availqty | supplycost | comment | |
---|---|---|---|---|---|
1 | drab aquamarine red papaya pale | Supplier#000000009 | 7731 | 65.54 | ronic accounts haggle blithely across the blithely special platelets. furiously final ideas haggle carefully after the fluffily unusual dep |
2 | drab aquamarine red papaya pale | Supplier#000000032 | 8847 | 935.64 | ular, regular requests affix ironically enticing ideas. fur |
3 | drab aquamarine red papaya pale | Supplier#000000055 | 6329 | 148.79 | enticingly final ideas sleep fluffily. blithely final ideas boost fluffily ironic asymptote |
4 | drab aquamarine red papaya pale | Supplier#000000078 | 8054 | 425.78 | the express accounts. even, ironic pinto beans wake furious |
5 | blue gainsboro sky burnished puff | Supplier#000000014 | 8029 | 765.35 | ous packages sleep slyly across the quickly ironic accounts. regular packages thrash above the furiously final theodolites. furi |
6 | blue gainsboro sky burnished puff | Supplier#000000040 | 1481 | 319.99 | coys against the even instructions cajole furiously bold, daring courts. slyly ironic deposits ought to wake blithely regular ideas. slyly regular ideas according to the slyly express ac |
7 | blue gainsboro sky burnished puff | Supplier#000000066 | 718 | 634.58 | furiously slyly ironic packages. pending requests use ruthlessly across the blithely bold deposits. furiously fin |
8 | blue gainsboro sky burnished puff | Supplier#000000012 | 2818 | 237.16 | the carefully special frays. final excuses haggle slyly. furiously regular deposits across t |
9 | plum indian cornflower frosted purple | Supplier#000000073 | 1076 | 844.74 | y ironic instructions. even, final requests are. bold instructions use silent, regular accounts-- slyly final excuses doubt fluffily after the quickly p |
10 | plum indian cornflower frosted purple | Supplier#000000022 | 9272 | 904.83 | ly regular packages. ruthless instructions haggle carefully final, special courts. silently ruthless courts hinder slyly regular packages; regular, regular pac |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
Among all suppliers, we are only interested in those who are situated in the given REGION.
@query(
partsupp
:filter(
(part.size == SIZE) &
contains(part.type_, TYPE) &
(supplier.nation.region.name == REGION)),
SIZE=8, TYPE="BRASS", REGION="EUROPE")
part | supplier | availqty | supplycost | comment | |
---|---|---|---|---|---|
1 | drab aquamarine red papaya pale | Supplier#000000032 | 8847 | 935.64 | ular, regular requests affix ironically enticing ideas. fur |
2 | blue gainsboro sky burnished puff | Supplier#000000040 | 1481 | 319.99 | coys against the even instructions cajole furiously bold, daring courts. slyly ironic deposits ought to wake blithely regular ideas. slyly regular ideas according to the slyly express ac |
3 | blue gainsboro sky burnished puff | Supplier#000000066 | 718 | 634.58 | furiously slyly ironic packages. pending requests use ruthlessly across the blithely bold deposits. furiously fin |
4 | tan ghost cyan salmon goldenrod | Supplier#000000066 | 322 | 104.62 | g the quickly express dolphins. slyly even instructions nag furiously. carefully final ideas are idly above the ironic asymptotes. unusual requests haggle about t |
5 | tan ghost cyan salmon goldenrod | Supplier#000000016 | 6830 | 298.77 | ss orbits. furiously regular deposits haggle blithely carefully final pinto beans. ironic, silent accounts thrash quickly about the accounts. iron |
6 | frosted blue ghost purple coral | Supplier#000000025 | 290 | 774.80 | r pinto beans-- quickly final instructions integrate alongside of the sly, silent th |
7 | burlywood seashell slate white cream | Supplier#000000040 | 9737 | 799.94 | ns cajole idly. deposits are quickly blithely even deposits. quickly regular packages boost carefully. blithely ironic asympto |
8 | burlywood seashell slate white cream | Supplier#000000077 | 9194 | 240.78 | l requests cajole carefully. blithely express gifts |
Some parts are offered by more than one supplier. We can use combinator and_around(part)
to relate each row of the output to all the others that provide the same part.
@query(
partsupp
:filter(
(part.size == SIZE) &
contains(part.type_, TYPE) &
(supplier.nation.region.name == REGION))
:select(part, supplier, supplycost, othersupplycost => and_around(part).supplycost),
SIZE=8, TYPE="BRASS", REGION="EUROPE")
part | supplier | supplycost | othersupplycost | |
---|---|---|---|---|
1 | drab aquamarine red papaya pale | Supplier#000000032 | 935.64 | [935.64] |
2 | blue gainsboro sky burnished puff | Supplier#000000040 | 319.99 | [319.99,634.58] |
3 | blue gainsboro sky burnished puff | Supplier#000000066 | 634.58 | [319.99,634.58] |
4 | tan ghost cyan salmon goldenrod | Supplier#000000066 | 104.62 | [104.62,298.77] |
5 | tan ghost cyan salmon goldenrod | Supplier#000000016 | 298.77 | [104.62,298.77] |
6 | frosted blue ghost purple coral | Supplier#000000025 | 774.80 | [774.80] |
7 | burlywood seashell slate white cream | Supplier#000000040 | 799.94 | [799.94,240.78] |
8 | burlywood seashell slate white cream | Supplier#000000077 | 240.78 | [799.94,240.78] |
Similarly, we can select rows that provide their part at the minimal cost.
@query(
partsupp
:filter(
(part.size == SIZE) &
contains(part.type_, TYPE) &
(supplier.nation.region.name == REGION))
:filter(supplycost == min(and_around(part).supplycost)),
SIZE=8, TYPE="BRASS", REGION="EUROPE")
part | supplier | availqty | supplycost | comment | |
---|---|---|---|---|---|
1 | drab aquamarine red papaya pale | Supplier#000000032 | 8847 | 935.64 | ular, regular requests affix ironically enticing ideas. fur |
2 | blue gainsboro sky burnished puff | Supplier#000000040 | 1481 | 319.99 | coys against the even instructions cajole furiously bold, daring courts. slyly ironic deposits ought to wake blithely regular ideas. slyly regular ideas according to the slyly express ac |
3 | tan ghost cyan salmon goldenrod | Supplier#000000066 | 322 | 104.62 | g the quickly express dolphins. slyly even instructions nag furiously. carefully final ideas are idly above the ironic asymptotes. unusual requests haggle about t |
4 | frosted blue ghost purple coral | Supplier#000000025 | 290 | 774.80 | r pinto beans-- quickly final instructions integrate alongside of the sly, silent th |
5 | burlywood seashell slate white cream | Supplier#000000077 | 9194 | 240.78 | l requests cajole carefully. blithely express gifts |
We have the rows that we need in the requested report. We only need to sort them and show the requested columns.
@query(
partsupp
:filter(
(part.size == SIZE) &
contains(part.type_, TYPE) &
(supplier.nation.region.name == REGION))
:filter(supplycost == min(and_around(part).supplycost))
:sort(
supplier.acctbal:desc,
supplier.nation.name,
part.name,
part.id)
:select(
supplier.acctbal,
supplier.name,
supplier.nation,
part.id,
part.mfgr,
supplier.address,
supplier.phone,
supplier.comment)
:take(100),
SIZE=8, TYPE="BRASS", REGION="EUROPE")
acctbal | name | nation | id | mfgr | address | phone | comment | |
---|---|---|---|---|---|---|---|---|
1 | 9198.31 | Supplier#000000025 | RUSSIA | 1156 | Manufacturer#5 | RCQKONXMFnrodzz6w7fObFVV6CUm2q | 32-431-945-3541 | ely regular deposits. carefully regular sauternes engage furiously above the regular accounts. idly |
2 | 4186.95 | Supplier#000000077 | GERMANY | 1362 | Manufacturer#1 | wVtcr0uH3CyrSiWMLsqnB09Syo,UuZxPMeBghlY | 17-281-345-4863 | the slyly final asymptotes. blithely pending theodoli |
3 | 3556.47 | Supplier#000000032 | UNITED KINGDOM | 248 | Manufacturer#1 | yvoD3TtZSx1skQNCK8agk5bZlZLug | 33-484-637-7873 | usly even depths. quickly ironic theodolites s |
4 | 2455.98 | Supplier#000000066 | UNITED KINGDOM | 805 | Manufacturer#5 | qYdruFJQJYYiKvnNVmYfCVydVB8bcW,AW,U6SOV3 | 33-300-836-9529 | ar requests. express orbits de |
5 | -290.06 | Supplier#000000040 | RUSSIA | 493 | Manufacturer#4 | zyIeWzbbpkTV37vm1nmSGBxSgd2Kp | 32-231-247-6991 | final patterns. accounts haggle idly pas |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 33]
This query retrieves the 10 unshipped orders with the highest value.
The Shipping Priority Query retrieves the shipping priority and potential revenue, defined as the sum of
l_extendedprice * (1-l_discount)
, of the orders having the largest revenue among those that had not been shipped as of a given date. Orders are listed in decreasing order of revenue. If more than 10 unshipped orders exist, only the 10 orders with the largest revenue are listed.
select
l_orderkey,
sum(l_extendedprice*(1-l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = '[SEGMENT]'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '[DATE]'
and l_shipdate > date '[DATE]'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10;
@query(
order
:filter(
(customer.mktsegment == SEGMENT) &
(orderdate < DATE))
:select(
id,
revenue =>
lineitem:filter(shipdate > DATE).(extendedprice*(1-discount)):sum,
orderdate,
shippriority)
:sort(revenue:desc, orderdate)
:take(10),
SEGMENT="BUILDING", DATE=Date("1995-03-15"))
id | revenue | orderdate | shippriority | |
---|---|---|---|---|
1 | 6240 | 245018.10 | 1995-01-28 | 0 |
2 | 5822 | 234486.93 | 1995-03-14 | 0 |
3 | 9974 | 231804.68 | 1995-03-06 | 0 |
4 | 4608 | 197118.76 | 1995-03-13 | 0 |
5 | 4672 | 182178.38 | 1995-03-06 | 0 |
6 | 7197 | 179200.59 | 1995-02-11 | 0 |
7 | 114 | 173955.47 | 1995-03-05 | 0 |
8 | 11089 | 172336.48 | 1995-02-04 | 0 |
9 | 1870 | 170114.81 | 1995-02-21 | 0 |
10 | 7189 | 165761.65 | 1995-03-01 | 0 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 35]
This query determines how well the order priority system is working and gives an assessment of customer satisfaction.
The Order Priority Checking Query counts the number of orders ordered in a given quarter of a given year in which at least one lineitem was received by the customer later than its committed date. The query lists the count of such orders for each order priority sorted in ascending priority order.
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '[DATE]'
and o_orderdate < date '[DATE]' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate)
group by
o_orderpriority
order by
o_orderpriority;
@query(
order
:filter(
DATE <= orderdate < DATE + 3_months &&
any(lineitem.(commitdate < receiptdate)))
:group(orderpriority)
:select(
orderpriority,
order_count => count(order)),
DATE=Date("1993-07-01"))
orderpriority | order_count | |
---|---|---|
1 | 1-URGENT | 78 |
2 | 2-HIGH | 80 |
3 | 3-MEDIUM | 89 |
4 | 4-NOT SPECIFIED | 85 |
5 | 5-LOW | 105 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 36]
This query lists the revenue volume done through local suppliers.
The Local Supplier Volume Query lists for each nation in a region the revenue volume that resulted from lineitem
transactions in which the customer ordering parts and the supplier filling them were both within that nation. The
query is run in order to determine whether to institute local distribution centers in a given region. The query considers only parts ordered in a given year. The query displays the nations and revenue volume in descending order by revenue. Revenue volume for all qualifying lineitems in a particular nation is defined as sum(l_extendedprice * (1 - l_discount))
.
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = '[REGION]'
and o_orderdate >= date '[DATE]'
and o_orderdate < date '[DATE]' + interval '1' year
group by
n_name
order by
revenue desc;
@query(
lineitem
:filter(
DATE <= order.orderdate < DATE + 1_year &&
supplier.nation == order.customer.nation &&
supplier.nation.region.name == REGION)
:group(supplier.nation)
:select(
nation,
revenue => sum(lineitem.(extendedprice*(1-discount))))
:sort(revenue:desc),
REGION="ASIA", DATE=Date("1994-01-01"))
nation | revenue | |
---|---|---|
1 | VIETNAM | 807082.63 |
2 | INDIA | 697063.03 |
3 | INDONESIA | 463882.68 |
4 | CHINA | 440134.00 |
5 | JAPAN | 237479.43 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 38]
This query quantifies the amount of revenue increase that would have resulted from eliminating certain company-wide discounts in a given percentage range in a given year. Asking this type of "what if" query can be used to look for ways to increase revenues.
The Forecasting Revenue Change Query considers all the lineitems shipped in a given year with discounts between
DISCOUNT-0.01 and DISCOUNT+0.01. The query lists the amount by which the total revenue would have
increased if these discounts had been eliminated for lineitems with l_quantity
less than quantity
. Note that the
potential revenue increase is equal to the sum of [l_extendedprice * l_discount]
for all lineitems with discounts and quantities in the qualifying range.
select
sum(l_extendedprice*l_discount) as revenue
from
lineitem
where
l_shipdate >= date '[DATE]'
and l_shipdate < date '[DATE]' + interval '1' year
and l_discount between [DISCOUNT] - 0.01 and [DISCOUNT] + 0.01
and l_quantity < [QUANTITY];
@query(
lineitem
:filter(
DATE <= shipdate < DATE + 1_year &&
DISCOUNT-0.011 <= discount <= DISCOUNT+0.011 &&
quantity < QUANTITY)
:group
:select(
revenue => sum(lineitem.(extendedprice*discount))),
DATE=Date("1994-01-01"), DISCOUNT=0.06, QUANTITY=24)
revenue | |
---|---|
1 | 905922.82 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 39]
This query determines the value of goods shipped between certain nations to help in the re-negotiation of shipping contracts.
The Volume Shipping Query finds, for two given nations, the gross discounted revenues derived from lineitems in which parts were shipped from a supplier in either nation to a customer in the other nation during 1995 and 1996. The query lists the supplier nation, the customer nation, the year, and the revenue from shipments that took place in that year. The query orders the answer by Supplier nation, Customer nation, and year (all ascending).
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from (
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = '[NATION1]' and n2.n_name = '[NATION2]')
or (n1.n_name = '[NATION2]' and n2.n_name = '[NATION1]'))
and l_shipdate between date '1995-01-01' and date '1996-12-31') as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
@query(
lineitem
:define(
supp_nation => supplier.nation.name,
cust_nation => order.customer.nation.name)
:filter(
date("1995-01-01") <= shipdate <= date("1996-12-31") && (
(supp_nation == NATION1 && cust_nation == NATION2) ||
(supp_nation == NATION2 && cust_nation == NATION1)))
:group(
supp_nation,
cust_nation,
year => year(shipdate))
:select(
supp_nation,
cust_nation,
year,
revenue => sum(lineitem.(extendedprice*(1-discount)))),
NATION1="FRANCE", NATION2="GERMANY")
supp_nation | cust_nation | year | revenue | |
---|---|---|---|---|
1 | FRANCE | GERMANY | 1995 | 263047.87 |
2 | FRANCE | GERMANY | 1996 | 154119.14 |
3 | GERMANY | FRANCE | 1995 | 205237.67 |
4 | GERMANY | FRANCE | 1996 | 407967.21 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 41]
This query determines how the market share of a given nation within a given region has changed over two years for a given part type.
The market share for a given nation within a given region is defined as the fraction of the revenue, the sum of
[l_extendedprice * (1-l_discount)]
, from the products of a specified type in that region that was supplied by suppliers from the given nation. The query determines this for the years 1995 and 1996 presented in this order.
select
o_year,
sum(case
when nation = '[NATION]'
then volume
else 0
end) / sum(volume) as mkt_share
from (
select
extract(year from o_orderdate) as o_year,
l_extendedprice * (1-l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = '[REGION]'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = '[TYPE]') as all_nations
group by
o_year
order by
o_year;
@query(
lineitem
:define(
year => year(order.orderdate),
supp_nation => supplier.nation.name,
volume => extendedprice*(1-discount))
:filter(
part.type_ == TYPE &&
order.customer.nation.region.name == REGION &&
1995 <= year <= 1996)
:group(year)
:select(
year,
mkt_share =>
sum(lineitem:filter(supp_nation == NATION).volume) / sum(lineitem.volume)),
NATION="CANADA", REGION="AMERICA", TYPE="ECONOMY ANODIZED STEEL")
year | mkt_share | |
---|---|---|
1 | 1995 | 0.1379404076847995 |
2 | 1996 | 0.2615672520944461 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 43]
This query determines how much profit is made on a given line of parts, broken out by supplier nation and year.
The Product Type Profit Measure Query finds, for each nation and each year, the profit for all parts ordered in that
year that contain a specified substring in their names and that were filled by a supplier in that nation. The profit is defined as the sum of [(l_extendedprice*(1-l_discount)) - (ps_supplycost * l_quantity)]
for all lineitems describing parts in the specified line. The query lists the nations in ascending alphabetical order and, for each nation, the year and profit in descending order by year (most recent first).
select
nation,
o_year,
sum(amount) as sum_profit
from (
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
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 '%[COLOR]%') as profit
group by
nation,
o_year
order by
nation,
o_year desc;
@query(
lineitem
:define(
amount =>
extendedprice*(1-discount) - partsupp.supplycost*quantity)
:filter(contains(part.name, COLOR))
:group(
nation => supplier.nation.name,
year => year(order.orderdate))
:sort(nation, year:desc)
:select(
nation,
year,
sum_profit => sum(lineitem.amount)),
COLOR="green")
nation | year | sum_profit | |
---|---|---|---|
1 | ALGERIA | 1998 | 197990.07 |
2 | ALGERIA | 1997 | 209363.95 |
3 | ALGERIA | 1996 | 508610.09 |
4 | ALGERIA | 1995 | 321224.39 |
5 | ALGERIA | 1994 | 323614.10 |
6 | ALGERIA | 1993 | 429217.36 |
7 | ALGERIA | 1992 | 313931.42 |
8 | ARGENTINA | 1998 | 207703.71 |
9 | ARGENTINA | 1997 | 404879.36 |
10 | ARGENTINA | 1996 | 277287.30 |
⋮ | ⋮ | ⋮ | ⋮ |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 45]
The query identifies customers who might be having problems with the parts that are shipped to them.
The Returned Item Reporting Query finds the top 20 customers, in terms of their effect on lost revenue for a given
quarter, who have returned parts. The query considers only parts that were ordered in the specified quarter. The
query lists the customer's name, address, nation, phone number, account balance, comment information and revenue
lost. The customers are listed in descending order of lost revenue. Revenue lost is defined as
sum(l_extendedprice*(1-l_discount))
for all qualifying lineitems.
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '[DATE]'
and o_orderdate < date '[DATE]' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
limit 20;
@query(
customer
:define(
returned =>
order:filter(DATE <= orderdate < DATE+3_months).lineitem:filter(returnflag == "R"))
:select(
id,
name,
revenue => sum(returned.(extendedprice*(1-discount))),
acctbal,
nation,
address,
phone,
comment)
:sort(revenue:desc)
:take(20),
DATE=Date("1993-10-01"))
id | name | revenue | acctbal | nation | address | phone | comment | |
---|---|---|---|---|---|---|---|---|
1 | 544 | Customer#000000544 | 391580.05 | 4974.68 | ETHIOPIA | Jv7vcm,oE,HEyxekXKia1V5H1up23 | 15-572-651-1323 | bout the packages integrate above the regular instructions. regular ideas hinder s |
2 | 1105 | Customer#000001105 | 375872.30 | 9491.46 | RUSSIA | cZhhOUzv6,Vbaa2bFT | 32-885-298-6750 | y final packages. furiously ironic packages was. fluffily ironic instructions integrate |
3 | 961 | Customer#000000961 | 372764.61 | 6963.68 | JAPAN | 5,81YDLFuRR47KKzv8GXdmi3zyP37PlPn | 22-989-463-6089 | e final requests: busily final accounts believe a |
4 | 266 | Customer#000000266 | 347106.76 | 5481.00 | ALGERIA | VSIEruiMdDvjDaTQxkuK60Yw3AGxO | 10-474-243-3974 | ccounts. quickly ironic excuses after the regular foxes wake along the ironic, fina |
5 | 683 | Customer#000000683 | 328973.73 | 9120.93 | FRANCE | G0, q8c6vBykpiLvcuSJLYvqE | 16-566-251-5446 | somas use-- slyly express foxes wake quickly blithely iro |
6 | 415 | Customer#000000415 | 327104.67 | 2317.93 | UNITED KINGDOM | 334jCRiUb,gx3 | 33-346-876-2972 | egular deposits. blithely ironic inst |
7 | 746 | Customer#000000746 | 311870.96 | 1164.46 | SAUDI ARABIA | JOKj8N2QKUm8Gi,F4qX0fLVy | 30-154-354-9928 | final theodolites. final pinto beans haggle-- furiously |
8 | 421 | Customer#000000421 | 311164.57 | 7073.17 | JORDAN | it3mUlkZAe9J8gmy | 23-918-228-2560 | lithely final deposits haggle furiously above the |
9 | 643 | Customer#000000643 | 281356.65 | 5184.70 | ALGERIA | 9T 2avhfyF PQ | 10-978-597-2747 | fily along the quickly ironic ideas. final, final |
10 | 1051 | Customer#000001051 | 275867.46 | 9776.39 | BRAZIL | iHS,UFudVOOe | 12-869-221-1428 | cuses boost furiously silent deposits. quickly silent requests integrate quickly bold asymptotes; slyly regular ide |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 47]
This query finds the most important subset of suppliers' stock in a given nation.
The Important Stock Identification Query finds, from scanning the available stock of suppliers in a given nation, all the parts that represent a significant percentage of the total value of all available parts. The query displays the part number and the value of those parts in descending order of value.
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 = '[NATION]'
group by
ps_partkey
having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * [FRACTION]
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = '[NATION]')
order by
value desc;
@query(
partsupp
:filter(supplier.nation.name == NATION)
:group(part)
:define(value => sum(partsupp.(supplycost * availqty)))
:filter(value > sum(and_around.value)*FRACTION)
:select(part.id, value)
:sort(value:desc),
NATION="GERMANY", FRACTION=0.0001)
id | value | |
---|---|---|
1 | 1083 | 13092535.78 |
2 | 1356 | 11542206.53 |
3 | 716 | 9945808.42 |
4 | 448 | 9941036.40 |
5 | 111 | 9382317.55 |
6 | 1454 | 9081368.05 |
7 | 1218 | 8999420.40 |
8 | 324 | 8901344.93 |
9 | 1312 | 8834080.10 |
10 | 1472 | 8609013.30 |
⋮ | ⋮ | ⋮ |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 49]
This query determines whether selecting less expensive modes of shipping is negatively affecting the critical-priority orders by causing more parts to be received by customers after the committed date.
The Shipping Modes and Order Priority Query counts, by ship mode, for lineitems actually received by customers in
a given year, the number of lineitems belonging to orders for which the l_receiptdate
exceeds the l_commitdate
for two different specified ship modes. Only lineitems that were actually shipped before the l_commitdate
are considered. The late lineitems are partitioned into two groups, those with priority URGENT or HIGH, and those with a
priority other than URGENT or HIGH.
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 ('[SHIPMODE1]', '[SHIPMODE2]')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '[DATE]'
and l_receiptdate < date '[DATE]' + interval '1' year
group by
l_shipmode
order by
l_shipmode;
@query(
lineitem
:define(
high => order.orderpriority in ["1-URGENT", "2-HIGH"])
:filter(
shipmode in SHIPMODES &&
shipdate < commitdate < receiptdate &&
DATE <= receiptdate < DATE + 1_year)
:group(shipmode)
:select(
shipmode,
high_line_count => count(lineitem:filter(high)),
low_line_count => count(lineitem:filter(!high))),
SHIPMODES=["MAIL", "SHIP"], DATE=Date("1994-01-01"))
shipmode | high_line_count | low_line_count | |
---|---|---|---|
1 | 45 | 67 | |
2 | SHIP | 45 | 69 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 51]
This query seeks relationships between customers and the size of their orders.
This query determines the distribution of customers by the number of orders they have made, including customers who have no record of orders, past or present. It counts and reports how many customers have no orders, how many have 1, 2, 3, etc. A check is made to ensure that the orders counted do not fall into one of several special categories of orders. Special categories are identified in the order comment column by looking for a particular pattern.
select
c_count,
count(*) as custdist
from (
select
c_custkey,
count(o_orderkey)
from
customer left outer join orders
on c_custkey = o_custkey and o_comment not like '%[WORD1]%[WORD2]%'
group by
c_custkey) as c_orders (c_custkey, c_count)
group by
c_count
order by
custdist desc,
c_count desc;
@query(
customer
:group(
c_count => count(order:filter(!contains(comment, WORDS))))
:select(
c_count,
custdist => count(customer))
:sort(
custdist:desc,
c_count:desc),
WORDS=r".*special.*requests.*")
c_count | custdist | |
---|---|---|
1 | 0 | 400 |
2 | 11 | 57 |
3 | 12 | 55 |
4 | 10 | 53 |
5 | 8 | 51 |
6 | 9 | 48 |
7 | 17 | 43 |
8 | 13 | 39 |
9 | 22 | 37 |
10 | 21 | 37 |
⋮ | ⋮ | ⋮ |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 52]
This query monitors the market response to a promotion such as TV advertisements or a special campaign.
The Promotion Effect Query determines what percentage of the revenue in a given year and month was derived from
promotional parts. The query considers only parts actually shipped in that month and gives the percentage. Revenue
is defined as (l_extendedprice * (1-l_discount))
.
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 >= date '[DATE]'
and l_shipdate < date '[DATE]' + interval '1' month;
@query(
lineitem
:define(
volume => extendedprice*(1-discount),
promo => startswith(part.type_, "PROMO"))
:filter(DATE <= shipdate < DATE + 1_month)
:group
:select(
promo_revenue =>
100*sum(lineitem:filter(promo).volume)/sum(lineitem.volume)),
DATE=Date("1995-09-01"))
promo_revenue | |
---|---|
1 | 12.409568669150199 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 53]
This query determines the top supplier so it can be rewarded, given more business, or identified for special recognition.
The Top Supplier Query finds the supplier who contributed the most to the overall revenue for parts shipped during a given quarter of a given year. In case of a tie, the query lists all suppliers whose contribution was equal to the maximum, presented in supplier number order.
create view revenue (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= date '[DATE]'
and l_shipdate < date '[DATE]' + interval '3' month
group by
l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue)
order by
s_suppkey;
drop view revenue;
@query(
supplier
:define(
total_revenue =>
sum(lineitem:filter(DATE <= shipdate < DATE+3_months).(extendedprice*(1-discount))))
:first(total_revenue)
:select(
id,
name,
address,
phone,
total_revenue),
DATE=Date("1996-01-01"))
id | name | address | phone | total_revenue | |
---|---|---|---|---|---|
1 | 17 | Supplier#000000017 | c2d,ESHRSkK3WYnxpgw6aOqN0q | 29-601-884-9219 | 1113801.53 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 55]
This query finds out how many suppliers can supply parts with given attributes. It might be used, for example, to determine whether there is a sufficient number of suppliers for heavily ordered parts.
The Parts/Supplier Relationship Query counts the number of suppliers who can supply parts that satisfy a particular customer's requirements. The customer is interested in parts of eight different sizes as long as they are not of a given type, not of a given brand, and not from a supplier who has had complaints registered at the Better Business Bureau. Results must be presented in descending count and ascending brand, type, and size.
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> '[BRAND]'
and p_type not like '[TYPE]%'
and p_size in ([SIZE1], [SIZE2], [SIZE3], [SIZE4], [SIZE5], [SIZE6], [SIZE7], [SIZE8])
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;
@query(
partsupp
:filter(
part.brand != BRAND &&
!startswith(part.type_, TYPE) &&
part.size in SIZES &&
!contains(supplier.comment, r".*Customer.*Complaints.*"))
:group(
part.brand,
part.type_,
part.size)
:select(
brand,
type_,
size,
supplier_cnt => count(unique(partsupp.supplier)))
:sort(supplier_cnt:desc),
BRAND="Brand#45", TYPE="MEDIUM POLISHED", SIZES=[49, 14, 23, 45, 19, 3, 36, 9])
brand | type_ | size | supplier_cnt | |
---|---|---|---|---|
1 | Brand#14 | PROMO BRUSHED STEEL | 9 | 8 |
2 | Brand#35 | SMALL POLISHED COPPER | 14 | 8 |
3 | Brand#11 | LARGE PLATED TIN | 23 | 4 |
4 | Brand#11 | MEDIUM ANODIZED BRASS | 45 | 4 |
5 | Brand#11 | MEDIUM BRUSHED BRASS | 45 | 4 |
6 | Brand#11 | PROMO ANODIZED BRASS | 49 | 4 |
7 | Brand#11 | PROMO ANODIZED TIN | 45 | 4 |
8 | Brand#11 | PROMO BURNISHED BRASS | 36 | 4 |
9 | Brand#11 | SMALL ANODIZED TIN | 45 | 4 |
10 | Brand#11 | SMALL PLATED COPPER | 45 | 4 |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 57]
This query determines how much average yearly revenue would be lost if orders were no longer filled for small quantities of certain parts. This may reduce overhead expenses by concentrating sales on larger shipments.
The Small-Quantity-Order Revenue Query considers parts of a given brand and with a given container type and determines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7-year database. What would be the average yearly gross (undiscounted) loss in revenue if orders for these parts with a quantity of less than 20% of this average were no longer taken?
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = '[BRAND]'
and p_container = '[CONTAINER]'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey);
@query(
lineitem
:filter(part.brand == BRAND && part.container == CONTAINER)
:filter(quantity < 0.2 * mean(and_around(part).quantity))
:group
:select(
avg_yearly => sum(lineitem.extendedprice)/7),
BRAND="Brand#23", CONTAINER="MED JAR")
avg_yearly | |
---|---|
1 | 1217.62 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 58]
The Large Volume Customer Query ranks customers based on their having placed a large quantity order. Large quantity orders are defined as those orders whose total quantity is above a certain level.
The Large Volume Customer Query finds a list of the top 100 customers who have ever placed large quantity orders. The query lists the customer name, customer key, the order key, date and total price and the quantity for the order.
select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
customer,
orders,
lineitem
where
o_orderkey in (
select
l_orderkey
from
lineitem
group by
l_orderkey
having
sum(l_quantity) > [QUANTITY])
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 100;
@query(
order
:select(
customer.name,
custkey => customer.id,
orderkey => id,
orderdate,
totalprice,
quantity => sum(lineitem.quantity))
:filter(quantity > QUANTITY)
:sort(
totalprice:desc,
orderdate)
:take(100),
QUANTITY=300)
name | custkey | orderkey | orderdate | totalprice | quantity | |
---|---|---|---|---|---|---|
1 | Customer#000000142 | 142 | 1722 | 1997-04-09 | 423609.13 | 303 |
2 | Customer#000000533 | 533 | 7294 | 1995-10-21 | 372230.14 | 305 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 60]
The Discounted Revenue Query reports the gross discounted revenue attributed to the sale of selected parts handled in a particular manner. This query is an example of code such as might be produced programmatically by a data mining tool.
The Discounted Revenue query finds the gross discounted revenue for all orders for three different types of parts that were shipped by air and delivered in person. Parts are selected based on the combination of specific brands, a list of containers, and a range of sizes.
select
sum(l_extendedprice * (1 - l_discount) ) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = '[BRAND1]'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = '[BRAND2]'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = '[BRAND3]'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= [QUANTITY3] and l_quantity <= [QUANTITY3] + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
@query(
lineitem
:filter(
(
part.brand == BRAND1 &&
part.container in ["SM CASE", "SM BOX", "SM PACK", "SM PKG"] &&
QUANTITY1 <= quantity <= QUANTITY1+10 &&
1 <= part.size <= 5 &&
shipmode in ["AIR", "AIR REG"] &&
shipinstruct == "DELIVER IN PERSON"
)
||
(
part.brand == BRAND2 &&
part.container in ["MED BAG", "MED BOX", "MED PKG", "MED PACK"] &&
QUANTITY2 <= quantity <= QUANTITY2+10 &&
1 <= part.size <= 10 &&
shipmode in ["AIR", "AIR REG"] &&
shipinstruct == "DELIVER IN PERSON"
)
||
(
part.brand == BRAND3 &&
part.container in ["LG CASE", "LG BOX", "LG PACK", "LG PKG"] &&
QUANTITY3 <= quantity <= QUANTITY3+10 &&
1 <= part.size <= 15 &&
shipmode in ["AIR", "AIR REG"] &&
shipinstruct == "DELIVER IN PERSON"
))
:group
:select(
revenue => sum(lineitem.(extendedprice*(1-discount)))),
QUANTITY1=1, QUANTITY2=10, QUANTITY3=20,
BRAND1="Brand#53", BRAND2="Brand#35", BRAND3="Brand#32")
revenue | |
---|---|
1 | 40908.53 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 62]
The Potential Part Promotion Query identifies suppliers in a particular nation having selected parts that may be candidates for a promotional offer.
The Potential Part Promotion query identifies suppliers who have an excess of a given part available; an excess is defined to be more than 50% of the parts like the given part that the supplier shipped in a given year for a given nation. Only parts whose names share a certain naming convention are considered.
select
s_name,
s_address
from
supplier,
nation
where
s_suppkey in (
select
ps_suppkey
from
partsupp
where
ps_partkey in (
select
p_partkey
from
part
where
p_name like '[COLOR]%')
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date('[DATE]')
and l_shipdate < date('[DATE]') + interval '1' year))
and s_nationkey = n_nationkey
and n_name = '[NATION]'
order by
s_name;
@query(
partsupp
:define(
expectedqty =>
sum(lineitem:filter(DATE <= shipdate < DATE + 1_year).quantity))
:filter(
supplier.nation.name == NATION &&
startswith(part.name, COLOR) &&
availqty > 0.5*expectedqty)
:group(supplier)
:select(
supplier.name,
supplier.address)
:sort(name),
COLOR="forest", DATE=Date("1994-01-01"), NATION="CANADA")
name | address | |
---|---|---|
1 | Supplier#000000020 | iybAE,RmTymrZVYaFZva2SH,j |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 64]
This query identifies certain suppliers who were not able to ship required parts in a timely manner.
The Suppliers Who Kept Orders Waiting query identifies suppliers, for a given nation, whose product was part of a multi-supplier order (with current status of 'F') where they were the only supplier who failed to meet the committed delivery date.
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 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)
and s_nationkey = n_nationkey
and n_name = '[NATION]'
group by
s_name
order by
numwait desc,
s_name
limit 100;
@query(
lineitem
:filter((
order.orderstatus == "F" &&
supplier.nation.name == NATION &&
receiptdate > commitdate &&
any(order.lineitem.supplier != SUPP) &&
!any(order.lineitem.(supplier != SUPP && receiptdate > commitdate)))
:given(SUPP => supplier))
:group(supplier)
:select(
supplier.name,
numwait => count(lineitem))
:sort(
numwait:desc,
name)
:take(100),
NATION="SAUDI ARABIA")
name | numwait | |
---|---|---|
1 | Supplier#000000074 | 10 |
[TPC Benchmark (TM) H Standard Specification Revision 2.17.1; Page 66]
The Global Sales Opportunity Query identifies geographies where there are customers who may be likely to make a purchase.
This query counts how many customers within a specific range of country codes have not placed orders for 7 years but who have a greater than average “positive” account balance. It also reflects the magnitude of that balance. Country code is defined as the first two characters of c_phone.
select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from (
select
substring(c_phone from 1 for 2) as cntrycode,
c_acctbal
from
customer
where
substring(c_phone from 1 for 2) in
('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]')
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring (c_phone from 1 for 2) in
('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]'))
and not exists (
select
*
from
orders
where
o_custkey = c_custkey)) as custsale
group by
cntrycode
order by
cntrycode;
@query(
customer
:define(
cntrycode => substr(phone, 1, 2))
:filter(
cntrycode in IS &&
acctbal > 0_usd)
:filter(
!exists(order) &&
acctbal > mean(and_around.acctbal))
:group(cntrycode)
:select(
cntrycode,
numcust => count(customer),
totacctbal => sum(customer.acctbal)),
IS=["13", "31", "23", "29", "30", "18", "17"])
cntrycode | numcust | totacctbal | |
---|---|---|---|
1 | 13 | 8 | 59306.91 |
2 | 17 | 6 | 49853.61 |
3 | 18 | 11 | 89404.25 |
4 | 23 | 3 | 25939.81 |
5 | 29 | 11 | 88722.85 |
6 | 30 | 13 | 92703.39 |
7 | 31 | 9 | 69434.60 |