Prestoで各行ごとに複数列の値の中から最大値/最小値を取得する

※Prestoと書いていますが、正確にはTreasure Dataで動かすことを念頭に置いた記事です。
ただ、この記事で紹介するGREATEST / LEAST という関数はMySQLにも実装されているようなので、MySQLでも同じように動作すると思います。

そんなに頻繁にあることでは無いのですが、DBのとあるテーブルのデータについて、行ごとに、複数列の最大値を取得したいことがありました。

これ、行と列が逆なら簡単です。MAX関数使うだけです。

SELECT
    MAX(col1),
    MAX(col2),
    MAX(col3)
FROM
    table_name

上のクエリで、列ごとに最大値が取得できます。
今回やりたいのはその逆で、行ごとの最大値が欲しいのです。
列が3つくらいであれば、CASE文で対応することもできなくは無いかなと思います。
こんなふうに。

SELECT
    id,
    CASE
        WHEN col1>=col2 AND col1>=col3 THEN col1
        WHEN col2>=col3 AND col2>=col1 THEN col2
        WHEN col3>=col1 AND col3>=col2 THEN col3
    END AS max_value
FROM
    table_name

ただ、列数が増えるとこの方法で対応するのはなかなか厄介です。(あまりやりたく無い)
それに、上のクエリでは対象の列にNULLが含まれていた場合に正常に動作しないので、NULLも考慮する必要がある場合はもっと複雑なクエリを書く必要があります。

もう少しスマートな方法としては、以前紹介した横縦変換の方法で値を縦持ちに変換して、
GROUP BY と MAX を使うこともできるかと思います。
参考: PrestoのUNNESTを利用した横縦変換

WITH
    unpivot_table AS (
        SELECT
            id,
        　　　　  t.key,
        　　　　  t.value
        FROM
            table_name
        CROSS JOIN UNNEST (
            array['col1', 'col2', 'col3'],
            array[col1, col2, col3]
        ) AS t (key, value)
    )
SELECT
    id,
    MAX(value) AS max_value
FROM
    unpivot_table
GROUP BY
    id

ただ、これはこれで仰々しくてちょっと嫌だなと思っていました。

それでドキュメントを調べてみると、どうやらGREATEST というメソッドが用意されていたようです。
参考: GREATEST and LEAST

これを使うと非常に話は単純で、次のクエリで行ごとに3列(co1, col2, col3)の最大値が取得できます。

SELECT
    id,
    GREATEST(
        col1,
        col2,
        col3
    ) AS max_value
FROM
    table_name

同様に、最小値を求めるLEAST も容易されています。(使用例略)

ちなみに、どの列が最大だったのかを取得できる GREATEST_BY みたいなのもあるといいなと思って探してみたのですが、流石にそれはなさそうでした。最大値と合わせてどの列が最大だったのかも欲しい場合は、上の UNNEST を使うクエリで縦持ちに変換して、MAX_BYするのが現実的かなと思います。(もしくはSQLで実行するのを諦めてPythonなどで書くか)

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル