blob: 153b72797b8192093beee3e32d51ba1c95752e78 (
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
|
-- TPC-H/TPC-R Parts/Supplier Relationship Query (Q16)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
$join = (
select
ps.ps_suppkey as ps_suppkey,
ps.ps_partkey as ps_partkey
from
plato.partsupp as ps
left join
plato.supplier as w
on
w.s_suppkey = ps.ps_suppkey
where not (s_comment like "%Customer%Complaints%")
);
select
p.p_brand as p_brand,
p.p_type as p_type,
p.p_size as p_size,
count(distinct j.ps_suppkey) as supplier_cnt
from
$join as j
join
plato.part as p
on
p.p_partkey = j.ps_partkey
where
p.p_brand <> 'Brand#33'
and (not StartsWith(p.p_type, 'PROMO POLISHED'))
and (p.p_size = 20 or p.p_size = 27 or p.p_size = 11 or p.p_size = 45 or p.p_size = 40 or p.p_size = 41 or p.p_size = 34 or p.p_size = 36)
group by
p.p_brand,
p.p_type,
p.p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
;
|
