Clickhouse 聚合函数

clickhouse的聚合函数包含了标准SQL的聚合函数,此外还有一些自有的聚合函数:

函数功能函数名标准OR自有函数别名
计数count  
求和sum  
求平均avg  
最大值max  
最小值min  
中位数medianquantile 
 any  
 anyHeavy  
 anyLast  
 VAR_POPvarPopvarPopStable
 VAR_SAMPvarSampvarSampStable
 STDDEV_POPstddevPopstddevPopStable
 STDDEV_SAMPstddevSampstddevSampStable
 COVAR_POPcovarPopcovarPopStable
 COVAR_SAMPcovarSampcovarSampStable
 corr corrStable
 argMin  
 argMax  
 avgWeighted  
 topK  
 topKWeighted  
 groupArray  
 groupUniqArray  
 groupArrayInsertAt  
 groupArrayMovingSum  
 groupArrayMovingAvg  
 groupArraySample  
 groupBitAnd  
 groupBitOr  
 groupBitXor  
 groupBitmap  
 groupBitmapAnd  
 groupBitmapOr  
 groupBitmapXor  
 sumWithOverflow  
 sumMap  
 minMap  
 maxMap  
 skewPop  
 skewSamp  
 kurtPop  
 kurtSamp  
 timeSeriesGroupSum  
 timeSeriesGroupRateSum  
 uniq  
 uniqCombined  
 uniqCombined64  
 uniqHLL12  
 uniqExact  

 特殊的聚合函数:

select  a.name, c.alias_to,b.alias like_name
from (
 select name from system.functions where name like 'quantile%' 
             and name not like 'quantiles%' ) a 
left join ( 
select name,concat(left(name,8),substring(name,10)) alias from system.functions 
where name like 'quantiles%' )  b 
  on a.name=b.alias
left join ( 
select  name,alias_to from system.functions where is_aggregate=1 
 and alias_to like 'quantile%') c 
  on a.name =c.alias_to
;


SELECT 
    a.name,
    c.alias_to,
    b.alias AS like_name
FROM 
(
    SELECT name
    FROM system.functions
    WHERE (name LIKE 'quantile%') AND (name NOT LIKE 'quantiles%')
) AS a
LEFT JOIN 
(
    SELECT 
        name,
        concat(substring(name, 1, 8), substring(name, 10)) AS alias
    FROM system.functions
    WHERE name LIKE 'quantiles%'
) AS b ON a.name = b.alias
LEFT JOIN 
(
    SELECT 
        name,
        alias_to
    FROM system.functions
    WHERE (is_aggregate = 1) AND (alias_to LIKE 'quantile%')
) AS c ON a.name = c.alias_to
ORDER BY 1 DESC

┌─a.name──────────────────┬─c.alias_to──────────────┬─like_name───────────────┐
│ quantileTiming          │ quantileTiming          │ quantileTiming          │
│ quantileTDigest         │ quantileTDigest         │ quantileTDigest         │
│ quantileTDigestWeighted │ quantileTDigestWeighted │ quantileTDigestWeighted │
│ quantileDeterministic   │ quantileDeterministic   │ quantileDeterministic   │
│ quantileTimingWeighted  │ quantileTimingWeighted  │ quantileTimingWeighted  │
│ quantileExactInclusive  │                         │ quantileExactInclusive  │
│ quantileExactExclusive  │                         │ quantileExactExclusive  │
│ quantileExactWeighted   │ quantileExactWeighted   │ quantileExactWeighted   │
│ quantileExact           │ quantileExact           │ quantileExact           │
│ quantile                │ quantile                │ quantile                │
└─────────────────────────┴─────────────────────────┴─────────────────────────┘

线性回归聚合函数:

Clickhouse> select * from system.functions where name like '%Reg%' and  is_aggregate=1;

SELECT *
FROM system.functions
WHERE (name LIKE '%Reg%') AND (is_aggregate = 1)

┌─name─────────────────────────┬─is_aggregate─┬─case_insensitive─┬─alias_to─┐
│ stochasticLogisticRegression │            1 │                0 │          │
│ stochasticLinearRegression   │            1 │                0 │          │
│ simpleLinearRegression       │            1 │                0 │          │
└──────────────────────────────┴──────────────┴──────────────────┴──────────┘

3 rows in set. Elapsed: 0.003 sec.

聚合函数的合并器(Aggregate Function Combinators):

-If
-Array
-State
-Merge
-MergeState
-ForEach
-OrDefault
-OrNull
-Resample

注意:
-ArrayIf
-State适用的场景:
AggregatingMergeTree table engine.
finalizeAggregation function.
runningAccumulate function.
-Merge combinator.
-MergeState combinator.

参考:

https://clickhouse.tech/docs/en/sql-reference/aggregate-functions/reference/count/

https://clickhouse.tech/docs/en/sql-reference/aggregate-functions/combinators/

https://clickhouse.tech/docs/en/sql-reference/aggregate-functions/parametric-functions/

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页