In Postgresql, suppose I have a 2-col table containing 3 records, namely tA
id | col_A | col_B | type
1 | 1 | 2 | A
2 | 3 | 4 | B
3 | 3 | 1 | A
and a table that contains column weights for each type, named tB
type | col | weight
A | col_A | 0.6
A | col_B | 0.4
B | col_A | 0.4
B | col_B | 0.6
then I want to have a new table that has the same size as tA
, but cols are weighted with tB
. For example, where id=1
, due to type A
, hence col_A*0.6
, col_B*0.4
. The expected result is below
id | col_A | col_B | type
1 | 0.6 | 0.8 | A
2 | 1.8 | 1.6 | B
3 | 1.2 | 0.4 | A
Currently, I wrote a SQL function to handle but I wonder if is there another way that can be done in SQL query?
2
Answers
For a fixed list of column names, you can
join
, then do conditional aggregation to weight to each value:This would filter out rows on
ta
whose types do no exist intb
. If that’s a concern, you can useleft join
instead.A pivot table would be perfect for this considering how there may be more than two types in the future, possibly dozens? For this you want the
tablefunc
extension that comes with Postgres as there is no native support for it like can be found in MS SQL Server or Oracle.This makes the
crosstab
pivot table function available in your database.With the assumption that your weights are
numeric
types that do not suffer from IEEE floating point accuracy issues:Change to
double precision
orreal
as wanted/needed. See thatSELECT
statement in thecrosstab
function? This basically says, the first column returned is your row identifier, your grouping. The second returned column are the names of the columns you want returned. The last is the value of the previous column. Soct
becomes a virtual table that changes yourtB
table looking like this:to look like this instead:
Note how
weight_C
never exists in thetB
values? That’s okay and useful. It means with a minor change to your query, you can add and remove columns to reflect the changing state oftB
. You can addweight_D
andweight_E
return columns, for example. If they are not present intB
, they will remain NULL, but if you add the appropriate weights totB
, they will fill in automatically!This computed table is what you join against to get your final answer.