今天在作業中遇到一個比較有意思的業務場景，不知道大家平時是怎么解決，（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就可以實作，不用在代碼業務層進行回圈遍歷，不僅僅減少代碼也優化了介面的性能，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/308505.html
標籤：Oracle
上一篇：18Oracle入門