翻訳：Junichi Maruyama. - Original Blog Link
Apache SparkとDatabricksの新しいSQL機能のサポートをご紹介できることを嬉しく思います： Lateral Column Alias (LCA)です。この機能は、同じSELECTリスト内で先に指定した式を再利用できるようにすることで、複雑なSQLクエリを簡素化し、多くの場合、ネストしたサブクエリや共通テーブル式（CTE）を使用する必要性を排除します。このブログポストでは、この機能の使用例と、SparkとDatabricksのユーザーにもたらす利点について説明します。
Lateral Column エイリアス (LCA) サポートとは？
Lateral Column Alias (LCA) は、同じSELECTリスト内で先に指定した式を再利用する機能をユーザーに提供します。
この機能は、以下に示す例を通してより良く理解することができます。以下は単純なクエリです：
SELECT 1 AS a, a + 1LCAのサポートがない場合、ユーザーはこのクエリで、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の中で、以前に定義されたエイリアスとして識別されるようになりました：
> SELECT 1 AS a, a + 1
1 2LCAチェイニングによる複雑なサブクエリとCTEの排除
前述の例では LCA の基本的なコンセプトを紹介しましたが、この機能の真の威力は、複雑なサブクエリや CTE を排除する機能にあります。
LCAが導入される前は、以前のエイリアスで定義された属性を参照しようとすると、ユーザーは複数のサブクエリやCTEを処理しなければなりませんでした。このため、SQLクエリの複雑さと冗長性が増し、読み書きや保守が困難になりました。対照的に、LCAのサポートはこれらのクエリを根本的に単純化し、より使いやすく管理しやすくします。
例を見てみよう。商品名、カテゴリー、価格、顧客評価などの商品情報を格納するproductsテーブルがあるとします。我々のゴールは、いくつかの影響因子に基づいて調整された価格を計算することです。このシナリオは、LCAがどのように複雑なクエリを大幅に簡略化できるかを明確にします。
これがテーブル構造です：
CREATE TABLE products (
id INT,
name STRING,
category INT,
price DECIMAL(10, 2),
member_price DECIMAL(10, 2) COMMENT 'Special price for members',
rating INT COMMENT 'Customer rating ranges between 1 to 5'
) USING delta;
INSERT INTO
products
VALUES
(1, 'Product 1', 0, 100.00, 90.00, 3),
(2, 'Product 2', 1, 150.00, 120.00, 4),
(3, 'Product 3', 0, 200.00, 190.00, 5),
(4, 'Product 4', 2, 250.00, 210.00, 2),
(5, 'Product 5', 1, 300.00, 150.00, 1);私たちは、各商品の調整価格を、ユーザーの商品評価に基づく値上げ率と、カテゴリー内での商品のランクに基づく値上げ率の、2つの要素のうち大きい方の値で計算したいと考えています。LCAのサポートがない場合、クエリは次のようになります：
-- BEFORE: without LCA
WITH cte1 AS (
SELECT id, name, category, rating, price,
CASE
WHEN rating = 1 THEN 0.02
WHEN rating = 2 THEN 0.04
WHEN rating = 3 THEN 0.06
WHEN rating = 4 THEN 0.08
ELSE 0.1
END AS increase_percentage_based_on_rating,
rank() OVER (PARTITION BY category ORDER BY rating) AS rating_rank
FROM products
), cte2 AS (
SELECT id, name, category, rating, price, increase_percentage_based_on_rating,
CASE
WHEN rating_rank = 1 THEN 0.2
WHEN rating_rank = 2 THEN 0.1
ELSE 0
END AS increase_percentage_based_on_rank
FROM cte1
), cte3 AS (
SELECT id, name, category, rating, price,
GREATEST(increase_percentage_based_on_rating,
increase_percentage_based_on_rank) AS final_increase_percentage
FROM cte2
)
SELECT id, name, category, rating, price,
CAST(price * (1 + final_increase_percentage) AS DECIMAL(10, 2))
AS adjusted_price
FROM cte3このロジックには多くの連鎖演算が含まれており、後の計算が前に計算された結果に依存します。そのため、各中間計算をクエリの後続ステージで参照するのに適した方法で保存するために、複数のCTEが必要になります。
しかし、LCA を使用すると、代わりに1つのSELECT文としてクエリを表現することができます：
-- AFTER: with LCA
SELECT id, name, category, price, rating,
CASE
WHEN rating = 1 THEN 0.02
WHEN rating = 2 THEN 0.04
WHEN rating = 3 THEN 0.06
WHEN rating = 4 THEN 0.08
ELSE 0.1
END AS increase_percentage_based_on_rating,
rank() OVER (PARTITION BY category ORDER BY rating) AS rating_rank,
CASE
WHEN rating_rank = 1 THEN 0.2
WHEN rating_rank = 2 THEN 0.1
ELSE 0
END AS increase_percentage_based_on_rank,
GREATEST(increase_percentage_based_on_rating,
increase_percentage_based_on_rank) AS final_increase_percentage,
CAST(price * (1 + final_increase_percentage) AS DECIMAL(10, 2))
AS adjusted_price
FROM productsLCAは連鎖させることもできる！これは、後続の式が参照できる現在のエイリアス式が、以前に定義された横方向のエイリアスを参照できることを意味します。例えば、final_increase_percentageの定義は、increase_percentage_based_on_ratingとincrease_percentage_based_on_rankという2つの横列エイリアスに依存します。次のadjusted_priceの計算は、final_increase_percentageを参照する。LCAのこの連鎖力により、ユーザーは、ある計算結果を次の計算の入力として使用する、一連の依存計算を作成することができます。
上記の例でわかるように、LCAはクエリを大幅に単純化し、繰り返し計算や複数のCTEを必要としないため、理解、保守、デバッグが容易になります。また、計算の定義と使用法がクエリ内で近接しているため、可読性も向上します。
LCAのすべて
単純式、集約式、ウィンドウ式
ほとんどすべての式が横列エイリアス内に存在することができます。前節の例では、複雑なCASE-WHEN式やGREATEST関数式、あるいはウィンドウ関数が、後続の式で使用するために横列エイリアスの中に存在できることを示しています。
同じ意味で、この方法で集約式を入れ子にすることもできます。同じ商品テーブルでの例を示します：
SELECT category AS c, string(c) AS c_str, avg(rating) AS avg_rating,
concat('category ', c_str, ' has average rating ', string(avg_rating))
FROM products
GROUP BY category
| 1 | 1 | 2.5 | category 1 has average rating 2.5 |
| 2 | 2 | 2 | category 2 has average rating 2.0 |
| 0 | 0 | 4 | category 0 has average rating 4.0 |複雑なデータ型
LCAはまた、構造体、配列、マップのような複雑なデータ型でもうまく機能する。例えば
SELECT named_struct('a', named_struct('b', 1)) AS foo1, foo1.a.b + 1 AS bar1,
map('a', 1, 'b', 2) AS foo2, foo2['b'] AS bar2,
array(named_struct('a', 1)) AS foo3, foo3[0].a AS bar3;
| foo1 | bar1 | foo2 | bar2 | foo3 | bar3 |
|-----------------|------|------------------|------|------------|------|
| {"a": {"b": 1}} | 2 | {"a": 1, "b": 2} | 2 | [{"a": 1}] | 1 |非決定論的表現
LCAは、CTEが提供する "run-once "セマンティクスを反映し、非決定性式が一度だけ評価されることを保証します。これにより、クエリで非決定性式を使用する場合、一貫した結果が保証されます。
例えば、上記のproductsテーブルの各商品にmember_priceがある場合を考えてみましょう。各商品に 0% から 5% の間のランダムな割引率を適用し、price と member_price の両方の割引価格を計算したいとします。この作業により、両方の価格に適用される割引率が同じであることが保証されます。
LCA を使用すると、次のように記述できます：
SELECT id, price, member_price,
1 - RAND() * 0.05 AS discounted_rate,
CAST(discounted_rate * price AS DECIMAL(10, 2)) AS adjusted_price,
CAST(discounted_rate * member_price AS DECIMAL(10, 2))
AS adjusted_member_price
FROM products
WHERE id = 3;
| 3 | 200.00 | 190.00 | 0.961144856978617 | 192.23 | 182.62 |この例では、Databricksはdiscount_rateを一度だけ計算し、この値はadjusted_priceとadjusted_member_priceの計算を含む後続のすべての参照を通じて同じままです。
一方、単純に非決定的な式をコピーしている場合、各式を個別に評価するため、この動作は適用されず、2つの価格の割引率が矛盾することになります：
SELECT id, name, price, member_price,
CAST((1 - RAND() * 0.05) * price AS DECIMAL(10, 2)) AS adjusted_price,
CAST((1 - RAND() * 0.05) * member_price AS DECIMAL(10, 2))
AS adjusted_member_price
FROM products
WHERE id = 3;
| 3 | 200.00 | 190.00 | 191.44 | 184.32 |LCAを試してみよう！
要約すると、ラテラル・カラム・エイリアスは、ユーザーが式ツリー上で名前付きエイリアスを定義し、後で同じSELECT句内でこのエイリアスを参照できるようにすることで、SQLクエリを大幅に簡素化する強力な機能です。
- これにより、同じ式を何度も繰り返したり、サブクエリやCTEを使用する必要がなくなり、代わりに簡潔で読みやすいSELECTクエリが生成されます。
- あらゆる種類の式や複雑なデータ型に対応しています。SQL構文は、より柔軟性を高めるために、これらのエイリアスを連結することもサポートしています。
- 各非決定性式が一度 だけ評価されることを保証するため、複数の参照で一貫した結果を得ることができます。
LCA は Databricks Runtime 12.2 LTS 以降、 Databricks SQL 2023.20 以降、Apache Spark 3.4 で完全に利用可能で、デフォルトで有効になっています。