文章目录一、分类总览1、函数分类2、全部函数二、条件判断类1、定义2、区别3、简单记忆4、案例三、类型转换类1、定义2、区别3、简单记忆4、案例四、NULL处理类1、定义2、区别3、简单记忆4、案例五、压缩 / 校验 / Hash类1、定义2、区别3、简单记忆4、案例六、身份证信息类1、定义2、区别3、简单记忆4、案例七、账号 / 元数据判断类1、定义2、区别3、简单记忆4、案例八、排序 / 采样类1、定义2、区别3、简单记忆4、案例九、Map转换类1、定义2、区别3、简单记忆4、案例十、行列转换类1、定义2、区别3、简单记忆4、案例十一、随机ID类1、定义2、区别3、简单记忆4、案例十二、最容易混淆的函数对比十三、常用案例1、NULL兜底2、多级兜底3、安全转换数字4、最新分区5、条件分类6、生成Data ID7、判断表是否存在8、字符串转MAP十四、参考资料一、分类总览1、函数分类分类主要解决的问题代表函数1、条件判断类区间判断、条件分支、条件返回BETWEEN AND、CASE WHEN、IF、FAILIF2、类型转换类数据类型转换和安全转换CAST、SAFE_CAST3、NULL处理类空值兜底、相等转 NULLCOALESCE、NVL、NULLIF4、压缩 / 校验 / Hash类GZIP 压缩、CRC 校验、Hash、SHACOMPRESS、DECOMPRESS、CRC32、HASH、SHA、SHA1、SHA25、身份证信息类从身份证号码提取年龄、出生日期、性别GET_IDCARD_AGE、GET_IDCARD_BIRTHDAY、GET_IDCARD_SEX6、账号 / 元数据判断类获取账号 ID判断表或分区是否存在GET_USER_ID、TABLE_EXISTS、PARTITION_EXISTS、MAX_PT7、排序 / 采样类取排序后指定位置值、数据采样ORDINAL、SAMPLE8、Map转换类将字符串解析为 MAPSTR_TO_MAP9、行列转换类一行拆多行、多列转多行STACK、TRANS_ARRAY、TRANS_COLS10、随机ID类生成随机唯一标识UNIQUE_ID、UUID2、全部函数分类函数功能1、条件判断类BETWEEN AND判断数据是否落在指定区间内1、条件判断类CASE WHEN按条件返回不同结果1、条件判断类FAILIF条件满足时返回TRUE否则触发自定义报错1、条件判断类IF根据条件真假返回不同值2、类型转换类CAST将表达式转换为目标数据类型2、类型转换类SAFE_CAST尝试转换类型失败时安全返回NULL3、NULL处理类COALESCE返回参数列表中第一个非NULL值3、NULL处理类NULLIF两个参数相等时返回NULL3、NULL处理类NVL参数为NULL时返回替代值4、压缩 / 校验 / Hash类COMPRESS使用GZIP压缩STRING或BINARY4、压缩 / 校验 / Hash类CRC32计算字符串或二进制数据的 CRC32 校验值4、压缩 / 校验 / Hash类DECOMPRESS使用GZIP解压BINARY4、压缩 / 校验 / Hash类HASH对输入参数计算 Hash 值4、压缩 / 校验 / Hash类SHA计算SHA-1Hash 值4、压缩 / 校验 / Hash类SHA1计算SHA-1Hash 值4、压缩 / 校验 / Hash类SHA2计算SHA-2Hash 值5、身份证信息类GET_IDCARD_AGE根据身份证号码计算当前年龄5、身份证信息类GET_IDCARD_BIRTHDAY根据身份证号码返回出生日期5、身份证信息类GET_IDCARD_SEX根据身份证号码返回性别6、账号 / 元数据判断类GET_USER_ID获取当前账号的账号 ID6、账号 / 元数据判断类MAX_PT返回分区表一级分区的最大值6、账号 / 元数据判断类PARTITION_EXISTS判断指定分区是否存在6、账号 / 元数据判断类TABLE_EXISTS判断指定表是否存在7、排序 / 采样类ORDINAL排序后返回指定位置的值7、排序 / 采样类SAMPLE对数据进行采样并过滤未命中的行8、Map转换类STR_TO_MAP按分隔符将字符串解析成MAP9、行列转换类STACK将一组参数拆分成指定行数9、行列转换类TRANS_ARRAY将分隔符数组字符串拆成多行9、行列转换类TRANS_COLS将不同列拆分成多行10、随机ID类UNIQUE_ID返回高效随机 ID10、随机ID类UUID返回随机 ID二、条件判断类函数 / 表达式功能BETWEEN AND判断是否位于区间CASE WHEN多条件分支IF二选一条件判断FAILIF条件校验与主动报错1、定义BETWEEN AND判断一个值是否位于指定闭区间。CASE WHEN支持多个条件分支。IF根据一个布尔条件返回两个结果之一。FAILIF用于数据校验不满足预期时可主动抛出错误。2、区别对比区别BETWEEN AND只判断区间IF最适合简单二选一CASE WHEN适合多条件、多分支FAILIF主要用于数据校验和中断任务IF与CASE WHEN两个结果二选一优先IF条件多、分支多优先CASE WHEN。3、简单记忆BETWEEN 在不在区间。IF 二选一。CASE WHEN 多分支。FAILIF 条件不符合就报错。4、案例-- 结果trueSELECT1200BETWEEN1000AND1500;-- 结果highSELECTCASEWHEN12001000THENhighELSElowEND;-- 结果highSELECTIF(12001000,high,low);-- 数据异常时主动报错SELECTFAILIF(order_cnt0,order_cnt不能小于0)FROMsource_table;三、类型转换类函数功能CAST强制类型转换SAFE_CAST安全类型转换1、定义CAST把表达式转换成指定目标类型非法转换可能直接报错。SAFE_CAST尝试转换转换失败时返回NULL而不是让 SQL 失败。2、区别对比区别CAST转换失败可能报错SAFE_CAST转换失败返回NULL使用场景数据质量可控用CAST脏数据较多时SAFE_CAST更稳妥3、简单记忆CAST 强转。SAFE_CAST 转不了就NULL。4、案例-- 结果123SELECTCAST(123ASBIGINT);-- 结果NULLSELECTSAFE_CAST(abcASBIGINT);四、NULL处理类函数功能COALESCE多参数取第一个非 NULLNVLNULL 时给默认值NULLIF相等时变 NULL1、定义COALESCE从左到右返回第一个非NULL值。NVL第一个参数为NULL时返回第二个参数。NULLIF两个参数相等时返回NULL否则返回第一个参数。2、区别对比区别COALESCE可传多个候选值NVL主要是一个值 一个默认值NULLIF不是填补 NULL而是把某些值转换成 NULL3、简单记忆COALESCE 多选一找第一个非空。NVL 空了就兜底。NULLIF 一样就置空。4、案例-- 结果ASELECTCOALESCE(NULL,NULL,A,B);-- 结果0SELECTNVL(NULL,0);-- 结果NULLSELECTNULLIF(100,100);五、压缩 / 校验 / Hash类函数功能COMPRESSGZIP 压缩DECOMPRESSGZIP 解压CRC32CRC32 校验HASH通用 HashSHASHA-1SHA1SHA-1SHA2SHA-21、定义这一类用于数据压缩、校验、散列和摘要计算。2、区别对比区别COMPRESS / DECOMPRESS压缩 / 解压使用GZIPCRC32更偏数据完整性校验HASH通用散列支持多个参数SHA / SHA1都计算SHA-1SHA2支持更强的SHA-2系列需要注意Hash 值相同不代表输入一定相同存在哈希碰撞可能。3、简单记忆COMPRESS压。DECOMPRESS解。CRC32校验。HASH通用散列。SHA / SHA1 SHA-1。SHA2 SHA-2。4、案例-- 返回 BINARY 压缩结果SELECTCOMPRESS(hello);-- 结果hello, worldSELECTCAST(DECOMPRESS(COMPRESS(hello, world))ASSTRING);-- 结果2743272264SELECTCRC32(ABC);-- 返回 Hash 值SELECTHASH(A,100);-- 返回 SHA-1SELECTSHA(abc);-- 返回 SHA-1SELECTSHA1(abc);-- 返回 SHA-256SELECTSHA2(abc,256);六、身份证信息类函数功能GET_IDCARD_AGE获取年龄GET_IDCARD_BIRTHDAY获取出生日期GET_IDCARD_SEX获取性别1、定义这三个函数都基于中国居民身份证号码进行解析并会校验身份证合法性。2、区别函数返回GET_IDCARD_AGE当前年龄BIGINTGET_IDCARD_BIRTHDAY出生日期DATETIMEGET_IDCARD_SEXM或FSTRING如果身份证校验不通过返回NULL。3、简单记忆AGE年龄。BIRTHDAY出生日期。SEX性别。4、案例-- 返回身份证对应年龄SELECTGET_IDCARD_AGE(idcard_no)FROMuser_table;-- 返回身份证对应出生日期SELECTGET_IDCARD_BIRTHDAY(idcard_no)FROMuser_table;-- 返回 M 或 FSELECTGET_IDCARD_SEX(idcard_no)FROMuser_table;七、账号 / 元数据判断类函数功能GET_USER_ID当前账号 IDMAX_PT最大一级分区PARTITION_EXISTS分区是否存在TABLE_EXISTS表是否存在1、定义这一类主要用于获取当前账号信息或在 SQL 中判断 MaxCompute 表、分区元数据。2、区别函数使用场景GET_USER_ID获取当前执行账号MAX_PT获取表最大一级分区值PARTITION_EXISTS判断具体分区是否存在TABLE_EXISTS判断表是否存在你日常使用最多的是MAX_PT(table_name)获取最新分区。3、简单记忆GET_USER_ID 我是谁。MAX_PT 最大分区。PARTITION_EXISTS 分区在不在。TABLE_EXISTS 表在不在。4、案例-- 返回当前账号 IDSELECTGET_USER_ID();-- 返回最大一级分区例如 20260927SELECTMAX_PT(udi.src_shop_df);-- 判断指定分区是否存在SELECTPARTITION_EXISTS(udi.src_shop_df,ds20260927);-- 判断表是否存在SELECTTABLE_EXISTS(udi.src_shop_df);八、排序 / 采样类函数功能ORDINAL排序后取指定位置值SAMPLE数据采样1、定义ORDINAL将输入变量排序后返回指定位置的值。SAMPLE按采样规则过滤输入数据。2、区别两个函数用途完全不同函数主要用途ORDINAL多个值中按大小取第 N 个SAMPLE从大量数据中抽取部分记录3、简单记忆ORDINAL 排序取位置。SAMPLE 抽样。4、案例-- 从输入值排序后取指定位置SELECTORDINAL(2,10,30,20);-- 按采样条件过滤数据SELECT*FROMsource_tableWHERESAMPLE(10,1);九、Map转换类函数功能STR_TO_MAPSTRING 转 MAP1、定义STR_TO_MAP将带固定 Key-Value 分隔格式的字符串解析为MAP。例如a:1,b:2,c:3可以转成{a:1,b:2,c:3}2、区别与MAP函数相比函数区别MAP已经有独立 Key、Value 时直接构造STR_TO_MAP原始数据是一个完整字符串需要先解析3、简单记忆STR_TO_MAP String → Map。4、案例-- 结果{a:1,b:2,c:3}SELECTSTR_TO_MAP(a:1,b:2,c:3,,,:)ASresult;十、行列转换类函数功能STACK参数组拆成多行TRANS_ARRAY分隔字符串拆成多行TRANS_COLS多列转多行1、定义这一类都用于改变数据的行列形态。STACK把指定参数组拆成固定行数。TRANS_ARRAY把列中以分隔符保存的数组字符串拆为多行。TRANS_COLS把多个不同列转换成多行。2、区别对比区别STACK静态参数组转多行TRANS_ARRAY一个字符串列内部拆多行TRANS_COLS多个列转成多行3、简单记忆STACK 堆成多行。TRANS_ARRAY 字符串数组拆行。TRANS_COLS 列转行。4、案例-- 结果拆成 2 行SELECTSTACK(2,A,100,B,200);-- 将逗号分隔字段拆成多行SELECTTRANS_ARRAY(2,,,shop_id,tag_list)FROMshop_table;-- 将多个指标列转换成多行SELECTTRANS_COLS(2,metric_a,metric_b)FROMsource_table;十一、随机ID类函数功能UNIQUE_ID高效随机 IDUUID随机 ID1、定义UNIQUE_ID返回随机 ID官方说明运行效率高于UUID。UUID返回随机 UUID。2、区别对比区别UNIQUE_ID更偏高性能唯一标识生成UUID标准随机 UUID日常使用更直观性能官方说明UNIQUE_ID高于UUID3、简单记忆只要随机 IDUUID。更关注执行效率UNIQUE_ID。4、案例-- 返回随机 IDSELECTUNIQUE_ID();-- 返回随机 UUIDSELECTUUID();十二、最容易混淆的函数对比函数组合最核心区别IFvsCASE WHEN简单二选一 vs 多条件分支CASTvsSAFE_CAST失败可能报错 vs 失败返回 NULLNVLvsCOALESCE两参数兜底 vs 多参数取第一个非 NULLNVLvsNULLIFNULL 补值 vs 指定值转 NULLCOMPRESSvsDECOMPRESSGZIP 压缩 vs GZIP 解压CRC32vsHASH校验值 vs 通用散列SHAvsSHA1都是 SHA-1SHA1vsSHA2SHA-1 vs SHA-2MAX_PTvsPARTITION_EXISTS找最大分区 vs 判断指定分区PARTITION_EXISTSvsTABLE_EXISTS判断分区 vs 判断表STR_TO_MAPvsMAP字符串解析 MAP vs 直接构造 MAPSTACKvsTRANS_ARRAY静态值拆行 vs 分隔字符串拆行TRANS_ARRAYvsTRANS_COLS单列数组字符串拆行 vs 多列转多行UNIQUE_IDvsUUID更高效随机 ID vs 标准随机 UUID十三、常用案例1、NULL兜底-- unit_price 为空时取 fixed_priceSELECTNVL(unit_price,fixed_price)ASunit_priceFROMsms_price;2、多级兜底-- 优先取 t1其次 t2最后取 0SELECTCOALESCE(t1.unit_price,t2.unit_price,0)ASunit_priceFROMsource_table;3、安全转换数字-- 非法数字返回 NULL不中断任务SELECTSAFE_CAST(price_strASDOUBLE)ASpriceFROMsource_table;4、最新分区SELECT*FROMudi.src_shop_dfWHEREdsMAX_PT(udi.src_shop_df);5、条件分类SELECTCASEWHENamount1000THEN高WHENamount500THEN中ELSE低ENDASamount_levelFROMorder_table;6、生成Data IDSELECTUUID()ASdata_id,shop_id,shop_nameFROMshop_table;7、判断表是否存在SELECTTABLE_EXISTS(udi.src_shop_df)ASis_exists;8、字符串转MAP-- 结果{platform:美团,city:北京}SELECTSTR_TO_MAP(platform:美团,city:北京,,,:)ASresult;十四、参考资料阿里云 MaxCompute 官方文档https://help.aliyun.com/zh/maxcompute/other-functions