集約関数¶

概要¶

集約関数は、単一の結果を計算するために、値の集合に対して演算を行います。

count()、count_if()、max_by()、min_by()、およびapprox_distinct()を除き、これらの集約関数はすべてnull値を無視し、入力行がない場合、またはすべての値がnullの場合はnullを返します。たとえば、sum()はゼロではなくnullを返し、avg()はnull値をカウントに含めません。coalesce関数を使用してnullをゼロに変換できます。

array_agg()などの一部の集約関数は、入力値の順序によって異なる結果を生成します。この順序付けは、集約関数内でORDER BY句を記述することで指定できます。

array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z)

一般的な集約関数¶

any_value(x) -> [入力と同じ]()¶: これはarbitrary()のエイリアスです。

arbitrary(x) -> [入力と同じ]()¶: 非nullのxの値が存在する場合は、任意の値のいずれかを返します。

array_agg(x) -> array<[入力と同じ]>()¶: 入力されたx要素から作成された配列を返します。

avg(x) -> double()¶: すべての入力値の平均（算術平均）を返します。

avg(時間間隔型) -> 時間間隔型()¶: すべての入力値の平均間隔長を返します。

bool_and(boolean) -> boolean()¶: すべての入力値がTRUEの場合、TRUEを返し、それ以外の場合はFALSEを返します。

bool_or(boolean) -> boolean()¶: いずれかの入力値がTRUEの場合、TRUEを返し、それ以外の場合はFALSEを返します。

checksum(x) -> varbinary()¶: 指定された値の順序に依存しないチェックサムを返します。

count(*) -> bigint()¶: 入力行の数を返します。

count(x) -> bigint()¶: NULLでない入力値の数を返します。

count_if(x) -> bigint()¶: TRUEの入力値の数を返します。この関数は、count(CASE WHEN x THEN 1 END)と同等です。

every(boolean) -> boolean()¶: これはbool_and()のエイリアスです。

geometric_mean(bigint) -> double()¶
geometric_mean(double) -> double()¶
geometric_mean(real) -> real()¶: すべての入力値の幾何平均を返します。

max_by(x, y) -> [x と同じ]()¶: すべての入力値のうち、yの最大値に関連付けられたxの値を返します。

max_by(x, y, n) -> array<[x と同じ]>()¶: yのすべての入力値のうち、大きい方からn番目に関連付けられたxの値をn個返します。yの降順で返されます。

min_by(x, y) -> [x と同じ]()¶: すべての入力値のうち、yの最小値に関連付けられたxの値を返します。

min_by(x, y, n) -> array<[x と同じ]>()¶: yのすべての入力値のうち、小さい方からn番目に関連付けられたxの値をn個返します。yの昇順で返されます。

max(x) -> [入力と同じ]()¶: すべての入力値の最大値を返します。

max(x, n) -> array<[x と同じ]>()¶: xのすべての入力値のうち、大きい方からn個の値を返します。

min(x) -> [入力と同じ]()¶: すべての入力値の最小値を返します。

min(x, n) -> array<[x と同じ]>()¶: xのすべての入力値のうち、小さい方からn個の値を返します。

reduce_agg(inputValue T, initialState S, inputFunction(S,T,S), combineFunction(S,S,S)) -> S()¶

すべての入力値を単一の値に縮小します。inputFunctionは、各入力値に対して呼び出されます。inputFunctionは、入力値を受け取るだけでなく、現在の状態（初期状態はinitialState）を受け取り、新しい状態を返します。combineFunctionは、2つの状態を結合して新しい状態を作成するために呼び出されます。最終的な状態が返されます。initialStateがNULLの場合、エラーがスローされます。inputFunctionまたはcombineFunctionがNULLを返した場合、動作は未定義です。

