phibble

Data & Workflow

Statistics, Modeling, & Visualization

Query a database table

Summarize 2013’s hourly weather at the three New York City airports in an SQLite table, with phibble’s methods compiled to one SQL query that the database runs. The table holds nycflights13’s weather data from the PHPstats datasets: the script reads the CSV and loads five columns into an in-memory SQLite database before the code shown. It needs the pdo_sqlite extension.

use Phibble\Sql\SqlTable;use function Phibble\tbl;use function Phibble\Expr\{col, desc, max, mean, n, sum};$weather = tbl($pdo, 'weather');$airports = $weather |>    (fn (SqlTable $t) => $t->filter(col('temp')->isNa()->not())) |>    (fn (SqlTable $t) => $t->groupBy(['origin'])) |>    (fn (SqlTable $t) => $t->summarize([        'hours' => n(),        'temp' => mean(col('temp')),        'precip' => sum(col('precip')),        'gust' => max(col('wind_gust'), na_rm: true),    ])) |>    (fn (SqlTable $t) => $t->arrange([desc(col('precip'))]));echo $airports->showQuery(), "\n\n";echo $airports->collect(), "\n";
use function Phibble\tbl;use function Phibble\Expr\{col, desc, max, mean, n, sum};$weather = tbl($pdo, 'weather');$airports = $weather    ->filter(col('temp')->isNa()->not())    ->groupBy(['origin'])    ->summarize([        'hours' => n(),        'temp' => mean(col('temp')),        'precip' => sum(col('precip')),        'gust' => max(col('wind_gust'), na_rm: true),    ])    ->arrange([desc(col('precip'))]);echo $airports->showQuery(), "\n\n";echo $airports->collect(), "\n";
The SQL from showQuery() 
SELECT "origin", "hours", "temp", "precip", "gust" FROM (SELECT "origin", "hours", "temp", "precip", "gust", ROW_NUMBER() OVER (ORDER BY ("origin" IS NULL), "origin") AS "__phibble_row" FROM (SELECT "origin" AS "origin", COUNT(*) AS "hours", (CASE WHEN COUNT(*) > COUNT("temp") THEN NULL ELSE (CASE WHEN COUNT("temp") = 0 THEN NULL ELSE AVG(CAST("temp" AS REAL)) END) END) AS "temp", (CASE WHEN COUNT(*) > COUNT("precip") THEN NULL ELSE COALESCE(SUM("precip"), 0) END) AS "precip", (CASE WHEN COUNT("wind_gust") = 0 THEN -9e999 ELSE MAX("wind_gust") END) AS "gust" FROM (SELECT * FROM (SELECT *, rowid AS "__phibble_row" FROM "weather") AS q1 WHERE (NOT ("temp" IS NULL))) AS q2 GROUP BY "origin") AS q3) AS q4 ORDER BY ("precip" IS NULL), "precip" DESC, ("__phibble_row" IS NULL), "__phibble_row"
# A tibble: 3 × 5
  origin hours  temp precip  gust
  <chr>  <int> <dbl>  <dbl> <dbl>
1 EWR     8702  55.5   43.8  58.7
2 LGA     8706  55.8   38.1  62.1
3 JFK     8706  54.5   34.7  66.7
  • Each airport has about 8,700 hourly readings with a temperature.
  • Newark (EWR) had the most precipitation, 43.8 inches over the year, and JFK the least, 34.7 inches. Mean temperatures differ by about 1 °F, and JFK had the strongest gust, 66.7 mph.
  • max(..., na_rm: true) skips the hours without a gust; mean() and sum() need no na_rm here, because the filter keeps only hours with a temperature and every such hour has a precipitation value.
  • Table methods only build the query. Nothing runs on the database until collect() returns a DataFrame. showQuery() shows the SQL with ? placeholders.
  • The same code runs on PostgreSQL and MySQL.

Runnable scripts: PHP 8.5+ · PHP 8.3–8.4.

Reference: SQL tables · phplyr.