# クエリを移行する

Sigma のクエリを Presto から Trino に移行します。

Sigma のクエリインフラを [Presto v334](https://trino.io/docs/334/sql.html) から [Trino v414](https://trino.io/docs/414/sql/select.html) にアップグレードしました。このアップグレードにより、ほとんどのクエリは高速で実行されますが、一部のクエリでは予期しないエラーが発生したり、異なる形式の結果が生成されることがあります。

以下の推奨事項を使用して、Sigma クエリが Treno v414 との互換性を持つようにします。

## 無効なタイムゾーン

Treno v414 では、`AMERICA/NEW_YORK` タイムゾーンは無効になりました。

```sql
-- FAILED: Presto error: NOT_SUPPORTED: Time zone not supported: AMERICA/NEW_YORK
select
    date_format(
        c.created AT TIME ZONE 'AMERICA/NEW_YORK',
        '%Y-%m-%d'
    )
from
    charges c
```

代わりに、`America/New_York` を使用します。

```sql
-- VALID
select
    date_format(
        c.created AT TIME ZONE 'America/New_York',
        '%Y-%m-%d'
    )
from
    charges c
```

## 無効な列参照

Treno v414 では、範囲外の元のサブクエリまたは条件付きテーブル式 (CTE) を使用して列名を参照することはできません。

```sql
-- FAILED: Presto error: COLUMN_NOT_FOUND: Column 'c.created' can't be resolved
select c.created from (select created from charges c)
```

サブクエリ `c` は最上位レベルで定義されていませんが、定義された範囲の外で最上位レベルで参照されているため、以前のクエリは無効です。

```sql
-- VALID
select created from (select created from charges c)
```

```sql
-- VALID
select c.created from (select created from charges) c
```

サブクエリを使用せずに列を参照するか、参照と同じレベルでサブクエリを定義します。

## 科学的表記

Trino v414 で [double](https://trino.io/docs/414/language/types.html#double) を [varchar](https://trino.io/docs/414/language/types.html#string) にキャストすると、Presto v334 のような 10 進法による表記ではなく、科学的表記で結果が生成されます。

```sql
-- RESULT: 1.0E2
select cast(100.0 as varchar)
```

この 10 進法による表記を維持するには、double を [decimal](https://trino.io/docs/414/language/types.html#decimal) としてキャストしてから、varchar としてキャストします。

```sql
-- RESULT: 100.0
select cast(cast(100.0 as decimal(18,1)) as varchar)
```

## タイムスタンプ関数

### FROM_UNIXTIME

[from_unixtime](https://trino.io/docs/414/functions/datetime.html?highlight=from_unixtime#from_unixtime) を使用すると、Treno v414 は結果のタイムスタンプが UTC であると想定し、末尾に「UTC」を追加します。

```sql
-- Trino v414 RESULT: 1970-01-01 00:00:00.000 UTC
-- Presto v334 RESULT: 1970-01-01 00:00:00 +0000
select from_unixtime(0)
```

末尾の「UTC」を削除するには、`from_unixtime` の結果を `timestamp` としてキャストします。

```sql
-- RESULT: 1970-01-01 00:00:00 +0000
select cast(from_unixtime(0) as timestamp)
```

### TO_ISO8601

Presto v334 では、[to_iso8601](https://trino.io/docs/414/functions/datetime.html?highlight=to_iso8601#to_iso8601) を使用すると Zulu タイムゾーンのサフィックス (「Z」) をタイムゾーンのないタイムスタンプの末尾に追加しますが、Trino v414 では追加されません。

```sql
-- Presto v334 RESULT: 2024-04-01T00:00:00.000Z
-- Trino v414 RESULT: 2024-04-01T00:00:00
select to_iso8601(timestamp '2024-04-01')
```

末尾に Zulu タイムゾーンのサフィックスが追加されていることを確認するには、UTC のタイムスタンプを解釈してから `to_iso8601` を呼び出します。

```sql
-- RESULT: 2024-04-01T00:00:00Z
select to_iso8601(timestamp '2024-04-01' at time zone 'UTC')
```

## クエリの非決定性

Sigma バージョンに関係なく、クエリが確定的でない場合、得られる結果が実行ごとに異なる可能性があります。非決定性の結果が生成される可能性のある一般的なクエリパターンを以下に示します。

### 上位 K 件のクエリ

```sql
-- POTENTIALLY NON-DETERMINISTIC
select * from charges order by created DESC limit 10
```

最新の 10 番目と 11 番目の支払いが同時に作成された場合、どちらの支払いが返されるかは保証されません。確定的な結果を得るには、必ず一意の識別子で分類してください。

```sql
-- DETERMINISTIC
select * from charges order by created DESC, id limit 10
```

### ウィンドウ関数による集計

```sql
-- POTENTIALLY NON-DETERMINISTIC
select
    *
from
    (
        select
            c.*,
            row_number() over (
                partition by c.customer_id
                order by
                    c.amount DESC
            ) as row_rank
        from
            charges c
    )
where
    row_rank = 1
```

上記のクエリは、[row_number](https://trino.io/docs/414/functions/window.html?highlight=row_number#row_number) を使用して各顧客の最大の支払いを返します。 [ウィンドウ関数](https://trino.io/docs/414/functions/window.html)をご覧ください。顧客が同一の最大金額で複数の支払いを行っている場合、どの支払いが返されるかは保証されません。

確定的な結果を得るために、ウィンドウの順序を一意の識別子で並べ替えます。

```sql
-- DETERMINISTIC
select
    *
from
    (
        select
            c.*,
            row_number() over (
                partition by c.customer_id
                order by
                    c.amount DESC, c.created DESC, c.id
            ) as row_rank
        from
            charges c
    )
where
    row_rank = 1
```