Example: Using the RANK Function in a QUALIFY Clause
The following statement displays each item in a sales table, its total sales, and its rank within the top 100 selling items:
SELECT itemid, sumprice, RANK() OVER (ORDER BY sumprice DESC) FROM (SELECT a1.item_id, SUM(a1.sale) FROM sales AS a1 GROUP BY a1.itemID) AS t1 (item_id, sumprice) QUALIFY RANK() OVER (ORDER BY sum_price DESC) <=100;
Example: Reporting the Bottom Percentile of Items Using QUANTILE in a QUALIFY Clause
The following example reports the bottom percentile of items by profitability:
SELECT item, profit, QUANTILE(100, profit) AS percentile FROM (SELECT item, SUM(sales)-(COUNT(sales)*items.itemcost) AS profit FROM daily_sales, items WHERE daily_sales.item = items.item GROUP BY item) AS itemprofit QUALIFY percentile = 99;
The results of this query might look something like the following table.
| item | profit | percentile |
|---|---|---|
| Carrot-flavored ice cream | 10.79 | 99 |
| Low fat carrot-flavored ice cream | - 100.55 | 99 |
| Low fat sugar-free carrot-flavored ice cream | - 1,110.67 | 99 |
| Regular broccoli-flavored ice cream | - 2,913.88 | 99 |
| Low fat broccoli-flavored ice cream | - 4,492.12 | 99 |
Example: Behavior of OLAP Aggregate Functions That Return Zeroes
When you specify an ordered analytical aggregate function in the search condition of a QUALIFY clause, it can return a result of 0.
When there are no values to aggregate, ordered analytical aggregate functions return a 0 instead of a null.
This example first shows the rows in the demogr table that are used to calculate the ordered analytical aggregate result for the examples of current behavior that follow.
The statement returns 12 rows with valueless aggregate rows reported with zeroes rather than nulls in the Remaining Count(inc) column.
SELECT line, da, mon, inc, COUNT(inc) OVER(PARTITION BY mon ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM demogr WHERE yr = 94 AND mon < 13 AND da < 10; *** Query completed. 12 rows found. 5 columns returned. *** Total elapsed time was 1 second. line da mon inc Remaining Count(inc) ------ ------ ---- -------------------- -------------------- 4803 3 1 234737.37 0 3253 2 1 ? 1 625 4 2 46706.97 0 3853 3 4 282747.07 0 3687 1 5 172470.52 0 547 9 5 31848.56 1 2292 6 7 170411.66 0 5000 8 9 40548.61 0 3213 8 9 257858.55 1 3948 6 10 217091.30 0 2471 1 10 121299.65 1 1496 7 12 ? 0
This example shows the current behavior of a qualify clause over demogr using the ordered analytical COUNT function. The query again returns 12 rows, with zeroes in place of nulls in the Remaining Count(inc) column.
SELECT line, da, mon, inc, COUNT(inc) OVER(PARTITION BY mon ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM demogr WHERE yr = 94 AND mon < 13 AND da < 10 QUALIFY COUNT(inc) OVER(PARTITION BY mon ORDER BY mon, line ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) < 3 ; *** Query completed. 12 rows found. 5 columns returned. *** Total elapsed time was 1 second. line da mon inc Remaining Count(inc) ------ ------ ---- -------------------- -------------------- 4803 3 1 234737.37 0 3253 2 1 ? 1 625 4 2 46706.97 0 3853 3 4 282747.07 0 3687 1 5 172470.52 0 547 9 5 31848.56 1 2292 6 7 170411.66 0 5000 8 9 40548.61 0 3213 8 9 257858.55 1 3948 6 10 217091.30 0 2471 1 10 121299.65 1 1496 7 12 ? 0
This query returns the rows from the demogr table used to calculate the ordered analytical aggregate result for the examples that show the old style of returning results of an ordered analytical aggregate function specified in the search condition of a QUALIFY clause.
By using this method, valueless aggregates are returned as nulls in the Remaining Sum(1) column rather than as zeroes.
SELECT line, da, mon, inc, SUM(1) OVER(PARTITION BY mon ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM demogr WHERE yr = 94 AND mon < 13 AND da < 10; *** Query completed. 12 rows found. 5 columns returned. *** Total elapsed time was 1 second. line da mon inc Remaining Sum(1) ------ ------ ---- -------------------- ---------------- 4803 3 1 234737.37 ? 3253 2 1 ? 1 625 4 2 46706.97 ? 3853 3 4 282747.07 ? 3687 1 5 172470.52 ? 547 9 5 31848.56 1 2292 6 7 170411.66 ? 5000 8 9 40548.61 ? 3213 8 9 257858.55 1 3948 6 10 217091.30 ? 2471 1 10 121299.65 1 1496 7 12 ? ?
This example shows how to use a ordered analytical SUM(1) function as a workaround to return nulls in the result instead of zeroes. The query again returns 12 rows, but reports valueless aggregate rows as nulls rather than zeroes for the Remaining Sum(1) column.
SELECT line, da, mon, inc, SUM(1) OVER(PARTITION BY mon ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM demogr WHERE yr = 94 AND mon < 13 AND da < 10 QUALIFY SUM(1) OVER(PARTITION BY mon ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) < 3; *** Query completed. 12 rows found. 5 columns returned. *** Total elapsed time was 1 second. line da mon inc Remaining Sum(1) ------ ------ ---- -------------------- ---------------- 4803 3 1 234737.37 ? 3253 2 1 ? 1 625 4 2 46706.97 ? 3853 3 4 282747.07 ? 3687 1 5 172470.52 ? 547 9 5 31848.56 1 2292 6 7 170411.66 ? 5000 8 9 40548.61 ? 3213 8 9 257858.55 1 3948 6 10 217091.30 ? 2471 1 10 121299.65 1 1496 7 12 ? ?
This example shows how to use NULLIFZERO with the ordered analytical COUNT function as a workaround to return nulls in the result instead of zeroes.
SELECT line, da, mon, inc, NULLIFZERO(COUNT(inc) OVER(PARTITION BY mon ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM demogr WHERE yr = 94 AND mon < 13 AND da < 10 QUALIFY NULLIFZERO(COUNT(inc)OVER(PARTITION BY mon ORDER BY mon, line ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) < 3; *** Query completed. 12 rows found. 5 columns returned. *** Total elapsed time was 1 second. line da mon inc Remaining Count(inc) ------ ------ ---- -------------------- -------------------- 4803 3 1 234737.37 ? 3253 2 1 ? 1 625 4 2 46706.97 ? 3853 3 4 282747.07 ? 3687 1 5 172470.52 ? 547 9 5 31848.56 1 2292 6 7 170411.66 ? 5000 8 9 40548.61 ? 3213 8 9 257858.55 1 3948 6 10 217091.30 ? 2471 1 10 121299.65 1 1496 7 12 ? ?