如何在Postgresql中转换成十进制？ [英] How do I CAST AS DECIMAL in postgresql?

问题描述

以下查询中的Percent_Failure给出的结果为1.00或0.00。有人可以帮我理解为什么它没有给我实际的小数吗？

The Percent_Failure in the query below is giving results as either 1.00 or 0.00. Can anyone help me understand why it's not giving me the actual decimal?

SELECT
    sf.hierarchy_name AS Hierarchy_Name,
    cl.cmh_id AS CMH_ID,
    cl.facility_name AS Clinic_Name,
    sf.billing_city AS City,
    sf.billing_state_code AS State,
    cl.num_types AS Num_Device_Dypes,
    cl.num_ssids AS Num_SSIDs,
    SUM(CAST(CASE WHEN (d.mdm_client_version NOT LIKE '1.14%' AND d.type = 'Wallboard')
        OR (d.mdm_client_version NOT LIKE '1.14%' AND d.type = 'Tablet')
        OR (d.mdm_client_version NOT LIKE '1.14%' AND d.type = 'AndroidMediaPlayer')
        OR (d.mdm_client_version NOT LIKE '1.14%' AND d.type = 'InfusionRoomTablet') THEN 1 ELSE 0 END AS INTEGER))  AS Non_Updated, 
    COUNT(d.asset_id) AS Total_Devices
    CAST((Non_Updated) / (Total_Devices) AS DECIMAL (5,4)) AS Percent_Failure
FROM 
    (SELECT id, clinic_table_id, type, asset_id, mdm_client_version, device_apk_version, ssid
    FROM mdm.devices) d
JOIN 
    (SELECT a.id, a.cmh_id, a.facility_name, COUNT(DISTINCT b.ssid) AS num_ssids, COUNT(DISTINCT b.type) AS num_types
    FROM mdm.clinics a
        JOIN 
            (SELECT *
            FROM mdm.devices
            WHERE status = 'Active'
            AND last_seen_at >= (CURRENT_DATE - 2)
            AND installed_date <= (CURRENT_DATE - 3)) b 
            ON a.id = b.clinic_table_id
    GROUP BY 1,2,3) cl
    ON d.clinic_table_id = cl.id
JOIN
    (SELECT x.cmh_id, x.hierarchy_group, x.hierarchy_name, x.billing_city, x.billing_state_code
    FROM salesforce.accounts x) sf
    ON sf.cmh_id = cl.cmh_id  
GROUP BY 1,2,3,4,5,6,7
ORDER BY 10 DESC;

推荐答案

Integer / Integer = Integer。因此，您需要在进行除法运算之前对其进行强制转换：

Integer / Integer = Integer. So, you need to cast it before you do the division:

cast (Non_Updated as decimal) / Total_Devices AS Percent_Failure

或速记：

Non_Updated::decimal / Total_Devices AS Percent_Failure

我见过其他可爱的实现，例如

I've seen other cute implementations, such as

Non_Updated * 1.0 / Total_Devices AS Percent_Failure

此外，您确定total_devices总是非零吗？如果没有，请务必解决。

Also, are you sure that total_devices is always non-zero? If not, be sure to handle that.

这篇关于如何在Postgresql中转换成十进制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！