[patch] Proposal for \rotate in psql - Mailing list pgsql-hackers

 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.

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