drizzle_core/expr/
window.rs

1//! Window functions and the `OVER (...)` clause.
2//!
3//! - [`window`] starts a window specification ([`WindowSpec`]) with
4//!   `PARTITION BY`, `ORDER BY` and a frame.
5//! - `.over(spec)` on an aggregate (such as [`sum`](super::sum) or
6//!   [`count`](super::count)) makes it a window function. The result is
7//!   scalar, so it can sit next to plain columns without `GROUP BY`.
8//! - Pure window functions ([`row_number`], [`rank`], [`lag`], ...) return a
9//!   [`WindowFnExpr`], which cannot be used until `.over(...)` is called.
10//!
11//! # Examples
12//!
13//! ```rust
14//! # use drizzle_core::asc;
15//! # use drizzle_core::desc;
16//! # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
17//! # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
18//! # #[derive(Clone, Debug)] struct Value(String);
19//! # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
20//! # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
21//! # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
22//! # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
23//! # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
24//! # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
25//! # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
26//! # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
27//! let running_total = sum(users.score).over(window().order_by(asc(users.id)));
28//! assert_eq!(
29//!     running_total.sql(),
30//!     r#"SUM ("users"."score") OVER (ORDER BY "users"."id" ASC)"#
31//! );
32//!
33//! let position = row_number().over(window().partition_by([users.name]).order_by(desc(users.age)));
34//! assert_eq!(
35//!     position.sql(),
36//!     r#"ROW_NUMBER() OVER (PARTITION BY "users"."name" ORDER BY "users"."age" DESC)"#
37//! );
38//! ```
39
40use crate::dialect::{DialectSupports, feature};
41use core::marker::PhantomData;
42
43use crate::sql::{SQL, Token};
44use crate::traits::{SQLParam, ToSQL};
45use crate::types::{BooleanLike, Compatible, DataType};
46
47use super::{Agg, Expr, ExprSources, NonNull, Null, Nullability, SQLExpr, Scalar};
48use crate::dialect::DialectTypes;
49use crate::scope::ScopeOnly;
50
51impl DialectSupports<feature::AggregateFilter> for crate::SQLiteDialect {}
52impl DialectSupports<feature::AggregateFilter> for crate::PostgresDialect {}
53
54// =============================================================================
55// Frame Bounds
56// =============================================================================
57
58/// One end of a window frame, for [`WindowSpec::rows_between`] and
59/// [`WindowSpec::range_between`].
60#[derive(Debug, Clone, Copy)]
61pub enum FrameBound {
62    /// `UNBOUNDED PRECEDING`: the first row of the partition.
63    UnboundedPreceding,
64    /// `n PRECEDING`: `n` rows (or, for `RANGE`, values) before the current row.
65    Preceding(u64),
66    /// `CURRENT ROW`.
67    CurrentRow,
68    /// `n FOLLOWING`: `n` rows (or, for `RANGE`, values) after the current row.
69    Following(u64),
70    /// `UNBOUNDED FOLLOWING`: the last row of the partition.
71    UnboundedFollowing,
72}
73
74impl FrameBound {
75    fn write_sql<'a, V: SQLParam>(&self) -> SQL<'a, V> {
76        match self {
77            Self::UnboundedPreceding => SQL::from(Token::UNBOUNDED).push(Token::PRECEDING),
78            Self::Preceding(n) => {
79                SQL::number(usize::try_from(*n).unwrap_or(usize::MAX)).push(Token::PRECEDING)
80            }
81            Self::CurrentRow => SQL::from(Token::CURRENT).push(Token::ROW),
82            Self::Following(n) => {
83                SQL::number(usize::try_from(*n).unwrap_or(usize::MAX)).push(Token::FOLLOWING)
84            }
85            Self::UnboundedFollowing => SQL::from(Token::UNBOUNDED).push(Token::FOLLOWING),
86        }
87    }
88}
89
90// =============================================================================
91// WindowSpec
92// =============================================================================
93
94/// The contents of an `OVER (...)` clause; start one with [`window`].
95///
96/// `S` records the tables that `PARTITION BY` and `ORDER BY` read, for the
97/// query's scope check.
98///
99/// # Examples
100///
101/// ```rust
102/// # use drizzle_core::asc;
103/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
104/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
105/// # #[derive(Clone, Debug)] struct Value(String);
106/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
107/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
108/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
109/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
110/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
111/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
112/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
113/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
114/// let spec = window()
115///     .partition_by([users.name])
116///     .order_by(asc(users.created_at))
117///     .rows_between(FrameBound::UnboundedPreceding, FrameBound::CurrentRow);
118/// let total = sum(users.score).over(spec);
119/// assert_eq!(
120///     total.sql(),
121///     r#"SUM ("users"."score") OVER (PARTITION BY "users"."name" ORDER BY "users"."created_at" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)"#
122/// );
123/// ```
124#[derive(Debug, Clone)]
125pub struct WindowSpec<'a, V: SQLParam, S = ()> {
126    partition: Option<SQL<'a, V>>,
127    order: Option<SQL<'a, V>>,
128    frame: Option<SQL<'a, V>>,
129    /// Sources read by `PARTITION BY` / `ORDER BY` (never NULL-making).
130    sources: PhantomData<fn() -> S>,
131}
132
133/// Starts an empty window specification (`OVER ()`).
134///
135/// An empty window covers the whole result set.
136///
137/// # Examples
138///
139/// ```rust
140/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
141/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
142/// # #[derive(Clone, Debug)] struct Value(String);
143/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
144/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
145/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
146/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
147/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
148/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
149/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
150/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
151/// let share = count::<Value, _>(()).over(window());
152/// assert_eq!(share.sql(), "COUNT(*) OVER ()");
153/// ```
154#[must_use]
155pub const fn window<'a, V: SQLParam>() -> WindowSpec<'a, V> {
156    WindowSpec {
157        partition: None,
158        order: None,
159        frame: None,
160        sources: PhantomData,
161    }
162}
163
164impl<'a, V: SQLParam + 'a, S> WindowSpec<'a, V, S> {
165    fn with_sources<S2>(self) -> WindowSpec<'a, V, S2> {
166        WindowSpec {
167            partition: self.partition,
168            order: self.order,
169            frame: self.frame,
170            sources: PhantomData,
171        }
172    }
173
174    /// Sets `PARTITION BY`; the window restarts for each distinct value.
175    ///
176    /// Takes an array or other iterator of expressions of one Rust type.
177    #[must_use]
178    #[allow(clippy::type_complexity)]
179    pub fn partition_by<I>(
180        mut self,
181        exprs: I,
182    ) -> WindowSpec<'a, V, (S, ScopeOnly<<I::Item as ExprSources>::Sources>)>
183    where
184        I: IntoIterator,
185        I::Item: ToSQL<'a, V> + ExprSources,
186    {
187        self.partition = Some(
188            SQL::from(Token::PARTITION)
189                .push(Token::BY)
190                .append(SQL::join(exprs, Token::COMMA)),
191        );
192        self.with_sources()
193    }
194
195    /// Sets `ORDER BY` inside the window.
196    ///
197    /// Takes one ordering term such as [`asc`](crate::asc)`(col)`, or a tuple or
198    /// array of terms.
199    #[must_use]
200    pub fn order_by<T: ToSQL<'a, V> + ExprSources>(
201        mut self,
202        exprs: T,
203    ) -> WindowSpec<'a, V, (S, ScopeOnly<T::Sources>)> {
204        self.order = Some(
205            SQL::from(Token::ORDER)
206                .push(Token::BY)
207                .append(exprs.into_sql()),
208        );
209        self.with_sources()
210    }
211
212    /// Sets a `ROWS BETWEEN start AND end` frame, counted in rows.
213    #[must_use]
214    pub fn rows_between(mut self, start: FrameBound, end: FrameBound) -> Self {
215        self.frame = Some(
216            SQL::from(Token::ROWS)
217                .push(Token::BETWEEN)
218                .append(start.write_sql())
219                .push(Token::AND)
220                .append(end.write_sql()),
221        );
222        self
223    }
224
225    /// Sets a `RANGE BETWEEN start AND end` frame, measured in `ORDER BY` values.
226    #[must_use]
227    pub fn range_between(mut self, start: FrameBound, end: FrameBound) -> Self {
228        self.frame = Some(
229            SQL::from(Token::RANGE)
230                .push(Token::BETWEEN)
231                .append(start.write_sql())
232                .push(Token::AND)
233                .append(end.write_sql()),
234        );
235        self
236    }
237
238    /// Renders the clause contents, without the surrounding `OVER (...)`.
239    fn into_sql(self) -> SQL<'a, V> {
240        let mut sql = SQL::empty();
241        if let Some(p) = self.partition {
242            sql.append_mut(p);
243        }
244        if let Some(o) = self.order {
245            sql.append_mut(o);
246        }
247        if let Some(f) = self.frame {
248            sql.append_mut(f);
249        }
250        sql
251    }
252}
253
254// =============================================================================
255// .over() on aggregate expressions — Agg → Scalar
256// =============================================================================
257
258impl<'a, V, T, N, S> SQLExpr<'a, V, T, N, Agg, S>
259where
260    V: SQLParam + 'a,
261    T: DataType,
262    N: Nullability,
263{
264    /// Turns this aggregate into a window function (`agg OVER (...)`).
265    ///
266    /// The result keeps the SQL type and nullability but is scalar, so it can
267    /// be selected next to plain columns.
268    ///
269    /// # Examples
270    ///
271    /// ```rust
272    /// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
273    /// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
274    /// # #[derive(Clone, Debug)] struct Value(String);
275    /// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
276    /// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
277    /// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
278    /// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
279    /// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
280    /// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
281    /// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
282    /// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
283    /// let per_name = sum(users.score).over(window().partition_by([users.name]));
284    /// assert_eq!(
285    ///     per_name.sql(),
286    ///     r#"SUM ("users"."score") OVER (PARTITION BY "users"."name")"#
287    /// );
288    /// ```
289    pub fn over<W>(self, spec: WindowSpec<'a, V, W>) -> SQLExpr<'a, V, T, N, Scalar, (S, W)> {
290        let sql = self
291            .into_sql()
292            .push(Token::OVER)
293            .push(Token::LPAREN)
294            .append(spec.into_sql())
295            .push(Token::RPAREN);
296        SQLExpr::new(sql)
297    }
298
299    /// Limits the rows an aggregate sees (`agg FILTER (WHERE condition)`), on
300    /// SQLite and PostgreSQL.
301    ///
302    /// The condition must be boolean. The result is still an aggregate.
303    ///
304    /// # Examples
305    ///
306    /// ```rust
307    /// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
308    /// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
309    /// # #[derive(Clone, Debug)] struct Value(String);
310    /// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
311    /// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
312    /// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
313    /// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
314    /// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
315    /// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
316    /// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
317    /// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
318    /// let adults = count(users.id).filter(gt(users.age, 18));
319    /// assert_eq!(
320    ///     adults.sql(),
321    ///     r#"COUNT ("users"."id") FILTER (WHERE "users"."age" > ?)"#
322    /// );
323    /// ```
324    #[allow(clippy::type_complexity)]
325    pub fn filter<C>(self, condition: C) -> SQLExpr<'a, V, T, N, Agg, (S, ScopeOnly<C::Sources>)>
326    where
327        C: Expr<'a, V>,
328        C::SQLType: BooleanLike,
329        V::DialectMarker: DialectSupports<feature::AggregateFilter>,
330    {
331        let sql = self
332            .into_sql()
333            .push(Token::FILTER)
334            .push(Token::LPAREN)
335            .push(Token::WHERE)
336            .append(condition.into_expr_sql())
337            .push(Token::RPAREN);
338        SQLExpr::new(sql)
339    }
340}
341
342// =============================================================================
343// WindowFnExpr — pure window functions that require .over()
344// =============================================================================
345
346/// A window function that still needs its `OVER (...)` clause.
347///
348/// [`row_number`], [`rank`], [`lag`] and the other pure window functions
349/// return this type. It implements neither [`Expr`] nor [`ToSQL`], so it
350/// cannot be used in a query until [`over`](Self::over) is called.
351///
352/// ```rust,compile_fail
353/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
354/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
355/// # #[derive(Clone, Debug)] struct Value(String);
356/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
357/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
358/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
359/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
360/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
361/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
362/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
363/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
364/// // ROW_NUMBER() without OVER is not an expression.
365/// let wrong = gt(row_number::<Value>(), 1);
366/// ```
367#[derive(Debug, Clone)]
368pub struct WindowFnExpr<'a, V: SQLParam, T: DataType, N: Nullability, S = ()> {
369    sql: SQL<'a, V>,
370    _marker: super::TypeMarker<(T, N, S)>,
371}
372
373impl<'a, V, T, N, S> WindowFnExpr<'a, V, T, N, S>
374where
375    V: SQLParam + 'a,
376    T: DataType,
377    N: Nullability,
378{
379    const fn new(sql: SQL<'a, V>) -> Self {
380        Self {
381            sql,
382            _marker: PhantomData,
383        }
384    }
385
386    /// Adds the window clause, producing a scalar expression (`fn OVER (...)`).
387    pub fn over<W>(self, spec: WindowSpec<'a, V, W>) -> SQLExpr<'a, V, T, N, Scalar, (S, W)> {
388        let sql = self
389            .sql
390            .push(Token::OVER)
391            .push(Token::LPAREN)
392            .append(spec.into_sql())
393            .push(Token::RPAREN);
394        SQLExpr::new(sql)
395    }
396}
397
398// =============================================================================
399// Pure Window Functions
400// =============================================================================
401
402/// Number of the row within its partition, from 1 (`ROW_NUMBER()`).
403///
404/// The result is the dialect's big-integer type and never NULL.
405///
406/// # Examples
407///
408/// ```rust
409/// # use drizzle_core::asc;
410/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
411/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
412/// # #[derive(Clone, Debug)] struct Value(String);
413/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
414/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
415/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
416/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
417/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
418/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
419/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
420/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
421/// let n = row_number().over(window().order_by(asc(users.age)));
422/// assert_eq!(n.sql(), r#"ROW_NUMBER() OVER (ORDER BY "users"."age" ASC)"#);
423/// ```
424#[must_use]
425pub fn row_number<'a, V>()
426-> WindowFnExpr<'a, V, <V::DialectMarker as DialectTypes>::BigInt, NonNull>
427where
428    V: SQLParam + 'a,
429{
430    WindowFnExpr::new(SQL::raw("ROW_NUMBER()"))
431}
432
433/// Rank of the row, with gaps after ties (`RANK()`).
434///
435/// Tied rows share a rank and the next rank skips ahead (1, 1, 3). The result
436/// is the dialect's big-integer type and never NULL.
437///
438/// # Examples
439///
440/// ```rust
441/// # use drizzle_core::asc;
442/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
443/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
444/// # #[derive(Clone, Debug)] struct Value(String);
445/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
446/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
447/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
448/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
449/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
450/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
451/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
452/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
453/// let n = rank().over(window().order_by(asc(users.age)));
454/// assert_eq!(n.sql(), r#"RANK() OVER (ORDER BY "users"."age" ASC)"#);
455/// ```
456#[must_use]
457pub fn rank<'a, V>() -> WindowFnExpr<'a, V, <V::DialectMarker as DialectTypes>::BigInt, NonNull>
458where
459    V: SQLParam + 'a,
460{
461    WindowFnExpr::new(SQL::raw("RANK()"))
462}
463
464/// Rank of the row, without gaps after ties (`DENSE_RANK()`).
465///
466/// Tied rows share a rank and the next rank follows on (1, 1, 2). The result
467/// is the dialect's big-integer type and never NULL.
468///
469/// # Examples
470///
471/// ```rust
472/// # use drizzle_core::asc;
473/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
474/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
475/// # #[derive(Clone, Debug)] struct Value(String);
476/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
477/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
478/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
479/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
480/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
481/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
482/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
483/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
484/// let n = dense_rank().over(window().order_by(asc(users.age)));
485/// assert_eq!(n.sql(), r#"DENSE_RANK() OVER (ORDER BY "users"."age" ASC)"#);
486/// ```
487#[must_use]
488pub fn dense_rank<'a, V>()
489-> WindowFnExpr<'a, V, <V::DialectMarker as DialectTypes>::BigInt, NonNull>
490where
491    V: SQLParam + 'a,
492{
493    WindowFnExpr::new(SQL::raw("DENSE_RANK()"))
494}
495
496/// Splits the partition into `n` groups of nearly equal size (`NTILE(n)`).
497///
498/// Returns the group number, from 1. The result is the dialect's integer type
499/// and never NULL.
500///
501/// # Examples
502///
503/// ```rust
504/// # use drizzle_core::asc;
505/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
506/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
507/// # #[derive(Clone, Debug)] struct Value(String);
508/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
509/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
510/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
511/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
512/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
513/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
514/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
515/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
516/// let n = ntile(4).over(window().order_by(asc(users.age)));
517/// assert_eq!(n.sql(), r#"NTILE (4) OVER (ORDER BY "users"."age" ASC)"#);
518/// ```
519#[must_use]
520pub fn ntile<'a, V>(
521    n: usize,
522) -> WindowFnExpr<'a, V, <V::DialectMarker as DialectTypes>::Int, NonNull>
523where
524    V: SQLParam + 'a,
525{
526    WindowFnExpr::new(SQL::func("NTILE", SQL::number(n)))
527}
528
529/// Relative rank: `(rank - 1) / (rows - 1)` (`PERCENT_RANK()`).
530///
531/// The result is the dialect's double type, between 0 and 1, and never NULL.
532///
533/// # Examples
534///
535/// ```rust
536/// # use drizzle_core::asc;
537/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
538/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
539/// # #[derive(Clone, Debug)] struct Value(String);
540/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
541/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
542/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
543/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
544/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
545/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
546/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
547/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
548/// let n = percent_rank().over(window().order_by(asc(users.age)));
549/// assert_eq!(n.sql(), r#"PERCENT_RANK() OVER (ORDER BY "users"."age" ASC)"#);
550/// ```
551#[must_use]
552pub fn percent_rank<'a, V>()
553-> WindowFnExpr<'a, V, <V::DialectMarker as DialectTypes>::Double, NonNull>
554where
555    V: SQLParam + 'a,
556{
557    WindowFnExpr::new(SQL::raw("PERCENT_RANK()"))
558}
559
560/// Fraction of rows ordered at or before this row (`CUME_DIST()`).
561///
562/// The result is the dialect's double type, greater than 0 and at most 1, and
563/// never NULL.
564///
565/// # Examples
566///
567/// ```rust
568/// # use drizzle_core::asc;
569/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
570/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
571/// # #[derive(Clone, Debug)] struct Value(String);
572/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
573/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
574/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
575/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
576/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
577/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
578/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
579/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
580/// let n = cume_dist().over(window().order_by(asc(users.age)));
581/// assert_eq!(n.sql(), r#"CUME_DIST() OVER (ORDER BY "users"."age" ASC)"#);
582/// ```
583#[must_use]
584pub fn cume_dist<'a, V>() -> WindowFnExpr<'a, V, <V::DialectMarker as DialectTypes>::Double, NonNull>
585where
586    V: SQLParam + 'a,
587{
588    WindowFnExpr::new(SQL::raw("CUME_DIST()"))
589}
590
591/// The value of `expr` in the previous row (`LAG(expr)`).
592///
593/// The result has `expr`'s SQL type and is always nullable: the first row has
594/// no previous row.
595///
596/// # Examples
597///
598/// ```rust
599/// # use drizzle_core::asc;
600/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
601/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
602/// # #[derive(Clone, Debug)] struct Value(String);
603/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
604/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
605/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
606/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
607/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
608/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
609/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
610/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
611/// let n = lag(users.name).over(window().order_by(asc(users.age)));
612/// assert_eq!(n.sql(), r#"LAG ("users"."name") OVER (ORDER BY "users"."age" ASC)"#);
613/// ```
614pub fn lag<'a, V, E>(expr: E) -> WindowFnExpr<'a, V, E::SQLType, Null, E::Sources>
615where
616    V: SQLParam + 'a,
617    E: Expr<'a, V>,
618{
619    WindowFnExpr::new(SQL::func("LAG", expr.into_sql()))
620}
621
622/// The value of `expr` `offset` rows back, or `default` (`LAG(expr, offset, default)`).
623///
624/// `default` must have a type compatible with `expr`. The result has `expr`'s
625/// SQL type and is nullable if `expr` or `default` is.
626///
627/// # Examples
628///
629/// ```rust
630/// # use drizzle_core::asc;
631/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
632/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
633/// # #[derive(Clone, Debug)] struct Value(String);
634/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
635/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
636/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
637/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
638/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
639/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
640/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
641/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
642/// let n = lag_with_default(users.name, 2, "none").over(window().order_by(asc(users.age)));
643/// assert_eq!(n.sql(), r#"LAG ("users"."name", 2, ?) OVER (ORDER BY "users"."age" ASC)"#);
644/// ```
645#[allow(clippy::type_complexity)]
646pub fn lag_with_default<'a, V, E, D>(
647    expr: E,
648    offset: usize,
649    default: D,
650) -> WindowFnExpr<
651    'a,
652    V,
653    E::SQLType,
654    <E::Nullable as Nullability>::Or<D::Nullable>,
655    (E::Sources, D::Sources),
656>
657where
658    V: SQLParam + 'a,
659    E: Expr<'a, V>,
660    D: Expr<'a, V>,
661    E::SQLType: Compatible<D::SQLType>,
662    D::Nullable: Nullability,
663{
664    let args = expr
665        .into_sql()
666        .push(Token::COMMA)
667        .append(SQL::number(offset))
668        .push(Token::COMMA)
669        .append(default.into_sql());
670    WindowFnExpr::new(SQL::func("LAG", args))
671}
672
673/// The value of `expr` in the next row (`LEAD(expr)`).
674///
675/// The result has `expr`'s SQL type and is always nullable: the last row has
676/// no next row.
677///
678/// # Examples
679///
680/// ```rust
681/// # use drizzle_core::asc;
682/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
683/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
684/// # #[derive(Clone, Debug)] struct Value(String);
685/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
686/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
687/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
688/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
689/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
690/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
691/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
692/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
693/// let n = lead(users.name).over(window().order_by(asc(users.age)));
694/// assert_eq!(n.sql(), r#"LEAD ("users"."name") OVER (ORDER BY "users"."age" ASC)"#);
695/// ```
696pub fn lead<'a, V, E>(expr: E) -> WindowFnExpr<'a, V, E::SQLType, Null, E::Sources>
697where
698    V: SQLParam + 'a,
699    E: Expr<'a, V>,
700{
701    WindowFnExpr::new(SQL::func("LEAD", expr.into_sql()))
702}
703
704/// The value of `expr` `offset` rows ahead, or `default` (`LEAD(expr, offset, default)`).
705///
706/// `default` must have a type compatible with `expr`. The result has `expr`'s
707/// SQL type and is nullable if `expr` or `default` is.
708///
709/// # Examples
710///
711/// ```rust
712/// # use drizzle_core::asc;
713/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
714/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
715/// # #[derive(Clone, Debug)] struct Value(String);
716/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
717/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
718/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
719/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
720/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
721/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
722/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
723/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
724/// let n = lead_with_default(users.name, 1, "none").over(window().order_by(asc(users.age)));
725/// assert_eq!(n.sql(), r#"LEAD ("users"."name", 1, ?) OVER (ORDER BY "users"."age" ASC)"#);
726/// ```
727#[allow(clippy::type_complexity)]
728pub fn lead_with_default<'a, V, E, D>(
729    expr: E,
730    offset: usize,
731    default: D,
732) -> WindowFnExpr<
733    'a,
734    V,
735    E::SQLType,
736    <E::Nullable as Nullability>::Or<D::Nullable>,
737    (E::Sources, D::Sources),
738>
739where
740    V: SQLParam + 'a,
741    E: Expr<'a, V>,
742    D: Expr<'a, V>,
743    E::SQLType: Compatible<D::SQLType>,
744    D::Nullable: Nullability,
745{
746    let args = expr
747        .into_sql()
748        .push(Token::COMMA)
749        .append(SQL::number(offset))
750        .push(Token::COMMA)
751        .append(default.into_sql());
752    WindowFnExpr::new(SQL::func("LEAD", args))
753}
754
755/// The value of `expr` in the first row of the frame (`FIRST_VALUE(expr)`).
756///
757/// The result has `expr`'s SQL type and is typed as nullable.
758///
759/// # Examples
760///
761/// ```rust
762/// # use drizzle_core::asc;
763/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
764/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
765/// # #[derive(Clone, Debug)] struct Value(String);
766/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
767/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
768/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
769/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
770/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
771/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
772/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
773/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
774/// let n = first_value(users.name).over(window().order_by(asc(users.age)));
775/// assert_eq!(n.sql(), r#"FIRST_VALUE ("users"."name") OVER (ORDER BY "users"."age" ASC)"#);
776/// ```
777pub fn first_value<'a, V, E>(expr: E) -> WindowFnExpr<'a, V, E::SQLType, Null, E::Sources>
778where
779    V: SQLParam + 'a,
780    E: Expr<'a, V>,
781{
782    WindowFnExpr::new(SQL::func("FIRST_VALUE", expr.into_sql()))
783}
784
785/// The value of `expr` in the last row of the frame (`LAST_VALUE(expr)`).
786///
787/// With an `ORDER BY` and the default frame, the frame ends at the current
788/// row (and its ties); use [`WindowSpec::rows_between`] to look further.
789/// The result has `expr`'s SQL type and is typed as nullable.
790///
791/// # Examples
792///
793/// ```rust
794/// # use drizzle_core::asc;
795/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
796/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
797/// # #[derive(Clone, Debug)] struct Value(String);
798/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
799/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
800/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
801/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
802/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
803/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
804/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
805/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
806/// let n = last_value(users.name).over(window().order_by(asc(users.age)));
807/// assert_eq!(n.sql(), r#"LAST_VALUE ("users"."name") OVER (ORDER BY "users"."age" ASC)"#);
808/// ```
809pub fn last_value<'a, V, E>(expr: E) -> WindowFnExpr<'a, V, E::SQLType, Null, E::Sources>
810where
811    V: SQLParam + 'a,
812    E: Expr<'a, V>,
813{
814    WindowFnExpr::new(SQL::func("LAST_VALUE", expr.into_sql()))
815}
816
817/// The value of `expr` in the `n`-th row of the frame, from 1 (`NTH_VALUE(expr, n)`).
818///
819/// The result has `expr`'s SQL type and is always nullable: the frame may
820/// have fewer than `n` rows.
821///
822/// # Examples
823///
824/// ```rust
825/// # use drizzle_core::asc;
826/// # use drizzle_core::dialect::{Dialect, DialectTypes, SQLiteDialect as D};
827/// # use drizzle_core::{ColumnRef, SQL, SQLParam, expr::*};
828/// # #[derive(Clone, Debug)] struct Value(String);
829/// # impl SQLParam for Value { const DIALECT: Dialect = Dialect::SQLite; type DialectMarker = D; }
830/// # impl<X: ToString> From<X> for Value { fn from(v: X) -> Self { Value(v.to_string()) } }
831/// # impl From<Value> for std::borrow::Cow<'_, Value> { fn from(v: Value) -> Self { Self::Owned(v) } }
832/// # type C<X, N = NonNull> = &'static SQLExpr<'static, Value, X, N>;
833/// # fn col<X: drizzle_core::types::DataType, N: Nullability>(c: &'static str) -> C<X, N> { Box::leak(Box::new(SQLExpr::new(SQL::column(ColumnRef::sql("users", c))))) }
834/// # type Int = <D as DialectTypes>::Int; type Text = <D as DialectTypes>::Text; type Real = <D as DialectTypes>::Double;
835/// # struct Users { id: C<Int>, age: C<Int>, name: C<Text>, email: C<Text, Null>, score: C<Real, Null>, active: C<<D as DialectTypes>::Bool>, created_at: C<<D as DialectTypes>::Timestamp> }
836/// # let users = Users { id: col("id"), age: col("age"), name: col("name"), email: col("email"), score: col("score"), active: col("active"), created_at: col("created_at") };
837/// let n = nth_value(users.name, 2).over(window().order_by(asc(users.age)));
838/// assert_eq!(n.sql(), r#"NTH_VALUE ("users"."name", 2) OVER (ORDER BY "users"."age" ASC)"#);
839/// ```
840pub fn nth_value<'a, V, E>(expr: E, n: usize) -> WindowFnExpr<'a, V, E::SQLType, Null, E::Sources>
841where
842    V: SQLParam + 'a,
843    E: Expr<'a, V>,
844{
845    let args = expr.into_sql().push(Token::COMMA).append(SQL::number(n));
846    WindowFnExpr::new(SQL::func("NTH_VALUE", args))
847}
drizzle_core/expr/window.rs

drizzle_core/expr/
window.rs
