| SPLIT_PART | 切分字符串 | select etl_date,split_part(etl_date,‘-’,1) as new_etl_date from analytical_martph_omtdb.TM_OMT_Inmkt_Sales_Agg_NOT_ONC; | 2023-05-25 20:01:51 | 2023 |
| SUBSTRING | 截取字符串(column,1,4):从1开始截取到4位置停止返回所截取到的字符串 | select etl_date,substring(etl_date,1,4) as new_etl_date from analytical_martph_omtdb.TM_OMT_Inmkt_Sales_Agg_NOT_ONC; | 2023-05-25 20:01:51 | 2023 |
| TRIM | 如果不写指定字符,则删除前面空格和后面空格或删除掉指定字符后返回字符串 | select trim(‘"’ FROM ‘“dog”’); select trim(from " gg "); | “dog” | dog |
| CURRENT_TIMESTAMP | 获取当前时间戳 | SELECT CURRENT_TIMESTAMP; | | 2023-05-25 20:01:51 |
| EXTRACT | 返回表达式中的日期或时间部分 | select extract(day from timestamp ‘2009-09-09 12:08:43’); | | 9 |
| DATE_PART | 返回表达式中的日期部分 | SELECT DATE_PART(week, timestamp ‘2009-09-09 12:08:43’); | | 37 |
| AGE | 返回两个表达式中Year,month,day,hour,minute,s。差异的部分。 | Select age(‘2023-05-05 12:33:22’,’2022-06-06 12:33:22’) | | 0 years 10 mons 29 days |
| NOW | 返回当前时间 | SELECT NOW(); | | 2023-05-05 12:33:22 |
| DECODE | 将一个特定值替换成另一个特定值或默认值 | select marketname,decode(marketname,‘零售连锁总部’,‘1’,‘UnKnown’) as decode_marketname from analytical_martph_OMTDB.TM_OMT_Kan_Inmkt_Sales_Agg; | 零售连锁总部，线上商业零售 | 1，UnKnown |
| NVL | 返回列中第一个不为null的列,如果全为null那么返回null.表达式类型必须全都一致,不然报错 | select nvl(product,year) from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg; | null,2018 | 2018 |
| NVL2 | 如果表达式为NULL返回第二个参数，反之返回第一个参数 | Select nvl2(null,1,0),nvl2(2,1,0) | null,2 | 0,1 |
| CEIL | 向上取整 | select ceil(123,22); | 123.22 | 124 |
| FLOOR | 向下取整 | select floor(123.78); | 123.78 | 123 |
| GREATEST/LEAST: | 从任意表达式的列表中返回最大值或最小值 | select year, greatest(year) from from analytical_martph_OMTDB.TM_OMT_Kan_Inmkt_Sales_Agg; | 2019,2020,2021 | 2021 |
| ASCII | 返回字符串的第一个字符的ASCII码 | select ascii(‘year’); | y | 127 |
| CHR | 返回与输入的 ASCII 码匹配的字符 | select chr(127); | 127 | y |
| CONCAT | 拼接两个表达式,并返回 | select yearmonth,concat(year,month) as new_yearmonth from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg; | 2018,02 | 201802 |
| NEXT_DAY | 返回给定日期后第一个表达式的日期数 | Select next_day(‘2023-05-29 19:22:00’,’Mon’); | 2023-05-29 | 2023-06-05 |
| LAST_DAY | 返回该月最后一天的日期 | Select last_day(‘2023-05-19’); | 2023-05-19 | 2023-05-31 |
| ADD_MONTHS | 将指定的月数添加到日期或时间戳 | select add_months(‘2023-05-24 19:22:00’,2) | 2023-05-24 19:22:00 | 2023-07-24 19:22:00 |
| MONTHS_BETWEEN | 返回两个日期之间相隔的月数 | Select months_between(‘2023-06-19’,’2023-05-19’); | | 1 |
| ROUND | 四舍五入,round(123,22,2) 保留两位小数 | select round(‘123.0000’); select round(‘123.66,1); | 123.0000,123.66 | 123,123.7 |
| MD5 | MD5加密 | select md5(‘dssddsds’); | dssddsds | sssssss |
| TRUNC | 将表达式的值截取到整数 | select trunc(123.23); | 123.23 | 123 |
| TO_CHAR | 将时间戳或数值转换成字符串类型 | Select to_char(timestamp ‘2023-05-22 02:28:16’,’yyyy-mm-dd hh:mm:ss’); | | |
| TO_DATE: | 将字符串类型转换成日期型 | select to_date(‘20190630’, ‘YYYYMMDD’); | | |
| TO_NUMBER | 将字符串转换成数值。格式 ‘99D999’ 指定要转换的字符串包含五位数，其中小数点在第三位 | select to_number(‘154.8’, ‘999D9’); | | |
| POSITION | 返回指定字符串在字符串中的位置 | select position (‘dag’ in ‘asdag’); | asdag | 3 |
| REPLACE | 把表达式中的旧字符替换成新字符 | select year,month,replace(year,2018,2000) as new_year,replace(month,0,1) as new_month from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg order by year,month; | 2018,0 | 2020,1 |
| MOD | 取两个表达式的余数 | select mod(20,5); -----0 select mod(23,5); ----- 3 | | 0,3 |
| CAST | 修改数据类型变成指定数据类型 | select cast(year as nvarchar) from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg; | | |
| COALESCE | 与nvl一样，返回第一个不为空的表达式 | | | |
| DATE_ADD | 按照给定年,月,日,小时,分钟,增加并返回值 | select dateadd(year,2,etl_date) as new_etl_date_year,dateadd(month,2,etl_date) as new_etl_date_month,dateadd(day,2,etl_date) as new_etl_date_day,dateadd(hour,2,etl_date) as new_etl_date_hour,dateadd(minute,2,etl_date) as new_etl_date_minute from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg; | 2018-05-05 10:10:10 | 2020-07-07 12:12:12 |
| LENGTH | 返回字符串的长度与len相同 | select marketname,length(marketname) as len from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg where yearmonth = ‘201802’; | 零售连锁总部 | 5 |
| UPPER | 将字符串变成大写 | select upper(‘abc’); | abc | ABC |
| LOWER | 将字符串变成小写 | select lower(‘ABC’); | ABC | abc |
| CURRENT_DATE | 以默认格式 YYYY-MM-DD 返回当前日期 | select current_date; | | 2023-05-05 |
| CURRENT_TIME | 返回当前时间 | select current_time; | | 12:12:09 |
| DATE_DIFF | 返回两个日期或表达式的日期部分之间的差异 | select datediff(day, start_date, end_date) as duration from date_table; | 原始数据 | 返回值 |
| DATE_TRUNC | 返回时间戳中的日期 | Select date_trunc(‘day’,’2023-05-22 02:28:16’); | 2023-05-22 | 22 |
| CONVERT | CONVERT 与cast函数相似，但不会转换源表中的类型 | select convert(varchar,year) from table_name | | |
| CASE | Case when then else end | Select year,Case whenYear <= 2018 then ‘group2018’WhenYear > 2018 and year <= 2019 then ‘group2019’WhenYear> 2019 and year <= 2020 then ‘group2020’Else ‘groupother’End as year_groupFrom analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Sales_Agg order by 1 desc; | | |
| REGEXP_INSTR: | 在字符串中搜索正则表达式模式并返回指示匹配子字符串的开始位置的整数。如果未找到匹配项，此函数将返回 0 | Select regexp_instr(‘ddd222’,’[0-9]’) | ddd222 | 4 |
| REGEXP_REPLACE: | 在字符串中搜索正则表达式模式并将该模式的每个匹配项替换为指定字符串 | SELECT f1,regexp_replace(f1,‘[A-Z]{3}’,‘ABC’) from enriched_prestage_omtdb.tp_turnover_rate_init where f1 = ‘STH’; | STH | ABC |
| REGEXP_SUBSTR | 通过在字符串中搜索正则表达式模式，返回字符串中的字符。 | Select regexp_substr(‘ddd222’,’[0-9].*’) | | 222 |
| LAG | LAG 窗口函数返回位于分区中当前行的上方（之前）的某个给定偏移量位置的行的值。 | select yearmonth,year,region,product,lag(product,1) over (order by yearmonth,year,region) from analytical_martph_omtdb.tm_omt_we_inmkt_sales_agg where year = 2022 order by yearmonth,year,region | | |
| LEAD | LEAD 窗口函数返回位于分区中当前行的下方（之后）的某个给定偏移量位置的行的值。 | Select foodtype,lead(foodtype) over (order by foodtype) from food_sex | 原始数据 | 返回值 |
| LISTAGG | 聚合函数,根据分组后的order by 对该分组的行进行排序,然后将返回值串联成一个字符串,最后进行返回.注意的地方,如果语句包含多个within group ,那么每个within group后的order by必须是相同值 | select listagg(yearmonth,‘,’) within group(order by yearmonth desc) from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg; | 202012 | 202012,202012,202011 |
| RANK | 根据order by 的结果进行排名 | select distinct yearmonth,rank() over(order by yearmonth) as rank_yearmonth from analytical_martph_OMTDB.TM_OMT_Kam_Inmkt_Salaes_Agg order by yearmonth,rank_yearmonth; | 201800,201801 | 201800,1 201801,3 |
1518
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
