菜鸟学oracle - 用PL/SQL画直方图

    现在用的库里有一个MV是统计按小时的访问量的,数据如下代码:

SQL> select * from mv_time_stat;



      TIME        CNT

---------- ----------

         0        187

         1         51

         2         34

         3         19

         4         19

         5         20

         6         50

         7        107

         8        682

         9       1342

        10       1854

        11       1292

        12       1416

        13       1180

        14       1217

        15       1573

        16       1785

        17       1469

        18       1892

        19       1907

        20       1602

        21       1540

        22       1013

        23        441



24 rows selected.


代码:


SQL> COL Time FORMAT A7

SQL> SELECT LPAD(DECODE(TIME,TO_CHAR(SYSDATE,'HH24'),'NOW: '||
TO_CHAR(TIME),TO_CHAR(TIME)),7,' ') AS "Time", 

  2  SUBSTR('|'||LPAD('> '||CNT,CEIL(CNT/A.TOTAL*300)+
LENGTH(TO_CHAR(CNT))+1,'-'),1,35) AS "    Count per hour" 

  3  FROM MV_TIME_STAT,(SELECT SUM(CNT) AS TOTAL FROM MV_TIME_STAT) A;

COL Time CLEAR



Time        Count per hour

------- -----------------------------------------------------------------

      0 |--> 187

      1 |> 51

      2 |> 34

      3 |> 19

      4 |> 19

      5 |> 20

      6 |> 50

      7 |-> 107

 NOW: 8 |---------> 682

      9 |-----------------> 1342

     10 |------------------------> 1854

     11 |-----------------> 1292

     12 |------------------> 1416

     13 |---------------> 1180

     14 |----------------> 1217

     15 |--------------------> 1573

     16 |-----------------------> 1785

     17 |-------------------> 1469

     18 |-------------------------> 1892

     19 |-------------------------> 1907

     20 |---------------------> 1602

     21 |--------------------> 1540

     22 |-------------> 1013

     23 |-----> 441



24 rows selected.

代码:


CREATE OR REPLACE PROCEDURE HISTOGRAPH(HEIGHT NUMBER DEFAULT 15) AS 

  MAX_ONE NUMBER;

  STR_LINE VARCHAR(120);

  STR_TEMP VARCHAR(120);

  I NUMBER;

BEGIN

  DBMS_OUTPUT.PUT_LINE(LPAD('^',14,' '));

  SELECT MAX(CNT) INTO MAX_ONE FROM MV_TIME_STAT;

  FOR I IN 1 .. HEIGHT+1 LOOP

    STR_LINE:=LPAD(TO_CHAR(MAX_ONE-(I-1)*MAX_ONE/HEIGHT,'99999')||'-'||
TO_CHAR(MAX_ONE-(I-2)*MAX_ONE/HEIGHT,'99999')||'|',14,'0');

    SELECT MAX(SYS_CONNECT_BY_PATH(

      DECODE(SIGN(CNT-MAX_ONE+(I-1)*MAX_ONE/HEIGHT),-1,

        DECODE(SIGN(CNT-MAX_ONE+(I-2)*MAX_ONE/HEIGHT),-1,'^','*'),

      '|'),

    '  '))  INTO STR_TEMP

    FROM MV_TIME_STAT

    START WITH TIME=0 

    CONNECT BY PRIOR TIME=TIME-1;

    STR_LINE:=STR_LINE||STR_TEMP;

    DBMS_OUTPUT.PUT_LINE(REPLACE (STR_LINE,'^',' '));

  END LOOP;

  SELECT MAX(SYS_CONNECT_BY_PATH(TO_CHAR(TIME,'00'),'^'))  INTO STR_TEMP

  FROM MV_TIME_STAT

  START WITH TIME=0 

  CONNECT BY PRIOR TIME=TIME-1;

  DBMS_OUTPUT.PUT_LINE(LPAD('0',14,' ')||LPAD('>',LENGTH(REPLACE(STR_TEMP,' '))+3,'-'));

  DBMS_OUTPUT.PUT_LINE(REPLACE(LPAD('^',14,' ')||REPLACE(STR_TEMP,' '),'^',' '));

END;

/    



Procedure created.



SQL> exec histograph;

             ^

  1907-  2034|                                                           |

  1780-  1907|                                |                 |     |  |

  1653-  1780|                                |                 |     |  |

  1526-  1653|                                |              |  |     |  |  |  |

  1398-  1526|                                |     |        |  |  |  |  |  |  |

  1271-  1398|                             |  |  |  |        |  |  |  |  |  |  |

  1144-  1271|                             |  |  |  |  |  |  |  |  |  |  |  |  |

  1017-  1144|                             |  |  |  |  |  |  |  |  |  |  |  |  |

   890-  1017|                             |  |  |  |  |  |  |  |  |  |  |  |  |  |

   763-   890|                             |  |  |  |  |  |  |  |  |  |  |  |  |  |

   636-   763|                          |  |  |  |  |  |  |  |  |  |  |  |  |  |  |

   509-   636|                          |  |  |  |  |  |  |  |  |  |  |  |  |  |  |

   381-   509|                          |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |

   254-   381|                          |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |

   127-   254|  |                       |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |

     0-   127|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |

             0-------------------------------------------------------------------------->

               00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23



PL/SQL procedure successfully completed.

查看本文来源