根据两列的不同组合选择最近的行

Question

我正在编写一个cronjob，它在我的数据库中的flags表上运行分析，结构如下：

| id | item | def | time_flagged | time_resolved | status  |
+----+------+-----+--------------+---------------+---------+
| 1  | 1    | foo | 1519338608   | 1519620669    | MISSED  |
| 2  | 1    | bar | 1519338608   | (NULL)        | OPEN    |
| 3  | 2    | bar | 1519338608   | 1519620669    | IGNORED |
| 4  | 1    | foo | 1519620700   | (NULL)        | OPEN    |

对于每个不同的def，对于每个唯一的price，我希望获得“最新”行(IFNULL(`time_resolved`, `time_flagged`) AS `time`)。如果给定的def-item组合不存在这样的行，那也没关系；我只是不希望给定的def-item组合有任何重复。

对于上面的数据集，我想选择：

| def | item | time       | status  |
+-----+------+------------+---------+
| foo | 1    | 1519620700 | OPEN    |
| bar | 1    | 1519338608 | OPEN    |
| bar | 2    | 1519620669 | IGNORED |

没有包括第1行，因为它被第4行“覆盖”了，因为两行具有相同的def-item组合，而后者具有更新的time。

数据集将具有几十个不同的def，几百个不同的item，以及大量只会随着时间增加的flag。

我该怎么做呢？我看到greatest-n-per-group标记中充斥着类似的问题，但我没有看到任何涉及我的特定情况的问题，即需要跨两列的“嵌套分组”。

Answer 1

您可以尝试：

select distinct def, item, IFNULL(time_resolved, time_flagged) AS time, status from flags A where IFNULL(time_resolved, time_flagged) = (select MAX(IFNULL(time_resolved, time_flagged)) from flags B where A.item = B.item and A.def = B.def )

我知道这不是最好的方法，但它可能对你有用

Answer 2

根据您的mySQL版本，您可以使用窗口函数：

SELECT def, item, time, status
FROM (
  SELECT 
    def, 
    item,
    time,
    status,
    RANK() OVER(PARTITION BY def, item ORDER BY COALESCE(time_resolved, time_flagged) DESC) MyRank  -- Rank each (def, item) combination by "time"
  FROM MyTable
) src
WHERE MyRank = 1 -- Only return top-ranked (i.e. most recent) rows per (def, item) grouping

如果可以使用具有相同"time“值的(def，item)组合，则将RANK()更改为ROW_NUMBER。这将保证每个分组只有一行。

Answer 3

你的意思是“对于每个唯一的定义和每个唯一的项目”？如果是这样的话，多列的group by似乎可以连接回原始表以获取其余数据(显示为临时表t)：

select 
    table.def,
    table.item,
    table.time,
    status
from
table
join (select
    def,
    item,
    max(time) time
from table
group by def, item) t
on 
    table.def=t.def and
    table.item=t.item and
    table.time=t.time