[patch] Proposal for \rotate in psql

2015-08-29 0:48 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:

Hi,

This is a reboot of my previous proposal for pivoting results in psql,
with a new patch that generalizes the idea further through a command
now named \rotate, and some examples.

So the concept is: having an existing query in the query buffer,
the user can specify two column numbers C1 and C2 (by default the 1st
and 2nd) as an argument to a \rotate command.

The query results are then displayed in a 2D grid such that each tuple
(vx, vy, va, vb,...) is shown as |va vb...| in a cell at coordinates
(vx,vy).
The values vx,xy come from columns C1,C2 respectively and are
represented in the output as an horizontal and a vertical header.

A cell may hold several columns from several rows, growing horizontally and
vertically (\n inside the cell) if necessary to show all results.

The examples below should be read with a monospaced font as in psql,
otherwise they will look pretty bad.

1. Example with only 2 columns, querying login/group membership from the
catalog.
Query:

SELECT r.rolname as username,r1.rolname as groupname
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1

Sample results:
username | groupname
------------+-----------
daniel | mailusers
drupal |
dv | admin
dv | common
extc | readonly
extu |
foobar |
joel |
mailreader | readonly
manitou | mailusers
manitou | admin
postgres |
u1 | common
u2 | mailusers
zaz | mailusers

Applying \rotate gives:
Rotated query results
username | admin | common | mailusers | readonly
------------+-------+--------+-----------+----------
daniel | | | X |
drupal | | | |
dv | X | X | |
extc | | | | X
extu | | | |
foobar | | | |
joel | | | |
mailreader | | | | X
manitou | X | | X |
postgres | | | |
u1 | | X | |
u2 | | | X |
zaz | | | X |

The 'X' inside cells is automatically added as there are only
2 columns. If there was a 3rd column, the content of that column would
be displayed instead (as in the next example).

What's good in that \rotate display compared to the classic output is that
it's more apparent, visually speaking, that such user belongs or not to
such
group or another.

2. Example with a unicode checkmark added as 3rd column, and
unicode linestyle and borders (to be seen with a mono-spaced font):

SELECT r.rolname as username,r1.rolname as groupname, chr(10003)
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1

Rotated query results
┌────────────┬───────┬───�”
�────┬───────────┬────────â
��─┐
│ username │ admin │ common │ mailusers │ readonly │
├────────────┼───────┼───�”
�────┼───────────┼────────â
��─┤
│ daniel │ │ │ ✓ │ │
│ drupal │ │ │ │ │
│ dv │ ✓ │ ✓ │ │ │
│ extc │ │ │ │ ✓ │
│ extu │ │ │ │ │
│ foobar │ │ │ │ │
│ joel │ │ │ │ │
│ mailreader │ │ │ │ ✓ │
│ manitou │ ✓ │ │ ✓ │ │
│ postgres │ │ │ │ │
│ u1 │ │ ✓ │ │ │
│ u2 │ │ │ ✓ │ │
│ zaz │ │ │ ✓ │ │
└────────────┴───────┴───�”
�────┴───────────┴────────â
��─┘

What I like in that representation is that it looks good enough
to be pasted directly into a document in a word processor.

3. It can be rotated easily in the other direction, with:
\rotate 2 1

(Cut horizontally to fit in a mail, the actual output is 116 chars wide).

Rotated query results
┌───────────┬────────┬───�”
�────┬────┬──────┬──────┬─â
��──────┬──────┬────
│ username │ daniel │ drupal │ dv │ extc │ extu │ foobar │
joel │ mai...
├───────────┼────────┼───�”
�────┼────┼──────┼──────┼─â
��──────┼──────┼────
│ mailusers │ ✓ │ │ │ │ │ │
│
│ admin │ │ │ ✓ │ │ │ │
│
│ common │ │ │ ✓ │ │ │ │
│
│ readonly │ │ │ │ ✓ │ │ │
│ ✓
└───────────┴────────┴───�”
�────┴────┴──────┴──────┴─â
��──────┴──────┴────

4. Example with 3 columns and a count as the value to visualize along
two axis: date and category.
I'm using the number of mails posted per month in a few PG mailing lists,
broken down by list (which are tags in my schema).

Query:
SELECT date_trunc('month', msg_date)::date as month,
t.name,
count(*) as cnt
FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
WHERE t.tag_id in (7,8,12,34,79)
AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
GROUP BY date_trunc('month', msg_date)::date, t.name
ORDER BY 1,2;

