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
|
--!syntax_pg
--TPC-DS Q35
-- start query 1 in stream 0 using template ../query_templates/query35.tpl
select
ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
count(*) cnt1,
avg(cd_dep_count) a1,
max(cd_dep_count) x1,
sum(cd_dep_count) s1,
cd_dep_employed_count,
count(*) cnt2,
avg(cd_dep_employed_count) a2,
max(cd_dep_employed_count) x2,
sum(cd_dep_employed_count) s2,
cd_dep_college_count,
count(*) cnt3,
avg(cd_dep_college_count) a3,
max(cd_dep_college_count) x3,
sum(cd_dep_college_count) s3
from
plato.customer c,plato.customer_address ca,plato.customer_demographics
where
c.c_current_addr_sk = ca.ca_address_sk and
cd_demo_sk = c.c_current_cdemo_sk and
exists (select *
from plato.store_sales,plato.date_dim
where c.c_customer_sk = ss_customer_sk and
ss_sold_date_sk = d_date_sk and
d_year = 1999 and
d_qoy < 4) and
(exists (select *
from plato.web_sales,plato.date_dim
where c.c_customer_sk = ws_bill_customer_sk and
ws_sold_date_sk = d_date_sk and
d_year = 1999 and
d_qoy < 4) or
exists (select *
from plato.catalog_sales,plato.date_dim
where c.c_customer_sk = cs_ship_customer_sk and
cs_sold_date_sk = d_date_sk and
d_year = 1999 and
d_qoy < 4))
group by ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
order by ca_state,
cd_gender,
cd_marital_status,
cd_dep_count,
cd_dep_employed_count,
cd_dep_college_count
limit 100;
-- end query 1 in stream 0 using template ../query_templates/query35.tpl
|
