friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.sqlp_5.4.0.1/rbafyolap.htm

343 lines
17 KiB
HTML
Raw Permalink Normal View History

Add readme and dist folders
2024-04-02 14:02:31 +00:00
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow" />
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="DC.Type" content="concept" />
<meta name="DC.Title" content="Use OLAP specifications" />
<meta name="abstract" content="Online analytical processing (OLAP) specifications are used to return ranking numbers and row numbers for the result rows of a query. You can specify RANK, DENSE_RANK, and ROW_NUMBER." />
<meta name="description" content="Online analytical processing (OLAP) specifications are used to return ranking numbers and row numbers for the result rows of a query. You can specify RANK, DENSE_RANK, and ROW_NUMBER." />
<meta name="DC.Relation" scheme="URI" content="rbafytexas.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rbafyolap" />
<meta name="DC.Language" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>Use OLAP specifications</title>
</head>
<body id="rbafyolap"><a name="rbafyolap"><!-- --></a>
<img src="./delta.gif" alt="Start of change" /><!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">Use OLAP specifications</h1>
<div><p><span>Online analytical processing (OLAP) specifications
are used to return ranking numbers and row numbers for the result rows of
a query. You can specify RANK, DENSE_RANK, and ROW_NUMBER.</span></p>
<div class="section"><h4 class="sectiontitle">Example: Ranking and row numbering</h4><div class="p">Suppose you want
a list of the top 10 salaries along with their ranking. The following query
generates the ranking number for you.<pre> <strong>SELECT</strong> EMPNO, SALARY
<strong>RANK()</strong> <strong>OVER</strong>(<strong>ORDER BY</strong> SALARY <strong>DESC</strong>),
<strong>DENSE_RANK()</strong> <strong>OVER</strong>(<strong>ORDER BY</strong> SALARY <strong>DESC</strong>),
<strong>ROW_NUMBER()</strong> <strong>OVER</strong>(<strong>ORDER BY</strong> SALARY <strong>DESC</strong>)
<strong>FROM</strong> EMPLOYEE
<strong>FETCH</strong> <strong>FIRST</strong> 10 <strong>ROWS</strong> <strong>ONLY</strong></pre>
</div>
<p>This
query returns the following information.</p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="border" border="1" rules="all"><caption>Table 1. Results of the previous
query</caption><thead align="left"><tr valign="bottom"><th valign="bottom" id="d0e86">EMPNO</th>
<th valign="bottom" id="d0e88">SALARY</th>
<th valign="bottom" id="d0e90">RANK</th>
<th valign="bottom" id="d0e92">DENSE_RANK</th>
<th valign="bottom" id="d0e94">ROW_NUMBER</th>
</tr>
</thead>
<tbody><tr><td valign="top" headers="d0e86 ">000010</td>
<td valign="top" headers="d0e88 ">52,750.00</td>
<td valign="top" headers="d0e90 ">1</td>
<td valign="top" headers="d0e92 ">1</td>
<td valign="top" headers="d0e94 ">1</td>
</tr>
<tr><td valign="top" headers="d0e86 ">000110</td>
<td valign="top" headers="d0e88 ">46,500.00</td>
<td valign="top" headers="d0e90 ">2</td>
<td valign="top" headers="d0e92 ">2</td>
<td valign="top" headers="d0e94 ">2</td>
</tr>
<tr><td valign="top" headers="d0e86 ">200010</td>
<td valign="top" headers="d0e88 ">46,500.00</td>
<td valign="top" headers="d0e90 ">2</td>
<td valign="top" headers="d0e92 ">2</td>
<td valign="top" headers="d0e94 ">3</td>
</tr>
<tr><td valign="top" headers="d0e86 ">000020</td>
<td valign="top" headers="d0e88 ">41,250.00</td>
<td valign="top" headers="d0e90 ">4</td>
<td valign="top" headers="d0e92 ">3</td>
<td valign="top" headers="d0e94 ">4</td>
</tr>
<tr><td valign="top" headers="d0e86 ">000050 </td>
<td valign="top" headers="d0e88 ">40,175.00</td>
<td valign="top" headers="d0e90 ">5</td>
<td valign="top" headers="d0e92 ">4</td>
<td valign="top" headers="d0e94 ">5</td>
</tr>
<tr><td valign="top" headers="d0e86 ">000030</td>
<td valign="top" headers="d0e88 ">38,250.00</td>
<td valign="top" headers="d0e90 ">6</td>
<td valign="top" headers="d0e92 ">5</td>
<td valign="top" headers="d0e94 ">6</td>
</tr>
<tr><td valign="top" headers="d0e86 ">000070</td>
<td valign="top" headers="d0e88 ">36,170.00</td>
<td valign="top" headers="d0e90 ">7</td>
<td valign="top" headers="d0e92 ">6</td>
<td valign="top" headers="d0e94 ">7</td>
</tr>
<tr><td valign="top" headers="d0e86 ">000060</td>
<td valign="top" headers="d0e88 ">32,250.00</td>
<td valign="top" headers="d0e90 ">8</td>
<td valign="top" headers="d0e92 ">7</td>
<td valign="top" headers="d0e94 ">8</td>
</tr>
<tr><td valign="top" headers="d0e86 ">000220</td>
<td valign="top" headers="d0e88 ">29,840.00</td>
<td valign="top" headers="d0e90 ">9</td>
<td valign="top" headers="d0e92 ">8</td>
<td valign="top" headers="d0e94 ">9</td>
</tr>
<tr><td valign="top" headers="d0e86 ">200220</td>
<td valign="top" headers="d0e88 ">29,840.00</td>
<td valign="top" headers="d0e90 ">9</td>
<td valign="top" headers="d0e92 ">8</td>
<td valign="top" headers="d0e94 ">10</td>
</tr>
</tbody>
</table>
</div>
<p>In this example, the SALARY descending order with the top 10 returned.
The RANK column shows the relative ranking of each salary. Notice that there
are two rows with the same salary at position 2. Each of those rows is assigned
the same rank value. The following row is assigned the value of 4. RANK returns
a value for a row that is one more than the total number of rows that precede
that row. There are gaps in the numbering sequence whenever there are duplicates.</p>
<p>In
contrast, the DENSE_RANK column shows a value of 3 for the row directly after
the duplicate rows. DENSE_RANK returns a value for a row that is one more
than the number of distinct row values that precede it. There will never be
gaps in the numbering sequence.</p>
<p>ROW_NUMBER returns a unique number for
each row. For rows that contain duplicate values according to the specified
ordering, the assignment of a row number is arbitrary; the row numbers could
be assigned in a different order for the duplicate rows when the query is
run another time.</p>
</div>
<div class="section"><h4 class="sectiontitle">Example: Ranking groups</h4><div class="p">In this example, suppose
you want to find out which department has the highest average salary. The
following query will group the data by department, determine the average salary
for each department, and then rank the resulting averages.<pre><strong>SELECT</strong> WORKDEPT, <strong>INT</strong>(<strong>AVG</strong>(SALARY)) <strong>AS</strong> AVERAGE,
<strong>RANK()</strong> <strong>OVER</strong>(<strong>ORDER BY</strong> <strong>AVG</strong>(SALARY) <strong>DESC</strong>) <strong>AS</strong> AVG_SALARY
<strong>FROM</strong> EMPLOYEE
<strong>GROUP BY</strong> WORKDEPT </pre>
</div>
<p>This query returns the following
information.</p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="border" border="1" rules="all"><caption>Table 2. Results of previous query</caption><thead align="left"><tr valign="bottom"><th valign="bottom" id="d0e266">WORKDEPT</th>
<th valign="bottom" id="d0e268">AVERAGE</th>
<th valign="bottom" id="d0e270">AVG_SALARY</th>
</tr>
</thead>
<tbody><tr><td valign="top" headers="d0e266 ">B01</td>
<td valign="top" headers="d0e268 ">41,250</td>
<td valign="top" headers="d0e270 ">1</td>
</tr>
<tr><td valign="top" headers="d0e266 ">A00</td>
<td valign="top" headers="d0e268 ">40,850</td>
<td valign="top" headers="d0e270 ">2</td>
</tr>
<tr><td valign="top" headers="d0e266 ">E01</td>
<td valign="top" headers="d0e268 ">40,175</td>
<td valign="top" headers="d0e270 ">3</td>
</tr>
<tr><td valign="top" headers="d0e266 ">C01</td>
<td valign="top" headers="d0e268 ">29,722</td>
<td valign="top" headers="d0e270 ">4</td>
</tr>
<tr><td valign="top" headers="d0e266 ">D21</td>
<td valign="top" headers="d0e268 ">25,668</td>
<td valign="top" headers="d0e270 ">5</td>
</tr>
<tr><td valign="top" headers="d0e266 ">D11</td>
<td valign="top" headers="d0e268 ">25,147</td>
<td valign="top" headers="d0e270 ">6</td>
</tr>
<tr><td valign="top" headers="d0e266 ">E21</td>
<td valign="top" headers="d0e268 ">24,086</td>
<td valign="top" headers="d0e270 ">7</td>
</tr>
<tr><td valign="top" headers="d0e266 ">E11</td>
<td valign="top" headers="d0e268 ">21,020</td>
<td valign="top" headers="d0e270 ">8</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section"><h4 class="sectiontitle">Example: Ranking within a department</h4><div class="p">Suppose you
want a list of employees along with how their bonus ranks within their department.
Using the PARTITION BY clause, you can specify groups that are to be numbered
separately.<pre><strong>SELECT</strong> LASTNAME, WORKDEPT, BONUS,
<strong>DENSE_RANK()</strong> <strong>OVER</strong>(<strong>PARTITION BY</strong> WORKDEPT <strong>ORDER BY</strong> BONUS <strong>DESC</strong>)
<strong>AS</strong> BONUS_RANK_IN_DEPT
<strong>FROM</strong> EMPLOYEE
<strong>WHERE</strong> WORKDEPT <strong>LIKE</strong> 'E%'</pre>
</div>
<p>This query returns
the following information.</p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="border" border="1" rules="all"><caption>Table 3. Results of the previous query</caption><thead align="left"><tr valign="bottom"><th valign="bottom" width="25%" id="d0e377">LASTNAME</th>
<th valign="bottom" width="25%" id="d0e379">WORKDEPT</th>
<th valign="bottom" width="25%" id="d0e381">BONUS</th>
<th valign="bottom" width="25%" id="d0e383">BONUS_RANK_in_DEPT</th>
</tr>
</thead>
<tbody><tr><td valign="top" width="25%" headers="d0e377 ">GEYER</td>
<td valign="top" width="25%" headers="d0e379 ">E01</td>
<td valign="top" width="25%" headers="d0e381 ">800.00</td>
<td valign="top" width="25%" headers="d0e383 ">1</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">HENDERSON</td>
<td valign="top" width="25%" headers="d0e379 ">E11</td>
<td valign="top" width="25%" headers="d0e381 ">600.00</td>
<td valign="top" width="25%" headers="d0e383 ">1</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">SCHNEIDER</td>
<td valign="top" width="25%" headers="d0e379 ">E11</td>
<td valign="top" width="25%" headers="d0e381 ">500.00</td>
<td valign="top" width="25%" headers="d0e383 ">2</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">SCHWARTZ</td>
<td valign="top" width="25%" headers="d0e379 ">E11</td>
<td valign="top" width="25%" headers="d0e381 ">500.00</td>
<td valign="top" width="25%" headers="d0e383 ">2</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">SMITH</td>
<td valign="top" width="25%" headers="d0e379 ">E11</td>
<td valign="top" width="25%" headers="d0e381 ">400.00</td>
<td valign="top" width="25%" headers="d0e383 ">3</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">PARKER</td>
<td valign="top" width="25%" headers="d0e379 ">E11</td>
<td valign="top" width="25%" headers="d0e381 ">300.00</td>
<td valign="top" width="25%" headers="d0e383 ">4</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">SETRIGHT</td>
<td valign="top" width="25%" headers="d0e379 ">E11</td>
<td valign="top" width="25%" headers="d0e381 ">300.00</td>
<td valign="top" width="25%" headers="d0e383 ">4</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">SPRINGER</td>
<td valign="top" width="25%" headers="d0e379 ">E11</td>
<td valign="top" width="25%" headers="d0e381 ">300.00</td>
<td valign="top" width="25%" headers="d0e383 ">4</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">SPENSER</td>
<td valign="top" width="25%" headers="d0e379 ">E21</td>
<td valign="top" width="25%" headers="d0e381 ">500.00</td>
<td valign="top" width="25%" headers="d0e383 ">1</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">LEE</td>
<td valign="top" width="25%" headers="d0e379 ">E21</td>
<td valign="top" width="25%" headers="d0e381 ">500.00</td>
<td valign="top" width="25%" headers="d0e383 ">1</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">GOUNOT</td>
<td valign="top" width="25%" headers="d0e379 ">E21</td>
<td valign="top" width="25%" headers="d0e381 ">500.00</td>
<td valign="top" width="25%" headers="d0e383 ">1</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">WONG</td>
<td valign="top" width="25%" headers="d0e379 ">E21</td>
<td valign="top" width="25%" headers="d0e381 ">500.00</td>
<td valign="top" width="25%" headers="d0e383 ">1</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">ALONZO</td>
<td valign="top" width="25%" headers="d0e379 ">E21</td>
<td valign="top" width="25%" headers="d0e381 ">500.00</td>
<td valign="top" width="25%" headers="d0e383 ">1</td>
</tr>
<tr><td valign="top" width="25%" headers="d0e377 ">MENTA</td>
<td valign="top" width="25%" headers="d0e379 ">E21</td>
<td valign="top" width="25%" headers="d0e381 ">400.00</td>
<td valign="top" width="25%" headers="d0e383 ">2</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section"><h4 class="sectiontitle">Example: Ranking and ordering by table expression results</h4><p>Suppose
you want to find the top five employees whose salaries are the highest along
with their department names. The department name is in the <em>department</em> table,
so a join is needed. Since ordering is already being done in the nested table
expression, that ordering can also be used for determining the ROW_NUMBER
value. The ORDER BY ORDER OF <em>table</em> clause is used to do this.</p>
<pre><strong>SELECT</strong> <strong>ROW_NUMBER()</strong> <strong>OVER</strong>(<strong>ORDER BY ORDER OF</strong> EMP),
EMPNO, SALARY, DEPTNO, DEPTNAME
<strong>FROM</strong> (<strong>SELECT</strong> EMPNO, WORKDEPT, SALARY
<strong>FROM</strong> EMPLOYEE
<strong>ORDER BY</strong> SALARY <strong>DESC</strong>
<strong>FETCH</strong> <strong>FIRST</strong> 5 <strong>ROWS</strong> <strong>ONLY</strong>) EMP,
DEPARTMENT
<strong>WHERE</strong> DEPTNO = WORKDEPT</pre>
<p>This query returns
the following information.</p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="border" border="1" rules="all"><caption>Table 4. Results of the previous query</caption><thead align="left"><tr valign="bottom"><th valign="bottom" width="20%" id="d0e579">ROW_NUMBER</th>
<th valign="bottom" width="20%" id="d0e581">EMPNO</th>
<th valign="bottom" width="20%" id="d0e583">SALARY</th>
<th valign="bottom" width="20%" id="d0e585">DEPTNO</th>
<th valign="bottom" width="20%" id="d0e587">DEPTNAME</th>
</tr>
</thead>
<tbody><tr><td valign="top" width="20%" headers="d0e579 ">1</td>
<td valign="top" width="20%" headers="d0e581 ">000010</td>
<td valign="top" width="20%" headers="d0e583 ">52,750.00</td>
<td valign="top" width="20%" headers="d0e585 ">A00</td>
<td valign="top" width="20%" headers="d0e587 ">SPIFFY COMPUTER SERVICE DIV.</td>
</tr>
<tr><td valign="top" width="20%" headers="d0e579 ">2</td>
<td valign="top" width="20%" headers="d0e581 ">000110</td>
<td valign="top" width="20%" headers="d0e583 ">46,500.00</td>
<td valign="top" width="20%" headers="d0e585 ">A00</td>
<td valign="top" width="20%" headers="d0e587 ">SPIFFY COMPUTER SERVICE DIV.</td>
</tr>
<tr><td valign="top" width="20%" headers="d0e579 ">3</td>
<td valign="top" width="20%" headers="d0e581 ">200010</td>
<td valign="top" width="20%" headers="d0e583 ">46,500.00</td>
<td valign="top" width="20%" headers="d0e585 ">A00</td>
<td valign="top" width="20%" headers="d0e587 ">SPIFFY COMPUTER SERVICE DIV.</td>
</tr>
<tr><td valign="top" width="20%" headers="d0e579 ">4</td>
<td valign="top" width="20%" headers="d0e581 ">000020</td>
<td valign="top" width="20%" headers="d0e583 ">41,250.00</td>
<td valign="top" width="20%" headers="d0e585 ">B01</td>
<td valign="top" width="20%" headers="d0e587 ">PLANNING</td>
</tr>
<tr><td valign="top" width="20%" headers="d0e579 ">5</td>
<td valign="top" width="20%" headers="d0e581 ">000050</td>
<td valign="top" width="20%" headers="d0e583 ">40,175.00</td>
<td valign="top" width="20%" headers="d0e585 ">E01</td>
<td valign="top" width="20%" headers="d0e587 ">SUPPORT SERVICES</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafytexas.htm" title="Learn a variety of ways of tailoring your query to gather data using the SELECT statement. One way to do this is to use the SELECT statement in a program to retrieve a specific row (for example, the row for an employee). Furthermore, you can use clauses to gather data in a specific way.">Retrieve data using the SELECT statement</a></div>
</div>
</div>
<img src="./deltaend.gif" alt="End of change" /></body>
</html>
Reference in New Issue Copy Permalink