initialState、inputFunction、およびcombineFunctionを設計する際には注意が必要です。これらは、多数のノードでの部分集計を使用し、次にグループ化キーを介したシャッフル、次に最終集計を行うことで、分散方式で集計を評価することをサポートする必要があります。状態のすべての可能な値を考慮して、combineFunctionが可換であり、結合的な演算であり、initialStateが単位元であることを確認してください。

任意のsに対して、combineFunction(s, initialState) = s

任意のs1とs2に対して、combineFunction(s1, s2) = combineFunction(s2, s1)

任意のs1、s2、s3に対して、combineFunction(s1, combineFunction(s2, s3)) = combineFunction(combineFunction(s1, s2), s3)

さらに、inputFunctionについては以下が成り立つことを確認してください。

任意のxとyに対して、inputFunction(inputFunction(initialState, x), y) = combineFunction(inputFunction(initialState, x), inputFunction(initialState, y))

SELECT id, reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b)
FROM (
    VALUES
        (1, 2),
        (1, 3),
        (1, 4),
        (2, 20),
        (2, 30),
        (2, 40)
) AS t(id, value)
GROUP BY id;
-- (1, 9)
-- (2, 90)

SELECT id, reduce_agg(value, 1, (a, b) -> a * b, (a, b) -> a * b)
FROM (
    VALUES
        (1, 2),
        (1, 3),
        (1, 4),
        (2, 20),
        (2, 30),
        (2, 40)
) AS t(id, value)
GROUP BY id;
-- (1, 24)
-- (2, 24000)

状態の型は、ブール型、整数型、浮動小数点型、または日付/時刻/間隔型である必要があります。

set_agg(x) -> array<[入力と同じ]>()¶

個別の入力x要素から作成された配列を返します。

入力にNULLが含まれている場合、NULLは返される配列に含まれます。入力にNULL要素を持つ配列や、NULLフィールドを持つ行が含まれている場合、それらは返される配列に含まれます。この関数は、個別性を判断するためにIS DISTINCT FROMを使用します。

SELECT set_agg(x) FROM (VALUES(1), (2), (null), (2), (null)) t(x) -- ARRAY[1, 2, null]
SELECT set_agg(x) FROM (VALUES(ROW(ROW(1, null))), ROW((ROW(2, 'a'))), ROW((ROW(1, null))), (null)) t(x) -- ARRAY[ROW(1, null), ROW(2, 'a'), null]

set_union(array(T)) -> array(T)¶

入力された各配列に含まれるすべての異なる値の配列を返します。

すべての入力がNULLの場合、この関数は空の配列を返します。NULLが入力配列のいずれかの要素である場合、NULLが返される配列に含まれます。入力にNULL要素を持つ配列や、NULLフィールドを持つ行が含まれている場合、それらは返される配列に含まれます。この関数は、異なる値の判定にIS DISTINCT FROMを使用します。

例

SELECT set_union(elements)
FROM (
    VALUES
        ARRAY[1, 2, 3],
        ARRAY[2, 3, 4]
) AS t(elements);

ARRAY[1, 2, 3, 4]を返します。

sum(x) -> [入力と同じ]()¶: すべての入力値の合計を返します。

ビット単位集計関数¶

bitwise_and_agg(x) -> bigint()¶: 2の補数表現におけるすべての入力値のビット単位ANDを返します。

bitwise_or_agg(x) -> bigint()¶: 2の補数表現におけるすべての入力値のビット単位ORを返します。

bitwise_xor_agg(x) -> bigint()¶: 2の補数表現におけるすべての入力値のビット単位XORを返します。

マップ集計関数¶

histogram(x) -> map(K, bigint)¶: 各入力値が出現した回数のカウントを含むマップを返します。

map_agg(key, value) -> map(K, V)¶: 入力されたkey / valueペアから作成されたマップを返します。

map_union(x(K, V)) -> map(K, V)¶: すべての入力マップの和集合を返します。キーが複数の入力マップで見つかった場合、結果のマップにおけるそのキーの値は、任意の入力マップからの値になります。

