SQL查询中笛卡尔积的巧妙使用（文末福利）

点击上方“超哥的杂货铺”，轻松关注

本文通过两个小例子学习一下笛卡尔积的巧妙使用。后台回复“笛卡尔积”可以获取本文pdf版本，便于阅读保存。

笛卡尔积，又叫cross join，是SQL中两表连接的一种方式。

假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行。

笛卡尔积的写法为：

select *
from A,B

或者

select * 
from A 
cross join B

通常我们都要在实际SQL中避免直接使用笛卡尔积，因为它会使“数据爆炸”，尤其是数据量很大的时候。但某些时候，巧妙的使用笛卡尔积，反而能快速帮助我们解决实际问题。下面看几个例子。

with as的用法

在此之前，我们先看一下with as 的用法。

with tmp as
(
    select * from class
)

select * from tmp

上面的写法先执行select * from class定义(生成)了一个中间表tmp，然后使用了tmp这个中间表。通常可以用来将固定的查询抽取出来，只查一次，多次使用，从而提高效率。也可以和union all结合起来构造数据供测试使用，在本文接下来的部分会看到后面场景的这种用法。关于with as的一些要点和注意事项可以参考下面的链接：

https://blog.csdn.net/baidu_30527569/article/details/48680745

例子1-产生顺序值：查询当日每小时的收入数据，未产生收入的置为0

假设有一张收入表，每过一个小时，就自动更新上一小时的收入数据。但我们希望对于未更新的时间收入值显示为0。这样能更好的体现完整性，也便于进行多天数据的对比。如下图所示：

对于收入非0的小时，我们可以从收入表中直接查询出当小时的收入数据。收入表结构如下(假设当前收入数据只更新到16点)：

查询的SQL为：

select dt, hour, income
from t_h_income
where day = '2020-04-19'

显然，得到的结果不会包含17点及以后的时间。我们可以采用笛卡尔积构造一个小时序列，如下面代码所示：

with t_hour as (
select '00' as dhour
union all 
select '01' as dhour
union all 
select '02' as dhour
union all 
select '03' as dhour
union all 
select '04' as dhour
union all 
select '05' as dhour
union all 
select '06' as dhour
union all 
select '07' as dhour
union all 
select '08' as dhour
union all 
select '09' as dhour
union all 
select '10' as dhour
union all 
select '11' as dhour
union all 
select '12' as dhour
union all 
select '13' as dhour
union all 
select '14' as dhour
union all 
select '15' as dhour
union all 
select '16' as dhour
union all 
select '17' as dhour
union all 
select '18' as dhour
union all 
select '19' as dhour
union all 
select '20' as dhour
union all 
select '21' as dhour
union all 
select '22' as dhour
union all 
select '23' as dhour
),t_day as 
(
select '2020-04-19' as dt
)
select * 
from t_day, t_hour

得到的结果如下，生成了这一天每个小时的结构。

将上面的结果与原来的数据左关联，关联不上的置为0，即可得到想要的结果。代码如下：

with t_hour as (
select '00' as dhour
union all 
select '01' as dhour
union all 
select '02' as dhour
union all 
select '03' as dhour
union all 
select '04' as dhour
union all 
select '05' as dhour
union all 
select '06' as dhour
union all 
select '07' as dhour
union all 
select '08' as dhour
union all 
select '09' as dhour
union all 
select '10' as dhour
union all 
select '11' as dhour
union all 
select '12' as dhour
union all 
select '13' as dhour
union all 
select '14' as dhour
union all 
select '15' as dhour
union all 
select '16' as dhour
union all 
select '17' as dhour
union all 
select '18' as dhour
union all 
select '19' as dhour
union all 
select '20' as dhour
union all 
select '21' as dhour
union all 
select '22' as dhour
union all 
select '23' as dhour
),t_day as 
(
select '2020-04-19' as dt
)

select * 
from t_day, t_hour

select a.dt, a.dhour, case when b.income is null then 0 else b.income end as income
from
(
select dt, dhour
from t_day, t_hour
) a 
left join 
t_h_income b
on a.dt = b.dt and a.dhour = b.hour

通过手动构造dt和dhour，用笛卡尔积产生了一个“序列”。而对于dhour的构造，也可以采用笛卡尔积的方式，但需要注意限制范围不大于23，代码如下：

