Advanced Nodes¶

Advanced nodes represent sophisticated SQL features that go beyond basic statements, clauses, and expressions. These include window functions, common table expressions, recursive queries, and database-specific extensions.

Overview¶

Advanced nodes handle complex SQL constructs that often involve multiple components working together. They typically appear in modern SQL dialects and provide powerful analytical and data processing capabilities.

Advanced Node Categories¶

Window and Analytical Functions¶

Window Functions - TFunctionCall with OVER clause
Analytical functions (ROW_NUMBER, RANK, DENSE_RANK)
Aggregate functions with windows
Frame specifications and partitioning
Window Specifications - TWindowDef
PARTITION BY clauses
ORDER BY specifications
Frame boundaries (ROWS, RANGE)

Common Table Expressions¶

CTE Nodes - TWithClause, TCTE
Non-recursive CTEs
Recursive common table expressions
Multiple CTE definitions
Recursive Queries
Hierarchical data traversal
Graph processing patterns
Cycle detection mechanisms

Pivoting and Data Transformation¶

PIVOT Operations - TPivotClause
Column-to-row transformations
Dynamic pivot operations
Aggregate specifications
UNPIVOT Operations - TUnpivotClause
Row-to-column transformations
Column name specifications
Value handling

Set Operations and Combinations¶

UNION Operations - TUnionSqlStatement
UNION, UNION ALL
INTERSECT, EXCEPT operations
Multiple set combinations
VALUES Clauses - TValuesSqlStatement
Table value constructors
Inline data specifications
Row value expressions

Database-Specific Features¶

Database-Specific Nodes
Vendor-specific syntax extensions
Proprietary function calls
Platform-specific optimizations

Advanced SQL Patterns¶

Window Function Structure¶

TFunctionCall (with OVER clause)
├── functionName -> function identifier
├── parameters -> function arguments
└── overClause -> TWindowDef
    ├── partitionByClause -> grouping expressions
    ├── orderByClause -> sorting specifications
    └── windowFrame -> frame boundaries

CTE Structure¶

TWithClause
├── cteList -> list of CTE definitions
└── TCTE (individual CTE)
    ├── tableName -> CTE name
    ├── columnList -> optional column names
    └── subquery -> CTE definition query

Common Usage Patterns¶

Analyzing Window Functions¶

public void analyzeWindowFunctions(TSqlStatement stmt) {
    stmt.acceptChildren(new IElementVisitor() {
        public void visitElement(TParseTreeNode node) {
            if (node instanceof TFunctionCall) {
                TFunctionCall func = (TFunctionCall) node;
                if (func.getWindowDef() != null) {
                    analyzeWindowFunction(func);
                }
            }
        }
    });
}

private void analyzeWindowFunction(TFunctionCall func) {
    TWindowDef windowDef = func.getWindowDef();

    // Analyze partitioning
    if (windowDef.getPartitionClause() != null) {
        TExpressionList partitions = windowDef.getPartitionClause().getExpressionList();
        // Process partition expressions
    }

    // Analyze ordering
    if (windowDef.getOrderByClause() != null) {
        TOrderByItemList orderItems = windowDef.getOrderByClause().getItems();
        // Process order specifications
    }

    // Analyze frame
    if (windowDef.getWindowFrame() != null) {
        // Process frame boundaries
    }
}

Processing CTEs¶

public Map<String, TSqlStatement> extractCTEs(TSelectSqlStatement select) {
    Map<String, TSqlStatement> ctes = new HashMap<>();

    if (select.getCteList() != null) {
        TWithClause withClause = select.getCteList();

        for (int i = 0; i < withClause.getCTEs().size(); i++) {
            TCTE cte = withClause.getCTEs().getCTE(i);

            String cteName = cte.getTableName().toString();
            TSqlStatement cteQuery = cte.getSubquery().getSelect();

            ctes.put(cteName, cteQuery);

            // Check for recursive CTE
            if (cte.isRecursive()) {
                processRecursiveCTE(cte);
            }
        }
    }

    return ctes;
}

Handling Set Operations¶

public void analyzeSetOperations(TSqlStatement stmt) {
    if (stmt instanceof TUnionSqlStatement) {
        TUnionSqlStatement union = (TUnionSqlStatement) stmt;

        // Get left and right queries
        TSqlStatement leftQuery = union.getLeftStmt();
        TSqlStatement rightQuery = union.getRightStmt();

        // Determine operation type
        ESetOperatorType operatorType = union.getUnionType();

        switch (operatorType) {
            case eUnion:
                processUnion(leftQuery, rightQuery, false);
                break;
            case eUnionAll:
                processUnion(leftQuery, rightQuery, true);
                break;
            case eIntersect:
                processIntersect(leftQuery, rightQuery);
                break;
            case eExcept:
                processExcept(leftQuery, rightQuery);
                break;
        }
    }
}

Advanced Query Patterns¶

Recursive Query Example¶

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member
    SELECT employee_id, manager_id, name, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Window Function Example¶

SELECT 
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM orders;

PIVOT Example¶

SELECT *
FROM (
    SELECT year, quarter, sales
    FROM quarterly_sales
) AS source
PIVOT (
    SUM(sales)
    FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pivot_table;

Database-Specific Advanced Features¶

SQL Server¶

MERGE statements with complex WHEN clauses
OUTPUT clauses with table variables
Hierarchical data with hierarchyid
Temporal tables with system versioning

PostgreSQL¶

Advanced CTE features (RECURSIVE, MATERIALIZED)
Array functions and operators
JSON/JSONB advanced operations
Custom aggregate functions

Oracle¶

Advanced analytical functions
MODEL clause for spreadsheet calculations
Flashback query capabilities
Object-relational features

MySQL¶

Window functions (8.0+)
JSON table functions
Generated columns
Common table expressions (8.0+)

Performance Considerations¶

Window Function Optimization¶

Partition elimination strategies
Index usage for window ordering
Memory usage for large partitions

CTE Performance¶

Materialization vs inline expansion
Recursive CTE termination conditions
Index usage in CTE definitions