Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit64d0b8b

Browse files
committed
Attached is an update to contrib/tablefunc. It implements a new hashed
version of crosstab. This fixes a major deficiency in real-world use ofthe original version. Easiest to undestand with an illustration:Data:-------------------------------------------------------------------select * from cth; id | rowid | rowdt | attribute | val----+-------+---------------------+----------------+--------------- 1 | test1 | 2003-03-01 00:00:00 | temperature | 42 2 | test1 | 2003-03-01 00:00:00 | test_result | PASS 3 | test1 | 2003-03-01 00:00:00 | volts | 2.6987 4 | test2 | 2003-03-02 00:00:00 | temperature | 53 5 | test2 | 2003-03-02 00:00:00 | test_result | FAIL 6 | test2 | 2003-03-02 00:00:00 | test_startdate | 01 March 2003 7 | test2 | 2003-03-02 00:00:00 | volts | 3.1234(7 rows)Original crosstab:-------------------------------------------------------------------SELECT * FROM crosstab( 'SELECT rowid, attribute, val FROM cth ORDER BY 1,2',4)AS c(rowid text, temperature text, test_result text, test_startdatetext, volts text); rowid | temperature | test_result | test_startdate | volts-------+-------------+-------------+----------------+-------- test1 | 42 | PASS | 2.6987 | test2 | 53 | FAIL | 01 March 2003 | 3.1234(2 rows)Hashed crosstab:-------------------------------------------------------------------SELECT * FROM crosstab( 'SELECT rowid, attribute, val FROM cth ORDER BY 1', 'SELECT DISTINCT attribute FROM cth ORDER BY 1')AS c(rowid text, temperature int4, test_result text, test_startdatetimestamp, volts float8); rowid | temperature | test_result | test_startdate | volts-------+-------------+-------------+---------------------+-------- test1 | 42 | PASS | | 2.6987 test2 | 53 | FAIL | 2003-03-01 00:00:00 | 3.1234(2 rows)Notice that the original crosstab slides data over to the left in theresult tuple when it encounters missing data. In order to work aroundthis you have to be make your source sql do all sorts of contortions(cartesian join of distinct rowid with distinct attribute; left jointhat back to the real source data). The new version avoids this bybuilding a hash table using a second distinct attribute query.The new version also allows for "extra" columns (see the README) andallows the result columns to be coerced into differing datatypes if theyare suitable (as shown above).In testing a "real-world" data set (69 distinct rowid's, 27 distinctcategories/attributes, multiple missing data points) I saw about a5-fold improvement in execution time (from about 2200 ms old, to 440 msnew).I left the original version intact because: 1) BC, 2) it is probablyslightly faster if you know that you have no missing attributes.README and regression test adjustments included. If there are noobjections, please apply.Joe Conway
1 parentadd932e commit64d0b8b

File tree

6 files changed

+655
-1
lines changed

6 files changed

+655
-1
lines changed

‎contrib/tablefunc/README.tablefunc

Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -333,6 +333,125 @@ AS ct(row_name text, category_1 text, category_2 text, category_3 text);
333333
==================================================================
334334
Name
335335

336+
crosstab(text, text) - returns a set of row_name, extra, and
337+
category value columns
338+
339+
Synopsis
340+
341+
crosstab(text source_sql, text category_sql)
342+
343+
Inputs
344+
345+
source_sql
346+
347+
A SQL statement which produces the source set of data. The SQL statement
348+
must return one row_name column, one category column, and one value
349+
column. It may also have one or more "extra" columns.
350+
351+
The row_name column must be first. The category and value columns
352+
must be the last two columns, in that order. "extra" columns must be
353+
columns 2 through (N - 2), where N is the total number of columns.
354+
355+
The "extra" columns are assumed to be the same for all rows with the
356+
same row_name. The values returned are copied from the first row
357+
with a given row_name and subsequent values of these columns are ignored
358+
until row_name changes.
359+
360+
e.g. source_sql must produce a set something like:
361+
SELECT row_name, extra_col, cat, value FROM foo;
362+
363+
row_name extra_col cat value
364+
----------+------------+-----+---------
365+
row1 extra1 cat1 val1
366+
row1 extra1 cat2 val2
367+
row1 extra1 cat4 val4
368+
row2 extra2 cat1 val5
369+
row2 extra2 cat2 val6
370+
row2 extra2 cat3 val7
371+
row2 extra2 cat4 val8
372+
373+
category_sql
374+
375+
A SQL statement which produces the distinct set of categories. The SQL
376+
statement must return one category column only. category_sql must produce
377+
at least one result row or an error will be generated. category_sql
378+
must not produce duplicate categories or an error will be generated.
379+
380+
e.g. SELECT DISTINCT cat FROM foo;
381+
382+
cat
383+
-------
384+
cat1
385+
cat2
386+
cat3
387+
cat4
388+
389+
Outputs
390+
391+
Returns setof record, which must be defined with a column definition
392+
in the FROM clause of the SELECT statement, e.g.:
393+
394+
SELECT * FROM crosstab(source_sql, cat_sql)
395+
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);
396+
397+
the example crosstab function produces a set something like:
398+
<== values columns ==>
399+
row_name extra cat1 cat2 cat3 cat4
400+
---------+-------+------+------+------+------
401+
row1 extra1 val1 val2 val4
402+
row2 extra2 val5 val6 val7 val8
403+
404+
Notes
405+
406+
1. source_sql must be ordered by row_name (column 1).
407+
408+
2. The number of values columns is determined at run-time. The
409+
column definition provided in the FROM clause must provide for
410+
the correct number of columns of the proper data types.
411+
412+
3. Missing values (i.e. not enough adjacent rows of same row_name to
413+
fill the number of result values columns) are filled in with nulls.
414+
415+
4. Extra values (i.e. source rows with category not found in category_sql
416+
result) are skipped.
417+
418+
5. Rows with a null row_name column are skipped.
419+
420+
421+
Example usage
422+
423+
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
424+
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
425+
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
426+
insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
427+
insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
428+
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
429+
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
430+
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
431+
432+
SELECT * FROM crosstab
433+
(
434+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
435+
'SELECT DISTINCT attribute FROM cth ORDER BY 1'
436+
)
437+
AS
438+
(
439+
rowid text,
440+
rowdt timestamp,
441+
temperature int4,
442+
test_result text,
443+
test_startdate timestamp,
444+
volts float8
445+
);
446+
rowid | rowdt | temperature | test_result | test_startdate | volts
447+
-------+--------------------------+-------------+-------------+--------------------------+--------
448+
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
449+
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
450+
(2 rows)
451+
452+
==================================================================
453+
Name
454+
336455
connectby(text, text, text, text, int[, text]) - returns a set
337456
representing a hierarchy (tree structure)
338457

