sql - 如何在 postgresql 中转换为十进制?
问题描述
下面查询中的 Percent_Failure 给出的结果为 1.00 或 0.00。谁能帮我理解为什么它没有给我实际的小数?
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;
解决方案
整数 / 整数 = 整数。因此,您需要在进行除法之前对其进行转换:
cast (Non_Updated as decimal) / Total_Devices AS Percent_Failure
或速记:
Non_Updated::decimal / Total_Devices AS Percent_Failure
我见过其他可爱的实现,例如
Non_Updated * 1.0 / Total_Devices AS Percent_Failure
另外,您确定 total_devices 始终非零吗?如果没有,请务必处理。
推荐阅读
- python - 如何将列表中的所有项目一一删除并在删除后同时打印
- c++ - 折叠表达式、参数包扩展、类成员函数中的递归
- javascript - 检查map中是否存在key
- java - 回滚多个服务
- crystal-reports - 如何在水晶报表中使用公式显示和隐藏 LineObject?
- c# - HttpClient - 任务被取消
- tomcat - Intellij-IDEA 可以在 tomcat 中启动和调试 jersey 应用程序,但是将战争部署到 tomcat 得到 404
- python-3.x - 在python中绘制数据后不显示X轴
- tomcat - 服务器上多个 tomcat 实例的 ansible 代码
- datetime - 与 Twig 的总日期