Hive案例学习：电商交易数据分析

电商交易案例
–字段含义–
Sdate定义了日期的分类，将每天分别赋予所属的月份、星期、季度等属性，
字段分别为日期、年月、年、月、日、周几、第几周、季度、旬、半月；

Stock定义了订单表头，字段分别为订单号、交易位置、交易日期；

StockDetail文件定义了订单明细，该表和Stock以交易号进行关联，
字段分别为订单号、行号、货品、数量、价格、金额；

使用Hive实现下面内容
1 创建表并加载数据

--Sdate定义了日期的分类，将每天分别赋予所属的月份、星期、季度等属性,--
--字段分别为日期、年月、年、月、日、周几、第几周、季度、旬、半月；--
--创建sdate表并加载数据--
create table sdate(
sdate_date string,
sdate_year_month string,
sdate_year string,
sdate_month string,
sdate_day string,
sdate_week string,
sdate_weeks string,
sdate_quarter string,
sdate_xun string,
sdate_part_month string)
row format delimited fields terminated by ',';

load data local inpath '/root/ksdata/sdate.txt' into table sdate;

select * from sdate limit 10;
	
--创建stock表并加载数据--
--字段分别为订单号、交易位置、交易日期；--
--order_number,tr_location,tr_date--
create table stock(order_number string,tr_location string,tr_date string)
row format delimited fields terminated by ',';

load data local inpath '/root/ksdata/stock.txt' into table stock;

--创建stockdetail表并加载数据--
--订单号、行号、货品、数量、价格、金额--
--order_number,line_number,goods,quantity,price,amount--
create table stockdetail(
order_number string,
line_number string,
goods string,
quantity string,
price string,
amount string)
row format delimited fields terminated by ',';

load data local inpath '/root/ksdata/stockdetail.txt' into table stockdetail;

2 需求实现
(1)计算所有订单每年的总金额

select a.stock_year,sum(cast(b.amount as int)) as total_sum from
(select order_number,substr(tr_date,0,4) as stock_year from stock ) a
join
stockdetail b
on a.order_number=b.order_number
group by a.stock_year;

+---------------+------------+--+
| a.stock_year  | total_sum  |
+---------------+------------+--+
| 2004          | 3265696    |
| 2005          | 13247234   |
| 2006          | 13670416   |
| 2007          | 16711974   |
| 2008          | 14670698   |
| 2009          | 6322137    |
| 2010          | 210924     |
| 3274          | 1703       |
+---------------+------------+--+

(2)统计所有订单中季度销售额前10位

create view stock_quarter as
select st.order_number as order_number,st.tr_location as tr_location,st.tr_date as tr_date,sd.sdate_year,sd.sdate_quarter as sdate_quarter 
from stock st 
join sdate sd 
on st.tr_date=sd.sdate_date;

select a.sdate_year as sdate_year,a.sdate_quarter as sdate_quarter,sum(cast(b.amount as int)) as total_sum
from stock_quarter a
join
stockdetail b
on a.order_number=b.order_number
group by a.sdate_quarter,a.sdate_year order by total_sum desc limit 10;
+-------------+----------------+------------+--+
| sdate_year  | sdate_quarter  | total_sum  |
+-------------+----------------+------------+--+
| 2008        | 1              | 5252819    |
| 2007        | 4              | 4613093    |
| 2007        | 1              | 4446088    |
| 2006        | 1              | 3916638    |
| 2008        | 2              | 3886470    |
| 2007        | 3              | 3870558    |
| 2007        | 2              | 3782235    |
| 2006        | 4              | 3691314    |
| 2005        | 1              | 3592007    |
| 2005        | 3              | 3304243    |
+-------------+----------------+------------+--+

(3)列出销售金额在100000以上的单据(订单号)

select a.order_number,sum(cast(b.amount as int)) as total_sum
from stock a 
join stockdetail b
on a.order_number=b.order_number
group by a.order_number
having total_sum>100000;
+-----------------+------------+--+
| a.order_number  | total_sum  |
+-----------------+------------+--+
| HMJSL00009024   | 119058     |
| HMJSL00009958   | 159126     |
+-----------------+------------+--+