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

现在用的库里有一个MV是统计按小时的访问量的,数据如下代码: r3 [dCmQy  
j[ VmM-n  
SQL> select * from mv_time_stat; F!TVGxE?  
=G\a;9#  
WBs>Ev  
+d!S,3S5Z  
    TIME     CNT j'tK~=-q6  
B0IPiZcy  
---------- ---------- .f>K}i  
<;*V|)M  
      0     187 zZN=1=@'l  
mnghK%/]  
      1       51 _O"3~*  
dusmPy(gQ  
      2       34 :I :okcA  
18}?0r^  
      3       19 ?%r mo-oc+  
>LwO9* +]  
      4       19 'H>/|Lhy  
"8hw-./@2  
      5       20 ?ci'Nl >i  
Kgd&Opjc  
      6       50
_ p:lIQ  
](^M/In  
      7     107 UfSaA(V'  
iqmw:aq`LT  
      8     682 aDEcW  
lMP~dm}E  
      9     1342 l-FVB6  
\gE{]rw  
    10     1854 no7oed}  
~ilL_SXl'  
    11     1292 J6q@ B#1L  
nLj BhL  
    12     1416 6nd"wA  
02z(W[PL  
    13     1180 A
Y>y349!  
t!4X<Mo  
    14     1217 $2R\Q7G&T  
qeE%U^<  
    15     1573 TF! zXjPOf  
ud, -xK  
    16     1785 #pXj*}l\  
qV<Xo_d  
    17     1469 9kt%f3a:X  
~4tV *pA>s  
    18     1892 ^jQaSuRl  
Or~y|f*n  
    19     1907 N84X^L0)%  
TvB ~t!Psk  
    20     1602 6!zD?1I %  
MC=< +J(  
    21     1540 }_$s Qgg  
c #JA)n<5  
    22     1013 ?`j2Hf![  
&{@#GT  
    23     441 d_bCs*_4*  
t+8"I]m*  
#LdC- PEi  
$JJUfjO?0  
24 rows selected. 5B0);"@S  
B@,KF^\pl  
hX
 +X+C  
<f-W&jER~5  
-------------------------------------------------------------------------------- hSye'8"Y  
zVZg)cc  
nte@E QY^U  
 !shkL{I  
R4digX]/  
  某年月日,突发奇想,打算在sqlplus下面按上面的数据画个直方图出来,首先写了个竖列的(因为好写): c~f e3^  
"
ki|sx:  
Iv2H]qF  
代码:--------------------------------------------------------------------------------  >VkY= ,  
SQL> COL Time FORMAT A7 p?\(\p\  
SQL> SELECT LPAD(DECODE(TIME,TO_CHAR(SYSDATE,'HH24'),'NOW: '||TO_CHAR(TIME),TO_CHAR(TIME)),7,' ') AS "Time", S<.(NeDP  
2 SUBSTR('|'||LPAD('> '||CNT,CEIL(CNT/A.TOTAL*300)+LENGTH(TO_CHAR(CNT))+1,'-'),1,35) AS "   Count per hour" :KB(D*  
3 FROM MV_TIME_STAT,(SELECT SUM(CNT) AS TOTAL FROM MV_TIME_STAT) A; Ui` %x@2  
COL Time CLEAR Qp:eb f  
Kexuo\o  
Time     Count per hour w4:llu>&S\  
------- ----------------------------------------------------------------- Yj:]53(iC  
    0 |--> 187 qC c0AI  
    1 |> 51 d)A7u_<H+  
    2 |> 34 &? Ev4"V  
    3 |> 19 K7+|2X  
    4 |> 19 X|vRS^ |  
    5 |> 20 LOE=7G
 
    6 |> 50 HF]+9D<  
    7 |-> 107 A,J06?  
NOW: 8 |---------> 682 mnm!91{bd  
    9 |-----------------> 1342 2lO3`QZq  
  10 |------------------------> 1854 ot=o:fc1=  
  11 |-----------------> 1292 wWb^8##  
  12 |------------------> 1416 }r6p$"]}  
  13 |---------------> 1180 n$T&|DG k  
  14 |----------------> 1217 BM2OoexkO  
  15 |--------------------> 1573 ^$MTg0u G  
  16 |-----------------------> 1785 ) v=~T3  
  17 |-------------------> 1469 t!G .  
  18 |-------------------------> 1892 lzF &89
_  
  19 |-------------------------> 1907 fi$P1#F`  
  20 |---------------------> 1602 8 V j8ef  
  21 |--------------------> 1540 *#}Cj,\V  
  22 |-------------> 1013 - 0a_bY]N  
  23 |-----> 441 @ X[qc9  
8_|)~JW/_  
24 rows selected.-------------------------------------------------------------------------------- ([^G@+7f{f  
OqOA*AnNK[  
s#|[\\T  
  然后又想,怎么样把它横过来,到网上查资料,又突击学习了下分析函数,写了一个过程是这个样子的: ;/+O#,r  
Z;ow0&'V  
P;_8i>v'^  
代码:-------------------------------------------------------------------------------- 3C@@? +LB  
CREATE OR REPLACE PROCEDURE HISTOGRAPH(HEIGHT NUMBER DEFAULT 15) AS Q-! |C  
MAX_ONE NUMBER; ~t&6sDp  
STR_LINE VARCHAR(120); y(bzzJ#  
STR_TEMP VARCHAR(120); %tD4.H:h  
I NUMBER; 1\k \j!  
BEGIN i 'g>vT=  
DBMS_OUTPUT.PUT_LINE(LPAD('^',14,' ')); "Qf&^X@i  
SELECT MAX(CNT) INTO MAX_ONE FROM MV_TIME_STAT; $
$Mf  
FOR I IN 1 .. HEIGHT+1 LOOP Do77Q2"M  
  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'); .oQmm-P  
  SELECT MAX(SYS_CONNECT_BY_PATH( (1ey-SO  
    DECODE(SIGN(CNT-MAX_ONE+(I-1)*MAX_ONE/HEIGHT),-1, ! #l5y  
    DECODE(SIGN(CNT-MAX_ONE+(I-2)*MAX_ONE/HEIGHT),-1,'^','*'), O<lQF%  
    '|'),
jgs 5US  
  ' ')) INTO STR_TEMP [j7Dr}i  
  FROM MV_TIME_STAT -Xf;3?S$  
  START WITH TIME=0 4BxCt RE  
  CONNECT BY PRIOR TIME=TIME-1; J4#fXaa\:  
  STR_LINE:=STR_LINE||STR_TEMP; `M+S*Ub  
  DBMS_OUTPUT.PUT_LINE(REPLACE (STR_LINE,'^',' ')); w| Gkp  
END LOOP; }8x`kdvvx  
SELECT MAX(SYS_CONNECT_BY_PATH(TO_CHAR(TIME,'00'),'^')) INTO STR_TEMP QpM\hD 1&  
FROM MV_TIME_STAT 1Th`8e*b  
START WITH TIME=0 1q<`W  
CONNECT BY PRIOR TIME=TIME-1; ?H+u 3ZGS  
DBMS_OUTPUT.PUT_LINE(LPAD('0',14,' ')||LPAD('>',LENGTH(REPLACE(STR_TEMP,' '))+3,'-')); #%%w_P]  
DBMS_OUTPUT.PUT_LINE(REPLACE(LPAD('^',14,' ')||REPLACE(STR_TEMP,' '),'^',' ')); >JU8~! =Z  
END; >*Hy6  
/   }VhWa;Xj  
LcIFjI5v6  
Procedure created. PdFK<eKc{|  
JrNNyy~Q{  
SQL> exec histograph; N?J^1A{#  
        ^ M'EF $m  
1907- 2034|                                       |
@Wx)s o  
1780- 1907|                     |           |   | | k&b; m/g9  
1653- 1780|                     |           |   | | Ee[u+T{ 5\  
1526- 1653|                     |         | |   | | | | cC% OQrI?  
1398- 1526|                     |   |     | | | | | | | hF $m*W0  
1271- 1398|                   | | | |     | | | | | | | @v`<K;?9  
1144- 1271|                   | | | | | | | | | | | | | _1ij)2Ol6  
1017- 1144|                   | | | | | | | | | | | | | 2n0f<2n  
  890- 1017|                   | | | | | | | | | | | | | | uST=;2^#  
  763-   890|                   | | | | | | | | | | | | | | S)U+G`@^  
  636-   763|                 | | | | | | | | | | | | | | | -T kCZ>U  
  509-   636|                 | | | | | | | | | | | | | | | e5r8_50ut|  
  381-   509|                 | | | | | | | | | | | | | | | | *=2g\z6  
  254-   381|                 | | | | | | | | | | | | | | | | Z]D"M?;ZE  
  127-   254| |               | | | | | | | | | | | | | | | | T ghYmPa  
  0-   127| | | | | | | | | | | | | | | | | | | | | | | | | c C=|3N8n  
        0--------------------------------------------------------------------------> D'9h{`%B  
          00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 . $1*  
amZeCw0+C  
PL/SQL procedure successfully completed.--------------------------------------------------------------------------------