sql - 如何修复 GROUP BY 中不允许的聚合函数？

Question

我有以下数据:

table: identifier

objectid    type    name            value
9345783451  GUID    msisdn          bc3b2286379da25fd6ef3bac120827589c783
9345783451  UMID    umid            f701a99c-96c9-4d4a-8508-932eeeaca77c
9345783451  UMDID   umdid           2840f267-4bdd-4af4-8b36-72badbce1e11
9345783451  UMDID   umdid           bbe467f5-0fc4-4624-8b01-dd656767d3b2
9345783451  GUID    encrypted       ZPmBF2Spq8DBX0wl
9345783451  UMDID   umdid           a4c6b3cb-938f-4ae5-8246-f2612ffd946b
9345783451  UMDID   umdid           2a8e5f98-5a74-431e-bbaf-7c75fdeb991a
9345783451  UMDID   umdid           269a39b9-1122-4d08-9ca4-36f6c8e00e8e
9345783451  COOKIE  amcv            39852788960115122553605179944081330813
9345783451  UMDID   umdid           7715969f-63ab-4540-952c-73beafb46024
9345783451  GROUPID ban-sha2        1d98f855e9a4fbeba1937f774b6dbab2ca194b5
9345783451  COOKIE  token           21agqB6x_H8.1575825731298.aXHr4GoVupopE
9345783451  GUID    acn             12433792
9345783451  UMDID   umdid           f2cf7402-21d2-44ea-b432-66e997cfebbf
9345783451  GUID    targetId        1255625699
9345783451  UMDID   umdid           b8d55cdd-4a95-4e07-8eeb-f281a0961a6a
9345783451  UMDID   umdid           af890608-b512-4a96-9274-f39f388ff442
9345783451  UMDID   umdid           0c55ecb1-e24e-419d-97a1-48f6eba45fe0

然后我执行以下查询:

select objectid,
           count(objectid) filter ( where type = 'GUID' )                                                                     as guid_count,
           count(objectid) filter ( where type = 'GUID' and (name in
                                                             ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
           count(objectid) filter ( where name = 'umdid' )                                                                    as umdid_count
    from identifier
    where objectid = '9345783451'
    group by objectid;

结果正确:

objectid    guid_count cuid_count umdid_count
9345783451  4          3          10

然后我添加两列，名称和值:

select objectid,
               count(objectid) filter ( where type = 'GUID' )                                                                     as guid_count,
               count(objectid) filter ( where type = 'GUID' and (name in
                                                                 ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
               count(objectid) filter ( where name = 'umdid' )                                                                    as umdid_count,
               case
                   when identifier.name = 'acn' then identifier.value::text
                   else '' end                                                                                                                          as acn
        from identifier
        where objectid = '9345783451'
        group by objectid, name, value;

返回:

objectid   guid_count cuid_count umdid_count acn
9345783451  0          0         1           "" 
9345783451  1          0         0           "" 
9345783451  0          0         1           "" 
9345783451  0          0         0           "" 
9345783451  0          0         1           "" 
9345783451  1          1         0           12433792 
9345783451  0          0         1           "" 
9345783451  0          0         1           "" 
9345783451  1          1         0           "" 
9345783451  0          0         0           "" 
9345783451  0          0         1           "" 
9345783451  0          0         1           "" 
9345783451  0          0         0           "" 
9345783451  0          0         1           "" 
9345783451  0          0         1           "" 
9345783451  0          0         0           "" 
9345783451  1          1         0           "" 
9345783451  0          0         1           ""

"" empty string

我期望的是:

objectid    guid_count cuid_count umdid_count acn
9345783451  4          3          10          12433792

如果我在 guid_count、cuid_count 和 umdid_count 上添加分组依据:

[42803] ERROR: aggregate functions are not allowed in GROUP BY Position: 26

如何克服这个问题？你能帮我指出我的错误在哪里吗？

谢谢。

Answer 1

您应该为添加的列使用聚合，例如:

select objectid,
    count(objectid) filter ( where type = 'GUID' ) as guid_count,
    count(objectid) filter ( 
        where type = 'GUID' 
        and (name in ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) 
        ) as cuid_count,
    count(objectid) filter ( where name = 'umdid' ) as umdid_count,
    max(case
        when identifier.name = 'acn' then identifier.value::text
        else '' end) as acn
from identifier
where objectid = '9345783451'
group by objectid;