SQL中如何对窗口方程(window functions)进行COUNT聚合运算？

Question

If countries were grouped by percent forestation in quartiles,
which group had the most countries in it in 2016?
如果国家根据森林覆盖率排序后再被划分为四分位(0-25%, 26-50%, 51-75%, 76-100%),
哪个区间段国家的个数是最多的。
我打算先用window function计算区间段然后再用COUNT排序
以下是我目前写出的代码，可是COUNT不能用在ranking上。

SELECT f.country_code, f.country_name, f.forest_area_sqkm,
l.total_area_sq_mi * 2.59 AS total_area_sq_km,
f.forest_area_sqkm/(l.total_area_sq_mi2.59) AS percent_of_forest_area,
CAST(f.forest_area_sqkm/(l.total_area_sq_mi2.59)100 AS DECIMAL(18,2)) AS PERCENT,
NTILE(4) OVER (ORDER BY f.forest_area_sqkm/(l.total_area_sq_mi2.59)) AS RANKING
FROM forest_area f
JOIN land_area l
ON f.country_code = l.country_code
AND f.year = l.year
WHERE f.year = 2016

Answer 1

使用 ntile ，是平均分布，不能达到指定区段的分布

使用 DENSE_RANK 来分布可能更好一些

另外，在你这个查询外，套一层查询，这个指令作为子查询，很容易得到结果

select sum(case when PERCENT<=25 then 1 else 0 end) as [0-25]
    ,sum(case when PERCENT<=50 and PERCENT>25 then 1 else 0 end) as [26-50]
    ,sum(case when PERCENT<=75 and PERCENT>50 then 1 else 0 end) as [51-75]
    ,sum(case when PERCENT>75 then 1 else 0 end) as [76-100]
from (
    SELECT f.country_code, f.country_name, f.forest_area_sqkm,
        l.total_area_sq_mi * 2.59 AS total_area_sq_km,
        f.forest_area_sqkm/(l.total_area_sq_mi2.59) AS percent_of_forest_area,
        CAST(f.forest_area_sqkm/(l.total_area_sq_mi2.59)100 AS DECIMAL(18,2)) AS PERCENT,
        NTILE(4) OVER (ORDER BY f.forest_area_sqkm/(l.total_area_sq_mi2.59)) AS RANKING
    FROM forest_area f
    JOIN land_area l
    ON f.country_code = l.country_code
    AND f.year = l.year
    WHERE f.year = 2016
) a