菜鸟学oracle - 用PL/SQL画直方图
来源:互联网 发布:苹果启用4g语音与数据 编辑:程序博客网 时间:2024/05/30 23:30
现在用的库里有一个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.
==============================
- 菜鸟学oracle - 用PL/SQL画直方图
- 菜鸟学oracle:用PL/SQL画直方图
- oracle - 用PL/SQL画直方图
- 新手学PL/SQL
- 新手学PL/SQL
- 学点PL/SQL
- 用PL/SQL连接Oracle
- 菜鸟学SQL语句
- 菜鸟个人总结之纯sql导入数据(oracle,pl/sql)
- 新手学PL/SQL (1)
- 新手学PL/SQL(2)
- 新手学PL/SQL(2)
- 新手学PL/SQL (1)
- 用PL/sql developer 访问Oracle
- 用PL/SQL 将excel 导入Oracle
- ORACLE PL/SQL 基础
- Oracle PL/SQL入门
- oracle pl/sql
- C#找不到头绪了
- ROR 与 SSH的一些想法
- Ubuntu下N卡配置无法保存的解决办法
- Video: Making money on the Web the black hat way
- Digester:一个通用xml引擎的设计剖析
- 菜鸟学oracle - 用PL/SQL画直方图
- Using a hacked Wordpress site to pwn the web server
- 任意类型文件转换PDF问题研究
- 原来是这样
- 从软盘引导操作系统
- include,include_once和require,require_once的区别 {转}
- 几个常用的JavaScript字符串处理函数 - split()、join()、substring()和indexOf()
- 感悟
- Matlab常用图像操作