王张开

Appearance

交叉连接 ​

【SQL入门教程 第27集 交叉连接和自连接-哔哩哔哩】 https://b23.tv/EINZLkR

但需要两张表的全部的一一对应信息时可以用，比如：

  • 一张商品信息表
  • 一张商品包装表
  • 交叉连接后，可以得到商品和包装的所有配对可能性。
sql
-- 创建商品表和包装表
create table goods(
gid int primary key auto_increment,
gname varchar(32)
)default charset = utf8;

create table packages(
pid int primary key auto_increment,
pname varchar(32)
)default charset = utf8;

-- 插入模拟数据
insert into goods(gname) values('A'),('B'),('C');
insert into packages(pname) values('a'),('b');


-- 交叉连接语法
select * from goods cross join packages;

1832670215054295040.png

自链接 ​

https://developer.aliyun.com/article/870215 阿里

  • 自连接就是结构和数据完全一样的两张表进行连接。

  • 在做数据处理的时候，通常会给它们分别重命名来加以区分，然后关联

  • 不分别起名字，sql脸盲。

  • 使用自连接的目的并不是自己和自己关联，而是和表里的其他字段进行组合。

举例： 黑产行为筛选 ​

  • 已知同一用户在移动端（platform=21）连续点击两次时间间隔不高于2秒，则可能产生黑产行为
  • 筛选出21年3月份所有有可能为黑产行为的点击记录
sql
-- 创建模拟表

create table if not exists tencent_video_click(
vuid int,
`date` date,                 -- 点击日期
`time` int,                  -- '点击时间时间戳格式',
platform int default 21      -- 21：移动端。22:网页端
);
select unix_timestamp('2021-03-01');
-- 插入造的数据
insert into tencent_video_click values
(1, '2021-03-01', 1614528000, 21),
(1, '2021-03-01', 1614528001, 22),
(1, '2021-03-01', 1614528001, 21),
(2, '2021-03-01', 1614528004, 21),
(3, '2021-03-01', 1614528000, 21),
(2, '2021-03-01', 1614528005, 21),
(3, '2021-03-01', 1614528000, 22),
(4, '2021-03-01', 1614528007, 21),
(4, '2021-03-01', 1614528009, 22),
(4, '2021-03-01', 1614528010, 21);

-- 要求：已知同一用户在移动端连续点击两次时间间隔不高于2秒，则可能产生黑产行为
-- 筛选出21年3月份所有有可能为黑产行为的点击记录

1832670215800881152.png

注意： ​

  • 上述例子中，第一次点击表和第二次点击表只是人为命名的，并不是真的按照时间顺序产生的表

  • 所以，自链接后，要将第二次点击时间大与第一次点击时间作为筛选条件

  • 实际上，没有任何筛选条件下的自链接就是表的自乘，如下图

    自链接前：

    1832670217252110336.png

    自链接后：

1832670217507962880.png

  • 因此自链接的重点是利用筛选条件，达到目的。�达到目的。