1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
-- TPC-H/TPC-R Volume Shipping Query (Q7)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
$n = select n_name, n_nationkey from plato.nation as n
where n_name = 'PERU' or n_name = 'MOZAMBIQUE';
$l = select
l_orderkey, l_suppkey,
DateTime::GetYear(cast(l_shipdate as timestamp)) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
plato.lineitem as l
where
cast(cast(l.l_shipdate as Timestamp) as Date)
between Date('1995-01-01') and Date('1996-12-31');
$j1 = select
n_name as supp_nation,
s_suppkey
from
plato.supplier as supplier
join
$n as n1
on
supplier.s_nationkey = n1.n_nationkey;
$j2 = select
n_name as cust_nation,
c_custkey
from
plato.customer as customer
join
$n as n2
on
customer.c_nationkey = n2.n_nationkey;
$j3 = select
cust_nation, o_orderkey
from
plato.orders as orders
join
$j2 as customer
on
orders.o_custkey = customer.c_custkey;
$j4 = select
cust_nation,
l_orderkey, l_suppkey,
l_year,
volume
from
$l as lineitem
join
$j3 as orders
on
lineitem.l_orderkey = orders.o_orderkey;
$j5 = select
supp_nation, cust_nation,
l_year, volume
from
$j4 as lineitem
join
$j1 as supplier
on
lineitem.l_suppkey = supplier.s_suppkey
where (supp_nation = 'PERU' and cust_nation = 'MOZAMBIQUE')
OR (supp_nation = 'MOZAMBIQUE' and cust_nation = 'PERU');
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from
$j5 as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
|
