具有多个返回列的 max_by [英] max_by with multiple return columns

问题描述

Presto 的文档说 max_by(x, y) 返回与所有输入值中 y 的最大值相关联的 x 值.(https://prestodb.github.io/docs/current/functions/aggregate.html#max_by)

Presto's documentation says max_by(x, y) returns the value of x associated with the maximum value of y over all input values. (https://prestodb.github.io/docs/current/functions/aggregate.html#max_by)

如何返回与最大 y 关联的多列(不仅是 x)?我发现 max_by((x1, x2, x3), y) 工作(几乎)但它返回一个带有 x1, x2, x3 的单列，我不知道如何将它转换成多个列.

How can I return multiple columns (not only x) associated with the maximum y? I found that max_by((x1, x2, x3), y) works (almost) but it returns a single column with x1, x2, x3 and I don't know how to convert it into multiple columns.

推荐答案

自 Presto 314

正如 Martin Traverso 指出的那样，由于 Presto 314更好的选择，[] 下标运算符.请参阅马丁的回答.

Since Presto 314

As Martin Traverso pointed out, since Presto 314 there is a better option, the [] subscript operator. See Martin's answer.

(x1, x2, x3) 创建一个带有匿名字段的 row.目前，要访问单个行字段，您需要将值转换为一行命名字段:

(x1, x2, x3) creates a row with anonymous fields. Currently, to access individual row fields, you need to cast the value to a row with named fields:

CAST(row_value AS row(some_field field_type, other_field, field_type, ...))

在查询中，这可以在 max_by 内部或外部(无关紧要).示例:

In a query this can be inside max_by or outside (doesn't matter). Example:

presto> SELECT r.afield, r.bfield, r.cfield
     -> FROM (
     ->     SELECT max_by(CAST((x1, x2, x3) AS row(afield integer, bfield varchar, cfield double)), y) r
     ->     FROM (VALUES (1, 42, 'a', 13e0), (2, 40, 'b', 77e0)) t(y, x1, x2, x3)
     -> );
 afield | bfield | cfield
--------+--------+--------
     40 | b      |   77.0
(1 row)

我知道这很冗长.有一个问题使这更方便:https://github.com/prestosql/presto/问题/860.

I understand this is quite verbose. There is an issue to make this more convenient: https://github.com/prestosql/presto/issues/860.

这篇关于具有多个返回列的 max_by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！