运行计数显示所有值，而不是值的总数。

Question

我的数据存储在数据库中。我正试着按月计算贷款的数量。这是我的疑问：

SELECT 
TO_CHAR(LD.INITIAL_PURCHASE_DATE,'YYYY-MM') AS INITIAL_PURCHASE,
COUNT( LD.LOAN_ID) OVER (ORDER  BY TO_CHAR(LD.INITIAL_PURCHASE_DATE,'YYYY-MM') ROWS UNBOUNDED PRECEDING ) AS TOTAL_LOANS
FROM LOANS_DETAILS
INNER JOIN LOANS L ON LD.LOAN_ID = L.ID
WHERE L.UNDERWRITING_STATUS IN ('...')
 AND LD.INITIAL_PURCHASE_DATE IS NOT NULL
 GROUP BY
 LD.LOAN_ID, 
 LD.INITIAL_PURCHASE_DATE;

我的预期结果如下：

INITIAL_PURCHASE|TOTAL_LOANS
...|...
2016-10|369
2016-11|424

但是我却得到了一个月中每天都有这样的记录

INITIAL_PURCHASE|TOTAL_LOANS
...|...
2016-10|366
2016-10|367
2016-10|368
2016-10|369
2016-11|371

我检查了来源系统，确认10月份总共有369笔贷款，11月份有424笔，所以我知道数据是正确的。

我如何获得每月贷款总额？

解决方案:这是正确的查询.

SELECT 
TO_CHAR(LD.INITIAL_PURCHASE_DATE,'YYYY-MM') AS INITIAL_PURCHASE_DATE,
SUM(COUNT( LD_LOANS.LOAN_ID )) OVER (ORDER BY TO_CHAR(LD.INITIAL_PURCHASE_DATE,'YYYY-MM') ROWS UNBOUNDED PRECEDING ) AS TOTAL_LOANS 
FROM LOANS_DETAIL LD
INNER JOIN LOANS L ON LD.LOAN_ID = L.ID
WHERE L.UNDERWRITING_STATUS IN ('...') AND LD.INITIAL_PURCHASE_DATE IS NOT NULL
GROUP BY TO_CHAR(LD.INITIAL_PURCHASE_DATE,'YYYY-MM')

Answer 1

您的group by需要按月而不是按日，并且您需要从GROUP BY中删除LOAN_ID

SELECT TO_CHAR(LD.INITIAL_PURCHASE_DATE, 'YYYY-MM') AS INITIAL_PURCHASE,
       SUM(COUNT( LD.LOAN_ID)) OVER (ORDER  BY TO_CHAR(LD.INITIAL_PURCHASE_DATE,'YYYY-MM') ROWS UNBOUNDED PRECEDING ) AS TOTAL_LOANS
FROM LOANS_DETAILS LD INNER JOIN
     LOANS L
     ON LD.LOAN_ID = L.ID
WHERE L.UNDERWRITING_STATUS IN ('...') AND
      LD.INITIAL_PURCHASE_DATE IS NOT 
GROUP BY TO_CHAR(LD.INITIAL_PURCHASE_DATE, 'YYYY-MM')

备注：

• 我认为Amazon允许在GROUP BY中使用别名，所以您可以使用GROUP BY INITIAL_PURPOSE, LD.LOAN_ID。
• SUM(COUNT(*))应该给你运行的总和。
• 如果您希望按月汇总，LOAN_ID不应该在GROUP BY中。