电商交易项目案例
–字段含义–
Sdate定义了日期的分类，将每天分别赋予所属的月份、星期、季度等属性，
字段分别为日期、年月、年、月、日、周几、第几周、季度、旬、半月；
Stock定义了订单表头，字段分别为订单号、交易位置、交易日期；
StockDetail文件定义了订单明细，该表和Stock以交易号进行关联，
字段分别为订单号、行号、货品、数量、价格、金额；

–创建sdate表–
CREATE TABLE sdate(
dateID string, –日期
theyearmonth string,–年月
theyear string,–年
themonth string,–月
thedate string,–日
theweek string,–周几
theweeks string,–第几周
thequot string,–季度
thetenday string,–旬
thehalfmonth string –半月
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’ ;

–创建stock表–
CREATE TABLE stock(
ordernumber string,–订单号
locationid string,–交易位置
dateID string –日期
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’ ;

–创建stockdetail表–
CREATE TABLE stockdetail(
ordernumber string,–订单号
rownum int,–行号
itemid string, –货品
qty int, –数量
price int, –价格
amount int –总金额
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’ ;

创建表，导入数据
load data local inpath ‘/home/lan/sdate.txt’
overwrite into table sdate;

load data local inpath ‘/home/lan/stock.txt’
overwrite into table stock;

load data local inpath ‘/home/lan/stockdetail.txt’
overwrite into table stockdetail;

1、计算所有订单每年的总金额
算法分析：
要计算所有订单每年的总金额，首先需要获取所有订单的订单号、订单日期和订单金信息，
然后把这些信息和日期表进行关联，
获取年份信息，最后根据这四个列按年份归组统计获取所有订单每年的总金额。
select a.theyear,sum(c.amount) from sdate a,stock b,stockdetail c where a.dateID=b.dateID and b.ordernumber=c.ordernumber group by a.theyear;

2、计算所有订单每年最大金额订单的销售额
算法分析：

该算法分为两步：
1）按照日期和订单号进行分组计算，
获取所有订单每天的销售数据；

select a.dateid, a.ordernumber,sum(b.amount) as sumofamount
from stock a,stockdetail b
where a.ordernumber=b.ordernumber
group by a.dateid,a.ordernumber;

2）把第一步获取的数据和日期表进行关联获取的年份信息，
然后按照年份进行分组，使用Max函数，获取所有订单每年最大金额订单的销售额。

select c.theyear,max(d.sumofamount) from sdate c,
(select a.dateid, a.ordernumber,sum(b.amount) as sumofamount
from stock a,stockdetail b
where a.ordernumber=b.ordernumber
group by a.dateid,a.ordernumber)d
where c.dateid=d.dateid
group by c.theyear order by c.theyear;

3、统计所有订单中季度销售额前10位
select c.theyear,c.thequot,sum(b.amount) as sumofamount
from stock a,stockdetail b,sdate c
where a.ordernumber=b.ordernumber and a.dateid=c.dateid
group by c.theyear,c.thequot
order by sumofamount desc limit 10;

4、列出销售金额在100000以上的单据(订单号)
select a.ordernumber,sum(b.amount) as sumofamount
from stock a,stockdetail b
where a.ordernumber=b.ordernumber
group by a.ordernumber
having sumofamount>100000;
5、所有订单中每年最畅销货品
第一步：
统计出每年每种货品的销售总金额
stock a,stockdetail b,sdate c
===================================
select c.theyear,b.itemid,sum(b.amount) as sumofamount
from stock a,stockdetail b,sdate c
where a.ordernumber=b.ordernumber and a.dateid=c.dateid
group by c.theyear,b.itemid;

第二步:
在第一步的数据上，统计出每年最大的销售总金额
将第一步的数据集起别名为d;
select d.theyear,max(sumofamount) as maxofamount from
(select c.theyear,b.itemid,sum(b.amount) as sumofamount
from stock a,stockdetail b,sdate c
where a.ordernumber=b.ordernumber and a.dateid=c.dateid
group by c.theyear,b.itemid) d
group by d.theyear;

第三步：所有订单中每年最畅销货品
e:每年每种货品的销售总金额
f:每年最大的销售总金额
select distinct e.theyear,e.itemid,f.maxofamount from
(select c.theyear,b.itemid,
sum(b.amount) as sumofamount from stock a,stockdetail b,sdate c
where a.ordernumber=b.ordernumber and a.dateid=c.dateid
group by c.theyear,b.itemid) e,
(select d.theyear,max(d.sumofamount) as maxofamount from
(select c.theyear,b.itemid,sum(b.amount) as sumofamount
from stock a,stockdetail b,sdate c
where a.ordernumber=b.ordernumber and a.dateid=c.dateid
group by c.theyear,b.itemid) d
group by d.theyear) f
where e.theyear=f.theyear and e.sumofamount=f.maxofamount
order by e.theyear;