窗口函数 / 聚合

SELECT
    Name,
    CountryCode,
    Min(Population) over (PARTITION BY CountryCode) min_population,
    Max(Population) over (PARTITION BY CountryCode) max_population,
    AVG(Population) over p_code avg_population,
    SUM(Population) over p_code sum_population,
    COUNT(Population) over p_code count_population
FROM city
WINDOW p_code AS (PARTITION BY CountryCode)


Name CountryCode min_population max_population avg_population sum_population count_population
Oranjestad ABW 29,034 29,034 29,034.00 29,034 1
Mazar-e-Sharif AFG 127,800 1,780,000 583,025.00 2,332,100 4
Herat AFG 127,800 1,780,000 583,025.00 2,332,100 4
Qandahar AFG 127,800 1,780,000 583,025.00 2,332,100 4
Kabul AFG 127,800 1,780,000 583,025.00 2,332,100 4
Luanda AGO 118,200 2,022,000 512,320.00 2,561,600 5
Namibe AGO 118,200 2,022,000 512,320.00 2,561,600 5
Benguela AGO 118,200 2,022,000 512,320.00 2,561,600 5
Lobito AGO 118,200 2,022,000 512,320.00 2,561,600 5
...

窗口函数既可以写在 SELECT 子句里，也可以写在单独的 WINDOW 子句里：在那里给窗口起个别名，SELECT 中引用这个别名即可。