In SQL server, I wrote the following script to calculate the odds ratios based the probabilities of my test group divided by my control group. The script is as follows:
--Compute the odds ratios from the model
select a.column1, a.uvs as testuvs. b.uvs as controluvs
, [odds]=case when b.uvs>0 then a.puvs/b.puvs else Null end
into unique_visitor_odds
from control_probabilties b
inner join test_probabilities a
on a.column1=b.column2
where a.uvs>24 and b.uvs>24
order by [odds] desc
I am not sure how to write this in Postgresql.
The code is remarkably similar:
create table unique_visitor_odds as
select tp.column1, tp.uvs as testuvs, cp.uvs as controluvs,
tp.puvs / nullif(cp.puvs, 0) as odds
from control_probabilties cp inner join
test_probabilities tp
on tp.column1 = cp.column2
where cp.uvs > 24 and tp.uvs > 24
order by odds desc ;
I removed the case statement in the select. That is handled by the where. Postgres is smart enough to respect the where when reporting errors. And, you can prevent a divide by zero by using nullif().
Except fro the create table as and into clauses, the same code works in both databases.
Also, the order by is suspicious. I'm actually surprised the SQL Server allows it.
Besides the potential type between your second and third column in the SELECT clause and the oddball alias syntax, everything should translate to postgres easily:
SELECT a.column1,
a.uvs AS testuvs,
b.uvs AS controluvs,
CASE WHEN b.uvs > 0 THEN a.puvs / b.puvs ELSE NULL END AS odds
INTO unique_visitor_odds
FROM control_probabilties b
INNER JOIN test_probabilities a ON a.column1 = b.column2
WHERE a.uvs > 24 AND b.uvs > 24
ORDER BY odds DESC