| DATUI-QUERY(7) | Miscellaneous | DATUI-QUERY(7) |
datui-query - the q syntax of the datui command line
select [columns] [by groups] [where conditions]
The grammar of q, typed on the command line (:, then q:). datui's q is a subset of the q language and evaluates right to left. Query data walks through it. Every example below runs on the public dataset its block names; open that dataset from Example datasets on the home screen.
q and kdb+ are trademarks of KX Systems. datui is not affiliated with or endorsed by KX.
Each part in brackets is optional; replace it with a list of expressions:
select [columns] [by group_columns] [where conditions]
Use clauses in the order shown, at most once each. Misplaced or repeated clauses and extra tokens after an expression are errors.
name : expression names an expression. The left side is the new column or group name, an identifier (total) or col["name with spaces"]; the right side is any expression (column reference, literal, arithmetic, function call).
On the flights dataset (see DATASETS):
select carrier, flight, gain: dep_delay - arr_delay select route: dest, flight select flights: count flight by airline: carrier, long: distance > 1000
Assignment works in both select and by. In by it defines computed group keys or renames.
Identifiers cannot contain spaces. For columns (or aliases) with spaces, use col["..."] with a quoted string, or col[identifier] for a name without spaces. The same syntax works in select, by and where.
On the football dataset (see DATASETS):
select col["Team 1"], col["Team 2"], FT select home: col["Team 1"]
A column named like a function (count, log, var) is read as the function when something follows it, so select log + 1 is log(+1). Write col["log"] + 1.
There is no operator precedence. Expressions are parsed right-to-left: the leftmost binary operator is the root, and everything to its right is parsed first as a unit.
Put the operation you want done first on the right, or use () to override grouping:
On the flights dataset (see DATASETS):
select gain: (dep_delay - arr_delay) * 60 select carrier, flight where (dep_delay > 60) | arr_delay > 60
& (or the word and) and | (or or) are operators like + or >: no precedence, right to left, in select, by and where. Only the rightmost comparison can go without parentheses.
On two booleans they are and and or. On numbers they are q's lesser and greater: & is the smaller of the two, | the larger, row by row. A boolean with a number counts as 0 or 1 in the number's type, and a whole number next to an integer column is an integer, so a & 5 stays an integer. As in q, any value exceeds a null: & with a null is null, and | with a null is the other side.
In a where condition, a null drops the row either way.
On the flights dataset (see DATASETS):
select carrier, flight where (origin = "JFK") & dep_delay > 60 select carrier, flight where (dep_delay > 60) or (arr_delay > 60) and distance > 1000 select flight, late: 0 | dep_delay, capped: 120 & arr_delay
0 | dep_delay floors early departures at 0, and a cancelled flight's null delay becomes 0 too; 120 & arr_delay caps delays at two hours and keeps a missing delay null.
not of a number is whether it is 0: not x is x = 0.
&& and || are errors: write & or |.
By uses the same comma-separated list and name : expression rules as select. Aggregation functions (avg, min, max, count, sum, std, med, nunique, var, dev) can be written fn[expr] or fn expr; brackets are optional. wavg goes between its operands: w wavg x.
An unaliased aggregate of a single column is named {fn}_{column}, so select avg dep_delay, max dep_delay by carrier yields avg_dep_delay and max_dep_delay; an explicit alias (total: sum[distance]) overrides it.
The where clause is a list of conditions separated by ,. Each one filters the rows the ones before it kept, in order, so an aggregate in a later condition is over those rows only. Within a condition, combine tests with & and | (above).
Flights later than average, then of those, longer than their average:
On the flights dataset (see DATASETS):
select carrier, flight, dep_delay, distance where dep_delay > avg dep_delay, distance > avg distance
The second avg distance is over the late flights only. Written as one condition, (dep_delay > avg dep_delay) & distance > avg distance, both averages are over every row.
A , inside parentheses is an error: in q it joins lists, which datui does not support. Combine the conditions with & or | instead. A , with no condition on one side, as in where a > 1, or ,,, is an error too.
The where clause takes conditions only: no name: expression assignment.
A timestamp literal compared with a column that has a time zone is read as a clock time in that zone. A clock time repeated when clocks fall back means its first instant.
Quoted text is a string, never a date: where d = "2024.01.01" on a date column is an error that names the literal to write, here 2024.01.01. Time and duration columns have no literal; compare t.hour, t.minute or t.second with a number.
Either side of a comparison can be a column, a literal or an expression: where a = 10, where created_at.date > other_date_col.
q's infix words, parsed right-to-left like every other operator.
Because evaluation is right-to-left, x mod 2 in [1] is x mod (2 in [1]). Write (x mod 2) in [1] or 1 = x mod 2. not name in ["Mary"] negates the whole test.
The words are operators only between two operands. A column named in, mod, and or or still works on its own or at the start of an expression, and col["in"] always does.
For columns of type Date or Datetime (with or without timezone), dot notation extracts components: column_ref.accessor.
Apply to String columns:
part, slice, replace, strip, to_date and to_datetime also work on number and date columns, read as their text: NOAA's DATE parses whether it was read as 20240101 text or as an integer. A value that does not parse becomes null.
Accessors chain left to right: FT.part["–", 0].int. Arguments are literals, quoted text or numbers, and a wrong number of them is an error naming the accessor. To apply an accessor to an aggregate or an expression, wrap it in parentheses: (avg dep_delay).round[1].
An accessor result is automatically aliased to {column}_{accessor}, so timestamp.date becomes timestamp_date.
time_hour in NYC flights is a UTC datetime:
On the flights dataset (see DATASETS):
select day: time_hour.date select time_hour.date, time_hour.year select flight, time_hour.time select time_hour, time_hour.month, time_hour.dow by time_hour.year select delay: arr_delay^dep_delay select tailnum.len, tailnum.upper, time_hour.format["%Y-%m"] select where time_hour.date > 2013.06.30 select where time_hour.month = 12, time_hour.dow = 1 select where dest.ends_with["A"] select where null dep_time select where not null dep_time
tpep_pickup_datetime in NYC yellow taxis is a datetime with no time zone:
On the taxis dataset (see DATASETS):
select where tpep_pickup_datetime > 2025.01.15T14:30:00.123456
Functions are used for aggregation (typically in select with by) and for logic in where. Write fn[expr] or fn expr; brackets are optional.
var, dev and std divide by n − 1, where q's var and dev divide by n.
NYC yellow taxis:
On the taxis dataset (see DATASETS):
select trips: count VendorID by tpep_pickup_datetime.hour select trips: count fare_amount by b: 5 xbar fare_amount where fare_amount > 0, fare_amount < 100
Premier League:
On the football dataset (see DATASETS):
select home: FT.part["–", 0].int, away: FT.part["–", 1].int select d: Date.replace["(P)", ""].strip.to_date["%a %b %d %Y"] select matches: count Round by m: Date.replace["(P)", ""].strip.to_date["%a %b %d %Y"].month
US baby names:
On the names dataset (see DATASETS):
select total: sum n by name where name in ["Emma", "Jennifer", "Olivia"] select total: sum n by decade: 10 xbar year where name = "Jennifer" select year, log_n: (log n).round[2] where name = "Emma"
NYC flights:
On the flights dataset (see DATASETS):
select mean_delay: (avg dep_delay).round[1] by hour select distinct carrier, origin select planes: nunique tailnum by carrier select delay: distance wavg arr_delay by carrier select dep_time, minute: dep_time mod 100 select sd: sqrt var dep_delay by origin
Food nutrition:
On the food dataset (see DATASETS):
select items: count item by restaurant where item like "*Chicken*" select restaurant, item where item like "*Chicken*"
Palmer penguins:
On the penguins dataset (see DATASETS):
select mean_mass_g: avg body_mass_g by species select species, island, bill_ratio: (bill_length_mm % bill_depth_mm).round[2] where not null bill_length_mm
The examples run on the Example datasets that come with datui (on the home screen). Open one, press /, and type the query.
datui https://vincentarelbundock.github.io/Rdatasets/csv/nycflights13/flights.csv
datui https://raw.githubusercontent.com/footballcsv/england/de3945297668d7114006a8ca1c4c3740010b111c/2020s/2020-21/eng.1.csv
datui https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet
datui https://raw.githubusercontent.com/rfordatascience/tidytuesday/8bfa9d9a7279192cb41cab041f426f2aacefde91/data/2022/2022-03-22/babynames.csv
datui https://vincentarelbundock.github.io/Rdatasets/csv/openintro/fastfood.csv
datui https://vincentarelbundock.github.io/Rdatasets/csv/palmerpenguins/penguins.csv
datui(1)
The datui documentation: <https://derekwisong.github.io/datui/>
Report bugs at <https://github.com/derekwisong/datui/issues>.
Derek Wisong and the datui contributors.
Copyright © 2026 Derek Wisong
datui is free software under the MIT License.
| 2026-10-16 | datui 0.4.12 |