我有一个联合查询:
(SELECT
to_char(createdatutc,'YYYY') as "Yr",
to_char(createdatutc,'MM') as "Mh",
count(postid) as Freq
FROM conversations
WHERE type = 'Post'
GROUP BY Yr, Mh
HAVING Yr = '2018')
UNION
(SELECT
to_char(createdatutc,'YYYY') as "Yr",
to_char(createdatutc,'MM') as "Mh",
count(postid) as Freq
FROM conversations
WHERE type <> 'Post'
GROUP BY Yr, Mh having Yr = '2018')
ORDER BY Yr, Mh
在执行时引发以下错误:
org.postgresql.util.PSQLException:错误:“conversations.createdatutc”列必须出现在GROUP BY子句中,或用于聚合函数中`
但是,如果我单独运行它们,它们会正常运行,这里createdautc是一个时间戳字段
最佳答案
首先:我对个别查询的运行感到惊讶。您不应该在HAVING
中使用别名列名,因为HAVING
发生在SELECT
之前。
使用UNION
可以删除重复项。所以你数了数,有几个月的帖子和没有帖子的完全一样,只有一半。这就是你要找的吗?看起来很奇怪。
无论如何,通过查询,您将得到多个结果行,并且您将无法区分哪些是针对文章的,哪些是针对非文章的。
(正如您所知道的:如果type可以为null,则不会在任何行中计算该值,因为未知的null既不等于也不等于Post。)
以下是编写查询的两种方法:
每月一行,类型
SELECT yr, mh, tp, COUNT(*)
FROM
(
SELECT
TO_CHAR(createdatutc, 'YYYY') AS yr,
TO_CHAR(createdatutc, 'MM') AS mh,
CASE WHEN type = 'Post' THEN 'Post' ELSE 'other' END AS tp
FROM conversations
WHERE EXTRACT(YEAR FROM createdatutc) = 2018
) yr2018
GROUP BY yr, mh, tp
ORDER BY yr, mh, tp;
每月一行
SELECT
yr, mh,
COUNT(CASE WHEN type = 'Post' THEN 1 END) AS count_posts,
COUNT(CASE WHEN type <> 'Post' THEN 1 END) AS count_nonposts
FROM
(
SELECT
TO_CHAR(createdatutc, 'YYYY') AS yr,
TO_CHAR(createdatutc, 'MM') AS mh,
type
FROM conversations
WHERE EXTRACT(YEAR FROM createdatutc) = 2018
) yr2018
GROUP BY yr, mh
ORDER BY yr, mh;
您可以在不使用子查询(派生表)的情况下执行此操作,但之后您将不得不一次又一次地重复相同的表达式。