zino_orm/
window.rs

1use self::WindownFunction::*;
2use super::{Entity, query::QueryExt};
3use zino_core::model::Query;
4
5/// A windown function.
6#[derive(Debug, Clone, Copy)]
7#[non_exhaustive]
8enum WindownFunction<E: Entity> {
9    /// The `COUNT` function.
10    Count(E::Column),
11    /// The `SUM` function.
12    Sum(E::Column),
13    /// The `AVG` function.
14    Avg(E::Column),
15    /// The `MIN` function.
16    Min(E::Column),
17    /// The `MAX` function.
18    Max(E::Column),
19    /// The `ROW_NUMBER` function.
20    RowNumber,
21    /// The `RNAK` function.
22    Rank,
23    /// The `DENSE_RNAK` function.
24    DenseRank,
25    /// The `PERCENT_RNAK` function.
26    PercentRank,
27    /// The `CUME_DIST` function.
28    CumeDist,
29    /// The `NTILE` function.
30    Ntile(usize),
31    /// The `LAG` function.
32    Lag(E::Column, usize),
33    /// The `LEAD` function.
34    Lead(E::Column, usize),
35    /// The `FIRST_VALUE` function.
36    FirstValue(E::Column),
37    /// The `LAST_VALUE` function.
38    LastValue(E::Column),
39    /// The `NTH_VALUE` function.
40    NthValue(E::Column, usize),
41}
42
43/// SQL window functions.
44///
45/// # Examples
46/// ```rust,ignore
47/// use crate::model::{User, UserColumn::*};
48/// use zino_orm::{QueryBuilder, Schema, Window};
49///
50/// let rank_window = Window::rank(CurrentLoginIp).order_desc(LoginCount);
51/// let query = QueryBuilder::new()
52///     .fields([Id, Name, CurrentLoginIp, LoginCount])
53///     .window(rank_window, "login_count_rank")
54///     .and_not_in(Status, ["Deleted", "Locked"])
55///     .order_desc(UpdatedAt)
56///     .limit(10)
57///     .build();
58/// let users: Vec<Map> = User::find(&query).await?;
59/// ```
60#[derive(Debug, Clone, Copy)]
61pub struct Window<E: Entity> {
62    /// The window function.
63    function: WindownFunction<E>,
64    /// `PARTITION BY` a column.
65    partition: E::Column,
66    /// An optional `ORDER BY`.
67    order: Option<(E::Column, bool)>,
68}
69
70impl<E: Entity> Window<E> {
71    /// Constructs an instance for the window function `COUNT`.
72    #[inline]
73    pub fn count(col: E::Column, partition: E::Column) -> Self {
74        Self {
75            function: Count(col),
76            partition,
77            order: None,
78        }
79    }
80
81    /// Constructs an instance for the window function `SUM`.
82    #[inline]
83    pub fn sum(col: E::Column, partition: E::Column) -> Self {
84        Self {
85            function: Sum(col),
86            partition,
87            order: None,
88        }
89    }
90
91    /// Constructs an instance for the window function `AVG`.
92    #[inline]
93    pub fn avg(col: E::Column, partition: E::Column) -> Self {
94        Self {
95            function: Avg(col),
96            partition,
97            order: None,
98        }
99    }
100
101    /// Constructs an instance for the window function `MIN`.
102    #[inline]
103    pub fn min(col: E::Column, partition: E::Column) -> Self {
104        Self {
105            function: Min(col),
106            partition,
107            order: None,
108        }
109    }
110
111    /// Constructs an instance for the window function `MAX`.
112    #[inline]
113    pub fn max(col: E::Column, partition: E::Column) -> Self {
114        Self {
115            function: Max(col),
116            partition,
117            order: None,
118        }
119    }
120
121    /// Constructs an instance for the window function `DENSE_RANK`.
122    #[inline]
123    pub fn row_number(partition: E::Column) -> Self {
124        Self {
125            function: RowNumber,
126            partition,
127            order: None,
128        }
129    }
130
131    /// Constructs an instance for the window function `RANK`.
132    #[inline]
133    pub fn rank(partition: E::Column) -> Self {
134        Self {
135            function: Rank,
136            partition,
137            order: None,
138        }
139    }
140
141    /// Constructs an instance for the window function `DENSE_RANK`.
142    #[inline]
143    pub fn dense_rank(partition: E::Column) -> Self {
144        Self {
145            function: DenseRank,
146            partition,
147            order: None,
148        }
149    }
150
151    /// Constructs an instance for the window function `PERCENT_RANK`.
152    #[inline]
153    pub fn percent_rank(partition: E::Column) -> Self {
154        Self {
155            function: PercentRank,
156            partition,
157            order: None,
158        }
159    }
160
161    /// Constructs an instance for the window function `CUME_DIST`.
162    #[inline]
163    pub fn cume_dist(partition: E::Column) -> Self {
164        Self {
165            function: CumeDist,
166            partition,
167            order: None,
168        }
169    }
170
171    /// Constructs an instance for the window function `NTILE`.
172    #[inline]
173    pub fn ntile(num_buckets: usize, partition: E::Column) -> Self {
174        Self {
175            function: Ntile(num_buckets),
176            partition,
177            order: None,
178        }
179    }
180
181    /// Constructs an instance for the window function `LAG`.
182    #[inline]
183    pub fn lag(col: E::Column, offset: usize, partition: E::Column) -> Self {
184        Self {
185            function: Lag(col, offset),
186            partition,
187            order: None,
188        }
189    }
190
191    /// Constructs an instance for the window function `LEAD`.
192    #[inline]
193    pub fn lead(col: E::Column, offset: usize, partition: E::Column) -> Self {
194        Self {
195            function: Lead(col, offset),
196            partition,
197            order: None,
198        }
199    }
200
201    /// Constructs an instance for the window function `FIRST_VALUE`.
202    #[inline]
203    pub fn first_value(col: E::Column, partition: E::Column) -> Self {
204        Self {
205            function: FirstValue(col),
206            partition,
207            order: None,
208        }
209    }
210
211    /// Constructs an instance for the window function `LAST_VALUE`.
212    #[inline]
213    pub fn last_value(col: E::Column, partition: E::Column) -> Self {
214        Self {
215            function: LastValue(col),
216            partition,
217            order: None,
218        }
219    }
220
221    /// Constructs an instance for the window function `NTH_VALUE`.
222    #[inline]
223    pub fn nth_value(col: E::Column, n: usize, partition: E::Column) -> Self {
224        Self {
225            function: NthValue(col, n),
226            partition,
227            order: None,
228        }
229    }
230
231    /// Sets the sort order.
232    #[inline]
233    pub fn order_by(mut self, col: E::Column, descending: bool) -> Self {
234        self.order = Some((col, descending));
235        self
236    }
237
238    /// Sets the sort order with an ascending order.
239    #[inline]
240    pub fn order_asc(mut self, col: E::Column) -> Self {
241        self.order = Some((col, false));
242        self
243    }
244
245    /// Sets the sort order with an descending order.
246    #[inline]
247    pub fn order_desc(mut self, col: E::Column) -> Self {
248        self.order = Some((col, true));
249        self
250    }
251
252    /// Returns the SQL expression.
253    pub(super) fn expr(&self) -> String {
254        let partition_col_name = E::format_column(&self.partition);
255        let partition = Query::format_field(&partition_col_name);
256        let sort = self
257            .order
258            .as_ref()
259            .map(|(col, descending)| {
260                let col_name = E::format_column(col);
261                let sort_field = Query::format_field(&col_name);
262                if *descending {
263                    format!(" ORDER BY {sort_field} DESC")
264                } else {
265                    format!(" ORDER BY {sort_field} ASC")
266                }
267            })
268            .unwrap_or_default();
269        match &self.function {
270            Count(col) => {
271                let col_name = E::format_column(col);
272                let field = Query::format_field(&col_name);
273                format!("count({field}) OVER (PARTITION BY {partition}{sort})")
274            }
275            Sum(col) => {
276                let col_name = E::format_column(col);
277                let field = Query::format_field(&col_name);
278                format!("sum({field}) OVER (PARTITION BY {partition}{sort})")
279            }
280            Avg(col) => {
281                let col_name = E::format_column(col);
282                let field = Query::format_field(&col_name);
283                format!("avg({field}) OVER (PARTITION BY {partition}{sort})")
284            }
285            Min(col) => {
286                let col_name = E::format_column(col);
287                let field = Query::format_field(&col_name);
288                format!("min({field}) OVER (PARTITION BY {partition}{sort})")
289            }
290            Max(col) => {
291                let col_name = E::format_column(col);
292                let field = Query::format_field(&col_name);
293                format!("max({field}) OVER (PARTITION BY {partition}{sort})")
294            }
295            RowNumber => {
296                format!("row_number() OVER (PARTITION BY {partition}{sort})")
297            }
298            Rank => {
299                format!("rank() OVER (PARTITION BY {partition}{sort})")
300            }
301            DenseRank => {
302                format!("dense_rank() OVER (PARTITION BY {partition}{sort})")
303            }
304            PercentRank => {
305                format!("percent_rank() OVER (PARTITION BY {partition}{sort})")
306            }
307            CumeDist => {
308                format!("cume_dist() OVER (PARTITION BY {partition}{sort})")
309            }
310            Ntile(n) => {
311                format!("ntile({n}) OVER (PARTITION BY {partition}{sort})")
312            }
313            Lag(col, offset) => {
314                let col_name = E::format_column(col);
315                let field = Query::format_field(&col_name);
316                format!("lag({field}, {offset}) OVER (PARTITION BY {partition}{sort})")
317            }
318            Lead(col, offset) => {
319                let col_name = E::format_column(col);
320                let field = Query::format_field(&col_name);
321                format!("lead({field}, {offset}) OVER (PARTITION BY {partition}{sort})")
322            }
323            FirstValue(col) => {
324                let col_name = E::format_column(col);
325                let field = Query::format_field(&col_name);
326                format!("first_value({field}) OVER (PARTITION BY {partition}{sort})")
327            }
328            LastValue(col) => {
329                let col_name = E::format_column(col);
330                let field = Query::format_field(&col_name);
331                format!("last_value({field}) OVER (PARTITION BY {partition}{sort})")
332            }
333            NthValue(col, n) => {
334                let col_name = E::format_column(col);
335                let field = Query::format_field(&col_name);
336                format!("nth_value({field}, {n}) OVER (PARTITION BY {partition}{sort})")
337            }
338        }
339    }
340}
zino_orm/window.rs

zino_orm/
window.rs
