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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 15
-- !query 0
CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
(1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2), (null, 1), (3, null), (null, null)
AS testData(a, b)
-- !query 0 schema
struct<>
-- !query 0 output
-- !query 1
SELECT a, COUNT(b) FROM testData
-- !query 1 schema
struct<>
-- !query 1 output
org.apache.spark.sql.AnalysisException
grouping expressions sequence is empty, and 'testdata.`a`' is not an aggregate function. Wrap '(count(testdata.`b`) AS `count(b)`)' in windowing function(s) or wrap 'testdata.`a`' in first() (or first_value) if you don't care which value you get.;
-- !query 2
SELECT COUNT(a), COUNT(b) FROM testData
-- !query 2 schema
struct<count(a):bigint,count(b):bigint>
-- !query 2 output
7 7
-- !query 3
SELECT a, COUNT(b) FROM testData GROUP BY a
-- !query 3 schema
struct<a:int,count(b):bigint>
-- !query 3 output
1 2
2 2
3 2
NULL 1
-- !query 4
SELECT a, COUNT(b) FROM testData GROUP BY b
-- !query 4 schema
struct<>
-- !query 4 output
org.apache.spark.sql.AnalysisException
expression 'testdata.`a`' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;
-- !query 5
SELECT COUNT(a), COUNT(b) FROM testData GROUP BY a
-- !query 5 schema
struct<count(a):bigint,count(b):bigint>
-- !query 5 output
0 1
2 2
2 2
3 2
-- !query 6
SELECT 'foo', COUNT(a) FROM testData GROUP BY 1
-- !query 6 schema
struct<foo:string,count(a):bigint>
-- !query 6 output
foo 7
-- !query 7
SELECT 'foo' FROM testData WHERE a = 0 GROUP BY 1
-- !query 7 schema
struct<foo:string>
-- !query 7 output
-- !query 8
SELECT 'foo', APPROX_COUNT_DISTINCT(a) FROM testData WHERE a = 0 GROUP BY 1
-- !query 8 schema
struct<foo:string,approx_count_distinct(a):bigint>
-- !query 8 output
-- !query 9
SELECT 'foo', MAX(STRUCT(a)) FROM testData WHERE a = 0 GROUP BY 1
-- !query 9 schema
struct<foo:string,max(named_struct(a, a)):struct<a:int>>
-- !query 9 output
-- !query 10
SELECT a + b, COUNT(b) FROM testData GROUP BY a + b
-- !query 10 schema
struct<(a + b):int,count(b):bigint>
-- !query 10 output
2 1
3 2
4 2
5 1
NULL 1
-- !query 11
SELECT a + 2, COUNT(b) FROM testData GROUP BY a + 1
-- !query 11 schema
struct<>
-- !query 11 output
org.apache.spark.sql.AnalysisException
expression 'testdata.`a`' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;
-- !query 12
SELECT a + 1 + 1, COUNT(b) FROM testData GROUP BY a + 1
-- !query 12 schema
struct<((a + 1) + 1):int,count(b):bigint>
-- !query 12 output
3 2
4 2
5 2
NULL 1
-- !query 13
SELECT SKEWNESS(a), KURTOSIS(a), MIN(a), MAX(a), AVG(a), VARIANCE(a), STDDEV(a), SUM(a), COUNT(a)
FROM testData
-- !query 13 schema
struct<skewness(CAST(a AS DOUBLE)):double,kurtosis(CAST(a AS DOUBLE)):double,min(a):int,max(a):int,avg(a):double,var_samp(CAST(a AS DOUBLE)):double,stddev_samp(CAST(a AS DOUBLE)):double,sum(a):bigint,count(a):bigint>
-- !query 13 output
-0.2723801058145729 -1.5069204152249134 1 3 2.142857142857143 0.8095238095238094 0.8997354108424372 15 7
-- !query 14
SELECT COUNT(DISTINCT b), COUNT(DISTINCT b, c) FROM (SELECT 1 AS a, 2 AS b, 3 AS c) GROUP BY a
-- !query 14 schema
struct<count(DISTINCT b):bigint,count(DISTINCT b, c):bigint>
-- !query 14 output
1 1
|
