Repository: SeaQL/sea-query (language: rust)

Issue:
Support `COUNT(DISTINCT a.b)`
Ref: 
- https://mariadb.com/kb/en/count-distinct/
- https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count-distinct

## Motivation

I am aggregating data and need to count not all rows, but merely distinct rows where some might be present multiple times.

## My use case is this (MariaDB):

### Table (just to give you context for the query further below - I am *not* currently looking to build this `CREATE TABLE` statement in sea-query):
```sql
CREATE TABLE tx_digibordauthentication_activity
(
  uid          bigint unsigned    NOT NULL auto_increment,

  date_time    datetime           NOT NULL,

  person       int(11) unsigned   NOT NULL DEFAULT '0',
  institution  int(11) unsigned   NOT NULL DEFAULT '0',
  subscription int(11) unsigned   NOT NULL DEFAULT '0',

  year_month      varchar(7) AS (DATE_FORMAT(CONVERT_TZ(UTC_TIMESTAMP, "UTC", "Europe/Amsterdam"), '%Y-%m')) PERSISTENT,
  year_week       varchar(7) AS (DATE_FORMAT(CONVERT_TZ(UTC_TIMESTAMP, "UTC", "Europe/Amsterdam"), '%x-W%v')) PERSISTENT,
  year_month_day  varchar(10) AS (DATE_FORMAT(CONVERT_TZ(UTC_TIMESTAMP, "UTC", "Europe/Amsterdam"), '%Y-%m-%d')) PERSISTENT,

  PRIMARY KEY (uid),

  KEY institution_last_activity (institution, date_time),
  KEY institution_monthly (institution, year_month, person),
  KEY institution_weekly (institution, year_week, person),
  KEY institution_daily (institution, year_month_day, person),

  KEY subscription_last_activity (subscription, date_time),
  KEY subscription_monthly (subscription, year_month, person),
  KEY subscription_weekly (subscription, year_week, person),
  KEY subscription_daily (subscription, year_month_day, person),
);

```

### Query - here sea-query does not support all I need - i.e.  `COUNT(DISTINCT a.b)` -, and some stuff gets very tricky even with `Expr::cust_with_exprs()` etc.:
```sql
SELECT p.`year_month`,
  COUNT(a.uid) AS all_activity,
  IF(
    LAG(p.`year_month`) OVER (ORDER BY p.`year_month` DESC) IS NULL,
    NULL,
    (COUNT(a.uid) / (LEAD(COUNT(a.uid)) OVER (ORDER BY p.`year_month` DESC)) - 1) * 100
  ) AS all_activity_change_percent,
  COUNT(DISTINCT a.year_month_day) AS active_days,
  IF(
    LAG(p.`year_month`) OVER (ORDER BY p.`year_month` DESC) IS NULL,
    NULL,
    (COUNT(DISTINCT a.year_month_day) / (LEAD(COUNT(DISTINCT a.year_month_day)) OVER (ORDER BY p.`year_month` DESC)) - 1) * 100
  ) AS active_days_change_percent,
  COUNT(DISTINCT a.person) AS active_personnel,
  IF(
    LAG(p.`year_month`) OVER (ORDER BY p.`year_month` DESC) IS NULL,
    NULL,
    (COUNT(DISTINCT a.person) / (LEAD(COUNT(DISTINCT a.person)) OVER (ORDER BY p.`year_month` DESC)) - 1) * 100
  ) AS active_personnel_change_percent
FROM (
  SELECT DISTINCT `year_month`
  FROM tx_digibordauthentication_activity
  ORDER BY `year_month` DESC
) p
LEFT JOIN tx_digibordauthentication_activity a
  ON a.year_month = p.year_month
    AND a.institution = 2050
GROUP BY p.`year_month`
ORDER BY p.`year_month` DESC;
```

## Proposed Solutions

Add `Expr::count_distinct()` and `Func::count_distinct()` 

## Additional Information

I am missing a few things here and I will open separate issues for these:
- `COUNT(DISTINCT <table-ref>)` #600
- `IF(<cond>, <then-expr>, <else-expr>)` #602
- `LAG(<expr>) OVER (<order-by>)` #601
- `LEAD(<expr>) OVER (<order-by>)` #601



Discussion / hints:

Fix patch:
diff --git a/src/backend/query_builder.rs b/src/backend/query_builder.rs
index fd8c7677d..c6f2e3f0b 100644
--- a/src/backend/query_builder.rs
+++ b/src/backend/query_builder.rs
@@ -310,7 +310,12 @@ pub trait QueryBuilder:
             }
             SimpleExpr::FunctionCall(func) => {
                 self.prepare_function(&func.func, sql);
-                self.prepare_tuple(&func.args, sql);
+                write!(sql, "(").unwrap();
+                if func.distinct {
+                    write!(sql, "DISTINCT ").unwrap();
+                }
+                self.prepare_comma_seperated_sequence(&func.args, sql);
+                write!(sql, ")").unwrap();
             }
             SimpleExpr::Binary(left, op, right) => match (op, right.as_ref()) {
                 (BinOper::In, SimpleExpr::Tuple(t)) if t.is_empty() => {
@@ -510,8 +515,12 @@ pub trait QueryBuilder:
             }
             TableRef::FunctionCall(func, alias) => {
                 self.prepare_function(&func.func, sql);
-                self.prepare_tuple(&func.args, sql);
-                write!(sql, " AS ").unwrap();
+                write!(sql, "(").unwrap();
+                if func.distinct {
+                    write!(sql, "DISTINCT ").unwrap();
+                }
+                self.prepare_comma_seperated_sequence(&func.args, sql);
+                write!(sql, ") AS ").unwrap();
                 alias.prepare(sql.as_writer(), self.quote());
             }
             _ => self.prepare_table_ref_iden(table_ref, sql),
@@ -946,9 +955,8 @@ pub trait QueryBuilder:
         });
     }
 
