HQL DQL语句基本查询

1.HQL DQL--基本查询

-- 1. 建库, 切库, 查表.
create database day08;
use day08;
show tables;

-- 2. 建表.
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';

-- 3. 上传源文件, 或者 load data方式加载数据.
-- 4. 查看表数据.
select * from orders;   -- 1000条

-- 5. 完成如下的需求.
-- 5.1 查询所有
select * from orders;   -- 1000条
select * from orders limit 100;   -- 10条, 实际开发中写法
-- 5.2 查询单列
select userName, orderId, totalMoney from orders;
-- 5.3 查询数据量
select count(orderId) from orders;        -- 1000条
-- 5.4 过滤广东省订单
select * from orders where userAddress like '广东省%';
-- 5.5 找出广东省单笔营业额最大的订单
-- 思路1: 排序.
select * from orders where userAddress like '广东省%' order by realTotalMoney desc limit 1;
-- 思路2: 子查询.
select *
from
    orders
where
      userAddress like '广东省%'
  and realTotalMoney = (select max(realTotalMoney) from orders where userAddress like '广东省%');

-- 5.6 统计未支付、已支付各自的人数
-- 方式1: 分组统计.
select
    case
        when isPay = 0 then '未支付'
        when isPay = 1 then '已支付'
    end as isPay,
    case isPay
        when 0 then '未支付'
        when 1 then '已支付'
    end as isPay2,
    count(orderId)
from
    orders
group by
    isPay;

-- 思路1: if()函数直接实现.     if(判断条件, 值1, 值2);  条件成立返回值1, 不成立返回值2
select
    count(if(isPay = 0, isPay, null)) as `未支付`,
    count(if(isPay = 1, isPay, null)) as `已支付`
from orders;

-- 5.7 在已付款订单中，统计每个用户最高的一笔消费金额
select userId, max(realTotalMoney) from orders where isPay = 1 group by userId;
-- 5.8 统计每个用户的平均订单消费额
select userId, userName, avg(realTotalMoney) from orders  group by userId, userName;
-- 升级需求: 保留两位小数.
select userId, userName, round(avg(realTotalMoney), 2) as avg_money from orders  group by userId, userName;
-- 5.9 统计每个用户的平均订单消费额，过滤大于10000的数据
-- 细节: having后边可以跟聚合函数, 不能直接写其它的 数字函数.
select userId, userName, avg(realTotalMoney) as avg_money from orders  group by userId, userName having avg_money > 10000;
-- 四舍五入, 保留两位小数, 组后筛选, 写法如下.
select
    userId,
    userName,
    round(avg(realTotalMoney), 2) as avg_money
from
    orders
group by
    userId, userName
having
    round(avg(realTotalMoney), 2) > 10000;

-- 细节: 保留两位小数.
select round(10.123, 2);    -- 10.12
select round(10.125, 2);    -- 10.13

2.HQL DQL语句--join连接查询

-- join连接查询是多表查询的方式, 它具体有 6种查询方式, 分别是: 交叉连接, 内连接, 左外连接, 右外连接, 满外连接(全外连接), 左半连接, 具体如下:
-- 1. 建表, 上传源文件.
--table1: 员工表
CREATE TABLE employee(
   id int,      -- 员工id
   name string,
   deg string,
   salary int,
   dept string
 ) row format delimited fields terminated by ',';

--table2:员工家庭住址信息表
CREATE TABLE employee_address (
    id int,     -- 员工id
    hno string,
    street string,
    city string
) row format delimited fields terminated by ',';

-- 2. 查看表数据.
select * from employee;
select * from employee_address;

-- 3. 演示各种连接查询.
-- 3.1 交叉连接查询 cross join, 查询结果是: 两张表的笛卡尔积, 即: 表A总条数 * 表B总条数, 会有大量的脏数据, 一般不用.
-- 写法1: 标准写法.
select * from employee cross join employee_address;     -- 25条
-- 写法2: 语法糖
select * from employee, employee_address;     -- 25条

-- 3.2 内连接查询, inner join, 其中inner可以省略不写, 查询结果为: 表的交集.
-- 写法1: 标准写法.  显式内连接
select * from employee e1 inner join employee_address e2 on e1.id = e2.id;  -- 4条
select * from employee e1 join employee_address e2 on e1.id = e2.id;  -- 4条

-- 写法2: 语法糖, 隐式内连接.
select * from employee e1, employee_address e2 where e1.id = e2.id;  -- 4条

-- 3.3 左外连接查询, left outer join, 其中outer可以省略不写, 查询结果为: 左表的全集 + 表的交集.
select * from employee e1 left outer join employee_address e2 on e1.id = e2.id; -- 5条
select * from employee e1 left join employee_address e2 on e1.id = e2.id; -- 5条

-- 3.4 右外连接查询, right outer join, 其中outer可以省略不写, 查询结果为: 右表的全集 + 表的交集.
select * from employee e1 right outer join employee_address e2 on e1.id = e2.id; -- 5条
select * from employee e1 right join employee_address e2 on e1.id = e2.id; -- 5条

-- 3.5 满外连接(全外连接)查询, full outer join, 其中outer可以省略不写, 查询结果为: 左表全集 + 右表全集 + 表的交集.
-- 细节: 满外连接 = 左外连接 + 右外连接 查询结果.
select * from employee e1 full outer join employee_address e2 on e1.id = e2.id; -- 6条
select * from employee e1 full join employee_address e2 on e1.id = e2.id; -- 6条

