1.需求说明
这是一个关于POI的应用，数据从水经微图下载而来，需要处理的是街道层级的数据，但是最终的POI信息要有省、市、县数据，所有需要用到行政区划表来补全数据。
2.编程实例
2.1 实现树结构
首先看一下具有树结构的数据：
通过 WITH RECURSIVE table_name AS 实现递归查询树结构数据【这里要特别注意一下 t0 和 t1 表】：
WITH RECURSIVE t1 AS (
SELECT "level", parent_code, area_code, "name"
FROM data_divisions
WHERE "name" = '枫杨街道'
UNION ALL
SELECT t0."level", t0.parent_code, t0.area_code, t0."name"
FROM data_divisions t0, t1
WHERE t0.area_code = t1.parent_code
)
SELECT "level", "name" FROM t1
结果验证：
2.2 自定义函数
使用STRING_AGG把省市县数据拼接成一个字段【函数等价于GROUP_CONCAT】：
SELECT
STRING_AGG ( "name", ',' ORDER BY "level" ) AS "divisions" FROM
( WITH RECURSIVE t1 AS (
SELECT "level", parent_code, area_code, "name"
FROM data_divisions
WHERE "name" = '枫杨街道'
UNION ALL
SELECT t0."level", t0.parent_code, t0.area_code, t0."name"
FROM data_divisions t0, t1
WHERE t0.area_code = t1.parent_code
)
SELECT "level", "name" FROM t1 ) t2
创建自定义函数：
CREATE OR REPLACE FUNCTION getdivisionsbyname ( TEXT ) RETURNS TEXT AS $BODY$
SELECT
STRING_AGG ( "name", ',' ORDER BY "level" ) AS "divisions" FROM
( WITH RECURSIVE t1 AS (
SELECT "level", parent_code, area_code, "name"
FROM data_divisions
WHERE "name" = '枫杨街道'
UNION ALL
SELECT t0."level", t0.parent_code, t0.area_code, t0."name"
FROM data_divisions t0, t1
WHERE t0.area_code = t1.parent_code
)
SELECT "level", "name" FROM t1 ) t2;
$BODY$ LANGUAGE SQL IMMUTABLE STRICT COST 100;
函数调用测试：
SELECT getDivisionsByName('枫杨街道');
2.3 函数使用
data_address_point 表的记录数是261条，执行耗时119.451s，这效率明显是由于多次调用自定义函数导致的 😢
SELECT
getdivisionsbyname(zone_name) || NAME AS "poi",
SPLIT_PART( coordinates, ',', 1 ) AS "longitude",
SPLIT_PART( coordinates, ',', 2 ) AS "latitude",
NAME AS "address",
SPLIT_PART( getdivisionsbyname(zone_name), ',', 1 ) AS "prov",
SPLIT_PART( getdivisionsbyname(zone_name), ',', 2 ) AS "city",
SPLIT_PART( getdivisionsbyname(zone_name), ',', 3 ) AS "district",
SPLIT_PART( getdivisionsbyname(zone_name), ',', 4 ) AS "town"
FROM data_address_point;
避免多次调用相同的自定义函数，优化后耗时23.634s，是之前的5分之1：
WITH t1 AS ( SELECT getdivisionsbyname ( zone_name ) AS "divisions", coordinates, "name", poi_type FROM data_address_point )
SELECT
ROW_NUMBER ( ) OVER ( ORDER BY "name" ) AS "id",
REPLACE ( divisions, ',', '' ) || "name" AS "poi",
poi_type,
SPLIT_PART( coordinates, ',', 1 ) AS "longitude",
SPLIT_PART( coordinates, ',', 2 ) AS "latitude",
NAME AS "address",
SPLIT_PART( divisions, ',', 1 ) AS "prov",
SPLIT_PART( divisions, ',', 2 ) AS "city",
SPLIT_PART( divisions, ',', 3 ) AS "district",
SPLIT_PART( divisions, ',', 4 ) AS "town"
FROM
t1
3.报错问题
实际上，上边的函数使用并是非顺利的，第一次进行查询时报错function cannot execute on a QE slice because it accesses relation
WITH t1 AS ( SELECT getdivisionsbyname ( zone_name ) AS "divisions", coordinates, "name", poi_type FROM data_address_point )
SELECT
ROW_NUMBER ( ) OVER ( ORDER BY "name" ) AS "id",
REPLACE ( divisions, ',', '' ) || "name" AS "poi",
poi_type,
SPLIT_PART( coordinates, ',', 1 ) AS "longitude",
SPLIT_PART( coordinates, ',', 2 ) AS "latitude",
NAME AS "address",
SPLIT_PART( divisions, ',', 1 ) AS "prov",
SPLIT_PART( divisions, ',', 2 ) AS "city",
SPLIT_PART( divisions, ',', 3 ) AS "district",
SPLIT_PART( divisions, ',', 4 ) AS "town"
FROM
t1
> ERROR: function cannot execute on a QE slice because it accesses relation "public.data_divisions" (seg0 slice1 192.168.0.123:6000 pid=168995)
CONTEXT: SQL function "getdivisionsbyname" during startup
UDF（User Defined Function）用户自定义函数在 segment 上不能访问任何表。由于 MPP 的特性，任何 segment 仅仅包含部分数据，因而在 segment 执行的 UDF 不能访问任何表，否则数据计算错误。Greenplum 支持另一种分布策略：复制表，即整张表在每个节点上都有一个完整的拷贝。可使用以下命令进行设置：
ALTER TABLE table_name SET DISTRIBUTED REPLICATED;
数据量大的表不适合使用复制表模式，一些不经常变动的数据量比较小的比如码表可以使用DISTRIBUTED REPLICATED模式，查询性能也会有明显的提升。