SQL语句中with函数用法

2022/07/23

概述

with 语句相当于建立了一张 临时虚拟表

即利用with子句为子查询的数据集作为一个内存临时表. 在内存中解析,提高执行效率.,并且提高SQL语句的可读性,用完即销毁

语法

可以同时定义多个临时表

With 
	Subtable1 as (select 1...), //as和select中的括号都不能省略
	Subtable2 as (select 2...), //后面的没有with,逗号分割,同一个主查询同级别地方,with子查询只能定义一次
	…
	Subtablen as (select n...) //与下面的实际查询之间没有逗号
Select …

with 的优点

增加了sql的易读性,如果构造了多个子查询,结构会更清晰;

更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标

“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标

with 的用法

  • WITH子句可以在开始时使用SELECT,UPDATE和DELETE语句:
with...select...
with...update...
with...delete...
  • WITH子句可以在子查询或派生表子查询的开头使用:
    select...where id in(with...select...)
    select * from (with...select...) as drived_table
    
  • WITH可以在SELECT包含SELECT子句的语句之前使用子句:
    create table...with...select...
    create view...with...select...
    insert...with...select...
    replace...with...select...
    

例子

之前sql

之前写的计算不达标率的sql语句,不易维护 如果要增加新的渠道及阈值,不止修改一处,对应不好的话,还容易出错

SELECT 	install_date,
       	SUM(case
               when (source = 'Facebook' and d3_roi_c < 0.04) or
                    (source = 'Google' and d3_roi_c < 0.03) 
		then cost
		else 0 end) as "不达标花费",
       	SUM(case
               when (source = 'Facebook' and d3_roi_c >= 0.04) or
                    (source = 'Google' and d3_roi_c >= 0.03)
		then cost
		else 0 end) as "达标花费",
		"不达标花费" + "达标花费"               as "总花费",
		case when "总花费" = 0 then 0 else "不达标花费" / "总花费" end   as "不达标率"
FROM roi_table
WHERE install_date BETWEEN '2022-05-01' AND '2022-07-28'
GROUP BY install_date
ORDER BY install_date

with改造后的sql

改造之后就灵活了很多

with conf as (select 'Android' os, 'Facebook' source, 0.04 ratio
              union all
              select 'iOS', 'Facebook', 0.04
              union all
              select 'Android', 'Google', 0.03
              union all
              select 'iOS', 'Google', 0.03           
),
     data as (
         select CASE WHEN SUM(NVL(CASE WHEN current_date - install_date - 1 >= 3 then cost else 0 end, 0)) = 0 THEN 0 
		 	ELSE SUM(NVL(d3_revenue_c, 0)) / SUM(NVL(CASE WHEN current_date - install_date - 1 >= 3 then cost else 0 end, 0))::NUMERIC(38, 6) END AS d3_roi_c
              ,NVL(SUM(cost), 0) AS cost
              ,install_date AS install_date
              ,os as os
              ,install_source as source
              ,campaign as campaign
         FROM roi_table
         WHERE install_date BETWEEN '2022-06-01' AND '2022-06-05'
         GROUP BY install_date, install_source, os, campaign
     )
select install_date,
       sum(case when d3_roi_c < ratio then cost else 0 end)  as "不达标花费",
       sum(case when d3_roi_c >= ratio then cost else 0 end) as "达标花费",
       "不达标花费" + "达标花费"                                as "总花费",
       case when "总花费" = 0 then 0 else "不达标花费" / "总花费" end   as "不达标率"
from (
         select data.install_date, data.d3_roi_c, data.cost, data.os, data.campaign, conf.ratio
         from data inner join conf 
		 on case
         	when data.os = conf.os and data.source = conf.source then 1
            when concat_ws('_', data.os, data.source) not in(select concat_ws('_', os, source) from conf) and conf.os = 'default' and conf.source = data.source then 1
            when data.source not in (select conf.source from conf) and conf.os = data.os and conf.source = 'default' then 1
            else 0 end = 1
     )
group by install_date
order by install_date

Post Directory