英文:
Sequence purchases in a table where the sequence is unique to the purchaser
问题
我正在查看如下表格：
Idx | CustID |Item | Date
________________________________
1 | 1 | Black Ink | 2023-01-01
2 | 2 | Red Ink | 2023-01-21
3 | 1 | Black Ink | 2023-01-12
4 | 2 | Red ink | 2023-02-14
我尝试的目标是按客户对购买进行排序，并为每个客户添加一个唯一的顺序，如下所示：
Idx | CustID |Item | Date | Sequence
_________________________________________________
1 | 1 | Black Ink | 2023-01-01 | 1
3 | 1 | Black Ink | 2023-01-12 | 2
2 | 2 | Red Ink | 2023-01-21 | 1
4 | 2 | Red ink | 2023-02-14 | 2
如何添加 'Sequence' 列？我尝试使用带有 ROW_NUMBER() OVER (ORDER BY CustID, Item) 的子查询语句，但它只会像 'Idx' 一样对整个表进行排序。
英文:
I'm looking at a table as follows:
Idx | CustID |Item | Date
________________________________
1 | 1 | Black Ink | 2023-01-01
2 | 2 | Red Ink | 2023-01-21
3 | 1 | Black Ink | 2023-01-12
4 | 2 | Red ink | 2023-02-14
What I'm trying to do is to sequence the purchases by customer with a unique sequence per customer as follows:
Idx | CustID |Item | Date | Sequence
_________________________________________________
1 | 1 | Black Ink | 2023-01-01 | 1
3 | 1 | Black Ink | 2023-01-12 | 2
2 | 2 | Red Ink | 2023-01-21 | 1
4 | 2 | Red ink | 2023-02-14 | 2
How do I add the 'Sequence' column? I've tried using a sub-select statement with ROW_NUMBER() OVER (ORDER BY CustID, Item) but it just sequences the entire table like Idx.
答案1
得分: 1
I've tried using a sub-select statement with ROW_NUMBER() OVER (ORDER BY CustID, Item) but it just sequences the entire table like Idx.
你尝试使用ROW_NUMBER() OVER (ORDER BY CustID, Item)的子查询语句，但它只是像Idx一样对整个表进行排序。
Your row_number() needs a partition by clause, so that each customer gets its own iterator. So something like:
你的row_number()需要一个partition by子句，以便每个客户都有自己的迭代器。所以可以这样写：
select t.*,
row_number() over(partition by custid order by idx) seq
from mytable t
Depending on your actual use case, you might want to consider ordering by date rather than by idx. Also if there may be ties, then rank() is more relevant than row_number().
根据你的实际用例，你可能想考虑按照date而不是idx进行排序。如果可能存在并列情况，那么rank()比row_number()更相关。
英文:
> I've tried using a sub-select statement with ROW_NUMBER() OVER (ORDER BY CustID, Item) but it just sequences the entire table like Idx.
Your row_number() needs a partition by clause, so that each customer gets its own iterator. So something like:
select t.*,
row_number() over(partition by custid order by idx) seq
from mytable t
Depending on your actual use case, you might want to consider ordering by date rather than by idx. Also if there may be ties, then rank() is more relevant than row_number().
答案2
得分: 0
需要在你的ROW_NUMBER()中添加PARTITION BY子句，以便为新的CustID重新开始编号。
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY Item) rn
FROM MY_TABLE
英文:
You need to add PARTITION BY clause to your ROW_NUMBER() to start numbering againg for new CustID
SELECT *, ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY Item) rn
FROM MY_TABLE
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论