SELSimple Expression Language
GitHub

Unnormalised data

One wide table where every line repeats its order's customer and product — an export, a spreadsheet, a nightly dump — with what comes with that: the same customer under two spellings of their name and e-mail, and a list of tags packed into one column. Here it is in MariaDB, with the case-insensitive collation a hand-made table often has.

The schema

text
order_export
  line_id          INT PK
  order_no         VARCHAR   'SO-1001', … (two lines per order)
  order_date       DATE
  customer_name    VARCHAR   'Anna Nowak' and 'anna nowak '
  customer_email   VARCHAR   'anna@example.pl' and 'Anna@Example.pl'; 'dawid@example.pl' and 'dawid@example.pl '
  customer_city    VARCHAR   Kraków, Berlin, Lyon, Malmö, Warszawa
  sku, product_name, category
  qty              INT
  unit_price       DECIMAL(10,2)
  tags             VARCHAR   'gift;promo', 'b2b', '', …
DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci

Forty lines. The seed is examples/sql-flat/seed.mariadb.sql.

Describing it

python
def relation(table, alias, **fields):
    return Binding.relation(table, alias, fields={
        name: Binding.column(name, alias, kind) for name, kind in fields.items()})


SCHEMA = {
    'EXPORT': relation('order_export', 'x', line_id='NUM', order_no='TEXT', order_date='TEXT',
                       customer_name='TEXT', customer_email='TEXT', customer_city='TEXT',
                       sku='TEXT', product_name='TEXT', category='TEXT', qty='NUM',
                       unit_price='NUM', tags='TEXT'),
}

The pipelines

Revenue per city, February and March. A filter on the date and a group — SQL from end to end:

sel
# Lines and revenue per customer city, February and March.
EXPORT
  .> FILTER(_["order_date"] $>= "2025-02-01" AND _["order_date"] $< "2025-04-01")
  .> BUCKET(_["customer_city"], RECORD(
       "city", _K,
       "lines", COUNT(_),
       "revenue", SUM(_, _["qty"] * _["unit_price"])))
  .> SORT_BY(_["revenue"], "DESC")

Distinct customers per city. A customer is their e-mail address, trimmed and lower-cased. SQL could compute that — but the de-duplication would then compare the results under MariaDB's collation, which decides for itself which strings are "the same" (utf8mb4_general_ci ignores case and trailing spaces). SEL's identity is exact bytes, so the planner keeps the normalisation, the de-duplication and the grouping in memory, and the database only filters:

sel
# Distinct customers per city, a customer being a normalised e-mail address.
EXPORT
  .> FILTER(_["category"] $!= "books")
  .> MAP(RECORD("city", _["customer_city"], "email", LOWER(TRIM(_["customer_email"]))))
  .> DEDUPE()
  .> BUCKET(_["city"], RECORD("city", _K, "customers", COUNT(_)))
  .> SORT_BY(_["city"])

Lines per tag. The tags of every line are joined and split again, which turns a list column into a list of tags — and a list is something no SQL expression yields, so this pipeline stays in memory whole:

sel
# How many lines carry each tag of the ;-separated tags column.
EXPORT
  .> FILTER(_["tags"] $!= "")
  .> MAP(_["tags"])
  .> JOIN(";")
  .> SPLIT(";")
  .> BUCKET(_, RECORD("tag", _K, "lines", COUNT(_)))
  .> SORT_BY(_["tag"])

Running them

python
with open(os.path.join(HERE, file), encoding='utf-8') as fh:
    program = compile(fh.read())
plan = plan_hybrid(program, 'mariadb', SCHEMA)
rows = execute_hybrid(plan, runner(conn), tables if plan.pure_memory else None)

What it prints

text
1. revenue per city, February and March
   plan        pure_sql
   reads       order_export
   sql         SELECT CAST(`x`.`customer_city` AS CHAR) COLLATE utf8mb4_nopad_bin AS `city`, COUNT(*) AS `lines`, COALESCE(SUM((`x`.`qty` * `x`.`unit_price`)), 0) AS `revenue` FROM `order_export` `x` WHERE ((CAST(`x`.`order_date` AS CHAR) COLLATE utf8mb4_nopad_bin >= CAST('2025-02-01' AS CHAR) COLLATE utf8mb4_nopad_bin) AND (CAST(`x`.`order_date` AS CHAR) COLLATE utf8mb4_nopad_bin < CAST('2025-04-01' AS CHAR) COLLATE utf8mb4_nopad_bin)) GROUP BY CAST(`x`.`customer_city` AS CHAR) COLLATE utf8mb4_nopad_bin ORDER BY COALESCE(SUM((`x`.`qty` * `x`.`unit_price`)), 0) DESC
   | city=Kraków  lines=9  revenue=2066.45
   | city=Berlin  lines=5  revenue=1358.37
   | city=Warszawa  lines=2  revenue=559.78
   | city=Malmö  lines=2  revenue=338.90
   | city=Lyon  lines=2  revenue=213.90
   in memory   same rows
2. distinct customers per city, by normalised e-mail
   plan        hybrid
   reads       order_export
   sql         SELECT `x`.* FROM `order_export` `x` WHERE (CAST(`x`.`category` AS CHAR) COLLATE utf8mb4_nopad_bin <> CAST('books' AS CHAR) COLLATE utf8mb4_nopad_bin)
   | city=Berlin  customers=2
   | city=Kraków  customers=2
   | city=Lyon  customers=1
   | city=Malmö  customers=1
   | city=Warszawa  customers=1
   in memory   same rows
3. lines per tag
   plan        pure_memory
   reads       order_export
   | tag=b2b  lines=5
   | tag=clearance  lines=4
   | tag=gift  lines=9
   | tag=promo  lines=14
   in memory   same rows

The first plan is one statement: the date compared as text under a binary collation, grouped by the city under the same collation, so Kraków and krakow would stay apart. The second is hybrid at the planner's identity barrier: the WHERE runs in MariaDB, and everything from the normalising MAP on runs in memory. Anna's two spellings and Dawid's trailing space each become one customer — because the application said how to normalise, not because the database's collation happened to agree. The third is pure_memory; the database supplies the table, and SEL does the rest.

View this page's Markdown on GitHub