SQLZOO 9-Window function

SQLZOO教程：窗口函数应用解析

最新推荐文章于 2024-08-09 11:59:39 发布

原创最新推荐文章于 2024-08-09 11:59:39 发布 · 216 阅读

0 ·

CC 4.0 BY-SA版权

Sql 专栏收录该内容

3 篇文章

订阅专栏

该博客主要介绍了SQLZOO教程中的窗口函数使用，包括暖身练习、选举赢家分析、PARTITION BY子句的应用、爱丁堡选区详情、仅显示胜者的结果以及苏格兰席位的深入探讨。

9-Window function

1.Warming up
2.Who won?
3.PARTITION BY
4.Edinburgh Constituency
5.Winners Only
6.Scottish seats

练习题链接： https://sqlzoo.net/wiki/Window_functions

1.Warming up

SELECT lastName
       ,party
       ,votes
FROM ge
WHERE constituency = 'S14000024' 
  AND yr = 2017
ORDER BY votes DESC

2.Who won?

SELECT party
       ,votes
       ,RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000024' 
  AND yr = 2017
ORDER BY party

3.PARTITION BY

SELECT yr
       ,party
       ,votes
       ,RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,yr

4.Edinburgh Constituency

SELECT constituency
       ,party
       ,votes
       ,RANK() OVER(PARTITION BY constituency ORDER BY votes DESC) AS posn
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY posn,constituency

5.Winners Only

SELECT constituency
       ,party
FROM (SELECT constituency
             ,party
             ,RANK() OVER(PARTITION BY constituency ORDER BY votes DESC) AS posn
      FROM ge
      WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
        AND yr  = 2017
      ORDER BY constituency,votes DESC) a
where posn=1

6.Scottish seats

SELECT party
       ,count(1)
from (SELECT constituency
             ,party
             ,RANK() OVER(PARTITION BY constituency ORDER BY votes DESC) AS posn
      FROM ge
      WHERE yr  = 2017 
        and constituency like 'S%'
      ORDER BY constituency,votes DESC) a
WHERE posn=1
GROUP BY party