SQL编号 - Row_Number（） - 允许重复行号

I'm using SQL Server 2008. I have this data returned in a query that looks pretty much like this ordered by Day and ManualOrder...

我正在使用SQL Server 2008.我在查询中返回了这些数据，看起来非常类似于Day和ManualOrder订购的...

ID   Day  ManualOrder  Lat     Lon    
1    Mon  0            36.55   36.55  
5    Mon  1            55.55   54.44  
3    Mon  2            44.33   44.30  
10   Mon  3            36.55   36.55  
11   Mon  4            36.55   36.55  
6    Mon  5            20.22   22.11  
9    Mon  6            55.55   54.44  
10   Mon  7            88.99   11.22  
77   Sun  0            23.33   11.11  
77   Sun  1            23.33   11.11  

What I'm trying to do is get this data ordered by Day, then ManualOrder...but I'd like a row counter (let's call it MapPinNumber). The catch is that I'd like this row counter to be repeated once it encounters the same Lat/Lon for the same day again. Then it can continue on with the next row counter for the next row if it's a different lat/lon. We MUST maintain Day, ManualOrder ordering in the final result.

我想要做的是按天订购这些数据，然后是ManualOrder ...但我想要一个行计数器（我们称之为MapPinNumber）。问题是，我希望这个行计数器在同一天遇到相同的Lat / Lon时再次重复。然后，如果它是不同的纬度/经度，它可以继续下一行的下一行计数器。我们必须在最终结果中维护Day，ManualOrder。

I'll be plotting these on a map, and this number should represent the pin number I'll be plotting in ManualOrder order. This data represents a driver's route and he may go to the same lat/lon multiple times during the day in his schedule. For example he drives to Walmart, then CVS, then back to Walmart again, then to Walgreens. The MapPinNumber column I need should be 1, 2, 1, 3. Since he goes to Walmart multiple times on Monday but it was the first place he drives too, it's always Pin #1 on the map.

我将在地图上绘制这些数字，这个数字应代表我将以ManualOrder顺序绘制的引脚编号。该数据代表驾驶员的路线，并且他可以在他的日程表中白天多次去同一个纬度/经度。例如，他开车去沃尔玛，然后是CVS，然后又回沃尔玛，然后再去沃尔格林。我需要的MapPinNumber列应该是1,2,1,3。由于他周一多次去沃尔玛，但这也是他开车的第一个地方，它总是在地图上的Pin＃1。

Here's what I need my result to be for the MapPinNumber column I need to calculate. I've tried everything I can think of with ROW_NUMBER and RANK, and going insane! I'm trying to avoid using an ugly CURSOR.

这就是我需要的结果，我需要计算的MapPinNumber列。我已经尝试了ROW_NUMBER和RANK所能想到的一切，并且疯了！我试图避免使用丑陋的CURSOR。

ID   Day  ManualOrder  Lat     Lon     MapPinNumber
1    Mon  0            36.55   36.55   1
5    Mon  1            55.55   54.44   2
3    Mon  2            44.33   44.30   3
10   Mon  3            36.55   36.55   1
11   Mon  4            36.55   36.55   1
6    Mon  5            20.22   22.11   4
9    Mon  6            55.55   54.44   2
10   Mon  7            88.99   11.22   5
77   Sun  0            23.33   11.11   1
77   Sun  1            23.33   11.11   1

4 个解决方案

#1

You can use aggregate function MIN with OVER to create your ranking groups and DENSE_RANK working on top of it like this.

你可以使用聚合函数MIN和OVER来创建你的排名组，DENSE_RANK就像这样在它上面工作。

Brief Explanation

简要说明

1. MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon) gets the minimum ManualOrder for a combination of Day, Lat and Lon.

   MIN（ManualOrder）OVER（PARTITION BY Day，Lat，Lon）获得Day，Lat和Lon组合的最小ManualOrder。

