集計関数 · R2 SQL ドキュメント

集計関数は、複数行を 1 つの結果にまとめます。GROUP BY と使い、グループごとの要約を計算します。GROUP BY なしでは、全行に対する 1 つの結果を計算します。

ほとんどの集計は DISTINCT 修飾子に対応しています。例: COUNT(DISTINCT customer_id) や SUM(DISTINCT total_amount)。

基本的な集計

COUNT

行を数えます。COUNT(*) はすべての行を数えます。COUNT(column) は NULL 以外の値を数えます。

SELECT COUNT(*) AS total_rows
FROM my_namespace.sales_data

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department
ORDER BY dept_count DESC

SUM

列の値の合計を返します。

SELECT SUM(total_amount) AS grand_total
FROM my_namespace.sales_data

SELECT department, SUM(total_amount) AS dept_total
FROM my_namespace.sales_data
GROUP BY department
ORDER BY dept_total DESC

AVG

列の値の平均を返します。別名: mean。

SELECT AVG(total_amount) AS avg_amount
FROM my_namespace.sales_data

SELECT department, AVG(total_amount) AS avg_amount
FROM my_namespace.sales_data
GROUP BY department
ORDER BY avg_amount DESC

MIN

最小値を返します。数値列と文字列列で使えます。

SELECT MIN(total_amount) AS min_amount, MIN(customer_id) AS first_customer
FROM my_namespace.sales_data

SELECT department, MIN(total_amount) AS min_amount
FROM my_namespace.sales_data
GROUP BY department

MAX

最大値を返します。数値列と文字列列で使えます。

SELECT MAX(total_amount) AS max_amount, MAX(customer_id) AS last_customer
FROM my_namespace.sales_data

SELECT department, MAX(total_amount) AS max_amount
FROM my_namespace.sales_data
GROUP BY department

MEDIAN

正確な中央値を返します。大規模データセットでは、代わりに approx_median を使ってください。

SELECT MEDIAN(total_amount) AS median_amount
FROM my_namespace.sales_data

SELECT department, MEDIAN(total_amount) AS median_amount
FROM my_namespace.sales_data
GROUP BY department

PERCENTILE_CONT

WITHIN GROUP (ORDER BY ...) を使い、指定したパーセンタイルの正確な値を返します。パーセンタイルの引数は 0.0 以上 1.0 以下である必要があります。大規模データセットでは、代わりに approx_percentile_cont を使ってください。

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_amount) AS p95
FROM my_namespace.sales_data

近似集計

近似集計関数は、統計的に推定した結果を返し、正確な集計よりメモリと計算を大幅に抑えられます。大規模データセットを分析し、近似結果で十分なときに使います。

approx_percentile_cont

T-Digest アルゴリズムで、指定したパーセンタイルの近似値を返します。パーセンタイルの引数は 0.0 以上 1.0 以下である必要があります。

SELECT approx_percentile_cont(total_amount, 0.5) AS median,
       approx_percentile_cont(total_amount, 0.95) AS p95
FROM my_namespace.sales_data

SELECT department,
       approx_percentile_cont(total_amount, 0.5) AS median
FROM my_namespace.sales_data
GROUP BY department
ORDER BY median DESC

approx_percentile_cont_with_weight

近似の加重パーセンタイルを返します。行は weight 列で重み付けします。

SELECT approx_percentile_cont_with_weight(unit_price, quantity, 0.5) AS weighted_median
FROM my_namespace.sales_data
WHERE unit_price IS NOT NULL AND quantity IS NOT NULL

approx_median

近似の中央値を返します。approx_percentile_cont(column, 0.5) と同等です。

SELECT approx_median(total_amount) AS median_amount
FROM my_namespace.sales_data

SELECT department, approx_median(total_amount) AS median
FROM my_namespace.sales_data
GROUP BY department

approx_distinct

HyperLogLog を使い、一意な値の近似件数を返します。

SELECT approx_distinct(customer_id) AS unique_customers
FROM my_namespace.sales_data

SELECT department, approx_distinct(customer_id) AS unique_customers
FROM my_namespace.sales_data
GROUP BY department

approx_top_k

出現頻度が高い上位 k 個の値と、その近似件数を返します。

SELECT approx_top_k(department, 5) AS top_departments
FROM my_namespace.sales_data

統計集計

var / var_samp

標本分散を返します。

SELECT var(total_amount) AS variance
FROM my_namespace.sales_data

