The Problem
When doing data analysis, how often do you need a quick look at a column’s distribution? For example: which countries do users come from? What payment methods are available? How many unique SKUs exist per product category?
The traditional approach uses GROUP BY + COUNT(*) + ORDER BY, which gets painfully slow with large datasets.
Imagine you have an e-commerce orders table with 50 million rows containing user_id, country, payment_method, amount, etc. Your boss asks you to quickly answer:
- Which countries do users come from? Roughly how many per country?
- What payment methods exist? What’s their share?
- How many unique users are there in total?
Using the traditional GROUP BY approach:
-- Traditional method: slow, memory-hungry
SELECT country, COUNT(*) as cnt
FROM orders
GROUP BY country
ORDER BY cnt DESC;
-- Time: ~45 seconds
-- Peak memory: ~2GB
The Solution
Trick One: APPROX_TOP_K — Top-K in One Line
SELECT
APPROX_TOP_K(country, 10) as top_countries,
APPROX_TOP_K(payment_method, 5) as top_payments
FROM orders;
Result:
| top_countries | top_payments |
|---|---|
{("US", 12500000), ("CN", 9800000), ("JP", 5200000), ...} | {("credit_card", 18000000), ("alipay", 12000000), ...} |
APPROX_TOP_K(column, K) leverages a HyperLogLog variant algorithm to return the K most frequent values and their counts in sub-second time. No explicit GROUP BY, no manual sorting needed.
Trick Two: APPROX_COUNT_DISTINCT — Cardinality Estimation with 95% Accuracy
SELECT
APPROX_COUNT_DISTINCT(user_id) as unique_users,
COUNT(DISTINCT user_id) as exact_users
FROM orders;
Comparison:
| unique_users | exact_users |
|---|---|
| 15234567 | 15234891 |
Error rate: just 0.002%, but 10x+ speedup.
Trick Three: Combine Them — One-Click Data Profile
SELECT
APPROX_TOP_K(country, 5) as top_countries,
APPROX_TOP_K(payment_method, 5) as top_payments,
APPROX_COUNT_DISTINCT(user_id) as unique_users,
APPROX_COUNT_DISTINCT(product_id) as unique_products
FROM orders;
One SQL query, 1.2 seconds to results.
Performance Benchmarks
| Metric | Traditional GROUP BY | APPROX_TOP_K | Improvement |
|---|---|---|---|
| Query Time | ~45 seconds | ~1.2 seconds | 37x faster |
| Memory Usage | ~2 GB | ~50 MB | 40x less |
| Code Lines | 5 lines | 1 line | 80% simpler |
When to Use (and When Not To)
APPROX_TOP_K and APPROX_COUNT_DISTINCT are built on approximate algorithms like HyperLogLog and Count-Min Sketch. They trade a small, bounded error margin (typically < 2%) for massive performance gains over full-scan-and-sort operations.
Best use cases:
- Exploratory data analysis: Quick column distribution surveys
- Dashboard pre-aggregation: Real-time monitoring panels showing Top-N
- Anomaly detection: Spotting rare categories by comparing approximate vs. exact counts
⚠️ Warning: If you need exact counts (e.g., financial reconciliation), stick with COUNT(DISTINCT ...). Approximate functions shine in exploratory analysis and scenarios where speed matters more than precision.
Subscribe to DuckDB Lab