SQL互粉问题

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

原题总共有三题：

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

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

3. 求出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;