zino_orm/
window.rs

1use self::WindownFunction::*;
2use super::{query::QueryExt, Entity};
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::<User>::new()
52///     .fields([Id, Name, CurrentLoginIp, LoginCount])
53///     .window(rank_window, Some("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 a default alias for the window function.
253    pub(super) fn default_alias(&self) -> String {
254        match &self.function {
255            Count(col) => [col.as_ref(), "_sum"].concat(),
256            Sum(col) => [col.as_ref(), "_sum"].concat(),
257            Avg(col) => [col.as_ref(), "_avg"].concat(),
258            Min(col) => [col.as_ref(), "_min"].concat(),
259            Max(col) => [col.as_ref(), "_max"].concat(),
260            RowNumber => "row_number".to_owned(),
261            Rank => "rank".to_owned(),
262            DenseRank => "dense_rank".to_owned(),
263            PercentRank => "percent_rank".to_owned(),
264            CumeDist => "cume_dist".to_owned(),
265            Ntile(_) => "ntile".to_owned(),
266            Lag(col, _) => [col.as_ref(), "_prev"].concat(),
267            Lead(col, _) => [col.as_ref(), "_next"].concat(),
268            FirstValue(col) => [col.as_ref(), "_first"].concat(),
269            LastValue(col) => [col.as_ref(), "_last"].concat(),
270            NthValue(col, _) => [col.as_ref(), "_nth"].concat(),
271        }
272    }
273
274    /// Returns the SQL expression.
275    pub(super) fn expr(&self) -> String {
276        let partition_col_name = E::format_column(&self.partition);
277        let partition = Query::format_field(&partition_col_name);
278        let sort = self
279            .order
280            .as_ref()
281            .map(|(col, descending)| {
282                let col_name = E::format_column(col);
283                let sort_field = Query::format_field(&col_name);
284                if *descending {
285                    format!(" ORDER BY {sort_field} DESC")
286                } else {
287                    format!(" ORDER BY {sort_field} ASC")
288                }
289            })
290            .unwrap_or_default();
291        match &self.function {
292            Count(col) => {
293                let col_name = E::format_column(col);
294                let field = Query::format_field(&col_name);
295                format!("count({field}) OVER (PARTITION BY {partition}{sort})")
296            }
297            Sum(col) => {
298                let col_name = E::format_column(col);
299                let field = Query::format_field(&col_name);
300                format!("sum({field}) OVER (PARTITION BY {partition}{sort})")
301            }
302            Avg(col) => {
303                let col_name = E::format_column(col);
304                let field = Query::format_field(&col_name);
305                format!("avg({field}) OVER (PARTITION BY {partition}{sort})")
306            }
307            Min(col) => {
308                let col_name = E::format_column(col);
309                let field = Query::format_field(&col_name);
310                format!("min({field}) OVER (PARTITION BY {partition}{sort})")
311            }
312            Max(col) => {
313                let col_name = E::format_column(col);
314                let field = Query::format_field(&col_name);
315                format!("max({field}) OVER (PARTITION BY {partition}{sort})")
316            }
317            RowNumber => {
318                format!("row_number() OVER (PARTITION BY {partition}{sort})")
319            }
320            Rank => {
321                format!("rank() OVER (PARTITION BY {partition}{sort})")
322            }
323            DenseRank => {
324                format!("dense_rank() OVER (PARTITION BY {partition}{sort})")
325            }
326            PercentRank => {
327                format!("percent_rank() OVER (PARTITION BY {partition}{sort})")
328            }
329            CumeDist => {
330                format!("cume_dist() OVER (PARTITION BY {partition}{sort})")
331            }
332            Ntile(n) => {
333                format!("ntile({n}) OVER (PARTITION BY {partition}{sort})")
334            }
335            Lag(col, offset) => {
336                let col_name = E::format_column(col);
337                let field = Query::format_field(&col_name);
338                format!("lag({field}, {offset}) OVER (PARTITION BY {partition}{sort})")
339            }
340            Lead(col, offset) => {
341                let col_name = E::format_column(col);
342                let field = Query::format_field(&col_name);
343                format!("lead({field}, {offset}) OVER (PARTITION BY {partition}{sort})")
344            }
345            FirstValue(col) => {
346                let col_name = E::format_column(col);
347                let field = Query::format_field(&col_name);
348                format!("first_value({field}) OVER (PARTITION BY {partition}{sort})")
349            }
350            LastValue(col) => {
351                let col_name = E::format_column(col);
352                let field = Query::format_field(&col_name);
353                format!("last_value({field}) OVER (PARTITION BY {partition}{sort})")
354            }
355            NthValue(col, n) => {
356                let col_name = E::format_column(col);
357                let field = Query::format_field(&col_name);
358                format!("nth_value({field}, {n}) OVER (PARTITION BY {partition}{sort})")
359            }
360        }
361    }
362}
zino_orm/window.rs

zino_orm/
window.rs
