|
||||
0001 -- 0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 0003 -- 0004 -- 0005 -- AGGREGATES [Part 1] 0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/aggregates.sql#L1-L143 0007 0008 -- avoid bit-exact output here because operations may not be bit-exact. 0009 -- SET extra_float_digits = 0; 0010 0011 -- This test file was converted from postgreSQL/aggregates_part1.sql. 0012 0013 SELECT avg(udf(four)) AS avg_1 FROM onek; 0014 0015 SELECT udf(avg(a)) AS avg_32 FROM aggtest WHERE a < 100; 0016 0017 -- In 7.1, avg(float4) is computed using float8 arithmetic. 0018 -- Round the result to 3 digits to avoid platform-specific results. 0019 0020 select CAST(avg(udf(b)) AS Decimal(10,3)) AS avg_107_943 FROM aggtest; 0021 -- `student` has a column with data type POINT, which is not supported by Spark [SPARK-27766] 0022 -- SELECT avg(gpa) AS avg_3_4 FROM ONLY student; 0023 0024 SELECT sum(udf(four)) AS sum_1500 FROM onek; 0025 SELECT udf(sum(a)) AS sum_198 FROM aggtest; 0026 SELECT udf(udf(sum(b))) AS avg_431_773 FROM aggtest; 0027 -- `student` has a column with data type POINT, which is not supported by Spark [SPARK-27766] 0028 -- SELECT sum(gpa) AS avg_6_8 FROM ONLY student; 0029 0030 SELECT udf(max(four)) AS max_3 FROM onek; 0031 SELECT max(udf(a)) AS max_100 FROM aggtest; 0032 SELECT udf(udf(max(aggtest.b))) AS max_324_78 FROM aggtest; 0033 -- `student` has a column with data type POINT, which is not supported by Spark [SPARK-27766] 0034 -- SELECT max(student.gpa) AS max_3_7 FROM student; 0035 0036 SELECT stddev_pop(udf(b)) FROM aggtest; 0037 SELECT udf(stddev_samp(b)) FROM aggtest; 0038 SELECT var_pop(udf(b)) FROM aggtest; 0039 SELECT udf(var_samp(b)) FROM aggtest; 0040 0041 SELECT udf(stddev_pop(CAST(b AS Decimal(38,0)))) FROM aggtest; 0042 SELECT stddev_samp(CAST(udf(b) AS Decimal(38,0))) FROM aggtest; 0043 SELECT udf(var_pop(CAST(b AS Decimal(38,0)))) FROM aggtest; 0044 SELECT var_samp(udf(CAST(b AS Decimal(38,0)))) FROM aggtest; 0045 0046 -- population variance is defined for a single tuple, sample variance 0047 -- is not 0048 SELECT udf(var_pop(1.0)), var_samp(udf(2.0)); 0049 SELECT stddev_pop(udf(CAST(3.0 AS Decimal(38,0)))), stddev_samp(CAST(udf(4.0) AS Decimal(38,0))); 0050 0051 0052 -- verify correct results for null and NaN inputs 0053 select sum(udf(CAST(null AS int))) from range(1,4); 0054 select sum(udf(CAST(null AS long))) from range(1,4); 0055 select sum(udf(CAST(null AS Decimal(38,0)))) from range(1,4); 0056 select sum(udf(CAST(null AS DOUBLE))) from range(1,4); 0057 select avg(udf(CAST(null AS int))) from range(1,4); 0058 select avg(udf(CAST(null AS long))) from range(1,4); 0059 select avg(udf(CAST(null AS Decimal(38,0)))) from range(1,4); 0060 select avg(udf(CAST(null AS DOUBLE))) from range(1,4); 0061 select sum(CAST(udf('NaN') AS DOUBLE)) from range(1,4); 0062 select avg(CAST(udf('NaN') AS DOUBLE)) from range(1,4); 0063 0064 -- [SPARK-27768] verify correct results for infinite inputs 0065 -- [SPARK-28291] UDFs cannot be evaluated within inline table definition 0066 -- SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE)) 0067 -- FROM (VALUES (CAST(udf('1') AS DOUBLE)), (CAST(udf('Infinity') AS DOUBLE))) v(x); 0068 SELECT avg(CAST(udf(x) AS DOUBLE)), var_pop(CAST(udf(x) AS DOUBLE)) 0069 FROM (VALUES ('Infinity'), ('1')) v(x); 0070 SELECT avg(CAST(udf(x) AS DOUBLE)), var_pop(CAST(udf(x) AS DOUBLE)) 0071 FROM (VALUES ('Infinity'), ('Infinity')) v(x); 0072 SELECT avg(CAST(udf(x) AS DOUBLE)), var_pop(CAST(udf(x) AS DOUBLE)) 0073 FROM (VALUES ('-Infinity'), ('Infinity')) v(x); 0074 0075 0076 -- test accuracy with a large input offset 0077 SELECT avg(udf(CAST(x AS DOUBLE))), udf(var_pop(CAST(x AS DOUBLE))) 0078 FROM (VALUES (100000003), (100000004), (100000006), (100000007)) v(x); 0079 SELECT avg(udf(CAST(x AS DOUBLE))), udf(var_pop(CAST(x AS DOUBLE))) 0080 FROM (VALUES (7000000000005), (7000000000007)) v(x); 0081 0082 -- SQL2003 binary aggregates [SPARK-23907] 0083 -- SELECT regr_count(b, a) FROM aggtest; 0084 -- SELECT regr_sxx(b, a) FROM aggtest; 0085 -- SELECT regr_syy(b, a) FROM aggtest; 0086 -- SELECT regr_sxy(b, a) FROM aggtest; 0087 -- SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest; 0088 -- SELECT regr_r2(b, a) FROM aggtest; 0089 -- SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest; 0090 SELECT udf(covar_pop(b, udf(a))), covar_samp(udf(b), a) FROM aggtest; 0091 SELECT corr(b, udf(a)) FROM aggtest; 0092 0093 0094 -- test accum and combine functions directly [SPARK-23907] 0095 -- CREATE TABLE regr_test (x float8, y float8); 0096 -- INSERT INTO regr_test VALUES (10,150),(20,250),(30,350),(80,540),(100,200); 0097 -- SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x) 0098 -- FROM regr_test WHERE x IN (10,20,30,80); 0099 -- SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x) 0100 -- FROM regr_test; 0101 -- SELECT float8_accum('{4,140,2900}'::float8[], 100); 0102 -- SELECT float8_regr_accum('{4,140,2900,1290,83075,15050}'::float8[], 200, 100); 0103 -- SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x) 0104 -- FROM regr_test WHERE x IN (10,20,30); 0105 -- SELECT count(*), sum(x), regr_sxx(y,x), sum(y),regr_syy(y,x), regr_sxy(y,x) 0106 -- FROM regr_test WHERE x IN (80,100); 0107 -- SELECT float8_combine('{3,60,200}'::float8[],ELECT CAST(udf(covar_pop(b, udf(a))) AS '{0,0,0}'::float8[]); 0108 -- SELECT float8_combine('{0,0,0}'::float8[], '{2,180,200}'::float8[]); 0109 -- SELECT float8_combine('{3,60,200}'::float8[], '{2,180,200}'::float8[]); 0110 -- SELECT float8_regr_combine('{3,60,200,750,20000,2000}'::float8[], 0111 -- '{0,0,0,0,0,0}'::float8[]); 0112 -- SELECT float8_regr_combine('{0,0,0,0,0,0}'::float8[], 0113 -- '{2,180,200,740,57800,-3400}'::float8[]); 0114 -- SELECT float8_regr_combine('{3,60,200,750,20000,2000}'::float8[], 0115 -- '{2,180,200,740,57800,-3400}'::float8[]); 0116 -- DROP TABLE regr_test; 0117 0118 0119 -- test count, distinct 0120 SELECT count(udf(four)) AS cnt_1000 FROM onek; 0121 SELECT udf(count(DISTINCT four)) AS cnt_4 FROM onek; 0122 0123 select ten, udf(count(*)), sum(udf(four)) from onek 0124 group by ten order by ten; 0125 0126 select ten, count(udf(four)), udf(sum(DISTINCT four)) from onek 0127 group by ten order by ten; 0128 0129 -- user-defined aggregates 0130 -- SELECT newavg(four) AS avg_1 FROM onek; 0131 -- SELECT newsum(four) AS sum_1500 FROM onek; 0132 -- SELECT newcnt(four) AS cnt_1000 FROM onek; 0133 -- SELECT newcnt(*) AS cnt_1000 FROM onek; 0134 -- SELECT oldcnt(*) AS cnt_1000 FROM onek; 0135 -- SELECT sum2(q1,q2) FROM int8_tbl; 0136 0137 -- test for outer-level aggregates 0138 0139 -- this should work 0140 select ten, udf(sum(distinct four)) from onek a 0141 group by ten 0142 having exists (select 1 from onek b where udf(sum(distinct a.four)) = b.four); 0143 0144 -- this should fail because subquery has an agg of its own in WHERE 0145 select ten, sum(distinct four) from onek a 0146 group by ten 0147 having exists (select 1 from onek b 0148 where sum(distinct a.four + b.four) = udf(b.four)); 0149 0150 -- [SPARK-27769] Test handling of sublinks within outer-level aggregates. 0151 -- Per bug report from Daniel Grace. 0152 select 0153 (select udf(max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))) 0154 from tenk1 o;
[ Source navigation ] | [ Diff markup ] | [ Identifier search ] | [ general search ] |
This page was automatically generated by the 2.1.0 LXR engine. The LXR team |