Results:
month | name | cnt
------------+-------------+------
2014-05-01 | announce | 19
2014-05-01 | general | 550
2014-05-01 | hackers | 1914
2014-05-01 | interfaces | 4
2014-05-01 | performance | 122
2014-06-01 | announce | 10
2014-06-01 | general | 499
2014-06-01 | hackers | 2008
2014-06-01 | interfaces | 10
2014-06-01 | performance | 137
2014-07-01 | announce | 12
2014-07-01 | general | 703
2014-07-01 | hackers | 1504
2014-07-01 | interfaces | 6
2014-07-01 | performance | 142
2014-08-01 | announce | 9
2014-08-01 | general | 616
2014-08-01 | hackers | 1864
2014-08-01 | interfaces | 11
2014-08-01 | performance | 116
2014-09-01 | announce | 10
2014-09-01 | general | 645
2014-09-01 | hackers | 2364
2014-09-01 | interfaces | 3
2014-09-01 | performance | 105
2014-10-01 | announce | 13
2014-10-01 | general | 476
2014-10-01 | hackers | 2325
2014-10-01 | interfaces | 10
2014-10-01 | performance | 137
2014-11-01 | announce | 10
2014-11-01 | general | 457
2014-11-01 | hackers | 1810
2014-11-01 | performance | 109
2014-12-01 | announce | 11
2014-12-01 | general | 623
2014-12-01 | hackers | 2043
2014-12-01 | interfaces | 1
2014-12-01 | performance | 71
(39 rows)

\rotate gives:
Rotated query results
month | announce | general | hackers | interfaces | performance
------------+----------+---------+---------+------------+-------------
2014-05-01 | 19 | 550 | 1914 | 4 | 122
2014-06-01 | 10 | 499 | 2008 | 10 | 137
2014-07-01 | 12 | 703 | 1504 | 6 | 142
2014-08-01 | 9 | 616 | 1864 | 11 | 116
2014-09-01 | 10 | 645 | 2364 | 3 | 105
2014-10-01 | 13 | 476 | 2325 | 10 | 137
2014-11-01 | 10 | 457 | 1810 | | 109
2014-12-01 | 11 | 623 | 2043 | 1 | 71

Advantage: we can figure out the trends, and notice empty slots,
much quicker than with the previous output. It seems smaller
but there is the same amount of information.

5. Example with an additional column showing if the count grows up or down
compared to the previous month. This shows how the contents get stacked
inside cells when they come from several columns and rows.

Query:

SELECT to_char(mon, 'yyyy-mm') as month,
name,
CASE when lag(name,1) over(order by name,mon)=name then
case sign(cnt-(lag(cnt,1) over(order by name,mon)))
when 1 then chr(8593)
when 0 then chr(8597)
when -1 then chr(8595)
else ' ' end
END,
cnt
from (SELECT date_trunc('month', msg_date)::date as mon, t.name,count(*)
as
cnt
FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
WHERE t.tag_id in (7,8,12,34,79)
AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
GROUP BY date_trunc('month', msg_date)::date, t.name) l order by 2,1;

Result:
month | name | case | cnt
---------+-------------+------+------
2014-05 | announce | | 19
2014-06 | announce | ↓ | 10
2014-07 | announce | ↑ | 12
2014-08 | announce | ↓ | 9
2014-09 | announce | ↑ | 10
2014-10 | announce | ↑ | 13
2014-11 | announce | ↓ | 10
2014-12 | announce | ↑ | 11
2014-05 | general | | 550
2014-06 | general | ↓ | 499
2014-07 | general | ↑ | 703
2014-08 | general | ↓ | 616
2014-09 | general | ↑ | 645
2014-10 | general | ↓ | 476
2014-11 | general | ↓ | 457
2014-12 | general | ↑ | 623
2014-05 | hackers | | 1914
2014-06 | hackers | ↑ | 2008
2014-07 | hackers | ↓ | 1504
2014-08 | hackers | ↑ | 1864
2014-09 | hackers | ↑ | 2364
2014-10 | hackers | ↓ | 2325
2014-11 | hackers | ↓ | 1810
2014-12 | hackers | ↑ | 2043
2014-05 | interfaces | | 4
2014-06 | interfaces | ↑ | 10
2014-07 | interfaces | ↓ | 6
2014-08 | interfaces | ↑ | 11
2014-09 | interfaces | ↓ | 3
2014-10 | interfaces | ↑ | 10
2014-12 | interfaces | ↓ | 1
2014-05 | performance | | 122
2014-06 | performance | ↑ | 137
2014-07 | performance | ↑ | 142
2014-08 | performance | ↓ | 116
2014-09 | performance | ↓ | 105
2014-10 | performance | ↑ | 137
2014-11 | performance | ↓ | 109
2014-12 | performance | ↓ | 71
(39 rows)

\rotate:

Rotated query results
month | announce | general | hackers | interfaces | performance
---------+----------+---------+---------+------------+-------------
2014-05 | 19 | 550 | 1914 | 4 | 122
2014-06 | ↓ 10 | ↓ 499 | ↑ 2008 | ↑ 10 | ↑ 137
2014-07 | ↑ 12 | ↑ 703 | ↓ 1504 | ↓ 6 | ↑ 142
2014-08 | ↓ 9 | ↓ 616 | ↑ 1864 | ↑ 11 | ↓ 116
2014-09 | ↑ 10 | ↑ 645 | ↑ 2364 | ↓ 3 | ↓ 105
2014-10 | ↑ 13 | ↓ 476 | ↓ 2325 | ↑ 10 | ↑ 137
2014-11 | ↓ 10 | ↓ 457 | ↓ 1810 | | ↓ 109
2014-12 | ↑ 11 | ↑ 623 | ↑ 2043 | ↓ 1 | ↓ 71
(8 rows)

The output columns 3 and 4 of the same row get projected into the same
cell, laid out horizontally (separated by space).

6. Example with the same query but rotated differently so that
it's split into two columns: the counts that go up from the previous
and those that go down. I'm also cheating a bit by
casting name and cnt to char(N) for a better alignment

SELECT to_char(mon, 'yyyy-mm') as month,
name::char(12),
CASE when lag(name,1) over(order by name,mon)=name then
case sign(cnt-(lag(cnt,1) over(order by name,mon)))
when 1 then chr(8593)
when 0 then chr(8597)
when -1 then chr(8595)
else ' ' end
END,
cnt::char(8)
from (SELECT date_trunc('month', msg_date)::date as mon, t.name,count(*)
as
cnt
FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
WHERE t.tag_id in (7,8,12,34,79)
AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
GROUP BY date_trunc('month', msg_date)::date, t.name) l order by 2,1;

\rotate 1 3

+---------+-----------------------+-----------------------+
|  month  |           ↑     |           ↓             |
+---------+-----------------------+-----------------------+
| 2014-05 |                       |                       |
| 2014-06 | hackers      2008    +| announce     10      +|
|         | interfaces   10      +| general      499      |
|         | performance  137      |                       |
| 2014-07 | announce     12      +| hackers      1504    +|
|         | general      703     +| interfaces   6        |
|         | performance  142      |                       |
| 2014-08 | hackers      1864    +| announce     9       +|
|         | interfaces   11       | general      616     +|
|         |                       | performance  116      |
| 2014-09 | announce     10      +| interfaces   3       +|
|         | general      645     +| performance  105      |
|         | hackers      2364     |                       |
| 2014-10 | announce     13      +| general      476     +|
|         | interfaces   10      +| hackers      2325     |
|         | performance  137      |                       |
| 2014-11 |                       | announce     10      +|
|         |                       | general      457     +|
|         |                       | hackers      1810    +|
|         |                       | performance  109      |
| 2014-12 | announce     11      +| interfaces   1       +|
|         | general      623     +| performance  71       |
|         | hackers      2043     |                       |
+---------+-----------------------+-----------------------+

As there are several rows that match the vertical/horizontal filter,
(for example 3 results for 2014-06 as row and "arrow up" as column),
they are stacked vertically inside the cell, in addition to
"name" and "cnt" being shown side by side horizontally.

Note that no number show up for 2014-05; this is because they're not
associated with arrow up or down; empty as a column is discarded.
Maybe it shouldn't. In this case, the numbers for 2014-05 would be in a
column with an empty name.

Conclusion, the point of \rotate:

When analyzing query results, these rotated representations may be
useful or not depending on the cases, but the point is that they require
no effort to be obtained through \rotate X Y
It's so easy to play with various combinations to see if the result
makes sense, and if it reveals something about the data.
(it still reexecutes the query each time, tough).

We can get more or less the same results with crosstab/pivot, as it's the
same basic concept, but with much more effort spent on getting the SQL
right,
plus the fact that columns not known in advance cannot be returned pivoted
in a single pass in SQL, a severe complication that the client-side doesn't
have.

simple and user friendy

nice

+1

Pavel

On Sat, Aug 29, 2015 at 12:48:23AM +0200, Daniel Verite wrote:

Hi,

This is a reboot of my previous proposal for pivoting results in psql,
with a new patch that generalizes the idea further through a command
now named \rotate, and some examples.

Neat!

Thanks for putting this together :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I wrote:

What I like in that representation is that it looks good enough
to be pasted directly into a document in a word processor.

And ironically, the nice unicode borders came out all garbled
in the mail, thanks to a glitch in my setup that mis-reformatted them
before sending.

Sorry about that, the results with unicode linestyle were supposed to be
as follows:

Example 2:

Rotated query results
┌────────────┬───────┬────────┬───────────┬──────────┐
│ username │ admin │ common │ mailusers │ readonly │
├────────────┼───────┼────────┼───────────┼──────────┤
│ daniel │ │ │ ✓ │ │
│ drupal │ │ │ │ │
│ dv │ ✓ │ ✓ │ │ │
│ extc │ │ │ │ ✓ │
│ extu │ │ │ │ │
│ foobar │ │ │ │ │
│ joel │ │ │ │ │
│ mailreader │ │ │ │ ✓ │
│ manitou │ ✓ │ │ ✓ │ │
│ postgres │ │ │ │ │
│ u1 │ │ ✓ │ │ │
│ u2 │ │ │ ✓ │ │
│ zaz │ │ │ ✓ │ │
└────────────┴───────┴────────┴───────────┴──────────┘

