0001
0002
0003
0004
0005
0006
0007 CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
0008 (1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2), (null, 1), (3, null), (null, null)
0009 AS testData(a, b);
0010
0011
0012 SELECT a, COUNT(b) FROM testData;
0013 SELECT COUNT(a), COUNT(b) FROM testData;
0014
0015
0016 SELECT a, COUNT(b) FROM testData GROUP BY a;
0017 SELECT a, COUNT(b) FROM testData GROUP BY b;
0018 SELECT COUNT(a), COUNT(b) FROM testData GROUP BY a;
0019
0020
0021 SELECT 'foo', COUNT(a) FROM testData GROUP BY 1;
0022
0023
0024 SELECT 'foo' FROM testData WHERE a = 0 GROUP BY 1;
0025
0026
0027 SELECT 'foo', APPROX_COUNT_DISTINCT(a) FROM testData WHERE a = 0 GROUP BY 1;
0028
0029
0030 SELECT 'foo', MAX(STRUCT(a)) FROM testData WHERE a = 0 GROUP BY 1;
0031
0032
0033 SELECT a + b, COUNT(b) FROM testData GROUP BY a + b;
0034 SELECT a + 2, COUNT(b) FROM testData GROUP BY a + 1;
0035 SELECT a + 1 + 1, COUNT(b) FROM testData GROUP BY a + 1;
0036
0037
0038 SELECT SKEWNESS(a), KURTOSIS(a), MIN(a), MAX(a), AVG(a), VARIANCE(a), STDDEV(a), SUM(a), COUNT(a)
0039 FROM testData;
0040
0041
0042 SELECT COUNT(DISTINCT b), COUNT(DISTINCT b, c) FROM (SELECT 1 AS a, 2 AS b, 3 AS c) GROUP BY a;
0043
0044
0045 SELECT a AS k, COUNT(b) FROM testData GROUP BY k;
0046 SELECT a AS k, COUNT(b) FROM testData GROUP BY k HAVING k > 1;
0047
0048
0049 SELECT COUNT(b) AS k FROM testData GROUP BY k;
0050
0051
0052 CREATE OR REPLACE TEMPORARY VIEW testDataHasSameNameWithAlias AS SELECT * FROM VALUES
0053 (1, 1, 3), (1, 2, 1) AS testDataHasSameNameWithAlias(k, a, v);
0054 SELECT k AS a, COUNT(v) FROM testDataHasSameNameWithAlias GROUP BY a;
0055
0056
0057 set spark.sql.groupByAliases=false;
0058
0059
0060 SELECT a AS k, COUNT(b) FROM testData GROUP BY k;
0061
0062
0063 SELECT a, COUNT(1) FROM testData WHERE false GROUP BY a;
0064
0065
0066 SELECT COUNT(1) FROM testData WHERE false;
0067 SELECT 1 FROM (SELECT COUNT(1) FROM testData WHERE false) t;
0068
0069
0070 SELECT 1 from (
0071 SELECT 1 AS z,
0072 MIN(a.x)
0073 FROM (select 1 as x) a
0074 WHERE false
0075 ) b
0076 where b.z != b.z;
0077
0078
0079 SELECT corr(DISTINCT x, y), corr(DISTINCT y, x), count(*)
0080 FROM (VALUES (1, 1), (2, 2), (2, 2)) t(x, y);
0081
0082
0083 SELECT 1 FROM range(10) HAVING true;
0084
0085 SELECT 1 FROM range(10) HAVING MAX(id) > 0;
0086
0087 SELECT id FROM range(10) HAVING id > 0;
0088
0089
0090 CREATE OR REPLACE TEMPORARY VIEW test_agg AS SELECT * FROM VALUES
0091 (1, true), (1, false),
0092 (2, true),
0093 (3, false), (3, null),
0094 (4, null), (4, null),
0095 (5, null), (5, true), (5, false) AS test_agg(k, v);
0096
0097
0098 SELECT every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg WHERE 1 = 0;
0099
0100
0101 SELECT every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg WHERE k = 4;
0102
0103
0104 SELECT every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg WHERE k = 5;
0105
0106
0107 SELECT k, every(v), some(v), any(v), bool_and(v), bool_or(v) FROM test_agg GROUP BY k;
0108
0109
0110 SELECT k, every(v) FROM test_agg GROUP BY k HAVING every(v) = false;
0111 SELECT k, every(v) FROM test_agg GROUP BY k HAVING every(v) IS NULL;
0112
0113
0114 SELECT k,
0115 Every(v) AS every
0116 FROM test_agg
0117 WHERE k = 2
0118 AND v IN (SELECT Any(v)
0119 FROM test_agg
0120 WHERE k = 1)
0121 GROUP BY k;
0122
0123
0124 SELECT k,
0125 Every(v) AS every
0126 FROM test_agg
0127 WHERE k = 2
0128 AND v IN (SELECT Every(v)
0129 FROM test_agg
0130 WHERE k = 1)
0131 GROUP BY k;
0132
0133
0134 SELECT every(1);
0135
0136
0137 SELECT some(1S);
0138
0139
0140 SELECT any(1L);
0141
0142
0143 SELECT every("true");
0144
0145
0146 SELECT bool_and(1.0);
0147
0148
0149 SELECT bool_or(1.0D);
0150
0151
0152 SELECT k, v, every(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg;
0153 SELECT k, v, some(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg;
0154 SELECT k, v, any(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg;
0155 SELECT k, v, bool_and(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg;
0156 SELECT k, v, bool_or(v) OVER (PARTITION BY k ORDER BY v) FROM test_agg;
0157
0158
0159 SELECT count(*) FROM test_agg HAVING count(*) > 1L;
0160 SELECT k, max(v) FROM test_agg GROUP BY k HAVING max(v) = true;
0161
0162
0163 SELECT * FROM (SELECT COUNT(*) AS cnt FROM test_agg) WHERE cnt > 1L;
0164
0165
0166 SELECT count(*) FROM test_agg WHERE count(*) > 1L;
0167 SELECT count(*) FROM test_agg WHERE count(*) + 1L > 1L;
0168 SELECT count(*) FROM test_agg WHERE k = 1 or k = 2 or count(*) + 1L > 1L or max(k) > 1;