Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/approx_percentile_combine below:

Website Navigation

• Reference
• Function and stored procedure reference
• Aggregate

APPROX_PERCENTILE_COMBINE | Snowflake Documentation

Categories:

Aggregate functions (Percentile Estimation) , Window function syntax and usage

Combines (merges) percentile input states into a single output state.

This allows scenarios where APPROX_PERCENTILE_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using APPROX_PERCENTILE_COMBINE, producing the same output state as a single run of APPROX_PERCENTILE_ACCUMULATE over the entire table.

See also:

APPROX_PERCENTILE_ACCUMULATE , APPROX_PERCENTILE_ESTIMATE

APPROX_PERCENTILE_COMBINE( <state> )

Copy

state

An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE.

Return an approximation for the median of numbers in the testtable.c2 column (0.5 means the 50th percentile):

CREATE OR REPLACE TABLE mytesttable AS
  SELECT APPROX_PERCENTILE_COMBINE(td) s FROM
    (
      (SELECT APPROX_PERCENTILE_ACCUMULATE(c2) td FROM testtable WHERE c2 <= 0)
        UNION ALL
      (SELECT APPROX_PERCENTILE_ACCUMULATE(c2) td FROM testtable WHERE c2 > 0 AND c2 <= 0.5)
        UNION ALL
      (SELECT APPROX_PERCENTILE_ACCUMULATE(C2) td FROM testtable WHERE c2 > 0.5)
    );

SELECT APPROX_PERCENTILE_ESTIMATE(s , 0.5) FROM mytesttable;

Copy

Return an approximate value for the 2nd percentile of numbers in mytest.s1 union mytest2.s2.

CREATE OR REPLACE TABLE mytest AS (SELECT APPROX_PERCENTILE_ACCUMULATE(c2) s1 FROM testtable WHERE c2 < 0);

CREATE OR REPLACE TABLE mytest2 AS (SELECT APPROX_PERCENTILE_ACCUMULATE(c2) s1 FROM testtable WHERE c2 >= 0);

CREATE OR REPLACE TABLE combinedtable AS
  SELECT APPROX_PERCENTILE_COMBINE(s) combinedstate FROM
    (
      (SELECT s1 s FROM mytest)
        UNION ALL
      (SELECT s1 s FROM mytest2)
    );

SELECT APPROX_PERCENTILE_ESTIMATE(combinedstate , 0.02) FROM combinedtable;

Copy

For a more extensive example, see the Examples section in APPROX_PERCENTILE_ACCUMULATE.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4