Example 3, rotated in the other direction

(Cut horizontally to fit in a mail, the actual output is 116 chars wide).

Rotated query results
┌───────────┬────────┬────────┬────┬──────┬──────┬────────┬──────┬────
│ username │ daniel │ drupal │ dv │ extc │ extu │ foobar │ joel │ mai...
├───────────┼────────┼────────┼────┼──────┼──────┼────────┼──────┼────
│ mailusers │ ✓ │ │ │ │ │ │ │
│ admin │ │ │ ✓ │ │ │ │ │
│ common │ │ │ ✓ │ │ │ │ │
│ readonly │ │ │ │ ✓ │ │ │ │ ✓
└───────────┴────────┴────────┴────┴──────┴──────┴────────┴──────┴────

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-08-29 5:57 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:

2015-08-29 0:48 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:

Hi,

This is a reboot of my previous proposal for pivoting results in psql,
with a new patch that generalizes the idea further through a command
now named \rotate, and some examples.

So the concept is: having an existing query in the query buffer,
the user can specify two column numbers C1 and C2 (by default the 1st
and 2nd) as an argument to a \rotate command.

The query results are then displayed in a 2D grid such that each tuple
(vx, vy, va, vb,...) is shown as |va vb...| in a cell at coordinates
(vx,vy).
The values vx,xy come from columns C1,C2 respectively and are
represented in the output as an horizontal and a vertical header.

A cell may hold several columns from several rows, growing horizontally
and
vertically (\n inside the cell) if necessary to show all results.

The examples below should be read with a monospaced font as in psql,
otherwise they will look pretty bad.

1. Example with only 2 columns, querying login/group membership from the
catalog.
Query:

SELECT r.rolname as username,r1.rolname as groupname
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1

Sample results:
username | groupname
------------+-----------
daniel | mailusers
drupal |
dv | admin
dv | common
extc | readonly
extu |
foobar |
joel |
mailreader | readonly
manitou | mailusers
manitou | admin
postgres |
u1 | common
u2 | mailusers
zaz | mailusers

Applying \rotate gives:
Rotated query results
username | admin | common | mailusers | readonly
------------+-------+--------+-----------+----------
daniel | | | X |
drupal | | | |
dv | X | X | |
extc | | | | X
extu | | | |
foobar | | | |
joel | | | |
mailreader | | | | X
manitou | X | | X |
postgres | | | |
u1 | | X | |
u2 | | | X |
zaz | | | X |

The 'X' inside cells is automatically added as there are only
2 columns. If there was a 3rd column, the content of that column would
be displayed instead (as in the next example).

What's good in that \rotate display compared to the classic output is that
it's more apparent, visually speaking, that such user belongs or not to
such
group or another.

2. Example with a unicode checkmark added as 3rd column, and
unicode linestyle and borders (to be seen with a mono-spaced font):

SELECT r.rolname as username,r1.rolname as groupname, chr(10003)
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1

Rotated query results
┌────────────┬───────┬───�”
�────┬───────────┬────────â
��─┐
│ username │ admin │ common │ mailusers │ readonly │
├────────────┼───────┼───�”
�────┼───────────┼────────â
��─┤
│ daniel │ │ │ ✓ │ │
│ drupal │ │ │ │ │
│ dv │ ✓ │ ✓ │ │ │
│ extc │ │ │ │ ✓ │
│ extu │ │ │ │ │
│ foobar │ │ │ │ │
│ joel │ │ │ │ │
│ mailreader │ │ │ │ ✓ │
│ manitou │ ✓ │ │ ✓ │ │
│ postgres │ │ │ │ │
│ u1 │ │ ✓ │ │ │
│ u2 │ │ │ ✓ │ │
│ zaz │ │ │ ✓ │ │
└────────────┴───────┴───�”
�────┴───────────┴────────â
��─┘

What I like in that representation is that it looks good enough
to be pasted directly into a document in a word processor.

3. It can be rotated easily in the other direction, with:
\rotate 2 1

(Cut horizontally to fit in a mail, the actual output is 116 chars wide).

Rotated query results
┌───────────┬────────┬───�”
�────┬────┬──────┬──────┬─â
��──────┬──────┬────
│ username │ daniel │ drupal │ dv │ extc │ extu │ foobar │
joel │ mai...
├───────────┼────────┼───�”
�────┼────┼──────┼──────┼─â
��──────┼──────┼────
│ mailusers │ ✓ │ │ │ │ │ │
│
│ admin │ │ │ ✓ │ │ │ │
│
│ common │ │ │ ✓ │ │ │ │
│
│ readonly │ │ │ │ ✓ │ │ │
│ ✓
└───────────┴────────┴───�”
�────┴────┴──────┴──────┴─â
��──────┴──────┴────

