coalesce函数_sql常用函数小归纳

清空表内已有记录

truncate 

保留小数 ：

round

百分比

100

日期时间：

当前时间

sysdate 

字符串转日期

to_date

日期转字符串

to_char

月份加减

add_month

多条件设置值

case 

填充空值

coalesce

向前后取值：

向前

lag

向后

lead

连续排序

row_number

跳跃排序

rank

连续排序

dense_rank

伪列，单纯对查询结果排序

rownum

文本处理：

文本截取

substr

查找字符位置

instr

clob转string（clob类型，字符大对象（不能用like，但可是作截取））

dbms_lob

正则截取，查找

regexp_substr

分组计算时加汇总行：

可以多个列进行group by，并分别进行统计

rollup

列填补字符

decode

转置：

pivot

空值放前

order 

创建临时表（在本会话窗口有效）

create 

---

Hive SQL

explode 函数

lateral 

分位数 函数

percentile_approx(col, 0.5)

类型转换 函数

cast(col AS FLOAT)

添加一列并定义值

cast('值' as 类型) as 字段名

条件分类汇总中：

count/sum(if(col=?,1,0) <=> count/sum(case when col=? then 1 end)

正则表达：

Regexp 相当于 rlike

字符串截取 regexp_extract（string，pattern，要提取的位置）

pattern：用()拆成一段段，然后提取

指定汉字，符号可以直接写，直接识别

数字：[0-9]
英文：[a-zA-Z]
中文：[u4e00-u9fa5] 双字节：[^x00-xff]
任意字符：(.*?)

PYTHON

分组统计

data.groupby('col1').agg({'col2': 'mean'}).collect()
groupby().mean('col1','col2','col3').collect()
.agg(F.countDistinct('col1').alias('col_name'))----去重
select('col1','col2').dropDuplicates()----去重

pyspark嵌hive sql

%

Repartition = 分区，让多台机器同时帮忙计算
persist是让df的计算保存下来，下面的语句不会重复调动计算

dataframe存成hive表

df.write.mode('overwrite').saveAsTable('name') 

DataFrame导出到csv文件

其中：index是否要索引，header是否要列名，True就是需要

outputpath='d:/Users/xxx/Desktop/xxx.csv'
df.to_csv(outputpath,sep=',',index=False,header=False)

循环体参考：

for s,e in sorted(zip(start_months, end_months)):
 start_time = s
 end_time = e
 member_count = per_month_shopper(start_time = s,end_time = e).collect()
 print(member_count)