Re-learning notes for SQL Server (2)

You may be familiar with the top keyword usage, but do you know whether the top query returns a table result or a cursor?

When the top clause is used, the same order by clause assumes the role of determining the logical priority of the rows for the top clause, and also assumes its regular role (displaying data ), the result is changed from the table to a cursor with a fixed order.

The following three methods are used: 1. If the keyword is percent, SQL Server calculates the number of rows that meet the condition based on the percentage (rounded up );

Use select top 1 percent orderid from ordervalues.

2. Select the first few lines, which are usually used for paging;

Use select top 10 orderid from ordervalues.

3. to return all other rows whose sorting values are the same as those of the last row in Top N, the with ties option must be added. If there are multiple sorting values, the last sorting value prevails, here is the custid.

Usage select top (5) with ties orderid, orderdate, custid, empid
From orders
Order by empid DESC, custid

The over clause defines a window for a row for specific operations. We can simply consider the window of rows as a set of rows to be operated. The over clause can provide a row window for aggregate and rank functions. These functions are also called window functions. The over clause with empty parentheses provides all rows for calculation. All rows here are not necessarily all rows in the tables in the from clause, but the rows that can still be used after the from, where, group by, and having processing stages are completed. So where can I use the over clause ?? Answer is only in the select and order by processing stages.

In the following example, ordervalues is a view (where data can be downloaded in my previous blog ). The statement for creating this view is

 Create   View  Ordervalues  As Select  O. orderid, O. custid, O. empid, O. shipperid, O. orderdate,  Cast ( Sum (OD. Qty * OD. unitprice * ( 1   -  Discount ))  As Numeric ( 12 , 2 )) As Val  From Orders As  O  Join Orderdetails As  Od  On O. orderid =  OD. orderid  Group   By O. orderid, O. custid, O. empid, O. shipperid, O. orderdate;

 

see the following SQL statement. Sum (VAL) over () as totalvalue indicates that the sum of the Val fields of all current rows is returned. If we want to get the sum of values with the same custid for the current row, you can use the partition by clause to specify the sum keyword.

   1   select   *   from   ordervalues;   2   select   orderid, custid, val,   3   sum  (VAL)  over  ()  as   totalvalue,   4   sum  (VAL)  over  (partition  by  custid)  as   custtotalvalue   5   from  ordervalues;  

Of course, an advantage of the over clause is that it can be used together with the basic columns while returning the basic rows. For example, the following SQL statement.

 
1 SelectOrderid, custid, Val,2100. * Val/sum (VAL) over ()AsPctall,3100. * Val/sum (VAL) over (partition by custid)AsPctust4 FromOrdervalues;
 

In addition, the over clause supports four ranking functions: row_number (), rank (), dense_rank (), and ntile, as shown in the following SQL statement.

1   Select  Orderid, custid, Val,  2 Row_number () Over ( Order   By Val) As  Rownum,  3 Rank () Over ( Order   By Val) As Rank,  4 Dense_rank () Over ( Order   By Val) As  Dense_rank,  5 Ntile (1 0 ) Over ( Order   By Val) As  Ntile 6   From  Ordervalues  7   Order   By Val

The row_number function is used to assign an incremental serial number to each row in the subquery result set. The logic order is determined by the order by statement in the over clause. Therefore, the value of the field in the order by clause should be unique in the database. The ntile function can associate rows in the result to a group. N in ntile (n) indicates the number of groups and assigns a group number to each row, therefore, the size of the group is 830/10 = 83, that is, 83 rows of data exist in the first group. Of course, the ranking function also supports the partition by statement. Rank () and dense_rank () can generate the same ranking for all rows with the same logical sorting value. The difference between the two functions is that rank () indicates the number of rows with lower sorting values, while the dense ranking function indicates the number of lower sorting values, note that one is a row and one value.