【Python大数据笔记_day08_hive查询】

hive查询

语法结构:

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]

类sql基本查询

基础查询格式: select distinct 字段名 from 表名;      
                注意: *代表所有字段  distinct去重  as给表或者字段起别名
条件查询格式: select distinct 字段名 from 表名 where 条件;
                比较运算符: > < >= <= != <>
                逻辑运算符: and or not
                模糊查询: %代表任意0个或者多个字符   _代表任意1个字符
                空判断: 为空is null   不为空is not null
                范围查询: x到y的连续范围:between x and y    x或者y或者z类的非连续范围: in(x,y,z)
排序查询格式:  select distinct 字段名 from 表名 [where 条件] order by 排序字段名 asc|desc ;
                asc : 升序 默认升序
                desc: 降序
聚合查询格式: select 聚合函数(字段名) from 表名;
                聚合函数: 又叫分组函数或者统计函数
                聚合函数: count()  sum()  avg()  max()  min()
分组查询格式:  select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];
                注意: 当分组查询的时候,select后的字段名要么在groupby后出现过,要么放在聚合函数内,否则报错
                where和having区别? 
                区别1: 书写顺序不同,where在group by关键字前,having在group by关键字后
                区别2: 执行顺序不同,where在分组之前过滤数据,having在分组之后过滤数据
                区别3: 筛选数据不同,where只能在分组之前过滤非聚合数据,having在分组之后主要过滤聚合数据
                区别4: 操作对象不同,where底层操作伪表,having底层操作运算区
分页查询格式: select 字段名 from 表名 [ order by 排序字段名 asc|desc] limit x,y;
                x: 起始索引 默认从0开始,如果x为0可以省略    计算格式: x=(页数-1)*y
                y: 本次查询记录数          

数据准备:

-- 创建订单表
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';
-- 加载数据(因为是普通内部表可以直接上传文件到hfds表路径下)

数仓分层思想:

-- 数仓分层: 本质就是分库分表
-- 构建源数据层
create database xls_ods;
-- 构建数数据仓库
create database xls_dw;
-- 构建数据应用层
create database xls_da;
​
-- 转换应用场景
-- 注意: 在大数据分析中转换完后为了以后方便使用一般存储起来
create table xls_dw.dw_orders as
select
    orderid,
    orderno,
    shopid,
    userid,
    orderstatus,
    goodsmoney,
    delivermoney,
    totalmoney,
    realtotalmoney,
    case