Skip to content

Aggregate Functions

Aggregate functions perform calculations on sets of values and return a single result.

aggregate

any_value 

any_value(col: Union[str, Column]) -> Func

Returns the ANY_VALUE aggregate SQL function for the given column name.

The ANY_VALUE function returns an arbitrary value from the specified column. It is useful when you do not care which particular value is returned, as long as it comes from one of the rows in the group.

Parameters:

  • col (str | Column) –

    The name of the column from which to return an arbitrary value. Column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object that represents the ANY_VALUE aggregate function.

Example 
dc.group_by(
    file_example=func.any_value("file.path"),
    signal_example=func.any_value(dc.C("signal.value")),
    partition_by="signal.category",
)
Notes
  • The any_value function can be used with any type of column.
  • The result column will have the same type as the input column.
  • The result of any_value is non-deterministic, meaning it may return different values for different executions.
Source code in datachain/func/aggregate.py 
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
def any_value(col: Union[str, Column]) -> Func:
    """
    Returns the ANY_VALUE aggregate SQL function for the given column name.

    The ANY_VALUE function returns an arbitrary value from the specified column.
    It is useful when you do not care which particular value is returned,
    as long as it comes from one of the rows in the group.

    Args:
        col (str | Column): The name of the column from which to return
            an arbitrary value.
            Column can be specified as a string or a `Column` object.

    Returns:
        Func: A Func object that represents the ANY_VALUE aggregate function.

    Example:
        ```py
        dc.group_by(
            file_example=func.any_value("file.path"),
            signal_example=func.any_value(dc.C("signal.value")),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The `any_value` function can be used with any type of column.
        - The result column will have the same type as the input column.
        - The result of `any_value` is non-deterministic,
          meaning it may return different values for different executions.
    """
    return Func("any_value", inner=aggregate.any_value, cols=[col])

avg 

avg(col: Union[str, Column]) -> Func

Returns the AVG aggregate SQL function for the specified column.

The AVG function returns the average of a numeric column in a table. It calculates the mean of all values in the specified column.

Parameters:

  • col (str | Column) –

    The name of the column for which to calculate the average. Column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object that represents the AVG aggregate function.

Example 
dc.group_by(
    avg_file_size=func.avg("file.size"),
    avg_signal_value=func.avg(dc.C("signal.value")),
    partition_by="signal.category",
)
Notes
  • The avg function should be used on numeric columns.
  • The result column will always be of type float.
Source code in datachain/func/aggregate.py 
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
def avg(col: Union[str, Column]) -> Func:
    """
    Returns the AVG aggregate SQL function for the specified column.

    The AVG function returns the average of a numeric column in a table.
    It calculates the mean of all values in the specified column.

    Args:
        col (str | Column): The name of the column for which to calculate the average.
            Column can be specified as a string or a `Column` object.

    Returns:
        Func: A Func object that represents the AVG aggregate function.

    Example:
        ```py
        dc.group_by(
            avg_file_size=func.avg("file.size"),
            avg_signal_value=func.avg(dc.C("signal.value")),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The `avg` function should be used on numeric columns.
        - The result column will always be of type float.
    """
    return Func("avg", inner=aggregate.avg, cols=[col], result_type=float)

collect 

collect(col: Union[str, Column]) -> Func

Returns the COLLECT aggregate SQL function for the given column name.

The COLLECT function gathers all values from the specified column into an array or similar structure. It is useful for combining values from a column into a collection, often for further processing or aggregation.

Parameters:

  • col (str | Column) –

    The name of the column from which to collect values. Column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object that represents the COLLECT aggregate function.

Example 
dc.group_by(
    signals=func.collect("signal"),
    file_paths=func.collect(dc.C("file.path")),
    partition_by="signal.category",
)
Notes
  • The collect function can be used with numeric and string columns.
  • The result column will have an array type.
Source code in datachain/func/aggregate.py 
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
def collect(col: Union[str, Column]) -> Func:
    """
    Returns the COLLECT aggregate SQL function for the given column name.

    The COLLECT function gathers all values from the specified column
    into an array or similar structure. It is useful for combining values from a column
    into a collection, often for further processing or aggregation.

    Args:
        col (str | Column): The name of the column from which to collect values.
            Column can be specified as a string or a `Column` object.

    Returns:
        Func: A Func object that represents the COLLECT aggregate function.

    Example:
        ```py
        dc.group_by(
            signals=func.collect("signal"),
            file_paths=func.collect(dc.C("file.path")),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The `collect` function can be used with numeric and string columns.
        - The result column will have an array type.
    """
    return Func("collect", inner=aggregate.collect, cols=[col], is_array=True)

