image-2021-12-16-19-13-21-584.png
screenshot-1.png
[MDEV-27286] Implement WITH FILL to GROUP BY Created: 2021-12-16 Updated: 2023-02-05 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | None |
| Fix Version/s: | None |
| Type: | Task | Priority: | Minor |
| Reporter: | Antonio Fernandes | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Attachments: |
image-2021-12-16-19-13-21-584.png
screenshot-1.png
|
| Description |
|
There are a lot of cases when GROUP BY does not handle "blanks" in (for example) dates. There should be an option to fetch data with zeros in GROUP BY:
Source: https://clickhouse.com/blog/en/2021/clickhouse-v21.12-released/#support-for-interval-operator-inside-with-fill-modifier-for-order-by-clause This is a very common use case when aggregating results. |
| Comments |
| Comment by Sergei Golubchik [ 2021-12-16 ] |
|
The SQL standard way would be to do a LEFT join with a table that has all dates in your interval. You can generate such a table on the fly with a recursive CTE or (in MariaDB specifically) with a sequence engine. Even if you don't know the interval in advance the above can be done, I think, with a set of CTEs, first one with your original query and then a recursive one to fill in blanks. |
| Comment by Antonio Fernandes [ 2021-12-16 ] |
|
The approach we use is indeed a LEFT JOIN with a every-date table. The approach used by Clickhouse is quite elegant and removes the need for additional tables (either materialized or generated). Removing the dates table, speeds things quite a lot: Just thought of it as very common use case |
