在sqlplus畫直方圖

来源：互联网发布：老板电器知乎编辑：程序博客网时间：2024/06/04 23:24

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