"Which ten products sell most?" and "which routes have the most flights?" are top-N questions. Answered exactly, they need every group counted and sorted. APPROX_COUNT and APPROX_SUM, combined with APPROX_RANK in the HAVING clause, find the top groups approximately, much faster on large data, while usually returning the same top groups.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
approx_count(* | expr [, 'max_error']) approx_sum(expr [, 'max_error']) approx_rank([partition by ...] order by approx_count(*) | approx_sum(expr) desc)
APPROX_COUNT and APPROX_SUM are meant for top-N queries: APPROX_RANK in HAVING keeps the top groups, and every approximate function in the select list needs an APPROX_RANK condition of its own. With 'MAX_ERROR', they return the maximum error of the estimate instead.
Top Routes and Top Cabin
The second query finds the three routes with the most flights; the third the cabin that earned the most.
Example:
select count(distinct customer_id) as exact_customers,
approx_count_distinct(customer_id) as approx_customers,
approx_count_distinct(booking_ref) as approx_refs
from bookings;
-- the three routes with the most flights, found approximately
select route_id, approx_count(*) as flights
from flights
group by route_id
having approx_rank(order by approx_count(*) desc) <= 3;
-- the cabin that earned the most
select cabin, approx_sum(fare) as revenue
from tickets
group by cabin
having approx_rank(order by approx_sum(fare) desc) <= 1;Output:
EXACT_CUSTOMERS APPROX_CUSTOMERS APPROX_REFS
__________________ ___________________ ______________
120 119 691
ROUTE_ID FLIGHTS
___________ __________
27 90
24 90
23 90
CABIN REVENUE
___________ ____________
BUSINESS 618345.79Three routes tie at 90 flights. Business class earned the most, about 618,346.
Top N per Group
PARTITION BY inside APPROX_RANK ranks within groups: here the two busiest routes out of each of three airports.
Example:
-- the two busiest routes out of each of three airports
select r.origin, f.route_id, approx_count(*) as flights
from flights f join routes r on r.route_id = f.route_id
where r.origin in ('SIN', 'SYD', 'LHR')
group by r.origin, f.route_id
having approx_rank(partition by r.origin order by approx_count(*) desc) <= 2
order by r.origin, flights desc;Output:
ORIGIN ROUTE_ID FLIGHTS _________ ___________ __________ LHR 2 90 LHR 41 39 SIN 28 90 SIN 43 51 SYD 44 51 SYD 34 39 6 rows selected.
Things to Know
- On small data, use the exact COUNT, SUM, and RANK; the approximate versions pay off on very large tables.
- APPROX_RANK may only appear in the HAVING clause.
- Results can include ties at the boundary, as with the three routes at 90 flights.
Related Guides
Conclusion
APPROX_COUNT and APPROX_SUM with APPROX_RANK answer top-N questions approximately and fast: the most frequent or largest groups overall, or per partition. Use them on large data, and exact aggregates where every count matters.
