WITH ['hello', 'world', 'arrays are great aren\'t they?'] AS string_array
SELECT string_array[0]
┌─arrayElement⋯g_array, 0)─┐
│ │
└──────────────────────────┘
SELECT
FlightDate,
Origin,
groupArray(toStringCutToZero(Dest)) AS Destinations
FROM ontime.ontime
WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA') AND FlightDate='2024-01-01'
GROUP BY FlightDate, Origin
ORDER BY length(Destinations)
WITH
'2024-01-01' AS date,
busy_airports AS (
SELECT
FlightDate,
Origin,
--highlight-next-line
groupArray(toStringCutToZero(Dest)) AS Destinations
FROM ontime.ontime
WHERE Origin IN ('ATL', 'ORD', 'DFW', 'DEN', 'LAX', 'JFK', 'LAS', 'CLT', 'SFO', 'SEA')
AND FlightDate = date
GROUP BY FlightDate, Origin
ORDER BY length(Destinations)
)
SELECT
Origin,
length(Destinations) AS outward_flights
FROM busy_airports
ORDER BY outward_flights DESC
WITH arrayMap(
d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME')),
groupArray(DepDelayMinutes)
) AS statuses
SELECT
Origin,
toStringCutToZero(Dest) AS Destination,
arrayZip(groupArray(Tail_Number), statuses) as tailNumberStatuses
FROM ontime.ontime
WHERE Origin = 'DEN'
AND FlightDate = '2024-01-01'
AND DepTime IS NOT NULL
AND DepDelayMinutes IS NOT NULL
GROUP BY ALL
上記のクエリでは、`arrayMap`関数が単一要素の配列`[DepDelayMinutes]`を受け取り、ラムダ関数`d -> if(d >= 30, 'DELAYED', if(d >= 15, 'WARNING', 'ON-TIME'`を適用して分類を行います。
次に、結果の配列の最初の要素が`[DepDelayMinutes][1]`で抽出されます。
[`arrayZip`](/sql-reference/functions/array-functions#arrayZip)関数は、`Tail_Number`配列と`statuses`配列を単一の配列に結合します。
### arrayFilter \{#arrayfilter}
次に、空港`DEN`、`ATL`、`DFW`について、30分以上遅延したフライトの数のみを確認します:
```sql runnable
SELECT
Origin,
OriginCityName,
--highlight-next-line
length(arrayFilter(d -> d >= 30, groupArray(ArrDelayMinutes))) AS num_delays_30_min_or_more
FROM ontime.ontime
WHERE Origin IN ('DEN', 'ATL', 'DFW')
AND FlightDate = '2024-01-01'
GROUP BY Origin, OriginCityName
ORDER BY num_delays_30_min_or_more DESC
上記のクエリでは、arrayFilter 関数の第1引数としてラムダ関数を渡しています。
このラムダ関数自体は、遅延時間（分）を表す d を受け取り、条件が満たされれば 1 を、そうでなければ 0 を返します。
WITH airport_routes AS (
SELECT
Origin,
--highlight-next-line
arraySort(groupArray(DISTINCT toStringCutToZero(Dest))) AS destinations
FROM ontime.ontime
WHERE FlightDate = '2024-01-01'
GROUP BY Origin
)
SELECT
a1.Origin AS airport1,
a2.Origin AS airport2,
--highlight-next-line
length(arrayIntersect(a1.destinations, a2.destinations)) AS common_destinations
FROM airport_routes a1
CROSS JOIN airport_routes a2
WHERE a1.Origin < a2.Origin
AND a1.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
AND a2.Origin IN ('DEN', 'ATL', 'DFW', 'ORD', 'LAS')
ORDER BY common_destinations DESC
LIMIT 10
SELECT
Origin,
toStringCutToZero(Dest) AS Destination,
groupArray(DepDelayMinutes) AS delays,
--highlight-start
round(arrayReduce('avg', groupArray(DepDelayMinutes)), 2) AS avg_delay,
round(arrayReduce('max', groupArray(DepDelayMinutes)), 2) AS worst_delay
--highlight-end
FROM ontime.ontime
WHERE Origin = 'DEN'
AND FlightDate = '2024-01-01'
AND DepDelayMinutes IS NOT NULL
GROUP BY Origin, Destination
ORDER BY avg_delay DESC
WITH range(0, 100, 10) AS delay,
toStringCutToZero(Dest) AS Destination
SELECT
'Up to ' || arrayJoin(delay) || ' minutes' AS delayTime,
countIf(DepDelayMinutes >= arrayJoin(delay)) AS flightsDelayed
FROM ontime.ontime
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY delayTime
ORDER BY flightsDelayed DESC
WITH range(0, 100, 10) AS delay,
toStringCutToZero(Dest) AS Destination
SELECT
del || '分まで' AS delayTime,
countIf(DepDelayMinutes >= del) flightsDelayed
FROM ontime.ontime
ARRAY JOIN delay AS del
WHERE Origin = 'DEN' AND Destination = 'MIA' AND FlightDate = '2024-01-01'
GROUP BY ALL
ORDER BY flightsDelayed DESC
