sql - 获取多个字段的不同信息，其中一些字段为 NULL

Question

我有一个包含 6500 万行和 140 列的表格。数据来自多个来源，并且至少每月提交一次。

我正在寻找一种快速方法，仅从该数据中获取唯一的特定字段。事情是，我想处理所有信息以链接发送哪个发票和哪个识别号以及由谁发送的信息。问题是，我不想迭代超过 6500 万条记录。如果我可以获得不同的值，那么我只需要处理 500 万条记录，而不是 6500 万条。请参阅下面的数据说明和示例的 SQL Fiddle

如果说客户每个月提交一个链接到 passport_number_1、national_identity_number_1 和 driving_license_1 的 invoice_number，我只想要它出现的一行。即 4 个字段必须是唯一的

如果他们提交上述内容 30 个月，然后在第 31 个月他们发送链接到 passport_number_1、national_identity_number_2 和 driving_license_1 的 invoice_number，我也想选择这一行，因为national_identity 字段是新的，因此整行都是唯一的

• 链接到是指它们出现在同一行
• 对于所有字段，都可能在某一点出现 Null。
• 'pivot/composite' 列是 invoice_number 和由...所提交。如果其中任何一个不存在，请删除该行
• 我还需要将 database_id 包含在上述数据中。 IE。postgresql数据库自动生成的primary_id
• 唯一不需要返回的字段是 other_column和 yet_another_column。请记住该表有 140 列，所以不要需要他们
• 根据结果，创建一个新表来保存这个唯一的记录

请参阅此 SQL fiddle 以尝试重现场景。

从那个 fiddle ，我希望得到这样的结果:

• 第 1、2 和第 11 行:只应保留其中一个，因为它们正是相同的。最好是 id 最小的行。
• 第 4 行和第 9 行:其中一个将被删除，因为它们正是一样。
• 第 5、7 和 8 行:将被删除，因为它们缺少invoice_number 或 submitted_by。
• 结果将包含行(1、2 或 11)、3、(4 或 9)、6 和 10。

Answer 1

从具有四个不同字段的组中获取一个代表行(带有附加字段):

SELECT 
distinct on (
  invoice_number
  , passport_number
  , national_id_number
  , driving_license_number
)
  * -- specify the columns you want here
FROM my_table
where invoice_number is not null
and submitted_by is not null
;

请注意，除非您指定顺序 (documentation on distinct)，否则无法预测确切返回哪一行

编辑:

要按 id 对结果进行排序，只需在末尾添加 order by id 是行不通的，但这可以通过使用 CTE 的 eiter 来完成

with distinct_rows as (
    SELECT 
    distinct on (
      invoice_number
      , passport_number
      , national_id_number
      , driving_license_number
      -- ...
    )
      * -- specify the columns you want here
    FROM my_table
    where invoice_number is not null
    and submitted_by is not null
)
select *
from distinct_rows
order by id;

或者使原始查询成为子查询

select *
from (
    SELECT 
    distinct on (
      invoice_number
      , passport_number
      , national_id_number
      , driving_license_number
      -- ...
    )
      * -- specify the columns you want here
    FROM my_table
    where invoice_number is not null
    and submitted_by is not null
) t
order by id;