SQL互粉问题

SQL挑战：计算互粉用户对及每日累计数

原创已于 2023-11-17 11:39:11 修改 · 173 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2023-11-14 21:01:19 首次发布

文章讨论了解决SQL问题，涉及如何计算到某日期的互粉用户对数，以及2023年每天的总互粉用户。给出了三种方法，包括使用CTE（公共表表达式）和递归查询来解决互粉关系的问题。

本以为自己已经会了很多的SQL类型题，但是前两天朋友面试时候问了一个互粉的SQL问题。

原题总共有三题：

请输出：互粉关系的用户有多少对？

求出截止到某一日期的互粉对的数量

求出2023年的截止到每一天的总的互粉用户数量

create table if not exists test1.fans
(
fans_uid varchar(255),
uid varchar(255),
create_date date
);

INSERT INTO test1.fans (fans_uid, uid, create_date)
VALUES (4, 1, '2022-11-09');
INSERT INTO test1.fans (fans_uid, uid, create_date)
VALUES (1, 2, '2023-01-02');
INSERT INTO test1.fans (fans_uid, uid, create_date)
VALUES (1, 3, '2023-01-05');
INSERT INTO test1.fans (fans_uid, uid, create_date)
VALUES (2, 1, '2023-01-09');
INSERT INTO test1.fans (fans_uid, uid, create_date)
VALUES (3, 1, '2023-02-08');
INSERT INTO test1.fans (fans_uid, uid, create_date)
VALUES (1, 4, '2023-11-09');
INSERT INTO test1.fans (fans_uid, uid, create_date)
VALUES (1, 5, '2023-11-09');

第一题：(总共想出来了三种方法)

# 法1
with t1 as(select fans_uid, uid,
       if(uid < fans.fans_uid,concat(uid,fans_uid),concat(fans_uid,uid)) as d
from test1.fans)
,t2 as (select count(d) as cnt
from t1
group by d
having cnt = 2)
select count(cnt) as total
from t2;

# todo 法2
with t1 as(
select fans_uid, uid from fans
union all
select uid ,fans_uid  from fans)
,t2 as (
select fans_uid, uid from fans
union
select uid ,fans_uid  from fans
)
,t3 as(select count(uid) as total from t1)
,t4 as(select count(uid) as cnt from t2)
select round((total - cnt)/2,0) as '互关'
from t3,t4
;

# todo 法3(可能是递归写的有问题，一直卡在运行界面)
with recursive t1 as (
    select uid,fans_uid from fans where uid = 1
    union all
    select t1.uid, concat(fans.uid,'->',t1.fans_uid)
    from t1 inner join fans on t1.uid = fans.fans_uid
    )
select *
from t1
;

第三种方法我是用递归求解的，但是电脑性能跟不上，运行了5分钟还是运行不出来

第二题：

with t1 as(select fans_uid, uid,create_date,
       if(uid < fans.fans_uid,concat(uid,fans_uid),concat(fans_uid,uid)) as d
from test1.fans
    order by d
    )
,t2 as (select fans_uid, uid, create_date, d,
       row_number() over (partition by d order by create_date) as rn
from t1)
select  create_date,
       sum(if(rn,1,0)) over(order by create_date) as cnt
from t2
where rn=2
;

第三题：(这个是最让我头疼的，就是因为这个我才知道有了 recursive 递归方法)

# 在这里边最重要的是使用递归求解连续日期 
with recursive t as (
    select date('2023-01-01') as dt,null as cnt
    union all
    select date_add(dt,interval 1 day), cnt from t where dt < date(current_date)
)
     ,t1 as(select fans_uid, uid,create_date,
       if(uid < fans.fans_uid,concat(uid,fans_uid),concat(fans_uid,uid)) as d
from test1.fans
    order by d
    )
,t2 as (select fans_uid, uid, create_date, d,
       row_number() over (partition by d order by create_date) as rn
from t1)
,t3 as (select  create_date,
       sum(if(rn,1,0)) over(order by create_date) as cnt
from t2
where rn=2)
select dt,
       if(t.cnt is null ,t3.cnt,t.cnt) as cnt
       # coalesce(t.cnt,t3.cnt)
from t left join t3
on t.dt=t3.create_date;