blob: d8425b0b4a80d67d2296e5bd056c422926cf0135 (
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
|
-- TPC-H/TPC-R Shipping Modes and Order Priority Query (Q12)
-- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
-- using 1680793381 as a seed to the RNG
$join = (
select
l.l_shipmode as l_shipmode,
o.o_orderpriority as o_orderpriority,
l.l_commitdate as l_commitdate,
l.l_shipdate as l_shipdate,
l.l_receiptdate as l_receiptdate
from
plato.orders as o
join plato.lineitem as l
on o.o_orderkey == l.l_orderkey
);
$border = Date("1994-01-01");
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from $join
where
(l_shipmode = 'MAIL' or l_shipmode = 'TRUCK')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and cast(l_receiptdate as timestamp) >= $border
and cast(l_receiptdate as timestamp) < ($border + Interval("P365D"))
group by
l_shipmode
order by
l_shipmode;
|
