.\" Generated by gen_docs from crates/datui-cli. Do not edit.
.TH DATUI\-QUERY 7 2026-10-06 "datui 0.4.1" "Miscellaneous"
.nr HY 0
.ds AD l
.SH NAME
datui\-query \- the q syntax of the datui command line
.SH SYNOPSIS
.nf
\fBselect\fR [\fIcolumns\fR] [\fBby\fR \fIgroups\fR] [\fBwhere\fR \fIconditions\fR]
.fi
.SH DESCRIPTION
.SS In brief
.TP
select [cols] [by groups] [where conds]
Each part optional
.TP
select a, b where a > 10, b < 5
In where, , is and; | is or
.TP
select avg price, n:count id by region
Aggregate by group
.TP
total:a+b col["first name"]
Name a column; a name with spaces
.TP
1/c+a is 1/(c+a)
Right to left: 100 < (a+b)*2
.TP
date.year 2024.01.31 city.contains["York"]
Accessors and dates
.TP
name in ["Ann", "Bo"] item like "*ham*"
Membership and patterns
.PP
The grammar of q on the command line (\fB:\fR, then \fBq:\fR): a subset of the q language, evaluated right to left. Query data walks through it. Every example below runs on the public dataset its block names; open that dataset from \fBExample datasets\fR on the home screen.
.PP
q and kdb+ are trademarks of KX Systems. datui is not affiliated with or endorsed by KX.
.SH "STRUCTURE OF A QUERY"
Each part in brackets is optional; replace it with a list of expressions:
.PP
.RS 4
.EX
select [columns] [by group_columns] [where conditions]
.EE
.RE
.TP
\fBselect\fR
Required. Alone it means all columns; otherwise a comma-separated list of column expressions
.TP
\fBby\fR
Optional. Grouping and aggregation
.TP
\fBfrom df\fR
Optional. The table on screen, as in q and like SQL's \fBFROM df\fR; no other name
.TP
\fBwhere\fR
Optional. Filtering
.PP
Use clauses in the order shown, at most once each. Misplaced or repeated clauses and extra tokens after an expression are errors.
.SH "THE : ASSIGNMENT (ALIASING)"
\fBname : expression\fR names an expression. The left side is the new column or group name, an identifier (\fBtotal\fR) or \fBcol["name with spaces"]\fR; the right side is any expression (column reference, literal, arithmetic, function call).
.PP
On the \fBflights\fR dataset (see DATASETS):
.PP
.RS 4
.EX
select carrier, flight, gain: dep_delay \- arr_delay
select route: dest, flight
select flights: count flight by airline: carrier, long: distance > 1000
.EE
.RE
.PP
Assignment works in both select and by. In by it defines computed group keys or renames.
.SH "COLUMNS WITH SPACES IN THEIR NAMES"
Identifiers cannot contain spaces. For columns (or aliases) with spaces, use \fBcol["..."]\fR with a quoted string, or \fBcol[identifier]\fR for a name without spaces. The same syntax works in select, by and where.
.PP
On the \fBfootball\fR dataset (see DATASETS):
.PP
.RS 4
.EX
select col["Team 1"], col["Team 2"], FT
select home: col["Team 1"]
.EE
.RE
.PP
A column named like a function (\fBcount\fR, \fBlog\fR, \fBvar\fR) is read as the function when something follows it, so \fBselect log + 1\fR is \fBlog(+1)\fR. Write \fBcol["log"] + 1\fR.
.SH "RIGHT-TO-LEFT EXPRESSION PARSING"
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.
.IP \(bu 4
\fBa + b * c\fR \(-> \fBa + (b * c)\fR
.IP \(bu 4
\fBa * b + c\fR \(-> \fBa * (b + c)\fR, not \fB(a * b) + c\fR
.IP \(bu 4
\fB(a + b) * 2 > 100\fR \(-> \fB(a + b) * (2 > 100)\fR; write the comparison first, \fB100 < (a + b) * 2\fR
.PP
Put the operation you want done first on the right, or use \fB()\fR to override grouping:
.PP
On the \fBflights\fR dataset (see DATASETS):
.PP
.RS 4
.EX
select gain: (dep_delay \- arr_delay) * 60
select carrier, flight where (dep_delay > 60) | (arr_delay > 60)
.EE
.RE
.PP
Parentheses also matter for \fB,\fR and \fB|\fR in where: splitting on comma and pipe respects nesting, so you can wrap ORs in \fB()\fR and combine them with commas. See Where clause.
.SH "SELECT CLAUSE"
.IP \(bu 4
\fBselect\fR \(em all columns, no expressions
.IP \(bu 4
\fBselect a, b, c\fR \(em those columns or expressions, in order, separated by \fB,\fR
.IP \(bu 4
\fBselect a, b: x + y, c\fR \(em columns and aliased expressions
.IP \(bu 4
\fBselect distinct carrier, origin\fR \(em only the distinct rows of the result; \fBselect distinct\fR alone drops duplicate rows. A column named \fBdistinct\fR is \fBcol["distinct"]\fR, or plain \fBdistinct\fR before \fB,\fR \fB:\fR \fB.\fR or an operator
.SH "BY CLAUSE (GROUPING AND AGGREGATION)"
.IP \(bu 4
\fBby origin, dest\fR \(em group by those columns; non-group columns become list columns, and the UI supports drill-down
.IP \(bu 4
\fBby carrier, long: distance > 1000\fR \(em group by a column and a computed expression
.IP \(bu 4
\fBselect avg dep_delay, min dep_delay by carrier\fR \(em aggregations per group; \fBEnter\fR on a row drills down to the rows behind it
.PP
By uses the same comma-separated list and \fBname : expression\fR rules as select. Aggregation functions (\fBavg\fR, \fBmin\fR, \fBmax\fR, \fBcount\fR, \fBsum\fR, \fBstd\fR, \fBmed\fR, \fBnunique\fR, \fBvar\fR, \fBdev\fR) can be written \fBfn[expr]\fR or \fBfn expr\fR; brackets are optional. \fBwavg\fR goes between its operands: \fBw wavg x\fR.
.PP
An unaliased aggregate of a single column is named \fB{fn}_{column}\fR, so \fBselect avg dep_delay, max dep_delay by carrier\fR yields \fBavg_dep_delay\fR and \fBmax_dep_delay\fR; an explicit alias (\fBtotal: sum[distance]\fR) overrides it.
.SH "WHERE CLAUSE: , AND |"
The where clause combines conditions with two separators:
.IP \(bu 4
\fB,\fR \(em AND. Each comma-separated segment is one ANDed condition.
.IP \(bu 4
\fB|\fR \(em OR. Within one segment, \fB|\fR separates alternatives that are ORed.
.PP
The where part is split on \fB,\fR first (respecting \fB()\fR and \fB[]\fR), then each segment on \fB|\fR, so \fB,\fR has broader scope than \fB|\fR:
.TP
\fBwhere a > 10, b < 2\fR
\fB(a > 10) AND (b < 2)\fR
.TP
\fBwhere a > 10 | a < 5\fR
\fB(a > 10) OR (a < 5)\fR
.TP
\fBwhere a > 10 | a < 5, b = 2\fR
\fB(a > 10 OR a < 5) AND (b = 2)\fR
.TP
\fBA, B | C\fR
\fBA AND (B OR C)\fR
.TP
\fBA | B, C | D\fR
\fB(A OR B) AND (C OR D)\fR
.PP
The where clause takes conditions only: no \fBname: expression\fR assignment.
.PP
For more complex logic, wrap OR subexpressions in \fB()\fR \(em parentheses keep \fB|\fR inside one AND term \(em and separate the groups with \fB,\fR.
.SH "OPERATORS AND LITERALS"
.TP
Arithmetic
\fB+\fR \fB\-\fR \fB*\fR \fB/\fR \fB%\fR (\fB/\fR and \fB%\fR both divide; \fB%\fR is not modulo, \fBmod\fR is)
.TP
Equal, not equal
\fB=\fR, \fB!=\fR, \fB<>\fR (same as \fB!=\fR)
.TP
Ordering
\fB<\fR \fB>\fR \fB<=\fR \fB>=\fR
.TP
Coalesce
\fB^\fR \(em first non-null, left to right; \fBa^b^c\fR = coalesce(a, b, c), binding right-to-left as \fBa^(b^c)\fR
.TP
Numbers
\fB42\fR, \fB3.14\fR
.TP
Strings
\fB"hello"\fR, \fB\e"\fR for an embedded quote
.TP
Date literals
\fB2021.01.01\fR (YYYY.MM.DD)
.TP
Timestamp literals
\fB2021.\:01.\:15T14:30:00.\:123456\fR (YYYY.\:MM.\:DDTHH:MM:SS[.\:fff.\:.\:.\:]); fractional-second digits set precision: 1\(en3 = ms, 4\(en6 = \(*ms, 7\(en9 = ns
.PP
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.
.PP
Quoted text is a string, never a date: \fBwhere d = "2024.01.01"\fR on a date column is an error that names the literal to write, here \fB2024.01.01\fR. Time and duration columns have no literal; compare \fBt.hour\fR, \fBt.minute\fR or \fBt.second\fR with a number.
.PP
Either side of a comparison can be a column, a literal or an expression: \fBwhere a = 10\fR, \fBwhere created_at.date > other_date_col\fR.
.SH "WORD OPERATORS"
q's infix words, parsed right-to-left like every other operator.
.TP
\fBx in [a, b, c]\fR
Result: True where \fBx\fR equals one of the values; the right side is a bracketed list
.br
Example: \fBselect total: sum n by name where name in ["Emma", "Jennifer", "Olivia"]\fR
.TP
\fBx like "pattern"\fR
Result: True where the whole value matches: \fB*\fR is any run of characters, \fB?\fR one character; case-sensitive
.br
Example: \fBselect restaurant, item where item like "*Chicken*"\fR
.TP
\fBsize xbar x\fR
Result: \fBx\fR rounded down to a multiple of \fBsize\fR, for buckets; a whole-number size keeps integers integral
.br
Example: \fBselect trips: count fare_amount by b: 5 xbar fare_amount\fR
.TP
\fBx mod n\fR
Result: Remainder, with the sign of \fBn\fR (\fB\-7 mod 3\fR is \fB2\fR)
.br
Example: \fBselect dep_time, minute: dep_time mod 100\fR
.TP
\fBw wavg x\fR
Result: Average of \fBx\fR weighted by \fBw\fR, an aggregate; pairs where either is null are skipped
.br
Example: \fBselect delay: distance wavg arr_delay by carrier\fR
.PP
Because evaluation is right-to-left, \fBx mod 2 in [1]\fR is \fBx mod (2 in [1])\fR. Write \fB(x mod 2) in [1]\fR or \fB1 = x mod 2\fR. \fBnot name in ["Mary"]\fR negates the whole test.
.PP
The words are operators only between two operands. A column named \fBin\fR or \fBmod\fR still works on its own or at the start of an expression, and \fBcol["in"]\fR always does.
.SH "DATE AND DATETIME ACCESSORS"
For columns of type Date or Datetime (with or without timezone), dot notation extracts components: \fBcolumn_ref.accessor\fR.
.TP
\fBdate\fR
Date part (year-month-day); Datetime only
.br
Result: Date
.TP
\fBtime\fR
Time part (Polars Time type); Datetime only
.br
Result: Time
.TP
\fByear\fR
Year
.br
Result: Int32
.TP
\fBmonth\fR
Month (1\(en12)
.br
Result: Int8
.TP
\fBweek\fR
Week number
.br
Result: Int8
.TP
\fBquarter\fR
Quarter (1\(en4)
.br
Result: Int8
.TP
\fBday\fR
Day of month (1\(en31)
.br
Result: Int8
.TP
\fBdoy\fR
Day of year (1\(en366)
.br
Result: Int16
.TP
\fBdow\fR
Day of week (1=Monday \[u2026] 7=Sunday, ISO)
.br
Result: Int8
.TP
\fBhour\fR
Hour (0\(en23); Datetime and Time
.br
Result: Int8
.TP
\fBminute\fR
Minute (0\(en59); Datetime and Time
.br
Result: Int8
.TP
\fBsecond\fR
Second (0\(en59); Datetime and Time
.br
Result: Int8
.TP
\fBmonth_start\fR
First day of month, at midnight for Datetime
.br
Result: Date/Datetime
.TP
\fBmonth_end\fR
Last day of month
.br
Result: Date/Datetime
.TP
\fBformat["fmt"]\fR
Format as string (chrono strftime, e.g. \fB"%Y\-%m"\fR)
.br
Result: String
.SS "String accessors"
Apply to String columns:
.TP
\fBlen\fR
Character length
.br
Result: Int32
.TP
\fBupper\fR
Uppercase
.br
Result: String
.TP
\fBlower\fR
Lowercase
.br
Result: String
.TP
\fBstarts_with["x"]\fR
True if the string starts with \fBx\fR
.br
Result: Boolean
.TP
\fBends_with["x"]\fR
True if the string ends with \fBx\fR
.br
Result: Boolean
.TP
\fBcontains["x"]\fR
True if the string contains \fBx\fR
.br
Result: Boolean
.TP
\fBpart[sep, n]\fR
Split on \fBsep\fR and take piece \fBn\fR, counting from 0; negative counts from the end; past the last piece is null
.br
Result: String
.TP
\fBslice[start, len]\fR
\fBlen\fR characters from \fBstart\fR (0-based; negative counts from the end); without \fBlen\fR, to the end
.br
Result: String
.TP
\fBreplace[from, to]\fR
Every \fBfrom\fR replaced with \fBto\fR, literally
.br
Result: String
.TP
\fBstrip\fR
Leading and trailing whitespace removed
.br
Result: String
.TP
\fBto_date["fmt"]\fR
Parse with a chrono format such as \fB"%Y%m%d"\fR; without a format, Polars infers it
.br
Result: Date
.TP
\fBto_datetime["fmt"]\fR
As \fBto_date\fR, for date and time: \fB"%Y\-%m\-%d %H:%M"\fR
.br
Result: Datetime
.PP
\fBpart\fR, \fBslice\fR, \fBreplace\fR, \fBstrip\fR, \fBto_date\fR and \fBto_datetime\fR also work on number and date columns, read as their text: NOAA's \fBDATE\fR parses whether it was read as \fB20240101\fR text or as an integer. A value that does not parse becomes null.
.SS "Number and conversion accessors"
.TP
\fBround[n]\fR
Round to \fBn\fR decimals, halves away from zero; \fBround\fR alone rounds to a whole number
.br
Result: Number
.TP
\fBint\fR
Convert; text that is not a whole number becomes null
.br
Result: Int64
.TP
\fBfloat\fR
Convert; text that is not a number becomes null
.br
Result: Float64
.TP
\fBstr\fR
Convert to text
.br
Result: String
.PP
Accessors chain left to right: \fBFT.part["\(en", 0].int\fR. 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: \fB(avg dep_delay).round[1]\fR.
.PP
An accessor result is automatically aliased to \fB{column}_{accessor}\fR, so \fBtimestamp.date\fR becomes \fBtimestamp_date\fR.
.SS "Examples"
\fBtime_hour\fR in NYC flights is a UTC datetime:
.PP
On the \fBflights\fR dataset (see DATASETS):
.PP
.RS 4
.EX
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
.EE
.RE
.PP
\fBtpep_pickup_datetime\fR in NYC yellow taxis is a datetime with no time zone:
.PP
On the \fBtaxis\fR dataset (see DATASETS):
.PP
.RS 4
.EX
select where tpep_pickup_datetime > 2025.01.15T14:30:00.123456
.EE
.RE
.SH "FUNCTIONS"
Functions are used for aggregation (typically in select with by) and for logic in where. Write \fBfn[expr]\fR or \fBfn expr\fR; brackets are optional.
.SS "Aggregation functions"
.TP
\fBavg\fR
Average
.br
Aliases: \fBmean\fR
.br
Example: \fBselect avg[dep_delay] by carrier\fR
.TP
\fBmin\fR
Minimum
.br
Example: \fBselect min[dep_delay] by origin\fR
.TP
\fBmax\fR
Maximum
.br
Example: \fBselect max[distance] by carrier\fR
.TP
\fBcount\fR
Count of non-null values
.br
Example: \fBselect count[dep_time] by origin\fR
.TP
\fBsum\fR
Sum
.br
Example: \fBselect sum[distance] by month\fR
.TP
\fBfirst\fR
First value in group
.br
Example: \fBselect first[dep_time] by day\fR
.TP
\fBlast\fR
Last value in group
.br
Example: \fBselect last[dep_time] by day\fR
.TP
\fBstd\fR
Standard deviation (sample)
.br
Aliases: \fBstddev\fR, \fBdev\fR
.br
Example: \fBselect dev dep_delay by origin\fR
.TP
\fBvar\fR
Variance (sample)
.br
Example: \fBselect var dep_delay by origin\fR
.TP
\fBnunique\fR
Count of distinct values
.br
Example: \fBselect planes: nunique tailnum by carrier\fR
.TP
\fBwavg\fR
Weighted average, written \fBw wavg x\fR
.br
Example: \fBselect delay: distance wavg arr_delay by carrier\fR
.TP
\fBmed\fR
Median
.br
Aliases: \fBmedian\fR
.br
Example: \fBselect med[air_time] by dest\fR
.TP
\fBlen\fR
String length (chars)
.br
Aliases: \fBlength\fR
.br
Example: \fBselect len[tailnum]\fR
.SS "Logic functions"
.TP
\fBnot\fR
Logical negation
.br
Example: \fBwhere not[origin = "JFK"]\fR, \fBwhere not dep_delay > 10\fR
.TP
\fBnull\fR
Is null
.br
Example: \fBwhere null dep_time\fR, \fBwhere null[dep_time]\fR
.TP
\fBnot null\fR
Is not null
.br
Example: \fBwhere not null dep_time\fR
.SS "Scalar functions"
.TP
\fBlen\fR / \fBlength\fR
String length
.br
Example: \fBselect len[tailnum]\fR, \fBwhere len[tailnum] > 5\fR
.TP
\fBupper\fR
Uppercase string
.br
Example: \fBselect upper[tailnum]\fR, \fBwhere lower[origin] = "jfk"\fR
.TP
\fBlower\fR
Lowercase string
.br
Example: \fBselect lower[carrier]\fR
.TP
\fBabs\fR
Absolute value
.br
Example: \fBselect abs[dep_delay]\fR
.TP
\fBfloor\fR
Numeric floor
.br
Example: \fBselect floor[distance % 100]\fR
.TP
\fBceil\fR / \fBceiling\fR
Numeric ceiling
.br
Example: \fBselect ceil[distance % 100]\fR
.TP
\fBsqrt\fR
Square root
.br
Example: \fBselect sd: sqrt var dep_delay by origin\fR
.TP
\fBlog\fR
Natural logarithm
.br
Example: \fBselect year, log_n: (log n).round[2] where name = "Emma"\fR
.TP
\fBexp\fR
e raised to the value
.br
Example: \fBselect exp[1]\fR
.PP
\fBvar\fR, \fBdev\fR and \fBstd\fR divide by n \(mi 1, where q's \fBvar\fR and \fBdev\fR divide by n.
.SH "EXAMPLES ON THE BUILT-IN DATASETS"
NYC yellow taxis:
.PP
On the \fBtaxis\fR dataset (see DATASETS):
.PP
.RS 4
.EX
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
.EE
.RE
.PP
Premier League:
.PP
On the \fBfootball\fR dataset (see DATASETS):
.PP
.RS 4
.EX
select home: FT.part["\(en", 0].int, away: FT.part["\(en", 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
.EE
.RE
.PP
US baby names:
.PP
On the \fBnames\fR dataset (see DATASETS):
.PP
.RS 4
.EX
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"
.EE
.RE
.PP
NYC flights:
.PP
On the \fBflights\fR dataset (see DATASETS):
.PP
.RS 4
.EX
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
.EE
.RE
.PP
Food nutrition:
.PP
On the \fBfood\fR dataset (see DATASETS):
.PP
.RS 4
.EX
select items: count item by restaurant where item like "*Chicken*"
select restaurant, item where item like "*Chicken*"
.EE
.RE
.PP
Palmer penguins:
.PP
On the \fBpenguins\fR dataset (see DATASETS):
.PP
.RS 4
.EX
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
.EE
.RE
.SH DATASETS
The examples run on the Example datasets that come with datui (on the home screen). Open one, press \fB/\fR, and type the query.
.TP
\fBflights\fR
.EX
datui https://vincentarelbundock.github.io/Rdatasets/csv/nycflights13/flights.csv
.EE
.TP
\fBfootball\fR
.EX
datui https://raw.githubusercontent.com/footballcsv/england/de3945297668d7114006a8ca1c4c3740010b111c/2020s/2020\-21/eng.1.csv
.EE
.TP
\fBtaxis\fR
.EX
datui https://d37ci6vzurychx.cloudfront.net/trip\-data/yellow_tripdata_2025\-01.parquet
.EE
.TP
\fBnames\fR
.EX
datui https://raw.githubusercontent.com/rfordatascience/tidytuesday/8bfa9d9a7279192cb41cab041f426f2aacefde91/data/2022/2022\-03\-22/babynames.csv
.EE
.TP
\fBfood\fR
.EX
datui https://vincentarelbundock.github.io/Rdatasets/csv/openintro/fastfood.csv
.EE
.TP
\fBpenguins\fR
.EX
datui https://vincentarelbundock.github.io/Rdatasets/csv/palmerpenguins/penguins.csv
.EE
.SH "SEE ALSO"
\fBdatui\fR(1)
.PP
The datui documentation: <https:/\:/\:derekwisong.\:github.\:io/\:datui/\:>
.SH BUGS
Report bugs at <https:/\:/\:github.\:com/\:derekwisong/\:datui/\:issues>.
.SH AUTHORS
Derek Wisong and the datui contributors.
.SH COPYRIGHT
Copyright \(co 2026 Derek Wisong
.PP
datui is free software under the MIT License.