A friend asked about a way to report the first three semesters that a group of students were documented as being present, and report those values each in a column. <\/p>\n
The tricky thing is that the semesters students attend are rarely the same. I started out with a very naive query (and sorry for the bad formatting that follows.. i need to find some good SQL formatting markup) just to get some initial results: <\/p>\n
That query pretty much sucks, requiring five sequential scans of ‘assoc’:<\/p>\n So, he reminded me about custom aggregates! I did a little searching and found an example function that I added an extra CASE statement that stops the aggregate from adding more than three items to the array returned:<\/p>\n And finally, I declared an aggregate:<\/p>\n One problem though – we want the array returned to be only the first<\/b> three semesters, rather than any three semesters a student has a record for. Meaning, we need to sort the information passed to the aggregate function. We could do this inside the aggregate itself (bubble sort, anyone?) or we can presort the input! I chose presorting, to avoid writing a real ugly case statement.<\/p>\n My query (compatible with 8.3 or higher): <\/p>\n Which yields the much nicer query plan, requiring just two sequential scans: <\/p>\n I ran my queries by Magnus, and he reminded me that what I really needed was ORDER BY in my aggregate! Fortunately, 9.0 has exactly this feature: <\/p>\n Which results in the following plan:<\/p>\n A final alternative would be to transform the IN query into a JOIN:<\/p>\n And the plan isn’t much different:<\/p>\n Any other suggestions for this type of query? <\/p>\n I’ve attached the file I was using to test this out. A friend asked about a way to report the first three semesters that a group of students were documented as being present, and report those values each in a column. The tricky thing is that the semesters students attend are … Continue reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[97,9],"tags":[328,358,360,359,647,624],"class_list":["post-2059","post","type-post","status-publish","format-standard","hentry","category-postgres","category-postgresql","tag-9-0","tag-custom-aggregates","tag-features","tag-order-by","tag-postgres","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/2059","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=2059"}],"version-history":[{"count":10,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/2059\/revisions"}],"predecessor-version":[{"id":2084,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/2059\/revisions\/2084"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=2059"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=2059"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=2059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nselect student,
\n(SELECT semester as sem1 FROM assoc a2 WHERE a2.student IN (a1.student) ORDER BY sem1 LIMIT 1) as sem1,
\n(SELECT semester as sem1 FROM assoc a2 WHERE a2.student IN (a1.student) ORDER BY sem1 LIMIT 1 offset 1) as sem2,
\n(SELECT semester as sem1 FROM assoc a2 WHERE a2.student IN (a1.student) ORDER BY sem1 LIMIT 1 offset 2) as sem3
\nFROM assoc a1
\nWHERE
\nstudent IN ( select student from assoc group by student HAVING count(*) > 2)
\nGROUP BY student;
\n<\/code><\/p>\n\r\n QUERY PLAN \r\n HashAggregate (cost=3913.13..315256.94 rows=78 width=2)\r\n -> Hash Semi Join (cost=1519.18..3718.08 rows=78017 width=2)\r\n Hash Cond: (a1.student = assoc.student)\r\n -> Seq Scan on assoc a1 (cost=0.00..1126.17 rows=78017 width=2)\r\n -> Hash (cost=1518.20..1518.20 rows=78 width=32)\r\n -> HashAggregate (cost=1516.26..1517.42 rows=78 width=2)\r\n Filter: (count(*) > 2)\r\n -> Seq Scan on assoc (cost=0.00..1126.17 rows=78017 width=2)\r\n SubPlan 1\r\n -> Limit (cost=1326.21..1326.22 rows=1 width=3)\r\n -> Sort (cost=1326.21..1328.71 rows=1000 width=3)\r\n Sort Key: a2.semester\r\n -> Seq Scan on assoc a2 (cost=0.00..1321.21 rows=1000 width=3)\r\n Filter: (student = a1.student)\r\n SubPlan 2\r\n -> Limit (cost=1331.22..1331.22 rows=1 width=3)\r\n -> Sort (cost=1331.21..1333.71 rows=1000 width=3)\r\n Sort Key: a2.semester\r\n -> Seq Scan on assoc a2 (cost=0.00..1321.21 rows=1000 width=3)\r\n Filter: (student = a1.student)\r\n SubPlan 3\r\n -> Limit (cost=1334.14..1334.14 rows=1 width=3)\r\n -> Sort (cost=1334.14..1336.64 rows=1000 width=3)\r\n Sort Key: a2.semester\r\n -> Seq Scan on assoc a2 (cost=0.00..1321.21 rows=1000 width=3)\r\n Filter: (student = a1.student)\r\n<\/pre>\n
\nCREATE FUNCTION array_append_not_null(anyarray,anyelement)
\nRETURNS anyarray
\n AS '
\nSELECT CASE WHEN $2 IS NULL THEN $1 WHEN array_upper($1, 1) > 2 THEN $1 ELSE array_append($1,$2) END
\n'
\nLANGUAGE sql IMMUTABLE RETURNS NULL ON NULL INPUT;
\n<\/code><\/p>\n
\nCREATE AGGREGATE three_semesters_not_null (
\n sfunc = array_append_not_null,
\n basetype = anyelement,
\n stype = anyarray,
\n initcond = '{}'
\n);
\n<\/code><\/p>\n
\nSELECT sorted.student, three_semesters_not_null(sorted.semester)
\n\tFROM (SELECT student, semester from assoc order by semester ) as sorted
\nWHERE
\n\tsorted.student IN (select a.student from assoc a group by a.student HAVING count(*) > 2)
\nGROUP BY sorted.student;
\n<\/code><\/p>\n\r\n QUERY PLAN \r\n HashAggregate (cost=11722.96..11725.46 rows=200 width=64)\r\n -> Hash Semi Join (cost=10052.32..11570.82 rows=30427 width=64)\r\n Hash Cond: (assoc.student = a.student)\r\n -> Sort (cost=8533.14..8728.18 rows=78017 width=5)\r\n Sort Key: assoc.semester\r\n -> Seq Scan on assoc (cost=0.00..1126.17 rows=78017 width=5)\r\n -> Hash (cost=1518.20..1518.20 rows=78 width=32)\r\n -> HashAggregate (cost=1516.26..1517.42 rows=78 width=2)\r\n Filter: (count(*) > 2)\r\n -> Seq Scan on assoc a (cost=0.00..1126.17 rows=78017 width=2)\r\n\r\n<\/pre>\n
\nSELECT student,
\n three_semesters_not_null(semester order by semester asc ) as first_three_semesters
\nFROM assoc
\nWHERE student IN (select student from assoc group by student HAVING count(*) > 2)
\n\tGROUP BY student;
\n<\/code><\/p>\n\r\n QUERY PLAN \r\n GroupAggregate (cost=11125.05..11711.15 rows=78 width=5)\r\n -> Sort (cost=11125.05..11320.09 rows=78017 width=5)\r\n Sort Key: public.assoc.student\r\n -> Hash Semi Join (cost=1519.18..3718.08 rows=78017 width=5)\r\n Hash Cond: (public.assoc.student = public.assoc.student)\r\n -> Seq Scan on assoc (cost=0.00..1126.17 rows=78017 width=5)\r\n -> Hash (cost=1518.20..1518.20 rows=78 width=32)\r\n -> HashAggregate (cost=1516.26..1517.42 rows=78 width=2)\r\n Filter: (count(*) > 2)\r\n -> Seq Scan on assoc (cost=0.00..1126.17 rows=78017 width=2)\r\n\r\n<\/pre>\n
\nSELECT a.student,
\n\tthree_semesters_not_null(a.semester order by a.semester asc ) as first_three_semesters
\nFROM assoc a
\n\tJOIN (select student from assoc group by student HAVING count(*) > 2) as b ON b.student = a.student
\nGROUP BY a.student;
\n<\/code><\/p>\n\r\n QUERY PLAN \r\n GroupAggregate (cost=11125.05..11711.15 rows=78 width=5)\r\n -> Sort (cost=11125.05..11320.09 rows=78017 width=5)\r\n Sort Key: a.student\r\n -> Hash Join (cost=1519.18..3718.08 rows=78017 width=5)\r\n Hash Cond: (a.student = assoc.student)\r\n -> Seq Scan on assoc a (cost=0.00..1126.17 rows=78017 width=5)\r\n -> Hash (cost=1518.20..1518.20 rows=78 width=32)\r\n -> HashAggregate (cost=1516.26..1517.42 rows=78 width=2)\r\n Filter: (count(*) > 2)\r\n -> Seq Scan on assoc (cost=0.00..1126.17 rows=78017 width=2)\r\n\r\n<\/pre>\n
\ncustom_aggregates.sql<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"