没有汇总功能的PostgreSQL枢轴行数据

发布于 2025-01-23 19:53:09 字数 1164 浏览 0 评论 0原文

我有一个表：

create table student
(
    key serial primary key,
    id int, 
    type_name text,
    updated_by text,
    updated date, 
    text_data text,
    int_data int
);

示例表和数据为

d2a9a46a46a46ded16145555fe68135ecd15e 可能具有同一主题的多行（标记）。我需要为每个学科的每个学生旋转所有分数。

我正在尝试使用以下查询旋转数据

select 
    id,
    max(updated_by) as updated_by,
    max(updated) as updated,
    max(case when type_name='Name' then text_data end) as "Name",
    max(case when type_name='Math' then int_data end) as "Math",
    max(case when type_name='English' then int_data end) as "English",
    max(case when type_name='Social' then int_data end) as "Social",
    max(case when type_name='Science' then int_data end) as "Science"
from
    stud 
group by 
    id

，但这并没有给出ID 1的所有标记，并且在Math主题中具有标记8和5。因为我在我的查询，我得到了单一价值。

但是似乎没有汇总函数，我们无法执行group_by。

在没有汇总函数的情况下，是否有任何方法可以低于预期函数

谢谢

原文

I have this table:

create table student
(
    key serial primary key,
    id int, 
    type_name text,
    updated_by text,
    updated date, 
    text_data text,
    int_data int
);

Sample table and data is here

A student id may have multiple rows(marks) for same subject. I would need to pivot all marks of each students for each subject.

I'm trying to pivot data with below query

select 
    id,
    max(updated_by) as updated_by,
    max(updated) as updated,
    max(case when type_name='Name' then text_data end) as "Name",
    max(case when type_name='Math' then int_data end) as "Math",
    max(case when type_name='English' then int_data end) as "English",
    max(case when type_name='Social' then int_data end) as "Social",
    max(case when type_name='Science' then int_data end) as "Science"
from
    stud 
group by 
    id

But this is not giving all marks for id 1 and in Math subject has marks 8 and 5. since i used max function in my query, i'm getting single value.

But it seems without aggregate function we can't do group_by.

Is there way in Postgresql to pivot data without aggregate functions to get below expected output

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

戴着白色围巾的女孩 2025-01-30 19:53:09

您需要的是另一个聚合，即 string_agg（） 以及 filter 等级，例如

SELECT id,
       MAX(text_data) AS "Name",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Math') AS "Math",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='English') AS "English",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Social') AS "Social",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Science') AS "Science"
  FROM stud 
 GROUP BY id

demo demo

What you need as another aggregation, namely STRING_AGG() along with FILTER clause such as

SELECT id,
       MAX(text_data) AS "Name",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Math') AS "Math",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='English') AS "English",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Social') AS "Social",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Science') AS "Science"
  FROM stud 
 GROUP BY id

Demo

回复收藏 0 原文

~没有更多了~