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

sql 数据库 java 2023-10-26 08:10:53 410人浏览安东尼

摘要

sql中with的用法及代码经验分享文章目录 SQL中with的用法及代码经验分享前言一、with的用法1.基本框架二、业务员各项业务办理SQL监控统计项目分享1.口径说明2.业务员

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

文章目录

SQL中with的用法及代码经验分享
前言
一、with的用法
- 1.基本框架
二、业务员各项业务办理SQL监控统计项目分享
总结

前言

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

注：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_cntfrom 关联表_V1 aleft join t1 b1 on a.opr_id = b1.opr_idleft join t2 b2 on a.opr_id = b2.opr_idleft join t3 b3 on a.opr_id = b3.opr_idleft 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<>'';--最后在系统中配置各种组件，让程序自动扫描管理的业务表，如若该天各个业务表数据到达，则自动运行并推送执行内容。