SQL ID SELECT单行，用于每个ID(按updated_at列排序) [英] SQL SELECT single row for each ID ordered by updated_at column

问题描述

我想从下面的表格中选择每个露营的实际数据:

I want to select actual data for each campaing from table bellow:

+------------------------------+-------------+--------+-----+
| id | updated_at              | name        | status | ... |
+------------------------------+-------------+--------+-----+
| 1  | 2021-01-01 09:00:00 UTC | campaign A  | ACTIVE | ... |
| 2  | 2021-01-05 18:00:00 UTC | campaign B  | PAUSED | ... |
| 3  | 2021-01-10 12:00:00 UTC | campaign C  | ACTIVE | ... |
| 4  | 2021-01-12 14:00:00 UTC | campaign D  | ACTIVE | ... |
| 1  | 2021-01-02 18:00:00 UTC | campaign A  | PAUSED | ... |
| 1  | 2021-01-05 13:00:00 UTC | campaign AA | ACTIVE | ... |
| 2  | 2021-01-17 19:00:00 UTC | campaign B  | ACTIVE | ... |
| 1  | 2021-01-06 09:00:00 UTC | campaign AA | PAUSED | ... |
+------------------------------+-------------+--------+-----+

广告系列的 id 字段不是唯一的.广告活动的名称，状态和其他省略的列可以随时间更改.每行代表更新日期和时间上的实际广告系列状态.例如，广告系列ID = 1最初处于活动状态，然后暂停，然后重命名并激活，最​​后再次暂停.

The campaign id field is not unique. A campaign's name, status and other omitted columns can be changed over time. Each row represent actual campaign state on update date and time. For example campaign id = 1 was in active state initially, then paused, then renamed and activated, and finally paused again.

可以使用 ORDER BY Updated_at DESC LIMIT 1 语句接收特定广告系列的实际状态，对于广告系列#1，它的名称为'campaign AA'，状态为'PAUSED'.但是，如何通过单个查询接收每个广告系列的实际数据呢?

Actual state for the specific campaign can be received with ORDER BY updated_at DESC LIMIT 1 statement, for campaign #1 it will be name = 'campaign AA' and status = 'PAUSED'. But how to receive actual data for each campaign with a single query?

预期输出:

+------------------------------+-------------+--------+-----+
| id | updated_at              | name        | status | ... |
+------------------------------+-------------+--------+-----+
| 1  | 2021-01-06 09:00:00 UTC | campaign AA | PAUSED | ... |
| 2  | 2021-01-17 19:00:00 UTC | campaign B  | ACTIVE | ... |
| 3  | 2021-01-10 12:00:00 UTC | campaign C  | ACTIVE | ... |
| 4  | 2021-01-12 14:00:00 UTC | campaign D  | ACTIVE | ... |
+------------------------------+-------------+--------+-----+

谢谢.

P.S .:我正在使用Google BigQuery.

P.S.: I'm using Google BigQuery.

推荐答案

执行此操作的一种标准方法是使用 ROW_NUMBER :

One standard way of doing this uses ROW_NUMBER:

SELECT id, updated_at, name, status
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) rn
    FROM yourTable
) t
WHERE rn = 1;

这篇关于SQL ID SELECT单行，用于每个ID(按updated_at列排序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！