首页 > 试题广场 >

某宝店铺连续2天及以上购物的用户及其对应的天数

[编程题]某宝店铺连续2天及以上购物的用户及其对应的天数
  • 热度指数:29157 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解
11月结束后,小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。
11月份销售数据表sales_tb如下(其中,sales_date表示销售日期,user_id指用户编号,item_id指货号,sales_num表示销售数量,sales_price表示结算金额):
sales_date user_id item_id sales_num sales_price
2021-11-01 1 A001 1 90
2021-11-01
2 A002 2 220
2021-11-01
2 B001 1 120
2021-11-02
3 C001 2 500
2021-11-02
4 B001 1 120
2021-11-03
5 C001 1 240
2021-11-03
6 C002 1 270
2021-11-04
7 A003 1 180
2021-11-04
8 B002 1 140
2021-11-04
9 B001 1 125
2021-11-05
10 B003 1 120
2021-11-05
10 B004 1 150
2021-11-05
10 A003 1 180
2021-11-06
11 B003 1 120
2021-11-06
10 B004 1 150
请你统计连续2天及以上在该店铺购物的用户及其对应的次数(若有多个用户,按user_id升序排序),以上例子的输出结果如下:
user_id days_count
10 2

示例1

输入

drop table if exists sales_tb;
CREATE TABLE sales_tb(
sales_date date NOT NULL,
user_id int(10) NOT NULL,
item_id char(10) NOT NULL,
sales_num int(10) NOT NULL,
sales_price int(10) NOT NULL
);

INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001',  1, 90);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002',  2, 220);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001',  2, 500);
INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001',  1, 240);
INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002',  1, 270);
INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003',  1, 180);
INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002',  1, 140);
INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001',  1, 125);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004',  1, 150);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003',  1, 180);
INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004',  1, 150);

输出

10|2
#第1步:sales_date日期去重,因为一个用户在一天内可能购买多次。
#第2步:row_number窗口函数对日期排序,新建一列我们命名为rk。
#第3步:date_sub函数,确定日期分组;
因为rk记录一个用户登陆的天数,rk=1、2、3 说明一个用户连续登录了1、2、3天
我们用date_sub把每个用户的当前的sales_date减去rk,得出每位用户第一天购买的日期,就是日期分组。
比如 2021-1-1减去1 得2020-12-31 
    2021-1-2减去2 得2020-12-31
    2021-1-3减去3 得2020-12-31,
2021-12-31就是一个日期分组,
然后我们group by 每个user_id,日期分组,得出count(*)的数量就是连续登录的天数。

以下是代码:

select user_id,cnt as days_count
from
(select user_id,days_group,count(*)as cnt
from 
(
select *,date_sub(sales_date,interval rk DAY)as days_group 
from 
(select *,
        ROW_NUMBER()over(partition by user_id order by sales_date)as rk
from 
(select 
         distinct sales_date,user_id  from sales_tb
    )a
  )b
)c
group by user_id,days_group
)d
where cnt>=2
order by user_id

发表于 2021-12-08 17:00:37 回复(5)
连续时间问题解决思路:
step1:添加新列rn
step2:做差date_sub(datetime interval rn day)
step3:分组统计

select                                            # step3:分组统计
    t2.user_id,
    count(diff_date) as days_count
from
(
select                                            # step2:做差
    t1.user_id,
    t1.sales_date,
    date_sub(t1.sales_date,interval rn day) as diff_date
from
    (select user_id,sales_date,
        ROW_NUMBER() over(partition by user_id order by sales_date) as rn
    from sales_tb) t1                             # step1:添加新列rn
) t2
group by t2.user_id,diff_date
having days_count>=2;
发表于 2021-12-04 16:32:41 回复(1)
select user_id,max(date_rank)
from (select * ,
 dense_rank() over(partition by user_id order by sales_date)date_rank
from sales_tb)new_table
where date_rank>1
group by user_id
order by user_id;
发表于 2021-12-18 22:19:16 回复(5)
牛客后台可以通过诶,有点神奇
select user_id,count(distinct sales_date) as days_count
from sales_tb
group by user_id
having days_count>=2
order by user_id;