-- 3.6 左半连接, left semi join, , 查询结果为: 表的交集.
-- 细节: 左半连接 相当于 内连接的查询结果, 只要左表部分.
select * from employee e1 left semi join employee_address e2 on e1.id = e2.id; -- 4条, 只有左表的数据.

3.HQL DQL语句--分桶查询

分桶查询介绍:
    概述:
        分桶查询就是根据分桶字段, 把表数据分成n份, 但是: 是逻辑划分, 不是物理划分.
        逻辑划分: 类似于分组, 就是根据分桶字段值 进行分组, HDFS上文件还是1个.
        物理划分: 就是昨天的分桶建表, HDFS上存储数据的时候, 已经变成了N个文件.
    格式:
        cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段 asc | desc
    细节:
        1. 分桶查询是 逻辑分桶, 把数据分成n组进行查询, 但是HDFS上还是1个文件.
           分桶建表是 物理分桶, 数据在HDFS上会被存储到N个文件几种.
        2. 分桶查询需要用到 set mapreduce.job.reduces = n; 这个n就是ReduceTask任务的数量, 即: 分几个桶.
        3. mapreduce.job.reduces 参数的值默认是 -1, 即: 程序会按照数据量, 任务量自动分配ReduceTask的个数, 一般是1个, 即: 1个桶.
        4. distribute by 表示分桶, sort by表示对桶内的数据进行排序(局部排序), 如果分桶字段和排序字段是同1个, 则可以简写为: cluster by
           即: cluster by = distribute by + sort by
        5. 面试题: cluster by, distribute by, sort by, order by这四组关键字的区别是什么?
            cluster by:     分桶且(桶内局部)排序的, 分桶字段和排序字段必须是同1个字段, 且是升序排列.
            distribute by:  仅分桶的, 一般要结合 set mapreduce.job.reduces = n;参数一起使用.
            sort by:        桶内排序(即: 局部排序), 一般要结合 distribute by 一起使用.
            order by:       全局排序的, 对全表数据进行排序, 和cluster by冲突, 不能共用.
*/
-- 1. 建表, 上传源文件, 查看表数据.
create table student(
    sid int,
    name string,
    gender string,
    age int,
    major string
) comment '学生信息表'
row format delimited fields terminated by ',';
select * from student;      -- 22条

-- 2. 按照性别进行分桶, 分成2个桶...
-- 核心细节, 分桶查询是, 需要设置ReduceTask的任务数, 有几个ReduceTask, 就有几个桶.
set mapreduce.job.reduces = 2;  -- 默认是-1, 即: 程序会根据数据量, 任务量, 自动给出 ReduceTask任务数, 一般是: 1
select * from student distribute by gender;        -- 根据gender分桶, 且根据gender升序排列.

-- 3. 按照id分成3个桶, 注意: 不排序.
set mapreduce.job.reduces = 3;
select * from student distribute by sid;

-- 4. 上述的数据, 虽然分成了3个桶, 但是不方便查看数据, 因此, 加入: 排序.
-- 按照id分成3个桶, 然后按照年龄降序排列.
select * from student distribute by sid sort by age;    -- 默认是升序.
select * from student distribute by sid sort by age desc;    -- 降序

-- 5. 如果分桶字段和排序字段是同1个, 则可以简写为: cluster by
-- 根据sid分成3个桶, 且根据sid升序排列.
select * from student distribute by sid sort by sid;    -- 默认是升序.
select * from student cluster by sid;       -- 效果同上.
-- select * from student cluster by sid desc;       -- 报错, cluster by默认升序.

4.HQL DQL--随机抽样

/*
随机抽样解释:
    概述:
        随机采样指的是 tablesample()函数, 通过它, 我们可以用 类似于分桶的思路, 对数据进行采样.
    格式:
        select .. from 表名 tablesample(bucket x out of y on 列名 | rand());
    格式解释:
        1. y表示: 把数据分成y个桶.   逻辑分桶.
        2. x表示: 从y个分桶中, 获取第x份数据.
        3. 如果是列名方式, 列名采样, 在列名等其它条件不发生改变的情况下, 每次采样获取的数据都是一样的.
        4. 如果是rand()函数, 随机采样, 则: 每次获取的数据都是不一样的.
        5. x 不能大于 y
*/
-- 1. 查看原表数据.
select * from student;      -- 22条, 12条男, 10条女

-- 2. 按照 性别分成2个桶, 取第1份数据.
select * from student tablesample ( bucket 1 out of 2 on gender);   -- 10条, 女
-- 3. 按照 性别分成2个桶, 取第2份数据.
select * from student tablesample ( bucket 2 out of 2 on gender);   -- 12条, 男

-- 4. 演示rand()函数
select rand();      -- 随机数, 生成0.0 ~ 1.0之间的数字, 包左不包右, 也叫: 前闭后开.  [0.0, 1.0)

-- 5. 随机采样, 分成3个桶, 取第1份数据.
select * from student tablesample ( bucket 1 out of 3 on rand());   -- 每次获取的数据条数都不一样.
select * from student tablesample ( bucket 4 out of 3 on rand());   -- 报错, x不能大于y

-- 也可以换成其它的表进行采样.
select * from orders tablesample ( bucket 1 out of 3 on rand());