🐈

# PrestoSQLのAggregate Functionsを試す-1

2020/12/18に公開

## arbitary(x)

``````SELECT
arbitrary(continent)
FROM
gapminder
``````
``````> Asia
``````

Asia以外という指定がなければEuropeとなる

``````SELECT
arbitrary(continent)
FROM
gapminder
WHERE continent != 'Asia'
``````
``````> Europe
``````

## array_agg(x)

インプットxを配列にする関数

``````SELECT
array_agg(continent)
FROM
gapminder
``````
``````> [Asia, Asia, Asia, Asia, Asia, Asia, Asia, Asia, Asia, Asia, Asia, Asia, Europe, Europe, Europe, .....

```sql
SELECT
array_agg(DISTINCT continent)
FROM
gapminder
``````
``````> [Americas, Asia, Africa, Oceania, Europe]
``````

## avg(x)

xに列名を入れればその列の平均を、time interval rangeをいれれば、その期間の平均を出す

``````SELECT
FLOOR(avg(gdppercap)) AS "GDPperCapの平均"
FROM
gapminder
``````
``````> 7215.0
``````

## bool_and(boolean) bool_or(boolean)

boolの演算用関数

``````SELECT
DISTINCT continent,
bool_and(continent = 'Asia')
FROM
gapminder
GROUP BY year,continent
LIMIT 30
``````

## max_by(x,y), min_by(x,y)

y列が最大値のx列の値を返すらしい

``````SELECT
max_by(pop,gdppercap)
FROM
gapminder
``````
``````> 212846
``````

``````SELECT
pop,
gdppercap
FROM
gapminder
ORDER BY gdppercap DESC
LIMIT 1
``````
``````> 212846
``````

### max_by(x,y,n), min_by(x,y,n)

``````SELECT
max_by(pop,gdppercap,2)
FROM
gapminder
``````
``````> [212846, 841934]
``````

``````SELECT
pop,
gdppercap
FROM
gapminder
ORDER BY gdppercap DESC
LIMIT 2
``````

ただのmax,min関数も、第二引数nを与えることで、上位n個を表現可能