带有动态列和联接计数的PostgreSQL查询 [英] PostgreSQL Query with dynamic columns and counts from join

问题描述

我可能对此考虑过多，因为我不太确定从哪里开始...

I'm probably overthinking this, because I'm not really sure where to start... But here goes:

我有以下

学生

评估

学生评估

student_id | Assessment 1 | Assessment 2 | Assessment 3 | Assessment 4
1            3              1              2              0
2            1              0              0              0
3            2              1              1              0
4            5              3              3              0
5            1              5              0              0
6            2              1              2              0

第二次查询通过"

student_id | Assessment 1 | Assessment 2 | Assessment 3 | Assessment 4
1            t              t              f              f
2            t              t              f              f
3            t              t              f              f
4            t              t              t              f
5            t              t              f              f
6            t              t              t              f

让我大跌眼镜的部分不是进行每次评估的联接，甚至没有手动定义列，而是生成"每个评估存在的列.

The part that is tripping me up is not doing a join for EVERY assessment, and not even defining the columns manually but rather "generating" each column for assessments that exist.

我觉得这很简单，我太劳累了，无法立即解决:)预先感谢您的帮助，这是

I feel like it's simple, and I am just too overworked to figure it out right now :) thank you in advance for your help, and here's a SQL Fiddle of data as an example

推荐答案

查询"的简单查询，

select student_id,sum(case when assessment_id=1 then 1 else 0 end) as "Assessment 1",
sum(case when assessment_id=2 then 1 else 0 end) as "Assessment 2",
sum(case when assessment_id=3 then 1 else 0 end) as "Assessment 3",
sum(case when assessment_id=4 then 1 else 0 end) as "Assessment 4",
sum(case when assessment_id=5 then 1 else 0 end) as "Assessment 5",
sum(case when assessment_id=6 then 1 else 0 end) as "Assessment 6"
from assessments_students
group by student_id
order by student_id

在crosstab()函数中，还需要显式定义列名称，例如评估1"，评估2"等等.

In crosstab() function also, need to define columns name explicitly like "Assessment 1","Assessment 2" and so on.

或编写用于创建动态查询的自定义函数，然后使用EXECUTE语句执行.

Or write custom function for creating dynamic query, and execute using EXECUTE statement.

DROP FUNCTION get_Attempts() ;

CREATE OR REPLACE FUNCTION get_Attempts() RETURNS text AS
$BODY$
DECLARE
        r1 record;
        str_query text := '';
 BEGIN
    str_query :='select student_id,';
    FOR r1 IN SELECT "_id" , "name" FROM Assessments
    LOOP
      str_query:= str_query || 
                  'sum(case when assessment_id=' || r1."_id" || ' then 1 else 0 end) as  "' || r1.name ||'",' ;
    END LOOP;
    str_query:=trim( trailing  ',' from str_query); -- remove last semicolon
    str_query:= str_query || ' from assessments_students group by student_id order by student_id';
    return str_query;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM get_Attempts();

---

第二次查询通过"

---

Second query for "passed"

select student_id,
max(case when assessment_id=1 and passed='t' then 't' else 'f' end) as  "Assessment 1",
max(case when assessment_id=2 and passed='t' then 't' else 'f' end) as  "Assessment 2",
max(case when assessment_id=3 and passed='t' then 't' else 'f' end) as  "Assessment 3",
max(case when assessment_id=4 and passed='t' then 't' else 'f' end) as  "Assessment 4",
max(case when assessment_id=5 and passed='t' then 't' else 'f' end) as  "Assessment 5",
max(case when assessment_id=6 and passed='t' then 't' else 'f' end) as  "Assessment 6" 
from assessments_students 
group by student_id 
order by student_id

它的功能看起来像

DROP FUNCTION get_passed() ;

CREATE OR REPLACE FUNCTION get_passed() RETURNS text AS
$BODY$
DECLARE
        r1 record;
        str_query text := '';
 BEGIN
    str_query :='select student_id,';
    FOR r1 IN SELECT "_id" , "name" FROM Assessments
    LOOP
      str_query:= str_query || 
                  'max(case when assessment_id=' || r1."_id" || ' and passed=''t'' then ''t'' else ''f'' end) as  "' || r1.name ||'",' ;
    END LOOP;
    str_query:=trim( trailing  ',' from str_query); -- remove last semicolon
    str_query:= str_query || ' from assessments_students group by student_id order by student_id';

    return str_query;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM get_passed();

这篇关于带有动态列和联接计数的PostgreSQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！