减去上一行id与上一行相同的数据

发布于 2024-12-17 12:09:59 字数 574 浏览 3 评论 0原文

我整个下午都在努力尝试实现这一目标，但没有成功。

我有一个数据库，其中包含客户信息以及他们从商店购买产品的日期。它按批次 ID 分组，我已将其转换为日期格式。

所以在我的表格中，我现在有：

CustomerID|Date
1234      |2011-10-18
1234      |2011-10-22
1235      |2011-11-16
1235      |2011-11-17

我想要实现的是查看最近一次购买和上次购买之间的天数等等。

例如：

CustomerID|Date       |Outcome
1234      |2011-10-18 |
1234      |2011-10-22 | 4
1235      |2011-11-16 |
1235      |2011-11-17 | 1

我尝试将表连接到自身，但我遇到的问题是我最终以相同的格式连接。然后我尝试用我的 join 语句返回它所做的地方 <>比赛日期。

希望这是有道理的，任何帮助表示赞赏。我已经在这里搜索了所有相关主题。

原文

I have been trying all afternoon to try and achieve this with no success.

I have a db in with info on customers and the date that they purchase products from the store. It is grouped by a batch ID which I have converted into a date format.

So in my table I now have:

CustomerID|Date
1234      |2011-10-18
1234      |2011-10-22
1235      |2011-11-16
1235      |2011-11-17

What I want to achieve is to see the number of days between the most recent purchase and the last purchase and so on.

For example:

CustomerID|Date       |Outcome
1234      |2011-10-18 |
1234      |2011-10-22 | 4
1235      |2011-11-16 |
1235      |2011-11-17 | 1

I have tried joining the table to itself but the problem I have is that I end up joining in the same format. I then tried with my join statement to return where it did <> match date.

Hope this makes sense, any help appreciated. I have searched all the relevant topics on here.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

悟红尘 2024-12-24 12:10:00

会有多组CustomerID吗？或者只是并且总是组合在一起？

DECLARE @myTable TABLE
(
    CustomerID INT,
    Date DATETIME
)

INSERT INTO @myTable
SELECT 1234, '2011-10-14' UNION ALL
SELECT 1234, '2011-10-18' UNION ALL
SELECT 1234, '2011-10-22' UNION ALL
SELECT 1234, '2011-10-26' UNION ALL
SELECT 1235, '2011-11-16' UNION ALL
SELECT 1235, '2011-11-17' UNION ALL 
SELECT 1235, '2011-11-18' UNION ALL
SELECT 1235, '2011-11-19'

SELECT  CustomerID, 
        MIN(date), 
        MAX(date), 
        DATEDIFF(day,MIN(date),MAX(date)) Outcome
FROM @myTable 
GROUP BY CustomerID

SELECT  a.CustomerID, 
        a.[Date], 
        ISNULL(DATEDIFF(DAY, b.[Date], a.[Date]),0) Outcome
FROM 
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row,
            CustomerID,
            Date
    FROM @myTable 
) A
LEFT JOIN 
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row,
            CustomerID,
            Date
    FROM @myTable 
) B ON a.CustomerID = b.CustomerID AND A.Row = B.Row + 1

Will there be multiple groups of CustomerID? Or only and always grouped together?

DECLARE @myTable TABLE
(
    CustomerID INT,
    Date DATETIME
)

INSERT INTO @myTable
SELECT 1234, '2011-10-14' UNION ALL
SELECT 1234, '2011-10-18' UNION ALL
SELECT 1234, '2011-10-22' UNION ALL
SELECT 1234, '2011-10-26' UNION ALL
SELECT 1235, '2011-11-16' UNION ALL
SELECT 1235, '2011-11-17' UNION ALL 
SELECT 1235, '2011-11-18' UNION ALL
SELECT 1235, '2011-11-19'

SELECT  CustomerID, 
        MIN(date), 
        MAX(date), 
        DATEDIFF(day,MIN(date),MAX(date)) Outcome
FROM @myTable 
GROUP BY CustomerID

SELECT  a.CustomerID, 
        a.[Date], 
        ISNULL(DATEDIFF(DAY, b.[Date], a.[Date]),0) Outcome
FROM 
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row,
            CustomerID,
            Date
    FROM @myTable 
) A
LEFT JOIN 
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row,
            CustomerID,
            Date
    FROM @myTable 
) B ON a.CustomerID = b.CustomerID AND A.Row = B.Row + 1

回复收藏 0 原文

~没有更多了~