sql - 如何修复 GROUP BY 中不允许的聚合函数?
问题描述
我有以下数据:
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 上添加 group by:
[42803] ERROR: aggregate functions are not allowed in GROUP BY Position: 26
如何克服这个问题?你能帮我指出我的错误在哪里吗?
谢谢你。
解决方案
您应该为添加的列使用聚合,例如:
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;
推荐阅读
- css - 使用 flex-layout 设置宽度导致左侧被切断而没有滚动
- python-3.x - 如何从另一列的截断值填充数据框中的一列
- apache-spark - 将数据从 Spark 结构化流加载到 ArrayList
- spring-boot - Spring Boot 应用程序中的 Swagger2 不会使用自定义 Docket 获取 @ApiResponses
- django - Django迁移删除和更新
- linux - 如何在 ubuntu 上的 /var/www 中创建目录?设置权限
- visual-studio - MDX 查询在 SSMS 中有效,但在 Visual Studio 中无效
- c# - 从控制器检索数据并返回 ajax 调用失败
- jquery - 单击菜单容器外部时如何关闭下拉菜单?
- android - Android 游戏和其他基于引擎的复杂应用程序如何通过 - 或以其他方式处理 - 进程死亡来保持状态?