-
Notifications
You must be signed in to change notification settings - Fork 32
/
Copy pathtpch.sql
144 lines (127 loc) · 5.7 KB
/
tpch.sql
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
create table lineitem(
l_orderkey integer,
l_partkey integer,
l_suppkey integer,
l_linenumber integer,
l_quantity real,
l_extendedprice real,
l_discount real,
l_tax real,
l_returnflag char,
l_linestatus char,
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(25),
l_shipmode char(10),
l_comment char(44),
l_dummy char(1));
create index lineitem_order_fk on lineitem(l_orderkey);
copy lineitem from 'lineitem.tbl' delimiter '|' csv;
create view lineitems as select
l_orderkey,
l_returnflag,
l_linestatus,
l_quantity,
l_extendedprice,
l_discount,
l_tax,
l_shipdate
from
lineitem;
\timing
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
where
l_shipdate <= cast('1998-12-01' as date)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
select cs_cut,sum_qty,sum_base_price,sum_disc_price,sum_charge,sum_qty/count_order as avg_qty,sum_base_price/count_order as avg_price,count_order
from
(select cs_cut(group_by,'i1i1'),agg_val as sum_qty from
(select (cs_project_agg(cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_quantity),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag||l_linestatus)))).*
from lineitems_get()) agg) q1
natural join
(select cs_cut(group_by,'i1i1'),agg_val as sum_base_price from
(select (cs_project_agg(cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_extendedprice),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag||l_linestatus)))).*
from lineitems_get()) agg) q2
natural join
(select cs_cut(group_by,'i1i1'),agg_val as sum_disc_price from
(select (cs_project_agg(cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_extendedprice*(-l_discount+1)),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag||l_linestatus)))).*
from lineitems_get()) agg) q3
natural join
(select cs_cut(group_by,'i1i1'),agg_val as sum_charge from
(select (cs_project_agg(cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_extendedprice*(-l_discount+1)*(l_tax+1)),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag||l_linestatus)))).*
from lineitems_get()) agg) q4
natural join
(select cs_cut(group_by,'i1i1'),agg_val as avg_disc from
(select (cs_project_agg(cs_hash_avg(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_discount),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag||l_linestatus)))).*
from lineitems_get()) agg) q5
natural join
(select cs_cut(group_by,'i1i1'),agg_val as count_order from
(select (cs_project_agg(cs_hash_count(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag||l_linestatus)))).*
from lineitems_get()) agg) q6;
select cs_cut(group_by,'i1i1'),agg_val as sum_charge from
(select (cs_project_agg(cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_extendedprice*(-l_discount+1)*(l_tax+1)),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag||l_linestatus)))).*
from lineitems_get()) agg;
select
l_returnflag,
l_linestatus,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge
from
lineitem
where
l_shipdate <= cast('1998-12-01' as date)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
.* agg;
select cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_quantity),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as sum_qty,
cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_extendedprice),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as sum_base_price,
cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_extendedprice*(1-l_discount)),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as sum_disc_price,
cs_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_extendedprice*(1-l_discount)*(1+l_tax)),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as sum_charge,
cs_hash_avg(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_quantity),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as avg_qty,
cs_hash_avg(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_price),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as avg_price,
cs_hash_avg(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_discount),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as avg_discount,
lineitems_hash_sum(cs_filter(l_shipdate <= cast('1998-12-01' as date), l_quantity),
cs_filter(l_shipdate <= cast('1998-12-01' as date), l_returnflag*256 + l_linestatus)) as sum_qty,
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
f