【SQL】

min, max 函数使用注意事项

#正确用法
select min(column_name)
from t1;

与where / if搭配时需注意，不能直接在比较符号之后跟min / max，仍需要借助select

#正确用法
where t1.date > (select min(t1.date) from t1)
#错误用法，报错 invalid use of group function
where t1.date > min(t1.date)

last_day()函数

没有first_day()函数
可以用于获取每个月的天数，day(last_day())

date()函数

在HIVE中是to_date()函数

distinct date(date_column)会报错
如这样

#submit_time 形如：2021-07-02 09:21:01

select uid,  distinct date(submit_time) as dates
from exam_record
where year(submit_time) = '2021' 

但是直接使得 distinct 作用于submit_time不会报错：

select uid,  distinct submit_time as dates
from exam_record
where year(submit_time) = '2021' 

或这样

select distinct submit_time 
from (
    select date(submit_time) as submit_time
    from exam_record
) as t1

unix_timestamp、from_unixtime

unix_timestamp将指定格式的时间转为标准unix时间戳（秒），
from_unixtime将标准unix时间戳（秒）转为指定时间格式。

字符串拼接函数concat系列函数

https://blog.csdn.net/weixin_57504474/article/details/123918557

group_concat

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

HIVE 暂不支持该函数

distinct关键字

报错：

无报错：

round()函数

select roud(81.667,1) as col1
输出：
col1
81.7

EXTRACT() 函数

用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
语法和参数

is in 和 not in

注意不是 is not in

union

union各子句内单独排序的方法
注意不要忘记起别名

rank(), dense_rank(), row_number()

rank 1 1 1 4 4 4
dense_rank 1 1 1 2 2 2
row_number 1 2 3 4 5 6

group by VS partition by

partition by可以在分组后保留每一条记录的详细信息，常和rank等窗口函数一起使用。

--这段代码想在( uid,ret_dt,fore_shop,new_shop）组合唯一的前提下，依次按照 fore_gap,new_gap进行排序。

rank() over(
                partition by uid,
                ret_dt,
                fore_shop,
                new_shop
                order by
                    fore_gap,
                    new_gap
            ) as rank_num

group by 只能保留一行，并且select 单独字段时只能在group by条件里选择。

NVL函数

空值转换函数

NVL（表达式1，表达式2）

• 如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。
• 该函数的目的是把一个空值（null）转换成一个实际的值。
• 其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

coalesce函数

coalesce(expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

• 应用场景：相比if函数，适用于处理多个选项的情况。
• 假设有t1，t2， t3三张表join在一起，每个表都有uid字段。
• if可以处理 if(t1.uid is null then t2.uid) as uid
• coalesce可以处理coalesce(t1.uid, t2.uid, t3.uid) as uid：当t1.uid为空使用t2.uid，当t2.uid也为空，使用t3.uid