COUNT_DISTINCT
Counts the number of distinct values that appear in a field.
COUNT_DISTINCT(VALUE)VALUE: The field you want to count. An integer
Duplicate field values are not counted.
For example: for values: "a, b, c, c, c, b" the distinct count would be 3, because the values "b" and "c" repeat and only get counted once.
NULLs are not counted.
[
{
"GROUP_ID":"G1",
"USER_ID":"U1",
"CONNECTION_TIME":"2020-06-26 02:31:29,573"
},
{
"GROUP_ID":"G1",
"USER_ID":"U2",
"CONNECTION_TIME":"2020-06-26 18:11:45,783"
},
{
"GROUP_ID":"G2",
"USER_ID":"Z1",
"CONNECTION_TIME":"2020-06-26 23:54:27,687"
},
{
"GROUP_ID":"G2",
"CONNECTION_TIME":"2020-07-26 23:54:27,687"
},
{
"GROUP_ID":"G1",
"USER_ID":"U2",
"CONNECTION_TIME":"2021-07-01 02:31:29,573"
},
{
"GROUP_ID":"G1",
"USER_ID":"U1",
"CONNECTION_TIME":"2021-07-01 18:11:45,783"
},
{
"GROUP_ID":"G2",
"USER_ID":"Z1",
"CONNECTION_TIME":"2021-07-01 23:54:27,687"
}
]
Count the number of distinct entries for each unique entry in a field:
SELECT
COUNT(DISTINCT data.USER_ID) AS count_distinct_data_user_id:BIGINT,
data.GROUP_ID AS group_id:STRING
FROM
"SAMPLE_DATA_G1U1 - json"
GROUP BY
data.GROUP_ID
{
"count_distinct_data_user_id":1,
"group_id":"G2"
}{
"count_distinct_data_user_id":2,
"group_id":"G1"
}
Last modified 1yr ago