PostgreSQL - 如何根据 PSQL VIEW 中的公共唯一标识符将 NULL 值替换为另一列中的值

Question

我的PSQL视图中有三个外来标识符。如何根据 NULL second_id 值的共同first_id将 NULL 替换为third_id值？

现在：

 first_id | second_id | third_id 
----------+-----------+----------
        1 |           |      11
        1 |           |      11
        1 |           |      11
        1 |        22 |      22
        2 |        33 |      33
        3 |        44 |      44
        4 |        55 |      55
        5 |        66 |      66
        6 |           |      77
        6 |           |      77
        6 |           |      77
        6 |           |      77
        6 |        88 |      88

应该是：

 first_id | second_id | third_id 
----------+-----------+----------
        1 |        22 |      11
        1 |        22 |      11
        1 |        22 |      11
        1 |        22 |      22
        2 |        33 |      33
        3 |        44 |      44
        4 |        55 |      55
        5 |        66 |      66
        6 |        88 |      77
        6 |        88 |      77
        6 |        88 |      77
        6 |        88 |      77
        6 |        88 |      88

如何进行此更改？

应填充second_id列中的 NULL 值，即不应有空白单元格。

如果second_id列与third_id列共享一个值，则此值应填充second_id列中的空白单元格。

它们

都应该基于它们的共同first_id。

非常感谢。我真的很感激。

---

second_id实际上是对third_id的CASE WHEN修改。此修改是在视图中进行的。

视图：

    View "public.my_view"
               Column            |            Type             | Modifiers | Storage  | Description 
    -----------------------------+-----------------------------+-----------+----------+-------------
     row_number                  | bigint                      |           | plain    | 
     first_id                    | integer                     |           | plain    | 
     second_id                   | integer                     |           | plain    | 
     third_id                    | integer                     |           | plain    | 
     first_type                  | character varying(255)      |           | extended | 
     date_1                      | timestamp without time zone |           | plain    | 
     date_2                      | timestamp without time zone |           | plain    | 
     date_3                      | timestamp without time zone |           | plain    | 
    View definition:
     SELECT row_number() OVER (PARTITION BY t.first_id) AS row_number,
        t.first_id,
            CASE
                WHEN t.localization_key::text = 'rq.bkd'::text THEN t.third_id
                ELSE NULL::integer
            END AS second_id,
        t.third_id,
        t.first_type,
            CASE
                WHEN t.localization_key::text = 'rq.bkd'::text THEN t.created_at
                ELSE NULL::timestamp without time zone
            END AS date_1,
            CASE
                WHEN t.localization_key::text = 'st.appt'::text THEN t.created_at
                ELSE NULL::timestamp without time zone
            END AS date_2,
            CASE
                WHEN t.localization_key::text = 'st.eta'::text THEN t.created_at
                ELSE NULL::timestamp without time zone
            END AS date_3
       FROM my_table t
      WHERE (t.localization_key::text = 'rq.bkd'::text OR t.localization_key::text = 'st.appt'::text OR t.localization_key::text = 'st.eta'::text) AND t.first_type::text = 'thing'::text
      ORDER BY t.created_at DESC;

下面是视图正在使用的表定义的链接 （my_table）。

https://gist.github.com/dankreiger/376f6545a0acff19536d

再次感谢您的帮助。

Answer 1

你可以通过以下方式获得它：

select a.first_id, coalesce(a.second_id,b.second_id), a.third_id 
from my_table a 
left outer join
    (
    select first_id, second_id from my_table
    where second_id is not null 
    ) b
    using (first_id)

所以更新应该是：

update my_table a set second_id = b.second_id
  from 
  (
  select first_id, second_id from my_table 
  where second_id is not null 
  ) b
where b.first_id = a.first_id and a.second_id is null

Answer 2

您无法UPDATE基础表my_table因为它没有second_id列，因此应使视图按所需方式显示数据。对于 CTE，这相当简单：

CREATE VIEW my_view AS
  WITH second (first, id) AS (
    SELECT first_id, third_id
    FROM my_table
    WHERE t.localization_key = 'rq.bkd')
  SELECT
    row_number() OVER (PARTITION BY t.first_id) AS row_number,
    t.first_id,
    s.id AS second_id,
    t.third_id,
    t.first_type,
    CASE
      WHEN t.localization_key = 'rq.bkd' THEN t.created_at
    END AS date_1,
    CASE
      WHEN t.localization_key = 'st.appt' THEN t.created_at
    END AS date_2,
    CASE
      WHEN t.localization_key = 'st.eta' THEN t.created_at
    END AS date_3
  FROM my_table t
  JOIN second s ON s.first = t.first_id
  WHERE (t.localization_key = 'rq.bkd'
     OR t.localization_key = 'st.appt'
     OR t.localization_key = 'st.eta')
    AND t.first_type = 'thing'
  ORDER BY t.created_at DESC;

这假设my_table.localization_key = 'rq.bkd'你只有 1 个third_id值;如果没有，你应该添加适当的限定符，如 ORDER BY first_id ASC NULLS LAST LIMIT 1 或其他合适的过滤器。另请注意，CTE 是 JOIN ed，而不是 LEFT JOIN ed，假设始终存在没有 NULL s 的有效对(first_id, third_id)。