--- title: "Advanced SQL Features" --- # Advanced SQL Features HeliosDB-Lite supports a rich SQL dialect including window functions, recursive CTEs, JSONB operations, full-text search, UPSERT, DISTINCT ON, prepared statements, and CASE expressions. This tutorial walks through each feature. ## Prerequisites - HeliosDB-Lite - Access to the SQL shell (REPL, PostgreSQL wire protocol, or REST API) ## Setup -- Create Sample Tables ```sql CREATE TABLE sales ( id INT PRIMARY KEY, employee TEXT, department TEXT, amount FLOAT8, sale_date TEXT ); INSERT INTO sales VALUES (1, 'Alice', 'East', 500, '2025-01-15'); INSERT INTO sales VALUES (2, 'Bob', 'East', 300, '2025-01-16'); INSERT INTO sales VALUES (3, 'Alice', 'East', 700, '2025-02-10'); INSERT INTO sales VALUES (4, 'Carol', 'West', 400, '2025-02-12'); INSERT INTO sales VALUES (5, 'Dave', 'West', 600, '2025-03-01'); INSERT INTO sales VALUES (6, 'Carol', 'West', 350, '2025-03-05'); INSERT INTO sales VALUES (7, 'Alice', 'East', 800, '2025-03-10'); INSERT INTO sales VALUES (8, 'Bob', 'East', 450, '2025-03-15'); ``` ## 1. Window Functions Window functions compute values across a set of rows related to the current row, without collapsing the result into groups. ### ROW_NUMBER ```sql SELECT employee, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank FROM sales; ``` ### RANK and DENSE_RANK ```sql SELECT employee, department, amount, RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS dense_rank FROM sales; ``` ### LAG and LEAD Access values from previous or next rows: ```sql SELECT employee, sale_date, amount, LAG(amount, 1) OVER (PARTITION BY employee ORDER BY sale_date) AS prev_amount, LEAD(amount, 1) OVER (PARTITION BY employee ORDER BY sale_date) AS next_amount FROM sales; ``` Expected output (for Alice): ``` employee | sale_date | amount | prev_amount | next_amount ----------+------------+--------+-------------+------------ Alice | 2025-01-15 | 500 | | 700 Alice | 2025-02-10 | 700 | 500 | 800 Alice | 2025-03-10 | 800 | 700 | ``` ### NTILE Distribute rows into N roughly equal buckets: ```sql SELECT employee, amount, NTILE(4) OVER (ORDER BY amount) AS quartile FROM sales; ``` ### SUM / AVG as Window Functions ```sql SELECT employee, amount, SUM(amount) OVER (PARTITION BY department ORDER BY sale_date) AS running_total, AVG(amount) OVER (PARTITION BY department) AS dept_avg FROM sales; ``` ## 2. Common Table Expressions (CTEs) ### Non-Recursive CTE ```sql WITH top_sellers AS ( SELECT employee, SUM(amount) AS total FROM sales GROUP BY employee ) SELECT * FROM top_sellers ORDER BY total DESC; ``` ### Recursive CTE Generate a sequence of numbers: ```sql WITH RECURSIVE numbers(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM numbers WHERE n < 10 ) SELECT n FROM numbers; ``` Traverse a hierarchy: ```sql CREATE TABLE categories ( id INT PRIMARY KEY, name TEXT, parent_id INT ); INSERT INTO categories VALUES (1, 'Electronics', NULL); INSERT INTO categories VALUES (2, 'Computers', 1); INSERT INTO categories VALUES (3, 'Laptops', 2); INSERT INTO categories VALUES (4, 'Phones', 1); WITH RECURSIVE tree(id, name, depth) AS ( SELECT id, name, 0 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT * FROM tree; ``` ``` id | name | depth ----+-------------+------ 1 | Electronics | 0 2 | Computers | 1 4 | Phones | 1 3 | Laptops | 2 ``` ## 3. JSONB Operations HeliosDB supports 20+ JSONB functions for semi-structured data. ```sql CREATE TABLE events ( id INT PRIMARY KEY, data JSONB ); INSERT INTO events VALUES (1, '{"type": "click", "x": 100, "y": 200, "tags": ["ui", "nav"]}'); INSERT INTO events VALUES (2, '{"type": "scroll", "offset": 450, "tags": ["ui"]}'); -- Extract a field SELECT id, data->>'type' AS event_type FROM events; -- Nested access SELECT id, data->'tags'->>0 AS first_tag FROM events; -- Containment check SELECT * FROM events WHERE data @> '{"type": "click"}'; ``` ## 4. Full-Text Search Use the `@@` operator for full-text search: ```sql CREATE TABLE articles ( id INT PRIMARY KEY, title TEXT, body TEXT ); INSERT INTO articles VALUES (1, 'Database Internals', 'How databases store and retrieve data efficiently'); INSERT INTO articles VALUES (2, 'Query Optimization', 'Techniques for making SQL queries run faster'); INSERT INTO articles VALUES (3, 'Network Protocols', 'Understanding TCP/IP and HTTP'); -- Full-text search with @@ SELECT id, title FROM articles WHERE body @@ 'database'; ``` ``` id | title ----+------------------- 1 | Database Internals ``` ## 5. UPSERT (INSERT... ON CONFLICT) Insert a row or update it if a conflict occurs on the primary key: ```sql CREATE TABLE counters ( name TEXT PRIMARY KEY, value INT ); INSERT INTO counters VALUES ('visits', 1); -- Upsert: increment if exists, insert if not INSERT INTO counters VALUES ('visits', 1) ON CONFLICT (name) DO UPDATE SET value = counters.value + 1; SELECT * FROM counters; ``` ``` name | value --------+------ visits | 2 ``` ## 6. DISTINCT ON Return only the first row for each distinct value of a column: ```sql SELECT DISTINCT ON (department) department, employee, amount FROM sales ORDER BY department, amount DESC; ``` ``` department | employee | amount ------------+----------+------- East | Alice | 800 West | Dave | 600 ``` This returns the highest sale per department. ## 7. PREPARE / EXECUTE Prepared statements parse and plan a query once, then execute it multiple times with different parameters: ```sql PREPARE find_sales AS SELECT * FROM sales WHERE employee = $1 AND amount > $2; EXECUTE find_sales('Alice', 600); ``` ``` id | employee | department | amount | sale_date ----+----------+------------+--------+----------- 3 | Alice | East | 700 | 2025-02-10 7 | Alice | East | 800 | 2025-03-10 ``` ```sql EXECUTE find_sales('Bob', 400); ``` ``` id | employee | department | amount | sale_date ----+----------+------------+--------+----------- 8 | Bob | East | 450 | 2025-03-15 ``` ## 8. CASE Expressions ### Searched CASE ```sql SELECT employee, amount, CASE WHEN amount >= 700 THEN 'High' WHEN amount >= 400 THEN 'Medium' ELSE 'Low' END AS tier FROM sales; ``` ### Simple CASE ```sql SELECT employee, department, CASE department WHEN 'East' THEN 'Eastern Region' WHEN 'West' THEN 'Western Region' ELSE 'Other' END AS region_name FROM sales; ``` ## Tips and Troubleshooting - **Window function performance**: Window functions with PARTITION BY benefit from indexes on the partition column. Large result sets without partitioning can be memory-intensive. - **Recursive CTE depth**: The default maximum recursion depth is 1,000 iterations. Configure via `resource_quotas.max_recursive_cte_depth` in `heliosdb.toml`. - **JSONB indexing**: For frequently queried JSONB fields, consider extracting them into regular columns for better index support. - **Full-text search**: The `@@` operator performs a case-insensitive substring match. For more advanced ranking, combine with `ORDER BY` on relevance scores. - **UPSERT limitations**: The `ON CONFLICT` clause requires a unique or primary key constraint on the conflict target columns. - **Prepared statement lifecycle**: Prepared statements persist for the lifetime of the database connection. They are not shared across connections. - **DISTINCT ON ordering**: The `ORDER BY` clause must start with the same columns used in `DISTINCT ON`. Additional sort columns after the distinct columns determine which row is selected from each group.