hive Unionall 路径 hive sql union

数分实习踩坑笔记：Hive SQL

• 数分实习中踩过的坑

• Hive SQL篇
• excel篇
• 面经分享

• AB实验
• 假设检验

• 后续会继续更新

数分实习中踩过的坑

记录一下数分实习中踩过的坑，好记性不如烂笔头，更何况我记性差到不能行，离职后能带走的也就只有自己的笔记经验和感受。

Hive SQL篇

• SQL执行顺序

1. from：作from子句前两个表的笛卡尔积
2. on：应用on筛选器，筛选出满足on逻辑表达式的行
3. join：根据join的条件选择是上一步的行还是外部行
4. where：应用where筛选器
5. group by：开始分组
6. having：对groupby 生成的虚拟表应用having条件
7. select：想要的字段列
8. distinct：删除掉重复的行
9. order by：根据条件进行排序

• union 和 union all 的区别 ：
  1. union 对两个结果集进行并集操作，不包括重复行；且对结果排序。
  2. union all 对两个结果集进行并集操作，包括重复行；不对结果排序。
• 去掉string中部分字符：
  1. substring( 字段名, 起始位置，截取长度）
  注：截取长度可省略
  2. regexp_replace( 字段名, 被替换字符，替换字符）
• count一定要distinct否则计数无意义 (当然也可以group by啦）
  关于两者性能之间的差别：
  1. distinct 空间换时间，当数据集中时效率高，离散时效率低。
  2. group by 时间换空间，当数据集中时效率低，离散时效率高。
• 解析json字段：
  get_json_object( 字段名，$表示json变量标识然后用 . 或 [] 读取对象或数组 )
  第二个参数具体要看要解析的json的结构是什么样的，然后再拆。
  我会用网上json解析看清结构再拆。
• 跑数前检查分区
  避免数还没产出就跑，区分清楚全量表和增量表
• date_sub() date_add()很好用
  方便限定日期和分区统一一起改日期，第一个参数：日期，第二个参数：天数。
• 窗口函数 很重要：
  <窗口函数> over (partition by <用于分组的列名>
  order by <用于排序的列名>)
  1. rank（） 会有并列的排序，后面排序跳过已占用的，比如1114
  2. row_number（）不考虑并列的问题，比如1234
  3. dense_rank（）有并列的情况，不占用下一个名次位置，比如1112
  4. 还有其他聚合函数sum(), count(), avg(), min(), max()
  5. lag(字段，向上偏移量，超范围后的填补值）
  6. lead(字段，向下偏移量，超范围后的填补值）
• 注意数据类型
  注意检查数据类型，如string的数字排序会错误，需转换类型后排序
• 全量表和增量表
  全量表最近一个分区存的是全量的历史数据，取数可以选择最近分区
  增量表新分区存的是新增加的数据，比如取数需要取2月1号到3月1号的数据，那分区可以选择大于取数日期范围的数据，例如2.1到3.2，但具体范围大多少，要以具体需求进行考量。
• concat() 和 concat_ws()拼接函数
  concat_ws 是 concat的 特殊形式 意为 CONCAT With Separator。
  当参数中有null，concat则为null（不管其他参数是不是null）
  concat_ws 会忽略null值，不会忽略其他参数。
  concat_ws 中分割值可以为其他参数，concat不行，只能为str。
  例如：concat（year，‘-’，month，‘-’，day）
  concat_ws(’-’, year, month, day)年-月-日拼起来
• 用时间戳进行时区转化
  from_unixtime(unix_timestamp(date) + timezone * 3600)
  date实例： 2022-02-28 20:21:26
  timezone实例：-11
  乘以3600是为了转换成秒
• 分位数函数
  percentile_approx(字段名, 想要看的分位数, 精度)
  例如：percentile_approx(grade, array(0.05,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,0.95,0.97,0.99), 9999)
• 日期转周数
  weekofyear(date) as weekno
• 有条件计数，求和
  count（case when …… then …… end）
  sum（case when …… then …… end）
• Hive中设置变量
  sql语句中直接用${var_name}调用
• 处理null值
  nvl（字段名，替代值）
  例子：nvl（number，0）
• 建临时表
  with 临时表名1 as( 建表逻辑 )，
  临时表名2 as( 建表逻辑 )
• case when
  case when 条件一 then 值一
  when 条件二 then 值二 end as 新字段名
• 转换日期格式
  to_date(time)
• 表连接
  以left join，join为主，搞清楚
• 当前日期时间
  ‘$[YYYY-MM-DD]’ 当前时间
  ‘$[YYYY-MM-DD - 2D]’ 当前时间减两天
  ‘$[YYYY-MM-DD-HH - 48H]’ 当前时间减48小时
• 建表调度结构

create table if not exists 表名（
	--字段信息
	字段名1 数据类型 COMMENT ‘备注’
	name string COMMENT ‘姓名‘
	grade int COMMENT ‘成绩‘
	）
comment ‘整个表的备注‘
partitioned by(
 -- 调度根据什么进行分区分组，例如
 country string --国家
 , pt string -分区
)；
-- 设置信息，例如：不严格模式（可不限制跑数）
set hive.exec.dynamic.partition.mode = nonstrict

insert overwrite table 表名 partition （country_code, pt)

-- 常规select 语句
select …………
from …………
where …………

• 时间取小时
  hour(time)
• 不等于
  <> 或者 !=
• 保留几位小数
  round（字段，小数点后几位）
• 数据类型转换
  cast（字段名 as 数据类型）
• coalesce（）
  COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值
  eg: coalesce（name，0）
• having语句
  由于where不能使用聚合函数，当需要聚合函数限定的时候可以使用having语句
  select……
  from……
  group by……
  having sum(grade) > 600
• split（）
  split（字段，分隔符号）
• 行转列
  select……
  from……
  lateral view explode(需展开的字段名）table名 as 新字段名
  where……
• limit（起始，终止）
  最后展示的行数

excel篇

• CSV文件改动会出错，下载完文件存为excel！！！不然改动完关闭文件，啥也不剩。
• iferror(value，value_if_error)
• vlookup(loopup_value, table_arry数据区域, col_index_num数据区域的第几列, 0精准查询 或 1模糊查询）
• sumifs(实际求和区域，第一个条件区域，第一个条件值，第二个条件区域，第二个条件值……）有时可以相当于多条件查询的vlookup
• 数据透视表要熟练运用

面经分享

面试岗位涉及数据分析师和数据工程师

AB实验

• AB实验是什么以及流程叙述
• AB实验的分流（正交、互斥）
• AB实验如何分组，要注意什么
• 什么是辛普森悖论
• AB实验最小样本量计算公式
• 讲一讲你所做过的AB实验（STAR原则）
• 什么是AA实验，一般用于哪些场景
• 实际运用中你认为它有哪些弊端，需要注意哪些问题
• AB实验前需不需要假设
• 为什么AB实验要做假设检验

假设检验

• 假设检验的流程
• P值的数学含义以及在AB实验中的含义）
• 如何给不懂技术的业务方解释P值
• 为什么我们倾向于将想要拒绝的假设设为原假设H0
• 一类错误二类错误是什么，在业务中是什么
• 一类错误二类错误哪个会造成更严重的影响
• α是什么
• 1-α是什么
• 检验方式分为什么（单侧双侧）有什么区别
• 拒绝域是什么

后续会继续更新