如何编辑此SQL代码以筛选表中的数据

Question

假设我们有下表(称为numbers(：

user a b c 
-----------
Jon  0 1 3
Bill 1 2 3
Jess 2 4 2
Jon  1 3 4

执行以下操作几乎可以执行我想要的计算：

CREATE VIEW v_numbers 
AS 
SELECT  
n.*, (b / c) - AVG(b / c) OVER (PARTITION BY user) AS e 
FROM numbers n

但是，我希望编辑此代码以排除列a值为0的行。因此，我尝试按如下方式编辑它以过滤数据，但代码不起作用。有人知道如何修改吗？也就是说，这可能是语法错误。

CREATE VIEW v_numbers 
AS 
SELECT n.* 
WHERE E a != 0, (b / c) - avg(b / c) OVER (PARTITION BY user) AS e 
FROM numbers n

Answer 1

我想编辑此代码以排除列a值=0 的行

只使用WHERE子句？它在FROM子句之后：

CREATE VIEW v_numbers AS 
SELECT n.*, (b / c) - avg(b / c) OVER (PARTITION BY user) AS e 
FROM numbers n
WHERE a <> 0

如果要在列e中为a为0的行放置null值：

CREATE VIEW v_numbers AS 
SELECT n.*, 
CASE WHEN a <> 0 
THEN (b / c) - avg(b / c) OVER (PARTITION BY user)
END AS e 
FROM numbers n

如果您想从平均计算中排除这些行：

CREATE VIEW v_numbers AS 
SELECT n.*, 
CASE WHEN a <> 0 
THEN (b / c) - avg(CASE WHEN a <> 0 THEN b / c END) OVER (PARTITION BY user) 
END AS e 
FROM numbers n

一些数据库(如Postgres或SQLite(支持标准的filter子句，因此您可以执行以下操作：

CREATE VIEW v_numbers AS 
SELECT n.*, 
CASE WHEN a <> 0 
THEN (b / c) - avg(b / c) FILTER(WHERE a <> 0) OVER (PARTITION BY user)
END AS e 
FROM numbers n