# Write your MySQL query statement below
2752. 在连续天数上进行了最多交易次数的顾客
表： Transactions
+------------------+------+ | 列名 | 类型 | +------------------+------+ | transaction_id | int | | customer_id | int | | transaction_date | date | | amount | int | +------------------+------+ transaction_id 是这个表的具有唯一值的列。 每行包含有关交易的信息，包括唯一的（customer_id，transaction_date）以及相应的 customer_id 和 amount。
编写一个解决方案，找到连续天数上进行了最多交易的所有 customer_id 。
返回所有具有最大连续交易次数的 customer_id 。结果表按 customer_id 的 升序 排序。
结果的格式如下所示。
示例 1：
输入： Transactions 表: +----------------+-------------+------------------+--------+ | transaction_id | customer_id | transaction_date | amount | +----------------+-------------+------------------+--------+ | 1 | 101 | 2023-05-01 | 100 | | 2 | 101 | 2023-05-02 | 150 | | 3 | 101 | 2023-05-03 | 200 | | 4 | 102 | 2023-05-01 | 50 | | 5 | 102 | 2023-05-03 | 100 | | 6 | 102 | 2023-05-04 | 200 | | 7 | 105 | 2023-05-01 | 100 | | 8 | 105 | 2023-05-02 | 150 | | 9 | 105 | 2023-05-03 | 200 | +----------------+-------------+------------------+--------+ 输出： +-------------+ | customer_id | +-------------+ | 101 | | 105 | +-------------+ 解释： - customer_id 为 101 共有 3 次交易，且全部是连续的。 - customer_id 为 102 共有 3 次交易，但只有其中 2 次是连续的。 - customer_id 为 105 共有 3 次交易，且全部是连续的。 总的来说，最大连续交易次数为 3，由 customer_id 为 101 和 105 的完成。customer_id 按升序排序。
原站题解
mysql 解法, 执行用时: 645 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:31:47
# Write your MySQL query statement below
with base as (
# 先给数据按照customer_id，date排序
Select customer_id
, transaction_date
, amount
, transaction_id
From Transactions
Order by customer_id,transaction_date
),
transaction_cons as(
# 使用负值对已经连续交易的天数大标签
Select customer_id
, cons_days
, rank() over(partition by customer_id order by cons_days desc) as rnk
From (
Select customer_id
, transaction_date
, @cons_days:= if(@pre_customer = customer_id and datediff(transaction_date,@pre_date )=1, @cons_days+1 ,1) as cons_days
, @pre_customer:= customer_id as pre_customer
, @pre_date:= transaction_date as pre_date
From base ,(select @pre_customer:= null,@pre_date:= null,@cons_days:= null ) init
) ta
),
max_tans as(
# 取最大连续交易天数
Select max(cons_days) as max_days from transaction_cons
)
# 筛选出对应的数据进行排序
Select customer_id
From transaction_cons
Where cons_days = (select max_days from max_tans)
order by customer_id
mysql 解法, 执行用时: 409 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 17:31:26
# Write your MySQL query statement below
with c as(
select customer_id,transaction_date, first_value(transaction_date) over(partition by customer_id order by transaction_date) as ftd from Transactions
), d as(
select customer_id,datediff(transaction_date,ftd) as df,row_number() over(partition by customer_id order by transaction_date) as rn from c
), e as(
select customer_id, cast(df as signed)-cast(rn as signed) as ff from d
), f as(
select customer_id,count(ff) as ct from e group by customer_id,ff
), g as(
select customer_id from f where ct = (select max(ct) from f)
)
select * from g