SQL。如何从表中获得唯一值，但仅适用于1列？

Question

我有一个桌子。我按名称对此表进行排序，按时间列：

SELECT name, value 
FROM table1
WHERE time >= '2022-05-23 00:00:00' AND time <= '2022-05-23 01:00:00'  
ORDER BY name, time 

结果我得到了下一个表：

    name,   value,  time
    A,      5,      2022-05-23 01:01:12
    A,      9,      2022-05-23 01:02:11
    A,      7,      2022-05-23 01:03:21
    B,      5,      2022-05-23 01:04:23
    B,      6,      2022-05-23 01:05:33
    C,      7,      2022-05-23 01:06:30
    C,      8,      2022-05-23 01:07:41
    C,      3,      2022-05-23 01:08:44
    C,      7,      2022-05-23 01:09:50

然后我需要选择所有具有最低时间的唯一名称，并获得此名称的值：

结果应该像这样：

name,   value,  time
A,      5,      2022-05-23 01:01:12
B,      5,      2022-05-23 01:04:23
C,      7,      2022-05-23 01:06:30

================== =============================================== ============= = ==

我们刚从表中获取了每个第一个唯一名称：

name,   value,  time
A,      5,      2022-05-23 01:01:12 <- take this one
A,      9,      2022-05-23 01:02:11
A,      7,      2022-05-23 01:03:21 
B,      5,      2022-05-23 01:04:23 <- take this one
B,      6,      2022-05-23 01:05:33
C,      7,      2022-05-23 01:06:30 <- take this one
C,      8,      2022-05-23 01:07:41
C,      3,      2022-05-23 01:08:44
C,      7,      2022-05-23 01:09:50

我的目标是以最低的时间戳（或表格中的每个第一个唯一名称，因为它已经由时间戳）

我不清楚如何获得想要的结果。 我尝试使用“选择不同的名称，值”，但它是所有唯一名称和唯一值，但是我只需要有唯一的名称+值（时间最低）

Answer 1

您可以使用函数argmin聚合函数（ https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/Reference/Argmin/ ）以实现所需的结果。如果需要获取最新的行，则有gragmax聚合函数（ https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/reference/argmax/ ）。

argmin/argmax的想法是返回给定值的最小值或最大参数（在您的用例中，time）。

示例数据：

select * from table1; 

SELECT *
FROM table1

┌─name─┬─value─┬────────────────time─┐
│ A    │     5 │ 2022-05-23 01:01:12 │
│ A    │     5 │ 2022-05-23 01:01:12 │
│ A    │     5 │ 2022-05-23 01:01:12 │
│ A    │     5 │ 2022-05-23 01:01:12 │
│ A    │     9 │ 2022-05-23 01:02:11 │
│ A    │     7 │ 2022-05-23 01:03:21 │
│ B    │     5 │ 2022-05-23 01:04:23 │
│ B    │     6 │ 2022-05-23 01:05:33 │
│ C    │     7 │ 2022-05-23 01:06:30 │
│ C    │     8 │ 2022-05-23 01:07:41 │
│ C    │     3 │ 2022-05-23 01:08:44 │
│ C    │     7 │ 2022-05-23 01:09:50 │
└──────┴───────┴─────────────────────┘

12 rows in set. Elapsed: 0.002 sec.

由于您需要整个行（和argmin/argmax需要一个参数），因此需要将其转换为另一个结构（此示例中的元组），然后将其投射回他们的名称。以下查询返回第一个（或val表达式的最小值的参数）：

SELECT 
    tpl.1 AS name, 
    tpl.2 AS value, 
    tpl.3 AS time
FROM (
    SELECT 
        argMin(tuple(*), time) as tpl
    FROM 
        table1 
    GROUP BY name
)
ORDER BY name


┌─name─┬─value─┬────────────────time─┐
│ A    │     5 │ 2022-05-23 01:01:12 │
│ B    │     5 │ 2022-05-23 01:04:23 │
│ C    │     7 │ 2022-05-23 01:06:30 │
└──────┴───────┴─────────────────────┘


3 rows in set. Elapsed: 0.004 sec. 

AS gragmax以类似的方式工作，但要获取最新的（或参数的参数） val表达式的最大值），这将为您提供最新的（最新）名称/值/时间行：

SELECT
    tpl.1 AS name,
    tpl.2 AS value,
    tpl.3 AS time
FROM
(
    SELECT argMax(tuple(*), time) AS tpl
    FROM table1
    GROUP BY name
)
ORDER BY name ASC


┌─name─┬─value─┬────────────────time─┐
│ A    │     7 │ 2022-05-23 01:03:21 │
│ B    │     6 │ 2022-05-23 01:05:33 │
│ C    │     7 │ 2022-05-23 01:09:50 │
└──────┴───────┴─────────────────────┘

3 rows in set. Elapsed: 0.002 sec.

Answer 2

通常，您会使用row_number，但是ClickHouse不支持分析功能。我们可以使用加入方法：

SELECT t1.*
FROM table1 t1
INNER JOIN
(
    SELECT name, MIN(time) AS min_time
    FROM table1
    WHERE time >= '2022-05-23 00:00:00' AND time <= '2022-05-23 01:00:00'
    GROUP BY name
) t2
    ON t2.name = t1.name AND
       t2.min_time = t1.time
WHERE
    time >= '2022-05-23 00:00:00' AND time <= '2022-05-23 01:00:00';