hive查询语句

1.基本语法

SELECT [ALL | DISTINCT]select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BYcol_list]
[HAVING where_condition]
[ORDER BYcol_list]
[CLUSTER BYcol_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

先建个表:订单表

CREATE DATABASE w_1026;
USE w_1026;
CREATE TABLE w_1026.orders (
orderId bigint COMMENT ' 订单 id',
orderNo string COMMENT ' 订单编号 ',
shopId bigint COMMENT ' 门店 id',
userId bigint COMMENT ' 用户 id',
orderStatus tinyint COMMENT ' 订单状态 -3: 用户拒收 -2: 未付款的订单 -1 ：用户取消 0: 待发货 1: 配送中 2: 用户确认收货 ',
goodsMoney double COMMENT ' 商品金额 ',
deliverMoney double COMMENT ' 运费 ',
totalMoney double COMMENT ' 订单金额（包括运费） ',
realTotalMoney double COMMENT ' 实际订单金额（折扣后金额） ',
payType tinyint COMMENT ' 支付方式 ,0: 未知 ;1: 支付宝， 2 ：微信 ;3 、现金； 4 、其他 ',
isPay tinyint COMMENT ' 是否支付 0: 未支付 1: 已支付 ',
userName string COMMENT ' 收件人姓名 ',
userAddress string COMMENT ' 收件人地址 ',
userPhone string COMMENT ' 收件人电话 ',
createTime timestamp COMMENT ' 下单时间 ',
payTime timestamp COMMENT ' 支付时间 ',
totalPayFee int COMMENT ' 总支付金额 '
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

用户表:

CREATE TABLE w_1026.users (
userId int,
loginName string,
loginSecret int,
loginPwd string,
userSex tinyint,
userName string,
trueName string,
brithday date,
userPhoto string,
userQQ string,
userPhone string,
userScore int,
userTotalScore int,
userFrom tinyint,
userMoney double,
lockMoney double,
createTime timestamp,
payPwd string,
rechargeMoney double
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

导入数据:

LOAD DATA LOCAL INPATH '/home/wtk/itheima_orders.txt' INTO TABLE w_1026.orders;

LOAD DATA LOCAL INPATH '/home/wtk/itheima_users.txt' INTO TABLE w_1026.users;

SELECT 基本查询:

查询所有:select * from users;

查询指定列:SELECT orderid, totalmoney, username, useraddress, paytime FROM w_1026.orders;

查询数据量:

SELECT count(*) FROM w_1026.orders;

过滤广东省订单:

SELECT count(*) FROM w_1026.orders where useraddress like '%广东省%';

找出广东省单笔营业额最大的订单:

方式1:select * from w_1026.orders where (SELECT max(totalmoney) FROM w_1026.orders where useraddress like '%广东省%');

方式2:SELECT * FROM w_1026.orders where useraddress like '%广东省%' order by totalmoney desc limit 1;

分组、聚合

统计未支付、已支付各自的人数:SELECT ispay,count(*) FROM w_1026.orders group by ispay;

•在已付款订单中，统计每个用户最高的一笔消费金额

SELECT userid,max(totalmoney) FROM w_1026.orders where ispay=1 group by userid;

• 统计每个用户的平均订单消费额

SELECT username,avg(totalmoney) FROM w_1026.orders group by username;

• 统计每个用户的平均订单消费额，过滤大于 10000 的数据

SELECT username,avg(totalmoney) as pay FROM w_1026.orders group by username having pay>10000;

JOIN 订单表和用户表，找出用户名

SELECT u.username FROM w_1026.orders join users u on orders.userid = u.userid

总结:Hive 中使用基本查询 SELECT 、 WHERE 、 GROUP BY 、聚合函数、 HAVING 、 JOIN 和普通的 SQL 语句没有区别