英文:
How can I efficiently retrieve details of prior transactions for each customer?
问题
我有一张包含CustomerID和TransactionDate的交易数据表，我想创建新的列来告诉我每个客户的每笔交易的详细信息（日期、金额等）。
这是我的原始表的快照：
| CustomerID | TransactionDate | Amount |
|---|---|---|
| 18563 | 2014-12-01 | 560 |
| 18563 | 2019-08-22 | 24 |
| 18563 | 2022-01-24 | 979 |
| 32523 | 2021-11-03 | 1024 |
以下是我想要的样子：
| CustomerID | TransactionDate | LastTransDate | Amount | LastTransAmount |
|---|---|---|---|---|
| 18563 | 2014-12-01 | NULL | 560 | NULL |
| 18563 | 2019-08-22 | 2014-12-01 | 24 | 560 |
| 18563 | 2022-01-24 | 2019-08-22 | 979 | 24 |
| 32523 | 2021-11-03 | NULL | 1024 | NULL |
我有一个使用OUTER APPLY的查询可以实现，但它太慢了，我不知道如何修复它。
以下是我的查询：
SELECT TOP 1000
t.CustomerID,
t.TransactionDate,
t2.LastTransDate,
t.Amount,
t2.Amount LastTransAmount
FROM myTable t
OUTER APPLY (
SELECT TOP 1
CustomerID,
TransactionDate AS LastTransDate,
Amount
FROM myTable
WHERE t.CustomerID = CustomerID
AND t.TransactionDate > TransactionDate
ORDER BY TransactionDate DESC) t2
GROUP BY t.Customer_ID,
t.TransactionDate,
t2.LastTransDate,
t.Amount,
t2.Amount,
ORDER BY t.CustomerID, t.TransactionDate
这可以得到我想要的结果，但速度太慢了。我尝试将其转换为LEFT JOIN，但没有成功。我还怀疑GROUP BY导致了问题，但不知道如何调整。任何建议将不胜感激！
英文:
I have a table of transactional data, including CustomerID & TransactionDate, and I'm looking to create new columns to tell me the details (date, amount, etc.) of the LAST transaction for each transaction for each customer.
Here's a snapshot of my original table:
| CustomerID | TransactionDate | Amount |
|---|---|---|
| 18563 | 2014-12-01 | 560 |
| 18563 | 2019-08-22 | 24 |
| 18563 | 2022-01-24 | 979 |
| 32523 | 2021-11-03 | 1024 |
And here's an idea of what I'm looking for:
| CustomerID | TransactionDate | LastTransDate | Amount | LastTransAmount |
|---|---|---|---|---|
| 18563 | 2014-12-01 | NULL | 560 | NULL |
| 18563 | 2019-08-22 | 2014-12-01 | 24 | 560 |
| 18563 | 2022-01-24 | 2019-08-22 | 979 | 24 |
| 32523 | 2021-11-03 | NULL | 1024 | NULL |
I have a query using an OUTER APPLY that works, but its way too slow, and I'm not sure how to fix it.
Here's my query:
SELECT TOP 1000
t.CustomerID,
t.TransactionDate,
t2.LastTransDate,
t.Amount,
t2.Amount LastTransAmount
FROM myTable t
OUTER APPLY (
SELECT TOP 1
CustomerID,
TransactionDate AS LastTransDate,
Amount
FROM myTable
WHERE t.CustomerID = CustomerID
AND t.TransactionDate > TransactionDate
ORDER BY TransactionDate DESC) t2
GROUP BY t.Customer_ID,
t.TransactionDate,
t2.LastTransDate,
t.Amount,
t2.Amount,
ORDER BY t.CustomerID, t.TransactionDate
This gives me what I want, but it's far too slow. I've tried converting into a LEFT JOIN, but to no avail. I also suspected the GROUP BY is causing the issue, but I don't know how to adjust for that. Any tips would be much appreciated!
答案1
得分: 2
你应该使用LAG窗口函数来获取先前的数值。
SELECT
t.CustomerID,
t.TransactionDate,
LAG(t.TransactionDate) OVER(PARTITION BY t.CustomerID ORDER BY t.TransactionDate) LastTransDate,
t.Amount,
LAG(t.Amount) OVER(PARTITION BY t.CustomerID ORDER BY t.TransactionDate) LastTransAmount
FROM myTable t
英文:
You should use the LAG window function to get the prior values
SELECT
t.CustomerID,
t.TransactionDate,
LAG(t.TransactionDate) OVER(PARTITION BY t.CustomerID ORDER BY t.TransactionDate) LastTransDate,
t.Amount,
LAG(t.Amount) OVER(PARTITION BY t.CustomerID ORDER BY t.TransactionDate) LastTransAmount
FROM myTable t
答案2
得分: 0
SELECT
t.CustomerID,
t.TransactionDate,
lag(t.TransactionDate) OVER(PARTITION BY t.CustomerID ORDER BY t.CustomerID, t.TransactionDate) LastTransDate,
t.Amount,
lag(t.Amount) OVER(PARTITION BY t.CustomerID ORDER BY t.CustomerID, t.TransactionDate) LastTransAmount
FROM myTable t
ORDER BY t.CustomerID, t.TransactionDate
英文:
SELECT
t.CustomerID,
t.TransactionDate,
lag(t.TransactionDate) OVER(PARTITION BY t.CustomerID ORDER BY t.CustomerID, t.TransactionDate) LastTransDate,
t.Amount,
lag(t.Amount) OVER(PARTITION BY t.CustomerID ORDER BY t.CustomerID, t.TransactionDate) LastTransAmount
FROM myTable t
order by t.CustomerID,t.TransactionDate
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论