| blob | e09e3310165853e1b3e49191e5828ec3da22daad |
1 --
2 -- \crosstabview
3 --
4 CREATE TABLE ctv_data (v, h, c, i, d) AS
5 VALUES
6 ('v1','h2','foo', 3, '2015-04-01'::date),
7 ('v2','h1','bar', 3, '2015-01-02'),
8 ('v1','h0','baz', NULL, '2015-07-12'),
9 ('v0','h4','qux', 4, '2015-07-15'),
10 ('v0','h4','dbl', -3, '2014-12-15'),
11 ('v0',NULL,'qux', 5, '2014-07-15'),
12 ('v1','h2','quux',7, '2015-04-04');
13 -- make plans more stable
14 ANALYZE ctv_data;
15 -- running \crosstabview after query uses query in buffer
16 SELECT v, EXTRACT(year FROM d), count(*)
17 FROM ctv_data
18 GROUP BY 1, 2
19 ORDER BY 1, 2;
20 v | extract | count
21 ----+---------+-------
22 v0 | 2014 | 2
23 v0 | 2015 | 1
24 v1 | 2015 | 3
25 v2 | 2015 | 1
26 (4 rows)
28 -- basic usage with 3 columns
29 \crosstabview
30 v | 2014 | 2015
31 ----+------+------
32 v0 | 2 | 1
33 v1 | | 3
34 v2 | | 1
35 (3 rows)
37 -- ordered months in horizontal header, quoted column name
38 SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
39 count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1
40 \crosstabview v "month name" 4 num
41 v | Jan | Apr | Jul | Dec
42 ----+-----+-----+-----+-----
43 v0 | | | 2 | 1
44 v1 | | 2 | 1 |
45 v2 | 1 | | |
46 (3 rows)
48 -- ordered months in vertical header, ordered years in horizontal header
49 SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS """month"" name",
50 EXTRACT(month FROM d) AS month,
51 format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
52 FROM ctv_data
53 GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
54 ORDER BY month
55 \crosstabview """month"" name" year format year
56 "month" name | 2014 | 2015
57 --------------+-----------------+----------------
58 Jan | | sum=3 avg=3.0
59 Apr | | sum=10 avg=5.0
60 Jul | sum=5 avg=5.0 | sum=4 avg=4.0
61 Dec | sum=-3 avg=-3.0 |
62 (4 rows)
64 -- combine contents vertically into the same cell (V/H duplicates)
65 SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
66 \crosstabview 1 2 3
67 v | h4 | | h0 | h2 | h1
68 ----+-----+-----+-----+------+-----
69 v0 | qux+| qux | | |
70 | dbl | | | |
71 v1 | | | baz | foo +|
72 | | | | quux |
73 v2 | | | | | bar
74 (3 rows)
76 -- horizontal ASC order from window function
77 SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
78 FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
79 \crosstabview v h c r
80 v | h0 | h1 | h2 | h4 |
81 ----+-----+-----+------+-----+-----
82 v0 | | | | qux+| qux
83 | | | | dbl |
84 v1 | baz | | foo +| |
85 | | | quux | |
86 v2 | | bar | | |
87 (3 rows)
89 -- horizontal DESC order from window function
90 SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
91 FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
92 \crosstabview v h c r
93 v | | h4 | h2 | h1 | h0
94 ----+-----+-----+------+-----+-----
95 v0 | qux | qux+| | |
96 | | dbl | | |
97 v1 | | | foo +| | baz
98 | | | quux | |
99 v2 | | | | bar |
100 (3 rows)
102 -- horizontal ASC order from window function, NULLs pushed rightmost
103 SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
104 FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
105 \crosstabview v h c r
106 v | h0 | h1 | h2 | h4 |
107 ----+-----+-----+------+-----+-----
108 v0 | | | | qux+| qux
109 | | | | dbl |
110 v1 | baz | | foo +| |
111 | | | quux | |
112 v2 | | bar | | |
113 (3 rows)
115 -- only null, no column name, 2 columns: error
116 SELECT null,null \crosstabview
117 \crosstabview: query must return at least three columns
118 -- only null, no column name, 3 columns: works
119 SELECT null,null,null \crosstabview
120 ?column? |
121 ----------+--
122 |
123 (1 row)
125 -- null display
126 \pset null '#null#'
127 SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
128 GROUP BY v, h ORDER BY h,v
129 \crosstabview v h i
130 v | h0 | h1 | h2 | h4 | #null#
131 ----+--------+----+----+----+--------
132 v1 | #null# | | 3 +| |
133 | | | 7 | |
134 v2 | | 3 | | |
135 v0 | | | | 4 +| 5
136 | | | | -3 |
137 (3 rows)
139 \pset null ''
140 -- refer to columns by position
141 SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
142 FROM ctv_data GROUP BY v, h ORDER BY h,v
143 \crosstabview 2 1 4
144 h | v1 | v2 | v0
145 ----+------+-----+-----
146 h0 | baz | |
147 h1 | | bar |
148 h2 | foo +| |
149 | quux | |
150 h4 | | | qux+
151 | | | dbl
152 | | | qux
153 (5 rows)
155 -- refer to columns by positions and names mixed
156 SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
157 FROM ctv_data GROUP BY v, h ORDER BY h,v
158 \crosstabview 1 "h" 4
159 v | h0 | h1 | h2 | h4 |
160 ----+-----+-----+------+-----+-----
161 v1 | baz | | foo +| |
162 | | | quux | |
163 v2 | | bar | | |
164 v0 | | | | qux+| qux
165 | | | | dbl |
166 (3 rows)
168 -- refer to columns by quoted names, check downcasing of unquoted name
169 SELECT 1 as "22", 2 as b, 3 as "Foo"
170 \crosstabview "22" B "Foo"
171 22 | 2
172 ----+---
173 1 | 3
174 (1 row)
176 -- error: bad column name
177 SELECT v,h,c,i FROM ctv_data
178 \crosstabview v h j
179 \crosstabview: column name not found: "j"
180 -- error: need to quote name
181 SELECT 1 as "22", 2 as b, 3 as "Foo"
182 \crosstabview 1 2 Foo
183 \crosstabview: column name not found: "foo"
184 -- error: need to not quote name
185 SELECT 1 as "22", 2 as b, 3 as "Foo"
186 \crosstabview 1 "B" "Foo"
187 \crosstabview: column name not found: "B"
188 -- error: bad column number
189 SELECT v,h,i,c FROM ctv_data
190 \crosstabview 2 1 5
191 \crosstabview: column number 5 is out of range 1..4
192 -- error: same H and V columns
193 SELECT v,h,i,c FROM ctv_data
194 \crosstabview 2 h 4
195 \crosstabview: vertical and horizontal headers must be different columns
196 -- error: too many columns
197 SELECT a,a,1 FROM generate_series(1,3000) AS a
198 \crosstabview
199 \crosstabview: maximum number of columns (1600) exceeded
200 -- error: only one column
201 SELECT 1 \crosstabview
202 \crosstabview: query must return at least three columns
203 DROP TABLE ctv_data;
204 -- check error reporting (bug #14476)
205 CREATE TABLE ctv_data (x int, y int, v text);
206 INSERT INTO ctv_data SELECT 1, x, '*' || x FROM generate_series(1,10) x;
207 SELECT * FROM ctv_data \crosstabview
208 x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10
209 ---+----+----+----+----+----+----+----+----+----+-----
210 1 | *1 | *2 | *3 | *4 | *5 | *6 | *7 | *8 | *9 | *10
211 (1 row)
213 INSERT INTO ctv_data VALUES (1, 10, '*'); -- duplicate data to cause error
214 SELECT * FROM ctv_data \crosstabview
215 \crosstabview: query result contains multiple data values for row "1", column "10"
216 DROP TABLE ctv_data;