<jupyter_start><jupyter_text>An example of using SQL WITH Common Table Expressions to produce more legibleSQL.A major complaint with SQL is that it composes statements by right-ward nesting.That is: a sequence of operations `A -> B -> C` is represented as `SELECT C FROM SELECT B FROM SELECT A`.However, the SQL 99 standard introduced the `WITH` statement and common tableexpressions ([ref](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL)).These statements allow forward composition.Let's take a look at asking the `data_algebra` ([ref](https://github.com/WinVector/data_algebra)) to emit SQL with and without common table expressions.First we set up some example data.<jupyter_code>import sqlite3

from data_algebra.data_ops import *
import data_algebra.test_util
import data_algebra.SQLite

d = data_algebra.default_data_model.pd.DataFrame({
    'x': [1, 2, 3]
})

d<jupyter_output><empty_output><jupyter_text>Next we set up our calculations. Please note the order they are performed.<jupyter_code>ops = describe_table(d, table_name='d') .\
    extend({'z': 'x + 1'}) .\
    extend({'q': 'z + 2'}) .\
    extend({'h': 'q + 3'})

ops
res_pandas = ops.transform(d)

res_pandas
expect = data_algebra.default_data_model.pd.DataFrame({
    'x': [1, 2, 3],
    'z': [2, 3, 4],
    'q': [4, 5, 6],
    'h': [7, 8, 9]
})

assert data_algebra.test_util.equivalent_frames(res_pandas, expect)
db_model = data_algebra.SQLite.SQLiteModel()
with sqlite3.connect(":memory:") as conn:
    db_model.prepare_connection(conn)
    db_handle = db_model.db_handle(conn)
    db_handle.insert_table(d, table_name='d')
    sql_regular = db_handle.to_sql(ops, pretty=True, use_with=False, annotate=True)
    res_regular = db_handle.read_query(sql_regular)
    sql_with = db_handle.to_sql(ops, pretty=True, use_with=True, annotate=True)
    res_with = db_handle.read_query(sql_with)

assert data_algebra.test_util.equivalent_frames(res_regular, expect)
assert data_algebra.test_util.equivalent_frames(res_with, expect)<jupyter_output><empty_output><jupyter_text>The standard nested SQL for these operations looks like the following.<jupyter_code>print(sql_regular)<jupyter_output>SELECT -- extend({ 'h': 'q + 3'})
 "x",
 "z",
 "q",
 "q" + 3 AS "h"
FROM
  (SELECT -- extend({ 'q': 'z + 2'})
 "x",
 "z",
 "z" + 2 AS "q"
   FROM
     (SELECT -- extend({ 'z': 'x + 1'})
 "x",
 "x" + 1 AS "z"
      FROM "d") "extend_0") "extend_1"<jupyter_text>The common table expression version looks like this, which involves less nesting and values move forward notation.<jupyter_code>print(sql_with)<jupyter_output>WITH "extend_0" AS
  (SELECT -- extend({ 'z': 'x + 1'})
 "x",
 "x" + 1 AS "z"
   FROM "d"),
     "extend_1" AS
  (SELECT -- extend({ 'q': 'z + 2'})
 "x",
 "z",
 "z" + 2 AS "q"
   FROM "extend_0")
SELECT -- extend({ 'h': 'q + 3'})
 "x",
 "z",
 "q",
 "q" + 3 AS "h"
FROM "extend_1"
{'fraction_non_alphanumeric': 0.14767199444058374, 'fraction_numerical': 0.01389854065323141, 'mean_word_length': 4.667322834645669, 'pattern_counts': {'":': 1, '<': 15, '<?xml version=': 0, '>': 17, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '306472', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1090, 'n_tokens_neox': 1029, 'n_words': 284, 'path': 'Examples/with/SQL_WITH.ipynb', 'repo_name': 'WinVector/data_algebra', 'size': '2878'}