nunique

nunique(X, [ignoreNull=false])

If X is a vector/array vector, return the number of unique elements in X.

If X is a tuple, elements of each vector at the same position forms a key, and this function returns the number of unique keys.

Note: In SQL-92, COUNT(DISTINCT ...) is commonly used to count unique values. DolphinDB does not currently support this syntax. Use nunique(...) as an equivalent alternative. That is, nunique(...) in DolphinDB is equivalent to COUNT(DISTINCT ...) in SQL-92. See the examples below for details.

X is a vector/array vector or a tuple composed of multiple vectors with same length.

ignoreNull (optional) is a Boolean value. If set to true, only non-null elements will be included in the calculation. The default value is false. Note that if X is a tuple or array vector, ignoreNull must be set to false.

An INT scalar.

v = [1,3,1,-6,NULL,2,NULL,1];
nunique(v);
// output: 5

// set ignoreNull=true
nunique(v,true);
// output: 4

a = array(INT[], 0, 10).append!([1 2 3, 3 5, 6 8 8, 9 10])
nunique(a)
// output: 8

t=table(1 2 4 8 4 2 7 1 as id, 10 20 40 80 40 20 70 10 as val);
select nunique([id,val]) from t;

dbName = "dfs://testdb"
if(existsDatabase(dbName)){
   dropDatabase(dbName)
}

db=database("dfs://testdb", VALUE, 2012.01.11..2012.01.29)

n=100
t=table(take(2012.01.11..2012.01.29, n) as date, symbol(take("A"+string(21..60), n)) as sym, take(100, n) as val)

pt=db.createPartitionedTable(t, `pt, `date).append!(t)
select nunique(date) from pt group by sym

dbName = "dfs://testdb"
if(existsDatabase(dbName)){
dropDatabase(dbName)
}

db=database("dfs://testdb", VALUE, 2012.01.11..2012.01.29)

n=100
t=table(take(2012.01.11..2012.01.29, n) as date, symbol(take("A"+string(21..60), n)) as sym, take(100, n) as val)

pt=db.createPartitionedTable(t, `pt, `date).append!(t)
select nunique(date) from pt group by sym

The following examples demonstrate how to count unique users in MySQL and DolphinDB, respectively.

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    product_id INT,
    order_date DATE
);

INSERT INTO orders VALUES
(1, 1001, 101, '2026-01-01'),
(2, 1002, 101, '2026-01-02'),
(3, 1001, 102, '2026-01-03'),
(4, 1003, 101, '2026-01-04'),
(5, 1002, 102, '2026-01-05'),
(6, 1004, 103, '2026-01-06'),
(7, 1001, 101, '2026-01-07');

SELECT count(distinct user_id) AS unique_users
FROM orders;

CREATE TABLE orders (
    order_id INT,
    user_id INT,
    product_id INT,
    order_date DATE
);

INSERT INTO orders VALUES(1, 1001, 101, '2026-01-01')
INSERT INTO orders VALUES(2, 1002, 101, '2026-01-02')
INSERT INTO orders VALUES(3, 1001, 102, '2026-01-03')
INSERT INTO orders VALUES(4, 1003, 101, '2026-01-04')
INSERT INTO orders VALUES(5, 1002, 102, '2026-01-05')
INSERT INTO orders VALUES(6, 1004, 103, '2026-01-06')
INSERT INTO orders VALUES(7, 1001, 101, '2026-01-07')

select nunique(user_id) FROM orders;