map_union_sum(x(K, V)) -> map(K, V)¶: すべての入力マップの和集合を返し、すべてのマップで一致するキーの値を合計します。元のマップのすべてのnull値は0にまとめられます。

multimap_agg(key, value) -> map(K, array(V))¶: 入力されたkey / valueペアから作成されたマルチマップを返します。各キーは複数の値に関連付けることができます。

近似集計関数¶

approx_distinct(x) -> bigint()¶

入力値の概算の異なる数を返します。この関数はcount(DISTINCT x)の近似値を提供します。すべての入力値がnullの場合、ゼロが返されます。

この関数は、すべての可能な集合に対する（ほぼ正規の）誤差分布の標準偏差である2.3％の標準誤差を生成する必要があります。特定の入力集合に対する誤差の上限を保証するものではありません。

approx_distinct(x, e) -> bigint()¶

入力値の概算の異なる数を返します。この関数はcount(DISTINCT x)の近似値を提供します。すべての入力値がnullの場合、ゼロが返されます。

この関数は、すべての可能な集合に対する（ほぼ正規の）誤差分布の標準偏差である、e以下の標準誤差を生成する必要があります。特定の入力集合に対する誤差の上限を保証するものではありません。この関数の現在の実装では、eが[0.0040625, 0.26000]の範囲にある必要があります。

approx_percentile(x, percentage) -> [xと同じ]()¶: xのすべての入力値について、指定されたpercentageにおけるおおよそのパーセンタイルを返します。percentageの値は0から1の間である必要があり、すべての入力行で一定である必要があります。

approx_percentile(x, percentage, accuracy) -> [xと同じ]()¶: approx_percentile(x, percentage)と同様ですが、最大ランクエラーがaccuracyになります。accuracyの値は0から1の間（排他的）である必要があり、すべての入力行で一定である必要があります。低い「accuracy」は実際には低い誤差閾値であり、したがってより正確であることに注意してください。デフォルトのaccuracyは0.01です。

approx_percentile(x, percentages) -> array<[xと同じ]>()¶: xのすべての入力値について、指定された各パーセンテージにおけるおおよそのパーセンタイルを返します。percentages配列の各要素は0から1の間である必要があり、配列はすべての入力行で一定である必要があります。

approx_percentile(x, percentages, accuracy) -> array<[xと同じ]>()¶: approx_percentile(x, percentages)と同様ですが、最大ランクエラーがaccuracyになります。

approx_percentile(x, w, percentage) -> [xと同じ]()¶: パーセンタイルセットにおける値xの事実上の複製回数である、項目ごとの重みwを使用して、パーセンテージpにおけるxのすべての入力値の概算の重み付きパーセンタイルを返します。重みは1以上の整数値である必要があります。pの値は0から1の間である必要があり、すべての入力行で一定である必要があります。

approx_percentile(x, w, percentage, accuracy) -> [xと同じ]()¶: approx_percentile(x, w, percentage)と同様ですが、最大ランクエラーがaccuracyになります。

approx_percentile(x, w, percentages) -> array<[xと同じ型]>()¶: 入力値 x のすべての値に対して、配列で指定された各パーセンテージにおける、項目ごとの重み w を使用した、近似的な重み付きパーセンタイルを返します。重みは1以上の整数値である必要があります。これは、パーセンタイルセットにおける値 x の実質的な複製回数です。配列の各要素は0から1の間である必要があり、配列はすべての入力行で一定である必要があります。

approx_percentile(x, w, percentages, accuracy) -> array<[xと同じ型]>()¶: approx_percentile(x, w, percentages) と同様ですが、最大ランク誤差が accuracy になります。

approx_set(x) -> HyperLogLog(): HyperLogLog関数を参照してください。

merge(x) -> HyperLogLog(): HyperLogLog関数を参照してください。

khyperloglog_agg(x) -> KHyperLogLog(): KHyperLogLog関数を参照してください。

