PostgreSQL：根据函数结果为另一个表中的每一行生成列

我需要帮助。
表1：
| 身份证|val1|val 2值|
| --------------|--------------|--------------|
| 0|十五岁|十七岁|
| 1|五|六|
| ……|……|……|
表2：
| id_another|val1|val 2值|val3|
| --------------|--------------|--------------|--------------|
| 0|十五岁|十七岁|一百|
| 1|五|六|两百|
我想得到：
| 身份证|val1|val 2值|belongs_to_id_another_0|belongs_to_id_another_1|is_outlier|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 0|十五岁|十七岁|1|空|空|
| 1|五|六|空|空|1|
| ……|……|……|……|……||
说明：对于表1中的每个实体，我应该应用与表2中的每个实体的函数比较（表1和表2的大小顺序是~1000：1，因此时间复杂度不会是问题）。

CREATE OR REPLACE FUNCTION is_loc_inside_cls (
    loc_longitude FLOAT,
    loc_latitude FLOAT,
    cls_longitude FLOAT,
    cls_latitude FLOAT,
    cls_radius FLOAT
)
RETURNS INT AS $$
BEGIN
  IF (2 * 6378137.0 * ASIN(SQRT(SIN((loc_latitude * PI() / 180.0 - cls_latitude * PI() / 180.0) / 2) * SIN((loc_latitude * PI() / 180.0 - cls_latitude * PI() / 180.0) / 2) + (COS(loc_latitude * PI() / 180.0 ) * COS(cls_latitude * PI() / 180.0)) * SIN((loc_longitude * PI() / 180.0 - cls_longitude * PI() / 180.0) / 2) * SIN((loc_longitude * PI() / 180.0 - cls_longitude * PI() / 180.0) / 2)))) * (2 * 6378137.0 * ASIN(SQRT(SIN((loc_latitude * PI() / 180.0 - cls_latitude * PI() / 180.0) / 2) * SIN((loc_latitude * PI() / 180.0 - cls_latitude * PI() / 180.0) / 2) + (COS(loc_latitude * PI() / 180.0 ) * COS(cls_latitude * PI() / 180.0)) * SIN((loc_longitude * PI() / 180.0 - cls_longitude * PI() / 180.0) / 2) * SIN((loc_longitude * PI() / 180.0 - cls_longitude * PI() / 180.0) / 2)))) <= cls_radius * cls_radius THEN 
    RETURN 1;
  ELSE
    RETURN NULL;
  END IF;
END; $$
LANGUAGE PLPGSQL;

然后，我在表2中创建与实体一样多的列，并在那里放置return. is_outlier value == 1，如果在这些新列中到处都是NULL值，否则== NULL。问题是我如何应用这个逻辑？找不到任何有效的例子。
我想不出我应该实现FOR LOOP逻辑的部分，我设法只实现了函数

在解决这个问题之前，以下是关于常规代码改进的几点：
1.使用有意义的列名。Latitude、longitude 和 radius 表示的含义比 * val 1 、 val 2 、 val 3 * 更多。
1.对值使用最自然的数据类型。因为 is_loc_inside_cls 计算逻辑条件，所以它应该返回布尔值。
1.避免使用NULL作为值。NULL通常应该表示缺少值或未知值。在 is_loc_inside_cls 的情况下，只要没有参数为NULL，结果就是已知的。
1.考虑重构 is_loc_inside_cls 以将数值计算移动到由 is_loc_inside_cls 调用的函数。这样做允许其他代码使用该计算。
下面的重构 is_loc_inside_cls 返回一个布尔值。IF语句已经从函数的原始版本中删除，直接返回结果。这个版本的函数返回NULL，表示如果任何参数为NULL，则条件未知;否则，它返回TRUE或FALSE。

CREATE OR REPLACE FUNCTION is_loc_inside_cls (loc_longitude float, loc_latitude float, cls_longitude float, cls_latitude float, cls_radius float)
  RETURNS boolean
  AS $$
BEGIN
  RETURN (2 * 6378137.0 * ASIN(SQRT(SIN((loc_latitude * PI() / 180.0 - cls_latitude * PI() / 180.0) / 2) * SIN((loc_latitude * PI()
    / 180.0 - cls_latitude * PI() / 180.0) / 2) + (COS(loc_latitude * PI() / 180.0) * COS(cls_latitude * PI() / 180.0)) *
    SIN((loc_longitude * PI() / 180.0 - cls_longitude * PI() / 180.0) / 2) * SIN((loc_longitude * PI() / 180.0 - cls_longitude *
    PI() / 180.0) / 2)))) * (2 * 6378137.0 * ASIN(SQRT(SIN((loc_latitude * PI() / 180.0 - cls_latitude * PI() / 180.0) / 2) *
    SIN((loc_latitude * PI() / 180.0 - cls_latitude * PI() / 180.0) / 2) + (COS(loc_latitude * PI() / 180.0) * COS(cls_latitude *
    PI() / 180.0)) * SIN((loc_longitude * PI() / 180.0 - cls_longitude * PI() / 180.0) / 2) * SIN((loc_longitude * PI() / 180.0 -
    cls_longitude * PI() / 180.0) / 2)))) <= cls_radius * cls_radius;
END;
$$
LANGUAGE PLPGSQL;

对于下面的查询，我已经将列名从 * val 1 、 val 2 * 和 * val 3 * 分别更改为 latitude、longitude 和 radius。此查询使用 is_loc_inside_cls 的重构版本，因此 belongs_to_id_another_0 和 belongs_to_id_another_1 可能分别为TRUE、FALSE、FALSE、TRUE、FALSE和FALSE。或NULL。如果 belongs_to_id_another_0 或 belongs_to_id_another_1 为TRUE，则 is_outlier 将为FALSE，而不管另一个的值如何。如果两者都为FALSE，则 is_outlier 将为TRUE。如果两者都不为TRUE并且其中一个或两者都为NULL，则则 is_outlier 也将为NULL，表示无法确定状态。

SELECT
  table_1.id,
  table_1.latitude,
  table_1.longitude,
  bool_and(
    CASE table_2.id
    WHEN 0 THEN
      t.is_inside_cls
    END) AS belongs_to_id_another_0,
  bool_and(
    CASE table_2.id
    WHEN 1 THEN
      t.is_inside_cls
    END) AS belongs_to_id_another_1,
  bool_and(NOT t.is_inside_cls)
    OR nullif (bool_or(t.is_inside_cls IS NULL), TRUE) AS is_outlier
  FROM
    table_1
  CROSS JOIN table_2
  JOIN LATERAL (
    VALUES (is_loc_inside_cls (table_1.latitude, table_1.longitude, table_2.latitude, table_2.longitude, table_2.radius))) t (is_inside_cls) ON TRUE
GROUP BY
  table_1.id,
  table_1.latitude,
  table_1.longitude
ORDER BY
  table_1.id;

通过复制 *belongs_to_id_another_**列的模式，可以轻松地扩展此查询以支持更多行。is_outlier 的逻辑不依赖于 table_2 中的行数。
如果在目标环境中可用，请考虑使用 tablefunc PostgreSQL扩展提供的 crosstab 函数。如果 tablefunc 扩展不可用，并且需要支持table_2中的任意行数，请创建一个函数来动态生成SQL，然后执行结果查询。

PostgreSQL：根据函数结果为另一个表中的每一行生成列

1条答案

相关问题

热门标签

最新问答