Assign weight and rate to products based on size of variety & weight-CodePudding

I have table1

line	products	product variety
1	a	49
2	b	27
3	c	26
4	d	28
5	e	7
6	f	6
7	g	7
8	h	13
9	i	12
10	j	13
11	k	13
12	l	3
13	m	6
14	n	13
15	o	5
16	p	5
17	q	10
18	r	707
19	s	26
20	t	10
21	u	10
22	v	10
23	w	5
24	x	2
25	y	2
26	z	2

Want to assign weight to each products based on the size of product variety and rate will be calculated based on the weight like, the 1st largest weight = 1, the 2nd largest weight = 2 and so on. The total sum of weights should be 100

The expected result looks like below

line	products	product variety	weight	rate
1	a	49	6.38	2
2	b	27	5.40	4
3	c	26	5.34	5
4	d	28	5.46	3
5	e	7	3.19	9
6	f	6	2.94	10
7	g	7	3.19	9
8	h	13	4.20	6
9	i	12	4.07	7
10	j	13	4.20	6
11	k	13	4.20	6
12	l	3	1.80	12
13	m	6	2.94	10
14	n	13	4.20	6
15	o	5	2.64	11
16	p	5	2.64	11
17	q	10	3.77	8
18	r	707	10.75	1
19	s	26	5.34	5
20	t	10	3.77	8
21	u	10	3.77	8
22	v	10	3.77	8
23	w	5	2.64	11
24	x	2	1.14	13
25	y	2	1.14	13
26	z	2	1.14	13

Tried in excel with the below formulas and is working perfectly, but i want something like this in postgresql to provide same above result.

D2=LOG10(C2)/SUM(LOG10($C$2:$C$27))*100
E2=SUMPRODUCT(($D2 < $D$2:$D$27)/COUNTIF($D$2:$D$27,$D$2:$D$27)) 1

CodePudding user response：

Calculate weights in the inner query and use the window function dense_rank():

select 
    line, 
    products, 
    product_variety,
    weight,
    dense_rank() over (order by weight desc) as rate
from (
    select
        line, 
        products, 
        product_variety,
        round(log(product_variety::dec)/ sum* 100, 2) as weight
    from table1
    cross join lateral (
        select sum(log(product_variety::dec))
        from table1 
        ) s
    ) s
order by line

Test the query in db<>fiddle.

line	products	product variety
1	a	49
2	b	27
3	c	26
4	d	28
5	e	7
6	f	6
7	g	7
8	h	13
9	i	12
10	j	13
11	k	13
12	l	3
13	m	6
14	n	13
15	o	5
16	p	5
17	q	10
18	r	707
19	s	26
20	t	10
21	u	10
22	v	10
23	w	5
24	x	2
25	y	2
26	z	2

line	products	product variety
1	a	49
2	b	27
3	c	26
4	d	28
5	e	7
6	f	6
7	g	7
8	h	13
9	i	12
10	j	13
11	k	13
12	l	3
13	m	6
14	n	13
15	o	5
16	p	5
17	q	10
18	r	707
19	s	26
20	t	10
21	u	10
22	v	10
23	w	5
24	x	2
25	y	2
26	z	2

line	products	product variety
1	a	49
2	b	27
3	c	26
4	d	28
5	e	7
6	f	6
7	g	7
8	h	13
9	i	12
10	j	13
11	k	13
12	l	3
13	m	6
14	n	13
15	o	5
16	p	5
17	q	10
18	r	707
19	s	26
20	t	10
21	u	10
22	v	10
23	w	5
24	x	2
25	y	2
26	z	2