Currently, an MV in the library is used to calculate the hourly traffic volume. The data is as follows:
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.
--------------------------------------------------------------------------------
On a particular day of the year, I had a whimsy. I planned to draw a histogram based on the above data under sqlplus. First I wrote a vertical column (because it is easy to write ):
Code :--------------------------------------------------------------------------------
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/. 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);
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 .--------------------------------------------------------------------------------
[Content navigation] |
Page 1st: [Oracle tutorial] Use PL/SQL to draw a Histogram |
Page 2nd: [Oracle tutorial] Use PL/SQL to draw a Histogram |