获取用户第二个月交易的所有交易详细信息

发布于 2025-01-11 18:27:57 字数 547 浏览 0 评论 0原文

尝试获取所有客户的第二个交易月份详细信息

Date          User_id      amount
2021-11-01      1           100
2021-11-21      1           200
2021-12-20      2           110
2022-01-20      2           200
2022-02-04      1            50
2022-02-21      1           100
2022-03-22      2           200

对于每个客户，获取其第二笔交易月份的所有记录（特定用户在一个月和一天内可以有多个交易）

预期输出

Date       User_id      amount
2022-02-04      1          50
2022-02-21      1          100
2022-01-20      2          200

原文

Trying to get the 2nd transaction month details for all the customers

Date          User_id      amount
2021-11-01      1           100
2021-11-21      1           200
2021-12-20      2           110
2022-01-20      2           200
2022-02-04      1            50
2022-02-21      1           100
2022-03-22      2           200

For every customer get all the records in the month of their 2nd transaction (There can be multiple transaction in a month and a day by a particular user)

Expected Output

Date       User_id      amount
2022-02-04      1          50
2022-02-21      1          100
2022-01-20      2          200

分享到QQ

分享到微博

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

发布评论

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

不必了 2025-01-18 18:27:57

您可以使用dense_rank：

select Date, User_id, amount from
(select *, dense_rank() over(partition by User_id order by year(Date), month(date)) r
from table_name) t
where r = 2;

Fiddle

You can use dense_rank:

select Date, User_id, amount from
(select *, dense_rank() over(partition by User_id order by year(Date), month(date)) r
from table_name) t
where r = 2;

Fiddle

回复收藏 0 原文

蓝海 2025-01-18 18:27:57

如果dense_rank是一个选项，您可以：

with cte1 as (
    select *, extract(year_month from date) as yyyymm
    from t
), cte2 as (
    select *, dense_rank() over (partition by user_id order by yyyymm) as dr
    from cte1
)
select *
from cte2
where dr = 2

请注意，可以使用一个cte编写以上内容。

If dense_rank is an option you can:

with cte1 as (
    select *, extract(year_month from date) as yyyymm
    from t
), cte2 as (
    select *, dense_rank() over (partition by user_id order by yyyymm) as dr
    from cte1
)
select *
from cte2
where dr = 2

Note that it is possible to write the above using one cte.

回复收藏 0 原文

~没有更多了~