语法结构
SELECT [ALL | DISTINCT] 字段名, 字段名, ...
FROM 表名 [inner | left outer | right outer | full outer | left semi JOIN 表名 ON 关联条件 ]
[WHERE 非聚合条件]
[GROUP BY 分组字段名]
[HAVING 聚合条件]
[ORDER BY 排序字段名 asc | desc]
[CLUSTER BY 字段名 | [DISTRIBUTE BY 字段名 SORT BY 字段名]]
[LIMIT x,y]抽样查询
知识点
TABLESAMPLE抽样好处: 尽可能实现随机抽样,并且不走MR,查询效率相对较快。
基于随机分桶抽样格式: SELECT 字段名 FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(字段名 | rand()))
y:决定将表数据随机划分成多少份
x:决定从第几份数据开始采样
| : 或者
字段名: 表示随机的依据基于某个列的值,每次按相关规则取样结果都是一致
rand(): 表示随机的依据基于整行,每次取样结果不同
准备数据
数据文件👉a_orders.txt
-- 创建订单表
CREATE TABLE 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';
-- 加载数据，通过hdfs上传至/目录下
load data inpath '/a_orders.txt' into table orders;
-- 验证数据
select * from orders;随机抽样函数 tablesample
-- 参考字段分桶抽样,快且随机
select * from orders tablesample ( bucket 1 out of 10 on orderid);
-- 参考rand()随机数,快且真正达到随机
select * from orders tablesample ( bucket 1 out of 10 on rand());-- 快速取前面部分数据 : 快但没有随机
-- 前100条
select * from orders tablesample ( 100 rows );
-- 前10%数据
select * from orders tablesample ( 10 percent );
-- 取1k或者1m的数据
select * from orders tablesample ( 16k );
select * from orders tablesample ( 167k );
select * from orders tablesample ( 1m );-- 随机取100条: 随机但是不快
select * from orders distribute by rand() sort by rand() limit 100;正则模糊查询
sql模糊查询和正则模糊查询的区别
sql模糊查询关键字: like 任意0个或者多个: % 任意1个: _
正则模糊查询关键字: rlike 任意0个或者多个: .* 任意1个: . 正则语法还有很多......
-- 1.查询广东省数据
-- sql模糊查询
select * from orders where userAddress like '广东省%';
-- 正则模糊查询
select * from orders where userAddress rlike '广东省.*';-- 2. 查询满足'xx省 xx市 xx区'格式的信息
-- sql模糊查询
select * from orders where userAddress like '__省 __市 __区';
-- 正则模糊查询
select * from orders where userAddress rlike '..省 ..市 ..区';-- 3.查询所有姓张王邓的用户信息
-- sql模糊查询
select * from orders where username like '张%' or username like '王%' or username like '邓%' ;
-- 正则模糊查询
select * from orders where username rlike '[张王邓].*';
select * from orders where username rlike "[张王邓].+";-- 4.查找所有188开头的手机号
-- sql模糊查询
select * from orders where userPhone like '188________' ;
-- 正则模糊查询
select * from orders where userPhone rlike '188........' ;
select * from orders where userPhone rlike '188.{8}' ;
select * from orders where userPhone rlike '188\\*{4}[0-9]{4}' ;
select * from orders where userPhone rlike '188\\*{4}\\d{4}' ;CTE表达式
CTE: 公用表表达式（CTE）是一个在查询中定义的临时命名结果集将在from子句中使用它。
注意: 每个CTE仅被定义一次（但在其作用域内可以被引用任意次）,仅适用于当前运行的sql语句。
语法如下:
with 临时结果集的别名1 as (子查询语句),
临时结果集的别名2 as (子查询语句)
...
select 字段名 from (子查询语句);
根据实际使用次数可以决定用"CTE表达式"还是"视图"还是"建表":
with 临时结果名 as (select语句) > create view 视图名 as select语句 > create table 表名 as select语句。
内置虚拟列
虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。
Hive目前可用3个虚拟列：
- INPUT__FILE__NAME:显示数据行所在的具体文件
- BLOCK__OFFSET__INSIDE__FILE:显示数据行所在文件的偏移量
- ROW__OFFSET__INSIDE__BLOCK:显示数据所在HDFS块的偏移量
注意: 此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用
SET hive.exec.rowoffset=true;select * ,INPUT__FILE__NAME,BLOCK__OFFSET__INSIDE__FILE,ROW__OFFSET__INSIDE__BLOCK from students;-- 按照位置分组
select INPUT__FILE__NAME ,count(*) from students group by INPUT__FILE__NAME;--按照偏移量查询
select *,BLOCK__OFFSET__INSIDE__FILE from students where BLOCK__OFFSET__INSIDE__FILE > 100;详解)
)