sql - CASE WHEN NOT 没有产生正确的结果？

Question

我目前正在处理 titanic.sql 数据集，我需要回答的一个问题是计算第二类女性的生存几率。

(我相信计算赔率是#where case is true/#where case not true)

我有两个不同的 sql 语句，它们产生不同的结果。我假设 CASE WHEN NOT 不起作用...

我可能不明白其中的逻辑，但有人可以解释一下吗？

SELECT 
   SUM(CASE WHEN sex='female' AND class='2nd' AND survived=1 THEN 1.0 ELSE 0.0 END) / 
      CAST(
        SUM(CASE WHEN NOT sex='female' AND NOT class='2nd' AND NOT survived=1 
            THEN 1.0 ELSE 0.0 END) 
    AS FLOAT) 
AS odds_second_fem_surive FROM passengers; 



SELECT 
   SUM(CASE WHEN sex='female' AND class='2nd' AND survived=1 THEN 1.0 ELSE 0.0 END) / 
      CAST( COUNT(*) - (CASE WHEN sex='female' AND class='2nd' AND survived=1 THEN 1.0 ELSE 0.0 END)
    AS FLOAT) 
AS odds_second_fem_surive____ FROM passengers;

Answer 1

您缺少一些 bool 逻辑知识。

负数:

sex = 'female' AND class = '2nd'

是:

not (sex = 'female' AND class = '2nd')

这也可以用这两种等价的方式之一来表达:

not sex = 'female' or not class = '2nd'
sex <> 'female' or not class <> '2nd'

这不同于:

not sex = 'female' and not class = '2nd'

注意:如果列可以是null，这些表达式会稍微复杂一些。