Getting ratio of summed columns-CodePudding

I have the following DB structure.

id	projectname	number	filename	type	unq	count
8	prj1	2	a	t1	888389f661e117	1
9	prj1	2	a	t1	888389f661e117	2
10	prj1	2	a	t1	888389f661e117	2
11	prj1	2	a	t2	816418549711c3d33	6
12	prj1	2	a	t2	816418549711c3d33	7
13	prj1	2	a	t2	816418549711c3d33	1
14	prj1	2	a	t3	NULL	NULL
15	prj1	2	a	t3	NULL	NULL
16	prj1	2	a	t3	NULL	NULL
17	prj1	36	b	t1	8dac5bdffc7f86502	0
18	prj1	36	b	t1	8dac5bdffc7f86502	0
19	prj1	36	b	t1	8dac5bdffc7f86502	0

I use the query below to get the sums of count column w.r.t. the type column. A unique identifier of a row is `(projectname, number, filename).

SELECT DISTINCT ON (projectname, number, type) number, type, SUM(count) as count
FROM myTable
GROUP BY (projectname, number, type)
ORDER BY number

which gives me the output

number	type	count
2	t1	5
2	t2	14
2	t3	NULL
36	t1	0
36	t2	16
36	t3	NULL

My ideal output is: for every number column item, I want to divide the t2 value by t1 and the t3 value to t2. Can I accomplish this with Postgres commands without using external data manipulation techniques? I am looking to obtain a table as below. The type column is just representative of the operation I am interested in.

number	type	ratio
2	t2t1	14 by 5
2	t3t2	NULL by 14 is NULL
36	t2t1	16 by 0 is INF
36	t3t2	NULL by 16 is NULL

CodePudding user response：

Based on your which gives me the output we can try to use LEAD and ROW_NUMBER window function to get next count and filter the latest row each 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

but I saw full sample data and expect result you might need to use OUTER JOIN based on type,number which is created by CROSS JOIN

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