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

最新推荐文章于 2021-04-04 08:25:02 发布

draiden

最新推荐文章于 2021-04-04 08:25:02 发布

阅读量554

点赞数

分类专栏：技巧文章标签： oracle path sql

技巧专栏收录该内容

17 篇文章 0 订阅

订阅专栏

现在用的库里有一个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.--------------------------------------------------------------------------------

某年月日,突发奇想,打算在sqlplus下面按上面的数据画个直方图出来,首先写了个竖列的(因为好写):

代码:--------------------------------------------------------------------------------
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.

==============================