blob: 3272f866d6c9a9e0a02b439ffcf9a73b3274dc8d (
plain) (
blame)
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
|
-- TPC-H/TPC-R Potential Part Promotion Query (Q20)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
$border = Date("1993-01-01");
$threshold = (
select
0.5 * sum(l_quantity) as threshold,
l_partkey as l_partkey,
l_suppkey as l_suppkey
from
plato.lineitem
where
cast(l_shipdate as timestamp) >= $border
and cast(l_shipdate as timestamp) < ($border + Interval("P365D"))
group by
l_partkey, l_suppkey
);
$parts = (
select
p_partkey
from
plato.part
where
StartsWith(p_name, 'maroon')
);
$join1 = (
select
ps.ps_suppkey as ps_suppkey,
ps.ps_availqty as ps_availqty,
ps.ps_partkey as ps_partkey
from
plato.partsupp as ps
join any
$parts as p
on
ps.ps_partkey = p.p_partkey
);
$join2 = (
select
distinct(j.ps_suppkey) as ps_suppkey
from
$join1 as j
join any
$threshold as t
on
j.ps_partkey = t.l_partkey and j.ps_suppkey = t.l_suppkey
where
j.ps_availqty > t.threshold
);
$join3 = (
select
j.ps_suppkey as ps_suppkey,
s.s_name as s_name,
s.s_address as s_address,
s.s_nationkey as s_nationkey
from
$join2 as j
join any
plato.supplier as s
on
j.ps_suppkey = s.s_suppkey
);
select
j.s_name as s_name,
j.s_address as s_address
from
$join3 as j
join
plato.nation as n
on
j.s_nationkey = n.n_nationkey
where
n.n_name = 'VIETNAM'
order by
s_name;
|
