SQL - NAs和

Question

样本数据我正在处理奥运数据(https://www.kaggle.com/heesoo37/120-years-of-olympic-history-athletes-and-results)。我尝试使用SQLite获取某些列的NAs之和。列的NAs之和显示为0(零)。码输出代码在下面，请帮助。谢谢

SELECT
  Sex,
  SUM(CASE WHEN ('Age' = 'NA') THEN (1.0) ELSE (0.0) END) AS NA_age,
  SUM(CASE WHEN ('Height' = 'NA') THEN (1.0) ELSE (0.0) END) AS NA_height,
  SUM(CASE WHEN ('Weight' = 'NA') THEN (1.0) ELSE (0.0) END) AS NA_weight,
  COUNT(*)
FROM olymp
GROUP BY Sex

Answer 1

不能在列名周围使用单引号。

单引号用于字符串文本。

改为：

SELECT
  Sex,
  SUM(CASE WHEN (Age = 'NA') THEN 1 ELSE 0 END) AS NA_age,
  SUM(CASE WHEN (Height = 'NA') THEN 1 ELSE 0 END) AS NA_height,
  SUM(CASE WHEN (Weight = 'NA') THEN 1 ELSE 0 END) AS NA_weight,
  COUNT(*)
FROM olymp
GROUP BY Sex

但是在SQLite中，不需要CASE表达式。

在SUM()中使用布尔表达式

SELECT
  Sex,
  SUM(Age = 'NA') AS NA_age,
  SUM(Height = 'NA') AS NA_height,
  SUM(Weight = 'NA') AS NA_weight,
  COUNT(*)
FROM olymp
GROUP BY Sex