-
Notifications
You must be signed in to change notification settings - Fork 2
/
q7
41 lines (41 loc) · 877 Bytes
/
q7
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
spark.sql(
"""select
| supp_nation,
| cust_nation,
| l_year,
| sum(volume) as revenue
| from
| (
| select
| n1.n_name as supp_nation,
| n2.n_name as cust_nation,
| year(l_shipdate) as l_year,
| l_extendedprice * (1 - l_discount) as volume
| from
| supplier,
| lineitem,
| orders,
| customer,
| nation n1,
| nation n2
| where
| s_suppkey = l_suppkey
| and o_orderkey = l_orderkey
| and c_custkey = o_custkey
| and s_nationkey = n1.n_nationkey
| and c_nationkey = n2.n_nationkey
| and (
| (n1.n_name = 'JAPAN' and n2.n_name = 'INDIA')
| or (n1.n_name = 'INDIA' and n2.n_name = 'JAPAN')
| )
| and l_shipdate between date '1995-01-01' and date '1996-12-31'
| ) as shipping
| group by
| supp_nation,
| cust_nation,
| l_year
| order by
| supp_nation,
| cust_nation,
| l_year
""".stripMargin).show