4. Example with 3 columns and a count as the value to visualize along
two axis: date and category.
I'm using the number of mails posted per month in a few PG mailing lists,
broken down by list (which are tags in my schema).

Query:
SELECT date_trunc('month', msg_date)::date as month,
t.name,
count(*) as cnt
FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
WHERE t.tag_id in (7,8,12,34,79)
AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
GROUP BY date_trunc('month', msg_date)::date, t.name
ORDER BY 1,2;

Results:
month | name | cnt
------------+-------------+------
2014-05-01 | announce | 19
2014-05-01 | general | 550
2014-05-01 | hackers | 1914
2014-05-01 | interfaces | 4
2014-05-01 | performance | 122
2014-06-01 | announce | 10
2014-06-01 | general | 499
2014-06-01 | hackers | 2008
2014-06-01 | interfaces | 10
2014-06-01 | performance | 137
2014-07-01 | announce | 12
2014-07-01 | general | 703
2014-07-01 | hackers | 1504
2014-07-01 | interfaces | 6
2014-07-01 | performance | 142
2014-08-01 | announce | 9
2014-08-01 | general | 616
2014-08-01 | hackers | 1864
2014-08-01 | interfaces | 11
2014-08-01 | performance | 116
2014-09-01 | announce | 10
2014-09-01 | general | 645
2014-09-01 | hackers | 2364
2014-09-01 | interfaces | 3
2014-09-01 | performance | 105
2014-10-01 | announce | 13
2014-10-01 | general | 476
2014-10-01 | hackers | 2325
2014-10-01 | interfaces | 10
2014-10-01 | performance | 137
2014-11-01 | announce | 10
2014-11-01 | general | 457
2014-11-01 | hackers | 1810
2014-11-01 | performance | 109
2014-12-01 | announce | 11
2014-12-01 | general | 623
2014-12-01 | hackers | 2043
2014-12-01 | interfaces | 1
2014-12-01 | performance | 71
(39 rows)

\rotate gives:
Rotated query results
month | announce | general | hackers | interfaces | performance
------------+----------+---------+---------+------------+-------------
2014-05-01 | 19 | 550 | 1914 | 4 | 122
2014-06-01 | 10 | 499 | 2008 | 10 | 137
2014-07-01 | 12 | 703 | 1504 | 6 | 142
2014-08-01 | 9 | 616 | 1864 | 11 | 116
2014-09-01 | 10 | 645 | 2364 | 3 | 105
2014-10-01 | 13 | 476 | 2325 | 10 | 137
2014-11-01 | 10 | 457 | 1810 | | 109
2014-12-01 | 11 | 623 | 2043 | 1 | 71

Advantage: we can figure out the trends, and notice empty slots,
much quicker than with the previous output. It seems smaller
but there is the same amount of information.

5. Example with an additional column showing if the count grows up or down
compared to the previous month. This shows how the contents get stacked
inside cells when they come from several columns and rows.

Query:

SELECT to_char(mon, 'yyyy-mm') as month,
name,
CASE when lag(name,1) over(order by name,mon)=name then
case sign(cnt-(lag(cnt,1) over(order by name,mon)))
when 1 then chr(8593)
when 0 then chr(8597)
when -1 then chr(8595)
else ' ' end
END,
cnt
from (SELECT date_trunc('month', msg_date)::date as mon, t.name,count(*)
as
cnt
FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
WHERE t.tag_id in (7,8,12,34,79)
AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
GROUP BY date_trunc('month', msg_date)::date, t.name) l order by 2,1;

Result:
month | name | case | cnt
---------+-------------+------+------
2014-05 | announce | | 19
2014-06 | announce | ↓ | 10
2014-07 | announce | ↑ | 12
2014-08 | announce | ↓ | 9
2014-09 | announce | ↑ | 10
2014-10 | announce | ↑ | 13
2014-11 | announce | ↓ | 10
2014-12 | announce | ↑ | 11
2014-05 | general | | 550
2014-06 | general | ↓ | 499
2014-07 | general | ↑ | 703
2014-08 | general | ↓ | 616
2014-09 | general | ↑ | 645
2014-10 | general | ↓ | 476
2014-11 | general | ↓ | 457
2014-12 | general | ↑ | 623
2014-05 | hackers | | 1914
2014-06 | hackers | ↑ | 2008
2014-07 | hackers | ↓ | 1504
2014-08 | hackers | ↑ | 1864
2014-09 | hackers | ↑ | 2364
2014-10 | hackers | ↓ | 2325
2014-11 | hackers | ↓ | 1810
2014-12 | hackers | ↑ | 2043
2014-05 | interfaces | | 4
2014-06 | interfaces | ↑ | 10
2014-07 | interfaces | ↓ | 6
2014-08 | interfaces | ↑ | 11
2014-09 | interfaces | ↓ | 3
2014-10 | interfaces | ↑ | 10
2014-12 | interfaces | ↓ | 1
2014-05 | performance | | 122
2014-06 | performance | ↑ | 137
2014-07 | performance | ↑ | 142
2014-08 | performance | ↓ | 116
2014-09 | performance | ↓ | 105
2014-10 | performance | ↑ | 137
2014-11 | performance | ↓ | 109
2014-12 | performance | ↓ | 71
(39 rows)