merge(qdigest(T)) -> qdigest(T): Quantile Digest関数を参照してください。

qdigest_agg(x) -> qdigest<[xと同じ型]>(): Quantile Digest関数を参照してください。

qdigest_agg(x, w) -> qdigest<[xと同じ型]>(): Quantile Digest関数を参照してください。

qdigest_agg(x, w, accuracy) -> qdigest<[xと同じ型]>(): Quantile Digest関数を参照してください。

numeric_histogram(buckets, value, weight) -> map<double, double>()¶

項目ごとの重み weight を持つすべての value に対して、最大 buckets 個のバケット数で近似ヒストグラムを計算します。返されるマップのキーは、おおよそビンの中心であり、エントリはビンの合計重みです。このアルゴリズムは、[BenHaimTomTov2010] を大まかに基にしています。

buckets は bigint である必要があります。value と weight は数値である必要があります。

numeric_histogram(buckets, value) -> map<double, double>()¶: すべての value に対して、最大 buckets 個のバケット数で近似ヒストグラムを計算します。この関数は、項目ごとの重みが 1 である weight を受け取る numeric_histogram() のバリアントと同等です。この場合、返されるマップの合計重みは、ビン内の項目のカウントです。

統計集計関数¶

corr(y, x) -> double()¶: 入力値の相関係数を返します。

covar_pop(y, x) -> double()¶: 入力値の母共分散を返します。

covar_samp(y, x) -> double()¶: 入力値の標本共分散を返します。

entropy(c) -> double()¶

カウント入力値の対数2エントロピーを返します。

\[\mathrm{entropy}(c) = \sum_i \left[ {c_i \over \sum_j [c_j]} \log_2\left({\sum_j [c_j] \over c_i}\right) \right].\]

c は、負でない値の bigint 列である必要があります。

この関数は、NULL カウントを無視します。非NULL カウントの合計が0の場合、0を返します。

kurtosis(x) -> double()¶

すべての入力値の過剰尖度を返します。次の式を用いた不偏推定です。

\[\mathrm{kurtosis}(x) = {n(n+1) \over (n-1)(n-2)(n-3)} { \sum[(x_i-\mu)^4] \over \sigma^4} -3{ (n-1)^2 \over (n-2)(n-3) }\]

ここで、\(\mu\) は平均値、\(\sigma\) は標準偏差です。

regr_intercept(y, x) -> double()¶: 入力値の線形回帰切片を返します。y は従属値です。x は独立値です。

regr_slope(y, x) -> double()¶: 入力値の線形回帰傾斜を返します。y は従属値です。x は独立値です。

regr_avgx(y, x) -> double()¶: グループ内の独立値の平均を返します。y は従属値です。x は独立値です。

regr_avgy(y, x) -> double()¶: グループ内の従属値の平均を返します。y は従属値です。x は独立値です。

regr_count(y, x) -> double()¶: 入力値の非NULLペアの数を返します。y は従属値です。x は独立値です。

regr_r2(y, x) -> double()¶: 線形回帰の決定係数を返します。y は従属値です。x は独立値です。

regr_sxy(y, x) -> double()¶: グループ内の従属値と独立値の積の合計を返します。y は従属値です。x は独立値です。

regr_syy(y, x) -> double()¶: グループ内の従属値の二乗の合計を返します。y は従属値です。x は独立値です。

regr_sxx(y, x) -> double()¶: グループ内の独立値の二乗の合計を返します。y は従属値です。x は独立値です。

skewness(x) -> double()¶: すべての入力値の歪度を返します。

stddev(x) -> double()¶: これは、stddev_samp() のエイリアスです。

stddev_pop(x) -> double()¶: すべての入力値の母標準偏差を返します。

stddev_samp(x) -> double()¶: すべての入力値の標本標準偏差を返します。

variance(x) -> double()¶: これは var_samp() のエイリアスです。

