sql - 显示周和年的百分比

Question

我有这个 sql 语法，效果很好。它显示了用于联系我们的资源数量。我希望数字以百分比(满分 100%)而不是计数显示。如何让我的语句以百分比显示计数，请数据库人员提供帮助。

声明:

select coalesce(t2.Source, 'Total') as "Source", 
       coalesce(count(case when Week = date_trunc('week', now())::date - 1
                                then t1."Source" end),0) "This Week", 
       coalesce(count(t1.Source),0) "YTD"
from infosys t2 
     left join infocentertracker t1 
               ON t1.Source = t2.Source
               and Week >= DATE '01/01/2017' 
               and Week < DATE '01/01/2018'
               and Project_ID = 'PCR'
group by rollup(t2.Source)

结果:

Source           This Week    YTD                      This Week     YTD
Billboard          0           0                          0%          0%
Builder            2           8                         20%         12%
Community Website  0           2                          0%          3%
Drive-by           6           35                        60%         52%
Non-Disclosed      0           1       Requested -->      0%          2%
Other Website      0           4                          0%          6%
Radio              0           0                          0%          0%
Realtor            1           13                        10%         19%
Resident Referral  1           3                         10%          4%
Return Visitor     0           0                          0%          0%
RPG Website        0           0                          0%          0%
Social Media       0           2                          0%          3%
Total             10           68                        100%        100%

Answer 1

我不确定这是否是最好的方法，但您可以将现有查询用作子查询，以使用 MAX 窗口函数获得所需的百分比结果。

SELECT Source,
ROUND(100.0*"This Week"/ MAX(CASE WHEN Source='Total' THEN "This Week" END) OVER(),0)||'%' AS "This Week",
ROUND(100.0*YTD/ MAX(CASE WHEN Source='Total' THEN YTD END) OVER(),0)||'%' AS YTD
FROM
 (SELECT COALESCE(T2.Source, 'Total') AS "Source",
          COALESCE(COUNT(CASE
                             WHEN WEEK = DATE_TRUNC('week', NOW())::date - 1 THEN T1."Source"
                         END),0) "This Week",
          COALESCE(COUNT(T1.Source),0) "YTD"
  FROM INFOSYS T2
  LEFT JOIN INFOCENTERTRACKER T1 ON T1.Source = T2.Source
  AND WEEK >= DATE '01/01/2017'
  AND WEEK < DATE '01/01/2018'
  AND PROJECT_ID = 'PCR'
  GROUP BY ROLLUP(T2.Source)
  ) X