\rotate:

Rotated query results
month | announce | general | hackers | interfaces | performance
---------+----------+---------+---------+------------+-------------
2014-05 | 19 | 550 | 1914 | 4 | 122
2014-06 | ↓ 10 | ↓ 499 | ↑ 2008 | ↑ 10 | ↑ 137
2014-07 | ↑ 12 | ↑ 703 | ↓ 1504 | ↓ 6 | ↑ 142
2014-08 | ↓ 9 | ↓ 616 | ↑ 1864 | ↑ 11 | ↓ 116
2014-09 | ↑ 10 | ↑ 645 | ↑ 2364 | ↓ 3 | ↓ 105
2014-10 | ↑ 13 | ↓ 476 | ↓ 2325 | ↑ 10 | ↑ 137
2014-11 | ↓ 10 | ↓ 457 | ↓ 1810 | | ↓ 109
2014-12 | ↑ 11 | ↑ 623 | ↑ 2043 | ↓ 1 | ↓ 71
(8 rows)

The output columns 3 and 4 of the same row get projected into the same
cell, laid out horizontally (separated by space).

6. Example with the same query but rotated differently so that
it's split into two columns: the counts that go up from the previous
and those that go down. I'm also cheating a bit by
casting name and cnt to char(N) for a better alignment

SELECT to_char(mon, 'yyyy-mm') as month,
name::char(12),
CASE when lag(name,1) over(order by name,mon)=name then
case sign(cnt-(lag(cnt,1) over(order by name,mon)))
when 1 then chr(8593)
when 0 then chr(8597)
when -1 then chr(8595)
else ' ' end
END,
cnt::char(8)
from (SELECT date_trunc('month', msg_date)::date as mon, t.name,count(*)
as
cnt
FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
WHERE t.tag_id in (7,8,12,34,79)
AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
GROUP BY date_trunc('month', msg_date)::date, t.name) l order by 2,1;

\rotate 1 3

+---------+-----------------------+-----------------------+
|  month  |           ↑     |           ↓             |
+---------+-----------------------+-----------------------+
| 2014-05 |                       |                       |
| 2014-06 | hackers      2008    +| announce     10      +|
|         | interfaces   10      +| general      499      |
|         | performance  137      |                       |
| 2014-07 | announce     12      +| hackers      1504    +|
|         | general      703     +| interfaces   6        |
|         | performance  142      |                       |
| 2014-08 | hackers      1864    +| announce     9       +|
|         | interfaces   11       | general      616     +|
|         |                       | performance  116      |
| 2014-09 | announce     10      +| interfaces   3       +|
|         | general      645     +| performance  105      |
|         | hackers      2364     |                       |
| 2014-10 | announce     13      +| general      476     +|
|         | interfaces   10      +| hackers      2325     |
|         | performance  137      |                       |
| 2014-11 |                       | announce     10      +|
|         |                       | general      457     +|
|         |                       | hackers      1810    +|
|         |                       | performance  109      |
| 2014-12 | announce     11      +| interfaces   1       +|
|         | general      623     +| performance  71       |
|         | hackers      2043     |                       |
+---------+-----------------------+-----------------------+

As there are several rows that match the vertical/horizontal filter,
(for example 3 results for 2014-06 as row and "arrow up" as column),
they are stacked vertically inside the cell, in addition to
"name" and "cnt" being shown side by side horizontally.

Note that no number show up for 2014-05; this is because they're not
associated with arrow up or down; empty as a column is discarded.
Maybe it shouldn't. In this case, the numbers for 2014-05 would be in a
column with an empty name.

Conclusion, the point of \rotate:

When analyzing query results, these rotated representations may be
useful or not depending on the cases, but the point is that they require
no effort to be obtained through \rotate X Y
It's so easy to play with various combinations to see if the result
makes sense, and if it reveals something about the data.
(it still reexecutes the query each time, tough).

We can get more or less the same results with crosstab/pivot, as it's the
same basic concept, but with much more effort spent on getting the SQL
right,
plus the fact that columns not known in advance cannot be returned pivoted
in a single pass in SQL, a severe complication that the client-side
doesn't
have.

