我有一个联合查询:

(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;

您可以在不使用子查询(派生表)的情况下执行此操作,但之后您将不得不一次又一次地重复相同的表达式。

10-07 19:17
查看更多