一条有思考SQL编写（Oracle数据库，DECODE函数）

今天在工作中遇到一个比较有意思的业务场景，不知道大家平时是怎么解决。（Oracle数据库）

后台管理小功能，统计系统每一天的客户转化率，也就是 当天注册并已经下单的客户数/当天注册的总客户数

 

 

返回给前端的数据格式是：

{
    "code": 200,
    "data": [
        {
            "time": "2021-07-22",
            "ratio": "60%"
        },
        {
            "time": "2021-07-23",
            "ratio": "0%"
        },
        {
            "time": "2021-07-26",
            "ratio": "100%"
        }
    ]
}

这里涉及了两张表，一张是注册表，一张是订单表，根据注册表的用户id去订单表查询，如果有数据，证明这个人已经下单了。

参考了同事的类似的业务场景实现：

他是根据前端传的时间范围，在java业务层遍历这个时间范围，拿到每一天的相关数据，比如说，先查询出这天注册并已经下单的客户数，再查询出当天注册的总客户数，在业务层进行相除，封装号数据进行返回。

这样的好处就是sql好写，很容易的两条sql，但是坏处就是发起的sql请求太多次了，一天就是2次sql，一年就是730，十年就是7300次sql，数据量一大这个接口肯定会有问题。

 

那我们能不能用一次sql来解决这个问题（Oracle数据库）

我的思路是：

所以首先是按照用户id将订单表左连接到注册表，然后根据注册表的注册时间进行按天分组，注意得用左连接，不用全连接，这样没有购买的注册数据才会出现。

然后在以每一天分组中，统计组内的数据总数也就是当天注册的总客户数，再统计组内订单状态为购买的数据，也就是当天注册并已经下单的客户数，两者相除

第一步：订单表左连接到注册表，然后根据注册表的注册时间进行按天分组

SELECT  TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd') AS TIME FROM  SYS_USER
LEFT JOIN SYS_ORDER ON SYS_USER.USER_ID=SYS_ORDER.USER_ID
GROUP BY TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd')

 

第二步：统计出各组的总条数  ，也就是当天注册的总客户数

SELECT  TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd') AS TIME , COUNT(*) AS TOTAL  FROM  SYS_USER
LEFT JOIN SYS_ORDER ON SYS_USER.USER_ID=SYS_ORDER.USER_ID
GROUP BY TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd')

 我们会发现，总客户数数量不对，这个问题是因为一个客户可能下了多次单，使用订单表有很多条数据，当左链接的时候，总条数就增加了。

那应该如何解决？

应该把订单表中的同个用户id进行分组排序，取第一条数据。

这里用到oracle开窗函数：先分组，再按某字段排序，取分组内第一条数据

select  t.*  
   from (select a.*, row_number() over(partition by 需要分组的字段 order by 需要排序的字段 desc) rw  
           from 表 a) t  
  where t.rw = 1  

 

 

第三步：这样我们就可以利用子查询，把sql再整合一下。

SELECT  TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd') AS TIME , COUNT(*) AS TOTAL  FROM  SYS_USER
LEFT JOIN （
select  t.*  
 from (select a.*, row_number() over(partition by USER_ID order by STATUS desc) rw  
     from SYS_ORDER a) t  
 where t.rw = 1  

)

GROUP BY TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd')

 

 

第四步：重要的一步，如何去查询出当天注册并已经下单的客户数，我们知道，订单表有状态，状态为0就是订单完成。

所以就转化成：查询分组中，订单状态为0的记录总条数。可以借助DECODE函数来实现。关于DECODE函数大家可以自行百度

SELECT  TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd') AS TIME , COUNT(*) AS TOTAL ，COUNT(DECODE(ORDER.STATUS,0,1,NULL)) AS BUY_TOTAL
FROM  SYS_USER
LEFT JOIN (
select  t.*  
　　from (select a.*, row_number() over(partition by USER_ID order by STATUS desc) rw  
 　　　　from SYS_ORDER a) t  
  where t.rw = 1  
)

GROUP BY TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd')

DECODE(ORDER.STATUS,0,1,NULL) 表示：ORDER.STATUS这个字段如果等于0那这个函数结果就是1，如果不等于0结果为NULL，我们知道COUNT（*）是不统计null的

 

第五步：相除

SELECT  TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd') AS TIME , ROUND(COUNT(DECODE(ORDER.STATUS,0,1,NULL))/COUNT(*)*100,2)||'%' AS RATIOAS BUY_TOTAL 
FROM SYS_USER
LEFT JOIN (
select  t.*  
　　from (select a.*, row_number() over(partition by USER_ID order by STATUS desc) rw  
　　　　　from  SYS_ORDER a) t  
where t.rw = 1  
)
GROUP BY TO_CHAR(SYS_USER.DATE,'yyyy-mm-dd')

 

 

 

这样的相同的业务场景一条sql就可以实现，不用在代码业务层进行循环遍历，不仅仅减少代码也优化了接口的性能。