需要获得客户的成功率

Question

我有一个非常好的家伙在FreeNode IRC引导我更接近答案。

我现在使用的查询是：

SELECT st.staff_id
     , ROUND (100.0 * ( sum (case when s.code in ('10401','10402','10403') then 1
                                      else 0 end)/count(s.code)), 1) as successes
    from notes n join services s on n.zrud_service=s.zzud_service
            join staff st on n.zrud_staff = st.zzud_staff
    WHERE s.code IN ( '10401','10402','10403','10405')
    AND n.date_service BETWEEN (now() - '30 days'::interval)::timestamp AND now()
    group by st.staff_id;

(我确实尝试了/count(*)以及其他一些方法)

它不会出错，并将结果显示为100.0或0，我只对按人员分组的代码运行了一个查询，得到了不同的结果。一名员工在过去一个月中解雇了23人，其中8人不成功(10405人)，这一比例为34.7%。但是查询显示0%。

这真是令人费解。有人有建议吗？

原始问题

我需要能够看到在30天内成功排放的百分比。下面是一个按代码显示放电的查询。我知道我可以在postgresql中使用“除法”方法，但是我只能在两个单独的列中使用它。有人能帮助我演示如何在列中划分数据吗？我需要这样做: 10401'+'10402'+'10403‘/ 10401'+'10402'+'10403'+'10405’

SELECT n.date_creation, g.name AS Group, s.staff_id, n.date_service, c.client_id,
       c.name_lastfirst_cs AS Client, q.code 
FROM notes n, clients c, groups g, staff s,services q 
WHERE n.visibility_flag = 1 -- valid note
AND notes.date_service BETWEEN (now() - '30 days'::interval)::timestamp AND now();
AND c.zzud_client = n.zrud_client AND n.zrud_group = g.zzud_group  
AND n.zrud_staff = s.zzud_staff 
AND q.code IN ('10401','10402','10403','10405')  -- 10405 is unsuccessful discharge
AND n.zrud_service = q.zzud_service AND n.zrud_staff = ? ORDER BY n.date_service

如果我以这样的方式重写查询：

SELECT g.name AS Group, s.staff_id, c.client_id,
       c.name_lastfirst_cs AS Client, q.code 
FROM notes n, clients c, groups g, staff s,services q 
WHERE n.visibility_flag = 1 -- valid note
AND notes.date_service BETWEEN (now() - '30 days'::interval)::timestamp AND now();
AND c.zzud_client = n.zrud_client AND n.zrud_group = g.zzud_group  
AND n.zrud_staff = s.zzud_staff 
AND n.zrud_service = q.zzud_service AND n.zrud_staff = ? ORDER BY n.date_service

或者，我可以用"SUM“运算符代替所有的+？

我将查询更改为：

SELECT g.name AS Group, s.staff_id AS Staff
SUM(CASE WHEN q.code BETWEEN '10401' AND '10405' THEN 1 ELSE 0 END) / SUM(CASE WHEN q.code       BETWEEN '10401' AND '10405' THEN 0 ELSE 1 END)
 AS success_ratio FROM FROM notes n, clients c, groups g, staff s,services q  
 AND n.date_service BETWEEN (now() - '30 days'::interval)::timestamp AND now()
AND q.code IN ('10401','10402','10403','10405')
AND c.zzud_client = n.zrud_client AND n.zrud_group = g.zzud_group  
AND n.zrud_staff = s.zzud_staff 
AND n.zrud_service = q.zzud_service AND s.staff_id = 'BATTNEAL1026' ORDER BY n.date_service
 GROUP BY s.staff_id

得到这个错误：

错误:在或接近"SUM“第2行的语法错误:SUM(当q.code在'10401‘和'10405’之间，然后是1.^*错误*错误:语法错误在"SUM“SQL状态: 42601字符: 45

Answer 1

您的问题是有两个bigint / integer号的除法。由于您的结果总是< 0，所以结果要么是0，要么是1。

sum (case when s.code in ('10401','10402','10403') then 1 else 0 end)
    /count(s.code)

先由100.0 乘积.

100.0中的分数位强制在numeric中进行计算，从而保留分数部分。

通过一些其他的修改和格式设置，它可以如下所示：

SELECT st.staff_id
      ,round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0)
            / count(*), 1) AS successes
FROM   notes    n 
JOIN   services s  ON s.zzud_service = n.zrud_service
JOIN   staff    st ON st.zzud_staff = n.zrud_staff
WHERE  s.code IN ('10401','10402','10403','10405')
AND    n.date_service BETWEEN (now() - '30 days'::interval) AND now()
GROUP  BY st.staff_id;