concat 

concat(col: Union[str, Column], separator='') -> Func

Returns the CONCAT aggregate SQL function for the given column name.

The CONCAT function concatenates values from the specified column into a single string. It is useful for merging text values from multiple rows into a single combined value.

Parameters:

  • col (str | Column) –

    The name of the column from which to concatenate values. Column can be specified as a string or a Column object.

  • separator (str, default: '' ) –

    The separator to use between concatenated values. Defaults to an empty string.

Returns:

  • Func ( Func ) –

    A Func object that represents the CONCAT aggregate function.

Example 
dc.group_by(
    files=func.concat("file.path", separator=", "),
    signals=func.concat(dc.C("signal.name"), separator=" | "),
    partition_by="signal.category",
)
Notes
  • The concat function can be used with string columns.
  • The result column will have a string type.
Source code in datachain/func/aggregate.py 
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
def concat(col: Union[str, Column], separator="") -> Func:
    """
    Returns the CONCAT aggregate SQL function for the given column name.

    The CONCAT function concatenates values from the specified column
    into a single string. It is useful for merging text values from multiple rows
    into a single combined value.

    Args:
        col (str | Column): The name of the column from which to concatenate values.
            Column can be specified as a string or a `Column` object.
        separator (str, optional): The separator to use between concatenated values.
            Defaults to an empty string.

    Returns:
        Func: A Func object that represents the CONCAT aggregate function.

    Example:
        ```py
        dc.group_by(
            files=func.concat("file.path", separator=", "),
            signals=func.concat(dc.C("signal.name"), separator=" | "),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The `concat` function can be used with string columns.
        - The result column will have a string type.
    """

    def inner(arg):
        return aggregate.group_concat(arg, separator)

    return Func("concat", inner=inner, cols=[col], result_type=str)

count 

count(col: Optional[Union[str, Column]] = None) -> Func

Returns a COUNT aggregate SQL function for the specified column.

The COUNT function returns the number of rows, optionally filtered by a specific column.

Parameters:

  • col (str | Column, default: None ) –

    The column to count. If omitted, counts all rows. The column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object representing the COUNT aggregate function.

Example 
dc.group_by(
    count1=func.count(),
    count2=func.count("signal.id"),
    count3=func.count(dc.C("signal.category")),
    partition_by="signal.category",
)
Notes
  • The result column will always have an integer type.
Source code in datachain/func/aggregate.py 
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
def count(col: Optional[Union[str, Column]] = None) -> Func:
    """
    Returns a COUNT aggregate SQL function for the specified column.

    The COUNT function returns the number of rows, optionally filtered
    by a specific column.

    Args:
        col (str | Column, optional): The column to count.
            If omitted, counts all rows.
            The column can be specified as a string or a `Column` object.

    Returns:
        Func: A `Func` object representing the COUNT aggregate function.

    Example:
        ```py
        dc.group_by(
            count1=func.count(),
            count2=func.count("signal.id"),
            count3=func.count(dc.C("signal.category")),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The result column will always have an integer type.
    """
    return Func(
        "count",
        inner=sa_func.count,
        cols=[col] if col is not None else None,
        result_type=int,
    )

dense_rank 

dense_rank() -> Func

Returns the DENSE_RANK window function for SQL queries.

The DENSE_RANK function assigns a rank to each row within a partition of a result set, without gaps in the ranking for ties. Rows with equal values receive the same rank, but the next rank is assigned consecutively (i.e., if two rows are ranked 1, the next row will be ranked 2).

Returns:

  • Func ( Func ) –

    A Func object that represents the DENSE_RANK window function.

Example 
window = func.window(partition_by="signal.category", order_by="created_at")
dc.mutate(
    dense_rank=func.dense_rank().over(window),
)
Notes
  • The result column will always be of type int.
  • The DENSE_RANK function differs from RANK in that it does not leave gaps in the ranking for tied values.
Source code in datachain/func/aggregate.py 
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
def dense_rank() -> Func:
    """
    Returns the DENSE_RANK window function for SQL queries.

    The DENSE_RANK function assigns a rank to each row within a partition
    of a result set, without gaps in the ranking for ties. Rows with equal values
    receive the same rank, but the next rank is assigned consecutively
    (i.e., if two rows are ranked 1, the next row will be ranked 2).

    Returns:
        Func: A Func object that represents the DENSE_RANK window function.

    Example:
        ```py
        window = func.window(partition_by="signal.category", order_by="created_at")
        dc.mutate(
            dense_rank=func.dense_rank().over(window),
        )
        ```

    Notes:
        - The result column will always be of type int.
        - The DENSE_RANK function differs from RANK in that it does not leave gaps
          in the ranking for tied values.
    """
    return Func("dense_rank", inner=sa_func.dense_rank, result_type=int, is_window=True)

