apache / apache/datafusion

Convert AVG(col) to SUM(x) / COUNT(*)

Open
#19,637 3 comments 0 reactions 1 assignee Claimed by @HrithikSampson View on GitHub
enhancement
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Is your feature request related to a problem or challenge?

In queries that contain sum and/or avg (on the same columns) and/or count(*), like TPCH query 1 we can convert AVG(col) to SUM(col) / COUNT(*) to avoid redundant `count` calculations for higher efficiency / reduced memory usage.

### Describe the solution you'd like

Zooming in on the avg:
```
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
```
Those accumulators will all (redundantly) compute the count.

We should be able to rewrite it to use a shared count (and thus faster accumulators / less memory):
```
sum(l_quantity) / count_order as avg_qty,
sum(l_extendedprice) / count_order as avg_price,
sum(l_discount) / count_order as avg_disc,
count_order
FROM (...sub expr...)
```

Next, sum(l_quantity) and sum(l_extendedprice) are already computed, so the CSE ruke can simplify it to only create/calculate them once!

### Describe alternatives you've considered

_No response_

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.