Parse the given SQL query and execute it against the registered frame data

Description

Usage

<SQLContext>$execute(query)

Arguments

query A valid string SQL query.

Value

A polars LazyFrame

Examples

library("polars")

# Declare frame data and register with a SQLContext:
df <- pl$DataFrame(
  title = c(
    "The Godfather",
    "The Dark Knight",
    "Schindler's List",
    "Pulp Fiction",
    "The Shawshank Redemption"
  ),
  release_year = c(1972, 2008, 1993, 1994, 1994),
  budget = c(6 * 1e6, 185 * 1e6, 22 * 1e6, 8 * 1e6, 25 * 1e6),
  gross = c(134821952, 533316061, 96067179, 107930000, 28341469),
  imdb_score = c(9.2, 9, 8.9, 8.9, 9.3)
)

ctx <- pl$SQLContext(films = df)
ctx$execute(
  "
     SELECT title, release_year, imdb_score
     FROM films
     WHERE release_year > 1990
     ORDER BY imdb_score DESC
     "
)$collect()

#> shape: (4, 3)
#> ┌──────────────────────────┬──────────────┬────────────┐
#> │ title                    ┆ release_year ┆ imdb_score │
#> │ ---                      ┆ ---          ┆ ---        │
#> │ str                      ┆ f64          ┆ f64        │
#> ╞══════════════════════════╪══════════════╪════════════╡
#> │ The Shawshank Redemption ┆ 1994.0       ┆ 9.3        │
#> │ The Dark Knight          ┆ 2008.0       ┆ 9.0        │
#> │ Schindler's List         ┆ 1993.0       ┆ 8.9        │
#> │ Pulp Fiction             ┆ 1994.0       ┆ 8.9        │
#> └──────────────────────────┴──────────────┴────────────┘

# Execute a GROUP BY query:
ctx$execute(
  "
  SELECT
       MAX(release_year / 10) * 10 AS decade,
       SUM(gross) AS total_gross,
       COUNT(title) AS n_films,
  FROM films
  GROUP BY (release_year / 10) -- decade
  ORDER BY total_gross DESC
  "
)$collect()

#> shape: (4, 3)
#> ┌────────┬──────────────┬─────────┐
#> │ decade ┆ total_gross  ┆ n_films │
#> │ ---    ┆ ---          ┆ ---     │
#> │ f64    ┆ f64          ┆ u32     │
#> ╞════════╪══════════════╪═════════╡
#> │ 2008.0 ┆ 5.33316061e8 ┆ 1       │
#> │ 1994.0 ┆ 1.36271469e8 ┆ 2       │
#> │ 1972.0 ┆ 1.34821952e8 ┆ 1       │
#> │ 1993.0 ┆ 9.6067179e7  ┆ 1       │
#> └────────┴──────────────┴─────────┘