一次ORACLE OVER()开窗函数的使用经历

前景：有一张标的表（loan），用户每做一笔借款请求，都会产生一条记录，每一条借款记录都会有一个状态。比如CLOSED（已结标），IN_PROPRESS（还款中），READY（已满标），FAILED（已流标），每一条记录都是一个“标的”。每一个标的有一个唯一的id，是标的表的主键，同时还有一个borrower_id，代表借贷人。同一个借贷人，可以有多笔借贷。

然后我编写sql想要实现的目标（需求）是：取出标的表的每一条记录，同时再加一个字段，这个字段是一个记录数，记录的这个标的对应的borrower_id对应的用户在标的表里的记录数，这个记录数有个条件：
loan.loan_status IN (‘OPEN’,’READY’,’IN_PROGRESS’,’OVER_DUE’,’READY_HF_CON’)

这里取出的每一条标的记录都是单独的，但是要count()的记录并不是唯一的，听别人说要用开窗函数。那么我就来用一下！

这是我第一次写的SQL：

SELECT l.id AS "id", l.loan_title AS "loanTitle",
l.contact_amount AS "contactAmount", l.interest AS "interest", 
l.loan_status AS "loanStatus",
l.loan_months AS "loanMonths", l.finished_ratio AS "finishedRatio",l.create_time AS "createTime",
COUNT(0) OVER() AS "noPayOff"
FROM loan l, user_info ui
WHERE l.borrower_id = ui.user_id AND l.loan_status IN ('OPEN','READY','IN_PROGRESS','OVER_DUE','READY_HF_CON')
ORDER BY l.id DESC

加粗的那段代表了把count()函数开窗了。。。因为count()只能返回一条，只有一个具体值，但是我前半段sql返回是多条记录，因为一条对多条，需要开窗。（好像很6的样子。。。，谁叫我菜呢，第一次听说。。。）

可以看到，noPayOff字段显示的是5293，且所有记录一样，代表记录数。

但是这并不是我想要的，我要的count() 是count同一个borrower_id在标的表的记录数，即count(borrower_id)。于是我写了

SELECT l.id AS "id", l.loan_title AS "loanTitle",
l.contact_amount AS "contactAmount", l.interest AS "interest", 
l.loan_status AS "loanStatus",l.borrower_id, 
l.loan_months AS "loanMonths", l.finished_ratio AS "finishedRatio",l.create_time AS "createTime",
COUNT(*) OVER(PARTITION BY l.borrower_id) AS "noPayOff"
FROM loan l, user_info ui
WHERE l.borrower_id = ui.user_id AND l.loan_status IN ('OPEN','READY','IN_PROGRESS','OVER_DUE','READY_HF_CON')
ORDER BY l.id DESC

此处PARTITION BY l.borrower_id 代表的是以borrower_id来分割，划分，效果类似GROUP BY

运行结果如图所示：

好像有点对了。。。
那么我们来验证一下

第一条.

SELECT COUNT(*) FROM loan l, user_info ui
WHERE l.borrower_id = ui.user_id AND l.loan_status IN ('OPEN','READY','IN_PROGRESS','OVER_DUE','READY_HF_CON')
AND l.borrower_id = 2802509
ORDER BY l.id DESC

运行结果：

第二条.

SELECT COUNT(*) FROM loan l, user_info ui
WHERE l.borrower_id = ui.user_id AND l.loan_status IN ('OPEN','READY','IN_PROGRESS','OVER_DUE','READY_HF_CON')
AND l.borrower_id = 1574561
ORDER BY l.id DESC

第十二条.

SELECT COUNT(*) FROM loan l, user_info ui
WHERE l.borrower_id = ui.user_id AND l.loan_status IN ('OPEN','READY','IN_PROGRESS','OVER_DUE','READY_HF_CON')
AND l.borrower_id = 1800626
ORDER BY l.id DESC

好了，3条已经验证完了，已经不会有错了。（就是这么严谨）

然而你以为到了这里问题结束了吗。。。并没有，我发现我把我需要统计的数据的条件写在了sql的最后面
AND l.loan_status IN (‘OPEN’,’READY’,’IN_PROGRESS’,’OVER_DUE’,’READY_HF_CON’)。这样会产生什么问题呢？让我们再来看一下需求，我需要的是所有的数据数，并且统计loan_status满足条件的列。比如，我有两条数据，一条是满足count的筛选条件的，一条不满足，我只需要统计满足条件的那一条，但是另一条数据我也会要显示出来，如果不满足统计条件，那就置为0好了。

然后我又写了：

SELECT l.id AS "id", l.loan_title AS "loanTitle", l.contact_amount AS "contactAmount",
l.interest AS "interest", l.loan_status AS "loanStatus", 
l.loan_months AS "loanMonths", l.create_time AS "createTime", l.borrower_id, 

COUNT(
CASE WHEN l.loan_status IN ('OPEN','READY','IN_PROGRESS','OVER_DUE','READY_HF_CON') 
THEN 1 
ELSE 0
END)

OVER(PARTITION BY l.borrower_id) AS "noPayOff"
FROM loan l, user_info ui
WHERE l.borrower_id = ui.user_id
ORDER BY l.id DESC

运行结果如图所示：

我在count里面加了一个判断条件，意思就是当记录满足条件时才计数，其他就不管。

然而好像记录数比之前多了。。。问了一个专门做连线的朋友也并没有发现问题的所在，后来还是我机智，发现 count(1), count(0), count(*) 好像在这里效果都一样啊。。。囧，这种小错误

最后的sql：

SELECT l.id AS "id", l.loan_title AS "loanTitle", l.contact_amount AS "contactAmount",
l.interest AS "interest", l.loan_status AS "loanStatus", 
l.loan_months AS "loanMonths", l.create_time AS "createTime", l.borrower_id, 

COUNT(
CASE WHEN l.loan_status IN ('OPEN','READY','IN_PROGRESS','OVER_DUE','READY_HF_CON') 
THEN 1
END)

OVER(PARTITION BY l.borrower_id) AS "noPayOff"
FROM loan l, user_info ui
WHERE l.borrower_id = ui.user_id
ORDER BY l.id DESC

运行结果如图所示：

简单粗暴，case里面只管满足的，不满足的就直接end，大概就是不做计数吧，好像不做计数的话，这个函数默认初始值是0。

好了，大功告成！