sql - union 在 PostgreSQL 中没有按预期工作

Question

我有一个联合查询:

(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: ERROR: column "conversations.createdatutc" must appear in the GROUP BY clause or be used in an aggregate function`

但是，如果我单独运行它们，它们会正常运行，这里 createdatutc 是一个时间戳字段

Answer 1

在派生表中进行 to_char 提取等，group by 结果:

select "Yr", "Mh", count(postid), type
from
(
    SELECT
        to_char(createdatutc,'YYYY') as "Yr",
        to_char(createdatutc,'MM') as "Mh",
        postid,
        case when type = 'Post' then 'Post' else 'NotPost' end type
    FROM conversations
) dt
where "Yr" = 2018
group by  "Yr", "Mh", type