sql - 如何比较表的多列和多行

Question

我有一个看起来像这样的大数据表

ID    Marker    Value1    Value2
================================
1     A         10        11
1     B         12        13
1     C         14        15
2     A         10        11
2     B         13        12
2     C
3     A         10        11
3     C         12        13

我想通过以下数据搜索此数据，这些数据是用户输入的，未存储在表中:

Marker    Value1    Value2
==========================
A         10        11
B         12        13
C         14        14

结果应该是这样的:

ID    Marker    Value1    Value2    Match?
==========================================
1     A         10        11        true
1     B         12        13        true
1     C         14        15        false
2     A         10        11        true
2     B         13        12        true
2     C                             false  
3     A         10        11        true
3     C         12        13        false

最终这个(上表不是必需的，它应该展示这些值是如何形成的):

ID    Matches    Percent
========================
1     2          66%
2     2          66%
3     1          33%

我正在寻找最有前途的方法来让它在 SQL(确切地说是 PostgreSQL)中工作。

我的想法:

1. 创建一个临时表，将其与上面的表合并，并将结果分组
2. 使用 CASE WHEN 或临时 PROCEDURE 仅使用单个(可能臃肿的)查询

我对这两种方法都不满意，因此提出了这个问题。我怎样才能有效地比较两个像这样的表？

Answer 1

可以使用公用表表达式中的 VALUES 子句提供用户输入，然后可以将其用于与实际表的左连接。

with user_input (marker, value1, value2) as (
  values 
    ('A', 10, 11),
    ('B', 12, 13),
    ('C', 14, 14)
)
select d.id,
       count(*) filter (where (d.marker, d.value1, d.value2) is not distinct from (u.marker, u.value1, u.value2)), 
       100 * count(*) filter (where (d.marker, d.value1, d.value2) is not distinct from (u.marker, u.value1, u.value2)) / cast(count(*) as numeric) as pct
from data d
  left join user_input u on (d.marker, d.value1, d.value2) = (u.marker, u.value1, u.value2)
group by d.id
order by d.id;  

返回:

id | count | pct  
---+-------+------
 1 |     2 | 66.67
 2 |     2 | 66.67
 3 |     1 | 50.00

在线示例:https://rextester.com/OBOOD9042

编辑

如果值的顺序不相关(因此 (12,13)​​ 被认为与 (13,12) 相同，那么比较会变得有点复杂。

with user_input (marker, value1, value2) as (
  values 
    ('A', 10, 11),
    ('B', 12, 13),
    ('C', 14, 14)
)
select d.id,
       count(*) filter (where (d.marker, least(d.value1, d.value2), greatest(d.value1, d.value2)) is not distinct from (u.marker, least(u.value1, u.value2), greatest(u.value1, u.value2)))
from data d
  left join user_input u on (d.marker, least(d.value1, d.value2), greatest(d.value1, d.value2)) = (u.marker, least(u.value1, u.value2), greatest(u.value1, u.value2))
group by d.id
order by d.id;