Skip site navigation (1) Skip section navigation (2)

Re: Pet Peeves?

From: Octavio Alvarez <alvarezp(at)alvarezp(dot)ods(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Pet Peeves?
Date: 2009-01-31 22:35:16
Message-ID: 1233441316.5647.64.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-general
On Sat, 2009-01-31 at 18:32 +0000, Greg Stark wrote:
> On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez
> <alvarezp(at)alvarezp(dot)ods(dot)org> wrote:
> >
> > It doesn't really matter. Since crosstabs are just a presentational
> > variation to a query with aggregate functions and GROUP BY clauses,
> 
> 
> Why are crosstabs just a presentation issue any more than GROUP BY or ORDER BY?

If I understood your question correctly, it is because you can take any
crosstab and convert it to a vertical list of values that generate it,
with a maximum number of columns, and easily WHERE-able.

For example, a accumulative percent-based grade report:
+-------------+----+----+----+----+---------+
| Assignment  | P1 | P2 | P3 | P4 | Average |
+-------------+----+----+----+----+---------+
| Assignment1 | 95 | 90 | 99 |    |  94.67  |
| Assignment2 | 90 | 90 | 91 |    |  90.33  |
| Assignment3 | 85 | 80 | 95 |    |  86.67  |
+-------------+----+----+----+----+---------+

The data source is:
+-------------+--------+-------+
| Assignment  | Period | Value |
+-------------+--------+-------+
| Assignment1 | P1     | 95    |
| Assignment1 | P2     | 90    |
| Assignment1 | P3     | 99    |
| Assignment2 | P1     | 90    |
| Assignment2 | P2     | 90    |
| Assignment2 | P3     | 91    |
| Assignment3 | P1     | 85    |
| Assignment3 | P2     | 80    |
| Assignment3 | P3     | 95    |
+-------------+--------+-------+

... even if P4 doesn't exist yet. You can have a crosstab with a dynamic
number of columns where P4 doesn't appear as a column, or a pre-defined
number of columns.

And even if each value from the crosstab is an aggregate function like
count() or max() the data source first is prepared as a select/where and
maybe a group by and then transformed into a crosstab.

In any case, the results are the same as GROUPing BY from the data
source.
+-------------+---------+
| Assignment  | Average |
+-------------+---------+
| Assignment1 |  94.67  |
| Assignment2 |  90.33  |
| Assignment3 |  86.67  |
+-------------+---------+

A crosstab is not but a presentational transform of the data set. Any
information you would eventually need can be taken from the original
data source, one way or another. That's why dynamic-column crosstab are
not a problem, and the DBMS should not worry about providing the
information about the columns, maybe by simply not allowing the
dynamic-column ones in subqueries.




In response to

Responses

pgsql-general by date

Next:From: Andreas WenkDate: 2009-01-31 23:16:58
Subject: ALTER TABLE with TYPE serial does not work
Previous:From: Gregory StarkDate: 2009-01-31 20:33:54
Subject: Re: Indices types, what to use. Btree, Hash, Gin or Gist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group