first 

first(col: Union[str, Column]) -> Func

Returns the FIRST_VALUE window function for SQL queries.

The FIRST_VALUE function returns the first value in an ordered set of values within a partition. The first value is determined by the specified order and can be useful for retrieving the leading value in a group of rows.

Parameters:

  • col (str | Column) –

    The name of the column from which to retrieve the first value. Column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object that represents the FIRST_VALUE window function.

Example 
window = func.window(partition_by="signal.category", order_by="created_at")
dc.mutate(
    first_file=func.first("file.path").over(window),
    first_signal=func.first(dc.C("signal.value")).over(window),
)
Note
  • The result of first_value will always reflect the value of the first row in the specified order.
  • The result column will have the same type as the input column.
Source code in datachain/func/aggregate.py 
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
def first(col: Union[str, Column]) -> Func:
    """
    Returns the FIRST_VALUE window function for SQL queries.

    The FIRST_VALUE function returns the first value in an ordered set of values
    within a partition. The first value is determined by the specified order
    and can be useful for retrieving the leading value in a group of rows.

    Args:
        col (str | Column): The name of the column from which to retrieve
            the first value.
            Column can be specified as a string or a `Column` object.

    Returns:
        Func: A Func object that represents the FIRST_VALUE window function.

    Example:
        ```py
        window = func.window(partition_by="signal.category", order_by="created_at")
        dc.mutate(
            first_file=func.first("file.path").over(window),
            first_signal=func.first(dc.C("signal.value")).over(window),
        )
        ```

    Note:
        - The result of `first_value` will always reflect the value of the first row
          in the specified order.
        - The result column will have the same type as the input column.
    """
    return Func("first", inner=sa_func.first_value, cols=[col], is_window=True)

max 

max(col: Union[str, Column]) -> Func

Returns the MAX aggregate SQL function for the given column name.

The MAX function returns the smallest value in the specified column. It can be used on both numeric and non-numeric columns to find the maximum value.

Parameters:

  • col (str | Column) –

    The name of the column for which to find the maximum value. Column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object that represents the MAX aggregate function.

Example 
dc.group_by(
    largest_file=func.max("file.size"),
    max_signal=func.max(dc.C("signal")),
    partition_by="signal.category",
)
Notes
  • The max function can be used with numeric, date, and string columns.
  • The result column will have the same type as the input column.
Source code in datachain/func/aggregate.py 
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
def max(col: Union[str, Column]) -> Func:
    """
    Returns the MAX aggregate SQL function for the given column name.

    The MAX function returns the smallest value in the specified column.
    It can be used on both numeric and non-numeric columns to find the maximum value.

    Args:
        col (str | Column): The name of the column for which to find the maximum value.
            Column can be specified as a string or a `Column` object.

    Returns:
        Func: A Func object that represents the MAX aggregate function.

    Example:
        ```py
        dc.group_by(
            largest_file=func.max("file.size"),
            max_signal=func.max(dc.C("signal")),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The `max` function can be used with numeric, date, and string columns.
        - The result column will have the same type as the input column.
    """
    return Func("max", inner=sa_func.max, cols=[col])

min 

min(col: Union[str, Column]) -> Func

Returns the MIN aggregate SQL function for the specified column.

The MIN function returns the smallest value in the specified column. It can be used on both numeric and non-numeric columns to find the minimum value.

Parameters:

  • col (str | Column) –

    The name of the column for which to find the minimum value. Column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object that represents the MIN aggregate function.

Example 
dc.group_by(
    smallest_file=func.min("file.size"),
    min_signal=func.min(dc.C("signal")),
    partition_by="signal.category",
)
Notes
  • The min function can be used with numeric, date, and string columns.
  • The result column will have the same type as the input column.
Source code in datachain/func/aggregate.py 
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
def min(col: Union[str, Column]) -> Func:
    """
    Returns the MIN aggregate SQL function for the specified column.

    The MIN function returns the smallest value in the specified column.
    It can be used on both numeric and non-numeric columns to find the minimum value.

    Args:
        col (str | Column): The name of the column for which to find the minimum value.
            Column can be specified as a string or a `Column` object.

    Returns:
        Func: A Func object that represents the MIN aggregate function.

    Example:
        ```py
        dc.group_by(
            smallest_file=func.min("file.size"),
            min_signal=func.min(dc.C("signal")),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The `min` function can be used with numeric, date, and string columns.
        - The result column will have the same type as the input column.
    """
    return Func("min", inner=sa_func.min, cols=[col])

