查詢中的 PostgreSQL 分區不起作用

所以我有一個真正的休息日或其他東西，但對於我的生活，我無法弄清楚這一點。我試圖除以tier1得到total總數的百分比。我是這樣想的：

(tier1 / total) as per

但沒有運氣。我將這些 CAST 為 int ，但仍然沒有。我會得到一個錯誤並且查詢不會執行，或者我會得到一個 0 的值。

這是我正在嘗試使用的內容：

SELECT count(student_id) as total
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
FROM national_assessments.aimsweb
WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL

我也有這個想法，但沒有運氣：

SELECT tier1,total, tier1/total as test
FROM (
   SELECT count(student_id) as total
   ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
   ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
   ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
   FROM national_assessments.aimsweb
   WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL
) as test

整數除法會截斷小數位數。您的表達式返回一個介於 0 和 1 之間的比率，該比率始終被截斷為 0。

要獲得“百分比”，首先乘以 100。

要獲得小數位數，請轉換為numeric（在除法之前） - 或乘以100.0. 數字文字中存在小數位會numeric自動強制結果。

通常，您會四捨五入到兩位小數或其他數字。用於round()那個（僅適用於numeric）。

向您獲得的查詢添加一些其他基本優化：

SELECT total
     ,tier1, round((tier1 * 100.0) / total, 2) AS tier1_pct
     ,tier2, round((tier2 * 100.0) / total, 2) AS tier2_pct
     ,tier3, round((tier3 * 100.0) / total, 2) AS tier3_pct
FROM  (
  SELECT count(*) AS total
        ,count(fall_september_tier = 1 OR NULL) AS tier1
        ,count(fall_september_tier = 2 OR NULL) AS tier2
        ,count(fall_september_tier = 3 OR NULL) AS tier3
  FROM   national_assessments.aimsweb
  WHERE  general_outcome_measure = 'PSF'
  AND    fall_september_tier IS NOT NULL
  ) sub;

計數技術說明：

• 對於絕對性能，SUM 更快還是 COUNT 更快？

引用自：https://dba.stackexchange.com/questions/75622