CODEWITHSUNDEEP
postgresqlrecursive-query
user4466316
user4466316

Reputation:

create table from 2 tables in postgres

Table 1:

+-----+-----+
| TID | Rev |
+-----+-----+
| A   |  20 |
| B   | 100 |
| C   |  10 |
+-----+-----+

Table 2:

+-----+-------+
| TID | Count |
+-----+-------+
| A   |     2 |
| B   |     3 |
| C   |     2 |
+-----+-------+

Required: Take 50% of rev from Table 1 and distribute using linear decay based on the count for a given TID in Table 2.

Example: For TID=A, table1: Rev=20 Table2: count=2

Step1: take 50% of Rev = 10

Step2: Distribute using decay(Divide by 2), so 10 and 5

    +-----+-------+
    | TID | Value |
    +-----+-------+
    | A   | 10    |
    | A   | 5     |
    | B   | 50    |
    | B   | 25    |
    | B   | 12.5  |
    | C   | 5     |
    | C   | 2.5   |
    +-----+-------+

Upvotes: 3

Views: 117

Answers (1)

klin
klin

Reputation: 121889

The setup:

create table revs (tid text, rev numeric);
insert into revs values
('A',  20),
('B', 100),
('C',  10);

create table counts (tid text, ct int);
insert into counts values
('A', 2),
('B', 5),
('C', 2);

This is a case for recursive cte:

with recursive revrec(tid, rev, ct) as (
    select tid, rev / 2, ct- 1
    from revs
    join counts using(tid)
union
    select tid, rev / 2, ct- 1
    from revrec
    where ct > 0
)
select tid, rev
from revrec
order by tid, ct desc;

 tid |         rev         
-----+---------------------
 A   | 10.0000000000000000
 A   |  5.0000000000000000
 B   | 50.0000000000000000
 B   | 25.0000000000000000
 B   | 12.5000000000000000
 B   |  6.2500000000000000
 B   |  3.1250000000000000
 C   |  5.0000000000000000
 C   |  2.5000000000000000
(9 rows)

Upvotes: 2

Related Questions