This example calculates the approximate percentiles 0, 25, 50, 75, and 100 within a 2% error rate for four brands of crackers.
Input
The input table is cracker, as in the Approximate Cardinality Example.
SQL Call
SELECT * FROM ApproxPercentileReduce (
ON ApproxPercentileMap (
ON cracker AS InputTable
USING
TargetColumns('pricesunshine','pricekeebler', 'pricenabisco','priceprivate')
GroupByColumns('choice')
ErrorRate(2)
) AS InputTable PARTITION BY choice
USING
GroupByColumns('choice')
Percentile(0,25,50,75,100)
TargetColumns('pricesunshine','pricekeebler', 'pricenabisco','priceprivate')
) AS dt order by choice,percentiles;
Output
choice percentiles pricesunshine pricekeebler pricenabisco priceprivate -------- ----------- ------------- ------------ ------------ ------------ keebler 0.0 49.000001 88.0 88.0 38.0 keebler 25.0 88.999999 99.000001 99.000001 58.999997 keebler 50.0 97.000003 109.0 109.0 64.999998 keebler 75.0 105.0 109.0 125.0 77.999997 keebler 100.0 129.0 135.0 129.0 95.999998 nabisco 0.0 49.000001 88.0 0.0 38.0 nabisco 25.0 88.999999 105.0 99.000001 61.000001 nabisco 50.0 97.000003 115.0 103.0 64.999998 nabisco 75.0 105.0 121.0 119.00001 77.999997 nabisco 100.0 129.0 139.0 169.00001 115.0 private 0.0 49.000001 88.0 88.999999 38.0 private 25.0 88.999999 109.0 99.000001 55.000001 private 50.0 98.000002 113.0 109.0 58.999997 private 75.0 109.0 121.0 125.0 79.000002 private 100.0 129.0 135.0 129.0 115.0 sunshine 0.0 49.000001 88.0 88.0 49.000001 sunshine 25.0 79.000002 107.00001 99.000001 61.000001 sunshine 50.0 88.999999 109.0 109.0 64.999998 sunshine 75.0 97.000003 121.0 119.00001 77.999997 sunshine 100.0 129.0 135.0 129.0 95.999998
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.