1"use strict";(globalThis.webpackChunkwebsite=globalThis.webpackChunkwebsite||[]).push([[5764],{3699(e,n,d){d.r(n),d.d(n,{assets:()=>l,contentTitle:()=>t,default:()=>a,frontMatter:()=>c,metadata:()=>s,toc:()=>h});const s=JSON.parse('{"id":"syntax/stdlib","title":"Standard Library Functions","description":"Wvlet ships with a standard library of functions that you call with dot syntax on column values, e.g. name.upper or price.round(2). Functions compile to the SQL of the target database engine: when engines differ (e.g. DuckDB, Trino, Hive, Snowflake, and BigQuery), Wvlet picks the right SQL for the engine you are compiling for, so the same query works across engines.","source":"@site/docs/syntax/stdlib.md","sourceDirName":"syntax","slug":"/syntax/stdlib","permalink":"/wvlet/docs/syntax/stdlib","draft":false,"unlisted":false,"editUrl":"https://github.com/wvlet/wvlet/tree/main/website/docs/syntax/stdlib.md","tags":[],"version":"current","frontMatter":{},"sidebar":"tutorialSidebar","previous":{"title":"Stdlib Reference","permalink":"/wvlet/docs/syntax/stdlib-reference"},"next":{"title":"Managing Tables and Schemas","permalink":"/wvlet/docs/syntax/table-management"}}');var r=d(1058),i=d(6798);const c={},t="Standard Library Functions",l={},h=[{value:"Type Conversions",id:"type-conversions",level:2},{value:"Null Handling",id:"null-handling",level:2},{value:"String Functions",id:"string-functions",level:2},{value:"Math Functions",id:"math-functions",level:2},{value:"Date and Timestamp Functions",id:"date-and-timestamp-functions",level:2},{value:"Array Functions",id:"array-functions",level:2},{value:"Map Functions",id:"map-functions",level:2},{value:"Aggregation Functions",id:"aggregation-functions",level:2},{value:"Window Functions",id:"window-functions",level:2},{value:"Engine-Specific Functions",id:"engine-specific-functions",level:2}];function o(e){const n={a:"a",code:"code",h1:"h1",h2:"h2",header:"header",p:"p",pre:"pre",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",...(0,i.R)(),...e.components};return(0,r.jsxs)(r.Fragment,{children:[(0,r.jsx)(n.header,{children:(0,r.jsx)(n.h1,{id:"standard-library-functions",children:"Standard Library Functions"})}),"\n",(0,r.jsxs)(n.p,{children:["Wvlet ships with a standard library of functions that you call with dot syntax on column values, e.g. ",(0,r.jsx)(n.code,{children:"name.upper"})," or ",(0,r.jsx)(n.code,{children:"price.round(2)"}),". Functions compile to the SQL of the target database engine: when engines differ (e.g. DuckDB, Trino, Hive, Snowflake, and BigQuery), Wvlet picks the right SQL for the engine you are compiling for, so the same query works across engines."]}),"\n",(0,r.jsxs)(n.p,{children:["This page is a guided tour of the most common functions. For the complete listing generated from the library sources, see the ",(0,r.jsx)(n.a,{href:"/wvlet/docs/syntax/stdlib-reference",children:"Standard Library Reference"}),"."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-wvlet",children:"from orders\nselect\n customer_name.upper as customer,\n order_date.year as order_year,\n amount.round(2) as amount\n"})}),"\n",(0,r.jsx)(n.p,{children:"Functions can be chained:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-wvlet",children:"from logs\nselect message.trim.lower.replace('error', 'warning') as normalized\n"})}),"\n",(0,r.jsx)(n.h2,{id:"type-conversions",children:"Type Conversions"}),"\n",(0,r.jsx)(n.p,{children:"Available on all values:"}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.to_string"})}),(0,r.jsx)(n.td,{children:"Cast to string"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"x.to_int"})," / ",(0,r.jsx)(n.code,{children:"x.to_long"})]}),(0,r.jsx)(n.td,{children:"Cast to a 64-bit integer"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"x.to_float"})," / ",(0,r.jsx)(n.code,{children:"x.to_double"})]}),(0,r.jsx)(n.td,{children:"Cast to a 64-bit floating point number"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.to_boolean"})}),(0,r.jsx)(n.td,{children:"Cast to boolean"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.to_date"})}),(0,r.jsx)(n.td,{children:"Cast to date"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.to_timestamp"})}),(0,r.jsx)(n.td,{children:"Cast to timestamp"})]})]})]}),"\n",(0,r.jsx)(n.h2,{id:"null-handling",children:"Null Handling"}),"\n",(0,r.jsx)(n.p,{children:"Available on all values:"}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.is_null"})}),(0,r.jsx)(n.td,{children:"True if the value is null"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.is_not_null"})}),(0,r.jsx)(n.td,{children:"True if the value is not null"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.or_else(default)"})}),(0,r.jsxs)(n.td,{children:["Return ",(0,r.jsx)(n.code,{children:"default"})," if the value is null (SQL ",(0,r.jsx)(n.code,{children:"coalesce"}),")"]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.null_if(v)"})}),(0,r.jsxs)(n.td,{children:["Return null if the value equals ",(0,r.jsx)(n.code,{children:"v"})," (SQL ",(0,r.jsx)(n.code,{children:"nullif"}),")"]})]})]})]}),"\n",(0,r.jsx)(n.h2,{id:"string-functions",children:"String Functions"}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.length"})}),(0,r.jsx)(n.td,{children:"Number of characters"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"s.upper"})," / ",(0,r.jsx)(n.code,{children:"s.lower"})]}),(0,r.jsx)(n.td,{children:"Change case"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"s.trim"})," / ",(0,r.jsx)(n.code,{children:"s.ltrim"})," / ",(0,r.jsx)(n.code,{children:"s.rtrim"})]}),(0,r.jsx)(n.td,{children:"Remove surrounding whitespace"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.reverse"})}
1),(0,r.jsx)(n.td,{children:"Reverse the characters"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.concat(other)"})}),(0,r.jsxs)(n.td,{children:["Concatenate strings (",(0,r.jsx)(n.code,{children:"||"}),")"]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.substring(start)"})}),(0,r.jsx)(n.td,{children:"Substring from a 1-origin position"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.substring(start, length)"})}),(0,r.jsx)(n.td,{children:"Substring of the given length"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.replace(search, replacement)"})}),(0,r.jsx)(n.td,{children:"Replace all occurrences"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"s.lpad(length, pad)"})," / ",(0,r.jsx)(n.code,{children:"s.rpad(length, pad)"})]}),(0,r.jsx)(n.td,{children:"Pad to the given length"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.strpos(substr)"})}),(0,r.jsx)(n.td,{children:"1-origin position of a substring (0 if absent)"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.contains(substr)"})}),(0,r.jsx)(n.td,{children:"True if the string contains the substring"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"s.starts_with(prefix)"})," / ",(0,r.jsx)(n.code,{children:"s.ends_with(suffix)"})]}),(0,r.jsx)(n.td,{children:"Prefix/suffix test"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.like(pattern)"})}),(0,r.jsx)(n.td,{children:"SQL LIKE match"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.regexp_like(pattern)"})}),(0,r.jsx)(n.td,{children:"True if the regex matches"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.regexp_extract(pattern)"})}),(0,r.jsx)(n.td,{children:"First substring matching the regex"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.regexp_extract(pattern, group)"})}),(0,r.jsx)(n.td,{children:"Regex capture group"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.regexp_replace(pattern, replacement)"})}),(0,r.jsx)(n.td,{children:"Replace every regex match"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.split(separator)"})}),(0,r.jsxs)(n.td,{children:["Split into an ",(0,r.jsx)(n.code,{children:"array[string]"})]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.levenshtein(other)"})}),(0,r.jsx)(n.td,{children:"Edit distance"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"s.md5"})," / ",(0,r.jsx)(n.code,{children:"s.sha256"})]}),(0,r.jsx)(n.td,{children:"Hex-encoded digest"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.json_extract(path)"})}),(0,r.jsxs)(n.td,{children:["Extract a JSON value with a JSONPath (e.g. ",(0,r.jsx)(n.code,{children:"'$.a.b'"}),")"]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"s.json_extract_string(path)"})}),(0,r.jsx)(n.td,{children:"Extract a JSON value as a plain string"})]})]})]}),"\n",(0,r.jsx)(n.h2,{id:"math-functions",children:"Math Functions"}),"\n",(0,r.jsx)(n.p,{children:"Available on numeric values (int, long, float, double, decimal):"}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.abs"})}),(0,r.jsx)(n.td,{children:"Absolute value"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"x.ceil"})," / ",(0,r.jsx)(n.code,{children:"x.floor"})]}),(0,r.jsx)(n.td,{children:"Round up / down"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.round(digits)"})}),(0,r.jsx)(n.td,{children:"Round to the given number of digits"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.truncate"})}),(0,r.jsx)(n.td,{children:"Drop the fractional part"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"x.sqrt"})," / ",(0,r.jsx)(n.code,{children:"x.cbrt"})]}),(0,r.jsx)(n.td,{children:"Square/cube root"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"x.exp"})," / ",(0,r.jsx)(n.code,{children:"x.ln"})," / ",(0,r.jsx)(n.code,{children:"x.log10"})," / ",(0,r.jsx)(n.code,{children:"x.log2"})]}),(0,r.jsx)(n.td,{children:"Exponential and logarithms"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.power(exponent)"})}),(0,r.jsx)(n.td,{children:"Raise to a power"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.mod(divisor)"})}),(0,r.jsx)(n.td,{children:"Modulo"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.sign"})}),(0,r.jsx)(n.td,{children:"-1, 0, or 1"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"x.between(low, high)"})}),(0,r.jsx)(n.td,{children:"Range test"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"x.in(v1, v2, ...)"})," / ",(0,r.jsx)(n.code,{children:"x.not_in(...)"})]}),(0,r.jsx)(n.td,{children:"Set membership"})]})]})]}),"\n",(0,r.jsxs)(n.p,{children:["On float/double values: ",(0,r.jsx)(n.code,{children:"x.is_nan"}),", ",(0,r.jsx)(n.code,{children:"x.is_finite"}),", ",(0,r.jsx)(n.code,{children:"x.is_infinite"}),", and trigonometric functions (",(0,r.jsx)(n.code,{children:"sin"}),", ",(0,r.jsx)(n.code,{children:"cos"}),", ",(0,r.jsx)(n.code,{children:"tan"}),", ",(0,r.jsx)(n.code,{children:"asin"}),", ",(0,r.jsx)(n.code,{children:"acos"}),", ",(0,r.jsx)(n.code,{children:"atan"}),", ",(0,r.jsx)(n.code,{children:"degrees"}),", ",(0,r.jsx)(n.code,{children:"radians"}),")."]}),"\n",(0,r.jsxs)(n.p,{children:["On integer values: ",(0,r.jsx)(n.code,{children:"x.from_unixtime"})," interprets the number as unix epoch seconds and returns a timestamp."]}),"\n",(0,r.jsx)(n.h2,{id:"date-and-timestamp-functions",children:"Date and Timestamp Functions"}),"\n",(0,r.jsxs)(n.p,{children:["On ",(0,r.jsx)(n.code,{children:"date"})," and ",(0,r.jsx)(n.code,{children:"timestamp"})," values:"]}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"d.year"})," / ",(0,r.jsx)(n.code,{children:"d.month"})," / ",(0,r.jsx)(n.code,{children:"d.day"})]}),(0,r.jsx)(n.td,{children:"Calendar fields"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"d.quarter"})," / ",(0,r.jsx)(n.code,{children:"d.week"})]}),(0,r.jsx)(n.td,{children:"Quarter and ISO week of year"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"d.day_of_week"})}),(0,r.jsx)(n.td,{children:"ISO day of week (1 = Monday, 7 = Sunday)"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"d.day_of_year"})}),(0,r.jsx)(n.td,{children:"Day of year"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"d.truncate_to(unit)"})}),(0,r.jsxs)(n.td,{children:["Truncate to ",(0,r.jsx)(n.code,{children:"'year'"}),", ",(0,r.jsx)(n.code,{children:"'month'"}),", ",(0,r.jsx)(n.code,{children:"'day'"}),", ..."]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"d.add_days(n)"})," / ",(0,r.jsx)(n.code,{children:"d.add_months(n)"})," / ",(0,r.jsx)(n.code,{children:"d.add_years(n)"})]}),(0,r.jsx)(n.td,{children:"Date arithmetic"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"d.diff_days(other)"})," / ",(0,r.jsx)(n.code,{children:"d.diff_months(other)"})," / ",(0,r.jsx)(n.code,{children:"d.diff_years(other)"})]}),(0,r.jsx)(n.td,{children:"Difference in the given unit"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"d.format(pattern)"})}),(0,r.jsxs)(n.td,{children:["Format with a ",(0,r.jsx)(n.code,{children:"'%Y-%m-%d'"}),"-style pattern"]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"d.last_day"})}),(0,r.jsx)(n.td,{children:"Last day of the month"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"d.extract(field)"})}),(0,r.jsx)(n.td,{children:"Extract an arbitrary field"})]})]})]}),"\n",(0,r.jsxs)(n.p,{children:["Additionally on timestamps: ",(0,r.jsx)(n.code,{children:"hour"}),", ",(0,r.jsx)(n.code,{children:"minute"}),", ",(0,r.jsx)(n.code,{children:"second"}),", ",(0,r.jsx)(n.code,{children:"add_seconds(n)"}),", ",(0,r.jsx)(n.code,{children:"add_minutes(n)"}),", ",(0,r.jsx)(n.code,{children:"add_hours(n)"}),", ",(0,r.jsx)(n.code,{children:"diff_seconds(other)"}),", ",(0,r.jsx)(n.code,{children:"diff_minutes(other)"}),", ",(0,r.jsx)(n.code,{children:"diff_hours(other)"}),", ",(0,r.jsx)(n.code,{children:"to_unixtime"})," (epoch seconds), and ",(0,r.jsx)(n.code,{children:"to_date"}),"."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-wvlet",children:"from events\nwhere event_time.between('2024-01-01'.to_timestamp, '2024-12-31'.to_timestamp)\nselect\n event_time.truncate_to('month') as month,\n event_time.format('%Y-%m-%d') as day\n"})}),"\n",(0,r.jsx)(n.h2,{id:"array-functions",children:"Array Functions"}),"\n",(0,r.jsxs)(n.p,{children:["On array values (e.g. from ",(0,r.jsx)(n.code,{children:"split"}),", array literals, or ",(0,r.jsx)(n.code,{children:"array_agg"}),"):"]}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"a.size"})," / ",(0,r.jsx)(n.code,{children:"a.length"})]}),(0,r.jsx)(n.td,{children:"Number of elements"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.get(index)"})}),(0,r.jsxs)(n.td,{children:["Element at a 1-origin index (same as ",(0,r.jsx)(n.code,{children:"a[index]"}),")"]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.contains(elem)"})}),(0,r.jsx)(n.td,{children:"True if the array contains the element"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.index_of(elem)"})}),(0,r.jsx)(n.td,{children:"1-origin position of an element (0 if absent)"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.sort"})}),(0,r.jsx)(n.td,{children:"Sort ascending"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.reverse"})}
1),(0,r.jsx)(n.td,{children:"Reverse the order"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.distinct"})}),(0,r.jsx)(n.td,{children:"Remove duplicates"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.concat(other)"})}),(0,r.jsx)(n.td,{children:"Concatenate arrays"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"a.flatten"})}),(0,r.jsx)(n.td,{children:"Flatten an array of arrays"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"a.mk_string"})," / ",(0,r.jsx)(n.code,{children:"a.mk_string(separator)"})]}),(0,r.jsx)(n.td,{children:"Join elements into a string"})]})]})]}),"\n",(0,r.jsx)(n.h2,{id:"map-functions",children:"Map Functions"}),"\n",(0,r.jsxs)(n.p,{children:["On map values (e.g. ",(0,r.jsx)(n.code,{children:'map {"a": 1, "b": 2}'}),"):"]}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"m.size"})}),(0,r.jsx)(n.td,{children:"Number of entries"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"m.keys"})," / ",(0,r.jsx)(n.code,{children:"m.values"})]}),(0,r.jsx)(n.td,{children:"Keys or values as an array"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"m.contains_key(key)"})}),(0,r.jsx)(n.td,{children:"True if the key is present"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"m.get(key)"})}),(0,r.jsx)(n.td,{children:"Value for the key, or null"})]})]})]}),"\n",(0,r.jsx)(n.h2,{id:"aggregation-functions",children:"Aggregation Functions"}),"\n",(0,r.jsxs)(n.p,{children:["After ",(0,r.jsx)(n.code,{children:"group by"}),", a column reference represents the group's values, and these\naggregation functions apply (see also ",(0,r.jsx)(n.a,{href:"/wvlet/docs/syntax/#group-by",children:"Aggregation"}),"):"]}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.count"})," / ",(0,r.jsx)(n.code,{children:"c.count_distinct"})]}),(0,r.jsx)(n.td,{children:"Count rows / distinct values"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"c.count_if(cond)"})}),(0,r.jsx)(n.td,{children:"Count rows matching a condition"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"c.count_approx_distinct"})}),(0,r.jsx)(n.td,{children:"Fast approximate distinct count"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.min"})," / ",(0,r.jsx)(n.code,{children:"c.max"})," / ",(0,r.jsx)(n.code,{children:"c.sum"})," / ",(0,r.jsx)(n.code,{children:"c.avg"})]}),(0,r.jsx)(n.td,{children:"Basic aggregates"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.min_by(expr)"})," / ",(0,r.jsx)(n.code,{children:"c.max_by(expr)"})]}),(0,r.jsxs)(n.td,{children:["Value at the row minimizing/maximizing ",(0,r.jsx)(n.code,{children:"expr"})]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"c.arbitrary"})}),(0,r.jsx)(n.td,{children:"Any value of the group"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"c.to_array"})}),(0,r.jsx)(n.td,{children:"Collect values into an array"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"c.string_agg(separator)"})}),(0,r.jsx)(n.td,{children:"Concatenate strings with a separator"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.bool_and"})," / ",(0,r.jsx)(n.code,{children:"c.bool_or"})]}),(0,r.jsx)(n.td,{children:"Boolean aggregates"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"c.median"})}),(0,r.jsx)(n.td,{children:"Median value"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.stddev"})," / ",(0,r.jsx)(n.code,{children:"c.variance"})]}),(0,r.jsx)(n.td,{children:"Sample standard deviation / variance"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.stddev_pop"})," / ",(0,r.jsx)(n.code,{children:"c.stddev_samp"})," / ",(0,r.jsx)(n.code,{children:"c.var_pop"})," / ",(0,r.jsx)(n.code,{children:"c.var_samp"})]}),(0,r.jsx)(n.td,{children:"Population/sample variants"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"c.approx_quantile(pos)"})}),(0,r.jsxs)(n.td,{children:["Approximate quantile (e.g. ",(0,r.jsx)(n.code,{children:"0.95"}),")"]})]})]})]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-wvlet",children:"from orders\ngroup by customer_id\nagg\n _.count as order_count,\n amount.sum as total,\n amount.approx_quantile(0.95) as p95,\n product.string_agg(',') as products\n"})}),"\n",(0,r.jsx)(n.h2,{id:"window-functions",children:"Window Functions"}),"\n",(0,r.jsxs)(n.p,{children:["Window (analytic) functions compute a value over a set of rows related to the current row, selected with an ",(0,r.jsx)(n.code,{children:"over(...)"})," clause:"]}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Function"}),(0,r.jsx)(n.th,{children:"Description"})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"row_number()"})}),(0,r.jsx)(n.td,{children:"Sequential row number within the window (1, 2, 3, ...)"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"rank()"})," / ",(0,r.jsx)(n.code,{children:"dense_rank()"})]}),(0,r.jsx)(n.td,{children:"Rank with / without gaps after ties"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"percent_rank()"})," / ",(0,r.jsx)(n.code,{children:"cume_dist()"})]}),(0,r.jsx)(n.td,{children:"Relative rank / cumulative distribution"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"ntile(n)"})}),(0,r.jsxs)(n.td,{children:["Bucket number when the window is divided into ",(0,r.jsx)(n.code,{children:"n"})," groups"]})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.lag"})," / ",(0,r.jsx)(n.code,{children:"c.lag(offset)"})," / ",(0,r.jsx)(n.code,{children:"c.lag(offset, default)"})]}),(0,r.jsx)(n.td,{children:"Value from a preceding row"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.lead"})," / ",(0,r.jsx)(n.code,{children:"c.lead(offset)"})," / ",(0,r.jsx)(n.code,{children:"c.lead(offset, default)"})]}),(0,r.jsx)(n.td,{children:"Value from a following row"})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsxs)(n.td,{children:[(0,r.jsx)(n.code,{children:"c.first_value"})," / ",(0,r.jsx)(n.code,{children:"c.last_value"})," / ",(0,r.jsx)(n.code,{children:"c.nth_value(n)"})]}),(0,r.jsx)(n.td,{children:"Value at a window position"})]})]})]}),"\n",(0,r.jsxs)(n.p,{children:["Aggregation functions (",(0,r.jsx)(n.code,{children:"sum"}),", ",(0,r.jsx)(n.code,{children:"avg"}),", ",(0,r.jsx)(n.code,{children:"count"}),", ...) also accept an ",(0,r.jsx)(n.code,{children:"over(...)"})," clause. The window clause supports ",(0,r.jsx)(n.code,{children:"partition by"}),", ",(0,r.jsx)(n.code,{children:"order by"}),", and row frames (",(0,r.jsx)(n.code,{children:"rows[-1, 0]"}),"):"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-wvlet",children:"from orders\nselect\n customer_id,\n order_date,\n row_
1number() over (partition by customer_id order by order_date) as nth_order,\n amount.lag over (partition by customer_id order by order_date) as prev_amount,\n amount.sum over (partition by customer_id) as customer_total\n"})}),"\n",(0,r.jsx)(n.h2,{id:"engine-specific-functions",children:"Engine-Specific Functions"}),"\n",(0,r.jsx)(n.p,{children:"Functions above compile to each target engine's SQL automatically. You can also define your own functions, including engine-specific variants, selected by the compile target:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-wvlet",children:'-- Selected when compiling for DuckDB\ndef bit_count(x: long) in duckdb: int = sql"bit_count(${x})"\n-- Selected when compiling for Trino\ndef bit_count(x: long) in trino: int = sql"bitwise_bit_count(${x})"\n'})}),"\n",(0,r.jsxs)(n.p,{children:["Supported dialect contexts include ",(0,r.jsx)(n.code,{children:"duckdb"}),", ",(0,r.jsx)(n.code,{children:"trino"}),", ",(0,r.jsx)(n.code,{children:"hive"}),", ",(0,r.jsx)(n.code,{children:"snowflake"}),", and ",(0,r.jsx)(n.code,{children:"bigquery"}),". Note that pattern strings remain engine-specific even when the function name is mapped: ",(0,r.jsx)(n.code,{children:"format"})," takes a strftime-style pattern on DuckDB and BigQuery (",(0,r.jsx)(n.code,{children:"'%Y-%m-%d'"}),"), a MySQL-style pattern on Trino (",(0,r.jsx)(n.code,{children:"'%Y-%m-%d'"})," with ",(0,r.jsx)(n.code,{children:"%i"})," for minutes), a Java SimpleDateFormat pattern on Hive (",(0,r.jsx)(n.code,{children:"'yyyy-MM-dd'"}),"), and a SQL format model on Snowflake (",(0,r.jsx)(n.code,{children:"'YYYY-MM-DD'"}),"). Similarly, Hive's ",(0,r.jsx)(n.code,{children:"split"})," treats the separator as a regular expression, and Snowflake's JSON paths omit the leading ",(0,r.jsx)(n.code,{children:"$."}),"."]}),"\n",(0,r.jsxs)(n.p,{children:["All DuckDB and Trino engine functions are bundled with the standard library, so calls like ",(0,r.jsx)(n.code,{children:"bit_count(x)"})," type-check offline out of the box and compile to the SQL of the engine you target. For other databases, or engine-specific UDFs, import the engine's function catalog with ",(0,r.jsx)(n.a,{href:"/wvlet/docs/usage/catalog-import",children:(0,r.jsx)(n.code,{children:"wvlet catalog import"})}),"."]})]})}function a(e={}){const{wrapper:n}={...(0,i.R)(),...e.components};return n?(0,r.jsx)(n,{...e,children:(0,r.jsx)(o,{...e})}):o(e)}},6798(e,n,d){d.d(n,{R:()=>c,x:()=>t});var s=d(3706);const r={},i=s.createContext(r);function c(e){const n=s.useContext(i);return s.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function t(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(r):e.components||r:c(e.components),s.createElement(i.Provider,{value:n},e.children)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.