求助：将MySQL分组中位数计算逻辑转换为SQL Server实现

Hey there! Let's translate that MySQL group median logic into SQL Server. Unlike MySQL's reliance on session variables, SQL Server has built-in window functions that make this task cleaner and more maintainable. Here's how to do it:

Step-by-Step Explanation & Code #

First, let's recap the core logic from your MySQL code: we need to number rows within each group, count total rows per group, then select the middle row(s) and average them to get the median. SQL Server's window functions handle this without needing variables.

Here's the equivalent SQL Server code:

WITH GroupedHeights AS (
    SELECT
        gender,
        height,
        -- Assign row numbers within each gender group, ordered by height
        ROW_NUMBER() OVER (PARTITION BY gender ORDER BY height) AS row_num,
        -- Count total rows in each gender group
        COUNT(*) OVER (PARTITION BY gender) AS total_rows
    FROM heights
)
SELECT
    gender AS median_group,
    AVG(CAST(height AS DECIMAL(10,2))) AS median -- Cast to avoid integer division
FROM GroupedHeights
-- Filter rows that fall at the median position(s)
WHERE row_num IN (
    FLOOR((total_rows + 1) / 2.0),
    CEILING((total_rows + 1) / 2.0)
)
GROUP BY gender;

Key Differences from MySQL #

• No session variables needed: We use a CTE (GroupedHeights) to handle row numbering and group counts in one pass, which is more efficient and avoids issues with session state.
• Window functions replace variable logic:
  • ROW_NUMBER() OVER (PARTITION BY gender ORDER BY height) does exactly what your @row_number variable did—numbers rows within each gender group.
  • COUNT(*) OVER (PARTITION BY gender) replaces the subquery you used to get total_of_group, and it's calculated in the same scan as the row numbers.
• Explicit casting for averages: If height is an integer, casting it to a decimal ensures we get a precise average instead of integer division (which would truncate results).

This query will return the same median per group as your original MySQL code, but it's more aligned with SQL Server's best practices.

内容的提问来源于stack exchange，提问作者Bayo O