var_pop(x) -> double()¶: すべての入力値の母分散を返します。

var_samp(x) -> double()¶: すべての入力値の標本分散を返します。

分類メトリクス集計関数¶

以下の各関数は、分類閾値の関数として、二値混同行列のメトリクスがどのように変化するかを測定します。これらは連携して使用することを目的としています。

たとえば、適合率-再現率曲線を求めるには、以下を使用します。

WITH
    recall_precision AS (
        SELECT
            CLASSIFICATION_RECALL(10000, correct, pred) AS recalls,
            CLASSIFICATION_PRECISION(10000, correct, pred) AS precisions
        FROM
           classification_dataset
    )
SELECT
    recall,
    precision
FROM
    recall_precision
CROSS JOIN UNNEST(recalls, precisions) AS t(recall, precision)

これらの値に対応する閾値を取得するには、以下を使用します。

WITH
    recall_precision AS (
        SELECT
            CLASSIFICATION_THRESHOLDS(10000, correct, pred) AS thresholds,
            CLASSIFICATION_RECALL(10000, correct, pred) AS recalls,
            CLASSIFICATION_PRECISION(10000, correct, pred) AS precisions
        FROM
           classification_dataset
    )
SELECT
    threshold,
    recall,
    precision
FROM
    recall_precision
CROSS JOIN UNNEST(thresholds, recalls, precisions) AS t(threshold, recall, precision)

ROC曲線を求めるには、以下を使用します。

WITH
    fallout_recall AS (
        SELECT
            CLASSIFICATION_FALLOUT(10000, correct, pred) AS fallouts,
            CLASSIFICATION_RECALL(10000, correct, pred) AS recalls
        FROM
           classification_dataset
    )
SELECT
    fallout
    recall,
FROM
    recall_fallout
CROSS JOIN UNNEST(fallouts, recalls) AS t(fallout, recall)

classification_miss_rate(buckets, y, x, weight) -> array<double>()¶

buckets 個までのバケットを使用して、ミス率を計算します。ミス率の値の配列を返します。

y はブール値の結果である必要があります。x は、0から1の範囲の予測値である必要があります。weight は、インスタンスの重みを示す非負の値である必要があります。

ミス率は、\(j\)番目のエントリが次の式で表されるシーケンスとして定義されます。

\[{ \sum_{i \;|\; x_i \leq t_j \bigwedge y_i = 1} \left[ w_i \right] \over \sum_{i \;|\; x_i \leq t_j \bigwedge y_i = 1} \left[ w_i \right] + \sum_{i \;|\; x_i > t_j \bigwedge y_i = 1} \left[ w_i \right] },\]

ここで、\(t_j\)は\(j\)番目に小さい閾値であり、\(y_i\)、\(x_i\)、および\(w_i\)は、それぞれy、x、およびweightの\(i\)番目のエントリです。

classification_miss_rate(buckets, y, x) -> array<double>()¶: この関数は、項目ごとの重みが 1 である、weight を取る classification_miss_rate() のバリアントと同等です。

classification_fall_out(buckets, y, x, weight) -> array<double>()¶

buckets 個までのバケットを使用して、フォールアウトを計算します。フォールアウト値の配列を返します。

フォールアウトは、\(j\)番目のエントリが次の式で表されるシーケンスとして定義されます。

\[{ \sum_{i \;|\; x_i > t_j \bigwedge y_i = 0} \left[ w_i \right] \over \sum_{i \;|\; y_i = 0} \left[ w_i \right] },\]

ここで、\(t_j\)は\(j\)番目に小さい閾値であり、\(y_i\)、\(x_i\)、および\(w_i\)は、それぞれy、x、およびweightの\(i\)番目のエントリです。

classification_fall_out(buckets, y, x) -> array<double>()¶: この関数は、項目ごとの重みが 1 である、weight を取る classification_fall_out() のバリアントと同等です。

