在 Presto 上选择"在多列上不同联接"

Question

我在presto有两个表。

因此，表 1 如下所示：

+--------+-------------+--------
|id1 | id2 |  date     | degree |
+--------+-------------+--------
|  1 |  10 |  20200101 |   1    |
|  1 |  11 |  20200101 |   1    |
|  1 |  11 |  20200101 |   1    |
|  2 |  52 |  20200101 |   2 .  |
|  2 |  52 |  20200101 |   2 .  |
|  2 |  53 |  20200101 | . 2 .  |
|  3 |  21 |  20200101 |   2 .  |
| ...| ... |  ...      |  ...   |
+--------+-----------+----------

表 2 是：

+--------+------------+-------+-------
|id1 | id2 |  date     | price | rank |
+--------+-------------+-------+-------
|  1 |  10 |  20200101 |  1200 | 1    |
|  1 |  10 |  20200101 |  1200 | 2    |
|  1 |  10 |  20200101 |       |      |
|  1 |  10 |  20200101 |  1300 | 1    |
|  1 |  10 |  20200101 |  1300 | 2    |
| ...| ... |  ...      |   ... |...   |
+--------+-----------+-----------------

我想做的是从表2中获取价格列并将其添加到基于三列id1，id2和date的表1中。如果我做这样的简单连接

select tab1.id1, tab1.id2, tab1.date, tab2.price
from tab1
left join tab2
on tab1.id1 = tab2.id1
and tab1.id2 = tab2.id2
and tab1.date = tab2.date

这就是我们所拥有的：

+--------+------------+----------------
|id1 | id2 |  date     | price | degree |
+--------+-------------+----------------
|  1 |  10 |  20200101 |  1200 |   1    |
|  1 |  10 |  20200101 |  1200 |   1    |
|  1 |  10 |  20200101 |       |   1    |
|  1 |  10 |  20200101 |  1300 |   1    |
|  1 |  10 |  20200101 |  1300 |   1    |
+--------+-----------+-------------------

但实际上我想要的是这个：

+--------+------------+----------------
|id1 | id2 |  date     | price | degree |
+--------+-------------+----------------
|  1 |  10 |  20200101 |  1200 | . 1 .  |
|  1 |  10 |  20200101 |  1300 |   1 .  |
+--------+-----------+-------------------

Answer 1

使用组

select * from (
select tab1.id1 as id1, tab1.id2 as id2, tab1.date as date, tab2.price as price
from tab1
left join tab2
on tab1.id1 = tab2.id1
and tab1.id2 = tab2.id2
and tab1.date = tab2.date) as t group by t.id1,t.id2,t.date,t.price

Answer 2

这涉及对数据的一些推测，但根据您的示例，如果您将排名列限制为值 1，它似乎会给出所需的结果。

select
tab1.id1, tab1.id2, tab1.date, tab2.price
from
tab1
join tab2 on
tab1.id1 = tab2.id1 and
tab1.id2 = tab2.id2 and
tab1.date = tab2.date and
tab2.rank = 1 -- add this line

当然，如果整个数据集中并非如此，那么这将不起作用。

在大多数情况下，我喜欢避免select distinct及其派生(包括按每列分组，这本质上是一个选择的不同(，因为它有一种非常随意的感觉——只需删除任何碰巧相同的记录。 相反，我认为最好了解您的数据并了解为什么某些记录被筛选掉。

例如，如果您确实想选择具有最低"排名"值的记录，但它并不总是保证为 1 的值，这将起作用：

select distinct on (tab1.id1, tab1.id2, tab1.date)
tab1.id1, tab1.id2, tab1.date, tab2.price
from
tab1
join tab2 on
tab1.id1 = tab2.id1 and
tab1.id2 = tab2.id2 and
tab1.date = tab2.date and
tab2.rank = 1 -- add this line
order by
tab1.id1, tab1.id2, tab1.date, tab2.rank

我知道我只是说我避免select distinct，但这实际上是一个完全不同的select distinct on，order by使得保留哪个记录以及为什么保留非常明确。