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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
# name: test/sql/function/list/aggregates/histogram.test
# description: Test list_histogram aggregate function
# group: [aggregates]
statement ok
CREATE TABLE const AS SELECT LIST(2) AS i FROM range(200) t1(i)
query I
SELECT list_histogram(i) FROM const
----
{2=200}
query I
select list_histogram([1])
----
{1=1}
# integers
statement ok
CREATE TABLE hist_data (g INTEGER[])
statement ok
INSERT INTO hist_data VALUES ([1, 1, 2, 2, 2, 3, 5]), ([1, 2, 3, 4, 5, 6, NULL]), ([]), (NULL), ([NULL]);
query T
SELECT list_histogram(g) from hist_data
----
{1=2, 2=3, 3=1, 5=1}
{1=1, 2=1, 3=1, 4=1, 5=1, 6=1}
NULL
NULL
NULL
# strings
statement ok
create table names (name string[])
statement ok
insert into names values (['pedro', 'pedro', 'pedro', 'hannes', 'hannes', 'mark', NULL, 'Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr.']);
query I
select list_histogram(name) from names;
----
{Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr.=1, hannes=2, mark=1, pedro=3}
# incorrect usage
statement error
select list_histogram()
----
# temporal types
# timestamp
query I
SELECT list_histogram(['2021-08-20'::TIMESTAMP])
----
{'2021-08-20 00:00:00'=1}
# timestamp_s
query I
SELECT list_histogram(['2021-08-20'::TIMESTAMP_S])
----
{'2021-08-20 00:00:00'=1}
# timestamp_ms
query I
SELECT list_histogram(['2021-08-20'::TIMESTAMP_MS])
----
{'2021-08-20 00:00:00'=1}
# timestamp_ns
query I
SELECT list_histogram(['2021-08-20'::TIMESTAMP_NS])
----
{'2021-08-20 00:00:00'=1}
# timestamp with time zone
query I
SELECT list_histogram(['2021-08-20'::TIMESTAMPTZ])
----
{'2021-08-20 00:00:00+00'=1}
# date
query I
SELECT list_histogram(['2022-01-02'::DATE])
----
{2022-01-02=1}
# time
query I
SELECT list_histogram(['15:00:07'::TIME])
----
{'15:00:07'=1}
# timetz
query I
SELECT list_histogram(['15:00:07'::TIMETZ])
----
{'15:00:07+00'=1}
# interval
query I
SELECT list_histogram([INTERVAL 1 YEAR])
----
{1 year=1}
|
