使用case作为联表条件

发表于 2021-09-02 更新于 2023-01-25

第一次遇到这种情况，需要先判断，联表的字段是哪个，然后再进行联表。初步思考，大概有以下几种方式。

1、联表条件使用case计算，计算出联表的字段值。
2、先计算出要联表的主键，放到临时表中。
3、一次联连两张表，谁有要谁的。
4、根据3，进行变种，两次联表。先联一次，有结果后，再联一次。不过每个字段取值，还是要case when计算
5、union方式。分两部计算。然后union在一起。

个人比较推崇方式2。不过还是根据数据量等进行合理的选择。

真实的场景需求是：根据运单号，去联表拿到大包号（不一定获取到），如果有大包号，则按大包号联表，否则一运单号联表，所以，存在优先级问题，联表的条件查不多。故假设以下场景来做实验。

准备条件

先按如下创建两张表，并插入适当的数据。test_demo2，id1有值，则按id1联表，否则按id2联表。

-- 表1
CREATE TABLE test_demo1 (
    id int,
    title text
)
DISTRIBUTED BY (id);

insert into test_demo1 values 
    (1,'测试1'),
    (2,'测试2'),
    (3,'测试3'),
    (4,'测试4'),
    (5,'测试5'),
    (6,'测试6')
    ;

-- 表2
CREATE TABLE test_demo2 (
    id1 int,
    id2 int,
    cont text
)
DISTRIBUTED BY (id1);

insert into test_demo2 values 
    (1,null,'描述1'),
    (2,null,'描述2'),
    (3,null,'描述3'),
    (null,4,'描述4'),
    (null,5,'描述5')
    ;

几种思路对比

1、联表条件使用case计算

一步能计算出条件。

select t2.*,t1.title from test_demo2 as t2 left join test_demo1 as t1 on (case when t2.id1 is not null then t2.id1 else t2.id2 end ) = t1.id;

美化版本

case …end的括号也能省略。

SELECT
	t2.*,t1.title
FROM
	test_demo2 AS t2
LEFT JOIN test_demo1 AS t1 ON (
	CASE
	WHEN t2.id1 IS NOT NULL THEN
		t2.id1
	ELSE
		t2.id2
	END
) = t1.id;

不足之处，由于test_demo2的关联键不确定，会导致计算出来的关联字段作为主键时，直接联表，会导致重分布。如果表test_demo2小的话，让一个大表重分布会比较耗费性能。（当然重分布谁，我还不确定）

采取该方式，试了貌似效果还不错。

联表条件如下，a是主表：

LEFT JOIN tb_scan as z on z.ship_id = (case when a.pac_id is not null then a.pac_id else a.ship_id end )

具体内容参见同名的sql文件。

2、先计算出要联表的主键，放到临时表中

需要两步才能完成。

-- 下面的sql会导致数据重分布
DROP TABLE IF EXISTS test_demo3;
CREATE TABLE test_demo3 WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS SELECT
    CASE
    WHEN t2.id1 IS NOT NULL THEN
        t2.id1
    ELSE
        t2.id2
    END as id,
    t2.*
FROM
    test_demo2 AS t2
DISTRIBUTED BY (id);

-- 理论上，数据计算，不会重分布。但是，保存的时候，可能要重分布。 
select * from  test_demo3 as t3 left join test_demo1 as t1 on t3.id = t1.id;

新计算出来的主键联表时，存在1:n的关系。

3、一次联连两张表，谁有要谁的。

另外一种思路是（我首先想出来的思路），进行两次联表，伪代码如下：

select 
	*
    (case when t2.xx is not null then t2.xx eles t1.xx end ) --其中xx代表各种要的字段值
from test_demo2  
left join test_demo1 as t1 on t1.id = test_demo2.id1
left join test_demo1 as t2 on t2.id = test_demo2.id2

或者，将联表相关的列都写上。最下一次计算的时候，再计算。

不管怎样，这种效果都不好。

4、分两次联表

差不多同上。

5、union方式，分两步计算

-- 首先，分为两部分  也可以用联表
SELECT id1 as id,cont from test_demo2 WHERE id1 is not NULL;
SELECT id2 as id,cont from test_demo2 WHERE id1 is null AND id2 is not NULL;

-- 整体如下：
SELECT id1 as id,cont,t1.title from (SELECT * from test_demo2 WHERE id1 is not NULL ) t2 LEFT JOIN test_demo1 as t1 on t1.id = t2.id1

UNION ALL

SELECT id2 as id,cont,t1.title from (SELECT * from test_demo2 WHERE id1 is null AND id2 is not NULL ) t2 LEFT JOIN test_demo1 as t1 on t1.id = t2.id2;

在真实场景中，可能要查的字段，并不存在，字段表达试可能要直接写null。

扩展

其实除了联表的时候，能用上case when else end这种语句，就连排序的时候，也能使用这种方式来处理。分组，没有试过，（分组的话，能直接使用select 的字段，而字段是可以用的，可以用简写group by 1，或者重写一次)，where条件，应该也可以。（估计不能直接引用计算字段，可能重复写一次）

case when else end，语句还是很强大的，实现了分支功能。

当然，也能将其封装到udf（用户自定义函数中）。如果对简单的枚举值，能支持数组表达式就更好了。（自定义函数plpgsql中如果能支持全局的数组，那更好了，待研究。python支持，但是python本身性能……）