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

2024-01-17 16:03:32 作者:欧亿体育

文章目录

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 )

城市 业务员名称 业务员工号
BRANCH OPR_NAM OPR_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的易读性,如果业务中涉及到多个子连接,通过区分各个语句块,结构会更清晰。
②更重要的是:这是各个独立语句,可以做到“一次运行,多次使用”,根据需求提取主要业务数据,来提高的代码的运行效率,达到程序执行的效率。

在线咨询 拨打电话

电话

010-67916526

微信二维码

微信二维码