MS SQL 按经纬度分组后计算物品账龄众数(MODE)的实现问题

你只需要在原有CTE的基础上，加一层窗口函数排序，筛选出每组排名第一的账龄即可，完整实现代码如下：

DECLARE @SupplierID int
SET @SupplierID = 12345

WITH cte AS
(
SELECT 
Round(p.Latitude,2) As Latitude,
Round(p.Longitude,2) As Longitude,
CASE 
WHEN p.Date >= getdate()-30 THEN 1 
WHEN p.Date >= getdate()-60 AND p.Date < getdate()-30 THEN 2 
WHEN p.Date >= getdate()-90 AND Date < getdate()-60 THEN 3 
WHEN p.Date < getdate()-90 THEN 4 END As Age,
Count(*) As Counter
FROM Items p

WHERE
p.SupplierID = @SupplierID AND
p.Latitude is not null AND
p.Longitude is not null

GROUP BY Round(p.Latitude,2),Round(p.Longitude,2),CASE 
WHEN p.Date >= getdate()-30 THEN 1 
WHEN p.Date >= getdate()-60 AND p.Date < getdate()-30 THEN 2 
WHEN p.Date >= getdate()-90 AND Date < getdate()-60 THEN 3 
WHEN p.Date < getdate()-90 THEN 4 END
),
-- 新增排序CTE，按经纬度分组对计数倒序排名
ranked_cte AS (
    SELECT 
        Latitude,
        Longitude,
        Age AS Mode_Age,
        ROW_NUMBER() OVER(PARTITION BY Latitude, Longitude ORDER BY Counter DESC) AS rank_num
    FROM cte
)
-- 取每组排名第一的记录即为对应经纬度的账龄众数
SELECT Latitude AS Lat, Longitude AS Lng, Mode_Age 
FROM ranked_cte 
WHERE rank_num = 1

补充说明：

• 如果存在多个账龄计数相同、同为最高的场景，你希望返回所有并列的众数，把ROW_NUMBER()替换为RANK()即可。
• 窗口函数的PARTITION BY子句就是多字段分组求众数的核心逻辑，后续如果要增加分组维度（比如同时按供应商+经纬度分组），直接加在PARTITION BY后面即可适配。

内容的提问来源于stack exchange，提问作者user2470281