sql - 如何获得每天 2 列总计数的百分比？

Question

我有以下查询:

SELECT ACCT_OT, 

COUNT(CASE WHEN BR_CD like '%0%' THEN 1 ELSE NULL END) AS new,

COUNT(CASE WHEN BR_CD like '%1%' THEN 1 ELSE NULL END) AS old,

FROM MSTR_TBL where ACCT_OT between '2017-10-23' and '2017-10-25'

GROUP BY ACCT_OT;

我希望能够在同一个查询中添加另一列，以显示旧/新的百分比。比如10/23，new是10，old是1，那么第三列就是10%。希望大家帮帮忙。

Answer 1

我会使用 sum() 而不是 count() 编写您的查询:

SELECT ACCT_OT, 
       SUM(CASE WHEN BR_CD like '%0%' THEN 1 ELSE 0 END) AS new,
       SUM(CASE WHEN BR_CD like '%1%' THEN 1 ELSE 0 END) AS old
FROM MSTR_TBL 
WHERE ACCT_OT BETWEEN '2017-10-23' ND '2017-10-25'
GROUP BY ACCT_OT;

如果你想要旧的百分比，我会这样做:

SELECT ACCT_OT, 
       SUM(CASE WHEN BR_CD like '%0%' THEN 1 ELSE 0 END) AS new,
       SUM(CASE WHEN BR_CD like '%1%' THEN 1 ELSE 0 END) AS old,
       AVG(CASE WHEN BR_CD like '%1%' THEN 1.0 ELSE 0 END) AS old
FROM MSTR_TBL 
WHERE ACCT_OT BETWEEN '2017-10-23' ND '2017-10-25'
GROUP BY ACCT_OT;

但是，比率需要重复表达式:

SELECT ACCT_OT, 
       SUM(CASE WHEN BR_CD like '%0%' THEN 1 ELSE 0 END) AS new,
       SUM(CASE WHEN BR_CD like '%1%' THEN 1 ELSE 0 END) AS old,
       (SUM(CASE WHEN BR_CD like '%1%' THEN 1 ELSE 0 END) /
        SUM(CASE WHEN BR_CD like '%0%' THEN 1 END)
       ) as ratio

我删除了 else 0 以避免被零除。