如何转换在SQL Server中编写的脚本以计算Postgresql的优势比？ [英] How can I translate a script I wrote in SQL server to compute the odds ratios to Postgresql?

问题描述

在SQL Server中，我编写了以下脚本，以将测试组的概率除以对照组的概率来计算优势比。脚本如下：

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 

我不确定如何在Postgresql中编写它。

I am not sure how to write this in Postgresql.

推荐答案

代码非常相似：

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 ;

我删除了 case 语句， code>选择。这由 where 处理。 Postgres非常聪明，可以在报告错误时遵守 where 。而且，可以使用 nullif（）防止被零除。

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.

此外， 的订单也是可疑的。我真的很惊讶SQL Server允许它。

Also, the order by is suspicious. I'm actually surprised the SQL Server allows it.

这篇关于如何转换在SQL Server中编写的脚本以计算Postgresql的优势比？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！