tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Stream Compute Service
    Documentation
    Download PDF

    Aggregate Functions

    Last updated: 2023-11-08 14:16:28
    Download PDF

    Functions

    Function
    Description
    COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*)
    By default or with ALL, returns the number of input rows for which expression is not NULL. Use DISTINCT for one unique instance of each value.
    COUNT(*) COUNT(1)
    Returns the number of input rows (including those for which expression is NULL).
    AVG([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the average (arithmetic mean) of expression across all input rows. Use DISTINCT for one unique instance of each value.
    SUM([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the sum of expression across all input rows. Use DISTINCT for one unique instance of each value.
    MAX([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the maximum value of expression across all input rows. Use DISTINCT for one unique instance of each value.
    MIN([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the minimum value of expression across all input rows. Use DISTINCT for one unique instance of each value.
    STDDEV_POP([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the population standard deviation of expression across all input rows. Use DISTINCT for one unique instance of each value.
    STDDEV_SAMP([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the sample standard deviation of expression across all input rows. Use DISTINCT for one unique instance of each value.
    VAR_POP([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the population variance of expression across all input rows. Use DISTINCT for one unique instance of each value.
    VAR_SAMP([ ALL | DISTINCT ] expression) VARIANCE([ ALL | DISTINCT ] expression)
    By default or with ALL, returns the sample variance of expression across all input rows. Use DISTINCT for one unique instance of each value. The two expressions are equivalent.
    COLLECT([ ALL | DISTINCT ] expression)
    By default or with ALL, returns a multiset of expression across all input rows. NULL values will be ignored, and duplicate values are allowed. If all values are NULL, an empty set is returned.
    RANK()
    Returns the rank of a value in a group of values. The values may produce gaps in the sequence. For example, if there are five values, two of which rank the second place, the result of RANK() will be 1, 2, 2, 4, 5.
    DENSE_RANK()
    Returns the rank of a value in a group of values. The values will not produce gaps in the sequence. For example, if there are five values, two of which rank the second place, the result of RANK() will be 1, 2, 2, 3, 4.
    ROW_NUMBER()
    Assigns a unique, sequential number to each row, starting from 1. For example, if there are five values, two of which rank the second place, the result of RANK() will be 1, 2, 3, 4, 5.
    LEAD(expression [, offset] [, default] )
    Returns the value of expression at the offsetth row after the current row in the window. The default value of offset is 1 and the default value of default is NULL.
    LAG(expression [, offset] [, default])
    Returns the value of expression at the offsetth row before the current row in the window. The default value of offset is 1 and the default value of default is NULL.
    FIRST_VALUE(expression)
    Returns the first value in a set of values.
    LAST_VALUE(expression)
    Returns the last value in a set of values.
    LISTAGG(expression [, separator])
    Concatenates the values of string expressions and places separator values between them. The default value of separator is ,. This function is similar to the String.join() method in other programming languages.

    Examples

    For better illustration, an example test data table Test is created as follows:
    id
    site_id
    count
    date
    1
    1
    45
    2021-07-10
    2
    3
    100
    2021-07-13
    3
    1
    230
    2021-07-14
    4
    2
    10
    2021-07-14
    5
    5
    205
    2021-07-14
    6
    4
    13
    2021-07-15
    7
    3
    220
    2021-07-15
    8
    5
    545
    2021-07-16
    9
    3
    201
    2021-07-17

    COUNT

    Feature description: Counts the total number of rows. Syntax: COUNT([ ALL ] expression | DISTINCT expression1 [, expression2] * ) Example statement: SELECT COUNT(*) FROM Test; Test data and result:
    Test Statement
    Test Result (nums)
    SELECT COUNT(*) AS nums FROM Test;
    9
    SELECT COUNT(DISTINCT site_id) AS nums FROM Test;
    5

    AVG

    Feature description: Returns the average value. Syntax: AVG([ ALL | DISTINCT ] expression) Example statement: SELECT AVG(count) FROM Test; Test data and result:
    Test Statement
    Test Result (nums)
    SELECT AVG(count) AS nums FROM Test;
    176.3

    SUM

    Feature description: Returns the sum. Syntax: SUM([ ALL | DISTINCT ] expression) Example statement: SELECT SUM(count) FROM Test; Test data and result:
    Test Statement
    Test Result (nums)
    SELECT SUM(count) AS nums FROM Test;
    1569

    MAX

    Feature description: Returns the maximum value. Syntax: MAX([ ALL | DISTINCT ] expression) Example statement: SELECT MAX(count) FROM Test; Test data and result:
    Test Statement
    Test Result (nums)
    SELECT MAX(count) AS nums FROM Test;
    545

    MIN

    Feature description: Returns the minimum value. Syntax: MIN([ ALL | DISTINCT ] expression) Example statement: SELECT MIN(count) FROM Test; Test data and result:
    Test Statement
    Test Result (nums)
    SELECT MIN(count) AS nums FROM Test;
    13

    STDDEV_POP

    Feature description: Returns the population standard deviation. Syntax: STDDEV_POP([ ALL | DISTINCT ] expression) Example statement: SELECT STDDEV_POP(count) FROM Test; Test data and result:
    Test Statement
    Test Result (pop)
    SELECT STDDEV_POP(count) AS pop FROM Test;
    156.18

    VAR_POP

    Feature description: Returns the population variance. Syntax: VAR_POP([ ALL | DISTINCT ] expression) Example statement: SELECT VAR_POP(count) FROM Test; Test data and result:
    Test Statement
    Test Result (pop)
    SELECT VAR_POP(count) AS pop FROM Test;
    24390.7

    FIRST_VALUE

    Feature description: Returns the first value in a set of values. Syntax: FIRST_VALUE(expression) Example statement: SELECT FIRST_VALUE(count) FROM Test; Test data and result:
    Test Statement
    Test Result (first)
    SELECT FIRST_VALUE(count) AS first FROM Test;
    45

    LAST_VALUE

    Feature description: Returns the last value in a set of values. Syntax: LAST_VALUE(expression) Example statement: SELECT LAST_VALUE(count) FROM Test; Test data and result:
    Test Statement
    Test Result (last)
    SELECT LAST_VALUE(count) AS last FROM Test;
    201
    
    Contact Us

    Contact our sales team or business advisors to help your business.

    Contact Us
    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    Submit a Ticket
    7x24 Phone Support
    Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
    Privacy PolicyLegalCookie Policy