CARTmodel.py

# -*- coding: utf-8 -*-
"""Untitled13.ipynb

Automatically generated by Colab.

Original file is located at
    https://colab.research.google.com/drive/1tWP7KMpk-I8Q4CwxDF7r_O46NkznT3Ye
"""

-- Example dataset: housing-like data
CREATE TABLE housing (
    id SERIAL PRIMARY KEY,
    rooms INT,
    sqft INT,
    age INT,
    price FLOAT
);

-- Insert synthetic rows (~1M for demo; scale down if laptop is slow)
INSERT INTO housing (rooms, sqft, age, price)
SELECT
    (random()*5)::INT + 1,
    (random()*2000)::INT + 300,
    (random()*50)::INT,
    (random()*200000 + 50000)::INT
FROM generate_series(1, 1000000);

-- Function to compute variance reduction for a candidate split
CREATE OR REPLACE FUNCTION variance_reduction(
    feature TEXT,
    threshold FLOAT
) RETURNS FLOAT AS $$
DECLARE
    total_var FLOAT;
    left_var FLOAT;
    right_var FLOAT;
    n_total INT;
    n_left INT;
    n_right INT;
    reduction FLOAT;
BEGIN
    -- Total variance
    EXECUTE format('SELECT VARIANCE(price), COUNT(*) FROM housing')
    INTO total_var, n_total;

    -- Left split variance
    EXECUTE format('SELECT VARIANCE(price), COUNT(*) FROM housing WHERE %I <= $1', feature)
    INTO left_var, n_left
    USING threshold;

    -- Right split variance
    EXECUTE format('SELECT VARIANCE(price), COUNT(*) FROM housing WHERE %I > $1', feature)
    INTO right_var, n_right
    USING threshold;

    -- Weighted child variance
    reduction := total_var - (
        (n_left::FLOAT/n_total)*COALESCE(left_var,0) +
        (n_right::FLOAT/n_total)*COALESCE(right_var,0)
    );
    RETURN reduction;
END;
$$ LANGUAGE plpgsql;