mssql PERCENTILE_CONT 組按年份

我正在嘗試為箱形圖準備一個數據集，該數據集需要按年分組的 MAX、MIN、MEDIAN 和四分位數。這是我的嘗試，但我無法按年分組：

SELECT
   years,
   MAX(NET_MERIT) AS high_end,
   max(q3) as q3,
   max(MEDIAN) as median,
   max(q1) as q1,
   min(NET_MERIT) as low_end

   from(

   select Year(FA_BDATE) as years ,NET_MERIT,
   PERCENTILE_CONT(0.25) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q1,
   PERCENTILE_CONT(0.50) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as MEDIAN,
   PERCENTILE_CONT(0.75) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q3
   from HerdAnalytics_tbl
   group by Year(FA_BDATE)
   order by Year(FA_BDATE)

   

   ) as sub
   order by years

表格是這樣的：db<>fiddle

我有一個小問題：是PERCENTILE_CONT(0.50)中位數本身嗎？

是的，使用PERCENTILE_CONT(0.50)是一種找到中位數的方法。您可以查看文件的基本語法範例部分PERCENTILE_CONT以查看範例。

關於GROUP BY，該PARTITION BY子句已經創建了年份組，因此您不需要在子查詢上使用 group by。此外，您不應ORDER BY在該子查詢上使用，否則您將收到錯誤消息：

ORDER BY 子句在視圖、內聯函式、派生表、子查詢和公用表表達式中無效，除非還指定了 TOP、OFFSET 或 FOR XML。

嘗試這樣的查詢：

SELECT
   years,
   MAX(NET_MERIT) AS high_end,
   MAX(q3) AS q3,
   MAX(MEDIAN) AS median,
   MAX(q1) AS q1,
   MIN(NET_MERIT) AS low_end

FROM(

   SELECT YEAR(FA_BDATE) AS years ,NET_MERIT,
   PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q1,
   PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS MEDIAN,
   PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q3
   FROM HerdAnalytics_tbl    

) AS sub
GROUP BY years
ORDER BY years;

引用自：https://dba.stackexchange.com/questions/285147