我正在try 对以下Customer_Item视图进行一些处理
CustomerId | Amount | ItemId | ItemPrice | IsActiveStore | Priority |
---|---|---|---|---|---|
1 | 1000 | 1 | 100 | 1 | 1 |
1 | 1000 | 2 | 200 | 1 | 2 |
1 | 1000 | 3 | 300 | 0 | 3 |
2 | 5000 | 4 | 1000 | 1 | 1 |
2 | 5000 | 5 | 4000 | 1 | 2 |
2 | 5000 | 6 | 500 | 1 | 3 |
For each customer, I want to subtract the prices of the related items, that are ordered by priority, from his amount, and update the status for each item accordingly
In case IsActiveStore is false or the amount reaches zero, then skip the item
备注:
- 优先级不是连续的,而是按顺序排列的,项目ID 1、2和3的优先级可以分别为5、7和9
- 如果按优先级排序的第一个项目的价格超过客户数量(例如5k),则将跳过该项目,并继续处理其余优先级
因此,更新后的Customer表应该如下所示
CustomerId | Amount |
---|---|
1 | 700 |
2 | 0 |
更新后的Items表如下
ItemId | ItemStatus |
---|---|
1 | subtracted |
2 | subtracted |
3 | skipped |
4 | subtracted |
5 | subtracted |
6 | skipped |
现在,我用一个游标在客户上循环,对于每个客户,我使用另一个游标在相关项目上循环,这很有效,但对于 Big Data 来说,它需要很长时间.
Is there any faster alternative to do the same processing efficiently?
Thanks in advance.