如何使用SQL在表中找到每日序列的每月第N个最高值

Question

问题：查找每个行政区每月的事件总数，按事件数降序排列，并返回排名第N的行政区。也就是说，每个月发生事故最多的是第三、第五、第七等行政区。

我可以毫无问题地得到每个行政区每月发生的事件数量。

SELECT borough_name, FORMAT_DATE("%Y-%m", date_of_call) Month_of_Incident,
COUNT(incident_number) Number_of_Incidents,
FROM `bigquery-public-data.london_fire_brigade.fire_brigade_service_calls` 
GROUP BY Month_of_Incident, borough_name
ORDER BY Month_of_Incident, Number_of_Incidents DESC

它给出：

Row     borough_name    Month_of_Incident   Number_of_Incidents 
1       WESTMINSTER     2017-01             620

2       CAMDEN          2017-01             401

3       SOUTHWARK       2017-01             389

4       LAMBETH         2017-01             377

5       TOWER HAMLETS   2017-01             334

我尝试使用Row_Number作为按日期选择前N的方法，但得到：

Window ORDER BY expression references column date_of_call which is neither grouped nor aggregated

当我尝试在窗口函数中放置date_of_call时。我之所以不能这样做，是因为我使用COUNT来获取事件数，而不是能够直接引用窗口函数可以聚合的带有int或float的列吗？

我希望能够调整外部row_number/rank_number=X、Y、Z或row_number/rank_number>=一些值和日期选择，以便能够在每周、每月、每年的范围内提取第N个最高或有序的条目范围。即，其中rn=3，其中rn<=5.

例如，当完成时，第三高的输出应该是：

Row      Borough_Name     Month_of_Incident     Number_of_Incidents
1        SOUTHWARK        2017-01               389
2        TOWER HAMLETS    2017-02               345
3        LAMBETH          2017-03               348

提前感谢您的帮助。

Answer 1

尝试向查询中添加另一个参数。LIMIT＝5(其中5可以由"top-N"值代替(。

这应该将数字限制在顶部"；N〃；使用其他字段设置的间隔值。

Answer 2

最终解决了这个问题。我认为问题在于，我试图在创建格式化的同一查询中，按date_of_call的格式化形式执行分区。一旦我把它从窗口中分离出来，我就能得到想要的结果。

SELECT *
FROM
(
SELECT DENSE_RANK() OVER(PARTITION BY Month_of_Incident ORDER BY Month_of_Incident, Number_of_Incidents DESC) rank_no,
borough_name, Month_of_Incident, Number_of_Incidents
FROM
(
SELECT borough_name, FORMAT_DATE("%Y-%m", date_of_call) Month_of_Incident,
COUNT(incident_number) Number_of_Incidents
FROM `bigquery-public-data.london_fire_brigade.fire_brigade_service_calls` 
GROUP BY Month_of_Incident, borough_name
ORDER BY Month_of_Incident, Number_of_Incidents DESC
)
ORDER BY Month_of_Incident, Number_of_Incidents DESC
) A
WHERE A.rank_no = 2

哪个给出：

Row     rank_no borough_name    Month_of_Incident   Number_of_Incidents 
1       2       CAMDEN          2017-01             401
2       2       CAMDEN          2017-02             348
3       2       SOUTHWARK       2017-03             372
4       2       CAMDEN          2017-04             412

为了清楚起见，我使用了FORMAT而不是DATEPART，这样它仍然适用于跨越多年的数据。DATEPART会将所有月份汇总在一起，而不考虑年份。

WHERE子句可以更改为WHERE A.rank_no<=3，WHERE A.rank_no BETWEEN 3 AND 5，etc.，每月获得前N等：

Row     rank_no borough_name    Month_of_Incident   Number_of_Incidents 
1       1       WESTMINSTER     2017-01             620
2       2       CAMDEN          2017-01             401
3       3       SOUTHWARK       2017-01             389
4       1       WESTMINSTER     2017-02             587
5       2       CAMDEN          2017-02             348
6       3       TOWER HAMLETS   2017-02             345

这不是我最初想要的，但事实证明，这样做是一个很好的奖励。