如何获得每天2列总计数的百分比?

c3frrgcw  于 2021-06-01  发布在  Hadoop
关注(0)|答案(4)|浏览(571)

我有以下疑问:

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%。希望你们能帮忙。

n53p2ov0

n53p2ov01#

可能是使用常用的表表达式。像这样:

WITH CTE AS (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;
)
SELECT ACCT_OT, new, old, ((old/new) * 100) AS per FROM CTE;
lmvvr0a8

lmvvr0a82#

你能做到的

WITH v_mstr_tbl
AS
(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
 )
 SELECT ACCT_OT, new, old, (new/old) * 100 AS percent
   FROM v_mstr_tbl;
gwbalxhn

gwbalxhn3#

我会用 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 避免被零除。

t3irkdon

t3irkdon4#

我不确定这是否有效,因为我不能测试它,但你可以试试。 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, CONVERT(VARCHSR(50), COUNT(CASE WHEN BR_CD like '%1%' THEN 1 ELSE NULL END) * 100 / COUNT(CASE WHEN BR_CD like '%0%' THEN 1 ELSE NULL END))+'% ' as percentage FROM MSTR_TBL where ACCT_OT between '2017-10-23' and '2017-10-25' GROUP BY ACCT_OT;

相关问题