我的查询如下所示:
SELECT mthreport.*
FROM crosstab
('SELECT
to_char(ipstimestamp, ''mon DD HH24h'') As row_name,
varid::text || log.varid || ''_'' || ips.objectname::text As bucket,
COUNT(*)::integer As bucketvalue
FROM loggingdb_ips_boolean As log
INNER JOIN IpsObjects As ips
ON log.Varid=ips.ObjectId
WHERE ((log.varid = 37551)
OR (log.varid = 27087)
OR (log.varid = 50876)
OR (log.varid = 45096)
OR (log.varid = 54708)
OR (log.varid = 47475)
OR (log.varid = 54606)
OR (log.varid = 25528)
OR (log.varid = 54729))
GROUP BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, bucket
ORDER BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, bucket' )
As mthreport(item_name text, varid_37551 integer,
varid_27087 integer ,
varid_50876 integer ,
varid_45096 integer ,
varid_54708 integer ,
varid_47475 integer ,
varid_54606 integer ,
varid_25528 integer ,
varid_54729 integer ,
varid_29469 integer)
可以使用以下连接字符串针对测试表测试查询:"host=bellariastrasse.com port=5432 dbname=IpsLogging user=guest password=guest"
查询语法正确且运行正常。我的问题是 COUNT(*) 值总是填充最左侧的列。但是,在许多情况下,左列应为零或 NULL,并且只应填充第 2 列(或第 n 列)。我的大脑正在融化,我不知道出了什么问题!
问题的解决方案是使用具有两个参数的crosstab()
变体。
第二个参数(另一个查询字符串)生成输出列的列表,以便正确分配数据查询中的 NULL 值(第一个参数)。
查看手册以获取tablefunc扩展,特别是crosstab(text, text)
:
交叉表的单参数形式的主要限制是 它一视同仁地对待组中的所有值,将每个值插入到 第一个可用列。如果希望值列对应于 特定类别的数据,某些组可能没有 有些类别,效果不佳。双参数形式 的交叉表通过提供显式列表来处理这种情况 与输出列对应的类别。
强调我的。我最近在这里或这里或这里发布了几个相关的答案。