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

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

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6408KB, 提交时间: 2021-12-08

select
    user_id,
    days_count
from
    (select 
        user_id,
        sub_date,
        count(*) days_count
    from
        (select 
            *,
            date_sub(sales_date,interval row_number()over(partition by user_id order by sales_date) day) sub_date
        from 
            (select 
                 distinct sales_date,
                 user_id
             from 
                 sales_tb) a ) b
    group by 
        user_id,
        sub_date) c
where 
    days_count >= 2

Mysql 解法, 执行用时: 37ms, 内存消耗: 6372KB, 提交时间: 2021-12-09

select user_id,count(*) as days_count from(
select user_id,date_sub(sales_date,interval row_number()over(partition by user_id order by sales_date) day) as t
    from (select distinct sales_date,user_id from sales_tb) as a) as b
    group by user_id,t
    having count(*)>=2;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6372KB, 提交时间: 2021-12-06

select  c.user_id,c.days_count
from(
select user_id,sales_date-sort as login_time,min(sales_date) as start_shopping,
max(sales_date) as end_shopping,count(*) as days_count
from (
select user_id,sales_date,
row_number() over(partition by user_id order by sales_date) as sort 
from (select distinct sales_date,user_id from sales_tb)a
    )b 
group by user_id
having days_count>=2
    )c
order by c.user_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2021-12-12

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


###

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2021-12-07

select user_id,max(d) D
from
sales_tb a
left join
(SELECT user_id,sales_date sd,(DENSE_RANK() over (partition by user_id order by sales_date)) d
from sales_tb) b
using(user_id)
where DATEDIFF(sd,sales_date)=d-1
group by user_id
having D>=2

上一题

下一题

详情