Orcale analysis function over (PARTITION by ...) Order by ...) The explanation __over

Over (PARTITION by ...) Order by ...) Description and significance of the use of

This function looks like this: Over (PARTITION by ...). Order by ...)

As the name suggests, PARTITION Chinese is the meaning of segmentation, order is the meaning of ordering, so translation is the first set of data in accordance with the established fields to be divided into various groups, and then the group in accordance with a field sorted.

To illustrate the use of this function in a practical case,

First, let's look at a set of commonly sorted data:

Then add a fake rownum to it and look at the original order of the data:

Over (order by ...) To sort by a field, so the order by effect is the same as the table directly:

Here it shows over (the order by ...) The first function is to renumber the data from the new query, that is, the value of the Rno, because there is no partition by ..., so you can compare the whole column to a chunk, then sort the chunks, then add partition by ..., query results:

It will be in accordance with the set of fields, the same value of the row together, divided into a block, that is, grouping, and then sorting the number within the group, so you can take the different groups of any number of values, similar to the TOP-N analysis.

The function used before over here is Row_number (), which is the number of the data.

with over (PARTITION by ...) Order by ...) Matching used functions row_number () over (), rank () over () and Dense_rank ( ) over ()

In the above example, the Row_number () can be used to number the data, but there is a problem, the mi_id in the example can not be repeated, if you can repeat the case, there is a side-by-side situation, so you can not take out the side of the data, only a single sorted data. So here you can change to rank () over () and Dense_rank () over () to number: (Rank () over () and Dense_rank () over () the difference as shown

SUM () over () , the use of First_value () over () and Last_value ( ) over ()

SUM () over () group sum

First_value () over () to group number one

Last_value () over () to group the last one

which with Row_number () over () the number of the first article can also achieve First_value () over () effect

Select DISTINCT *
  from (select t1.mi_id,
               T3. I_identity_card,
               SUM (NVL (t2.is_visheartpromember, 0)) over (PARTITION by T3. I_identity_card) Pro_num,
               SUM (NVL (t2.is_visheartexpmember, 0)) over (PARTITION by T3. I_identity_card) Exp_num,
               SUM (NVL (t2.is_visheartpolmember, 0)) over (PARTITION by T3. I_identity_card) Pol_num,
							 T1. Create_time,
               first_value (T2. create_date) over (PARTITION by T3. I_identity_card ORDER by T2. Create_date DESC) first_create_date from
          t_zz_petitioners T1 left
          JOIN t_zz_visit_record on
            T2. ci_rs_id = T2. ci_rs_id and
           T2. STATUS!= ' 003 ' left
          JOIN t_dc_ci_rs_top T3 on
            T1. ci_rs_id = T3. ci_rs_id and
           T3. STATUS = ' 1 '
         WHERE t1.is_add_heartprotect = ' 1 ' and
           T1. STATUS = ' 1 ') C ORDER by
 C.create_time DESC

SELECT * FROM (select t1.mi_id, T3. I_identity_card, SUM (NVL (t2.is_visheartpromember, 0)) over (PARTITION by T3. I_identity_card) Pro_num, SUM (NVL (t2.is_visheartexpmember, 0)) over (PARTITION by T3. I_identity_card) Exp_num, SUM (NVL (t2.is_visheartpolmember, 0)) over (PARTITION by T3. I_identity_card) Pol_num, T1. Create_time, Row_number () over (PARTITION by T3. I_identity_card ORDER by T2. Create_date DESC) Rno from T_zz_petitioners T1 left JOIN T_zz_visit_record on T2. ci_rs_id = T2. CI_RS_ID and T2. STATUS!= ' 003 ' left JOIN t_dc_ci_rs_top T3 on T1. ci_rs_id = T3. ci_rs_id left JOIN T_dc_grid T4 on T1. Region_code = t4.info_org_code WHERE t1.is_add_heartprotect = ' 1 ' and T1. STATUS = ' 1 ' and T3. STATUS = ' 1 ' and T4.
 STATUS = ' 001 ') C WHERE C.rno = 1 ORDER by C.create_time DESC

Ignore nulls: Filter out null value

The common analytic functions are listed below:

Row_number () over (partition by ...)
Rank () over (partition by ...)
Dense_rank () over (partition by ...)
Count () over (partition by ...)
Max () over (partition by ...)
Min () over (partition by ...)
SUM () over (partition by ...)
AVG () over (partition by ...)
First_value () over (partition by ...)
Last_value () over (partition by ...)
Lag () over (partition by ...)
Lead () over (partition by ...)