根据时间戳删除BQ中的旧重复行 [英] Remove old duplicate rows in BQ based on timestamp

问题描述

我有一个具有相同ad_id的重复(x2次)行的BQ表。

我要删除TS&>为120分钟的旧行，其中存在具有相同ad_id(架构包含timestamp, ad_id, value. But there is not rowId)的较新行。

这是我的尝试，有没有更好的方法？

DELETE FROM {table_full_name} o
       WHERE timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 MINUTE) AND timestamp in ( 
                                            SELECT MIN(timestamp) 
                                            FROM  {table_full_name}  i 
                                            WHERE i.ad_id=o.ad_id 
                                            GROUP  BY ad_id) 

数据示例：

`ad-id` | `ts`             | `value`   |
`1`     | Sep-1-2021 12:01 | `Scanned` |
`2`     | Sep-1-2021 12:02 | `Error`   |
`1`     | Sep-1-2021 12:03 | `Removed` |

我要将其清理为：

`ad-id` | `ts`             | `value`   |
`2`     | Sep-1-2021 12:02 | `Error`   |
`1`     | Sep-1-2021 12:03 | `Removed` |

我看到这个post，但BQ不支持row-id的自动递增。

我看到这个post。但我如何在没有ts间隔的情况下修改它(因为它是未知的)。

推荐答案

您可以尝试此脚本。使用COUNT() with HAVING从当前时间拉取时间戳超过120分钟的重复记录。TIMESTAMP_DIFF。

DELETE
FROM `table_full_name` 
WHERE ad_id in (SELECT ad_id 
                    FROM `table_full_name`
                    GROUP BY ad_id
                    HAVING COUNT(ad_id) > 1) 
AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), timestamp, MINUTE) > 120

之前：

之后：

这篇关于根据时间戳删除BQ中的旧重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！