CTE函数化是否纳入SQL标准？纯CTE实现Strahler数计算求助

Great question! Let's break this down into two parts: first, the status of parameterized CTEs (what you're calling "Function on CTE") in SQL standards and major databases, then a pure CTE solution to calculate Strahler numbers for your river network graph.

Part 1: Parameterized CTEs (Function on CTE) Standard & Support #

First off, parameterized CTEs (treating a CTE like a callable function with arguments) are not part of the official SQL standard (including SQL:2016 and later revisions). This syntax is a non-standard extension that hasn't been formalized into the core SQL spec.

As for mainstream RDBMS support:

• PostgreSQL: No native support for this exact syntax. You can simulate it using LATERAL joins with recursive CTEs, or wrap the logic in a custom PL/pgSQL function (which you've already done in your example).
• MySQL/MariaDB: Doesn't support parameterized CTEs. Recursive CTEs here are limited to unparameterized self-references.
• SQL Server: Similarly, no support for treating CTEs as parameterized functions. Recursive logic relies on standard anchor/recursion clauses without explicit parameters.
• Oracle: Also lacks this syntax. Recursive operations use either CONNECT BY clauses or standard unparameterized recursive CTEs; parameterization requires using stored functions or bind variables.

Part 2: Pure CTE Solution for Strahler Numbers #

To calculate Strahler numbers using only CTEs, we need to process nodes bottom-up (starting from leaves, moving up to the root) since a node's Strahler number depends entirely on its children's values. Here's a working implementation (tested with PostgreSQL, adaptable to other databases with minor tweaks):

WITH RECURSIVE node_levels AS (
    -- Anchor: Leaf nodes (no children) get level 0
    SELECT 
        node, 
        0 AS level
    FROM streams s
    WHERE NOT EXISTS (
        SELECT 1 FROM streams WHERE to_node = s.node
    )
    UNION ALL
    -- Recursive step: Parent nodes get level = child's level + 1
    SELECT 
        s.node, 
        nl.level + 1
    FROM streams s
    JOIN node_levels nl ON s.node = nl.to_node
),
ordered_nodes AS (
    -- Sort nodes from deepest (leaves) to shallowest (root) to ensure bottom-up processing
    SELECT node, level
    FROM node_levels
    ORDER BY level DESC
),
strahler_calc AS (
    -- Anchor: Leaf nodes have Strahler number 1
    SELECT 
        node, 
        1 AS sn
    FROM ordered_nodes
    WHERE level = 0
    UNION ALL
    -- Recursive step: Calculate Strahler number for parent nodes
    SELECT 
        s.node,
        CASE
            -- Rule 3: If 2+ children have the max Strahler number, add 1
            WHEN SUM(CASE WHEN sc.sn = sub_max.max_sn THEN 1 ELSE 0 END) >= 2 
                THEN sub_max.max_sn + 1
            -- Rule 2: If only one child has the max Strahler number, keep the max value
            ELSE sub_max.max_sn
        END AS sn
    FROM streams s
    JOIN strahler_calc sc ON s.node = sc.to_node
    CROSS JOIN (
        -- Get the highest Strahler number among this node's children
        SELECT MAX(sn) AS max_sn
        FROM strahler_calc sc2
        WHERE sc2.to_node = s.node
    ) AS sub_max
    GROUP BY s.node, sub_max.max_sn
)
-- Final output: Compare calculated Strahler number with expected values
SELECT 
    sc.node, 
    sc.sn AS calculated_strahler, 
    s.expected_order
FROM strahler_calc sc
JOIN streams s ON sc.node = s.node
ORDER BY sc.node;

How This Works: #

1. node_levels: Recursively assigns a "depth" to each node, starting at 0 for leaves (nodes with no children) and incrementing by 1 for each parent level up.
2. ordered_nodes: Ensures we process nodes from leaves up to the root, so when we calculate a parent's Strahler number, all its children's values are already computed.
3. strahler_calc:
   • Starts by setting all leaf nodes' Strahler number to 1 (per Rule 1).
   • For each parent node, it first finds the highest Strahler number among its children. Then it counts how many children have that maximum value:
     • If 2 or more children have the max value, the parent's number is max + 1 (Rule 3).
     • If only one child has the max value, the parent's number matches the max (Rule 2).

Adaptations for Other Databases: #

• SQL Server/MySQL: The SUM(CASE...) logic works universally (PostgreSQL's FILTER clause isn't supported everywhere, but this alternative is compatible).
• Oracle: Use CONNECT BY for the node_levels CTE if preferred, but the recursive CTE syntax is also supported in recent versions.

内容的提问来源于stack exchange，提问作者Michael Buen