如何从窗口顺序的另一列计算RANK?
问题描述:
有没有一种方法可以从另一列中计算“排序”,而不是在窗口排序中指定的那一列?如何从窗口顺序的另一列计算RANK?
为了说明更清楚我想要做的,我会用下面的例子:
数据例如:
| Date | Amount | Product_ID |
|------------------|--------------|------------------|
| 2016-01-10 | 7000 | A |
| 2016-02-01 | 1000 | A |
| 2016-01-08 | 10000 | B |
| 2016-02-10 | 2000 | B |
| 2016-03-05 | 3000 | A |
| 2016-04-01 | 10000 | A |
| 2016-03-20 | 4000 | B |
| 2016-05-01 | 8500 | B |
| 2016-05-15 | 2000 | A |
|------------------|--------------|------------------|
问题
所以在这个例子中,我想,首先用“Product_ID”对窗口进行分区,然后按“Date”排序,但计算的排名应该在“Amount”列而不是有序列“Date”:
| Date | Amount | Product_ID | Rank |
|------------------|--------------|------------------|------------|
| 2016-01-10 | 7000 | A | 1 |
| 2016-02-01 | 1000 | A | 1 |
| 2016-03-05 | 3000 | A | 2 |
| 2016-04-01 | 10000 | A | 4 |
| 2016-05-15 | 2000 | A | 2 |
| 2016-01-08 | 10000 | B | 1 |
| 2016-02-10 | 2000 | B | 1 |
| 2016-03-20 | 4000 | B | 2 |
| 2016-05-01 | 8500 | B | 3 |
|------------------|--------------|------------------|------------|
我想通过“日期”订购窗口,以便我只计算过去日期的“金额排名”。
说明
为了更清楚地解释它,在隔板上的product_id答:
-
第一个窗口(在按日期排序):
| Date | Amount | Product_ID | Rank | |------------------|--------------|------------------|------------| | 2016-01-10 | 7000 | A | 1 |
-
二窗口:在这里,第二行的数量为1000,低于7000(按d排序的窗口的第一行的数量酯),“秩”应为1。
| Date | Amount | Product_ID | Rank | |------------------|--------------|------------------|------------| | 2016-01-10 | 7000 | A | 1 | | 2016-02-01 | 1000 | A | 1 |
-
第三窗口:随着与上述相同的逻辑,我们得到“等级” 2作为3000被排名第二,在子组[7000,1000,3000]
| Date | Amount | Product_ID | Rank | |------------------|--------------|------------------|------------| | 2016-01-10 | 7000 | A | 1 | | 2016-02-01 | 1000 | A | 1 | | 2016-03-05 | 3000 | A | 2 |
-
第四窗口:相同的逻辑,上述
| Date | Amount | Product_ID | Rank | |------------------|--------------|------------------|------------| | 2016-01-10 | 7000 | A | 1 | | 2016-02-01 | 1000 | A | 1 | | 2016-03-05 | 3000 | A | 2 | | 2016-05-15 | 2000 | A | 2 |
等等。
我已经试过
我试过下面的代码来获得我想要的东西,即由PRODUCT_ID分区,按日期窗口,并采取等级:
SELECT
Date,
Amount,
Product_ID,
RANK() OVER(PARTITION BY Product_ID ORDER BY Date) AS Rank
FROM Data
此代码给我下面的结果:
| Date | Amount | Product_ID | Rank |
|------------------|--------------|------------------|------------|
| 2016-01-10 | 7000 | A | 1 |
| 2016-02-01 | 1000 | A | 2 |
| 2016-03-05 | 3000 | A | 3 |
| 2016-04-01 | 10000 | A | 4 |
| 2016-05-15 | 2000 | A | 5 |
| 2016-01-08 | 10000 | B | 1 |
| 2016-02-10 | 2000 | B | 2 |
| 2016-03-20 | 4000 | B | 3 |
| 2016-05-01 | 8500 | B | 4 |
|------------------|--------------|------------------|------------|
我已经试过金额的顺序是一样的:
SELECT
Date,
Amount,
Product_ID,
RANK() OVER(PARTITION BY Product_ID ORDER BY Amount) AS Rank
FROM Data
这个新的代码给我的结果如下:
| Date | Amount | Product_ID | Rank |
|------------------|--------------|------------------|------------|
| 2016-02-01 | 1000 | A | 1 |
| 2016-05-15 | 2000 | A | 2 |
| 2016-03-05 | 3000 | A | 3 |
| 2016-01-10 | 7000 | A | 4 |
| 2016-04-01 | 10000 | A | 5 |
| 2016-02-10 | 2000 | B | 1 |
| 2016-03-20 | 4000 | B | 2 |
| 2016-05-01 | 8500 | B | 3 |
| 2016-01-08 | 10000 | B | 4 |
|------------------|--------------|------------------|------------|
诺塔贝奈斯
N.B.1:我一直试图做的星火SQL所以SQL是基本的。任何与Scala或pySpark的回答也可以接受。
N.B.2:这是我在Stack Overflow上的第一篇文章
非常感谢你对你的答案和你的理解。
答
非常有趣的问题。您似乎希望按日期累计排名。
我不能轻易想到一种使用窗口函数的方法。这里是带有明确JOIN
和GROUP BY
的方法:
SELECT d.Product_Id, d.Date, d.Amount,
SUM(CASE WHEN d2.Amount < d.Amount THEN 1 ELSE 0 END) + 1 as rank
FROM Data d JOIN
Data d2
ON d2.Product_Id = d.Product_Id AND
d2.Date <= d.Date
GROUP BY d.Product_Id, d.Date, d.Amount;
当然,性能还不如一个窗口函数的方法是。
在某些数据库中工作的一种方法是将数量累积到字符串或数组中,然后使用字符串/数组操作来计算排名。然而,即使这可能会很棘手。
非常感谢您的回答,@Gordon Linoff! 它确实完成了这项工作。 在我全部约一千五百万行的数据上,它有点贵,但通过将数据分割成更小的块,我希望能够实现它。 –