← Back to context

Comment by refactor_master

3 days ago

The cool thing about polars is that you can conditionally collect expressions over many layers of business logic, and then compute the result at the end. Doing this in SQL ends up in a hodgepodge of strings and trimmed ends to please the syntax. You can also pretty effortlessly write quite complex conditionals directly in polars, and bridge it easily to the surrounding python.

I find that SQL is only easier to read with minimal abstraction, but as soon as the project gets bigger SQL becomes an unwieldy island of different that has served its purpose after we’re done with reading/writing the data.

7 comments

refactor_master

Reply
fzumstein  3 days ago

This sounds interesting! Do you have a specific example by any chance or blog post/doc references?

  • refactor_master  3 days ago

    It’s just the lazy/expression part of the API, which is really the bread and butter of polars, rather than just being “replacement syntax” for pandas. This allows you to tap into abstraction that SQL can’t keep up with:

      import polars as pl

  # 1. Base Dataset
  lazy_df = pl.LazyFrame(
    {
      "store_id": ["S01", "S02", "S03", "S04", "S05"],
      "revenue": [5000.0, 2400.0, 15000.0, 900.0, 3200.0],
      "margin": [0.45, 0.30, 0.60, 0.15, 0.50],
      "tx_count": [120, 45, 300, 20, 85],
      "returns": [5, 12, 45, 2, 8],
    }
  )

  # 2. Define Layer Abstractions
  def get_kpi_layer() -> list[pl.Expr]:
    return [
      (pl.col("returns") / pl.col("tx_count")).alias("return_rate"),
      (pl.col("revenue") / pl.col("tx_count")).alias("avg_order_value"),
    ]

  def get_threshold_layer(thresholds: dict[str, list[float]]) -> list[pl.Expr]:
    return [
      (pl.col(col) > limit).alias(f"is_{col}above{int(limit)}")
      for col, limits in thresholds.items()
      for limit in limits
    ]

  def get_interaction_layer(numeric_cols: list[str]) -> list[pl.Expr]:
    return [
      (pl.col(a) / (pl.col(b) + 1e-5)).alias(f"ratio_{a}per{b}")
      for i, a in enumerate(numeric_cols)
      for b in numeric_cols[i + 1 :]
    ]

  def get_segmentation_layer() -> list[pl.Expr]:
    return [
      pl.when(pl.col("margin") > 0.4)
      .then(pl.literal("High"))
      .otherwise(pl.literal("Low"))
      .alias("margin_profile")
    ]

  # 3. Consolidate and Execute Single Graph Pass
  thresholds = {"revenue": [1000.0, 5000.0, 10000.0], "tx_count": [50, 100, 200]}
  numeric_cols = ["revenue", "margin", "tx_count", "returns"]

  expr_pool = [
    *get_kpi_layer(),
    *get_threshold_layer(thresholds),
    *get_interaction_layer(numeric_cols),
    *get_segmentation_layer(),
  ]

  final_df = lazy_df.with_columns(expr_pool).collect()

    • _zoltan_  3 days ago

      I'm sorry but this looks much better:

        WITH raw_data AS (

    SELECT * FROM (
        VALUES 
            ('S01', 5000.0, 0.45, 120, 5),
            ('S02', 2400.0, 0.30,  45, 12),
            ('S03', 15000.0, 0.60, 300, 45),
            ('S04',  900.0, 0.15,  20, 2),
            ('S05', 3200.0, 0.50,  85, 8)
    ) AS t(store_id, revenue, margin, tx_count, returns)),

  base_data AS (
    SELECT
        store_id,
        revenue,
        margin,
        CAST(tx_count AS DOUBLE) AS tx_count,
        CAST(returns AS DOUBLE) AS returns
    FROM raw_data
  )

  SELECT

    store_id,
    revenue,
    margin,
    CAST(tx_count AS BIGINT) AS tx_count,
    CAST(returns AS BIGINT) AS returns,

    -- KPI Layer
    returns / tx_count AS return_rate,
    revenue / tx_count AS avg_order_value,

    -- Threshold Layer (matching original alias names)
    revenue > 1000.0 AS is_revenueabove1000,
    revenue > 5000.0 AS is_revenueabove5000,
    revenue > 10000.0 AS is_revenueabove10000,
    tx_count > 50 AS is_tx_countabove50,
    tx_count > 100 AS is_tx_countabove100,
    tx_count > 200 AS is_tx_countabove200,

    -- Interaction Layer (preserving exact numeric formula & aliases)
    revenue / (margin + 1e-5) AS ratio_revenuepermargin,
    revenue / (tx_count + 1e-5) AS ratio_revenuepertx_count,
    revenue / (returns + 1e-5) AS ratio_revenueperreturns,
    margin / (tx_count + 1e-5) AS ratio_marginpertx_count,
    margin / (returns + 1e-5) AS ratio_marginperreturns,
    tx_count / (returns + 1e-5) AS ratio_tx_countperreturns,

    -- Segmentation Layer
    CASE WHEN margin > 0.4 THEN 'High' ELSE 'Low' END AS margin_profile

  FROM base_data;

      3 replies →