Example: Specifying GROUP BY Using an Ordered Analytic Function - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

The following statement specifies a GROUP BY clause with an ordered analytical function to generate report breaks where the function resets and computes a new value for the next grouping.

The example groups all items into percentile by profitability for each store and then returns only the items of interest, which, in this case, are the lowest percentile for each store.

     SELECT store, item, profit, QUANTILE(100, profit) AS percentile
     FROM (SELECT items.item, SUM(sales) -
                  (COUNT(sales)*items.item_cost) AS profit
           FROM daily_sales, items
           WHERE daily_sales.item = items.item
           GROUP BY items.item,items.itemcost) AS item_profit
     GROUP BY store, item, profit, percentile
     QUALIFY percentile = 99;

The result of this query looks like the following table:

store ----- item ---- profit ------ percentile ----------
Eastside Golf balls     100.19 99
Westside Tennis balls   -110.00 99
Central Bowling balls    -986.81 99
South Codfish balls - 1,891.89 99
North Bocce balls   1,180.88 99