Execute a SQL query against the LazyFrame

Description

Usage

<LazyFrame>$sql(query, ..., table_name = "self")

Arguments

query SQL query to execute.

… These dots are for future extensions and must be empty.

table_name Optionally provide an explicit name for the table that represents the calling frame (defaults to “self”).

Details

The calling frame is automatically registered as a table in the SQL context under the name “self”.

More control over registration and execution behaviour is available by using the SQLContext object.

Value

A polars LazyFrame

Examples

library("polars")

lf1 <- pl$LazyFrame(a = 1:3, b = 6:8, c = c("z", "y", "x"))

# Query the LazyFrame using SQL:
lf1$sql("SELECT c, b FROM self WHERE a > 1")$collect()

#> shape: (2, 2)
#> ┌─────┬─────┐
#> │ c   ┆ b   │
#> │ --- ┆ --- │
#> │ str ┆ i32 │
#> ╞═════╪═════╡
#> │ y   ┆ 7   │
#> │ x   ┆ 8   │
#> └─────┴─────┘

# Apply SQL transforms (aliasing "self" to "frame") then filter natively
# (you can freely mix SQL and native operations):
lf1$sql(
  query = "
       SELECT
          a,
          (a % 2 == 0) AS a_is_even,
          (b::float4 / 2) AS 'b/2',
          CONCAT_WS(':', c, c, c) AS c_c_c
       FROM frame
       ORDER BY a
 ",
  table_name = "frame",
)$filter(!pl$col("c_c_c")$str$starts_with("x"))$collect()

#> shape: (2, 4)
#> ┌─────┬───────────┬─────┬───────┐
#> │ a   ┆ a_is_even ┆ b/2 ┆ c_c_c │
#> │ --- ┆ ---       ┆ --- ┆ ---   │
#> │ i32 ┆ bool      ┆ f32 ┆ str   │
#> ╞═════╪═══════════╪═════╪═══════╡
#> │ 1   ┆ false     ┆ 3.0 ┆ z:z:z │
#> │ 2   ┆ true      ┆ 3.5 ┆ y:y:y │
#> └─────┴───────────┴─────┴───────┘