如何在 postgresql 交叉表中用零替换空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6355177/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace nulls with zeros in postgresql crosstabs
提问by Mike
I've a product table with product_id and 100+ attributes. The product_id is text whereas the attribute columns are integer, i.e. 1 if the attribute exists. When the Postgresql crosstab is run, non-matching atrributes return null values. How do I replace nulls with zeros instead.
我有一个带有 product_id 和 100 多个属性的产品表。product_id 是文本，而属性列是整数，即如果属性存在则为 1。运行 Postgresql 交叉表时，不匹配的属性返回空值。我如何用零替换空值。
SELECT ct.*
INTO ct3
FROM crosstab(
'SELECT account_number, attr_name, sub FROM products ORDER BY 1,2',
'SELECT DISTINCT attr_name FROM attr_names ORDER BY 1')
AS ct(
account_number text,
Attr1 integer,
Attr2 integer,
Attr3 integer,
Attr4 integer,
...
)
Replace this result:
替换这个结果：
account_number Attr1 Attr2 Attr3 Attr4
1.00000001 1 null null null
1.00000002 null null 1 null
1.00000003 null null 1 null
1.00000004 1 null null null
1.00000005 1 null null null
1.00000006 null null null 1
1.00000007 1 null null null
with this below:
下面这个：
account_number Attr1 Attr2 Attr3 Attr4
1.00000001 1 0 0 0
1.00000002 0 0 1 0
1.00000003 0 0 1 0
1.00000004 1 0 0 0
1.00000005 1 0 0 0
1.00000006 0 0 0 1
1.00000007 1 0 0 0
A workaround would be to do a select account_number, coalesce(Attr1,0)... on the result. But typing out coalesce for each of the 100+ columns is rather unyieldly. Is there a way to handle this using crosstab? Thanks
一种解决方法是对结果执行 select account_number,coalesce(Attr1,0)...。但是为 100 多列中的每一列都输入 coalesce 是相当顽固的。有没有办法使用交叉表来处理这个问题？谢谢
回答by Denis de Bernardy
You can use coalesce:
您可以使用合并：
select account_number,
coalesce(Attr1, 0) as Attr1,
coalesce(Attr2, 0) as Attr2,
etc
回答by Yi Wang
if you can put those Attrs into a table like
如果你可以把这些 Attrs 放到一个表中
attr
属性
Attr1
属性 1
Attr2
属性2
Attr3
属性3
...
...
then you could automatically generate the repeating coalesce statement like
那么你可以自动生成重复的合并语句，如
SELECT 'coalesce("' || attr || '", 0) "'|| attr ||'",' from table;
to save some typing.
SELECT 'coalesce("' || attr || '", 0) "'|| attr ||'",' from table;
节省一些打字。