‎contrib/tablefunc/expected/tablefunc.out

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,79 @@ SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''
123123
test2 | val5 | val6 | val7 | val8
124124
(2 rows)
125125

126+
--
127+
-- hash based crosstab
128+
--
129+
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
130+
NOTICE: CREATE TABLE will create implicit sequence 'cth_id_seq' for SERIAL column 'cth.id'
131+
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
132+
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
133+
-- the next line is intentionally left commented and is therefore a "missing" attribute
134+
-- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003');
135+
insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
136+
insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
137+
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
138+
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
139+
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
140+
-- return attributes as plain text
141+
SELECT * FROM crosstab(
142+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
143+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
144+
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
145+
rowid | rowdt | temperature | test_result | test_startdate | volts
146+
-------+--------------------------+-------------+-------------+----------------+--------
147+
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
148+
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | 01 March 2003 | 3.1234
149+
(2 rows)
150+
151+
-- this time without rowdt
152+
SELECT * FROM crosstab(
153+
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
154+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
155+
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
156+
rowid | temperature | test_result | test_startdate | volts
157+
-------+-------------+-------------+----------------+--------
158+
test1 | 42 | PASS | | 2.6987
159+
test2 | 53 | FAIL | 01 March 2003 | 3.1234
160+
(2 rows)
161+
162+
-- convert attributes to specific datatypes
163+
SELECT * FROM crosstab(
164+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
165+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
166+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
167+
rowid | rowdt | temperature | test_result | test_startdate | volts
168+
-------+--------------------------+-------------+-------------+--------------------------+--------
169+
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
170+
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
171+
(2 rows)
172+
173+
-- source query and category query out of sync
174+
SELECT * FROM crosstab(
175+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
176+
'SELECT DISTINCT attribute FROM cth WHERE attribute IN (''temperature'',''test_result'',''test_startdate'') ORDER BY 1')
177+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp);
178+
rowid | rowdt | temperature | test_result | test_startdate
179+
-------+--------------------------+-------------+-------------+--------------------------
180+
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS |
181+
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003
182+
(2 rows)
183+
184+
-- if category query generates no rows, get expected error
185+
SELECT * FROM crosstab(
186+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
187+
'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1')
188+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
189+
ERROR: load_categories_hash: provided categories SQL must return 1 column of at least one row
190+
-- if category query generates more than one column, get expected error
191+
SELECT * FROM crosstab(
192+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
193+
'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
194+
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
195+
ERROR: load_categories_hash: provided categories SQL must return 1 column of at least one row
196+
--
197+
-- connectby
198+
--
126199
-- test connectby with text based hierarchy
127200
CREATE TABLE connectby_text(keyid text, parent_keyid text);
128201
\copy connectby_text from 'data/connectby_text.data'

‎contrib/tablefunc/sql/tablefunc.sql

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,61 @@ SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''
3838
SELECT*FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =''group1'' ORDER BY 1,2;',3)AS c(rowidtext, att1text, att2text, att3text);
3939
SELECT*FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =''group1'' ORDER BY 1,2;',4)AS c(rowidtext, att1text, att2text, att3text, att4text);
4040

41+
--
42+
-- hash based crosstab
43+
--
44+
createtablecth(idserial, rowidtext, rowdttimestamp, attributetext, valtext);
45+
insert into cthvalues(DEFAULT,'test1','01 March 2003','temperature','42');
46+
insert into cthvalues(DEFAULT,'test1','01 March 2003','test_result','PASS');
47+
-- the next line is intentionally left commented and is therefore a "missing" attribute
48+
-- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003');
49+
insert into cthvalues(DEFAULT,'test1','01 March 2003','volts','2.6987');
50+
insert into cthvalues(DEFAULT,'test2','02 March 2003','temperature','53');
51+
insert into cthvalues(DEFAULT,'test2','02 March 2003','test_result','FAIL');
52+
insert into cthvalues(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
53+
insert into cthvalues(DEFAULT,'test2','02 March 2003','volts','3.1234');
54+
55+
-- return attributes as plain text
56+
SELECT*FROM crosstab(
57+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
58+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
59+
AS c(rowidtext, rowdttimestamp, temperaturetext, test_resulttext, test_startdatetext, voltstext);
60+
61+
-- this time without rowdt
62+
SELECT*FROM crosstab(
63+
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
64+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
65+
AS c(rowidtext, temperaturetext, test_resulttext, test_startdatetext, voltstext);
66+
67+
-- convert attributes to specific datatypes
68+
SELECT*FROM crosstab(
69+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
70+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
71+
AS c(rowidtext, rowdttimestamp, temperature int4, test_resulttext, test_startdatetimestamp, volts float8);
72+
73+
-- source query and category query out of sync
74+
SELECT*FROM crosstab(
75+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
76+
'SELECT DISTINCT attribute FROM cth WHERE attribute IN (''temperature'',''test_result'',''test_startdate'') ORDER BY 1')
77+
AS c(rowidtext, rowdttimestamp, temperature int4, test_resulttext, test_startdatetimestamp);
78+
79+
-- if category query generates no rows, get expected error
80+
SELECT*FROM crosstab(
81+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
82+
'SELECT DISTINCT attribute FROM cth WHERE attribute =''a'' ORDER BY 1')
83+
AS c(rowidtext, rowdttimestamp, temperature int4, test_resulttext, test_startdatetimestamp, volts float8);
84+
85+
-- if category query generates more than one column, get expected error
86+
SELECT*FROM crosstab(
87+
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
88+
'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
89+
AS c(rowidtext, rowdttimestamp, temperature int4, test_resulttext, test_startdatetimestamp, volts float8);
90+
91+
92+
--
93+
-- connectby
94+
--
95+
4196
-- test connectby with text based hierarchy
4297
CREATETABLEconnectby_text(keyidtext, parent_keyidtext);
4398
\copy connectby_textfrom'data/connectby_text.data'

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp