How to create an oracle view for multiple queries of data in different time periods in the same table

I believe you have some knowledge about how to create a view in oracle. The following describes how to create a view in oracle for multiple queries of data in different time periods in the same table.

During project development, there is an hourly flow statement, which records the traffic of each channel every hour. The simplified fields are as follows and only valid fields are retained ):
NAME Null? Type
---------------------------------------------
IDNUMBER (19,0)
REPORT_TIME DATE
SEND_NUM NUMBER (19,0)
SUCCESS_NUM NUMBER (19,0)
 
Now you need to create a view with the following fields: Channel id, sending success rate of the previous hour, sending success rate of the last hour, and sending success rate of the last third hour. For example, the current view is, the sending success rate of the three time periods, and is required. If send_num is 0 or null, the result is null.

Create a view in oracle:

1. Processing of time periods:
To_char (sysdate, 'hh24')-to_char (report_time, 'hh24') can be used to obtain a small time difference. The query results are obtained based on the results of 1, 2, and 3. You can use case... when... then... end to determine. Note: If the hour of the next day is 00 and the hour of the previous day is 23, special processing is required. Otherwise, a negative number is displayed. Here we use + 24 to convert it to a positive number. This completes the comparison of time difference.

2. If send_num is null or 0, it is used as a divisor:
Send_num can be processed first. If it is null or 0, no computation is performed directly.

3. List the complete query statements:

 
 

  
  
select  vmhr.v_mo,  
  
  
vmhr.org_code,  
  
  
case decode(sign(to_char(sysdate,'hh24')-to_char(report_time,'hh24')),-1,to_char  (sysdate,'hh24')-to_char(report_time,'hh24')+24,to_char(sysdate,'hh24')-to_char(report_time,'hh24')) when 1 then (select decode(nvl(send_num,0),0,null,nvl(success_num,0)/nvl(send_num,1)) from channel_flux where v_mo=vmhr.v_mo) end last_1_suss_rate,  
  
  
 
  
  
case decode(sign(to_char(sysdate,'hh24')-to_char(report_time,'hh24')),-1,to_char(sysdate,'hh24')-to_char(report_time,'hh24')+24,to_char(sysdate,'hh24')-to_char(report_time,'hh24')) when 2 then (select decode(nvl(send_num,0),0,null,nvl(success_num,0)/nvl(send_num,1)) from channel_flux where v_mo=vmhr.v_mo) end last_2_suss_rate,  
  
  
 
  
  
case decode(sign(to_char(sysdate,'hh24')-to_char(report_time,'hh24')),-1,to_char(sysdate,'hh24')-to_char(report_time,'hh24')+24,to_char(sysdate,'hh24')-to_char(report_time,'hh24')) when 3 then (select decode(nvl(send_num,0),0,null,nvl(success_num,0)/nvl(send_num,1)) from channel_flux where v_mo=vmhr.v_mo) end last_3_suss_rate  
  
  
from channel_flux vmhr  
 
 

Description: sign (var) is an oracle built-in function. Positive and Negative values of the variable var are obtained. If it is less than 0,-1 is returned. If it is equal to 0, 0 is returned. If it is greater than 0, 1 is returned. decode is a built-in oracle function that can be used for simple judgment. case... when... then... end judgment condition, indicating that the case variable when meets the condition then can be used to query the help documentation of oracle for specific use of the execution statement end.

4. After the query is completed, create a view:
Create view as..., add the preceding query statement to meet the view requirements.
 

Create tables and indexes in Oracle

Implementation of oracle re-Indexing

Complete Oracle File System Parsing

ORACLE system table and data dictionary View

In-depth analysis of Oracle ERP system module