rank 

rank() -> Func

Returns the RANK window function for SQL queries.

The RANK function assigns a rank to each row within a partition of a result set, with gaps in the ranking for ties. Rows with equal values receive the same rank, and the next rank is skipped (i.e., if two rows are ranked 1, the next row is ranked 3).

Returns:

  • Func ( Func ) –

    A Func object that represents the RANK window function.

Example 
window = func.window(partition_by="signal.category", order_by="created_at")
dc.mutate(
    rank=func.rank().over(window),
)
Notes
  • The result column will always be of type int.
  • The RANK function differs from ROW_NUMBER in that rows with the same value in the ordering column(s) receive the same rank.
Source code in datachain/func/aggregate.py 
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
def rank() -> Func:
    """
    Returns the RANK window function for SQL queries.

    The RANK function assigns a rank to each row within a partition of a result set,
    with gaps in the ranking for ties. Rows with equal values receive the same rank,
    and the next rank is skipped (i.e., if two rows are ranked 1,
    the next row is ranked 3).

    Returns:
        Func: A Func object that represents the RANK window function.

    Example:
        ```py
        window = func.window(partition_by="signal.category", order_by="created_at")
        dc.mutate(
            rank=func.rank().over(window),
        )
        ```

    Notes:
        - The result column will always be of type int.
        - The RANK function differs from ROW_NUMBER in that rows with the same value
          in the ordering column(s) receive the same rank.
    """
    return Func("rank", inner=sa_func.rank, result_type=int, is_window=True)

row_number 

row_number() -> Func

Returns the ROW_NUMBER window function for SQL queries.

The ROW_NUMBER function assigns a unique sequential integer to rows within a partition of a result set, starting from 1 for the first row in each partition. It is commonly used to generate row numbers within partitions or ordered results.

Returns:

  • Func ( Func ) –

    A Func object that represents the ROW_NUMBER window function.

Example 
window = func.window(partition_by="signal.category", order_by="created_at")
dc.mutate(
    row_number=func.row_number().over(window),
)
Note
  • The result column will always be of type int.
Source code in datachain/func/aggregate.py 
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
def row_number() -> Func:
    """
    Returns the ROW_NUMBER window function for SQL queries.

    The ROW_NUMBER function assigns a unique sequential integer to rows
    within a partition of a result set, starting from 1 for the first row
    in each partition. It is commonly used to generate row numbers within
    partitions or ordered results.

    Returns:
        Func: A Func object that represents the ROW_NUMBER window function.

    Example:
        ```py
        window = func.window(partition_by="signal.category", order_by="created_at")
        dc.mutate(
            row_number=func.row_number().over(window),
        )
        ```

    Note:
        - The result column will always be of type int.
    """
    return Func("row_number", inner=sa_func.row_number, result_type=int, is_window=True)

sum 

sum(col: Union[str, Column]) -> Func

Returns the SUM aggregate SQL function for the specified column.

The SUM function returns the total sum of a numeric column in a table. It sums up all the values for the specified column.

Parameters:

  • col (str | Column) –

    The name of the column for which to calculate the sum. The column can be specified as a string or a Column object.

Returns:

  • Func ( Func ) –

    A Func object that represents the SUM aggregate function.

Example 
dc.group_by(
    files_size=func.sum("file.size"),
    total_size=func.sum(dc.C("size")),
    partition_by="signal.category",
)
Notes
  • The sum function should be used on numeric columns.
  • The result column type will be the same as the input column type.
Source code in datachain/func/aggregate.py 
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
def sum(col: Union[str, Column]) -> Func:
    """
    Returns the SUM aggregate SQL function for the specified column.

    The SUM function returns the total sum of a numeric column in a table.
    It sums up all the values for the specified column.

    Args:
        col (str | Column): The name of the column for which to calculate the sum.
            The column can be specified as a string or a `Column` object.

    Returns:
        Func: A `Func` object that represents the SUM aggregate function.

    Example:
        ```py
        dc.group_by(
            files_size=func.sum("file.size"),
            total_size=func.sum(dc.C("size")),
            partition_by="signal.category",
        )
        ```

    Notes:
        - The `sum` function should be used on numeric columns.
        - The result column type will be the same as the input column type.
    """
    return Func("sum", inner=sa_func.sum, cols=[col])