classification_precision(buckets, y, x, weight) -> array<double>()¶

buckets 個までのバケットを使用して、適合率を計算します。適合率の値の配列を返します。

適合率は、\(j\)番目のエントリが次の式で表されるシーケンスとして定義されます。

\[{ \sum_{i \;|\; x_i > t_j \bigwedge y_i = 1} \left[ w_i \right] \over \sum_{i \;|\; x_i > t_j} \left[ w_i \right] },\]

ここで、\(t_j\)は\(j\)番目に小さい閾値であり、\(y_i\)、\(x_i\)、および\(w_i\)は、それぞれy、x、およびweightの\(i\)番目のエントリです。

classification_precision(buckets, y, x) -> array<double>()¶: この関数は、項目ごとの重みが 1 である、weight を取る classification_precision() のバリアントと同等です。

classification_recall(buckets, y, x, weight) -> array<double>()¶

buckets 個までのバケットを使用して、再現率を計算します。再現率の値の配列を返します。

再現率は、\(j\)番目のエントリが次の式で表されるシーケンスとして定義されます。

\[{ \sum_{i \;|\; x_i > t_j \bigwedge y_i = 1} \left[ w_i \right] \over \sum_{i \;|\; y_i = 1} \left[ w_i \right] },\]

ここで、\(t_j\)は\(j\)番目に小さい閾値であり、\(y_i\)、\(x_i\)、および\(w_i\)は、それぞれy、x、およびweightの\(i\)番目のエントリです。

classification_recall(buckets, y, x) -> array<double>()¶: この関数は、項目ごとの重みが 1 である、weight を取る classification_recall() のバリアントと同等です。

classification_thresholds(buckets, y, x) -> array<double>()¶

buckets 個までのバケットを使用して、閾値を計算します。閾値の値の配列を返します。

y はブール値の結果である必要があります。x は、0から1の範囲の予測値である必要があります。

閾値は、\(j\)番目のエントリが\(j\)番目に小さい閾値であるシーケンスとして定義されます。

微分エントロピー関数¶

以下の関数は、二値の微分エントロピーを近似します。つまり、確率変数\(x\)に対して、以下を近似します。

\[h(x) = - \int x \log_2\left(f(x)\right) dx,\]

ここで、\(f(x)\)は\(x\)の偏密度関数です。

differential_entropy(sample_size, x)¶

確率変数のサンプル結果から、近似的な対数2微分エントロピーを返します。この関数は内部的にリザーバーを作成し（[Black2015]を参照）、次に累積分布の導関数を近似することにより、サンプル結果からエントロピーを計算します（[Alizadeh2010]を参照）。

sample_size（long）は、リザーバーサンプルの最大数です。

x（double）はサンプルです。

たとえば、1000000のリザーバーサンプルを使用して、dataのxの微分エントロピーを求めるには、以下を使用します。

SELECT
    differential_entropy(1000000, x)
FROM
    data

注意

\(x\)に既知の下限と上限がある場合は、(bucket_count, x, 1.0, "fixed_histogram_mle", min, max)または(bucket_count, x, 1.0, "fixed_histogram_jacknife", min, max)をとるバージョンを使用してください。これらは収束が優れています。

differential_entropy(sample_size, x, weight)¶

ランダム変数のサンプル結果から、近似的な対数2微分エントロピーを返します。この関数は内部で重み付きリザーバー（[Efraimidis2006]を参照）を作成し、累積分布の微分を近似することによりサンプル結果からエントロピーを計算します（[Alizadeh2010]を参照）。

sample_size は、リザーバーサンプルの最大数です。

x（double）はサンプルです。

weight （double）は、サンプルの重みを示す非負のdouble値です。

たとえば、1000000個のリザーバーサンプルを使用して、x の微分エントロピーを、data の重み weight で求めるには、次のようにします。

SELECT
    differential_entropy(1000000, x, weight)
FROM
    data

注意

