sql--来自各路面筋

1.pdd

CREATE TABLE pdd(
u_id        VARCHAR(20)   NOT NULL,
ip          INT           NOT NULL,
timestamp   datetime      NOT NULL
);
insert into pdd values('a',124,'2019-08-07 12:09:02');
insert into pdd values('b',124,'2019-08-07 12:09:02');
insert into pdd values('c',124,'2019-08-07 12:09:02');
insert into pdd values('a',174,'2019-08-07 12:09:02');
insert into pdd values('b',174,'2019-08-07 12:09:02');
insert into pdd values('a',194,'2019-08-07 12:09:02');
insert into pdd values('b',194,'2019-08-07 12:09:02');
-- 使用共同使用过的ip数量大于等于3个的用户名单
-- ab共同使用过124，174，194；输出uid_1；uid_2(a，b)
-- 一个人开了很多小号，用ip登陆次数识别出来

一开始没有想起自连接，不知道要groupby哪个，其实两个表自连接之后，变成ab,ac,ba,bc,ca,cb;cb,ba;ab;ba所以对两个id进行聚合然后过滤就可以 ;这也是自连接的一种运用方式，注意体会

select 
	a.u_id
    ,b.u_id
    ,count(*)
from pdd as a
join pdd as b
on a.ip = b.ip
where a.u_id <> b.u_id
group by a.u_id, b.u_id ;

select 
	distinct
	a.u_id
	,b.u_id
from pdd as a
join pdd as b
on a.ip = b.ip
where a.u_id <> b.u_id
group by a.u_id, b.u_id 
having count(*)>=3

2.就是一张表里有 mall_id, gmv

现在把这个总的gmv分均分成10个层级，每个层级的GMV由多少家店铺构成，输出的表字段是 layer(layer1,layer2....),  count(前10%GMV由多少家店铺贡献，10-20%由多少家贡献）

作者：跪求pdd给offer
链接：https://www.nowcoder.com/discuss/227871
来源：牛客网

select
    concat('layer', c.rk) as layer,
    count(c.rk) as count
from
    (select
        b.mall_id,
        case 
            when b.t2/b.t1 >= 0 and b.t2/b.t1 <= 0.1 then '1'
            when b.t2/b.t1 > 0.1 and b.t2/b.t1 <= 0.2 then '2'
            when b.t2/b.t1 > 0.2 and b.t2/b.t1 <= 0.3 then '3'
            when b.t2/b.t1 > 0.3 and b.t2/b.t1 <= 0.4 then '4'
            when b.t2/b.t1 > 0.4 and b.t2/b.t1 <= 0.5 then '5'
            when b.t2/b.t1 > 0.5 and b.t2/b.t1 <= 0.6 then '6'
            when b.t2/b.t1 > 0.6 and b.t2/b.t1 <= 0.7 then '7'
            when b.t2/b.t1 > 0.7 and b.t2/b.t1 <= 0.8 then '8'
            when b.t2/b.t1 > 0.8 and b.t2/b.t1 <= 0.9 then '9'
            else '10'
        end as rk
    from
        (select
            a.mall_id,
            sum(a.total_gmv) as t1,
            sum(a.total_gmv) over(order by a.total_gmv desc) as t2
        from
            (select
                mall_id,
                sum(gmv) as total_gmv
            from mall_gmv
            group by mall_id) a) b) c
group by c.rk
order by layer;