blob: 2a80fb61289702b304155390cffb933cb53402eb (
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
|
-- TPC-H/TPC-R Important Stock Identification Query (Q11)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
PRAGMA DisableSimpleColumns;
$join1 = (
select
ps.ps_partkey as ps_partkey,
ps.ps_supplycost as ps_supplycost,
ps.ps_availqty as ps_availqty,
s.s_nationkey as s_nationkey
from
plato.partsupp as ps
join
plato.supplier as s
on
ps.ps_suppkey = s.s_suppkey
);
$join2 = (
select
j.ps_partkey as ps_partkey,
j.ps_supplycost as ps_supplycost,
j.ps_availqty as ps_availqty,
j.s_nationkey as s_nationkey
from
$join1 as j
join
plato.nation as n
on
n.n_nationkey = j.s_nationkey
where
n.n_name = 'CANADA'
);
$threshold = (
select
sum(ps_supplycost * ps_availqty) * 0.0001000000 as threshold
from
$join2
);
$values = (
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
$join2
group by
ps_partkey
);
select
v.ps_partkey as ps_partkey,
v.value as value
from
$values as v
cross join
$threshold as t
where
v.value > t.threshold
order by
value desc;
|