simple and user friendy

nice

+1

Pavel

the name "rotate" is not correct - maybe "\cross" ?

Pavel Stehule wrote:

the name "rotate" is not correct - maybe "\cross" ?

I'm not dead set on \rotate and suggested other names
previously in [1]/messages/by-id/cd521513-1349-4698-b93c-693199962e23@mm, but none of them seems decisively
superior.

The rationale behind rotate is that, it's a synonym of pivot
as a verb, and it's not already used for other things in SQL.

Incidentally I'm discovering by googling that people actually
searched previously for that feature with that name:
http://postgresql.nabble.com/rotate-psql-output-td3046832.html

OTOH "cross" is already used in the database vocabulary for
cross joins. Also I find it used too in "cross-db queries" or the
"cross apply" of other engines.
I think that plays against it for choosing it to designate
something different again.

However, maybe \across may be a better fit, or "cross"
combined with some other word, as in \crossview .
Not sure how that sounds to a native english speaker.

[1]: /messages/by-id/cd521513-1349-4698-b93c-693199962e23@mm
/messages/by-id/cd521513-1349-4698-b93c-693199962e23@mm

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Sep 4, 2015 at 5:08 PM, Daniel Verite <daniel@manitou-mail.org> wrote:

I'm not dead set on \rotate and suggested other names
previously in [1], but none of them seems decisively
superior.

Fwiw I like \rotate. It's pretty clear what it means and it sounds
similar to but not exactly the same as pivot.

--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-09-07 22:14 GMT+02:00 Greg Stark <stark@mit.edu>:

On Fri, Sep 4, 2015 at 5:08 PM, Daniel Verite <daniel@manitou-mail.org>
wrote:

I'm not dead set on \rotate and suggested other names
previously in [1], but none of them seems decisively
superior.

Fwiw I like \rotate. It's pretty clear what it means and it sounds
similar to but not exactly the same as pivot.

rotate ~ sounds like transpose matrix, what is not true in this case.

Pavel

On Mon, Sep 7, 2015 at 4:18 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-09-07 22:14 GMT+02:00 Greg Stark <stark@mit.edu>:

On Fri, Sep 4, 2015 at 5:08 PM, Daniel Verite <daniel@manitou-mail.org>
wrote:

I'm not dead set on \rotate and suggested other names
previously in [1], but none of them seems decisively
superior.

Fwiw I like \rotate. It's pretty clear what it means and it sounds
similar to but not exactly the same as pivot.

rotate ~ sounds like transpose matrix, what is not true in this case.

So? If PostgreSQL had any native matrix processing capabilities this would
maybe warrant a bit of consideration.

​https://github.com/hadley/tidyr

\spread
\unfold
\rotate

Given the role that psql performs I do think \rotate to be the least
problematic choice; I concur that avoiding \pivot is desirable due to SQL's
usage.

David J.

On Mon, Sep 7, 2015 at 5:08 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

Given the role that psql performs I do think \rotate to be the least
problematic choice; I concur that avoiding \pivot is desirable due to SQL's
usage.

I can't agree. Rotating a matrix has a well-defined meaning, and this
does something that is not that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Sep 8, 2015 at 1:38 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Sep 7, 2015 at 5:08 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

Given the role that psql performs I do think \rotate to be the least
problematic choice; I concur that avoiding \pivot is desirable due to

SQL's

usage.

I can't agree. Rotating a matrix has a well-defined meaning, and this
does something that is not that.

​Even though the input data is a table and not a matrix? Do you have an
alternative choice you'd like to defend?

David J.
​

On Tue, Sep 8, 2015 at 2:10 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tue, Sep 8, 2015 at 1:38 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Sep 7, 2015 at 5:08 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

Given the role that psql performs I do think \rotate to be the least
problematic choice; I concur that avoiding \pivot is desirable due to
SQL's
usage.

I can't agree. Rotating a matrix has a well-defined meaning, and this
does something that is not that.

Even though the input data is a table and not a matrix?

Yes, I think rotating a table also has a pretty well-defined meaning.

Do you have an
alternative choice you'd like to defend?

Not particularly. If everybody picks one thing they like and argues
strenuously for it, we'll never get anywhere. I think it's enough to
say that I think this particular choice isn't the best. It's not as
if no other suggestions have been made.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Pavel Stehule wrote:

rotate ~ sounds like transpose matrix, what is not true in this case.

The various definitions that I can see, such as
http://dictionary.reference.com/browse/rotate
make no mention of matrices. It applies to anything that
moves around a pivot or axis.

OTOH, the established term for the matrix operation you're
referring to appears to be "transpose", as you mention.
https://en.wikipedia.org/wiki/Transpose