发表于 2022-05-09 16:17:35 回复(3)
select user_id,count(distinct sales_date)
from
(select sales_date,
user_id,
lead(sales_date) over (partition by user_id order by sales_date) as sales_date_2
from sales_tb
group by sales_date,user_id) a
group by user_id
having min(datediff(sales_date_2,sales_date))=1
发表于 2022-12-07 18:34:21 回复(0)
关于连续时间类问题解决方法总结
发表于 2022-05-01 11:25:11 回复(0)
首先呢,我认为需要对user_id,sales_date进行去重处理(存在一个用户一天购买了两次物品这种情况)。然后利用
sales_date-row_number() over (partition by user_id order by sales_date asc)
求取差值,若是是连续登录的则差值是一样的。
最后对用户号,差值进行聚合,既可以求出哪些用户的连续登陆天数大于等于2了。
此处我还有一个疑问,比如说某一用户11.1,11.2来购物了,11.3没有来购物,11.4和11.5又来购物了,那么用上述方法进行聚合的时候,一个用户号就会出现两条符合去、题意的记录,因为不涉及聚合的那个差值,所以亮条记录的结果是一模一样的,那就是需要把所有的结果都输出来吗?求大佬指教~
献上完整的程序:
select user_id,count(cnt) days_count from
(select *,
sales_date-row_number() over (partition by user_id order by sales_date asc) cnt from
(select distinct user_id,sales_date from sales_tb
where date_format(sales_date,'%Y-%m')='2021-11') as a) b
group by user_id,cnt
having days_count >= 2
order by user_id


发表于 2021-12-08 08:05:27 回复(3)
select a.user_id as user_id,count(distinct b.sales_date)+1 as days_count 
from sales_tb as a left join sales_tb as b on a.user_id = b.user_id
where TIMESTAMPDIFF (day, a.sales_date,b.sales_date) >=1 group by a.user_id order by a.user_id
发表于 2021-12-07 23:20:33 回复(0)
提供一个思路:
1、生成一个表,按照user_id分隔,日期升序排列,求出下一行日期与本行日期的时间差

2、筛选出时间差为1的行并求和,days_count=求和数+1
select user_id , sum(aa)+1
from 
(select user_id
, lead(sales_date ,1 ) over(partition by user_id order by sales_date asc ) -sales_date "aa"
from sales_tb) s
where aa = 1
group by user_id
发表于 2022-06-22 23:42:37 回复(2)
select 
    user_id,count(cnt)
from
    (select  
        distinct sales_date,user_id
        ,(datediff(now(),sales_date)-dense_rank()over(partition by user_id order by sales_date desc)) as cnt
    from sales_tb
    ) t 
group by user_id,cnt having count(cnt)>=2
order by user_id
对于求解连续天数,首先求解各个日期距离某个固定日期的天数间隔,再对每个用户进行分组后按日期降序排列,将间隔与排列名次相减,若是连续天数则差值相同。因此,统计相同的差值个数即为连续的天数。
发表于 2022-04-09 20:57:18 回复(0)
select m.user_id,count(distinct c.sales_date) days_count
from 
(
select a.sales_date,user_id
from sales_tb a
join (select date_sub(sales_date,interval 1 day) sales_date,user_id from sales_tb) b using(user_id)
where a.sales_date=b.sales_date
) m
join sales_tb c using(user_id)
group by user_id
order by user_id;
发表于 2022-03-13 15:05:00 回复(0)
select distinct s.user_id, count(distinct s.sales_date)
from sales_tb s join
(
select user_id, sales_date from sales_tb where (user_id, sales_date+1) in
(select user_id, sales_date from sales_tb)
)t
on s.user_id = t.user_id
group by s.user_id
order by s.user_id
编辑于 2024-02-19 16:01:54 回复(0)
select user_id,count(user_id) days_count
from 
(select *,sales_date-row_number() over(partition by user_id order by sales_date) sss /*连续登录天数通用*/
from 
(select distinct user_id,sales_date
from sales_tb) cb) cb2
group by user_id,sss
having days_count >= 2
order by user_id
编辑于 2023-12-28 18:49:29 回复(0)
with t1 as (
    select  distinct user_id,sales_date
    from sales_tb
)
,
t2 as (
    select  user_id,
            row_number() over (partition by user_id order by sales_date) rk 
    from t1
)
select user_id,max(rk)
from t2 
where rk>=2
group by user_id

发表于 2023-10-09 18:47:25 回复(0)
# t1:使用lead()窗口函数获取按相同用户id分区且按时间排序向后数1行的日期(记作dt_lead)
with t1 as (
select distinct user_id,
       date(sales_date) as dt,
       lead(sales_date, 1) over (partition by user_id order by sales_date) as dt_lead,
       datediff(lead(sales_date, 1) over (partition by user_id order by sales_date), date(sales_date)) as dt_diff
from sales_tb
order by 1, 2)

# 当dt - dt_lead >= 1 即连续两天及以上
select user_id,
       count(distinct date(sales_date)) as days_count