\(x\) に既知の下限と上限がある場合は、より収束が良い (bucket_count, x, weight, "fixed_histogram_mle", min, max) または (bucket_count, x, weight, "fixed_histogram_jacknife", min, max) を使用することをお勧めします。

differential_entropy(bucket_count, x, weight, method, min, max) -> double()¶

ランダム変数のサンプル結果から、近似的な対数2微分エントロピーを返します。この関数は内部でサンプル値の概念的なヒストグラムを作成し、度数を計算し、次に method パラメータに基づいて、最尤法を使用して、ジャックナイフ補正ありまたはなしでエントロピーを近似します。ジャックナイフ補正（[Beirlant2001]を参照）を使用する場合、推定値は

\[n H(x) - (n - 1) \sum_{i = 1}^n H\left(x_{(i)}\right)\]

ここで、\(n\) はシーケンスの長さであり、\(x_{(i)}\) は \(i\) 番目の要素が削除されたシーケンスです。

bucket_count （long）は、ヒストグラムのバケット数を決定します。

x（double）はサンプルです。

method （varchar）は、'fixed_histogram_mle' （最尤推定用）または 'fixed_histogram_jacknife' （ジャックナイフ補正付き最尤推定用）のいずれかです。

min と max （両方とも double）は、それぞれ最小値と最大値です。この範囲外の入力があると、関数はエラーをスローします。

weight （double）は、サンプルの重みであり、非負でなければなりません。

たとえば、x の微分エントロピーを、それぞれ 0.0 と 1.0 の間で、1000000個のビンとジャックナイフ推定を使用して、data の重み 1.0 で求めるには、次のようにします。

SELECT
    differential_entropy(1000000, x, 1.0, 'fixed_histogram_jacknife', 0.0, 1.0)
FROM
    data

たとえば、x の微分エントロピーを、それぞれ -2.0 と 2.0 の間で、1000000個のバケットと最尤推定を使用して、data の重み weight で求めるには、次のようにします。

SELECT
    differential_entropy(1000000, x, weight, 'fixed_histogram_mle', -2.0, 2.0)
FROM
    data

注意

\(x\) に既知の下限と上限がない場合は、(sample_size, x) (重みなしの場合) または (sample_size, x, weight) (重み付きの場合) を使用することをお勧めします。これらは、サンプルに既知の範囲を必要としないリザーバーサンプリングを使用しているためです。

それ以外の場合、特にサンプルの数が少ない場合、異なる重みの数が少ない場合は、(bucket_count, x, weight, "fixed_histogram_jacknife", min, max) を使用することを検討してください。ジャックナイフバイアス補正は最尤推定よりも優れているためです。ただし、異なる重みの数が多い場合は、(bucket_count, x, weight, "fixed_histogram_mle", min, max) を使用することを検討してください。これにより、メモリと実行時間が削減されます。

approx_most_frequent(buckets, value, capacity) -> map<[same as value], bigint>()¶

頻繁な値の上位 buckets 要素までを概算で計算します。関数の概算推定により、少ないメモリで頻繁な値を拾い上げることができます。 capacity が大きいほど、メモリ容量を犠牲にして、基になるアルゴリズムの精度が向上します。返される値は、推定頻度に対応する上位要素を含むマップです。

関数の誤差は、値の順列とそのカーディナリティに依存します。最小の誤差を達成するために、基になるデータのカーディナリティと同じ容量を設定できます。

buckets と capacity は bigint でなければなりません。value は、数値または文字列型にすることができます。

この関数は、A.Metwally、D.Agrawal、A.Abbadiによる論文Efficient computation of frequent and top-k elements in data streamsで提案されているストリームサマリーデータ構造を使用します。

リザーバーサンプル関数¶

リザーバーサンプル関数は、TABLESAMPLEとは対照的に、固定サンプルサイズを使用します。固定サンプルサイズは常に固定合計サイズをもたらしますが、データセット内の各レコードが選択される確率が等しいことを保証します。[Vitter1985]を参照してください。

