DWD层脚本中常用函数总结

  • cast(amount as decimal(17, 2))

    • CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式,它包括用AS关键字分隔的源值和目标数据类型。

    • 语法:CAST (expression AS data_type),expression 表示原有表达式数据类型,data_type 表示目标类型。

    • decimal(17, 2) 表示总长17位,包括1位小数点和2位小数,也就是说整数位只有14位。

      最终结果举例:

      | amount | cast(amount as decimal(17, 2)) |
      | ------ | ------------------------------ |
      | 100    | 100.00                         |
      | 82.364 | 82.36                          |
      
  • replace(substr(create_time, 1, 10), '-', '')

    • substr 用于截取字符。

      • substr (string,pos,len): 从pos开始的位置,截取len个字符

        • substr('string' ,1,3) :取string左边第1位置起,3字长的字符串。 所以结果为: str
        • substr('string', -1,3):取string右边第1位置起,3字长的字符串。显然右边第一位置起往右不够3字长。结果只能是: g
        • substr('string', -3,3):取string右边第3位置起,3字长的字符串。 结果为: ing
      • SUBSTR(str,pos): pos开始的位置,一直截取到最后

        • substr('string' ,4) : 从右第4位置截取到最后 结果是: ing
    • REPLACE() 字符串替换函数:

      • 把数据库表article中的所有title字段里的w3cschool字符串替换成hello。

        update `article` set title=replace(title,'w3cschool','hello');
        

因此replace(substr(create_time, 1, 10), '-', '') 就是将 create_time 从位置1开始,提取10个字符,组成一个新字符串,并将这个字符串中的'-' 全部替换成空

最终结果举例:

| create_time         | replace(substr(create_time, 1, 10), '-', '') |
| ------------------- | -------------------------------------------- |
| 2020-07-15 18:33:13 | 20200715                                     |
  • trim()、ltrim() 和 rtrim()

    trim移除所有的空格,ltrim即left-trim,把左边空格修剪掉。同理,rtrim即right-trim,把右边的空格修剪掉。

    SELECT TRIM('   Sample   ');
    

    结果:'Sample'

    SELECT LTRIM('   Sample   ');
    

    结果:'Sample '

    SELECT RTRIM('  Sample  ');
    

    结果:' Sample'

  • case ... when ... then

    case
        when sale_channel='1' then '03'
        when sale_channel='2' then '04'
        else '00'
    end as channel_code,
    
    	(case 
    	when m.sale_budget is not null then tsb.sale_budget * exb_sale_budget / m.sale_budget else m.sale_budget 
    end)                  
    as exb_sale_budget,
    

    适用于一个条件判断有多种值的情况下分别执行不同的操作

    详见:https://www.cnblogs.com/martinzhang/p/3220595.html 和 https://www.cnblogs.com/kirinboy/archive/2010/01/11/1644108.html

  • date_format(from_utc_timestamp(current_timestamp(),'PRC'),'yyyy-MM-dd HH:mm:ss')

    • UTC 时间转换为 PRC(北京时间):

      from_utc_timestamp('1970-01-01 00:00:00','PRC')

    • data_format 用于时间格式转换

      date_format(from_utc_timestamp(current_timestamp(), 'PRC'), 'yyyy-MM-dd HH:mm:ss')

所有上面的语句含义就是将当前时间戳先从UTC时间转为北京时间,然后转换成 yyyy-MM-dd HH:mm:ss 的格式。

  • with ... as ...

    WITH AS短语,也叫做子查询部分(subquery factoring),是用来定义一个SQL片断,该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式(CTE)。with ... as ...的结果也可以理解为用一个子查询生成的临时表

    with A as 
    (
    select * from class
    )
    select *from A  
    

    这个语句的意思就是,先执行select * from class 得到一个结果,将这个结果记录为A ,在执行select *from A 语句。A 表只是一个别名。

    也就是将重复用到的大批量 的SQL语句,放到with as 中,加一个别名,在后面用到的时候就可以直接用,这对于大批量的SQL数据能起到优化的作用。

    但需要注意的是:

    • with ... as ... 后面必须直接跟使用了这个别名A的SQL语句(如select、insert、update等),否则,with ... as ... 将失效。

    • with ... as ... 后面也可以跟其他的 with ... as ...,但只能使用一个with,多个 as 中间用逗号(,)分隔,如下面的SQL语句所示:

      with
      cte1 as
      (
          select * from table1 where name like 'abc%'
      ),
      cte2 as
      (
          select * from table2 where id > 20
      ),
      cte3 as
      (
          select * from table3 where price < 100
      )
      select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
      
  • ROW_NUMBER() OVER函数的基本用法用法

    row_number() over (partition by alter_empl_id order by erp_update_time desc) as rn
    
    row_number() over (partition by alter_empl_id order by case when job_indicator = 'P' then 1 else 2 end) as rn
    
  • coalesce() 函数返回列表中的第一个非空值

    SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');  //结果:W3Schools.com
    
  • union 和 union all

    UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

    SELECT column_name(s) FROM table_name1
    UNION
    SELECT column_name(s) FROM table_name2
    

    默认地,UNION 操作符选取不同的值。如果有重复的值,则只会列出一个。如果需要将重复的值列出,请使用 UNION ALL。

    SELECT column_name(s) FROM table_name1
    UNION ALL
    SELECT column_name(s) FROM table_name2
    
  • IF 表达式

    IF( expr1 , expr2 , expr3 )
    

    expr1 的值为 TRUE,则返回值为 expr2 expr1 的值为FALSE,则返回值为 expr3

    if(substr(soa.xsbm, 1, 2) = '92', trim(cxy.shop_code), substr(soa.xsbm, 1, 8)) as offline_shop_code,
    if(substr(soa.xsbm, 1, 2) = '92', cxy.branch_code, soa.fbid)                   as offline_branch_code,
    cast(sum(cast(if(b.code='48',a.syje,'0') as decimal(17,2))) as String) as 'qje48',
    

DWD层SQL脚本的规律:

  • 先清空 DWD 中需要做数据导入的表

    truncate table dwd.dwd_activity_xdsh_coupon_number;
    
  • 再从 ODS 层中的表中复制所有的列插入到 DWD 的表中:

    insert into dwd.dwd_activity_xdsh_coupon_number[(所有列字段)] [partition (想用于分区的字段)]
    select 所有列字段 from ods.ods_activity_xdsh_coupon_number;
    

注:所有的 [] 号包裹的都是可选值