Reusing Part of a Query

While using TidierDB, you may need to generate part of a query and reuse it multiple times. from_query() enables a query portion to be reused multiple times as shown below.

import TidierDB as DB
con = DB.connect(duckdb())
mtcars_path = "https://gist.githubusercontent.com/seankross/a412dfbd88b3db70b74b/raw/5f23f993cd87c283ce766e7ac6b329ee7cc2e1d1/mtcars.csv"

Start a query to analyze fuel efficiency by number of cylinders. However, to further build on this query later, end the chain without using @show_query or @collect

query = DB.@chain DB.db_table(con, mtcars_path) begin
    DB.@group_by cyl
    DB.@summarize begin
        across(mpg, (mean, minimum, maximum))
        num_cars = n()
        end
    DB.@mutate begin
        efficiency = case_when(
            mean_mpg >= 25, "High",
            mean_mpg >= 15, "Moderate",
            "Low" )
       end
end;

Now, from_query will allow you to reuse the query to calculate the average horsepower for each efficiency category

DB.@chain DB.from_query(query) begin
   DB.@left_join(mtcars2, cyl, cyl)
   DB.@group_by(efficiency)
   DB.@summarize(avg_hp = mean(hp))
   DB.@collect
end

2×2 DataFrame
 Row │ efficiency  avg_hp
     │ String?     Float64?
─────┼──────────────────────
   1 │ Moderate    180.238
   2 │ High         82.6364

Reuse the query again to find the car with the highest MPG for each cylinder category

DB.@chain DB.from_query(query) begin
   DB.@left_join(mtcars2, cyl, cyl)
   DB.@group_by cyl
   DB.@slice_max(mpg)
   DB.@select model cyl mpg
   DB.@collect
end

3×3 DataFrame
 Row │ model             cyl     mpg
     │ String?           Int64?  Float64?
─────┼────────────────────────────────────
   1 │ Pontiac Firebird       8      19.2
   2 │ Toyota Corolla         4      33.9
   3 │ Hornet 4 Drive         6      21.4

Preview or save an intermediate table

While querying a dataset, you may wish to see an intermediate table, or even save it. You can use @aside and from_query(_), illustrated below, to do just that. While we opted to print the results in this simple example below, we could have saved them by using name = DB.@chain...

import ClickHouse;
conn = conn = DB.connect(DB.clickhouse(); host="localhost", port=19000, database="default", user="default", password="")
path = "https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset/resolve/refs%2Fconvert%2Fparquet/default/train/0000.parquet"
DB.@chain DB.db_table(conn, path) begin
   DB.@count(cyl)
   @aside println(DB.@chain DB.from_query(_) DB.@head(5) DB.@collect)
   DB.@arrange(desc(count))
   DB.@collect
end

5×2 DataFrame
 Row │ artists  count
     │ String?  UInt64
─────┼─────────────────
   1 │ missing       1
   2 │ Wizo          3
   3 │ MAGIC!        3
   4 │ Macaco        1
   5 │ SOYOU         1
31438×2 DataFrame
   Row │ artists          count
       │ String?          UInt64
───────┼─────────────────────────
     1 │ The Beatles         279
     2 │ George Jones        271
     3 │ Stevie Wonder       236
     4 │ Linkin Park         224
     5 │ Ella Fitzgerald     222
     6 │ Prateek Kuhad       217
     7 │ Feid                202
   ⋮   │        ⋮           ⋮
 31432 │ Leonard               1
 31433 │ marcos g              1
 31434 │ BLVKSHP               1
 31435 │ Memtrix               1
 31436 │ SOYOU                 1
 31437 │ Macaco                1
 31438 │ missing               1
               31424 rows omitted

---

This page was generated using Literate.jl.