with t_hour as 
(
select '0' as id
union all 
select '1' as id
union all 
select '2' as id 
),
f_hour as (
select '0' as id
union all 
select '1' as id
union all 
select '2' as id
union all 
select '3' as id
union all 
select '4' as id
union all 
select '5' as id
union all 
select '6' as id
union all 
select '7' as id
union all 
select '8' as id
union all 
select '9' as id
)

select concat(a.id, b.id) hour
from t_hour a, f_hour b
where concat(a.id, b.id) <= '23'
order by hour

以上我们都主要使用了笛卡尔积产生顺序值的场景，类似的可以构造从00~99的数字，构造之后也可以根据实际需要加入新的限制条件。

例子2-模拟循环：遍历字符串，每行按顺序输出一个字符

注：例子来源于《SQL Cookbook》第6章，经过自己的修改。

问题：考虑用SQL实现：将表emp中name为KING的字符串显示为4行，每行包含其中一个字符。

这里需要笛卡尔积配合字符串截取函数来实现。要实现逐一访问字符串，需要有一个中间表，存储序列值，类似于前面提到的序列。我们看下下面的代码：

with t5 as 
(
select 1 as pos
union all 
select 2 as pos 
union all
select 3 as pos 
union all
select 4 as pos 
union all
select 5 as pos 
),
emp as 
(
select 'KING' as name
)

select *
from emp, t5

得到的结果如下图所示：

考虑到字符串截取函数能够按位置截取。正好可以用上生成的pos。代码如下:

with t5 as 
(
select 1 as pos
union all 
select 2 as pos 
union all
select 3 as pos 
union all
select 4 as pos 
union all
select 5 as pos 
),
emp as 
(
select 'KING' as name
)

select substr(name, pos, 1)
from emp, t5
where t5.pos <= length(emp.name)

可以看到使用了pos，就能够“循环”地截取字符串了。需要注意where里加上了循环跳出的条件，这也比较好理解：不能截取超过字符串长度的字符。

还可以按照需要调整遍历时输出的格式，如下面代码和结果所示：

select 
substr(name, pos) char_name1,
substr(name, length(name)-pos+1) char_name1
from emp, t5
where t5.pos <= length(emp.name)

这个例子中我们利用笛卡尔积模拟循环，对字符串进行了遍历。

小结

本文首先学习了with as的用法，然后通过例子总结了两个巧妙使用笛卡尔积的场景：生成序列和模拟循环。虽然在实际中可能用的不是很多，但也体现出了SQL的灵活性。生成序列可以更广义的理解为：需要产生两个表中字段的任意组合，这两个字段可能是没有实际联系的。可以参考下面链接中关于每个班级血型的例子，核心思想也是这个。

https://blog.csdn.net/xiaolinyouni/article/details/6943337

实际中应该有很多类似的场景。

而模拟循环是笛卡尔积结合了字符串截取函数实现的，本质上还是“组合”。下次再遇到类似场景的时候，可以考虑下笛卡尔积能否实现。

除此以外《SQL Cookbook》中也提到了笛卡尔积可以用于结果转置~有机会我们以后再来学习。本文代码不是很复杂，后台回复“笛卡尔积”可以获取本文pdf版本，便于阅读保存。

福利时间：

最后给大家推荐一个小福利：超哥的杂货铺专属当当优惠码：7VC7KX

当当目前买书有满100-50，200-100，300-150,400-200的活动。我上面发的优惠码可以和活动叠加使用。如果你买书满了400，官方可以减200，优惠码再减30，相当于170买400的书。优惠码全场通用，结算页面输入可以自动抵扣截止时间为4月23日，用完为止。

以清净心看世界；

用欢喜心过生活。

超哥的杂货铺，你值得拥有~

长按二维码关注我们

推荐阅读：

1.手把手带你开启机器学习之路——房价预测(一)

2.手把手带你开启机器学习之路——房价预测(二)

3.手把手带你开启机器学习之路——分类问题总览

4.在一个机器学习项目中，你需要做哪些工作？

5.简单几步，教你使用scikit-learn做分类和回归预测

6.RFM模型是什么，我用python带你实战！

7.一场pandas与SQL的巅峰大战