INTEGRITY Dokumentace

← R2 SQL / r2-sql / sql-reference

Agregační funkce

Agregační funkce sloučí více řádků do jediného výsledku. Používají se s GROUP BY k výpočtu souhrnů podle skupin, nebo bez GROUP BY k výpočtu jediného výsledku napříč všemi řádky.

Většina agregačních funkcí přijímá DISTINCT modifikátoru, například COUNT(DISTINCT customer_id) nebo SUM(DISTINCT total_amount).

Základní agregace

COUNT

Spočítá řádky. COUNT(*) počítá všechny řádky. COUNT(column) počítá hodnoty, které nejsou 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

Vrací součet hodnot ve sloupci.

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

Vrací průměr hodnot ve sloupci. Alias: 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

Vrací minimální hodnotu. Funguje pro číselné i textové sloupce.

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

Vrací maximální hodnotu. Funguje pro číselné i textové sloupce.

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

Vrací přesnou hodnotu mediánu. U velkých datových sad použijte approx_median místo toho.

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

Vrací přesnou hodnotu daného percentilu pomocí WITHIN GROUP (ORDER BY ...). Parametr percentilu musí být mezi 0.0 a 1.0 včetně. U velkých datových sad použijte approx_percentile_cont místo toho.

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

Přibližné agregace

Funkce pro přibližnou agregaci produkují statisticky odhadnuté výsledky a přitom využívají výrazně méně paměti a výpočetního výkonu než jejich přesné protějšky. Použijte je při analýze rozsáhlých datových sad, kdy je přibližný výsledek dostačující.

approx_percentile_cont

Vrací přibližnou hodnotu daného percentilu pomocí algoritmu T-Digest. Parametr percentilu musí být mezi 0.0 a 1.0 včetně.

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

Vrací přibližný vážený percentil. Řádky jsou váženy podle weight sloupec.

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

Vrací přibližný medián. Odpovídá funkci 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

Vrací přibližný počet unikátních hodnot pomocí algoritmu 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

Vrací k nejčastějších hodnot s jejich přibližnými počty.

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

Statistické agregace

var / var_samp

Vrací výběrový rozptyl.

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

Vrací rozptyl základního souboru.

SELECT var_pop(total_amount) AS pop_variance
FROM my_namespace.sales_data

stddev / stddev_samp

Vrací výběrovou směrodatnou odchylku.

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

Vrací směrodatnou odchylku základního souboru.

SELECT stddev_pop(total_amount) AS pop_std_dev
FROM my_namespace.sales_data

covar_samp

Vrací výběrovou kovarianci. Alias: 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

Vrací kovarianci základního souboru.

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

Vrací Pearsonův korelační koeficient mezi dvěma sloupci.

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

Vrací směrnici přímky lineární regrese.

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

Vrací úsek na ose y přímky lineární regrese.

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

Vrací počet dvojic bez hodnoty 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

Vrací koeficient determinace (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

Vrací průměr nezávislé proměnné (x) pro dvojice bez hodnoty NULL.

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

Vrací průměr závislé proměnné (y) pro dvojice bez hodnoty NULL.

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

Vrací součet čtverců nezávislé proměnné.

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

Vrací součet čtverců závislé proměnné.

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

Vrací součet součinů spárovaných proměnných.

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

Bitové agregace

bit_and

Vrací bitový AND všech hodnot ve skupině.

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

bit_or

Vrací bitový OR všech hodnot ve skupině.

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

bit_xor

Vrací bitový XOR všech hodnot ve skupině.

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

Booleovské agregace

bool_and

Vrací true, pokud jsou všechny hodnoty ve skupině 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

Vrací true, pokud je alespoň jedna hodnota ve skupině true.

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

Poziční agregace

first_value

Vrací první hodnotu ve skupině podle zadaného řazení.

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

Vrací poslední hodnotu ve skupině podle zadaného řazení.

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

Agregace kolekcí

Tyto agregace shromažďují všechny vstupní hodnoty do jednoho výsledku. Protože si pamatují každou hodnotu, použijte WHERE filtr nebo GROUP BY aby velikost skupin zůstala u velkých datových sad omezená.

array_agg

Shromáždí hodnoty ze skupiny do pole.

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

string_agg

Zřetězí hodnoty ze skupiny do jednoho řetězce odděleného zadaným oddělovačem.

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