[大数据]窗口函数 嵌套查询 TopN避坑

前两天写一个【求各类别下出现次数最多的前两个品牌】的SQL，一直报2附近有语法错误。

今天将此记录一下

我写的

SELECT *
FROM (
SELECT *,
row_number() over(partition by a.categoryname ORDER BY a.brandcount DESC) as ranking
FROM 
(SELECT categoryname,brandname,count(brandname)as brandcount 
FROM vw_sku_basic_info GROUP BY categoryname,brandname))
as a WHERE ranking <= 2

报错信息

SELECT *
FROM (
SELECT *,
row_number() over(partition by a.categoryname ORDER BY a.brandcount DESC) as ranking
FROM (SELECT categoryname,brandname,count(brandname)as brandcount FROM vw_sku_basic_info GROUP BY categoryname,brandname))as a WHERE ranking <= 2
> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]')' 附近有语法错误。 (102)

正确的?

SELECT *
FROM (
SELECT *,
row_number() over(partition by a.categoryname ORDER BY a.brandcount DESC) as ranking
FROM 
(SELECT categoryname,brandname,count(brandname)as brandcount
FROM vw_sku_basic_info GROUP BY categoryname,brandname)
as a) 
as b WHERE ranking <= 2 ORDER BY b.categoryname

结果展示

错误原因

括号问题，嵌套查询时要注意括号的组合。因为我这里所求的topN中的排序列是需要从上一个查询结果中得到。因此比正常情况下使用row_number over()要多一层嵌套

举一反三模板

经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

本题的sql语句修改下（将where字句里的条件修改成N），就可以成为这类问题的一个万能模板，遇到这类问题往里面套就可以了：

 topN问题 sql模板
select *
from (
   select *, 
          row_number() over (partition by 要分组的列名
                       order by 要排序的列名 desc) as ranking
   from 表名) as a
where ranking <= N

?

?