大数据Spark “蘑菇云”行动第96课：基于Hive对电商数据案例分析
tbStock.txt
订单号， 交易位置 ，交易日期
BYSL00000893,ZHAO,2007-8-23
BYSL00000897,ZHAO,2007-8-24
BYSL00000898,ZHAO,2007-8-25
tbStockDetail.txt
订单号，行号，订单产品，有效性，数量，金额
BYSL00000893,0,FS527258160501,-1,268,-268
BYSL00000893,1,FS527258169701,1,268,268
tbDate.txt
日期，年月，年，月，日， 周三，第一周，第一季度，上旬，上半月
2003-1-1,200301,2003,1,1,3,1,1,1,1
2003-1-2,200301,2003,1,2,4,1,1,1,1
2003-1-3,200301,2003,1,3,5,1,1,1,1
2003-1-4,200301,2003,1,4,6,1,1,1,1
create table tbData(dataID 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';
CREATE TABLE tbStock(ordernumber STRING,locatitionid string,dataID string) ROW FORMAT DELIMITED FIELDS
TERMINATED BY ',' LINES TERMINATED BY '\n';
CREATE TABLE tbStockDetail(ordernumber STRING,rownum int,itemid string,qty int,price int ,amout int) ROW FORMAT
DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
load data local inpath '/usr/local/IMFdatatest/HiveTBdata/tbDate.txt' into table tbdata;
load data inpath '/library/HiveTBdata/tbStock.txt'into table tbStock;
load data inpath '/library/HiveTBdata/tbStockDetail.txt'into table tbstockdetail;
//销售金额大于50000的订单
select ordernumber,sum(t2.amout) as totalfees FROM tbStock as t1 ,tbStockDetail as t2 where t1.ordernumber = t2.ordernumber group by t1.oredernumber having
totalfees >50000;
//每年每季度销售排名前10名的商品
select c.theyear,c.thequot,sum(b.amout) as sumofamount from tbstock a,tbstockdetail b,tbdata c where
a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,c.thequot order by sumofamount desc limit 10;
//所有的订单系统每年最畅销的产品
select distinct e.theyear,e.itemid,f.maxofmount from
(select c.theyear,b.itemid,sum(b.amout) as sumofmount from tbstock a,tbstockdetail b,tbdata c where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by
c.theyear,b.itemid ) e,
(select d.theyear, max(d.sumofamount) as maxofmount from (select c.theyear,b.itemid,sum(b.amout) as sumofamount from tbstock a,tbstockdetail b,tbdata c where
a.ordernumber=b.ordernumber and
a.dataid=c.dataid group by c.theyear,b.itemid ) d
group by d.theyear ) f
where e.theyear=f.theyear and
e.sumofmount=f.maxofmount order by e.theyear;
tbStock.txt
订单号， 交易位置 ，交易日期
BYSL00000893,ZHAO,2007-8-23
BYSL00000897,ZHAO,2007-8-24
BYSL00000898,ZHAO,2007-8-25
tbStockDetail.txt
订单号，行号，订单产品，有效性，数量，金额
BYSL00000893,0,FS527258160501,-1,268,-268
BYSL00000893,1,FS527258169701,1,268,268
tbDate.txt
日期，年月，年，月，日， 周三，第一周，第一季度，上旬，上半月
2003-1-1,200301,2003,1,1,3,1,1,1,1
2003-1-2,200301,2003,1,2,4,1,1,1,1
2003-1-3,200301,2003,1,3,5,1,1,1,1
2003-1-4,200301,2003,1,4,6,1,1,1,1
create table tbData(dataID 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';
CREATE TABLE tbStock(ordernumber STRING,locatitionid string,dataID string) ROW FORMAT DELIMITED FIELDS
TERMINATED BY ',' LINES TERMINATED BY '\n';
CREATE TABLE tbStockDetail(ordernumber STRING,rownum int,itemid string,qty int,price int ,amout int) ROW FORMAT
DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
load data local inpath '/usr/local/IMFdatatest/HiveTBdata/tbDate.txt' into table tbdata;
load data inpath '/library/HiveTBdata/tbStock.txt'into table tbStock;
load data inpath '/library/HiveTBdata/tbStockDetail.txt'into table tbstockdetail;
//销售金额大于50000的订单
select ordernumber,sum(t2.amout) as totalfees FROM tbStock as t1 ,tbStockDetail as t2 where t1.ordernumber = t2.ordernumber group by t1.oredernumber having
totalfees >50000;
//每年每季度销售排名前10名的商品
select c.theyear,c.thequot,sum(b.amout) as sumofamount from tbstock a,tbstockdetail b,tbdata c where
a.ordernumber=b.ordernumber and a.dataid=c.dataid group by c.theyear,c.thequot order by sumofamount desc limit 10;
//所有的订单系统每年最畅销的产品
select distinct e.theyear,e.itemid,f.maxofmount from
(select c.theyear,b.itemid,sum(b.amout) as sumofmount from tbstock a,tbstockdetail b,tbdata c where a.ordernumber=b.ordernumber and a.dataid=c.dataid group by
c.theyear,b.itemid ) e,
(select d.theyear, max(d.sumofamount) as maxofmount from (select c.theyear,b.itemid,sum(b.amout) as sumofamount from tbstock a,tbstockdetail b,tbdata c where
a.ordernumber=b.ordernumber and
a.dataid=c.dataid group by c.theyear,b.itemid ) d
group by d.theyear ) f
where e.theyear=f.theyear and
e.sumofmount=f.maxofmount order by e.theyear;
//每年所有订单中最大金额订单的全部销售额
