spark快速大數據分析大數據Spark “蘑菇云”行動第96課：基于Hive對電商數據案例分析－IT工程師數位筆記本

文章出處

大數據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;

//每年所有訂單中最大金額訂單的全部銷售額