Database 获取总和列的比率

根据你的which gives me the output，我们可以try 使用LEAD和ROW_NUMBER窗口函数来获得下一个count，并过滤最新的每一行number

SELECT number,
       type,
       CASE WHEN count = 0 THEN 'INF' ELSE (n_count::DECIMAL(8,3) /count )::VARCHAR(20) END
FROM (
  SELECT *,
        LEAD(count) OVER(PARTITION BY number ORDER BY type) n_count,
        ROW_NUMBER() OVER(PARTITION BY number ORDER BY type DESC) rn 
  FROM (
    SELECT DISTINCT ON (projectname, number, type) number, type, SUM(count) as count
    FROM myTable
    GROUP BY (projectname, number, type)
    ORDER BY number
  ) t1
) t1
WHERE rn > 1

但我看到了完整的样本数据和预期结果，您可能需要使用OUTER JOIN，基于CROSS JOIN创建的typenumber

WITH CTE AS (
 SELECT *
 FROM (
  SELECT distinct type
  FROM myTable
 ) t1 CROSS JOIN (
  SELECT distinct number
  FROM myTable
 ) t2
)
SELECT number,
       type,
        CASE WHEN count = 0 THEN 'INF' ELSE (n_count::DECIMAL(8,3) /count )::VARCHAR(20) END
FROM (
  SELECT *,
        LEAD(count) OVER(PARTITION BY number ORDER BY type) n_count,
        ROW_NUMBER() OVER(PARTITION BY number ORDER BY type DESC) rn 
  FROM (
    SELECT t1.number, t1.type, SUM(t2.count) count
    FROM CTE t1
    LEFT JOIN myTable t2
    ON t1.type = t2.type
    AND t1.number = t2.number
   GROUP BY t1.number, t1.type
  ) t1
) t1
WHERE rn > 1

sqlfiddle