I notice that according to
http://www.thesaurus.com/browse/transpose
"rotate" is not present in the 25+ synonyms they suggest for
"transpose".

In the above wikipedia article about matrix transposition,
"rotate" is also never used anywhere.

"rotate matrix" does not exist for google ngrams, whereas
"transpose matrix" does.
https://books.google.com/ngrams

Overall I don't see the evidence that "rotate" alone would
suggest transposing a matrix.

Now it appears that there is a concept in linear algebra named
"rotation matrix", defined as:
https://en.wikipedia.org/wiki/Rotation_matrix
that seems quite relevant for 3D software.

But as psql is not a tool for linear algebra or 3D in the first place,
who could realistically be deceived?

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-09-08 22:55 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:

Pavel Stehule wrote:

rotate ~ sounds like transpose matrix, what is not true in this case.

for me the relation rotation is exactly what \x does

On Tue, Sep 8, 2015 at 4:58 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-09-08 22:55 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:

Pavel Stehule wrote:

rotate ~ sounds like transpose matrix, what is not true in this case.

for me the relation rotation is exactly what \x does

\x doesn't exactly rotate it either. \x puts the column headers down
the side instead of across the top, but it doesn't put the rows across
the top instead of down the side. Rather, each row is listed in a
separate chunk. This feature is doing something else again. I've
actually never seen this particular transformation anywhere except for
Microsoft Excel's pivot tables, which I still find confusing.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

\x doesn't exactly rotate it either. \x puts the column headers down
the side instead of across the top, but it doesn't put the rows across
the top instead of down the side. Rather, each row is listed in a
separate chunk.

true, it is rotation per one row. I was wrong.

Hi,

This is the 2nd iteration of this patch, for comments and review.

Changes:

- the arguments can be column names (rather than only numbers).

- the horizontal headers are sorted server-side according to their original
type. DESC order is possible by prefixing the column arg with a minus sign.

- the command is now modelled after \g so it can be used
in place of \g

- the title is no longer set by the command, it was getting in the
way when outputting to data file.

- there's a hard limit on 1600 columns. This is to fail early and clean
on large resultsets that are not amenable to being rotated.

- includes SGML user doc.

As I don't have plans for further improvements, I'll submit this one
to the open commitfest.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

2015-09-08 22:55 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:

Pavel Stehule wrote:

rotate ~ sounds like transpose matrix, what is not true in this case.

The various definitions that I can see, such as
http://dictionary.reference.com/browse/rotate
make no mention of matrices. It applies to anything that
moves around a pivot or axis.

OTOH, the established term for the matrix operation you're
referring to appears to be "transpose", as you mention.
https://en.wikipedia.org/wiki/Transpose

I notice that according to
http://www.thesaurus.com/browse/transpose
"rotate" is not present in the 25+ synonyms they suggest for
"transpose".

In the above wikipedia article about matrix transposition,
"rotate" is also never used anywhere.

"rotate matrix" does not exist for google ngrams, whereas
"transpose matrix" does.
https://books.google.com/ngrams

Overall I don't see the evidence that "rotate" alone would
suggest transposing a matrix.

Now it appears that there is a concept in linear algebra named
"rotation matrix", defined as:
https://en.wikipedia.org/wiki/Rotation_matrix
that seems quite relevant for 3D software.

But as psql is not a tool for linear algebra or 3D in the first place,
who could realistically be deceived?

in the help inside your last patch, you are using "crosstab". Cannto be
crosstab the name for this feature?

Regards

Pavel

Hi

2015-09-16 11:35 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:

Hi,

This is the 2nd iteration of this patch, for comments and review.

my comments:

1. I don't understand why you are use two methods for sorting columns
(qsort, and query with ORDER BY)

2. Data column are not well aligned - numbers are aligned as text

3. When data are multiattribute - then merging together with space
separator is not practical

* important information is lost
* same transformation can be done as expression, so this feature is
useless

Is possible to use one cell per attribute (don't do merge)?

DATA QUERY: SELECT dim1, dim2, sum(x), avg(x) FROM .. GROUP BY dim1, dim2

and result header of rotate can be

DIM1 | dim2_val1/sum | dim2_val1/avg | dim2_val2/sum | dim2_val2/avg | ...

3. When data are multiattribute - then merging together with space

separator is not practical

* important information is lost
* same transformation can be done as expression, so this feature is
useless

Is possible to use one cell per attribute (don't do merge)?

DATA QUERY: SELECT dim1, dim2, sum(x), avg(x) FROM .. GROUP BY dim1, dim2

and result header of rotate can be

DIM1 | dim2_val1/sum | dim2_val1/avg | dim2_val2/sum | dim2_val2/avg |
...

Last point can wait - we don't need to show pivot table with all details
perfectly in first step.

The main issue of this patch is name - "rotate" is really pretty strange
for me. Please, change it :) - crosstab is much better

Regards

Pavel