How PostgreSQL database Implements crosstab

Here I will demonstrate the postgresql inside how to achieve the display of the cross table, as to what is the cross table, I do not say more, the Niang go Oh.
The original table data is as follows:

Click (here) to collapse or open

t_girl=# select * from score;
name | Subject | Score
-------+---------+-------
Lucy | 中文版 | 100
Lucy | Physics | 90
Lucy | Math | 85
Lily | 中文版 | 95
Lily | Physics | 81
Lily | Math | 84
David | 中文版 | 100
David | Physics | 86
David | Math | 89
Simon | 中文版 | 90
Simon | Physics | 76
Simon | Math | 79
(rows)


time:2.066 ms





Want to achieve the following results:


Click (here) to collapse or open

name | 中文版 | Physics | Math
-------+---------+---------+------
Simon | 90 | 76 | 79
Lucy | 100 | 90 | 85
Lily | 95 | 81 | 84
David | 100 | 86 | 89




There are several ways to do this:


1, with standard SQL display

Click (here) to collapse or open

t_girl=# select Name,
t_girl-# sum (case when subject = ' 中文版 ' then score else 0 end) as "中文版",
t_girl-# sum (case when subject = ' Physics ' then score else 0-end) as "physics",
t_girl-# sum (case when subject = ' math ' then score else 0) as "math"
t_girl-# from Score
t_girl-# GROUP BY name Desc;
name | 中文版 | Physics | Math
-------+---------+---------+------
Simon | 90 | 76 | 79
Lucy | 100 | 90 | 85
Lily | 95 | 81 | 84
David | 100 | 86 | 89
(4 rows)


Time:1.123 ms




2, with the PostgreSQL provided by the third party extended tablefunc to bring the function realization
The following function crosstab SQL must have three fields, name, category, and category values as the starting arguments, and must be of the name, the category value as the output parameter.

Click (here) to collapse or open

t_girl=# SELECT *
From crosstab (' Select Name,subject,score to score ORDER BY name Desc ', $ $values (' Chinese ':: Text), (' Physics ':: Text), (' Math ':: text) $$
As score (name text, 中文版 int, physics int, Math int);
name | 中文版 | Physics | Math
-------+---------+---------+------
Simon | 90 | 76 | 79
Lucy | 100 | 90 | 85
Lily | 95 | 81 | 84
David | 100 | 86 | 89
(4 rows)


time:2.059 ms





3, using the PostgreSQL of its own aggregation function to achieve

Click (here) to collapse or open

t_girl=# Select Name,split_part (Split_part (TMP, ', ', ', 1), ': ', 2 "as" 中文版 ",
t_girl-# Split_part (Split_part (TMP, ', ', ', 2), ': ', 2 "as" Physics ",
t_girl-# Split_part (TMP, ', ', 3), ': ', 2) as "Math"
t_girl-# from
t_girl-# (
T_girl (# Select Name,string_agg (subject| | ': ' | | Score, ', ') as TMP from score GROUP BY name Desc
T_girl (#) as T;
name | 中文版 | Physics | Math
-------+---------+---------+------
Simon | 90 | 76 | 79
Lucy | 100 | 90 | 85
Lily | 95 | 81 | 84
David | 100 | 86 | 89
(4 rows)


time:2.396 ms







4. Storage function Implementation

Click (here) to collapse or open

Create or Replace function func_ytt_crosstab_py ()
Returns Setof Ytt_crosstab
As
$ytt $
For row in Plpy.cursor ("Select Name,string_agg" (subject| | ': ' | | Score, ', ') as TMP from score GROUP by name Desc "):
A = row[' tmp '].split (', ')
Yield (row[' name '],a[0].split (': ') [1],a[1].split (': ') [1],a[2].split (': ') [1])
$ytt $ language Plpythonu;


t_girl=# Select Name,english,physics,math from func_ytt_crosstab_py ();
name | 中文版 | Physics | Math
-------+---------+---------+------
Simon | 90 | 76 | 79
Lucy | 100 | 90 | 85
Lily | 95 | 81 | 84
David | 100 | 86 | 89
(4 rows)


time:2.687 ms






5, with Plpgsql to achieve

Click (here) to collapse or open

t_girl=# Create type Ytt_crosstab as (name text, 中文版 text, physics text, Math text);
CREATE TYPE
time:22.518 ms


Create or Replace function Func_ytt_crosstab ()
Returns Setof Ytt_crosstab
As
$ytt $
Declare v_name text: = ';
V_english text: = ';
V_physics text: = ';
V_math text: = ';
V_tmp_result text: = ';
Declare CS1 cursor FOR select Name,string_agg (subject| | ': ' | | Score, ', ') from score group by name, Desc;
Begin
Open CS1;
Loop
Fetch CS1 into V_name,v_tmp_result;
Exit when is not found;
V_english = Split_part (Split_part (V_tmp_result, ', ', 1), ': ', 2);
V_physics = Split_part (Split_part (V_tmp_result, ', ', 2), ': ', 2);
V_math = Split_part (Split_part (V_tmp_result, ', ', 3), ': ', 2);
return query select V_name,v_english,v_physics,v_math;
End Loop;
End
$ytt $ language Plpgsql;


t_girl=# Select Name,english,physics,math from Func_ytt_crosstab ();
name | 中文版 | Physics | Math
-------+---------+---------+------
Simon | 90 | 76 | 79
Lucy | 100 | 90 | 85
Lily | 95 | 81 | 84
David | 100 | 86 | 89
(4 rows)


time:2.127 ms