inputs/udf/udf-window.sql

0001 --This test file was converted from window.sql.
0002 -- Test data.
0003 CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES
0004 (null, 1L, 1.0D, date("2017-08-01"), timestamp(1501545600), "a"),
0005 (1, 1L, 1.0D, date("2017-08-01"), timestamp(1501545600), "a"),
0006 (1, 2L, 2.5D, date("2017-08-02"), timestamp(1502000000), "a"),
0007 (2, 2147483650L, 100.001D, date("2020-12-31"), timestamp(1609372800), "a"),
0008 (1, null, 1.0D, date("2017-08-01"), timestamp(1501545600), "b"),
0009 (2, 3L, 3.3D, date("2017-08-03"), timestamp(1503000000), "b"),
0010 (3, 2147483650L, 100.001D, date("2020-12-31"), timestamp(1609372800), "b"),
0011 (null, null, null, null, null, null),
0012 (3, 1L, 1.0D, date("2017-08-01"), timestamp(1501545600), null)
0013 AS testData(val, val_long, val_double, val_date, val_timestamp, cate);
0014
0015 -- RowsBetween
0016 SELECT udf(val), cate, count(val) OVER(PARTITION BY cate ORDER BY udf(val) ROWS CURRENT ROW) FROM testData
0017 ORDER BY cate, udf(val);
0018 SELECT udf(val), cate, sum(val) OVER(PARTITION BY cate ORDER BY udf(val)
0019 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) FROM testData ORDER BY cate, udf(val);
0020 SELECT val_long, udf(cate), sum(val_long) OVER(PARTITION BY cate ORDER BY udf(val_long)
0021 ROWS BETWEEN CURRENT ROW AND 2147483648 FOLLOWING) FROM testData ORDER BY udf(cate), val_long;
0022
0023 -- RangeBetween
0024 SELECT udf(val), cate, count(val) OVER(PARTITION BY udf(cate) ORDER BY val RANGE 1 PRECEDING) FROM testData
0025 ORDER BY cate, udf(val);
0026 SELECT val, udf(cate), sum(val) OVER(PARTITION BY udf(cate) ORDER BY val
0027 RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM testData ORDER BY udf(cate), val;
0028 SELECT val_long, udf(cate), sum(val_long) OVER(PARTITION BY udf(cate) ORDER BY val_long
0029 RANGE BETWEEN CURRENT ROW AND 2147483648 FOLLOWING) FROM testData ORDER BY udf(cate), val_long;
0030 SELECT val_double, udf(cate), sum(val_double) OVER(PARTITION BY udf(cate) ORDER BY val_double
0031 RANGE BETWEEN CURRENT ROW AND 2.5 FOLLOWING) FROM testData ORDER BY udf(cate), val_double;
0032 SELECT val_date, udf(cate), max(val_date) OVER(PARTITION BY udf(cate) ORDER BY val_date
0033 RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) FROM testData ORDER BY udf(cate), val_date;
0034 SELECT val_timestamp, udf(cate), avg(val_timestamp) OVER(PARTITION BY udf(cate) ORDER BY val_timestamp
0035 RANGE BETWEEN CURRENT ROW AND interval 23 days 4 hours FOLLOWING) FROM testData
0036 ORDER BY udf(cate), val_timestamp;
0037
0038 -- RangeBetween with reverse OrderBy
0039 SELECT val, udf(cate), sum(val) OVER(PARTITION BY cate ORDER BY val DESC
0040 RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM testData ORDER BY cate, val;
0041
0042 -- Invalid window frame
0043 SELECT udf(val), cate, count(val) OVER(PARTITION BY udf(cate)
0044 ROWS BETWEEN UNBOUNDED FOLLOWING AND 1 FOLLOWING) FROM testData ORDER BY cate, udf(val);
0045 SELECT udf(val), cate, count(val) OVER(PARTITION BY udf(cate)
0046 RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM testData ORDER BY cate, udf(val);
0047 SELECT udf(val), cate, count(val) OVER(PARTITION BY udf(cate) ORDER BY udf(val), cate
0048 RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM testData ORDER BY cate, udf(val);
0049 SELECT udf(val), cate, count(val) OVER(PARTITION BY udf(cate) ORDER BY current_timestamp
0050 RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM testData ORDER BY cate, udf(val);
0051 SELECT udf(val), cate, count(val) OVER(PARTITION BY udf(cate) ORDER BY val
0052 RANGE BETWEEN 1 FOLLOWING AND 1 PRECEDING) FROM testData ORDER BY udf(cate), val;
0053 SELECT udf(val), cate, count(val) OVER(PARTITION BY udf(cate) ORDER BY udf(val)
0054 RANGE BETWEEN CURRENT ROW AND current_date PRECEDING) FROM testData ORDER BY cate, val(val);
0055
0056
0057 -- Window functions
0058 SELECT udf(val), cate,
0059 max(udf(val)) OVER w AS max,
0060 min(udf(val)) OVER w AS min,
0061 min(udf(val)) OVER w AS min,
0062 count(udf(val)) OVER w AS count,
0063 sum(udf(val)) OVER w AS sum,
0064 avg(udf(val)) OVER w AS avg,
0065 stddev(udf(val)) OVER w AS stddev,
0066 first_value(udf(val)) OVER w AS first_value,
0067 first_value(udf(val), true) OVER w AS first_value_ignore_null,
0068 first_value(udf(val), false) OVER w AS first_value_contain_null,
0069 last_value(udf(val)) OVER w AS last_value,
0070 last_value(udf(val), true) OVER w AS last_value_ignore_null,
0071 last_value(udf(val), false) OVER w AS last_value_contain_null,
0072 rank() OVER w AS rank,
0073 dense_rank() OVER w AS dense_rank,
0074 cume_dist() OVER w AS cume_dist,
0075 percent_rank() OVER w AS percent_rank,
0076 ntile(2) OVER w AS ntile,
0077 row_number() OVER w AS row_number,
0078 var_pop(udf(val)) OVER w AS var_pop,
0079 var_samp(udf(val)) OVER w AS var_samp,
0080 approx_count_distinct(udf(val)) OVER w AS approx_count_distinct,
0081 covar_pop(udf(val), udf(val_long)) OVER w AS covar_pop,
0082 corr(udf(val), udf(val_long)) OVER w AS corr,
0083 stddev_samp(udf(val)) OVER w AS stddev_samp,
0084 stddev_pop(udf(val)) OVER w AS stddev_pop,
0085 collect_list(udf(val)) OVER w AS collect_list,
0086 collect_set(udf(val)) OVER w AS collect_set,
0087 skewness(udf(val_double)) OVER w AS skewness,
0088 kurtosis(udf(val_double)) OVER w AS kurtosis
0089 FROM testData
0090 WINDOW w AS (PARTITION BY udf(cate) ORDER BY udf(val))
0091 ORDER BY cate, udf(val);
0092
0093 -- Null inputs
0094 SELECT udf(val), cate, avg(null) OVER(PARTITION BY cate ORDER BY val) FROM testData ORDER BY cate, val;
0095
0096 -- OrderBy not specified
0097 SELECT udf(val), cate, row_number() OVER(PARTITION BY cate) FROM testData ORDER BY cate, udf(val);
0098
0099 -- Over clause is empty
0100 SELECT udf(val), cate, sum(val) OVER(), avg(val) OVER() FROM testData ORDER BY cate, val;
0101
0102 -- first_value()/last_value() over ()
0103 SELECT udf(val), cate,
0104 first_value(false) OVER w AS first_value,
0105 first_value(true, true) OVER w AS first_value_ignore_null,
0106 first_value(false, false) OVER w AS first_value_contain_null,
0107 last_value(false) OVER w AS last_value,
0108 last_value(true, true) OVER w AS last_value_ignore_null,
0109 last_value(false, false) OVER w AS last_value_contain_null
0110 FROM testData
0111 WINDOW w AS ()
0112 ORDER BY cate, val;
0113
0114 -- parentheses around window reference
0115 SELECT udf(cate), sum(val) OVER (w)
0116 FROM testData
0117 WHERE val is not null
0118 WINDOW w AS (PARTITION BY cate ORDER BY val);