from sales_tb
where user_id in (select user_id from t1 where dt_diff >= 1)
group by 1
order by 1

发表于 2023-08-15 17:39:48 回复(0)
-- step6:得到连续天最长的一组的用户编号和连续天数,最后根据用户编号升序排序
select user_id,counts as days_count
from (
	-- step5:考虑到可能有多组连续天,找到连续天最长的一组,用row_number来排序
	select *,row_number()over(partition by user_id order by counts desc) rk
	from (
		-- step4:根据差值日期和用户编号分组,计算每组的天数,天数>= 2的,则为连续2天及2天以上的
		select date_diff,user_id,count(distinct sales_date) counts
		from (
			-- step3:用销售日期减去辅助列,得到差值日期,(思路:如果差值日期相同,则代表相同的几个差值日期为连续天)
			select date_sub(sales_date,interval rk_1 day) as date_diff,user_id,sales_date
			from (
				-- step2:用row_number,添加辅助列
				select *,row_number()over(partition by user_id order by sales_date) rk_1
				from (
					-- step1:去重 用户编号 和 销售日期
					select distinct sales_date,user_id
					from sales_tb
				) a
			) a
		) a
		group by date_diff,user_id
		having count(distinct sales_date) >= 2
	) a
) a
where rk = 1
order by user_id

发表于 2023-05-31 10:16:37 回复(0)
大家看我这个有问题吗??能通过
with a as(
    select
    user_id,
    sales_date,
    lead(sales_date,1) over(partition by user_id order by sales_date) next_date
    from sales_tb
)

select
user_id,
sum(timestampdiff(day,sales_date,next_date))+1 days_count
from a
group by user_id
    having days_count>=2
order by user_id


发表于 2023-04-04 17:52:36 回复(0)
思路:
(1)假设(user_id,sales_date)为
10 11-01
10 11-02
10 11-03
10 11-05
(2)增加一列 row_number over(partition by user_id order by sales_date)为rk
10 11-01 1
10 11-02 2
10 11-03 3
10 11-05 4
(3)增加一列 sales_date-rk 为sub_date
10 11-01 1 10-31
10 11-02 2 10-31
10 11-03 3 10-31
10 11-05 4 11-01
(4)数sub_date的个数cnt
10 10-31 3
10 11-01 1
(5)输出max(cnt)>=2的user_id以及max(cnt)
10 3


select
    user_id,
    max(cnt) as days_count
from
(
    select
        user_id,
        sub_date,
        count(1) as cnt
    from
    (
        select
            user_id,
            subdate(sales_date,rk) as sub_date 
        from
        (
            select
                user_id,
                sales_date,
                row_number() over(partition by user_id order by sales_date asc) as rk
            from
            (
                select
                    distinct user_id,
                    sales_date
                from sales_tb
            ) st
        ) tmp
    ) tmp1
    group by user_id,
        sub_date
) tmp2
group by user_id
having max(cnt) >= 2
order by user_id asc



发表于 2023-03-29 13:51:54 回复(0)
就是连续天数问题,板子题
# 就是连续天数问题,和SQL194
# 某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级 思路是一样的


# 代码由chatgpt给出
# Keep in mind that MySQL user variables are deprecated since MySQL 8.0.13, so using window functions might be a better approach for future-proofing your query.

WITH sales_dates AS (
    SELECT
        sales_date,
        user_id,
        IF(sales_date = @prev_date + INTERVAL 1 DAY AND user_id = @prev_user_id, @consec_days := @consec_days + 1, @consec_days := 1) AS consec_days,
        @prev_date := sales_date,
        @prev_user_id := user_id
    FROM
        (SELECT @prev_date := NULL, @prev_user_id := NULL, @consec_days := 1) vars,
        (SELECT sales_date, user_id FROM sales_tb ORDER BY user_id, sales_date) ordered_dates
)


SELECT
    user_id,
    cast(MAX(consec_days) as unsigned)  AS days_count
FROM
    sales_dates
GROUP BY
    user_id
HAVING
    max_consec_days >= 2




发表于 2023-03-23 11:08:15 回复(0)
select a.user_id,
max(date(b.sales_date))-min(date(a.sales_date))+1 days_count
from sales_tb a, sales_tb b
where a.user_id = b.user_id
and timestampdiff(day, a.sales_date, b.sales_date) >= 1
group by user_id
发表于 2023-02-26 15:04:03 回复(0)
  • 二维码

    扫描二维码,关注牛客网

  • 二维码

    下载牛客APP,随时随地刷题