在SQL Server中按段中的客户交易排名

发布于 2025-02-13 04:28:49 字数 2759 浏览 0 评论 0原文

我有以下表，其中包含客户的交易详细信息。

tranactaction Date	culting ind
1/27/2022	1
1/29/2022	1
2/27/2022	1
3/27/2022	1
3/29/2022	1
3/31/2022	1
4/2/2022	1
4/4/4/20222222	1
4/6/2022	1

在该表中连续交易发生在每两天被视为一个细分市场中。例如， 1月27日至1月29日之间的交易被认为是段1＆amp; 3月29日和4月6日之间的交易被认为是段2。我需要按日期顺序对每段的交易进行排名。如果默认情况下，交易不属于任何细分市场，则排名为1。预期输出为低于。

细分等级	tranactaction date	cusitiverid
1	1/27/2022	1
2	1/29/2022	1
1	2/27/2022	1
1 1	3/27/2022	1
2	3/29/2022	1
3	3/31/2022	1
4 4 4/ 4	/4/ 2/2022	1
5	4/4/2022	1
6	4/6/2022	1

有人可以指导如何在T-SQL中实现这一目标吗？

原文

I have below table which has customer's transaction details.

Tranactaction date	CustomerID
1/27/2022	1
1/29/2022	1
2/27/2022	1
3/27/2022	1
3/29/2022	1
3/31/2022	1
4/2/2022	1
4/4/2022	1
4/6/2022	1

In this table consecutive transactions occurred in every two days considered as a segment.
For example, Transactions between Jan 27th and Jan 29th considered as segment 1 & Transactions between Mar 29th and Apr 6th considered as Segment 2. I need to rank the transactions per segment with date order. If a transaction not fall under any segment by default the rank is 1. Expected output is below.

Segment Rank	Tranactaction date	CustomerID
1	1/27/2022	1
2	1/29/2022	1
1	2/27/2022	1
1	3/27/2022	1
2	3/29/2022	1
3	3/31/2022	1
4	4/2/2022	1
5	4/4/2022	1
6	4/6/2022	1

Can somebody guide how to achieve this in T-sql?

分享到QQ

分享到微博

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

发布评论

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

北城挽邺 2025-02-20 04:28:49

使用（）检查transdate的更改，即在2天之内并组合在一起（作为一个细分）。 After that use row_number() to generate the required sequence

with 
cte as
(
    select *,
           g = case when datediff(day,
                                  lag(t.TransDate) over (order by t.TransDate),
                                  t.TransDate
                                  ) <= 2
                    then 0
                    else 1
                    end
    from   tbl t
),
cte2 as
(
    select *, grp = sum(g) over (order by TransDate)
    from cte
)
select *, row_number() over (partition by grp order by TransDate)
from   cte2

Using lag() to check for change in TransDate that is within 2 days and groups together (as a segment). After that use row_number() to generate the required sequence

with 
cte as
(
    select *,
           g = case when datediff(day,
                                  lag(t.TransDate) over (order by t.TransDate),
                                  t.TransDate
                                  ) <= 2
                    then 0
                    else 1
                    end
    from   tbl t
),
cte2 as
(
    select *, grp = sum(g) over (order by TransDate)
    from cte
)
select *, row_number() over (partition by grp order by TransDate)
from   cte2

db<>fiddle demo

回复收藏 0 原文

~没有更多了~