blob: c775d585bb6068b53c8ec0c65afa443e7a8631ef (
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
|
-- TPC-H/TPC-R Small-Quantity-Order Revenue Query (Q17)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
$threshold = (
select
0.2 * avg(l_quantity) as threshold,
l_partkey
from
plato.lineitem
group by
l_partkey
);
$join1 = (
select
p.p_partkey as p_partkey,
l.l_extendedprice as l_extendedprice,
l.l_quantity as l_quantity
from
plato.lineitem as l
join
plato.part as p
on
p.p_partkey = l.l_partkey
where
p.p_brand = 'Brand#35'
and p.p_container = 'LG DRUM'
);
select
sum(j.l_extendedprice) / 7.0 as avg_yearly
from
$join1 as j
join
$threshold as t
on
j.p_partkey = t.l_partkey
where
j.l_quantity < t.threshold;
|
