MySQL

书写顺序：select--from--where--group by--having--order by
执行顺序：from--where--group by--having--select--order by
from：从哪个表检索
where：过滤表中数据的条件
group by：分组
having：对已经分组的数据进行过滤
select：查看结果集中的哪个列，或列的结果
order by：按照什么样的顺序来查看

hive与sql的区别(转自链接处）：

• hive不支持等值连接：
  例如
  sql：select * from a,b where a.id=b.id
  hive必须为：select * from a join b on a.id=b.id;
• 分号字符
  sql:select concat(key,concat(';',key)) from dual;
  hive需要进行转义：select concat(key,concat('\073',key)) from dual;
• hive不支持insert into 表 values() ,delete,update
• hive支持嵌入mapreduce程序，来处理复杂的逻辑
• Hive不支持将数据插入现有的表或分区中
  只支持覆盖重写整个表 insert overwrite table t1
• hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录
  from t1
  insert overwrite table t2
  select t3.c2, avg(t3.c1) from t3 where
  怎么防止数据倾斜

1.开窗函数

-排名开窗 ROW_NUMBER、DENSE_RANK、RANK、NTILE
--查询按组累加
select id,[group],num,sum(num) over(partition by [group] order by id) from testaa
group by id,[group],num

ROW_NUMBER（）为每一组的行按顺序生成一个唯一的序号
RANK() 若有重复值则生成重复的序号，且下一个序号不连续
DENSE_RANK()若有重复值则生成重复的序号，下一个序号是连续的

#row_number() over (partition by 分组列 order by 排序列)
SELECT WeekNo,Price,Sales_Amount,
row_number() over (partition by WeekNo order by Sales_Amount) as rk from forecasting
order by WeekNo

运行结果

#DENSE_RANK() over (partition by 分组列 order by 排序列)
SELECT WeekNo,Price,Sales_Amount,
DENSE_RANK() over (partition by WeekNo order by Sales_Amount) as rk from forecasting
order by WeekNo

运行结果

#RANK() over (partition by 分组列 order by 排序列)
SELECT WeekNo,Price,Sales_Amount,
RANK() over (partition by WeekNo order by Sales_Amount) as rk from forecasting
order by WeekNo

运行结果

-聚合开窗函数 sum() max() min()

#max(聚合列) over(partition by 排序列)
SELECT WeekNo,Price,Sales_Amount,
max(Sales_Amount) over (partition by WeekNo) as rk from forecasting
order by WeekNo

运行结果

2.join,left join,right join,union all,union

-join：取交集，INNER JOIN 与 JOIN 是相同的；
-left join：以左表为主表，若左(m条)右(n条)两表是一（多）对多的关系，则结果集显示左表中连接字段的值分别与右表连接字段的多个值进行连接，最多m*n条，最少0条，left join与left outer join是相同的；
-right join：以右表为主表，结果集返回右表的行数，一（多）对多与上面left join类似；
-union all：取并集，包括重复行，不进行排序，m+n条；
-union：取并集，不包括重复行，排序，最多m+n条；

select * from (SELECT distinct WeekNo from forecasting) a 
left join forecasting b
on a.WeekNo=b.WeekNo
order by a.WeekNo

left join一对多运行结果

#union all
select * from (SELECT distinct WeekNo from forecasting where month_num=12) a 
union all
SELECT distinct WeekNo from forecasting b
order by WeekNo

union all运行结果

#union 
select * from (SELECT distinct WeekNo from forecasting where month_num=12) a 
union 
SELECT distinct WeekNo from forecasting b
order by WeekNo

union运行结果

3.日期函数

-now() 返回当前日期时间；
-curdate()返回当前日期；
-datediff(end_date,start_date)返回相差的天数；
-date_sub/add(start_date,interval n day/hour/year);hive:date_sub/add(start_date,n)返回减/加天数的日期

SELECT  WeekNo,NOW(),datediff(curdate(),'2020/12/31'),
date_add(now(),interval -4 day)  from forecasting order by WeekNo

运行结果

4.空值处理函数

-isnull(字段名,返回值)，coalesce(),ifnull()一样的用法

SELECT  ifnull(Inventory_Unit,0)  from forecasting order by WeekNo

运行结果

5. 去除重复值只保留一行

用row_number() over(partition by order by )编号，取第一条

6.数据库

ODS层：粒度最细；当前需要加载的数据，存储处理完后的历史数据
DW层：数据仓库层；经过清洗了的，满足第三范式；粒度与ODS相同；只允许增加，不允许修改删除；按照一定主题进行组织；
DM层：数据集市层；以某个业务应用为出发点，雪花状；汇总级，不存在明细数据；

7. 将字符串转化为数组,并满足top_category_name_new包含在rule_category 中

array_contains(split(colnameA,','),colnameB)
eg: colnameA: a,b,c colnameB:c

8. concat相关

• 连接多个字段

(1) concat(colname1,'separator',colname2,'separator',...,colnameN)
只要其中一个是NULL，那么将返回NULL

(2) concat_ws连接多个字段，并一次性指定分隔符
concat_ws('separator',colname1,colname2,...,colnameN)
只要有一个字符串不是NULL，就不会返回NULL,separator不能为NULL，否则返回NULL
eg:

image.png

• 实现行转列,并放在一条记录里

(1) 【Hive SQL】concat_ws('seperator',collect_set(colname))
collect_set将同一个分组中的值转化成数组，去重无序，排除NULL值
需要排序可以改成collect_list(不去重)或在外面加sort_array进行排序

concat_ws('seperator',collect_list(colname))
concat_ws('seperator',sort_array(collect_set(colname)),false)
eg:
colname
1
2
3
转换为(1,2,3)

(2)group_concat( [distinct] colname [order by 排序字段 asc/desc ] [separator '分隔符'] )

或者group_concat(colname,'separator'),表示将group by产生的同一个分组中的值连接起来，返回一个字段
后面别忘了group by
eg:

image.png

9. array_contains(split( CASE WHEN rule_category IN ('服装','女装') THEN '服装' ELSE rule_category END,','), category_lv1)

表示某个字段的值包含在数组中

10. 分类汇总（a,b,c,total)

grouping(col==1,'total',col)
group by cube(col)

11.json字符串解析

get_json_object(col,'$.name')