数据仓库——hive的相关配置和操作

* create database db_name;
* create database if not exists db_name;//创建一个不存在的数据库final

* show databases;
* show databases like 'f.*'; //选择性查看数据库
* describe database db_name; //查看某一个数据库的详细信息

* create table t_name (....)
* create table t_name as select ... //以查询结果创建表
* create tabale t_name like t_old_name  //克隆表
* create table gds_goods(id int,g_name string,price int,desc string)row format delimited fields terminated by '\t';     //以分隔符创建数据表

* insert into table t_name values(.....)    //插入数据
* insesrt into table t_Name select .... //查询的方式插入数据
* load data local inpath '....' into table t_name;  //导入Linux本地的数据（不覆盖表数据）
* load data local inpath '....' overwrite into table t_name;    //导入Linux本地的数据并且覆盖表数据
* load data inpath '....' into table t_name     //导入hdfs的数据（不覆盖表数据）
* load data inpath '....' overwrite into table t_name   //导入hdfs的数据并且覆盖表数据
* load data inpath  '/gds_goods' INTO TABLE gds_goods;（例子）

* into data local direction '' from t_name      //导出到Linux本地
* into data direction '' from t_name        //导出到hdfs
* insert overwrite local directory "/home/hadoop/hive_data/sale.txt" row format delimited fields terminated by '\t' select * from kongtao.sale;（例子）

* create table t_name (....) partitioned by (....)
* create table sale(id int,name String,price int)partitioned by (date int) row format delimited fields terminated by '\t';（例子）
* 分别向分区导入数据
* load data local inpath '/home/hadoop/day1' into table kongtao.sale partition (udate='20200401’);
* load data local inpath '/home/hadoop/day2' into table kongtao.sale partition (udate='20200402’);

建立访客表
create table fangke(ip string,ftime string,secondTime string,uid string,tel string,aid string,link string,event string,resource string,dfree float)row format delimited fields terminated by ',';
load data local inpath '/home/hadoop/桌面/CSV/访客表.csv' overwrite into table fangke;

建立订单表
create table dingdan(id string,dtime string,mid string,bookid string,mip string,address string,tel string,clickres string,dfree float,overtime string,orderStatus string)row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/桌面/CSV/订单表.csv' overwrite into table dingdan;

建立商品类目表
create table shop(sid string,sname string,author string,editor string,etime string,idbm string,sellPrice float,price float,sCount int,fAddress string)row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/桌面/CSV/商品类目表.csv' overwrite into table shop;

1.独立访客数(派生表必须加别名）
  指访问电商网站的不重复用户。指访问某个站点的不同IP地址的人数，多次进入不重复统计。

select count(*) from (select count(*) from fangke group by ip) as ip;
select count(*) from (select count(*) from fangke group by ip) group by resource;


2.页面访问数
  页面浏览量，用户每一次对电商网站或着移动电商应用中的每个网页访问均被记录一次，用户对同一页面的多次访问，访问量累计
select count(*) from fangke;
select count(*) from fangke group by resource;



3.人均页面访问数
  页面访问数（PV）／独立访客数，指平均每个用户浏览的页面数，该指标反映的是网站访问粘性，要对每一个网站分开计算
select resource,count(resource),round(count(ip)/count(distinct ip),2) from fangke group by resource;



4.总订单数量
  完成下单操作的订单总量
select count(*) dingdan from fangke where event = 'chargeRequest';


5.访问到下单转化率
  下单数与网站访问次数之比，总下单数/页面访问数。
with c1
as (select count(*) pv from fangke),
c2
as (select count(*) dingdan from fangke where event=’chargeRequest’)
select b.dingdan/a.pv from c1 a,c2 b;


6.成交金额(GMV)
生成订单号的所有订单总和，包括付款和未付款两部分
select sum(price) from (select * from dingdan where orderStatus='支付成功' or orderStatus='待支付') a join shop on a.bookid=sid;


7.客单价
订单金额与订单数量的比值。反映平均每一件商品的平均单价
商品销售额(GMV)/顾客量  22.54
with c1
as (select count(*) uv from dingdan where orderStatus='支付成功'),
c2
as (select sum(price) zong from (select * from dingdan where orderStatus='支付成功') a join shop on a.bookid=sid)
select b.zong/a.uv from c1 a,c2 b;


8.销售毛利（毛利最高的时间商品） 计算规则：默认商品的成本默认是商品定价的40%（仅为指标实现，不具备现实普适性）
销售收入减去成本的差值，并没有减去运营成本（比如管理费用，营业费用）
商品售价总额-生产成本
with c1 
as (select sum(price) zong,sum(sellPrice) ss from (select * from dingdan where orderStatus='支付成功') a join shop on a.bookid=sid)
select a.zong-(a.ss*0.4) from c1 a;

9.毛利率（默认规则：(商品售价-商品定价*40%-渠道单点费用)）
衡量电商企业盈利能力的指标，是销售毛利与销售收入的比值。
with c1 
as (select sum(price) zong,sum(sellPrice) ss from (select * from dingdan where orderStatus='支付成功') a join shop on a.bookid=sid),
c2
as (select sum(dfree) df from fangke)
select (a.zong-(a.ss*0.4)-b.df)/a.zong from c1 a,c2 b;