SELECT department, var(total_amount) AS variance
FROM my_namespace.sales_data
GROUP BY department

var_pop

母分散を返します。

SELECT var_pop(total_amount) AS pop_variance
FROM my_namespace.sales_data

stddev / stddev_samp

標本標準偏差を返します。

SELECT stddev(total_amount) AS std_dev
FROM my_namespace.sales_data

SELECT department, stddev(total_amount) AS std_dev
FROM my_namespace.sales_data
GROUP BY department

stddev_pop

母標準偏差を返します。

SELECT stddev_pop(total_amount) AS pop_std_dev
FROM my_namespace.sales_data

covar_samp

標本共分散を返します。別名: covar。

SELECT covar_samp(total_amount, CAST(quantity AS DOUBLE)) AS covariance
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

covar_pop

母共分散を返します。

SELECT covar_pop(total_amount, CAST(quantity AS DOUBLE)) AS pop_covariance
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

corr

2 つの列のピアソン相関係数を返します。

SELECT corr(total_amount, CAST(quantity AS DOUBLE)) AS correlation
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_slope

線形回帰直線の傾きを返します。

SELECT regr_slope(total_amount, CAST(quantity AS DOUBLE)) AS slope
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_intercept

線形回帰直線の y 切片を返します。

SELECT regr_intercept(total_amount, CAST(quantity AS DOUBLE)) AS intercept
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_count

NULL 以外のペアの件数を返します。

SELECT regr_count(total_amount, CAST(quantity AS DOUBLE)) AS pair_count
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_r2

決定係数（R-squared）を返します。

SELECT regr_r2(total_amount, CAST(quantity AS DOUBLE)) AS r_squared
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_avgx

NULL 以外のペアについて、独立変数（x）の平均を返します。

SELECT regr_avgx(total_amount, CAST(quantity AS DOUBLE)) AS avg_qty
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_avgy

NULL 以外のペアについて、従属変数（y）の平均を返します。

SELECT regr_avgy(total_amount, CAST(quantity AS DOUBLE)) AS avg_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_sxx

独立変数の平方和を返します。

SELECT regr_sxx(total_amount, CAST(quantity AS DOUBLE)) AS sxx
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_syy

従属変数の平方和を返します。

SELECT regr_syy(total_amount, CAST(quantity AS DOUBLE)) AS syy
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

regr_sxy

対になった変数の積和を返します。

SELECT regr_sxy(total_amount, CAST(quantity AS DOUBLE)) AS sxy
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL AND quantity IS NOT NULL

ビット単位の集計

bit_and

グループ内のすべての値のビット単位 AND を返します。

SELECT department, bit_and(quantity) AS and_result
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
GROUP BY department

bit_or

グループ内のすべての値のビット単位 OR を返します。

SELECT department, bit_or(quantity) AS or_result
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
GROUP BY department

bit_xor

グループ内のすべての値のビット単位 XOR を返します。

SELECT department, bit_xor(quantity) AS xor_result
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
GROUP BY department

真偽値の集計

bool_and

グループ内の値がすべて true の場合に true を返します。

SELECT department, bool_and(is_completed) AS all_completed
FROM my_namespace.sales_data
WHERE is_completed IS NOT NULL
GROUP BY department

bool_or

グループ内のいずれかの値が true の場合に true を返します。

SELECT department, bool_or(is_completed) AS any_completed
FROM my_namespace.sales_data
WHERE is_completed IS NOT NULL
GROUP BY department

位置による集計

first_value

指定した並び順で、グループ内の最初の値を返します。

SELECT department,
       first_value(customer_id ORDER BY total_amount ASC) AS lowest_spender
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
GROUP BY department

last_value

指定した並び順で、グループ内の最後の値を返します。

SELECT department,
       last_value(customer_id ORDER BY total_amount ASC) AS highest_spender
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
GROUP BY department

コレクションの集計

これらの集計は、入力値をすべて 1 つの結果に集めます。すべての値をメモリに保持するため、大規模データセットでは WHERE フィルタまたは GROUP BY でグループサイズを抑えてください。

array_agg

グループ内の値を配列に集めます。

SELECT department, array_agg(customer_id) AS customers
FROM my_namespace.sales_data
GROUP BY department

string_agg

グループ内の値を、指定した区切り文字で 1 つの文字列に連結します。

SELECT department, string_agg(customer_id, ', ') AS customer_list
FROM my_namespace.sales_data
GROUP BY department