MS SQL对分组数据的计算模式

Question

我有一个条目表，每个条目都标有经度、纬度和输入该表的日期。

每个Item有一个Supplier。

对于每个供应商，我想按Lat/Lng对他们的物品进行分组，以找出每个位置的大多数物品的年龄-即MODE。然后，我将把它显示在谷歌地图上，用红绿灯标记显示数据的新旧位置。

我试过遵循其他SQL MODE答案，但他们没有按任何列分组，只是获得一组数据的模式。

下面是我的代码:

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
)
SELECT * FROM cte

得到如下数据:

Lat    Lng    Age    Counter
12.34  56.78  1      5
12.34  56.78  3      2
12.34  56.78  4      24
23.45  67.89  1      21
23.45  67.89  2      16
23.45  67.89  3      13

现在我需要选择哪个年龄是每个Lat/Lng最普遍的分组。尽管摆弄了几个小时，我似乎还是弄不明白。

我期望的数据是:

Lat    Lng    Mode_Age
12.34  56.78  4
23.45  67.89  1

(我将Lat/Lng四舍五入以减少数据点。)

Answer 1

您可以使用ROW_NUMBER来获取每个分组的顶部项

DECLARE @SupplierID int = 12345;
WITH cte AS
(
SELECT 
Round(p.Latitude, 2) As Latitude,
Round(p.Longitude, 2) As Longitude,
v.Age,
Count(*) As Counter,
ROW_NUMBER() OVER (
PARTITION BY Round(p.Latitude, 2),
Round(p.Longitude, 2)
ORDER BY COUNT(*) DESC) As Rn
FROM Items p
CROSS APPLY (VALUES (
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 
ELSE 4 END
) ) As v(Age)
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),
v.Age
)
SELECT
Latitude,
Longitude,
Age
FROM cte
WHERE Rn = 1;

注意使用CROSS APPLY (VALUES来删除代码重复