25. März 2013

SQL*Plus für alle ... heute: Diagramme mit SQL*Plus

SQL*Plus everywhere ... today: Charts with SQL*Plus
Heute gibt es ein recht kurzes - und um ehrlich zu sein, auch eher nutzloses Posting - aber ich bin jüngst in der Dokumentation (SQL Developers Guide - Funktion RPAD auf ein nettes "Anwendungsbeispiel" zur RPAD-Funktion gestoßen: Barcharts in SQL*Plus.
SQL> select ename, sal, rpad('|',ceil(sal/500)+1, '*') as sal_chart from emp;

ENAME         SAL SAL_CHART
---------- ------ --------------------
SMITH         800 |**
ALLEN        1600 |****
WARD         1250 |***
JONES        2975 |******
MARTIN       1250 |***
BLAKE        2850 |******
CLARK        2450 |*****
SCOTT        3000 |******
KING         5000 |**********
TURNER       1500 |***
ADAMS        1100 |***
JAMES         950 |**
FORD         3000 |******
MILLER       1300 |***
Auch "Linecharts" sind möglich ;-)
SQL> select ename, sal, rpad('|',ceil(sal/500), ' ')||'*' as sal_chart 
  2  from emp 
  2  order by sal

ENAME         SAL SAL_CHART
---------- ------ --------------------
SMITH         800 | *
JAMES         950 | *
ADAMS        1100 |  *
WARD         1250 |  *
MARTIN       1250 |  *
MILLER       1300 |  *
TURNER       1500 |  *
ALLEN        1600 |   *
CLARK        2450 |    *
BLAKE        2850 |     *
JONES        2975 |     *
SCOTT        3000 |     *
FORD         3000 |     *
KING         5000 |         *
Gerne genommen sind auch Stacked Bar Charts ...
SQL> select
  2    ename,
  3    sal,
  4    comm,
  5    rpad(
  6      rpad('|',ceil(sal/300)+1,'*'),
  7      ceil((sal+nvl(comm,0))/300)+1,
  8      '#'
  9    ) as pay_chart
 10  from emp
 11* order by sal

ENAME             SAL       COMM PAY_CHART
---------- ---------- ---------- --------------------
SMITH             800            |***
JAMES             950            |****
ADAMS            1100            |****
WARD             1250        500 |*****#
MARTIN           1250       1400 |*****####
MILLER           1300            |*****
TURNER           1500          0 |*****
ALLEN            1600        300 |******#
CLARK            2450            |*********
BLAKE            2850            |**********
JONES            2975            |**********
SCOTT            3000            |**********
FORD             3000            |**********
KING             5000            |*****************
Auch für die Schule kann SQL*Plus nun wertvolle Dienste leisten ...
SQL> select level x, power(level,2) y, rpad('|', round(power(level,2)/10)+1,' ')||'*' yc 
  2  from dual 
  3  connect by level < 21;

         X      Y YC
---------- ------ ------------------------------------------------------------
         1      1 |*
         2      4 |*
         3      9 | *
         4     16 |  *
         5     25 |   *
         6     36 |    *
         7     49 |     *
         8     64 |      *
         9     81 |        *
        10    100 |          *
        11    121 |            *
        12    144 |              *
        13    169 |                 *
        14    196 |                    *
        15    225 |                       *
        16    256 |                          *
        17    289 |                             *
        18    324 |                                *
        19    361 |                                    *
        20    400 |                                        *
So ... nun ist es aber genug. Muss wieder was richtiges arbeiten.
Today's posting is short and - to be honest - rather useless. But recently, I encountered a nice usage example of the RPAD function within the Oracle Documentation - SQL Developers Guide: Diagrams in SQL*Plus.
SQL> select ename, sal, rpad('|',ceil(sal/500)+1, '*') as sal_chart from emp;

ENAME         SAL SAL_CHART
---------- ------ --------------------
SMITH         800 |**
ALLEN        1600 |****
WARD         1250 |***
JONES        2975 |******
MARTIN       1250 |***
BLAKE        2850 |******
CLARK        2450 |*****
SCOTT        3000 |******
KING         5000 |**********
TURNER       1500 |***
ADAMS        1100 |***
JAMES         950 |**
FORD         3000 |******
MILLER       1300 |***
We can change this to a line chart ...
SQL> select ename, sal, rpad('|',ceil(sal/500), ' ')||'*' as sal_chart 
  2  from emp 
  2  order by sal

ENAME         SAL SAL_CHART
---------- ------ --------------------
SMITH         800 | *
JAMES         950 | *
ADAMS        1100 |  *
WARD         1250 |  *
MARTIN       1250 |  *
MILLER       1300 |  *
TURNER       1500 |  *
ALLEN        1600 |   *
CLARK        2450 |    *
BLAKE        2850 |     *
JONES        2975 |     *
SCOTT        3000 |     *
FORD         3000 |     *
KING         5000 |         *
Stacked Bar Charts are also very nice ...
SQL> select
  2    ename,
  3    sal,
  4    comm,
  5    rpad(
  6      rpad('|',ceil(sal/300)+1,'*'),
  7      ceil((sal+nvl(comm,0))/300)+1,
  8      '#'
  9    ) as pay_chart
 10  from emp
 11* order by sal

ENAME             SAL       COMM PAY_CHART
---------- ---------- ---------- --------------------
SMITH             800            |***
JAMES             950            |****
ADAMS            1100            |****
WARD             1250        500 |*****#
MARTIN           1250       1400 |*****####
MILLER           1300            |*****
TURNER           1500          0 |*****
ALLEN            1600        300 |******#
CLARK            2450            |*********
BLAKE            2850            |**********
JONES            2975            |**********
SCOTT            3000            |**********
FORD             3000            |**********
KING             5000            |*****************
And, SQL*Plus gets usable for school kids ...
SQL> select level x, power(level,2) y, rpad('|', round(power(level,2)/10)+1,' ')||'*' yc 
  2  from dual 
  3  connect by level < 21;

         X      Y YC
---------- ------ ------------------------------------------------------------
         1      1 |*
         2      4 |*
         3      9 | *
         4     16 |  *
         5     25 |   *
         6     36 |    *
         7     49 |     *
         8     64 |      *
         9     81 |        *
        10    100 |          *
        11    121 |            *
        12    144 |              *
        13    169 |                 *
        14    196 |                    *
        15    225 |                       *
        16    256 |                          *
        17    289 |                             *
        18    324 |                                *
        19    361 |                                    *
        20    400 |                                        *
OK ... enough for the moment - have to do some real work again ;-)

Keine Kommentare:

Beliebte Postings