SQL Server或Pandas按分区对窗口函数进行排序/编号

Question

我有一个表，我想在一个类别中对PartNumber组进行排名，而保持LineItem顺序.

这是起始表——>

行项目零件号富士富士奇基塔奇基塔Del MontoDel Monto

听起来您需要的是按每Category, PartNumber的最小LineItem来订购DENSE_RANK

SELECT 
Category,
LineItem,
PartNumber,
DENSE_RANK() OVER (PARTITION BY Category ORDER BY MinLineItem)
FROM (
SELECT *,
MinLineItem = MIN(LineItem) OVER (PARTITION BY Category, PartNumber)
FROM [TABLE]
) t

,db&lt的在小提琴

在pandas中使用groupby transform+factorize，正如@Scott Boston对在多个pandas列上使用groupby后计数值的答案所推荐的:

df['Rank Order'] = (
df.groupby('Category')['Part Number']
.transform(lambda s: s.factorize()[0] + 1)
)

*factorize将每组零件号转换为枚举类型，确保按外观按数字升序显示。这与groupby rank相反，它将使用排序方法。

df:

Category  Line Item   Part Number  Rank Order
0    Apple          1  Granny Smith           1
1    Apple          2  Granny Smith           1
2    Apple          3          Fuji           2
3    Apple          4          Fuji           2
4   Banana          1      Chiquita           1
5   Banana          2      Chiquita           1
6   Banana          3          Dole           2
7   Banana          4          Dole           2
8   Banana          5     Del Monto           3
9   Banana          6     Del Monto           3

设置和导入:

import pandas as pd
df = pd.DataFrame({
'Category': ['Apple', 'Apple', 'Apple', 'Apple', 'Banana', 'Banana',
'Banana', 'Banana', 'Banana', 'Banana'],
'Line Item': [1, 2, 3, 4, 1, 2, 3, 4, 5, 6],
'Part Number': ['Granny Smith', 'Granny Smith', 'Fuji', 'Fuji', 'Chiquita',
'Chiquita', 'Dole', 'Dole', 'Del Monto', 'Del Monto']
})