| ==== |
| ---- QUERY: TPCH-Q7 |
| # Q7 - Volume Shipping Query |
| 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 |
| customer c, |
| c.c_orders o, |
| o.o_lineitems l, |
| supplier s, |
| region.r_nations n1, |
| region.r_nations n2 |
| where |
| s_suppkey = l_suppkey |
| and s_nationkey = n1.n_nationkey |
| and c_nationkey = n2.n_nationkey |
| and ( |
| (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY') |
| or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE') |
| ) |
| and l_shipdate between '1995-01-01' and '1996-12-31' |
| ) as shipping |
| group by |
| supp_nation, |
| cust_nation, |
| l_year |
| order by |
| supp_nation, |
| cust_nation, |
| l_year |
| ---- RESULTS |
| 'FRANCE','GERMANY',1995,54639732.7336 |
| 'FRANCE','GERMANY',1996,54633083.3076 |
| 'GERMANY','FRANCE',1995,52531746.6697 |
| 'GERMANY','FRANCE',1996,52520549.0224 |
| ---- TYPES |
| STRING, STRING, INT, DECIMAL |
| ==== |
