<jupyter_start><jupyter_text>Custom Functions Apart from the SQL functions that are already implemented in `dask-sql`, it is possible to add custom functions and aggregations.Have a look into [the documentation](https://dask-sql.readthedocs.io/en/latest/pages/custom.html) for more information.<jupyter_code>import numpy as np
import dask.dataframe as dd
import dask.datasets
from dask_sql.context import Context<jupyter_output><empty_output><jupyter_text>We use some generated test data for the notebook:<jupyter_code>c = Context()

df = dask.datasets.timeseries().reset_index().persist()
c.create_table("timeseries", df)<jupyter_output><empty_output><jupyter_text>As a first step, we will create a scalar function to calculate the absolute value of a column.(Please note that this can also be done via the `ABS` function in SQL):<jupyter_code># The input to the function will be a dask series
def my_abs(x):
    return x.abs()

# As SQL is a typed language, we need to specify all types 
c.register_function(my_abs, "MY_ABS", parameters=[("x", np.float64)], return_type=np.float64)<jupyter_output><empty_output><jupyter_text>We are now able to use our new function in all queries<jupyter_code>c.sql("""
    SELECT
        x, y, MY_ABS(x) AS "abs_x", MY_ABS(y) AS "abs_y"
    FROM
        "timeseries"
    WHERE
        MY_ABS(x * y) > 0.5
""").compute()<jupyter_output><empty_output><jupyter_text>Next, we will register an aggregation, which gets a column as input and returns a single value.An aggregation needs to be an instance of `dask.Aggregation` (see the [dask docu](https://docs.dask.org/en/latest/dataframe-groupby.htmlaggregate)).<jupyter_code>my_sum = dd.Aggregation("MY_SUM", lambda x: x.sum(), lambda x: x.sum())

c.register_aggregation(my_sum, "MY_SUM", [("x", np.float64)], np.float64)
c.sql("""
    SELECT
        name, MY_SUM(x) AS "my_sum"
    FROM
        "timeseries"
    GROUP BY
        name
    LIMIT 10
""").compute()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11505321844906234, 'fraction_numerical': 0.006082108464267613, 'mean_word_length': 4.857566765578635, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 22, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '94126', 'license': 'MIT', 'n_tokens_mistral': 685, 'n_tokens_neox': 660, 'n_words': 198, 'path': 'notebooks/Custom Functions.ipynb', 'repo_name': 'pawelbielski/dask-sql', 'size': '1973'}