-    /// Translate [`SimpleExpr::Tuple`] into SQL statement.
-    fn prepare_tuple(&self, exprs: &[SimpleExpr], sql: &mut dyn SqlWriter) {
-        write!(sql, "(").unwrap();
+    /// Write a comma seperated sequence of [`SimpleExpr`]s.
+    fn prepare_comma_seperated_sequence(&self, exprs: &[SimpleExpr], sql: &mut dyn SqlWriter) {
         exprs.iter().fold(true, |first, expr| {
             if !first {
                 write!(sql, ", ").unwrap();
@@ -956,6 +964,12 @@ pub trait QueryBuilder:
             self.prepare_simple_expr(expr, sql);
             false
         });
+    }
+
+    /// Translate [`SimpleExpr::Tuple`] into SQL statement.
+    fn prepare_tuple(&self, exprs: &[SimpleExpr], sql: &mut dyn SqlWriter) {
+        write!(sql, "(").unwrap();
+        self.prepare_comma_seperated_sequence(exprs, sql);
         write!(sql, ")").unwrap();
     }
 
diff --git a/src/expr.rs b/src/expr.rs
index ed846c15f..74311ffe0 100644
--- a/src/expr.rs
+++ b/src/expr.rs
@@ -1430,6 +1430,35 @@ impl Expr {
         Func::count(self.left).into()
     }
 
+    /// Express a `COUNT` function with the DISTINCT modifier.
+    ///
+    /// # Examples
+    ///
+    /// ```
+    /// use sea_query::{tests_cfg::*, *};
+    ///
+    /// let query = Query::select()
+    ///     .expr(Expr::col((Char::Table, Char::SizeW)).count_distinct())
+    ///     .from(Char::Table)
+    ///     .to_owned();
+    ///
+    /// assert_eq!(
+    ///     query.to_string(MysqlQueryBuilder),
+    ///     r#"SELECT COUNT(DISTINCT `character`.`size_w`) FROM `character`"#
+    /// );
+    /// assert_eq!(
+    ///     query.to_string(PostgresQueryBuilder),
+    ///     r#"SELECT COUNT(DISTINCT "character"."size_w") FROM "character""#
+    /// );
+    /// assert_eq!(
+    ///     query.to_string(SqliteQueryBuilder),
+    ///     r#"SELECT COUNT(DISTINCT "character"."size_w") FROM "character""#
+    /// );
+    /// ```
+    pub fn count_distinct(self) -> SimpleExpr {
+        Func::count_distinct(self.left).into()
+    }
+
     /// Express a `IF NULL` function.
     ///
     /// # Examples
diff --git a/src/func.rs b/src/func.rs
index bba4efe57..814b58e8a 100644
--- a/src/func.rs
+++ b/src/func.rs
@@ -33,6 +33,7 @@ pub enum Function {
 #[derive(Debug, Clone, PartialEq)]
 pub struct FunctionCall {
     pub(crate) func: Function,
+    pub(crate) distinct: bool,
     pub(crate) args: Vec<SimpleExpr>,
 }
 
@@ -40,6 +41,7 @@ impl FunctionCall {
     pub(crate) fn new(func: Function) -> Self {
         Self {
             func,
+            distinct: false,
             args: Vec::new(),
         }
     }
@@ -62,6 +64,12 @@ impl FunctionCall {
         self
     }
 
+    /// Add the `DISTINCT` modifier to the first argument
+    pub fn distinct(mut self) -> Self {
+        self.distinct = true;
+        self
+    }
+
     pub fn get_func(&self) -> &Function {
         &self.func
     }
@@ -307,6 +315,38 @@ impl Func {
         FunctionCall::new(Function::Count).arg(expr)
     }
 
+    /// Call `COUNT` function with the `DISTINCT` modifier.
+    ///
+    /// # Examples
+    ///
+    /// ```
+    /// use sea_query::{tests_cfg::*, *};
+    ///
+    /// let query = Query::select()
+    ///     .expr(Func::count_distinct(Expr::col((Char::Table, Char::Id))))
+    ///     .from(Char::Table)
+    ///     .to_owned();
+    ///
+    /// assert_eq!(
+    ///     query.to_string(MysqlQueryBuilder),
+    ///     r#"SELECT COUNT(DISTINCT `character`.`id`) FROM `character`"#
+    /// );
+    /// assert_eq!(
+    ///     query.to_string(PostgresQueryBuilder),
+    ///     r#"SELECT COUNT(DISTINCT "character"."id") FROM "character""#
+    /// );
+    /// assert_eq!(
+    ///     query.to_string(SqliteQueryBuilder),
+    ///     r#"SELECT COUNT(DISTINCT "character"."id") FROM "character""#
+    /// );
+    /// ```
+    pub fn count_distinct<T>(expr: T) -> FunctionCall
+    where
+        T: Into<SimpleExpr>,
+    {
+        FunctionCall::new(Function::Count).arg(expr).distinct()
+    }
+
     /// Call `CHAR_LENGTH` function.
     ///
     /// # Examples


Test patch: