COUNT

描述

返回指定列的非 NULL 记录数，或者记录总数。

语法

COUNT(DISTINCT <expr> [,<expr>,...])
COUNT(*)
COUNT(<expr>)

参数

参数	说明
<expr>	如果填写表达式，则计算非 NULL 的记录数，否则计算总行数。

返回值

返回值的类型为 Bigint。如果 expr 为 NULL，则不参数统计。

举例

-- setup
create table test_count(
    id int,
    name varchar(20),
    sex int
) distributed by hash(id) buckets 1
properties ("replication_num"="1");

insert into test_count values
    (1, '1', 1),
    (2, '2', 1),
    (3, '3', 1),
    (4, '0', 1),
    (4, '4', 1),
    (5, NULL, 1);

create table test_insert(
    id int,
    name varchar(20),
    sex int
) distributed by hash(id) buckets 1
properties ("replication_num"="1");

insert into test_insert values
    (1, '1', 1),
    (2, '2', 1),
    (3, '3', 1),
    (4, '0', 1),
    (4, '4', 1),
    (5, NULL, 1);

select count(*) from test_count;

+----------+
| count(*) |
+----------+
|        6 |
+----------+

select count(name) from test_insert;

+-------------+
| count(name) |
+-------------+
|           5 |
+-------------+

select count(distinct sex) from test_insert;

+---------------------+
| count(DISTINCT sex) |
+---------------------+
|                   1 |
+---------------------+

select count(distinct id,sex) from test_insert;

+-------------------------+
| count(DISTINCT id, sex) |
+-------------------------+
|                       5 |
+-------------------------+