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
|
# name: test/sql/tpch/tpch_grouping_sets.test_slow
# description: Test TPC-H SF0.01 Q1 with unions
# group: [tpch]
require tpch
statement ok
PRAGMA threads=4
statement ok
PRAGMA verify_parallelism
statement ok
CALL dbgen(sf=0.1);
query IIIIIIIIII
SELECT
l_returnflag,
l_linestatus,
sum(l_quantity) AS sum_qty,
sum(l_extendedprice) AS sum_base_price,
sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
avg(l_quantity) AS avg_qty,
avg(l_extendedprice) AS avg_price,
avg(l_discount) AS avg_disc,
count(*) AS count_order
FROM
lineitem
GROUP BY
GROUPING SETS ((l_returnflag, l_linestatus), (l_returnflag), (l_linestatus), ())
ORDER BY
l_returnflag NULLS LAST,
l_linestatus NULLS LAST;
----
A F 3774200 5320753880.69 5054096266.6828 5256751331.449234 25.5375871168549970 36002.123829014142 0.05014459706340077136 147790
A NULL 3774200 5320753880.69 5054096266.6828 5256751331.449234 25.5375871168549970 36002.123829014142 0.05014459706340077136 147790
N F 95257 133737795.84 127132372.6512 132286291.229445 25.3006640106241700 35521.326916334661 0.04939442231075697211 3765
N O 7679822 10823487077.24 10282025059.1390 10693158047.350890 25.5384548876680988 35992.388423761955 0.05008945317176339137 300716
N NULL 7775079 10957224873.08 10409157431.7902 10825444338.580335 25.5355145312843823 35986.563605216746 0.05008085890416807617 304481
R F 3785523 5337950526.47 5071818532.9420 5274405503.049367 25.5259438574251017 35994.029214030924 0.04998927856184381764 148301
R NULL 3785523 5337950526.47 5071818532.9420 5274405503.049367 25.5259438574251017 35994.029214030924 0.04998927856184381764 148301
NULL F 7654980 10792442203.00 10253047172.2760 10663443125.728046 25.5288538498479270 35992.083543434182 0.05005836134677978763 299856
NULL O 7679822 10823487077.24 10282025059.1390 10693158047.350890 25.5384548876680988 35992.388423761955 0.05008945317176339137 300716
NULL NULL 15334802 21615929280.24 20535072231.4150 21356601173.078936 25.5336612429483892 35992.236201887534 0.05007392952052376734 600572
|