2. DENSE_RANK() just sets this value as incremental values from 1.

   DENSE_RANK（）只是将此值设置为1的增量值。

SQL Fiddle

SQL小提琴

Sample Data

样本数据

CREATE TABLE Tbl ([ID] int, [Day] varchar(3), [ManualOrder] int, [Lat] int, [Lon] int);

INSERT INTO Tbl ([ID], [Day], [ManualOrder], [Lat], [Lon])
VALUES
    (1, 'Mon', 0, 36.55, 36.55),
    (5, 'Mon', 1, 55.55, 54.44),
    (3, 'Mon', 2, 44.33, 44.30),
    (10, 'Mon', 3, 36.55, 36.55),
    (11, 'Mon', 4, 36.55, 36.55),
    (6, 'Mon', 5, 20.22, 22.11),
    (9, 'Mon', 6, 55.55, 54.44),
    (10, 'Mon', 7, 88.99, 11.22),
    (77, 'Sun', 0, 23.33, 11.11),
    (77, 'Sun', 1, 23.33, 11.11);

Query

询问

;WITH CTE AS 
(
SELECT *,GRP = MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon) FROM Tbl
)
SELECT ID,Day,ManualOrder,Lat,Lon,DENSE_RANK()OVER(PARTITION BY Day ORDER BY GRP) AS RN
FROM CTE
ORDER BY Day,ManualOrder

Output

产量

ID  Day ManualOrder Lat Lon RN
1   Mon 0   36.55   36.55   1
5   Mon 1   55.55   54.44   2
3   Mon 2   44.33   44.30   3
10  Mon 3   36.55   36.55   1
11  Mon 4   36.55   36.55   1
6   Mon 5   20.22   22.11   4
9   Mon 6   55.55   54.44   2
10  Mon 7   88.99   11.22   5
77  Sun 0   23.33   11.11   1
77  Sun 1   23.33   11.11   1

#2

Here is my attempt using ROW_NUMBER:

这是我尝试使用ROW_NUMBER：

SQL Fiddle

SQL小提琴

WITH CteRN AS(
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY Day ORDER BY ManualOrder),
        Grp = ROW_NUMBER() OVER(PARTITION BY Day, Lat, Lon ORDER BY ManualOrder)
    FROM tbl
),
CteBase AS(
    SELECT *,
        N = ROW_NUMBER() OVER(PARTITION BY Day ORDER BY ManualOrder)
    FROM CteRN
    WHERE Grp = 1
)
SELECT 
    r.ID, r.Day, r.ManualOrder, r.Lat, r.Lon,
    MapPinNumber = ISNULL(b.N, r.RN)
FROM CteRN r
LEFT JOIN CteBase b
    ON b.Day = r.Day
    AND b.Lat = r.Lat
    AND b.Lon = r.Lon
ORDER BY 
    r.Day, r.ManualOrder

#3

This may not be the most elegant solution, but it works:

这可能不是最优雅的解决方案，但它有效：

Select a.*, b.MapPinOrder from MyTable a
left join
  (
  select distinct Day, Lat, Lon
    , row_number() 
      over (partition by Day order by min(ManualOrder)) as MapPinOrder
  from MyTable
  group by Day, Lat, Lon
  ) b
on a.day = b.day 
  and a.lat = b.lat 
  and a.lon = b.lon  

Calculate the rows separately using the ordering you want, then join them back in to the full table.

使用您想要的顺序分别计算行，然后将它们重新连接到完整表。

SQL Fiddle

SQL小提琴

#4

This would give you the result you need. However the MapPinNumber may not be in the exact same order as shown in your result.

这将为您提供所需的结果。但是，MapPinNumber的顺序可能与结果中显示的顺序不同。

SELECT *, 
    MapPinNumber = DENSE_RANK() OVER (PARTITION BY Day ORDER BY Lat, Lon) 
FROM Table1
ORDER BY Day, ManualOrder