翻訳:Junichi Maruyama. - Original Blog Link
Apache SparkとDatabricksの新しいSQL機能のサポートをご紹介できることを嬉しく思います: Lateral Column Alias (LCA)です。この機能は、同じSELECTリスト内で先に指定した式を再利用できるようにすることで、複雑なSQLクエリを簡素化し、多くの場合、ネストしたサブクエリや共通テーブル式(CTE)を使用する必要性を排除します。このブログポストでは、この機能の使用例と、SparkとDatabricksのユーザーにもたらす利点について説明します。
Lateral Column Alias (LCA) は、同じSELECTリスト内で先に指定した式を再利用する機能をユーザーに提供します。
この機能は、以下に示す例を通してより良く理解することができます。以下は単純なクエリです:
LCAのサポートがない場合、ユーザーはこのクエリで、SELECTリストの後者のaが解決できないというエラーを受け取ることになる:
[UNRESOLVED_COLUMN.WITHOUT_SUGGESTION] A column or function parameter with name `a` cannot be resolved. ; line 1 pos 15;
幸いなことに、LCA機能により、このクエリの2番目のaは、同じSELECTリスト:1 AS aの中で、以前に定義されたエイリアスとして識別されるようになりました:
前述の例では LCA の基本的なコンセプトを紹介しましたが、この機能の真の威力は、複雑なサブクエリや CTE を排除する機能にあります。
LCAが導入される前は、以前のエイリアスで定義された属性を参照しようとすると、ユーザーは複数のサブクエリやCTEを処理しなければなりませんでした。このため、SQLクエリの複雑さと冗長性が増し、読み書きや保守が困難になりました。対照的に、LCAのサポートはこれらのクエリを根本的に単純化し、より使いやすく管理しやすくします。
例を見てみよう。商品名、カテゴリー、価格、顧客評価などの商品情報を格納するproductsテーブルがあるとします。我々のゴールは、いくつかの影響因子に基づいて調整された価格を計算することです。このシナリオは、LCAがどのように複雑なクエリを大幅に簡略化できるかを明確にします。
これがテーブル構造です:
私たちは、各商品の調整価格を、ユーザーの商品評価に基づく値上げ率と、カテゴリー内での商品のランクに基づく値上げ率の、2つの要素のうち大きい方の値で計算したいと考えています。LCAのサポートがない場合、クエリは次のようになります:
このロジックには多くの連鎖演算が含まれており、後の計算が前に計算された結果に依存します。そのため、各中間計算をクエリの後続ステージで参照するのに適した方法で保存するために、複数のCTEが必要になります。
しかし、LCAを使用すると、代わりに1つのSELECT文としてクエリを表現することができます:
LCAは連鎖させることもできる!これは、後続の式が参照できる現在のエイリアス式が、以前に定義された横方向のエイリアスを参照できることを意味します。例えば、final_increase_percentageの定義は、increase_percentage_based_on_ratingとincrease_percentage_based_on_rankという2つの横列エイリアスに依存します。次のadjusted_priceの計算は、final_increase_percentageを参照する。LCAのこの連鎖力により、ユーザーは、ある計算結果を次の計算の入力として使用する、一連の依存計算を作成することができます。
上記の例でわかるように、LCAはクエリを大幅に単純化し、繰り返し計算や複数のCTEを必要としないため、理解、保守、デバッグが容易になります。また、計算の定義と使用法がクエリ内で近接しているため、可読性も向上します。
ほとんどすべての式が横列エイリアス内に存在することができます。前節の例では、複雑なCASE-WHEN式やGREATEST関数式、あるいはウィンドウ関数が、後続の式で使用するために横列エイリアスの中に存在できることを示しています。
同じ意味で、この方法で集約式を入れ子にすることもできます。同じ商品テーブルでの例を示します:
LCAはまた、構造体、配列、マップのような複雑なデータ型でもうまく機能する。例えば
LCAは、CTEが提供する "run-once "セマンティクスを反映し、非決定性式が一度だけ評価されることを保証します。これにより、クエリで非決定性式を使用する場合、一貫した結果が保証されます。
例えば、上記のproductsテーブルの各商品にmember_priceがある場合を考えてみましょう。各商品に 0% から 5% の間のランダムな割引率を適用し、price と member_price の両方の割引価格を計算したいとします。この作業により、両方の価格に適用される割引率が同じであることが保証されます。
LCA を使用すると、次のように記述できます:
この例では、Databricksはdiscount_rateを一度だけ計算し、この値はadjusted_priceとadjusted_member_priceの計算を含む後続のすべての参照を通じて同じままです。
一方、単純に非決定的な式をコピーしている場合、各式を個別に評価するため、この動作は適用されず、2つの価格の割引率が矛盾することになります:
要約すると、ラテラル・カラム・エイリアスは、ユーザーが式ツリー上で名前付きエイリアスを定義し、後で同じSELECT句内でこのエイリアスを参照できるようにすることで、SQLクエリを大幅に簡素化する強力な機能です。
LCA は Databricks Runtime 12.2 LTS 以降、 Databricks SQL 2023.20 以降、Apache Spark 3.4 で完全に利用可能で、デフォルトで有効になっています。