/
count.sql
40 lines (34 loc) · 1.32 KB
/
count.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Test data.
CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
(1, 1), (1, 2), (2, 1), (1, 1), (null, 2), (1, null), (null, null)
AS testData(a, b);
-- count with single expression
SELECT
count(*), count(1), count(null), count(a), count(b), count(a + b), count((a, b))
FROM testData;
-- distinct count with single expression
SELECT
count(DISTINCT 1),
count(DISTINCT null),
count(DISTINCT a),
count(DISTINCT b),
count(DISTINCT (a + b)),
count(DISTINCT (a, b))
FROM testData;
-- count with multiple expressions
SELECT count(a, b), count(b, a), count(testData.*) FROM testData;
-- distinct count with multiple expressions
SELECT
count(DISTINCT a, b), count(DISTINCT b, a), count(DISTINCT *), count(DISTINCT testData.*)
FROM testData;
-- distinct count with multiple literals
SELECT count(DISTINCT 3,2);
SELECT count(DISTINCT 2), count(DISTINCT 2,3);
SELECT count(DISTINCT 2), count(DISTINCT 3,2);
SELECT count(DISTINCT a), count(DISTINCT 2,3) FROM testData;
SELECT count(DISTINCT a), count(DISTINCT 3,2) FROM testData;
SELECT count(DISTINCT a), count(DISTINCT 2), count(DISTINCT 2,3) FROM testData;
SELECT count(DISTINCT a), count(DISTINCT 2), count(DISTINCT 3,2) FROM testData;
SELECT count(distinct 0.8), percentile_approx(distinct a, 0.8) FROM testData;
-- count without expressions
SELECT count() FROM testData;