[Pipeline] Underlying SQL Metrics

[zacandcheese]

Description:

There is currently no way to generate the SQL to make a metric table.

Tasks:

• [ ] machine_learning/metrics/classification.py: Create a way to get the underlying SQL of the metrics
• [x] machine_learning/metrics/regression.py: Adding an additional parameter to regression_report to return the SQL of the metric instead of the result of the metrics.

Definition of Done:

• SQL code generation is possible for regression and classification.

Concerns:

An example to show we really don't use sql to compute classification anymore:

• how accuracy_score used to be computed in _metrics.py the 0.12.0 version of Verticapy AVG(CASE WHEN {0} = {1} THEN 1 ELSE 0 END)
• how accuracy_score is computed now in classification.py in 1.0.0

def accuracy_score(...):
    return _compute_final_score(
        _accuracy_score,
        **locals(),
    )

def _accuracy_score(...):
    return (tp + tn) / (tp + tn + fn + fp)

def confusion_matrix(...) -> np.ndarray:
        res = _executeSQL(
            query=f"""
            SELECT 
                CONFUSION_MATRIX(obs, response 
                USING PARAMETERS num_classes = 2) OVER() 
            FROM 
                (SELECT 
                    DECODE({y_true}, '{pos_label}', 
                           1, NULL, NULL, 0) AS obs, 
                    DECODE({y_score}, '{pos_label}', 
                           1, NULL, NULL, 0) AS response 
                 FROM {input_relation}) VERTICAPY_SUBTABLE;""",
            title="Computing Confusion matrix.",
            method="fetchall",
        )
        return np.round(np.array([x[1:-1] for x in res])).astype(int)

def _compute_final_score(...):
    cm = confusion_matrix(y_true, y_score, input_relation, **kwargs)
    return _compute_final_score_from_cm(metric, cm, average=average, multi=multi

[oualib]

@zacandcheese did you find any solution for this one?