英文:
Partitioning on multiple columns?
问题
我正在进行一个个人项目,我有一个名为Movies的表格,格式如下:
电影标题 | 类型 | 发行年份 |
---|---|---|
约翰·威克4 | 动作-惊悚-犯罪 | 2023 |
我将其按类型分成以下表格,因为我想统计每个发行年份创建的类型数:
电影标题 | 类型 | 发行年份 |
---|---|---|
阿凡达 | 动作 | 2022 |
阿凡达 | 冒险 | 2022 |
阿凡达 | 科幻 | 2022 |
蚁人 | 动作 | 2022 |
蚁人 | 冒险 | 2022 |
蚁人 | 科幻 | 2022 |
约翰·威克4 | 动作 | 2023 |
约翰·威克4 | 惊悚 | 2023 |
约翰·威克4 | 犯罪 | 2023 |
为了做到这一点,我尝试使用以下查询按'类型'和'发行年份'两列进行分区:
SELECT 类型, COUNT(类型) OVER (PARTITION BY 类型, 发行年份) as 类型数量, 发行年份
FROM Movies
我期望的结果是这样的表格:
类型 | 类型数量 | 发行年份 |
---|---|---|
动作 | 2 | 2022 |
科幻 | 2 | 2022 |
冒险 | 2 | 2022 |
动作 | 1 | 2023 |
惊悚 | 1 | 2023 |
犯罪 | 1 | 2023 |
然而,当我运行该查询时,我得到了很多重复的结果(相同类型,相同年份,相同计数),结果看起来像这样:结果
任何帮助都将不胜感激,谢谢!
英文:
I'm working a personal project and I have this table called Movies, Formatted as such:
Movie_Title | Genre | Release_Year |
---|---|---|
John Wick 4 | Action-Thriller-Crime | 2023 |
And I split it up by genre into a table as below because
I want to count the number of genres create per Release Year:
Movie_Title | Genre | Release_Year |
---|---|---|
Avatar | Action | 2022 |
Avatar | Adventure | 2022 |
Avatar | Sci-Fi | 2022 |
Ant-Man | Action | 2022 |
Ant-Man | Adventure | 2022 |
Ant-Man | Sci-Fi | 2022 |
John Wick 4 | Action | 2023 |
John Wick 4 | Thriller | 2023 |
John Wick 4 | Crime | 2023 |
To do that I tried to partition it by two columns 'Genre' and 'Release_Year' using this Query:
Select Genre, Count(Genre) OVER (Partition by Genre,Release_Year) as Num_genre, Release_Year
FROM Movies
My Expected outcome is a table as such:
Genre | Num_genre | Release_Year |
---|---|---|
Action | 2 | 2022 |
Sci-Fi | 2 | 2022 |
Adventure | 2 | 2022 |
Action | 1 | 2023 |
Thriller | 1 | 2023 |
Crime | 1 | 2023 |
However when I run that query I end up getting a lot of dupes (same genre same year same count) back and it ends up looking like this:
Any help would be really appreciated thanks!
答案1
得分: 0
使用GROUP BY
聚合子句:
SELECT
Genre,
COUNT(1) AS Num_genre,
Release_Year
FROM
Movies
GROUP BY
Genre,
Release_Year
;
英文:
Use the GROUP BY
aggregation clause:
SELECT
Genre,
COUNT(1) AS Num_genre,
Release_Year
FROM
Movies
GROUP BY
Genre,
Release_Year
;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论