【SQL中with的用法及代码经验分享】

SQL中with的用法及代码经验分享

文章目录

前言

提示：这里可以添加本文要记录的大概内容：

注：WITH AS短语，也叫做子查询部分，可以称为独立查询模块，即定义一个SQL的独立模块后，该SQL模块可以被整个SQL语句所用到。有的时候，with as是为了提高SQL语句的可读性，减少关联太多表产生的冗余。

提示：以下是本篇文章正文内容，下面案例可供参考

一、with的用法

1.基本框架

代码如下（示例）：

with xx_v1 as(
sql查询语句1),
     xx_v2 as(
sql查询语句2),
     xx_vn as(
sql查询语句n
)

注：如果需要关联多个表，最好在查询语句块时设置相同的以及所需多表关联的字段，方便后续汇总业务。

二、业务员各项业务办理SQL监控统计项目分享

1.口径说明

（1）需求口径说明
①主要汇总业务员多项业务办理量情况（目前重点关注其中五种）。
②区分业务员信息关联表和六个业务表。
（2）六个业务表名：业务表_VN（N=1-6）

2.业务员信息表字段（关联表_V1 ）

城市业务员名称业务员工号BRANCHOPR_NAMOPR_ID

3.Sql脚本编写

（1）变量赋值
v_day：办理日期：xxxx-xx-xx（例：20230223）
v_month：办理月份：xxxx-xx（例：202302）
注：由于该系统上有变量赋值组件，可通过设置参数自动赋值。
（2）sql代码

--数据处理

--分别统计各项业务当天当月业务

with t1 as(
    select a.branch,a.opr_id
    ,count(distinct case when b.CONT_ACCEPT_DT=${v_day} then b.USR_NBR end) as day_usr_xx1_cnt                     --统计当日销量且号码去重
    ,count(distinct case when substr(b.CONT_ACCEPT_DT,1,6)=${v_month} then b.USR_NBR end) as mon_usr_xx1_cnt      --统计当月销量且号码去重
      from 关联表_V1 a
      left join 业务表_V2  b
      on a.opr_id=b.EMP_ID and b.day=${v_day}
      group by a.branch,a.opr_id
),

t2 as(
    select a.branch,a.opr_id
    ,count(distinct case when b.accept_dt=${v_day} then b.usr_id end) as day_usr_xx2_cnt           --统计当日销量且号码去重
    ,count(distinct case when substr(b.accept_dt,1,6)=${v_month} then b.usr_id end) as mon_usr_xx2_cnt    --统计当月销量且号码去重
    from 关联表_V1 a
      left join (
          select distinct accept_dt,accept_opr_id,usr_id
          from (
              select a.USR_ID,a.BASS_PRDCT_CD,a.ACCEPT_DT,b.ACCEPT_OPR_ID
              from 业务表_V3 a
              join 业务表_V3   b   --自连接

              b on a.ORDER_ID=b.ORDER_ID and b.month=${v_month} 
              where a.month=${v_month} 
          ) a
          join (select bass_prdct_cd,row_number()over(partition by bass_prdct_cd order by stat_dt desc) rn    --由于该表无号码字段，以办理方案进行去重处理
                        from 业务表_V4
          ) b
          on a.bass_prdct_cd=b.bass_prdct_cd and b.rn=1
    ) b on a.opr_id=b.accept_opr_id
      group by a.branch,a.opr_id
),

t3 as(
    select a.branch,a.opr_id
    ,count(distinct case when b.ACPT_DT=${v_day} then b.USR_ID end) as day_usr_xx3_cnt   --统计当日销量且号码去重
    ,count(distinct case when substr(b.ACPT_DT,1,6)=${v_month} then b.USR_ID end) as mon_usr_xx3_cnt    --统计当月销量且号码去重
      from 关联表_V1 a
      left join 业务表_V5

      b on a.opr_id=b.STAFF_ID and b.month=${v_month} 
      group by a.branch,a.opr_id
),

t4 as(
    select a.branch,a.opr_id
    ,count(distinct case when b.accept_dt=${v_day} then b.usr_id end) as day_usr_xx4_cnt
    ,count(distinct case when substr(b.accept_dt,1,6)=${v_month} then b.usr_id end) as mon_usr_xx4_cnt
      from 关联表_V1 a
      left join 业务表_V6

      b on a.opr_id=b.accept_opr_id  and b.day=${v_day}
      group by a.branch,a.opr_id
),

t5 as(
    select a.branch,a.opr_id
    ,count(distinct case when b.innet_dt=${v_day} then b.usr_nbr end) as day_usr_xx5_cnt
    ,count(distinct case when substr(b.innet_dt,1,6)= ${v_month}  then b.usr_nbr end) as mon_usr_xx5_cnt
    from 关联表_V1 a
    left join
    (
      select
      usr_nbr,  
      innet_dt,
      INNET_MO,
      OPR_STAFF_ID
      from
      业务表_V7
      where INNET_MO=${v_month} 
     ) b
    ON
    a.OPR_ID=b.OPR_STAFF_ID
    GROUP BY
    a.BRANCH,a.OPR_ID
 )

--数据入库
insert overwrite table SCB_A_REF_HSH_OPR_ID_V2 partition(day=${v_day})
select 
    a.branch,a.opr_nam,a.opr_id
    ,b1.day_usr_xx1_cnt,b2.day_usr_xx2_cnt,b3.day_usr_xx3_cnt,b4.day_usr_xx4_cnt,b5.day_usr_xx5_cnt
    ,(b1.day_usr_xx1_cnt+b2.day_usr_xx2_cnt+b3.day_usr_xx3_cnt+b4.day_usr_xx4_cnt+b5.day_usr_xx5_cnt) as day_usr_all_cnt
    ,b1.mon_usr_xx1_cnt,b2.mon_usr_xx2_cnt,b3.mon_usr_xx3_cnt,b4.mon_usr_xx4_cnt,b5.mon_usr_xx5_cnt
    ,(b1.mon_usr_xx1_cnt+b2.mon_usr_xx2_cnt+b3.mon_usr_xx3_cnt+b4.mon_usr_xx4_cnt+b5.mon_usr_xx5_cnt) as mon_usr_all_cnt
from 关联表_V1 a
left join t1 b1 on a.opr_id = b1.opr_id
left join t2 b2 on a.opr_id = b2.opr_id
left join t3 b3 on a.opr_id = b3.opr_id
left join t4 b4 on a.opr_id = b4.opr_id 
left join t5 b5 on a.opr_id = b5.opr_id 
where a.opr_id is not null and a.opr_id<>''
;

--最后在系统中配置各种组件，让程序自动扫描管理的业务表，如若该天各个业务表数据到达，则自动运行并推送执行内容。

总结

提示：这里对文章进行总结：

①with as的好处是增加了sql的易读性，如果业务中涉及到多个子连接，通过区分各个语句块，结构会更清晰。
②更重要的是：这是各个独立语句，可以做到“一次运行，多次使用”，根据需求提取主要业务数据，来提高的代码的运行效率，达到程序执行的效率。