sql_cli/refactoring/
conditional_agg.rs

1// Conditional aggregation pattern generators
2
3use serde::{Deserialize, Serialize};
4
5#[derive(Debug, Serialize, Deserialize)]
6pub struct ConditionalAggregation {
7    pub column: String,
8    pub values: Vec<String>,
9    pub aggregate_func: AggregateFunction,
10    pub aggregate_column: Option<String>,
11}
12
13#[derive(Debug, Serialize, Deserialize)]
14pub enum AggregateFunction {
15    Count,
16    Sum,
17    Avg,
18    Max,
19    Min,
20}
21
22impl ConditionalAggregation {
23    /// Generate pivot-like conditional aggregation
24    pub fn to_sql(&self) -> Vec<String> {
25        let mut statements = Vec::new();
26
27        for value in &self.values {
28            let condition = format!("{} = '{}'", self.column, value);
29
30            let agg_expr = match &self.aggregate_func {
31                AggregateFunction::Count => {
32                    format!("SUM(CASE WHEN {} THEN 1 ELSE 0 END)", condition)
33                }
34                AggregateFunction::Sum => {
35                    let default_col = "1".to_string();
36                    let col = self.aggregate_column.as_ref().unwrap_or(&default_col);
37                    format!("SUM(CASE WHEN {} THEN {} ELSE 0 END)", condition, col)
38                }
39                AggregateFunction::Avg => {
40                    let default_col = "1".to_string();
41                    let col = self.aggregate_column.as_ref().unwrap_or(&default_col);
42                    format!("AVG(CASE WHEN {} THEN {} ELSE NULL END)", condition, col)
43                }
44                AggregateFunction::Max => {
45                    let col = self.aggregate_column.as_ref().unwrap_or(&self.column);
46                    format!("MAX(CASE WHEN {} THEN {} ELSE NULL END)", condition, col)
47                }
48                AggregateFunction::Min => {
49                    let col = self.aggregate_column.as_ref().unwrap_or(&self.column);
50                    format!("MIN(CASE WHEN {} THEN {} ELSE NULL END)", condition, col)
51                }
52            };
53
54            // Generate alias from value (sanitized)
55            let alias = format!(
56                "{}_{}",
57                value.to_lowercase().replace(' ', "_"),
58                match &self.aggregate_func {
59                    AggregateFunction::Count => "count",
60                    AggregateFunction::Sum => "sum",
61                    AggregateFunction::Avg => "avg",
62                    AggregateFunction::Max => "max",
63                    AggregateFunction::Min => "min",
64                }
65            );
66
67            statements.push(format!("{} AS {}", agg_expr, alias));
68        }
69
70        statements
71    }
72
73    /// Generate a balance calculation pattern (e.g., debits vs credits)
74    pub fn balance_pattern(
75        positive_condition: &str,
76        negative_condition: &str,
77        column: &str,
78    ) -> String {
79        format!(
80            r#"SUM(CASE WHEN {} THEN {} ELSE 0 END) -
81SUM(CASE WHEN {} THEN {} ELSE 0 END) AS net_balance"#,
82            positive_condition, column, negative_condition, column
83        )
84    }
85}
sql_cli/refactoring/conditional_agg.rs

sql_cli/refactoring/
conditional_agg.rs
