SQLZOO 9-Window function

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

Nosfy

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

阅读量198

点赞数

分类专栏： Sql

本文链接：https://blog.csdn.net/sapruby/article/details/112325279

版权

Sql 专栏收录该内容

3 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

练习题链接： 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