Comprehensive use of segmented statistics and Oracle analysis functions

 

Project requirements:

Count the attendance rate of each inspector (USER_ID) for the current month and query related fields

Sign-In rate formula: the unit is inspection personnel,

(Total number of inspection points in all the points sign in this month till today) = (B. Point/a. Total)

More requirements related to TOTAL:

① During use, the number of inspection sites will be changed according to the actual situation. For example, on January 1, January 15, 2014, five inspection sites will be arranged for a certain inspector and two points will be added in 10 days, that is to say, the statistics are based on seven points after January 1, January 24, 2014. Therefore, if the changes are made multiple times in the current month, this month will be divided into many segments.

② Each change has a batch number, corresponding to the POINT_DATE field in the T_SIGN_POINTS table. This field is changed the next day, that is, today's change, and will take effect tomorrow.

③ TOTAL formula:

Assume that the following known conditions exist:

Assume that today is July 22, April 25, 2014, and USER_ID = 1249. count the number of points that should be inspected by the inspector on July 31, April 2014. In T_SIGN_POINTS, the number of related batches includes Batch2014.3.29 = 6 and Batch2014.4.5 = 8, batch2014.4.12 = 11 and Batch2014.4.26 = 5

Then the mathematical calculation of TOTAL should be: 6 × 4 days + 8 × 11 days + 11 × 13 days = 255 (and other boundary conditions are similar)

Multiple possibilities need to be taken into account here. Even if you only use the most ideal scenario, using the above formula completely using the SQL language is also a very complicated process. In contrast, it is difficult to calculate the required fields in the background. However, this method can greatly improve the use and understanding of Oracle or SQL, it is also possible that Oracle has more appropriate functions to solve similar problems.

Related tables and fields:

Table T_SIGN_RECORDS: records the points that the inspector has signed in.

Table T_SIGN_POINTS: record the inspection points arranged for each inspector

Complete SQL statement:

             ( a.                                                                                                                                                                                                                                                                                                                        b.name                   c.name                         a.dep_id              a.role_id              IS_PATROL       ( a.                     (                        b.point                                                                        )    (a.total)                  ( , (PartNum)  total                            (                                           decode(                                                                                        decode(greatest(batchday, tday), tday, (tday  batchday  )  batchnum, batchday, ,                                                                                                  (seg_next  batchday)                                                     (tday  batchday  )                                                                                                )                                  ( a.,                                                                      lead(a.batchDay, , ) (PARTITION  a.   a.batchDay, a.batchmon)                                          ( t.,                                                                   to_char(( EXTRACT(  sysdate)  DUAL))                                                      to_char(( EXTRACT(  sysdate)  DUAL))                                                      decode((to_CHAR(TO_DATE(t.points_date, ),                                                             (to_CHAR(sysdate,                                                             ( (trunc(TO_DATE(t.points_date, ), )  )                                                             ( trunc(sysdate, )  dual))                                                      decode((to_CHAR(TO_DATE(t.points_date, ),                                                             (to_CHAR(sysdate,                                                             to_number(to_CHAR(TO_DATE(t.points_date, ),                                                             )                                                      (t.)                                                                                                 (to_date(t.points_date, )  ( trunc(sysdate, )  dual)                                                     (t.points_date  ( (points_date)                                                                                                                                                     t.                                                                           (to_date(points_date, )                                                                               ( trunc(sysdate, )                                                                                                                            t.                                                 t. , batchDay , batchmon ) A                                            , batchday , batchmon ))                                                      ) a                                    ( ()  point,                                                                       to_char(sign_time, )                                                  a.  b.      x.id  y.    order_index, 

Note:

[Peripheral layer] connects to: User table (T_USER), department table (T_DEPARTMENT), role table (T_ROLE) to query some fields, it is worth noting that the use of case when then else end and the display format "||'/'|"

[POINT layer] the use of a simple count function

[TOTAL layer] is the focus, which is nested by multiple layers. Next, the result pictures and knowledge points of each layer are as follows:

[TOTAL layer 0th]

Result image:

SQL function: Query and count the number of batches related to the current month, that is, the number of vertices of each batch.

BatchNum is the number of points in the batch,

TMon is the month of the current month,

BatchMon is the month where the batch number is located. The value of this field can distinguish whether the batch number is the current month or the maximum batch number before this month to help the lead function sort the batch number. If the batch number is in the current month, the result is 2nd days of the current month. If the result is earlier than the current month, the system returns the 1st days of the current month.

[TOTAL layer 1st]

Result image:

SQL function: Mainly adds the SEG_NEXT field and uses the date of the next batch number as SEG_NEXT

lead(a.batchDay, , ) (PARTITION  a.   a.batchDay, a.batchmon)  seg_Next

PARTITION starts with. user_id is the region where the lead function is executed, according to. batchDay and. batchmon sorting. Here we can see the significance of the batchmon field: if there is a batch number on January 1, April 1, 2014 this month, the maximum batch number smaller than this month will be placed under the batch number on January 1, April 1, in the result image, the values of batchDay and SEG_NEXT are both 1 and the difference between the two segments is 0, which does not affect subsequent results.

 

[TOTAL layer 2nd]

Result Image

SQL knowledge point:

Decode function:

The Decode function can be used with the sigh function and the greatest function. When used with the greatest function, pay attention to the order of parameters and the order of if_value and value, because the greatest parameters are equal, when the parameters are equal, the value of the first if_value is executed.