reservoir_sample(initial_sample: array(T), initial_processed_count: bigint, values_to_sample: T, desired_sample_size: int) -> row(processed_count: bigint, sample: array(T))¶

与えられた新しいリザーバーサンプルを計算します

initial_sample: 初期サンプル配列。または、新しいサンプルを作成する場合は NULL。
initial_processed_count: 初期サンプル配列を生成するために処理されたレコード数。 initital_sample が NULL の場合は、0 または NULL にする必要があります。
values_to_sample: サンプリングする列。
desired_sample_size: リザーバーサンプルのサイズ。

この関数は、2つの列を持つ単一行タイプを出力します

処理済みカウント: 関数がサンプリングした行の合計数。これには、initial_processed_count が提供されている場合は、その合計が含まれます。
リザーバーサンプル: desired_sample_size の最小値と values_to_sample 引数の値の数に相当する長さの配列。

WITH result as (
    SELECT
        reservoir_sample(NULL, 0, col, 5) as reservoir
    FROM (
        VALUES
        1, 2, 3, 4, 5, 6, 7, 8, 9, 0
    ) as t(col)
)
SELECT
    reservoir.processed_count, reservoir.sample
FROM result;

 processed_count |     sample
-----------------+-----------------
              10 | [1, 2, 8, 4, 5]

以前のサンプルを新しいデータとマージするには、initial_sample 引数と initial_processed_count 引数に有効な引数を指定します。

WITH initial_sample as (
    SELECT
        reservoir_sample(NULL, 0, col, 3) as reservoir
    FROM (
        VALUES
        0, 1, 2, 3, 4
    ) as t(col)
),
new_sample as (
    SELECT
        reservoir_sample(
            (SELECT reservoir.sample FROM initial_sample),
            (SELECT reservoir.processed_count FROM initial_sample),
            col,
            3
        ) as result
    FROM (
        VALUES
        5, 6, 7, 8, 9
    ) as t(col)
)
SELECT
    result.processed_count, result.sample
FROM new_sample;

 processed_count |  sample
-----------------+-----------
              10 | [8, 3, 2]

テーブルの行全体をサンプリングするには、ソーステーブルの列に対応する各サブフィールドを含む ROW 型の入力を使用します。

WITH result as (
    SELECT
        reservoir_sample(NULL, 0, CAST(row(idx, val) AS row(idx int, val varchar)), 2) as reservoir
    FROM (
        VALUES
        (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')
    ) as t(idx, val)
)
SELECT
    reservoir.processed_count, reservoir.sample
FROM result;

 processed_count |              sample
-----------------+----------------------------------
               5 | [{idx=1, val=a}, {idx=5, val=e}]

ノイズの多い集計関数¶

ノイズの多い集計関数を参照してください。

[Alizadeh2010] (1,2)

Alizadeh Noughabi, Hadi & Arghami, N. (2010). “A New Estimator of Entropy”.

[Beirlant2001]

Beirlant, Dudewicz, Gyorfi, and van der Meulen, “Nonparametric entropy estimation: an overview”, (2001)

[BenHaimTomTov2010]

Yael Ben-Haim and Elad Tom-Tov, “A streaming parallel decision tree algorithm”, J. Machine Learning Research 11 (2010), pp. 849–872.

[Black2015]

Black, Paul E. (26 January 2015). “Reservoir sampling”. Dictionary of Algorithms and Data Structures.

[Efraimidis2006]

Efraimidis, Pavlos S.; Spirakis, Paul G. (2006-03-16). “Weighted random sampling with a reservoir”. Information Processing Letters. 97 (5): 181–185.

[Vitter1985]

Vitter, Jeffrey S. 「リザーバを用いたランダムサンプリング」 ACM Transactions on Mathematical Software (TOMS) 11.1 (1985): 37-57.