---
layout: docu
redirect_from:
- /docs/api/python/relational_api
- /docs/clients/python/relational_api
- /docs/stable/clients/python/relational_api
- /docs/current/clients/python/relational_api
- /docs/lts/clients/python/relational_api
- /docs/preview/clients/python/relational_api
title: Relational API
tested: true
---

<!-- Generated by scripts/generate_python_relational_docs.py -->

<!-- markdownlint-disable MD001 -->



The Relational API builds a query one step at a time from Python.
A relation, a `DuckDBPyRelation`, is a piece of SQL text: the methods of the [Transformation](#transformation) and [Functions](#functions) sections return a new relation whose text extends the one they were called on.

## Lazy Evaluation

Building a relation runs nothing: DuckDB binds the text once to learn the columns and their types, and holds no data.
For example, a relation over a billion rows is built at once:

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("FROM range(1_000_000_000) t(id) SELECT id, id % 7 AS bucket")
print(rel.sql_query())
```

```text
FROM range(1_000_000_000) t(id) SELECT id, id % 7 AS bucket
```

[`sql_query()`](#sql_query) returns exactly the text that runs.
The query runs when a method of the [Output](#output) section reads the relation, and it runs again on every read, so each read sees the current data.
Printing a relation, or calling [`show()`](#show), also runs it and displays the first rows:

```python
import duckdb

duckdb_conn = duckdb.connect()
rel = duckdb_conn.sql("FROM range(1_000_000_000) t(id) SELECT id, id % 7 AS bucket")
rel.filter("bucket = 3").order("id").limit(3).show()
```

```text
┌───────┬────────┐
│  id   │ bucket │
│ int64 │ int64  │
├───────┼────────┤
│     3 │      3 │
│    10 │      3 │
│    17 │      3 │
└───────┴────────┘
```

Only a statement that reads, such as a `SELECT`, gives a lazy relation.
Any other statement passed to [`sql()`](#sql), such as `CREATE TABLE` or `INSERT`, runs at the call, and the relation holds what it returned.
The `params` list or dict is copied when the relation is created, and its values are converted each time the relation runs.
A column is also an attribute of the relation, as in `rel.price`, unless a method has its name: a column called `offset` is reached as `rel["offset"]`, since `rel.offset` is the [`offset()`](#offset) method.

To run a query once and keep its rows, call [`materialize()`](#materialize):

```python
import duckdb

duckdb_conn = duckdb.connect()
duckdb_conn.sql("CREATE TABLE items (id INTEGER)")
counted = duckdb_conn.sql("SELECT count(*) AS n FROM items")
held = counted.materialize()
duckdb_conn.sql("INSERT INTO items VALUES (1), (2)")
print(counted.fetchall(), held.fetchall())
```

```text
[(2,)] [(0,)]
```



## Relation Creation 

This section contains the details on how a relation is created. A relation over a query that reads is [lazy evaluated](#lazy-evaluation).

| Name | Description |
|:--|:-------|
| [`from_arrow`](#from_arrow) | Create a relation object from an Arrow object. |
| [`from_csv_auto`](#from_csv_auto) | Alias of [`read_csv()`](#read_csv). |
| [`from_df`](#from_df) | Create a relation object from the DataFrame in df. |
| [`from_parquet`](#from_parquet) | Alias of [`read_parquet()`](#read_parquet). |
| [`from_query`](#from_query) | Alias of [`sql()`](#sql). |
| [`query`](#query) | Alias of [`sql()`](#sql). |
| [`read_csv`](#read_csv) | Create a relation object from the CSV file in 'name'. |
| [`read_json`](#read_json) | Create a relation object from the JSON file in 'name'. |
| [`read_parquet`](#read_parquet) | Create a relation object from the Parquet path(s) or file-like object(s) in 'path_or_buffer'. |
| [`sql`](#sql) | Run a SQL query. A statement that only reads returns a relation that runs the query each time it is read, parameter values bound at every run. Any other statement runs once, now, and gives a relation over what it returned. Statements before the last one run immediately. |
| [`table`](#table) | Create a relation object for the named table. |
| [`table_function`](#table_function) | Create a relation object from the named table function with given parameters. |
| [`values`](#values) | Create a relation object from the passed values. |
| [`view`](#view) | Create a relation object for the named view. |

### `from_arrow`

```python
from_arrow(self: _duckdb.DuckDBPyConnection, arrow_object: object) -> DuckDBPyRelation
```

Create a relation object from an Arrow object.

**Parameters**

* **arrow_object** : object

    The Arrow object to create a relation from: a table, a record batch, a record batch reader, a dataset, a scanner, a polars DataFrame or an object that exports the Arrow PyCapsule interface.

**Example**

```python
import duckdb
import pyarrow as pa

ids = pa.array([1], type=pa.int8())
texts = pa.array(['a'], type=pa.string())
example_table = pa.table([ids, texts], names=["id", "text"])

duckdb_conn = duckdb.connect()

rel = duckdb_conn.from_arrow(example_table)

rel.show()
```

**Result**

```text
┌──────┬─────────┐
│  id  │  text   │
│ int8 │ varchar │
├──────┼─────────┤
│    1 │ a       │
└──────┴─────────┘
```

----

### `from_csv_auto`

```python
from_csv_auto(self: _duckdb.DuckDBPyConnection, *args: object, **kwargs: object) -> DuckDBPyRelation
```

Alias of [`read_csv()`](#read_csv).

----

### `from_df`

```python
from_df(self: _duckdb.DuckDBPyConnection, df: object) -> DuckDBPyRelation
```

Create a relation object from the DataFrame in df.

**Parameters**

* **df** : pandas.DataFrame

    A pandas DataFrame to be converted into a DuckDB relation.

**Example**

```python
import duckdb
import pandas as pd

df = pd.DataFrame(data = {'id': [1], "text":["a"]})

duckdb_conn = duckdb.connect()

rel = duckdb_conn.from_df(df)

rel.show()
```

**Result**

```text
┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘
```

----

### `from_parquet`

```python
from_parquet(self: _duckdb.DuckDBPyConnection, path_or_buffer: object, binary_as_string: bool = False, **kwargs: Any) -> DuckDBPyRelation
```

Alias of [`read_parquet()`](#read_parquet).

----

### `from_query`

```python
from_query(self: _duckdb.DuckDBPyConnection, query: object, *, alias: str = '', params: object = None) -> DuckDBPyRelation | None
```

Alias of [`sql()`](#sql).

----

### `query`

```python
query(self: _duckdb.DuckDBPyConnection, query: object, *, alias: str = '', params: object = None) -> DuckDBPyRelation | None
```

Alias of [`sql()`](#sql).

----

### `read_csv`

```python
read_csv(self: _duckdb.DuckDBPyConnection, *args: object, **kwargs: object) -> DuckDBPyRelation
```

Create a relation object from the CSV file in 'name'.

The options other than `path_or_buffer` are keyword arguments; an unknown one raises `InvalidInputException`. Most are the option of the same name of the SQL [`read_csv` function]({% link docs/2.0/data/csv/overview.md %}#parameters); `sep` and `delimiter` are `delim`, `dtype` is `dtypes`, `na_values` is `nullstr`, `skiprows` is `skip`, `quotechar` is `quote`, `escapechar` is `escape`, `date_format` is `dateformat`, `timestamp_format` is `timestampformat`, `lineterminator` is `new_line` and `decimal` is `decimal_separator`.

**Aliases**: [`from_csv_auto`](#from_csv_auto)

**Parameters**

* **path_or_buffer** : str, list[str], file-like object

    Path to the file, a glob pattern, a list of paths or a file-like object such as `io.StringIO` or `io.BytesIO`.
* **header** : bool, int, keyword-only, default: None

    Whether the first line holds the column names; `0` means `True`. Detected when omitted.
* **compression** : str, keyword-only, default: None

    Compression of the files: `'none'`, `'gzip'` or `'zstd'`. Detected from the file extension when omitted.
* **sep** : str, keyword-only, default: None

    Delimiter between the columns, up to 4 bytes; cannot be combined with `delimiter`. Detected when omitted.
* **delimiter** : str, keyword-only, default: None

    Same as `sep`.
* **dtype** : dict[str, str], list[str], keyword-only, default: None

    Column types, as a dict of column name to type or a list of types by position; a type is a type name or a `DuckDBPyType`.
* **na_values** : str, list[str], keyword-only, default: None

    Strings that are read as `NULL`.
* **skiprows** : int, keyword-only, default: None

    Number of lines to skip at the start of each file.
* **quotechar** : str, keyword-only, default: None

    Character used to quote values.
* **escapechar** : str, keyword-only, default: None

    Character that escapes the quote character inside a quoted value.
* **comment** : str, keyword-only, default: None

    Character that starts a comment: a line is read only up to it, and a line that starts with it is skipped.
* **thousands** : str, keyword-only, default: None

    Character that separates the thousands in numbers; it must differ from `decimal`.
* **encoding** : str, keyword-only, default: None

    Only UTF-8 is accepted, written `'utf-8'` or `'utf8'`; any other encoding raises `BinderException`.
* **parallel** : bool, keyword-only, default: None

    Whether to use the parallel CSV reader.
* **date_format** : str, keyword-only, default: None

    [Format]({% link docs/2.0/sql/functions/dateformat.md %}) used to parse dates.
* **timestamp_format** : str, keyword-only, default: None

    [Format]({% link docs/2.0/sql/functions/dateformat.md %}) used to parse timestamps.
* **sample_size** : int, keyword-only, default: None

    Number of lines sampled to detect the dialect and the types.
* **files_to_sniff** : int, keyword-only, default: None

    Number of files sampled to detect the schema when reading several files; `-1` samples all of them.
* **auto_detect** : bool, keyword-only, default: None

    Whether to detect the dialect, the header and the types. Detected when omitted.
* **all_varchar** : bool, keyword-only, default: None

    Read every column as `VARCHAR`, without detecting the types.
* **normalize_names** : bool, keyword-only, default: None

    Normalize the column names; for example, `My Col` becomes `my_col`.
* **null_padding** : bool, keyword-only, default: None

    Pad a line that lacks columns with `NULL` values on the right.
* **strict_mode** : bool, keyword-only, default: None

    Whether to refuse a file that is not structurally valid CSV; with `False`, the reader tries to read it anyway.
* **names** : list[str], keyword-only, default: None

    Column names, as a list.
* **lineterminator** : str, CSVLineTerminator, keyword-only, default: None

    The line ending: one of `duckdb.CSVLineTerminator.LINE_FEED`, `duckdb.CSVLineTerminator.CARRIAGE_RETURN_LINE_FEED`, `'\\n'` and `'\\r\\n'`, the last two being escape text as written in Python; a string that holds the newline character itself, such as `'\n'`, is refused.
* **columns** : dict[str, str], keyword-only, default: None

    Column names and types, as a dict of column name to type name; the types are then not detected.
* **auto_type_candidates** : list[str], keyword-only, default: None

    Types the reader may choose from when it detects the column types; `VARCHAR` is always one of them.
* **max_line_size** : int, str, keyword-only, default: None

    Maximum line size, in bytes.
* **ignore_errors** : bool, keyword-only, default: None

    Skip the lines that fail to parse.
* **store_rejects** : bool, keyword-only, default: None

    Skip the lines with errors and store them in the [rejects table]({% link docs/2.0/data/csv/reading_faulty_csv_files.md %}).
* **rejects_table** : str, keyword-only, default: None

    Name of the temporary table that holds the faulty lines.
* **rejects_scan** : str, keyword-only, default: None

    Name of the temporary table that holds information on the scans that found faulty lines.
* **rejects_limit** : int, keyword-only, default: None

    Maximum number of faulty lines stored for each file; `0` means no limit.
* **force_not_null** : list[str], keyword-only, default: None

    Columns whose values are never read as `NULL`.
* **buffer_size** : int, keyword-only, default: None

    Size of the buffers used to read the files, in bytes.
* **decimal** : str, keyword-only, default: None

    Decimal separator of numbers.
* **allow_quoted_nulls** : bool, keyword-only, default: None

    Whether a quoted value can be read as `NULL`.
* **filename** : bool, keyword-only, default: None

    Add a column `filename` with the path of the file each row came from.
* **hive_partitioning** : bool, keyword-only, default: None

    Interpret the path as a [Hive partitioned path]({% link docs/2.0/data/partitioning/hive_partitioning.md %}).
* **union_by_name** : bool, keyword-only, default: None

    Align the columns of several files by name instead of by position.
* **hive_types** : dict[str, str], keyword-only, default: None

    Types of the Hive partition columns, as a dict of column name to type name.
* **hive_types_autocast** : bool, keyword-only, default: None

    Whether to detect the types of the Hive partition columns.

**Example**

```python
import csv
import duckdb

duckdb_conn = duckdb.connect()

with open('code_example.csv', 'w', newline='') as csvfile:
    fieldnames = ['id', 'text']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerow({'id': '1', 'text': 'a'})

rel = duckdb_conn.read_csv("code_example.csv")

rel.show()
```

**Result**

```text
┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘
```

----

### `read_json`

```python
read_json(self: _duckdb.DuckDBPyConnection, path_or_buffer: object, **kwargs: Any) -> DuckDBPyRelation
```

Create a relation object from the JSON file in 'name'.

The options other than `path_or_buffer` are keyword arguments; an unknown one raises `TypeError`. Each is the option of the same name of the SQL [`read_json` function]({% link docs/2.0/data/json/loading_json.md %}).

**Parameters**

* **path_or_buffer** : str, list[str], file-like object

    Path to the file, a glob pattern, a list of paths or a file-like object such as `io.StringIO` or `io.BytesIO`.
* **columns** : dict[str, str], keyword-only, default: None

    Key names and types, as a dict of key name to type name; the schema is then not detected.
* **sample_size** : int, keyword-only, default: None

    Number of objects sampled to detect the types; `-1` samples all of them.
* **maximum_depth** : int, keyword-only, default: None

    Maximum nesting depth to which the types are detected; `-1` detects all levels.
* **records** : str, keyword-only, default: None

    `'true'` to read each object as a row of columns, `'false'` to read each as one value, `'auto'` to detect it.
* **format** : str, keyword-only, default: None

    `'auto'`, `'unstructured'`, `'newline_delimited'` or `'array'`.
* **date_format** : str, keyword-only, default: None

    [Format]({% link docs/2.0/sql/functions/dateformat.md %}) used to parse dates.
* **timestamp_format** : str, keyword-only, default: None

    [Format]({% link docs/2.0/sql/functions/dateformat.md %}) used to parse timestamps.
* **compression** : str, keyword-only, default: None

    Compression of the files: `'none'`, `'gzip'`, `'zstd'` or `'auto_detect'`. Detected from the file extension when omitted.
* **maximum_object_size** : int, keyword-only, default: None

    Maximum size of a JSON object, in bytes.
* **ignore_errors** : bool, keyword-only, default: None

    Read the objects that fail to parse as `NULL` rows; only with `format='newline_delimited'`.
* **convert_strings_to_integers** : bool, keyword-only, default: None

    Whether strings that hold integers are read as numbers.
* **field_appearance_threshold** : float, keyword-only, default: None

    When the fields of an object appear, on average, in a smaller fraction of the sampled objects than this, the object is read as a `MAP`.
* **map_inference_threshold** : int, keyword-only, default: None

    An object with more fields than this is read as a `MAP` instead of a `STRUCT`; `-1` never reads a `MAP`.
* **maximum_sample_files** : int, keyword-only, default: None

    Maximum number of files sampled to detect the schema.
* **filename** : bool, keyword-only, default: None

    Add a column `filename` with the path of the file each row came from.
* **hive_partitioning** : bool, keyword-only, default: None

    Interpret the path as a [Hive partitioned path]({% link docs/2.0/data/partitioning/hive_partitioning.md %}).
* **union_by_name** : bool, keyword-only, default: None

    Align the columns of several files by name instead of by position.
* **hive_types** : dict[str, str], keyword-only, default: None

    Types of the Hive partition columns, as a dict of column name to type name.
* **hive_types_autocast** : bool, keyword-only, default: None

    Whether to detect the types of the Hive partition columns.

**Example**

```python
import duckdb
import json

with open("code_example.json", mode="w") as f:
    json.dump([{'id': 1, "text":"a"}], f)

duckdb_conn = duckdb.connect()

rel = duckdb_conn.read_json("code_example.json")

rel.show()
```

**Result**

```text
┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘
```

----

### `read_parquet`

```python
read_parquet(self: _duckdb.DuckDBPyConnection, path_or_buffer: object, binary_as_string: bool = False, **kwargs: Any) -> DuckDBPyRelation
```

Create a relation object from the Parquet path(s) or file-like object(s) in 'path_or_buffer'.

Each option is the option of the same name of the SQL [`read_parquet` function]({% link docs/2.0/data/parquet/overview.md %}).

**Aliases**: [`from_parquet`](#from_parquet)

**Parameters**

* **path_or_buffer** : str, list[str], file-like object

    Path to the Parquet file, a glob pattern, a list of paths or a file object opened in binary mode.
* **binary_as_string** : bool, default: False

    Read binary columns as strings, for files from writers that do not mark their strings as UTF-8.
* **file_row_number** : bool, keyword-only, default: False

    Add a column `file_row_number` with the number of each row within its file.
* **filename** : bool, keyword-only, default: False

    Add a column `filename` with the path of the file each row came from.
* **hive_partitioning** : bool, keyword-only, default: False

    Interpret the path as a [Hive partitioned path]({% link docs/2.0/data/partitioning/hive_partitioning.md %}).
* **union_by_name** : bool, keyword-only, default: False

    Align the columns of several files by name instead of by position.
* **compression** : str, keyword-only, default: None

    Accepted and ignored: a Parquet file records its own compression.

**Example**

```python
import duckdb
import pyarrow as pa
import pyarrow.parquet as pq

ids = pa.array([1], type=pa.int8())
texts = pa.array(['a'], type=pa.string())
example_table = pa.table([ids, texts], names=["id", "text"])

pq.write_table(example_table, "code_example.parquet")

duckdb_conn = duckdb.connect()

rel = duckdb_conn.read_parquet("code_example.parquet")

rel.show()
```

**Result**

```text
┌──────┬─────────┐
│  id  │  text   │
│ int8 │ varchar │
├──────┼─────────┤
│    1 │ a       │
└──────┴─────────┘
```

----

### `sql`

```python
sql(self: _duckdb.DuckDBPyConnection, query: object, *, alias: str = '', params: object = None) -> DuckDBPyRelation | None
```

Run a SQL query. A statement that only reads returns a relation that runs the query each time it is read, parameter values bound at every run. Any other statement runs once, now, and gives a relation over what it returned. Statements before the last one run immediately.

**Aliases**: [`from_query`](#from_query), [`query`](#query)

**Parameters**

* **query** : str

    The SQL text; when it holds several statements, the ones before the last run at the call.
* **alias** : str, keyword-only, default: ''

    Alias of the relation.
* **params** : list, dict, keyword-only, default: None

    Values of the query parameters: a list for `?` and `$1` markers, a dict for `$name` markers. The list or dict is copied at the call, as is any list, tuple or dict inside it; the values in it are converted each time the relation runs, so a `bytearray` changed in place afterwards is read changed.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("SELECT * FROM range(1, 4) tbl(id) WHERE id > $minimum", params={"minimum": 1})

rel.show()
```

**Result**

```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     2 │
│     3 │
└───────┘
```

----

### `table`

```python
table(self: _duckdb.DuckDBPyConnection, table_name: str) -> DuckDBPyRelation
```

Create a relation object for the named table.

**Parameters**

* **table_name** : str

    Name of the table to create a relation from.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE code_example AS SELECT * FROM range(1, 2) tbl(id)")

rel = duckdb_conn.table("code_example")

rel.show()
```

**Result**

```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     1 │
└───────┘
```

----

### `table_function`

```python
table_function(self: _duckdb.DuckDBPyConnection, name: str, parameters: object = None) -> DuckDBPyRelation
```

Create a relation object from the named table function with given parameters.

**Parameters**

* **name** : str

    Name of the table function to call.
* **parameters** : list, default: None

    The arguments to pass to the table function, as a list.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("""
    CREATE MACRO get_record_for(x) AS TABLE
    SELECT x * range AS result FROM range(1, 2)
""")

rel = duckdb_conn.table_function(name="get_record_for", parameters=[1])

rel.show()
```

**Result**

```text
┌────────┐
│ result │
│ int64  │
├────────┤
│      1 │
└────────┘
```

----

### `values`

```python
values(self: _duckdb.DuckDBPyConnection, *args: object) -> DuckDBPyRelation
```

Create a relation object from the passed values.

Python values are converted once, at the call, so a later change to the Python objects does not change the relation. Such a relation reads rows held in this process, which [`create_view()`](#create_view) refuses.

**Parameters**

* **\*args** : list, tuple, object

    The rows: one list holding the values of one row, several tuples for several rows or the values of one row as separate arguments. A value is a Python value or an Expression.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.values([1, 'a'])

rel.show()
```

**Result**

```text
┌───────┬─────────┐
│ col0  │  col1   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ a       │
└───────┴─────────┘
```

----

### `view`

```python
view(self: _duckdb.DuckDBPyConnection, view_name: str) -> DuckDBPyRelation
```

Create a relation object for the named view.

**Parameters**

* **view_name** : str

    Name of the view to create a relation from.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE VIEW code_example AS SELECT * FROM range(1, 2) tbl(id)")

rel = duckdb_conn.view("code_example")

rel.show()
```

**Result**

```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     1 │
└───────┘
```

## Relation Definition Details 

This section contains the details on how to inspect a relation.

| Name | Description |
|:--|:-------|
| [`alias`](#alias) | Get the name of the current alias. |
| [`columns`](#columns) | Return a list containing the names of the columns of the relation. |
| [`describe`](#describe) | Gives basic statistics (e.g., min, max) and if NULL exists for each column of the relation. |
| [`description`](#description) | Return the description of the result. |
| [`dtypes`](#dtypes) | Alias of [`types`](#types). |
| [`explain`](#explain) | The plan the engine runs for this relation, as text; under Jupyter the default is shown as HTML. |
| [`preview`](#preview) | The first `limit` rows drawn as a table, with the footer `__repr__` prints for the relation. |
| [`query`](#query-1) | Run the given SQL query in sql_query on the view named virtual_table_name that refers to the relation object. |
| [`set_alias`](#set_alias) | Rename the relation object to new alias. |
| [`shape`](#shape) | Tuple of # of rows, # of columns in relation. |
| [`show`](#show) | Display a summary of the data. |
| [`sql_query`](#sql_query) | Get the SQL query that is equivalent to the relation; values given as `params=` stay markers. |
| [`type`](#type) | Get the type of the relation. |
| [`types`](#types) | Return a list containing the types of the columns of the relation. |

### `alias`

Get the name of the current alias.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.set_alias("my_relation").alias
```

**Result**

```text
'my_relation'
```

----

### `columns`

Return a list containing the names of the columns of the relation.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.columns
```

**Result**

```text
['id', 'item', 'category', 'price', 'in_stock']
```

----

### `describe`

```python
describe(self) -> DuckDBPyRelation
```

Gives basic statistics (e.g., min, max) and if NULL exists for each column of the relation.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.describe()
```

**Result**

```text
┌─────────┬────────────────────┬─────────┬──────────┬───────────────────┬──────────┐
│  aggr   │         id         │  item   │ category │       price       │ in_stock │
│ varchar │       double       │ varchar │ varchar  │      double       │ varchar  │
├─────────┼────────────────────┼─────────┼──────────┼───────────────────┼──────────┤
│ count   │                6.0 │ 6       │ 6        │               6.0 │ 6        │
│ mean    │                3.5 │ NULL    │ NULL     │              38.5 │ NULL     │
│ stddev  │ 1.8708286933869707 │ NULL    │ NULL     │ 33.25507480069771 │ NULL     │
│ min     │                1.0 │ drill   │ clothing │               7.0 │ false    │
│ max     │                6.0 │ t-shirt │ tools    │              89.0 │ true     │
│ median  │                3.5 │ NULL    │ NULL     │              31.0 │ NULL     │
└─────────┴────────────────────┴─────────┴──────────┴───────────────────┴──────────┘
```

----

### `description`

Return the description of the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.description
```

**Result**

```text
[('id', INTEGER, None, None, None, None, None), ('item', VARCHAR, None, None, None, None, None), ('category', VARCHAR, None, None, None, None, None), ('price', INTEGER, None, None, None, None, None), ('in_stock', BOOLEAN, None, None, None, None, None)]
```

----

### `dtypes`

Alias of [`types`](#types).

----

### `explain`

```python
explain(self, type: object = ExplainType.STANDARD, format: str | None = None, *, analyze: bool = False) -> str
```

The plan the engine runs for this relation, as text; under Jupyter the default is shown as HTML.

With `analyze=True` or with `type` set to `'analyze'` or `duckdb.ExplainType.ANALYZE`, the query runs and the plan shows the rows and the time of each operator, as [`EXPLAIN ANALYZE`]({% link docs/2.0/guides/meta/explain_analyze.md %}) does.

**Parameters**

* **type** : str, ExplainType, default: ExplainType.STANDARD

    `'standard'` or `duckdb.ExplainType.STANDARD` for the plan, `'analyze'` or `duckdb.ExplainType.ANALYZE` to run the query and profile it.
* **format** : str, default: None

    The output format, such as `'text'`, `'json'`, `'html'`, `'graphviz'` or `'yaml'`. The default draws the plan as text, and as HTML under Jupyter.
* **analyze** : bool, keyword-only, default: False

    Run the query and profile it, as `type='analyze'` does.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

print(rel.explain())
```

**Result**

```text
╭─ Column Data Scan ─╮
│ ~6 rows            │
╰────────────────────╯
```

----

### `preview`

```python
preview(self, limit: int = 10) -> str
```

The first `limit` rows drawn as a table, with the footer `__repr__` prints for the relation.

**Parameters**

* **limit** : int, default: 10

    The number of rows to draw.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

print(rel.preview(3))
```

**Result**

```text
┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     1 │ jeans   │ clothing │    20 │ true     │
│     2 │ t-shirt │ clothing │     8 │ false    │
│     3 │ jacket  │ clothing │    65 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘
```

----

### `query`

```python
query(self, virtual_table_name: str, sql_query: str) -> DuckDBPyRelation | None
```

Run the given SQL query in sql_query on the view named virtual_table_name that refers to the relation object.

The name is this query's own: it is a common table expression over the relation, and a catalog entry for the length of the call, so a table function or a query that defines the name itself can bind against it. Nothing of it is left afterwards. A statement that does not only read, which includes a SELECT whose common table expression writes, runs at the call and the relation holds what it returned.

**Parameters**

* **virtual_table_name** : str

    The name under which `sql_query` reads the relation.
* **sql_query** : str

    The SQL query, a single statement, that reads the relation by the name `virtual_table_name`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.query(virtual_table_name="items", sql_query="SELECT max(price) AS max_price FROM items")
```

**Result**

```text
┌───────────┐
│ max_price │
│   int32   │
├───────────┤
│        89 │
└───────────┘
```

----

### `set_alias`

```python
set_alias(self, alias: str) -> DuckDBPyRelation
```

Rename the relation object to new alias.

**Parameters**

* **alias** : str

    The alias name to assign to the relation.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.set_alias("items").select("items.item, items.price")
```

**Result**

```text
┌─────────────┬───────┐
│    item     │ price │
│   varchar   │ int32 │
├─────────────┼───────┤
│ jeans       │    20 │
│ t-shirt     │     8 │
│ jacket      │    65 │
│ hammer      │    42 │
│ screwdriver │     7 │
│ drill       │    89 │
└─────────────┴───────┘
```

----

### `shape`

Tuple of # of rows, # of columns in relation.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.shape
```

**Result**

```text
(6, 5)
```

----

### `show`

```python
show(self, *, max_width: int | None = None, max_rows: int | None = None, max_col_width: int | None = None, null_value: str | None = None, render_mode: object = None) -> None
```

Display a summary of the data.

**Parameters**

* **max_width** : int, keyword-only, default: None

    Maximum display width for the entire output in characters.
* **max_rows** : int, keyword-only, default: None

    Maximum number of rows to display.
* **max_col_width** : int, keyword-only, default: None

    Maximum number of characters to display per column.
* **null_value** : str, keyword-only, default: None

    String to display in place of NULL values.
* **render_mode** : RenderMode, keyword-only, default: None

    `duckdb.RenderMode.ROWS` to draw a row per line, `duckdb.RenderMode.COLUMNS` to draw a column per line.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.show()
```

**Result**

```text
┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     1 │ jeans       │ clothing │    20 │ true     │
│     2 │ t-shirt     │ clothing │     8 │ false    │
│     3 │ jacket      │ clothing │    65 │ true     │
│     4 │ hammer      │ tools    │    42 │ true     │
│     5 │ screwdriver │ tools    │     7 │ true     │
│     6 │ drill       │ tools    │    89 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘
```

----

### `sql_query`

```python
sql_query(self) -> str
```

Get the SQL query that is equivalent to the relation; values given as `params=` stay markers.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

print(rel.sql_query())
```

**Result**

```sql
SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
```

----

### `type`

Get the type of the relation.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.type
```

**Result**

```text
'QUERY_RELATION'
```

----

### `types`

Return a list containing the types of the columns of the relation.

**Aliases**: [`dtypes`](#dtypes)

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.types
```

**Result**

```text
[INTEGER, VARCHAR, VARCHAR, INTEGER, BOOLEAN]
```

## Transformation 

This section contains the methods which can be used to chain queries. They build a new query from the relation and run nothing.

| Name | Description |
|:--|:-------|
| [`aggregate`](#aggregate) | Compute the aggregate aggr_expr by the optional groups group_expr on the relation. |
| [`apply`](#apply) | Compute the function of a single column or a list of columns by the optional groups on the relation. |
| [`cross`](#cross) | Create cross/cartesian product of two relational objects. |
| [`distinct`](#distinct) | Retrieve distinct rows from this relation object, or the first row of each distinct value of `on`. |
| [`drop`](#drop) | Remove columns. |
| [`except_`](#except_) | Create the set except of this relation object with another relation object in other_rel. |
| [`filter`](#filter) | Filter the relation object by the filter in filter_expr. |
| [`group_by`](#group_by) | Begin a grouped aggregation; continue with `.agg(...)`. |
| [`head`](#head) | The first `n` rows. |
| [`intersect`](#intersect) | Create the set intersection of this relation object with another relation object in other_rel. |
| [`join`](#join) | Join the relation object with another relation object in other_rel. |
| [`limit`](#limit) | Only retrieve the first n rows from this relation object, starting at offset. |
| [`map`](#map) | Calls the passed function on the relation. |
| [`offset`](#offset) | Skip `n` rows. |
| [`order`](#order) | Reorder the relation object by order_expr, which is SQL order text or an expression. |
| [`pivot`](#pivot) | Turn the given values of `column` into columns holding `aggregate`; the values are explicit. |
| [`project`](#project) | Alias of [`select()`](#select). |
| [`rename`](#rename) | Rename columns, given as old=new. |
| [`sample`](#sample) | A random subset: `n` rows, or `percent` of them, with `seed` repeating a draw. |
| [`select`](#select) | Project the relation object by the projection in project_expr. |
| [`select_dtypes`](#select_dtypes) | Alias of [`select_types()`](#select_types). |
| [`select_types`](#select_types) | Select columns from the relation, by filtering based on type(s). |
| [`sort`](#sort) | Reorder the relation object by the provided expressions. |
| [`union`](#union) | Create the set union of this relation object with another relation object in other_rel. |
| [`union_by_name`](#union_by_name) | Rows from both relations, matching columns by name; keeps duplicates unless `all` is false. |
| [`unnest`](#unnest) | Expand list columns to one row per element. |
| [`unpivot`](#unpivot) | Turn the named columns into rows; `name` and `value` name the two new columns. |
| [`with_columns`](#with_columns) | Add columns, replacing any of the same name in place. |

### `aggregate`

```python
aggregate(self, aggr_expr: object, group_expr: object = '') -> DuckDBPyRelation
```

Compute the aggregate aggr_expr by the optional groups group_expr on the relation.

**Parameters**

* **aggr_expr** : str, list[Expression]

    The columns and aggregates to compute: SQL text, or a list of Expressions, in which a string is a column name.
* **group_expr** : str, Expression, None, default: ''

    The columns to group by, as SQL text or an Expression. `''` or `None` groups by every column of `aggr_expr` that is not an aggregate (`GROUP BY ALL`).

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.aggregate("category, max(price) AS max_price").order("category")
```

**Result**

```text
┌──────────┬───────────┐
│ category │ max_price │
│ varchar  │   int32   │
├──────────┼───────────┤
│ clothing │        65 │
│ tools    │        89 │
└──────────┴───────────┘
```

----

### `apply`

```python
apply(self, function_name: str, function_aggr: str, group_expr: str = '', function_parameter: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Compute the function of a single column or a list of columns by the optional groups on the relation.

**Parameters**

* **function_name** : str

    Name of the function to apply over the relation.
* **function_aggr** : str

    The list of columns to apply the function over.
* **group_expr** : str, default: ''

    Optional SQL expression for grouping.
* **function_parameter** : str, default: ''

    Optional parameters to pass into the function.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.apply("max", "price", group_expr="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────┐
│ category │ max(price) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │         65 │
│ tools    │         89 │
└──────────┴────────────┘
```

----

### `cross`

```python
cross(self, other_rel: DuckDBPyRelation) -> DuckDBPyRelation
```

Create cross/cartesian product of two relational objects.

**Parameters**

* **other_rel** : DuckDBPyRelation

    Another relation to perform a cross product with.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

sizes = duckdb_conn.sql("SELECT * FROM (VALUES ('S'), ('M')) AS sizes(size)")

rel.filter("category = 'clothing'").select("item").cross(sizes).order("item, size")
```

**Result**

```text
┌─────────┬─────────┐
│  item   │  size   │
│ varchar │ varchar │
├─────────┼─────────┤
│ jacket  │ M       │
│ jacket  │ S       │
│ jeans   │ M       │
│ jeans   │ S       │
│ t-shirt │ M       │
│ t-shirt │ S       │
└─────────┴─────────┘
```

----

### `distinct`

```python
distinct(self, on: object = None) -> DuckDBPyRelation
```

Retrieve distinct rows from this relation object, or the first row of each distinct value of `on`.

**Parameters**

* **on** : str, Expression, list, default: None

    A column name, an Expression or a list of them: keep one row for each distinct value of these columns, as SQL `DISTINCT ON` does. Omitted, only fully duplicate rows are removed.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select("category").distinct().order("category")
```

**Result**

```text
┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ tools    │
└──────────┘
```

----

### `drop`

```python
drop(self, *columns: str) -> DuckDBPyRelation
```

Remove columns.

**Parameters**

* **\*columns** : str

    Names of the columns to remove; a name the relation does not have raises `BinderException`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.drop("id", "in_stock")
```

**Result**

```text
┌─────────────┬──────────┬───────┐
│    item     │ category │ price │
│   varchar   │ varchar  │ int32 │
├─────────────┼──────────┼───────┤
│ jeans       │ clothing │    20 │
│ t-shirt     │ clothing │     8 │
│ jacket      │ clothing │    65 │
│ hammer      │ tools    │    42 │
│ screwdriver │ tools    │     7 │
│ drill       │ tools    │    89 │
└─────────────┴──────────┴───────┘
```

----

### `except_`

```python
except_(self, other_rel: DuckDBPyRelation) -> DuckDBPyRelation
```

Create the set except of this relation object with another relation object in other_rel.

The difference is `EXCEPT ALL`, so duplicates count: a row that appears three times in this relation and once in `other_rel` appears twice in the result.

**Parameters**

* **other_rel** : DuckDBPyRelation

    The relation to combine with this one; it must have the same number of columns.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

categories = rel.select("category")
expensive = rel.filter("price > 30").select("category")

categories.except_(expensive).order("category")
```

**Result**

```text
┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ clothing │
│ tools    │
└──────────┘
```

----

### `filter`

```python
filter(self, filter_expr: object) -> DuckDBPyRelation
```

Filter the relation object by the filter in filter_expr.

**Parameters**

* **filter_expr** : str, Expression

    The filter expression to apply over the relation.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.filter("price > 30")
```

**Result**

```text
┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     3 │ jacket  │ clothing │    65 │ true     │
│     4 │ hammer  │ tools    │    42 │ true     │
│     6 │ drill   │ tools    │    89 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘
```

----

### `group_by`

```python
group_by(self, *columns: object) -> GroupedRelation
```

Begin a grouped aggregation; continue with `.agg(...)`.

`group_by()` returns a grouped relation whose only method is `agg(*aggregates)`. `agg()` reads its arguments as [`aggregate()`](#aggregate) reads `aggr_expr`: one string is SQL text, which may list several aggregates; among several arguments, a string is a column name and an Expression is an aggregate. It returns a relation whose first columns are the group columns.

**Parameters**

* **\*columns** : str, Expression

    The columns to group by: column names or Expressions.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.group_by("category").agg("sum(price) AS total, count(*) AS n").order("category")
```

**Result**

```text
┌──────────┬────────┬───────┐
│ category │ total  │   n   │
│ varchar  │ int128 │ int64 │
├──────────┼────────┼───────┤
│ clothing │     93 │     3 │
│ tools    │    138 │     3 │
└──────────┴────────┴───────┘
```

----

### `head`

```python
head(self, n: int = 5) -> DuckDBPyRelation
```

The first `n` rows.

**Parameters**

* **n** : int, default: 5

    The number of rows to keep.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.head(3)
```

**Result**

```text
┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     1 │ jeans   │ clothing │    20 │ true     │
│     2 │ t-shirt │ clothing │     8 │ false    │
│     3 │ jacket  │ clothing │    65 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘
```

----

### `intersect`

```python
intersect(self, other_rel: DuckDBPyRelation) -> DuckDBPyRelation
```

Create the set intersection of this relation object with another relation object in other_rel.

The intersection is `INTERSECT ALL`, so duplicates count: a row that appears three times in this relation and twice in `other_rel` appears twice in the result.

**Parameters**

* **other_rel** : DuckDBPyRelation

    The relation to combine with this one; it must have the same number of columns.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

categories = rel.select("category")
expensive = rel.filter("price > 30").select("category")

categories.intersect(expensive).order("category")
```

**Result**

```text
┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ tools    │
│ tools    │
└──────────┘
```

----

### `join`

```python
join(self, other_rel: object, condition: object = None, how: str = 'inner', suffix: str | None = None) -> DuckDBPyRelation
```

Join the relation object with another relation object in other_rel.

The condition is SQL text, a column name or a list of them for a USING join, an Expression, or a function of the two sides. Types supported are 'inner', 'left', 'right', 'outer', 'full', 'semi', 'anti', 'cross', 'positional', 'natural' and 'asof'. With `suffix`, a column of the right side whose name the left side already has is renamed with the suffix appended.

An `'inner'`, `'left'`, `'right'`, `'outer'`, `'full'`, `'semi'`, `'anti'` or `'asof'` join needs a `condition`, and raises `InvalidInputException` without one; a `'cross'`, `'positional'` or `'natural'` join takes none. The [joins section of the `FROM` clause]({% link docs/2.0/sql/query_syntax/from.md %}#joins) says what each join type does. A `CROSS` join is also provided through the [`cross()`](#cross) method.

**Parameters**

* **other_rel** : DuckDBPyRelation

    The relation to join with the current relation.
* **condition** : str, list[str], Expression, Callable, default: None

    The join condition: SQL text such as `'a.id = b.id'`, a column name or a list of column names for a `USING` join, an Expression or a function that takes the two sides and returns an Expression, where `side['id']` is the column `id` of that side.
* **how** : str, default: 'inner'

    The type of join: `'inner'`, `'left'`, `'right'`, `'outer'`, `'full'`, `'semi'`, `'anti'`, `'cross'`, `'positional'`, `'natural'` or `'asof'`.
* **suffix** : str, default: None

    Rename each column of the right side whose name the left side also has, by appending the suffix; the columns of a `USING` or natural join, which appear once, keep their names.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

left = duckdb_conn.sql("SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS t(id, name)")
right = duckdb_conn.sql("SELECT * FROM (VALUES (1, 'x'), (3, 'z'), (4, 'w')) AS t(id, name)")

print(left.join(right, "id").order("id"))

left.join(right, lambda a, b: a["id"] == b["id"], how="left", suffix="_right").order("id")
```

**Result**

```text
┌───────┬─────────┬─────────┐
│  id   │  name   │  name   │
│ int32 │ varchar │ varchar │
├───────┼─────────┼─────────┤
│     1 │ a       │ x       │
│     3 │ c       │ z       │
└───────┴─────────┴─────────┘

┌───────┬─────────┬──────────┬────────────┐
│  id   │  name   │ id_right │ name_right │
│ int32 │ varchar │  int32   │  varchar   │
├───────┼─────────┼──────────┼────────────┤
│     1 │ a       │        1 │ x          │
│     2 │ b       │     NULL │ NULL       │
│     3 │ c       │        3 │ z          │
└───────┴─────────┴──────────┴────────────┘
```

----

### `limit`

```python
limit(self, n: int, offset: int = 0) -> DuckDBPyRelation
```

Only retrieve the first n rows from this relation object, starting at offset.

To read the first rows into Python, as `fetchone()` and `fetchmany()` did on a relation before DuckDB 2.0, use `rel.limit(1).fetchall()` and `rel.limit(n).fetchall()`. To read all the rows a batch at a time, use [`iter_rows()`](#iter_rows).

**Parameters**

* **n** : int

    The maximum number of rows to return.
* **offset** : int, default: 0

    The number of rows to skip before starting to return rows.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.limit(1)
```

**Result**

```text
┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     1 │ jeans   │ clothing │    20 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘
```

----

### `map`

```python
map(self, map_function: Callable[..., Any], *, schema: object = None) -> DuckDBPyRelation
```

Calls the passed function on the relation.

**Parameters**

* **map_function** : Callable

    A Python function that takes a DataFrame and returns a transformed DataFrame.
* **schema** : dict, keyword-only, default: None

    The columns of the output relation, as a dict of name to type. Without it, the function is called once on an empty DataFrame when the relation is built, to learn the columns it returns.

**Example**

```python
import duckdb
from pandas import DataFrame

def multiply_by_2(df: DataFrame):
    df["id"] = df["id"] * 2
    return df

duckdb_conn = duckdb.connect()
rel = duckdb_conn.sql("SELECT range AS id, 'dummy' AS text FROM range(1, 3)")

rel.map(multiply_by_2, schema={"id": int, "text": str})
```

**Result**

```text
┌───────┬─────────┐
│  id   │  text   │
│ int64 │ varchar │
├───────┼─────────┤
│     2 │ dummy   │
│     4 │ dummy   │
└───────┴─────────┘
```

----

### `offset`

```python
offset(self, n: int) -> DuckDBPyRelation
```

Skip `n` rows.

**Parameters**

* **n** : int

    The number of rows to skip.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.order("id").offset(4)
```

**Result**

```text
┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     5 │ screwdriver │ tools    │     7 │ true     │
│     6 │ drill       │ tools    │    89 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘
```

----

### `order`

```python
order(self, order_expr: object) -> DuckDBPyRelation
```

Reorder the relation object by order_expr, which is SQL order text or an expression.

**Parameters**

* **order_expr** : str, Expression

    The ordering of the rows: SQL text such as `'price DESC'`, or an Expression such as `col('price').desc()`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.order("price DESC")
```

**Result**

```text
┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     6 │ drill       │ tools    │    89 │ true     │
│     3 │ jacket      │ clothing │    65 │ true     │
│     4 │ hammer      │ tools    │    42 │ true     │
│     1 │ jeans       │ clothing │    20 │ true     │
│     2 │ t-shirt     │ clothing │     8 │ false    │
│     5 │ screwdriver │ tools    │     7 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘
```

----

### `pivot`

```python
pivot(self, column: object, values: Iterable[object], aggregate: object) -> DuckDBPyRelation
```

Turn the given values of `column` into columns holding `aggregate`; the values are explicit.

It runs a [`PIVOT`]({% link docs/2.0/sql/statements/pivot.md %}) with the values given; the columns not named in `column` or `aggregate` are the rows of the result.

**Parameters**

* **column** : str, Expression

    The column whose values become the new columns.
* **values** : Iterable

    The values of `column` that become columns, as Python values.
* **aggregate** : str, Expression

    The aggregate each new column holds, as SQL text or an Expression.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select("in_stock, category, price").pivot("category", ["clothing", "tools"], "sum(price)").order("in_stock")
```

**Result**

```text
┌──────────┬──────────┬────────┐
│ in_stock │ clothing │ tools  │
│ boolean  │  int128  │ int128 │
├──────────┼──────────┼────────┤
│ false    │        8 │   NULL │
│ true     │       85 │    138 │
└──────────┴──────────┴────────┘
```

----

### `project`

```python
project(self, *args: object, groups: str = '') -> DuckDBPyRelation
```

Alias of [`select()`](#select).

----

### `rename`

```python
rename(self, **columns: str) -> DuckDBPyRelation
```

Rename columns, given as old=new.

**Parameters**

* **\*\*columns** : str

    The columns to rename, as `old=new`; a name the relation does not have raises `BinderException`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.rename(price="unit_price")
```

**Result**

```text
┌───────┬─────────────┬──────────┬────────────┬──────────┐
│  id   │    item     │ category │ unit_price │ in_stock │
│ int32 │   varchar   │ varchar  │   int32    │ boolean  │
├───────┼─────────────┼──────────┼────────────┼──────────┤
│     1 │ jeans       │ clothing │         20 │ true     │
│     2 │ t-shirt     │ clothing │          8 │ false    │
│     3 │ jacket      │ clothing │         65 │ true     │
│     4 │ hammer      │ tools    │         42 │ true     │
│     5 │ screwdriver │ tools    │          7 │ true     │
│     6 │ drill       │ tools    │         89 │ true     │
└───────┴─────────────┴──────────┴────────────┴──────────┘
```

----

### `sample`

```python
sample(self, n: int | None = None, *, percent: float | None = None, seed: int | None = None, method: str | None = None) -> DuckDBPyRelation
```

A random subset: `n` rows, or `percent` of them, with `seed` repeating a draw.

See [Samples]({% link docs/2.0/sql/samples.md %}) for the sampling methods. A fixed `seed` gives the same sample on every run only when DuckDB runs on one thread.

**Parameters**

* **n** : int, default: None

    The number of rows to draw; give either `n` or `percent`.
* **percent** : float, keyword-only, default: None

    The percentage of the rows to draw.
* **seed** : int, keyword-only, default: None

    The seed of the random draw.
* **method** : str, keyword-only, default: None

    `'reservoir'`, `'bernoulli'` or `'system'`; `'reservoir'` for `n` and `'bernoulli'` for `percent` when omitted.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

duckdb_conn.sql("SET threads = 1")

rel.sample(3, seed=42).order("id")
```

**Result**

```text
┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     3 │ jacket  │ clothing │    65 │ true     │
│     4 │ hammer  │ tools    │    42 │ true     │
│     6 │ drill   │ tools    │    89 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘
```

----

### `select`

```python
select(self, *args: object, groups: str = '') -> DuckDBPyRelation
```

Project the relation object by the projection in project_expr.

**Aliases**: [`project`](#project)

**Parameters**

* **\*args** : str, Expression

    The columns to keep, as strings or Expressions. A single string is SQL text, which may list several columns or expressions; among several arguments, each string names one column.
* **groups** : str, keyword-only, default: ''

    Comma-separated list of columns to include in the `GROUP BY`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select("item, price")
```

**Result**

```text
┌─────────────┬───────┐
│    item     │ price │
│   varchar   │ int32 │
├─────────────┼───────┤
│ jeans       │    20 │
│ t-shirt     │     8 │
│ jacket      │    65 │
│ hammer      │    42 │
│ screwdriver │     7 │
│ drill       │    89 │
└─────────────┴───────┘
```

----

### `select_dtypes`

```python
select_dtypes(self, types: object) -> DuckDBPyRelation
```

Alias of [`select_types()`](#select_types).

----

### `select_types`

```python
select_types(self, types: object) -> DuckDBPyRelation
```

Select columns from the relation, by filtering based on type(s).

**Aliases**: [`select_dtypes`](#select_dtypes)

**Parameters**

* **types** : list

    The types of the columns to keep, as a list of `DuckDBPyType` values or type names such as `'VARCHAR'`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.select_types([duckdb.sqltypes.VARCHAR])
```

**Result**

```text
┌─────────────┬──────────┐
│    item     │ category │
│   varchar   │ varchar  │
├─────────────┼──────────┤
│ jeans       │ clothing │
│ t-shirt     │ clothing │
│ jacket      │ clothing │
│ hammer      │ tools    │
│ screwdriver │ tools    │
│ drill       │ tools    │
└─────────────┴──────────┘
```

----

### `sort`

```python
sort(self, *args: object) -> DuckDBPyRelation
```

Reorder the relation object by the provided expressions.

**Parameters**

* **\*args** : str, Expression

    The expressions to sort by, as SQL text such as `'price DESC'` or as Expressions.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.sort("category", "price DESC")
```

**Result**

```text
┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     3 │ jacket      │ clothing │    65 │ true     │
│     1 │ jeans       │ clothing │    20 │ true     │
│     2 │ t-shirt     │ clothing │     8 │ false    │
│     6 │ drill       │ tools    │    89 │ true     │
│     4 │ hammer      │ tools    │    42 │ true     │
│     5 │ screwdriver │ tools    │     7 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘
```

----

### `union`

```python
union(self, union_rel: DuckDBPyRelation) -> DuckDBPyRelation
```

Create the set union of this relation object with another relation object in other_rel.

The union is `UNION ALL`, so it keeps duplicate rows. To keep only distinct rows, apply [`distinct()`](#distinct).

**Parameters**

* **union_rel** : DuckDBPyRelation

    The relation to combine with this one; it must have the same number of columns.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.union(rel).count("*")
```

**Result**

```text
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│           12 │
└──────────────┘
```

----

### `union_by_name`

```python
union_by_name(self, other: DuckDBPyRelation, *, all: bool = True) -> DuckDBPyRelation
```

Rows from both relations, matching columns by name; keeps duplicates unless `all` is false.

**Parameters**

* **other** : DuckDBPyRelation

    The relation to combine with this one; a column that only one of them has is `NULL` in the rows of the other.
* **all** : bool, keyword-only, default: True

    Keep duplicate rows (`UNION ALL BY NAME`); with `False`, keep only distinct rows (`UNION BY NAME`).

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

first = duckdb_conn.sql("SELECT 1 AS id, 10 AS x")
second = duckdb_conn.sql("SELECT 20 AS y, 2 AS id")

first.union_by_name(second).order("id")
```

**Result**

```text
┌───────┬───────┬───────┐
│  id   │   x   │   y   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     1 │    10 │  NULL │
│     2 │  NULL │    20 │
└───────┴───────┴───────┘
```

----

### `unnest`

```python
unnest(self, *columns: str) -> DuckDBPyRelation
```

Expand list columns to one row per element.

**Parameters**

* **\*columns** : str

    Names of the list columns to expand; each keeps its name and holds one element per row.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("SELECT 1 AS id, [10, 20] AS amounts UNION ALL SELECT 2, [30]")

rel.unnest("amounts").order("id, amounts")
```

**Result**

```text
┌───────┬─────────┐
│  id   │ amounts │
│ int32 │  int32  │
├───────┼─────────┤
│     1 │      10 │
│     1 │      20 │
│     2 │      30 │
└───────┴─────────┘
```

----

### `unpivot`

```python
unpivot(self, *columns: str, name: str = 'name', value: str = 'value') -> DuckDBPyRelation
```

Turn the named columns into rows; `name` and `value` name the two new columns.

It runs an [`UNPIVOT`]({% link docs/2.0/sql/statements/unpivot.md %}); the columns not named stay as they are, repeated for each new row.

**Parameters**

* **\*columns** : str

    Names of the columns to turn into rows.
* **name** : str, keyword-only, default: 'name'

    Name of the new column that holds the names of the unpivoted columns.
* **value** : str, keyword-only, default: 'value'

    Name of the new column that holds their values.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("SELECT * FROM (VALUES ('north', 10, 20), ('south', 30, 40)) AS t(region, q1, q2)")

rel.unpivot("q1", "q2", name="quarter", value="amount").order("region, quarter")
```

**Result**

```text
┌─────────┬─────────┬────────┐
│ region  │ quarter │ amount │
│ varchar │ varchar │ int32  │
├─────────┼─────────┼────────┤
│ north   │ q1      │     10 │
│ north   │ q2      │     20 │
│ south   │ q1      │     30 │
│ south   │ q2      │     40 │
└─────────┴─────────┴────────┘
```

----

### `with_columns`

```python
with_columns(self, **columns: object) -> DuckDBPyRelation
```

Add columns, replacing any of the same name in place.

**Parameters**

* **\*\*columns** : Expression, object

    The columns, as `name=value`, where a value is an Expression, a column name or a Python value; a column of the same name is replaced in place, any other is added at the end.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

from duckdb import col, lit

rel.with_columns(price=col("price") * 2, currency=lit("EUR"))
```

**Result**

```text
┌───────┬─────────────┬──────────┬───────┬──────────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │ currency │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │ varchar  │
├───────┼─────────────┼──────────┼───────┼──────────┼──────────┤
│     1 │ jeans       │ clothing │    40 │ true     │ EUR      │
│     2 │ t-shirt     │ clothing │    16 │ false    │ EUR      │
│     3 │ jacket      │ clothing │   130 │ true     │ EUR      │
│     4 │ hammer      │ tools    │    84 │ true     │ EUR      │
│     5 │ screwdriver │ tools    │    14 │ true     │ EUR      │
│     6 │ drill       │ tools    │   178 │ true     │ EUR      │
└───────┴─────────────┴──────────┴───────┴──────────┴──────────┘
```

## Functions 

This section contains the functions which can be applied to a relation, in order to get a (scalar) result. They build a new query and run nothing.

| Name | Description |
|:--|:-------|
| [`any_value`](#any_value) | Returns the first non-null value from a given expression. |
| [`arg_max`](#arg_max) | Finds the row with the maximum value for a value column and returns the value of that row for an argument column. |
| [`arg_min`](#arg_min) | Finds the row with the minimum value for a value column and returns the value of that row for an argument column. |
| [`avg`](#avg) | Computes the average of a given expression. |
| [`bit_and`](#bit_and) | Computes the bitwise AND of all bits present in a given expression. |
| [`bit_or`](#bit_or) | Computes the bitwise OR of all bits present in a given expression. |
| [`bit_xor`](#bit_xor) | Computes the bitwise XOR of all bits present in a given expression. |
| [`bitstring_agg`](#bitstring_agg) | Computes a bitstring with bits set for each distinct value in a given expression. |
| [`bool_and`](#bool_and) | Computes the logical AND of all values present in a given expression. |
| [`bool_or`](#bool_or) | Computes the logical OR of all values present in a given expression. |
| [`count`](#count) | Computes the number of elements present in a given expression. |
| [`cume_dist`](#cume_dist) | Computes the cumulative distribution within the partition. |
| [`dense_rank`](#dense_rank) | Computes the dense rank within the partition. |
| [`favg`](#favg) | Computes the average of all values present in a given expression using a more accurate floating point summation (Kahan Sum). |
| [`first`](#first) | Returns the first value of a given expression. |
| [`first_value`](#first_value) | Computes the first value within the group or partition. |
| [`fsum`](#fsum) | Computes the sum of all values present in a given expression using a more accurate floating point summation (Kahan Sum). |
| [`geomean`](#geomean) | Computes the geometric mean over all values present in a given expression. |
| [`histogram`](#histogram) | Computes the histogram over all values present in a given expression. |
| [`lag`](#lag) | Computes the lag within the partition. |
| [`last`](#last) | Returns the last value of a given expression. |
| [`last_value`](#last_value) | Computes the last value within the group or partition. |
| [`lead`](#lead) | Computes the lead within the partition. |
| [`list`](#list) | Returns a list containing all values present in a given expression. |
| [`max`](#max) | Returns the maximum value present in a given expression. |
| [`mean`](#mean) | Alias of [`avg()`](#avg). |
| [`median`](#median) | Computes the median over all values present in a given expression. |
| [`min`](#min) | Returns the minimum value present in a given expression. |
| [`mode`](#mode) | Computes the mode over all values present in a given expression. |
| [`n_tile`](#n_tile) | Divides the partition as equally as possible into num_buckets. |
| [`nth_value`](#nth_value) | Computes the nth value within the partition. |
| [`percent_rank`](#percent_rank) | Computes the relative rank within the partition. |
| [`product`](#product) | Returns the product of all values present in a given expression. |
| [`quantile`](#quantile) | Alias of [`quantile_disc()`](#quantile_disc). |
| [`quantile_cont`](#quantile_cont) | Computes the interpolated quantile value for a given expression. |
| [`quantile_disc`](#quantile_disc) | Computes the exact quantile value for a given expression. |
| [`rank`](#rank) | Computes the rank within the partition. |
| [`rank_dense`](#rank_dense) | Alias of [`dense_rank()`](#dense_rank). |
| [`row_number`](#row_number) | Computes the row number within the partition. |
| [`std`](#std) | Alias of [`stddev_samp()`](#stddev_samp). |
| [`stddev`](#stddev) | Alias of [`stddev_samp()`](#stddev_samp). |
| [`stddev_pop`](#stddev_pop) | Computes the population standard deviation for a given expression. |
| [`stddev_samp`](#stddev_samp) | Computes the sample standard deviation for a given expression. |
| [`string_agg`](#string_agg) | Concatenates the values present in a given expression with a separator. |
| [`sum`](#sum) | Computes the sum of all values present in a given expression. |
| [`unique`](#unique) | Returns the distinct values in a column. |
| [`value_counts`](#value_counts) | Computes the number of elements present in a given expression, also projecting the original expression. |
| [`var`](#var) | Alias of [`var_samp()`](#var_samp). |
| [`var_pop`](#var_pop) | Computes the population variance for a given expression. |
| [`var_samp`](#var_samp) | Computes the sample variance for a given expression. |
| [`variance`](#variance) | Alias of [`var_samp()`](#var_samp). |

### `any_value`

```python
any_value(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Returns the first non-null value from a given expression.

**Parameters**

* **expression** : str

    The column or expression from which to retrieve any value.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.any_value("item")
```

**Result**

```text
┌─────────────────┐
│ any_value(item) │
│     varchar     │
├─────────────────┤
│ jeans           │
└─────────────────┘
```

----

### `arg_max`

```python
arg_max(self, arg_column: str, value_column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Finds the row with the maximum value for a value column and returns the value of that row for an argument column.

**Parameters**

* **arg_column** : str

    The column whose value is returned, taken from the row where `value_column` is largest.
* **value_column** : str

    The column whose largest value picks the row.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.arg_max("item", "price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬──────────────────────┐
│ category │ arg_max(item, price) │
│ varchar  │       varchar        │
├──────────┼──────────────────────┤
│ clothing │ jacket               │
│ tools    │ drill                │
└──────────┴──────────────────────┘
```

----

### `arg_min`

```python
arg_min(self, arg_column: str, value_column: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Finds the row with the minimum value for a value column and returns the value of that row for an argument column.

**Parameters**

* **arg_column** : str

    The column whose value is returned, taken from the row where `value_column` is smallest.
* **value_column** : str

    The column whose smallest value picks the row.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.arg_min("item", "price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬──────────────────────┐
│ category │ arg_min(item, price) │
│ varchar  │       varchar        │
├──────────┼──────────────────────┤
│ clothing │ t-shirt              │
│ tools    │ screwdriver          │
└──────────┴──────────────────────┘
```

----

### `avg`

```python
avg(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the average of a given expression.

**Aliases**: [`mean`](#mean)

**Parameters**

* **expression** : str

    The column or expression to calculate the average of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.avg("price")
```

**Result**

```text
┌────────────┐
│ avg(price) │
│   double   │
├────────────┤
│       38.5 │
└────────────┘
```

----

### `bit_and`

```python
bit_and(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the bitwise AND of all bits present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to perform the bitwise AND aggregation on.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bit_and("id", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬─────────────┐
│ category │ bit_and(id) │
│ varchar  │    int32    │
├──────────┼─────────────┤
│ clothing │           0 │
│ tools    │           4 │
└──────────┴─────────────┘
```

----

### `bit_or`

```python
bit_or(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the bitwise OR of all bits present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to perform the bitwise OR aggregation on.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bit_or("id", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────┐
│ category │ bit_or(id) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │          3 │
│ tools    │          7 │
└──────────┴────────────┘
```

----

### `bit_xor`

```python
bit_xor(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the bitwise XOR of all bits present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to perform the bitwise XOR aggregation on.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bit_xor("id", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬─────────────┐
│ category │ bit_xor(id) │
│ varchar  │    int32    │
├──────────┼─────────────┤
│ clothing │           0 │
│ tools    │           7 │
└──────────┴─────────────┘
```

----

### `bitstring_agg`

```python
bitstring_agg(self, expression: str, min: int | None = None, max: int | None = None, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes a bitstring with bits set for each distinct value in a given expression.

**Parameters**

* **expression** : str

    The column or expression to aggregate as a bitstring.
* **min** : int, default: None

    The smallest value the bitstring can hold; give both `min` and `max`, or neither.
* **max** : int, default: None

    The largest value the bitstring can hold; give both `min` and `max`, or neither.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bitstring_agg("id", min=1, max=6, groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬─────────────────────────┐
│ category │ bitstring_agg(id, 1, 6) │
│ varchar  │           bit           │
├──────────┼─────────────────────────┤
│ clothing │ 111000                  │
│ tools    │ 000111                  │
└──────────┴─────────────────────────┘
```

----

### `bool_and`

```python
bool_and(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the logical AND of all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to perform the boolean AND aggregation on.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bool_and("in_stock", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────┐
│ category │ bool_and(in_stock) │
│ varchar  │      boolean       │
├──────────┼────────────────────┤
│ clothing │ false              │
│ tools    │ true               │
└──────────┴────────────────────┘
```

----

### `bool_or`

```python
bool_or(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the logical OR of all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to perform the boolean OR aggregation on.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.bool_or("in_stock", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬───────────────────┐
│ category │ bool_or(in_stock) │
│ varchar  │      boolean      │
├──────────┼───────────────────┤
│ clothing │ true              │
│ tools    │ true              │
└──────────┴───────────────────┘
```

----

### `count`

```python
count(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the number of elements present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to count the values of; `'*'` counts the rows.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.count("id")
```

**Result**

```text
┌───────────┐
│ count(id) │
│   int64   │
├───────────┤
│         6 │
└───────────┘
```

----

### `cume_dist`

```python
cume_dist(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the cumulative distribution within the partition.

**Parameters**

* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.cume_dist(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")
```

**Result**

```text
┌──────────┬─────────────┬───────┬─────────────────────────────────────────────────────────┐
│ category │    item     │ price │ cume_dist() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                         double                          │
├──────────┼─────────────┼───────┼─────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                      0.3333333333333333 │
│ clothing │ jeans       │    20 │                                      0.6666666666666666 │
│ clothing │ jacket      │    65 │                                                     1.0 │
│ tools    │ screwdriver │     7 │                                      0.3333333333333333 │
│ tools    │ hammer      │    42 │                                      0.6666666666666666 │
│ tools    │ drill       │    89 │                                                     1.0 │
└──────────┴─────────────┴───────┴─────────────────────────────────────────────────────────┘
```

----

### `dense_rank`

```python
dense_rank(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the dense rank within the partition.

**Aliases**: [`rank_dense`](#rank_dense)

**Parameters**

* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.dense_rank(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")
```

**Result**

```text
┌──────────┬─────────────┬───────┬──────────────────────────────────────────────────────────┐
│ category │    item     │ price │ dense_rank() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                          int64                           │
├──────────┼─────────────┼───────┼──────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                        1 │
│ clothing │ jeans       │    20 │                                                        2 │
│ clothing │ jacket      │    65 │                                                        3 │
│ tools    │ screwdriver │     7 │                                                        1 │
│ tools    │ hammer      │    42 │                                                        2 │
│ tools    │ drill       │    89 │                                                        3 │
└──────────┴─────────────┴───────┴──────────────────────────────────────────────────────────┘
```

----

### `favg`

```python
favg(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the average of all values present in a given expression using a more accurate floating point summation (Kahan Sum).

**Parameters**

* **expression** : str

    The column or expression to calculate the average of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.favg("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬─────────────┐
│ category │ favg(price) │
│ varchar  │   double    │
├──────────┼─────────────┤
│ clothing │        31.0 │
│ tools    │        46.0 │
└──────────┴─────────────┘
```

----

### `first`

```python
first(self, expression: str, groups: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Returns the first value of a given expression.

**Parameters**

* **expression** : str

    The column or expression from which to retrieve the first value.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.first("item", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬───────────────┐
│ category │ "first"(item) │
│ varchar  │    varchar    │
├──────────┼───────────────┤
│ clothing │ jeans         │
│ tools    │ hammer        │
└──────────┴───────────────┘
```

----

### `first_value`

```python
first_value(self, expression: str, window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the first value within the group or partition.

**Parameters**

* **expression** : str

    The column or expression from which to retrieve the first value within the window.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.first_value("item", window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category").distinct().order("category")
```

**Result**

```text
┌──────────┬───────────────────────────────────────────────────────────────┐
│ category │ first_value(item) OVER (PARTITION BY category ORDER BY price) │
│ varchar  │                            varchar                            │
├──────────┼───────────────────────────────────────────────────────────────┤
│ clothing │ t-shirt                                                       │
│ tools    │ screwdriver                                                   │
└──────────┴───────────────────────────────────────────────────────────────┘
```

----

### `fsum`

```python
fsum(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the sum of all values present in a given expression using a more accurate floating point summation (Kahan Sum).

**Parameters**

* **expression** : str

    The column or expression to calculate the sum of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.fsum("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬─────────────┐
│ category │ fsum(price) │
│ varchar  │   double    │
├──────────┼─────────────┤
│ clothing │        93.0 │
│ tools    │       138.0 │
└──────────┴─────────────┘
```

----

### `geomean`

```python
geomean(self, expression: str, groups: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the geometric mean over all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the geometric mean of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.geomean("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────┐
│ category │   geomean(price)   │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │ 21.827857661222115 │
│ tools    │  29.68787497217981 │
└──────────┴────────────────────┘
```

----

### `histogram`

```python
histogram(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the histogram over all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the histogram of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.histogram("in_stock", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬───────────────────────┐
│ category │  histogram(in_stock)  │
│ varchar  │ map(boolean, ubigint) │
├──────────┼───────────────────────┤
│ clothing │ {false=1, true=2}     │
│ tools    │ {true=3}              │
└──────────┴───────────────────────┘
```

----

### `lag`

```python
lag(self, expression: str, window_spec: str, offset: int = 1, default_value: str = 'NULL', ignore_nulls: bool = False, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the lag within the partition.

**Parameters**

* **expression** : str

    The column or expression to apply the lag function on.
* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **offset** : int, default: 1

    The number of rows to lag behind.
* **default_value** : str, default: 'NULL'

    The value, as SQL text, to return when the lag offset goes out of bounds.
* **ignore_nulls** : bool, default: False

    Whether to ignore NULL values when computing the lag.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.lag("item", window_spec="OVER (ORDER BY price)", projected_columns="item, price").order("price")
```

**Result**

```text
┌─────────────┬───────┬──────────────────────────────────────────┐
│    item     │ price │ lag(item, 1, NULL) OVER (ORDER BY price) │
│   varchar   │ int32 │                 varchar                  │
├─────────────┼───────┼──────────────────────────────────────────┤
│ screwdriver │     7 │ NULL                                     │
│ t-shirt     │     8 │ screwdriver                              │
│ jeans       │    20 │ t-shirt                                  │
│ hammer      │    42 │ jeans                                    │
│ jacket      │    65 │ hammer                                   │
│ drill       │    89 │ jacket                                   │
└─────────────┴───────┴──────────────────────────────────────────┘
```

----

### `last`

```python
last(self, expression: str, groups: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Returns the last value of a given expression.

**Parameters**

* **expression** : str

    The column or expression from which to retrieve the last value.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.last("item", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬──────────────┐
│ category │ "last"(item) │
│ varchar  │   varchar    │
├──────────┼──────────────┤
│ clothing │ jacket       │
│ tools    │ drill        │
└──────────┴──────────────┘
```

----

### `last_value`

```python
last_value(self, expression: str, window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the last value within the group or partition.

**Parameters**

* **expression** : str

    The column or expression from which to retrieve the last value within the window.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.last_value("item", window_spec="OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)", projected_columns="category").distinct().order("category")
```

**Result**

```text
┌──────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ category │ last_value(item) OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDE │
│          │                                               D FOLLOWING)                                                │
│ varchar  │                                                  varchar                                                  │
├──────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ clothing │ jacket                                                                                                    │
├──────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ tools    │ drill                                                                                                     │
└──────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

----

### `lead`

```python
lead(self, expression: str, window_spec: str, offset: int = 1, default_value: str = 'NULL', ignore_nulls: bool = False, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the lead within the partition.

**Parameters**

* **expression** : str

    The column or expression to apply the lead function on.
* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **offset** : int, default: 1

    The number of rows to lead ahead.
* **default_value** : str, default: 'NULL'

    The value, as SQL text, to return when the lead offset goes out of bounds.
* **ignore_nulls** : bool, default: False

    Whether to ignore NULL values when computing the lead.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.lead("item", window_spec="OVER (ORDER BY price)", projected_columns="item, price").order("price")
```

**Result**

```text
┌─────────────┬───────┬───────────────────────────────────────────┐
│    item     │ price │ lead(item, 1, NULL) OVER (ORDER BY price) │
│   varchar   │ int32 │                  varchar                  │
├─────────────┼───────┼───────────────────────────────────────────┤
│ screwdriver │     7 │ t-shirt                                   │
│ t-shirt     │     8 │ jeans                                     │
│ jeans       │    20 │ hammer                                    │
│ hammer      │    42 │ jacket                                    │
│ jacket      │    65 │ drill                                     │
│ drill       │    89 │ NULL                                      │
└─────────────┴───────┴───────────────────────────────────────────┘
```

----

### `list`

```python
list(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Returns a list containing all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to aggregate values into a list.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.list("item", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬──────────────────────────────┐
│ category │          list(item)          │
│ varchar  │          varchar[]           │
├──────────┼──────────────────────────────┤
│ clothing │ [jeans, t-shirt, jacket]     │
│ tools    │ [hammer, screwdriver, drill] │
└──────────┴──────────────────────────────┘
```

----

### `max`

```python
max(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Returns the maximum value present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the maximum value of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.max("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────┐
│ category │ max(price) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │         65 │
│ tools    │         89 │
└──────────┴────────────┘
```

----

### `mean`

```python
mean(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Alias of [`avg()`](#avg).

----

### `median`

```python
median(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the median over all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the median value of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.median("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬───────────────┐
│ category │ median(price) │
│ varchar  │    double     │
├──────────┼───────────────┤
│ clothing │          20.0 │
│ tools    │          42.0 │
└──────────┴───────────────┘
```

----

### `min`

```python
min(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Returns the minimum value present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the min value of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.min("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────┐
│ category │ min(price) │
│ varchar  │   int32    │
├──────────┼────────────┤
│ clothing │          8 │
│ tools    │          7 │
└──────────┴────────────┘
```

----

### `mode`

```python
mode(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the mode over all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the mode (most frequent value) of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

sales = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES ('clothing', 20), ('clothing', 20), ('clothing', 8), ('tools', 42), ('tools', 7), ('tools', 7))
        AS sales(category, price)
""")

sales.mode("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬───────────────┐
│ category │ "mode"(price) │
│ varchar  │     int32     │
├──────────┼───────────────┤
│ clothing │            20 │
│ tools    │             7 │
└──────────┴───────────────┘
```

----

### `n_tile`

```python
n_tile(self, window_spec: str, num_buckets: int, projected_columns: str = '') -> DuckDBPyRelation
```

Divides the partition as equally as possible into num_buckets.

**Parameters**

* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **num_buckets** : int

    The number of buckets to divide the rows into.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.n_tile(window_spec="OVER (PARTITION BY category ORDER BY price)", num_buckets=2, projected_columns="category, item, price").order("category, price")
```

**Result**

```text
┌──────────┬─────────────┬───────┬──────────────────────────────────────────────────────┐
│ category │    item     │ price │ ntile(2) OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                        int64                         │
├──────────┼─────────────┼───────┼──────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                    1 │
│ clothing │ jeans       │    20 │                                                    1 │
│ clothing │ jacket      │    65 │                                                    2 │
│ tools    │ screwdriver │     7 │                                                    1 │
│ tools    │ hammer      │    42 │                                                    1 │
│ tools    │ drill       │    89 │                                                    2 │
└──────────┴─────────────┴───────┴──────────────────────────────────────────────────────┘
```

----

### `nth_value`

```python
nth_value(self, expression: str, window_spec: str, offset: int, ignore_nulls: bool = False, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the nth value within the partition.

**Parameters**

* **expression** : str

    The column or expression from which to retrieve the nth value within the window.
* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **offset** : int

    The position of the value to retrieve within the window (1-based index).
* **ignore_nulls** : bool, default: False

    Whether to ignore NULL values when computing the nth value.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.nth_value("item", window_spec="OVER (PARTITION BY category ORDER BY price)", offset=2, projected_columns="category, item, price").order("category, price")
```

**Result**

```text
┌──────────┬─────────────┬───────┬────────────────────────────────────────────────────────────────┐
│ category │    item     │ price │ nth_value(item, 2) OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                            varchar                             │
├──────────┼─────────────┼───────┼────────────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │ NULL                                                           │
│ clothing │ jeans       │    20 │ jeans                                                          │
│ clothing │ jacket      │    65 │ jeans                                                          │
│ tools    │ screwdriver │     7 │ NULL                                                           │
│ tools    │ hammer      │    42 │ hammer                                                         │
│ tools    │ drill       │    89 │ hammer                                                         │
└──────────┴─────────────┴───────┴────────────────────────────────────────────────────────────────┘
```

----

### `percent_rank`

```python
percent_rank(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the relative rank within the partition.

**Parameters**

* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.percent_rank(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")
```

**Result**

```text
┌──────────┬─────────────┬───────┬────────────────────────────────────────────────────────────┐
│ category │    item     │ price │ percent_rank() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                           double                           │
├──────────┼─────────────┼───────┼────────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                        0.0 │
│ clothing │ jeans       │    20 │                                                        0.5 │
│ clothing │ jacket      │    65 │                                                        1.0 │
│ tools    │ screwdriver │     7 │                                                        0.0 │
│ tools    │ hammer      │    42 │                                                        0.5 │
│ tools    │ drill       │    89 │                                                        1.0 │
└──────────┴─────────────┴───────┴────────────────────────────────────────────────────────────┘
```

----

### `product`

```python
product(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Returns the product of all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the product of.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.product("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────┐
│ category │ product(price) │
│ varchar  │     double     │
├──────────┼────────────────┤
│ clothing │        10400.0 │
│ tools    │        26166.0 │
└──────────┴────────────────┘
```

----

### `quantile`

```python
quantile(self, expression: str, q: float | builtins.list[float] = 0.5, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Alias of [`quantile_disc()`](#quantile_disc).

----

### `quantile_cont`

```python
quantile_cont(self, expression: str, q: float | builtins.list[float] = 0.5, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the interpolated quantile value for a given expression.

**Parameters**

* **expression** : str

    The column or expression to compute the continuous quantile for.
* **q** : float, list[float], default: 0.5

    The quantile to compute, such as 0.5 for the median, or a list of quantiles.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.quantile_cont("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────────────────┐
│ category │ quantile_cont(price, 0.500000) │
│ varchar  │             double             │
├──────────┼────────────────────────────────┤
│ clothing │                           20.0 │
│ tools    │                           42.0 │
└──────────┴────────────────────────────────┘
```

----

### `quantile_disc`

```python
quantile_disc(self, expression: str, q: float | builtins.list[float] = 0.5, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the exact quantile value for a given expression.

**Aliases**: [`quantile`](#quantile)

**Parameters**

* **expression** : str

    The column or expression to compute the discrete quantile for.
* **q** : float, list[float], default: 0.5

    The quantile to compute, such as 0.5 for the median, or a list of quantiles.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.quantile_disc("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────────────────┐
│ category │ quantile_disc(price, 0.500000) │
│ varchar  │             int32              │
├──────────┼────────────────────────────────┤
│ clothing │                             20 │
│ tools    │                             42 │
└──────────┴────────────────────────────────┘
```

----

### `rank`

```python
rank(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the rank within the partition.

**Parameters**

* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.rank(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")
```

**Result**

```text
┌──────────┬─────────────┬───────┬────────────────────────────────────────────────────┐
│ category │    item     │ price │ rank() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                       int64                        │
├──────────┼─────────────┼───────┼────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                  1 │
│ clothing │ jeans       │    20 │                                                  2 │
│ clothing │ jacket      │    65 │                                                  3 │
│ tools    │ screwdriver │     7 │                                                  1 │
│ tools    │ hammer      │    42 │                                                  2 │
│ tools    │ drill       │    89 │                                                  3 │
└──────────┴─────────────┴───────┴────────────────────────────────────────────────────┘
```

----

### `rank_dense`

```python
rank_dense(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation
```

Alias of [`dense_rank()`](#dense_rank).

----

### `row_number`

```python
row_number(self, window_spec: str, projected_columns: str = '') -> DuckDBPyRelation
```

Computes the row number within the partition.

**Parameters**

* **window_spec** : str

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.row_number(window_spec="OVER (PARTITION BY category ORDER BY price)", projected_columns="category, item, price").order("category, price")
```

**Result**

```text
┌──────────┬─────────────┬───────┬──────────────────────────────────────────────────────────┐
│ category │    item     │ price │ row_number() OVER (PARTITION BY category ORDER BY price) │
│ varchar  │   varchar   │ int32 │                          int64                           │
├──────────┼─────────────┼───────┼──────────────────────────────────────────────────────────┤
│ clothing │ t-shirt     │     8 │                                                        1 │
│ clothing │ jeans       │    20 │                                                        2 │
│ clothing │ jacket      │    65 │                                                        3 │
│ tools    │ screwdriver │     7 │                                                        1 │
│ tools    │ hammer      │    42 │                                                        2 │
│ tools    │ drill       │    89 │                                                        3 │
└──────────┴─────────────┴───────┴──────────────────────────────────────────────────────────┘
```

----

### `std`

```python
std(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Alias of [`stddev_samp()`](#stddev_samp).

----

### `stddev`

```python
stddev(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Alias of [`stddev_samp()`](#stddev_samp).

----

### `stddev_pop`

```python
stddev_pop(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the population standard deviation for a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the standard deviation for.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.stddev_pop("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────┐
│ category │ stddev_pop(price)  │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │ 24.535688292770594 │
│ tools    │ 33.595634637057636 │
└──────────┴────────────────────┘
```

----

### `stddev_samp`

```python
stddev_samp(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the sample standard deviation for a given expression.

**Aliases**: [`std`](#std), [`stddev`](#stddev)

**Parameters**

* **expression** : str

    The column or expression to calculate the standard deviation for.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.stddev_samp("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────┐
│ category │ stddev_samp(price) │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │  30.04995840263344 │
│ tools    │  41.14608122288197 │
└──────────┴────────────────────┘
```

----

### `string_agg`

```python
string_agg(self, expression: str, sep: str = ',', groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Concatenates the values present in a given expression with a separator.

**Parameters**

* **expression** : str

    The column or expression to concatenate values from.
* **sep** : str, default: ','

    Separator string to use between concatenated values.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.string_agg("item", sep=", ", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────────────┐
│ category │   string_agg(item, ', ')   │
│ varchar  │          varchar           │
├──────────┼────────────────────────────┤
│ clothing │ jeans, t-shirt, jacket     │
│ tools    │ hammer, screwdriver, drill │
└──────────┴────────────────────────────┘
```

----

### `sum`

```python
sum(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the sum of all values present in a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the sum for.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.sum("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────┐
│ category │ sum(price) │
│ varchar  │   int128   │
├──────────┼────────────┤
│ clothing │         93 │
│ tools    │        138 │
└──────────┴────────────┘
```

----

### `unique`

```python
unique(self, unique_aggr: str) -> DuckDBPyRelation
```

Returns the distinct values in a column.

**Parameters**

* **unique_aggr** : str

    The column to get the distinct values for.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.unique("category").order("category")
```

**Result**

```text
┌──────────┐
│ category │
│ varchar  │
├──────────┤
│ clothing │
│ tools    │
└──────────┘
```

----

### `value_counts`

```python
value_counts(self, expression: str, groups: str = '') -> DuckDBPyRelation
```

Computes the number of elements present in a given expression, also projecting the original expression.

**Parameters**

* **expression** : str

    The column or expression to count values from.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.value_counts("in_stock", groups="in_stock").order("in_stock")
```

**Result**

```text
┌──────────┬─────────────────┐
│ in_stock │ count(in_stock) │
│ boolean  │      int64      │
├──────────┼─────────────────┤
│ false    │               1 │
│ true     │               5 │
└──────────┴─────────────────┘
```

----

### `var`

```python
var(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Alias of [`var_samp()`](#var_samp).

----

### `var_pop`

```python
var_pop(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the population variance for a given expression.

**Parameters**

* **expression** : str

    The column or expression to calculate the population variance for.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.var_pop("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬────────────────────┐
│ category │   var_pop(price)   │
│ varchar  │       double       │
├──────────┼────────────────────┤
│ clothing │              602.0 │
│ tools    │ 1128.6666666666667 │
└──────────┴────────────────────┘
```

----

### `var_samp`

```python
var_samp(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Computes the sample variance for a given expression.

**Aliases**: [`var`](#var), [`variance`](#variance)

**Parameters**

* **expression** : str

    The column or expression to calculate the sample variance for.
* **groups** : str, default: ''

    Comma-separated list of columns to include in the `GROUP BY`; cannot be combined with `window_spec`.
* **window_spec** : str, default: ''

    Window specification, written as `OVER (PARTITION BY ... ORDER BY ...)`; cannot be combined with `groups`.
* **projected_columns** : str, default: ''

    Comma-separated list of columns to include in the result.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.var_samp("price", groups="category", projected_columns="category").order("category")
```

**Result**

```text
┌──────────┬─────────────────┐
│ category │ var_samp(price) │
│ varchar  │     double      │
├──────────┼─────────────────┤
│ clothing │           903.0 │
│ tools    │          1693.0 │
└──────────┴─────────────────┘
```

----

### `variance`

```python
variance(self, expression: str, groups: str = '', window_spec: str = '', projected_columns: str = '') -> DuckDBPyRelation
```

Alias of [`var_samp()`](#var_samp).

## Output 

This section contains the methods which read the relation, store it in a table, a view or a file, or write to the table it reads.

| Name | Description |
|:--|:-------|
| [`insert`](#insert) | Inserts the given values into the relation. |
| [`insert_into`](#insert_into) | Inserts the relation object into an existing table named table_name. |
| [`update`](#update) | Update the given relation with the provided expressions. |
| [`arrow`](#arrow) | Alias of [`to_arrow_reader()`](#to_arrow_reader). |
| [`close`](#close) | Does nothing: a relation holds no result. |
| [`create`](#create) | Creates a new table named table_name with the contents of the relation object. |
| [`create_view`](#create_view) | Creates a view named view_name that refers to the relation object. |
| [`df`](#df) | Execute and fetch all rows as a pandas DataFrame. |
| [`fetch_arrow_reader`](#fetch_arrow_reader) | Deprecated alias of [`to_arrow_reader()`](#to_arrow_reader). |
| [`fetch_arrow_table`](#fetch_arrow_table) | Deprecated alias of [`to_arrow_table()`](#to_arrow_table). |
| [`fetch_record_batch`](#fetch_record_batch) | Deprecated alias of [`to_arrow_reader()`](#to_arrow_reader). |
| [`fetchall`](#fetchall) | Execute and fetch all rows as a list of tuples. |
| [`fetchdf`](#fetchdf) | Alias of [`df()`](#df). |
| [`fetchnumpy`](#fetchnumpy) | Execute and fetch all rows as a Python dict mapping each column to one numpy arrays. |
| [`iter_rows`](#iter_rows) | Every row, a batch at a time, over one run of the relation. |
| [`materialize`](#materialize) | Execute once and return a relation over the rows, held in client memory. |
| [`pl`](#pl) | Execute and fetch all rows as a Polars DataFrame. |
| [`tf`](#tf) | Fetch a result as dict of TensorFlow Tensors. |
| [`to_arrow_reader`](#to_arrow_reader) | Execute and return an Arrow Record Batch Reader that yields all rows. |
| [`to_arrow_table`](#to_arrow_table) | Execute and fetch all rows as an Arrow Table. |
| [`to_csv`](#to_csv) | Alias of [`write_csv()`](#write_csv). |
| [`to_df`](#to_df) | Alias of [`df()`](#df). |
| [`to_dicts`](#to_dicts) | Every row, as a dict keyed by column name. |
| [`to_parquet`](#to_parquet) | Alias of [`write_parquet()`](#write_parquet). |
| [`to_table`](#to_table) | Alias of [`create()`](#create). |
| [`to_view`](#to_view) | Alias of [`create_view()`](#create_view). |
| [`torch`](#torch) | Fetch a result as dict of PyTorch Tensors. |
| [`write_csv`](#write_csv) | Write the relation object to a CSV file in 'file_name'. |
| [`write_parquet`](#write_parquet) | Write the relation object to a Parquet file in 'file_name'. |

### `insert`

```python
insert(self, values: object) -> None
```

Inserts the given values into the relation.

The relation must read a table, as one from [`table()`](#table) does. The relation runs again on every read, so reading it afterwards shows the new rows.

**Parameters**

* **values** : tuple, list, object

    The values of one row in the order of the table's columns: a tuple, a list or, for a table of one column, a single value.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items (item VARCHAR, price INTEGER)")

rel = duckdb_conn.table("items")

rel.insert(("jeans", 20))
rel.insert(["hammer", 42])

rel.order("item")
```

**Result**

```text
┌─────────┬───────┐
│  item   │ price │
│ varchar │ int32 │
├─────────┼───────┤
│ hammer  │    42 │
│ jeans   │    20 │
└─────────┴───────┘
```

----

### `insert_into`

```python
insert_into(self, table_name: str) -> None
```

Inserts the relation object into an existing table named table_name.

The name is table, schema.table or catalog.schema.table, and a part may be double quoted; a name of more than three parts needs SQL (INSERT INTO) for now.

**Parameters**

* **table_name** : str

    The name of the table: `table`, `schema.table` or `catalog.schema.table`; a name of more parts raises `NotImplementedException`. The table must exist, and the relation must have its columns in the table's column order.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items (item VARCHAR, price INTEGER)")

rel = duckdb_conn.values(("jeans", 20), ("hammer", 42))

rel.insert_into("items")

duckdb_conn.table("items").order("item")
```

**Result**

```text
┌─────────┬───────┐
│  item   │ price │
│ varchar │ int32 │
├─────────┼───────┤
│ hammer  │    42 │
│ jeans   │    20 │
└─────────┴───────┘
```

----

### `update`

```python
update(self, set: object, *, condition: object = None) -> None
```

Update the given relation with the provided expressions.

The relation must read a table, as one from [`table()`](#table) does. The relation runs again on every read, so reading it afterwards shows the updated rows.

**Parameters**

* **set** : dict

    The new values, as a dict of column name to an Expression or a Python value. A string value raises `TypeError`: write `col('y')` for the column `y`, or `lit('y')` for the text.
* **condition** : str, Expression, keyword-only, default: None

    Optional condition to filter which rows to update.

**Example**

```python
import duckdb
from duckdb import col, lit

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items AS SELECT * FROM (VALUES (1, 'jeans', 20), (2, 'hammer', 42)) AS t(id, item, price)")

rel = duckdb_conn.table("items")

rel.update(set={"item": lit("claw hammer"), "price": col("price") + 5}, condition=col("id") == 2)

rel.order("id")
```

**Result**

```text
┌───────┬─────────────┬───────┐
│  id   │    item     │ price │
│ int32 │   varchar   │ int32 │
├───────┼─────────────┼───────┤
│     1 │ jeans       │    20 │
│     2 │ claw hammer │    47 │
└───────┴─────────────┴───────┘
```

----

### `arrow`

```python
arrow(self, batch_size: int = 131072) -> Any
```

Alias of [`to_arrow_reader()`](#to_arrow_reader).

> We recommend using [`to_arrow_reader()`](#to_arrow_reader) instead.

----

### `close`

```python
close(self) -> None
```

Does nothing: a relation holds no result.

----

### `create`

```python
create(self, table_name: str) -> None
```

Creates a new table named table_name with the contents of the relation object.

The name is table, schema.table or catalog.schema.table, and a part may be double quoted; a name of more than three parts needs SQL (CREATE TABLE AS) for now.

**Aliases**: [`to_table`](#to_table)

**Parameters**

* **table_name** : str

    The name of the table: `table`, `schema.table` or `catalog.schema.table`; a name of more parts raises `NotImplementedException`. There must not be a table of that name already.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.filter("category = 'tools'").create("tools")

duckdb_conn.table("tools")
```

**Result**

```text
┌───────┬─────────────┬──────────┬───────┬──────────┐
│  id   │    item     │ category │ price │ in_stock │
│ int32 │   varchar   │ varchar  │ int32 │ boolean  │
├───────┼─────────────┼──────────┼───────┼──────────┤
│     4 │ hammer      │ tools    │    42 │ true     │
│     5 │ screwdriver │ tools    │     7 │ true     │
│     6 │ drill       │ tools    │    89 │ true     │
└───────┴─────────────┴──────────┴───────┴──────────┘
```

----

### `create_view`

```python
create_view(self, view_name: str, replace: bool = True) -> DuckDBPyRelation
```

Creates a view named view_name that refers to the relation object.

A view stores SQL text, so a relation over rows that DuckDB holds for it cannot become one: a relation from [`values()`](#values) or [`materialize()`](#materialize) or over a statement that ran at the call, such as an `INSERT`, raises `InvalidInputException`. Use [`create()`](#create) to store such rows in a table, or `con.register(name, rel)` for a temporary view that lasts as long as the connection.

**Aliases**: [`to_view`](#to_view)

**Parameters**

* **view_name** : str

    The name of the view to be created.
* **replace** : bool, default: True

    If the view should be created with `CREATE OR REPLACE`. When set to `False`, there must not be a view of that name already.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.filter("price > 30").create_view("expensive_items")

duckdb_conn.view("expensive_items")
```

**Result**

```text
┌───────┬─────────┬──────────┬───────┬──────────┐
│  id   │  item   │ category │ price │ in_stock │
│ int32 │ varchar │ varchar  │ int32 │ boolean  │
├───────┼─────────┼──────────┼───────┼──────────┤
│     3 │ jacket  │ clothing │    65 │ true     │
│     4 │ hammer  │ tools    │    42 │ true     │
│     6 │ drill   │ tools    │    89 │ true     │
└───────┴─────────┴──────────┴───────┴──────────┘
```

----

### `df`

```python
df(self, *, date_as_object: bool = False) -> Any
```

Execute and fetch all rows as a pandas DataFrame.

**Aliases**: [`fetchdf`](#fetchdf), [`to_df`](#to_df)

**Parameters**

* **date_as_object** : bool, keyword-only, default: False

    If the date columns should be interpreted as Python date objects.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.df()
```

**Result**

```text
   id         item  category  price  in_stock
0   1        jeans  clothing     20      True
1   2      t-shirt  clothing      8     False
2   3       jacket  clothing     65      True
3   4       hammer     tools     42      True
4   5  screwdriver     tools      7      True
5   6        drill     tools     89      True
```

----

### `fetch_arrow_reader`

```python
fetch_arrow_reader(self, batch_size: int = 131072) -> Any
```

Deprecated alias of [`to_arrow_reader()`](#to_arrow_reader).

> Deprecated `fetch_arrow_reader()` is deprecated. Use [`to_arrow_reader()`](#to_arrow_reader) instead.

----

### `fetch_arrow_table`

```python
fetch_arrow_table(self, batch_size: int = 131072) -> Any
```

Deprecated alias of [`to_arrow_table()`](#to_arrow_table).

> Deprecated `fetch_arrow_table()` is deprecated. Use [`to_arrow_table()`](#to_arrow_table) instead.

----

### `fetch_record_batch`

```python
fetch_record_batch(self, rows_per_batch: int = 131072) -> Any
```

Deprecated alias of [`to_arrow_reader()`](#to_arrow_reader).

> Deprecated `fetch_record_batch()` is deprecated. Use [`to_arrow_reader()`](#to_arrow_reader) instead.

----

### `fetchall`

```python
fetchall(self) -> builtins.list[tuple[Any, ...]]
```

Execute and fetch all rows as a list of tuples.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.limit(1).fetchall()
```

**Result**

```text
[(1, 'jeans', 'clothing', 20, True)]
```

----

### `fetchdf`

```python
fetchdf(self, *, date_as_object: bool = False) -> Any
```

Alias of [`df()`](#df).

----

### `fetchnumpy`

```python
fetchnumpy(self) -> dict[str, Any]
```

Execute and fetch all rows as a Python dict mapping each column to one numpy arrays.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.fetchnumpy()
```

**Result**

```text
{'id': array([1, 2, 3, 4, 5, 6], dtype=int32), 'item': array(['jeans', 't-shirt', 'jacket', 'hammer', 'screwdriver', 'drill'],
      dtype=object), 'category': array(['clothing', 'clothing', 'clothing', 'tools', 'tools', 'tools'],
      dtype=object), 'price': array([20,  8, 65, 42,  7, 89], dtype=int32), 'in_stock': array([ True, False,  True,  True,  True,  True])}
```

----

### `iter_rows`

```python
iter_rows(self, batch_size: int = 1024) -> Iterator[tuple[Any, ...]]
```

Every row, a batch at a time, over one run of the relation.

The rows are read once into the engine's memory, so every batch comes from the same run and the connection is free between batches; the caller holds one batch at a time rather than the whole result.

To read a relation row by row, as a loop over `fetchone()` or `fetchmany()` did before DuckDB 2.0, iterate over `iter_rows()`.

**Parameters**

* **batch_size** : int, default: 1024

    The number of rows fetched at a time; it must be above zero.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

for row in rel.iter_rows(batch_size=4):
    print(row)
```

**Result**

```text
(1, 'jeans', 'clothing', 20, True)
(2, 't-shirt', 'clothing', 8, False)
(3, 'jacket', 'clothing', 65, True)
(4, 'hammer', 'tools', 42, True)
(5, 'screwdriver', 'tools', 7, True)
(6, 'drill', 'tools', 89, True)
```

----

### `materialize`

```python
materialize(self) -> DuckDBPyRelation
```

Execute once and return a relation over the rows, held in client memory.

The returned relation reads the held rows, so it does not see later changes to the data, and [`create_view()`](#create_view) refuses it. `materialize()` takes the place of `execute()`, which relations no longer have since DuckDB 2.0.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

duckdb_conn.sql("CREATE TABLE items AS SELECT range AS id FROM range(3)")

rel = duckdb_conn.table("items")
held = rel.materialize()

duckdb_conn.sql("INSERT INTO items VALUES (3)")

print(len(rel), len(held))
```

**Result**

```text
4 3
```

----

### `pl`

```python
pl(self, batch_size: int = 131072, *, lazy: bool = False) -> Any
```

Execute and fetch all rows as a Polars DataFrame.

Needs the `polars` package.

**Parameters**

* **batch_size** : int, default: 131072

    The number of rows of each Arrow record batch.
* **lazy** : bool, keyword-only, default: False

    Return a polars `LazyFrame` instead of a `DataFrame`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.pl(batch_size=1)
```

**Result**

```text
shape: (6, 5)
┌─────┬─────────────┬──────────┬───────┬──────────┐
│ id  ┆ item        ┆ category ┆ price ┆ in_stock │
│ --- ┆ ---         ┆ ---      ┆ ---   ┆ ---      │
│ i32 ┆ str         ┆ str      ┆ i32   ┆ bool     │
╞═════╪═════════════╪══════════╪═══════╪══════════╡
│ 1   ┆ jeans       ┆ clothing ┆ 20    ┆ true     │
│ 2   ┆ t-shirt     ┆ clothing ┆ 8     ┆ false    │
│ 3   ┆ jacket      ┆ clothing ┆ 65    ┆ true     │
│ 4   ┆ hammer      ┆ tools    ┆ 42    ┆ true     │
│ 5   ┆ screwdriver ┆ tools    ┆ 7     ┆ true     │
│ 6   ┆ drill       ┆ tools    ┆ 89    ┆ true     │
└─────┴─────────────┴──────────┴───────┴──────────┘
```

----

### `tf`

```python
tf(self) -> dict[str, Any]
```

Fetch a result as dict of TensorFlow Tensors.

Needs the `tensorflow` package.

----

### `to_arrow_reader`

```python
to_arrow_reader(self, batch_size: int = 131072) -> Any
```

Execute and return an Arrow Record Batch Reader that yields all rows.

Until the reader is read to the end or closed, the connection refuses other queries with `ResourceInUseException`. A `with` block closes the reader when it ends, also when the loop stops early. The example sets a small `batch_size` only to show several batches. To read the rows a batch at a time, as `fetch_df_chunk()` did before DuckDB 2.0, read the batches of this reader.

**Aliases**: [`arrow`](#arrow), [`fetch_arrow_reader`](#fetch_arrow_reader), [`fetch_record_batch`](#fetch_record_batch)

**Parameters**

* **batch_size** : int, default: 131072

    The number of rows of each Arrow record batch.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

with rel.select("item, price").to_arrow_reader(batch_size=2) as reader:
    for batch in reader:
        print(batch.to_pydict())
```

**Result**

```text
{'item': ['jeans', 't-shirt'], 'price': [20, 8]}
{'item': ['jacket', 'hammer'], 'price': [65, 42]}
{'item': ['screwdriver', 'drill'], 'price': [7, 89]}
```

----

### `to_arrow_table`

```python
to_arrow_table(self, batch_size: int = 131072) -> Any
```

Execute and fetch all rows as an Arrow Table.

**Aliases**: [`fetch_arrow_table`](#fetch_arrow_table)

**Parameters**

* **batch_size** : int, default: 131072

    The number of rows of each Arrow record batch.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.to_arrow_table()
```

**Result**

```text
pyarrow.Table
id: int32
item: string
category: string
price: int32
in_stock: bool
----
id: [[1,2,3,4,5,6]]
item: [["jeans","t-shirt","jacket","hammer","screwdriver","drill"]]
category: [["clothing","clothing","clothing","tools","tools","tools"]]
price: [[20,8,65,42,7,89]]
in_stock: [[true,false,true,true,true,true]]
```

----

### `to_csv`

```python
to_csv(self, file_name: str, *, sep: str | None = None, na_rep: str | None = None, header: bool | None = None, quotechar: str | None = None, escapechar: str | None = None, date_format: str | None = None, timestamp_format: str | None = None, quoting: str | int | None = None, encoding: str | None = None, compression: str | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None) -> None
```

Alias of [`write_csv()`](#write_csv).

----

### `to_df`

```python
to_df(self, *, date_as_object: bool = False) -> Any
```

Alias of [`df()`](#df).

----

### `to_dicts`

```python
to_dicts(self) -> builtins.list[dict[str, Any]]
```

Every row, as a dict keyed by column name.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.limit(2).to_dicts()
```

**Result**

```text
[{'id': 1, 'item': 'jeans', 'category': 'clothing', 'price': 20, 'in_stock': True}, {'id': 2, 'item': 't-shirt', 'category': 'clothing', 'price': 8, 'in_stock': False}]
```

----

### `to_parquet`

```python
to_parquet(self, file_name: str, *, compression: str | None = None, field_ids: object = None, row_group_size_bytes: int | str | None = None, row_group_size: int | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None, append: bool | None = None, filename_pattern: str | None = None, file_size_bytes: str | int | None = None) -> None
```

Alias of [`write_parquet()`](#write_parquet).

----

### `to_table`

```python
to_table(self, table_name: str) -> None
```

Alias of [`create()`](#create).

----

### `to_view`

```python
to_view(self, view_name: str, replace: bool = True) -> DuckDBPyRelation
```

Alias of [`create_view()`](#create_view).

----

### `torch`

```python
torch(self) -> dict[str, Any]
```

Fetch a result as dict of PyTorch Tensors.

Needs the `torch` package.

----

### `write_csv`

```python
write_csv(self, file_name: str, *, sep: str | None = None, na_rep: str | None = None, header: bool | None = None, quotechar: str | None = None, escapechar: str | None = None, date_format: str | None = None, timestamp_format: str | None = None, quoting: str | int | None = None, encoding: str | None = None, compression: str | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None) -> None
```

Write the relation object to a CSV file in 'file_name'.

Most options are the option of the same name of the [`COPY ... TO` statement]({% link docs/2.0/sql/statements/copy.md %}); `sep` is `DELIMITER`, `na_rep` is `NULL`, `quotechar` is `QUOTE`, `escapechar` is `ESCAPE`, `quoting` sets `FORCE_QUOTE` for every column and `overwrite` is `OVERWRITE_OR_IGNORE`.

**Aliases**: [`to_csv`](#to_csv)

**Parameters**

* **file_name** : str

    Path of the CSV file to write; a directory with `partition_by` or `per_thread_output`.
* **sep** : str, keyword-only, default: None

    Delimiter between the columns; a comma when omitted.
* **na_rep** : str, keyword-only, default: None

    Text written for `NULL` values; empty when omitted.
* **header** : bool, keyword-only, default: None

    Whether to write a first line with the column names; written when omitted.
* **quotechar** : str, keyword-only, default: None

    Character used to quote values; `"` when omitted.
* **escapechar** : str, keyword-only, default: None

    Character written before a quote character inside a quoted value; `"` when omitted.
* **date_format** : str, keyword-only, default: None

    [Format]({% link docs/2.0/sql/functions/dateformat.md %}) used to write dates.
* **timestamp_format** : str, keyword-only, default: None

    [Format]({% link docs/2.0/sql/functions/dateformat.md %}) used to write timestamps.
* **quoting** : str, int, keyword-only, default: None

    Quote every value: pass `csv.QUOTE_ALL`, `'ALL'` or `'FORCE'`; any other value is refused. Values are quoted only where needed when omitted.
* **encoding** : str, keyword-only, default: None

    Only UTF-8 is accepted, written `'utf-8'` or `'utf8'`.
* **compression** : str, keyword-only, default: None

    Compression of the file: `'none'`, `'gzip'` or `'zstd'`. Chosen from the file extension when omitted.
* **overwrite** : bool, keyword-only, default: None

    Whether an existing file may be replaced. Omitted or `True`, an existing file is replaced; `False` refuses an existing file with `IOException`. With `partition_by`, only `True` writes into a directory that already holds files.
* **per_thread_output** : bool, keyword-only, default: None

    Write one file per thread into the directory `file_name`, rather than one file.
* **use_tmp_file** : bool, keyword-only, default: None

    Write to a temporary file first, so a cancelled write does not leave a broken file in place of an existing one.
* **partition_by** : list[str], keyword-only, default: None

    Columns to partition the output by, written as [Hive partitioned]({% link docs/2.0/data/partitioning/partitioned_writes.md %}) directories under `file_name`.
* **write_partition_columns** : bool, keyword-only, default: None

    Whether to also write the partition columns into the files; only has an effect with `partition_by`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.write_csv("items.csv")
```

----

### `write_parquet`

```python
write_parquet(self, file_name: str, *, compression: str | None = None, field_ids: object = None, row_group_size_bytes: int | str | None = None, row_group_size: int | None = None, overwrite: bool | None = None, per_thread_output: bool | None = None, use_tmp_file: bool | None = None, partition_by: builtins.list[str] | None = None, write_partition_columns: bool | None = None, append: bool | None = None, filename_pattern: str | None = None, file_size_bytes: str | int | None = None) -> None
```

Write the relation object to a Parquet file in 'file_name'.

Each option is the option of the same name of the [`COPY ... TO` statement]({% link docs/2.0/sql/statements/copy.md %}), except `overwrite`, which is `OVERWRITE_OR_IGNORE`.

**Aliases**: [`to_parquet`](#to_parquet)

**Parameters**

* **file_name** : str

    Path of the Parquet file to write; a directory with `partition_by`, `per_thread_output` or `file_size_bytes`.
* **compression** : str, keyword-only, default: None

    Compression codec: `'uncompressed'`, `'snappy'`, `'gzip'`, `'zstd'`, `'brotli'`, `'lz4'` or `'lz4_raw'`; `'snappy'` when omitted.
* **field_ids** : dict, str, keyword-only, default: None

    The Parquet `field_id` of each column, as a dict of column name to id, or `'auto'` to assign them.
* **row_group_size_bytes** : int, str, keyword-only, default: None

    Target size of each row group, as a number of bytes or a text such as `'2MB'`.
* **row_group_size** : int, keyword-only, default: None

    Target number of rows of each row group; 122880 when omitted.
* **overwrite** : bool, keyword-only, default: None

    Whether an existing file may be replaced. Omitted or `True`, an existing file is replaced; `False` refuses an existing file with `IOException`. With `partition_by`, `True` writes into a directory that already holds files, as `append=True` does.
* **per_thread_output** : bool, keyword-only, default: None

    Write one file per thread into the directory `file_name`, rather than one file.
* **use_tmp_file** : bool, keyword-only, default: None

    Write to a temporary file first, so a cancelled write does not leave a broken file in place of an existing one.
* **partition_by** : list[str], keyword-only, default: None

    Columns to partition the output by, written as [Hive partitioned]({% link docs/2.0/data/partitioning/partitioned_writes.md %}) directories under `file_name`.
* **write_partition_columns** : bool, keyword-only, default: None

    Whether to also write the partition columns into the files; only has an effect with `partition_by`.
* **append** : bool, keyword-only, default: None

    When a generated file name already exists, choose another name instead of overwriting the file; only has an effect when several files are written. Combined with `overwrite`, it raises `BinderException`.
* **filename_pattern** : str, keyword-only, default: None

    Pattern of the names of the files written, where `{i}` is replaced by an index and `{uuid}` by a UUID; only has an effect when several files are written.
* **file_size_bytes** : int, str, keyword-only, default: None

    Start a new file in the directory `file_name` when a file grows past this size, given as a number of bytes or a text such as `'1MB'`.

**Example**

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
    SELECT *
    FROM (VALUES
        (1, 'jeans', 'clothing', 20, TRUE),
        (2, 't-shirt', 'clothing', 8, FALSE),
        (3, 'jacket', 'clothing', 65, TRUE),
        (4, 'hammer', 'tools', 42, TRUE),
        (5, 'screwdriver', 'tools', 7, TRUE),
        (6, 'drill', 'tools', 89, TRUE)
    ) AS items(id, item, category, price, in_stock)
""")

rel.write_parquet("items.parquet")
```