diff options
| author | Maxim Yurchuk <maxim-yurchuk@ydb.tech> | 2024-11-20 17:37:57 +0000 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-11-20 17:37:57 +0000 |
| commit | f76323e9b295c15751e51e3443aa47a36bee8023 (patch) | |
| tree | 4113c8cad473a33e0f746966e0cf087252fa1d7a /yql/essentials/tests/sql/suites/window | |
| parent | 753ecb8d410a4cb459c26f3a0082fb2d1724fe63 (diff) | |
| parent | a7b9a6afea2a9d7a7bfac4c5eb4c1a8e60adb9e6 (diff) | |
| download | ydb-f76323e9b295c15751e51e3443aa47a36bee8023.tar.gz | |
Merge pull request #11788 from ydb-platform/mergelibs-241120-1113
Library import 241120-1113
Diffstat (limited to 'yql/essentials/tests/sql/suites/window')
239 files changed, 2520 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/window/agg_factory.sql.txt b/yql/essentials/tests/sql/suites/window/agg_factory.sql.txt new file mode 100644 index 0000000000..6842b1818c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/agg_factory.sql.txt @@ -0,0 +1,3 @@ +$factory = AGGREGATION_FACTORY('COUNT'); + +export $factory; diff --git a/yql/essentials/tests/sql/suites/window/all_columns_hide_window_special_ones.sql b/yql/essentials/tests/sql/suites/window/all_columns_hide_window_special_ones.sql new file mode 100644 index 0000000000..b9b356c939 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/all_columns_hide_window_special_ones.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +use plato; +pragma simplecolumns; + +select +a.*, +(ROW_NUMBER() over w) - 1 as position_cnt, +lag(key) over w as pkey, +lead(key) over w as nkey +from Input as a +window w as ( + order by value desc +) +order by position_cnt; diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations.cfg b/yql/essentials/tests/sql/suites/window/current/aggregations.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/aggregations.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations.sql b/yql/essentials/tests/sql/suites/window/current/aggregations.sql new file mode 100644 index 0000000000..444ea196cc --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/aggregations.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM plato.Input +WINDOW + w1 as (PARTITION BY key ORDER BY value), + w2 as ( ORDER BY value) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.cfg b/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.sql b/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.sql new file mode 100644 index 0000000000..c73f26cb67 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/aggregations_leadlag.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM plato.Input +WINDOW + w1 as (PARTITION BY key ORDER BY value), + w2 as ( ORDER BY value) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current.cfg b/yql/essentials/tests/sql/suites/window/current/ansi_current.cfg new file mode 100644 index 0000000000..812be3893b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/ansi_current.cfg @@ -0,0 +1 @@ +in Input input4.txt diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current.sql b/yql/essentials/tests/sql/suites/window/current/ansi_current.sql new file mode 100644 index 0000000000..d005c57dc2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/ansi_current.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma AnsiCurrentRow; + +SELECT + value, + key, + subkey, + SUM(cast(subkey as Int32)) over w as subkey_sum, +FROM plato.Input +WINDOW w AS ( + PARTITION BY value + ORDER BY key +) +ORDER BY value, key, subkey; diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.cfg b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.cfg new file mode 100644 index 0000000000..812be3893b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.cfg @@ -0,0 +1 @@ +in Input input4.txt diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.sql b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.sql new file mode 100644 index 0000000000..b89caf963f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_mixed.sql @@ -0,0 +1,25 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma AnsiCurrentRow; + +SELECT + value, + key, + subkey, + SUM(cast(subkey as Int32)) over w as subkey_sum_ansi, + SUM(cast(subkey as Int32)) over w1 as subkey_sum, + SUM(cast(subkey as Int32)) over w2 as subkey_sum_next, +FROM plato.Input +WINDOW w AS ( + PARTITION BY value + ORDER BY key +), w1 AS ( + PARTITION BY value + ORDER BY key + ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +), w2 AS ( + PARTITION BY value + ORDER BY key + ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING +) +ORDER BY value, key, subkey; diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.cfg b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.cfg new file mode 100644 index 0000000000..812be3893b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.cfg @@ -0,0 +1 @@ +in Input input4.txt diff --git a/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.sql b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.sql new file mode 100644 index 0000000000..8baa348440 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/ansi_current_with_win.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma AnsiCurrentRow; + +SELECT + value, + key, + subkey, + SUM(cast(subkey as Int32)) over w as subkey_sum_ansi, + LEAD(cast(subkey as Int32)) over w as subkey_next, +FROM plato.Input +WINDOW w AS ( + PARTITION BY value + ORDER BY key +) +ORDER BY value, key, subkey; diff --git a/yql/essentials/tests/sql/suites/window/current/session.cfg b/yql/essentials/tests/sql/suites/window/current/session.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/current/session.sql b/yql/essentials/tests/sql/suites/window/current/session.sql new file mode 100644 index 0000000000..10ae2c44d3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + payload, + AGGREGATE_LIST(ts) over w as ts_session, + COUNT(1) over w as session_len, +FROM plato.Input +WINDOW w AS ( + PARTITION BY user, SessionWindow(ts, 10) + ORDER BY ts +) +ORDER BY user, payload; diff --git a/yql/essentials/tests/sql/suites/window/current/session_aliases.cfg b/yql/essentials/tests/sql/suites/window/current/session_aliases.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session_aliases.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/current/session_aliases.sql b/yql/essentials/tests/sql/suites/window/current/session_aliases.sql new file mode 100644 index 0000000000..9aa080e22c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session_aliases.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + SessionStart() over w1 as ss1, + SessionStart() over w as ss, + + AGGREGATE_LIST(ts) over w as ts_session, + COUNT(1) over w as session_len, +FROM plato.Input +WINDOW w AS ( + PARTITION BY user, SessionWindow(ts, 10) as ss0 + ORDER BY ts +), +w1 AS ( + PARTITION BY SessionWindow(ts, 10), user + ORDER BY ts +) +ORDER BY user, ts, session_len; diff --git a/yql/essentials/tests/sql/suites/window/current/session_extended.cfg b/yql/essentials/tests/sql/suites/window/current/session_extended.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session_extended.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/current/session_extended.sql b/yql/essentials/tests/sql/suites/window/current/session_extended.sql new file mode 100644 index 0000000000..5990b4ec12 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session_extended.sql @@ -0,0 +1,29 @@ +/* syntax version 1 */ +/* postgres can not */ + +$init = ($row) -> (AsStruct($row.ts ?? 0 as value, 1 as count)); + +$calculate = ($_row, $state) -> ($state.value); +-- split partition into two-element grooups, make session key to be cumulative sum of ts from partition start +$update = ($row, $state) -> { + $state = AsStruct($state.count + 1 as count, $state.value as value); + $state = AsStruct($state.count as count, $state.value + ($row.ts ?? 0) as value); + return AsTuple(Unwrap($state.count % 2) == 1, $state); +}; + + +SELECT + user, + ts, + payload, + AGGREGATE_LIST(cast(ts as string) ?? "null") over w as ts_session, + COUNT(1) over w as session_len, + SessionStart() over w as session_start, + SessionState() over w as session_state, +FROM plato.Input +WINDOW w AS ( + PARTITION BY user, SessionWindow(ts + 1, $init, $update, $calculate) + ORDER BY ts +) +ORDER BY user, payload; + diff --git a/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.cfg b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.sql b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.sql new file mode 100644 index 0000000000..81efa941a6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/current/session_incompat_sort.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + payload, + AGGREGATE_LIST(ts) over w as ts_session, + COUNT(1) over w as session_len, + SessionStart() over w as session_start, +FROM plato.Input +WINDOW w AS ( + PARTITION BY SessionWindow(ts, 10), user + ORDER BY payload +) +ORDER BY user, payload; diff --git a/yql/essentials/tests/sql/suites/window/default.cfg b/yql/essentials/tests/sql/suites/window/default.cfg new file mode 100644 index 0000000000..0b7e11f3c5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/default.cfg @@ -0,0 +1,2 @@ +in Input input.txt +in Input4 input4.txt diff --git a/yql/essentials/tests/sql/suites/window/distinct_over_window.cfg b/yql/essentials/tests/sql/suites/window/distinct_over_window.cfg new file mode 100644 index 0000000000..ed883e79be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/distinct_over_window.cfg @@ -0,0 +1 @@ +in Input input3.txt diff --git a/yql/essentials/tests/sql/suites/window/distinct_over_window.sql b/yql/essentials/tests/sql/suites/window/distinct_over_window.sql new file mode 100644 index 0000000000..536db81911 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/distinct_over_window.sql @@ -0,0 +1,20 @@ +/* syntax version 1 */ +/* postgres can not */ + +USE plato; + +PRAGMA DistinctOverWindow; + +$input = (SELECT cast(key AS Int32) AS key, cast(subkey AS Int32) AS subkey, value FROM Input); + +SELECT + subkey, + key, + value, + -- assuming ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + count(DISTINCT key) OVER (PARTITION BY subkey ORDER BY key) AS count_by_key, + count(DISTINCT value || "force_preagg") OVER (PARTITION BY subkey ORDER BY key) AS count_by_value, + sum(DISTINCT key) OVER (PARTITION BY subkey ORDER BY key) AS sum, + median(DISTINCT key) OVER (PARTITION BY subkey ORDER BY key) AS median, +FROM $input +ORDER BY subkey, key, value; diff --git a/yql/essentials/tests/sql/suites/window/distinct_over_window_full_frames.cfg b/yql/essentials/tests/sql/suites/window/distinct_over_window_full_frames.cfg new file mode 100644 index 0000000000..ed883e79be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/distinct_over_window_full_frames.cfg @@ -0,0 +1 @@ +in Input input3.txt diff --git a/yql/essentials/tests/sql/suites/window/distinct_over_window_full_frames.sql b/yql/essentials/tests/sql/suites/window/distinct_over_window_full_frames.sql new file mode 100644 index 0000000000..973a23db8c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/distinct_over_window_full_frames.sql @@ -0,0 +1,22 @@ +/* syntax version 1 */ +/* postgres can not */ + +USE plato; + +PRAGMA DistinctOverWindow; + +$input = (SELECT cast(key AS Int32) AS key, cast(subkey AS Int32) AS subkey, value FROM Input); + +SELECT + subkey, + key, + value, + -- assuming ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + count(DISTINCT key) OVER () AS cnt_distinct_total, + sum(DISTINCT key) OVER () AS sum_distinct_total, + median(DISTINCT key) OVER () AS median_distinct_total, + count(DISTINCT key) OVER (PARTITION BY subkey) AS cnt_distinct_part, + sum(DISTINCT key) OVER (PARTITION BY subkey) AS sum_distinct_part, + median(DISTINCT key) OVER (PARTITION BY subkey) AS median_distinct_part, +FROM $input +ORDER BY subkey, key, value; diff --git a/yql/essentials/tests/sql/suites/window/distinct_over_window_struct.sql b/yql/essentials/tests/sql/suites/window/distinct_over_window_struct.sql new file mode 100644 index 0000000000..57e76a0935 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/distinct_over_window_struct.sql @@ -0,0 +1,37 @@ +/* syntax version 1 */ +/* postgres can not */ + +USE plato; + +PRAGMA DistinctOverWindow; + +$input = AsList( + AsStruct(1 AS key, 1 AS subkey, AsStruct(1 AS i1, 2 AS i2, 3 AS i3) AS col), + AsStruct(2 AS key, 1 AS subkey, AsStruct(1 AS i1, 2 AS i2, 3 AS i3) AS col), + AsStruct(3 AS key, 1 AS subkey, AsStruct(1 AS i1, 2 AS i2, 3 AS i3) AS col), + AsStruct(4 AS key, 2 AS subkey, AsStruct(3 AS i1, 4 AS i2, 5 AS i3) AS col), + AsStruct(5 AS key, 2 AS subkey, AsStruct(3 AS i1, 4 AS i2, 5 AS i3) AS col), + AsStruct(6 AS key, 2 AS subkey, AsStruct(5 AS i1, 5 AS i2, 5 AS i3) AS col), + AsStruct(7 AS key, 3 AS subkey, AsStruct(5 AS i1, 6 AS i2, 7 AS i3) AS col), + AsStruct(8 AS key, 3 AS subkey, AsStruct(6 AS i1, 7 AS i2, 8 AS i3) AS col), + AsStruct(9 AS key, 3 AS subkey, AsStruct(7 AS i1, 8 AS i2, 9 AS i3) AS col), +); + +SELECT + key, + subkey, + col, + -- assuming ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + count(DISTINCT col) OVER (PARTITION BY subkey ORDER BY key ASC) AS cnt1_asc, + count(DISTINCT col) OVER (PARTITION BY subkey ORDER BY key DESC) AS cnt2_desc, +FROM AS_TABLE($input) +ORDER BY key; + +SELECT + key, + subkey, + col, + -- assuming ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + count(DISTINCT col) OVER (PARTITION BY subkey) AS cnt, +FROM AS_TABLE($input) +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/window/empty/aggregations.cfg b/yql/essentials/tests/sql/suites/window/empty/aggregations.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/empty/aggregations.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/empty/aggregations.sql b/yql/essentials/tests/sql/suites/window/empty/aggregations.sql new file mode 100644 index 0000000000..673e724f83 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/empty/aggregations.sql @@ -0,0 +1,18 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA warning("disable", "4520"); + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM plato.Input +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN 5 PRECEDING AND 10 PRECEDING), + w2 as ( ROWS BETWEEN 3 FOLLOWING AND 2 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/empty/aggregations_leadlag.cfg b/yql/essentials/tests/sql/suites/window/empty/aggregations_leadlag.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/empty/aggregations_leadlag.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/empty/aggregations_leadlag.sql b/yql/essentials/tests/sql/suites/window/empty/aggregations_leadlag.sql new file mode 100644 index 0000000000..c305328b8b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/empty/aggregations_leadlag.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA warning("disable", "4520"); + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM plato.Input +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN 5 PRECEDING AND 10 PRECEDING), + w2 as (ORDER BY value DESC ROWS BETWEEN 3 FOLLOWING AND 2 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations.sql b/yql/essentials/tests/sql/suites/window/full/aggregations.sql new file mode 100644 index 0000000000..b413db5f7c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (), + w2 as (PARTITION BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_compact.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_compact.sql b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.sql new file mode 100644 index 0000000000..1c18473298 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations_compact.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION COMPACT BY ()), + w2 as (PARTITION COMPACT BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.sql b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.sql new file mode 100644 index 0000000000..413e1a0ddd --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w2 as (PARTITION BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.cfg b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.sql b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.sql new file mode 100644 index 0000000000..c68f9946a1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/aggregations_leadlag_compact.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION COMPACT BY () ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w2 as (PARTITION COMPACT BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag.cfg b/yql/essentials/tests/sql/suites/window/full/leadlag.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/leadlag.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag.sql b/yql/essentials/tests/sql/suites/window/full/leadlag.sql new file mode 100644 index 0000000000..1fccdfd276 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/leadlag.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ + +$in = SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w2 as (PARTITION BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +; + +SELECT value, dvalue_lead1, value_lag2 FROM $in ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag_compact.cfg b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/leadlag_compact.sql b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.sql new file mode 100644 index 0000000000..45a8a6232f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/leadlag_compact.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ + +$in = SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION /*+ COMPACT() */ BY () ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w2 as (PARTITION COMPACT BY key ORDER BY value DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +; + +SELECT value, dvalue_lead1, value_lag2 FROM $in ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.cfg b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.sql b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.sql new file mode 100644 index 0000000000..019551ef7e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +/* postgres can not */ + +$input = ( +SELECT * FROM plato.Input WHERE key = '1' +UNION ALL +SELECT NULL AS key, "9" as subkey, "000" as value +UNION ALL +SELECT NULL AS key, "9" as subkey, "001" as value +); + + +SELECT + key, + subkey, + value, + + AGGREGATE_LIST(value) over w1 as agglist1, + +FROM $input +WINDOW + w1 as (PARTITION BY key, subkey ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.cfg b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.sql b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.sql new file mode 100644 index 0000000000..81c3fe43ff --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_nulls_tuple_key.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +/* postgres can not */ + +$input = ( +SELECT * FROM plato.Input WHERE key = '1' +UNION ALL +SELECT NULL AS key, "9" as subkey, "000" as value +UNION ALL +SELECT NULL AS key, "9" as subkey, "001" as value +); + + +SELECT + key, + subkey, + value, + + AGGREGATE_LIST(value) over w1 as agglist1, + +FROM $input +WINDOW + w1 as (PARTITION BY (key, subkey) as pkey ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.cfg b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.cfg new file mode 100644 index 0000000000..bcf4082c5c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.cfg @@ -0,0 +1 @@ +in Input input_optkey2.txt diff --git a/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.sql b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.sql new file mode 100644 index 0000000000..1b3778fc96 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/noncompact_with_tablerow.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + key, + subkey, + value, + + AGGREGATE_LIST(TableRow()) OVER w AS frame, + +FROM plato.Input +WINDOW + w as (PARTITION BY key, subkey ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/full/session.cfg b/yql/essentials/tests/sql/suites/window/full/session.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/full/session.sql b/yql/essentials/tests/sql/suites/window/full/session.sql new file mode 100644 index 0000000000..76ee7bd0c8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +-- add non-optional partition key +$src = SELECT t.*, user ?? "u0" as user_nonopt FROM Input as t; + +SELECT + user, + user_nonopt, + ts, + payload, + AGGREGATE_LIST(TableRow()) over w as full_session, + COUNT(1) over w as session_len, +FROM $src +WINDOW w AS ( + PARTITION BY user, user_nonopt, SessionWindow(ts, 10) + ORDER BY ts + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING +) +ORDER BY user, payload; diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases.cfg b/yql/essentials/tests/sql/suites/window/full/session_aliases.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_aliases.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases.sql b/yql/essentials/tests/sql/suites/window/full/session_aliases.sql new file mode 100644 index 0000000000..b809c6b534 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_aliases.sql @@ -0,0 +1,19 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + SessionStart() over w1 as ss1, + SessionStart() over w as ss, + + ListSort(AGGREGATE_LIST(ts) over w) as ts_session, + COUNT(1) over w as session_len, +FROM plato.Input +WINDOW w AS ( + PARTITION BY user, SessionWindow(ts, 10) as ss0 +), +w1 AS ( + PARTITION BY SessionWindow(ts, 10), user +) +ORDER BY user, ts; diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.cfg b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.sql b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.sql new file mode 100644 index 0000000000..2bd49f12f1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_aliases_compact.sql @@ -0,0 +1,19 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + SessionStart() over w1 as ss1, + SessionStart() over w as ss, + + ListSort(AGGREGATE_LIST(ts) over w) as ts_session, + COUNT(1) over w as session_len, +FROM plato.Input +WINDOW w AS ( + PARTITION COMPACT BY user, SessionWindow(ts, 10) as ss0 +), +w1 AS ( + PARTITION COMPACT BY SessionWindow(ts, 10), user +) +ORDER BY user, ts; diff --git a/yql/essentials/tests/sql/suites/window/full/session_compact.cfg b/yql/essentials/tests/sql/suites/window/full/session_compact.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_compact.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/full/session_compact.sql b/yql/essentials/tests/sql/suites/window/full/session_compact.sql new file mode 100644 index 0000000000..8c86df8e1c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_compact.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + payload, + AGGREGATE_LIST(ts) over w as ts_session, + COUNT(1) over w as session_len, +FROM plato.Input +WINDOW w AS ( + PARTITION COMPACT BY user, SessionWindow(ts, 10) + ORDER BY ts + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING +) +ORDER BY user, payload; diff --git a/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.cfg b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.sql b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.sql new file mode 100644 index 0000000000..6280f571af --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/session_incompat_sort.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +-- add non-optional partition key +$src = SELECT t.*, user ?? "u0" as user_nonopt FROM Input as t; + +SELECT + user, + user_nonopt, + ts, + payload, + AGGREGATE_LIST(TableRow()) over w as full_session, + COUNT(1) over w as session_len, +FROM $src +WINDOW w AS ( + PARTITION BY user, user_nonopt, SessionWindow(ts, 10) + ORDER BY ts DESC + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING +) +ORDER BY user, payload; diff --git a/yql/essentials/tests/sql/suites/window/full/syscolumns.cfg b/yql/essentials/tests/sql/suites/window/full/syscolumns.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/syscolumns.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/full/syscolumns.sql b/yql/essentials/tests/sql/suites/window/full/syscolumns.sql new file mode 100644 index 0000000000..d2533c062b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/full/syscolumns.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +select + value, + max(value) over (partition by cast(TableName() as Utf8)), + cast(TableName() as Utf8), +from Input order by value; + +select + value, + max(value) over (order by cast(TableName() as Utf8) rows between unbounded preceding and unbounded following), + cast(TableName() as Utf8), +from Input order by value; diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.sql new file mode 100644 index 0000000000..aca3f1c6e6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_after_current.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN 3 FOLLOWING AND 5 FOLLOWING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.sql new file mode 100644 index 0000000000..e3adba7ded --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_before_current.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN 5 PRECEDING AND 3 PRECEDING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.sql new file mode 100644 index 0000000000..8b43cd6699 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_include_current.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.sql new file mode 100644 index 0000000000..f301c179de --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.cfg b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.sql b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.sql new file mode 100644 index 0000000000..2b744948ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/aggregations_mixed_leadlag.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/generic/session.cfg b/yql/essentials/tests/sql/suites/window/generic/session.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/session.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/generic/session.sql b/yql/essentials/tests/sql/suites/window/generic/session.sql new file mode 100644 index 0000000000..998f12e1ea --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/session.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + payload, + AGGREGATE_LIST(ts) over w as ts_session, + COUNT(1) over w as session_len, +FROM plato.Input +WINDOW w AS ( + PARTITION BY user, SessionWindow(ts, 10) + ORDER BY ts + ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING +) +ORDER BY user, payload; diff --git a/yql/essentials/tests/sql/suites/window/generic/session_aliases.cfg b/yql/essentials/tests/sql/suites/window/generic/session_aliases.cfg new file mode 100644 index 0000000000..c788a7d1ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/session_aliases.cfg @@ -0,0 +1 @@ +in Input session1.txt diff --git a/yql/essentials/tests/sql/suites/window/generic/session_aliases.sql b/yql/essentials/tests/sql/suites/window/generic/session_aliases.sql new file mode 100644 index 0000000000..2375d56064 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/generic/session_aliases.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + user, + ts, + SessionStart() over w1 as ss1, + SessionStart() over w as ss, + + AGGREGATE_LIST(ts) over w as ts_session, + COUNT(1) over w as session_len, +FROM plato.Input +WINDOW w AS ( + PARTITION BY user, SessionWindow(ts, 10) as ss0 + ORDER BY ts + ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING +), +w1 AS ( + PARTITION BY SessionWindow(ts, 10), user + ORDER BY ts + ROWS BETWEEN 100 PRECEDING AND 100 FOLLOWING +) +ORDER BY user, ts; diff --git a/yql/essentials/tests/sql/suites/window/input.txt b/yql/essentials/tests/sql/suites/window/input.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input.txt @@ -0,0 +1,4 @@ +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="800";"subkey"="2";"value"="ddd"}; +{"key"="020";"subkey"="3";"value"="q"}; +{"key"="150";"subkey"="4";"value"="qzz"}; diff --git a/yql/essentials/tests/sql/suites/window/input2.txt b/yql/essentials/tests/sql/suites/window/input2.txt new file mode 100644 index 0000000000..b214aab0d9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input2.txt @@ -0,0 +1,10 @@ +{"key"="023";"subkey"="3";"value"="aaa"}; +{"key"="037";"subkey"="5";"value"="ddd"}; +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="150";"subkey"="1";"value"="aaa"}; +{"key"="150";"subkey"="3";"value"="iii"}; +{"key"="150";"subkey"="8";"value"="zzz"}; +{"key"="200";"subkey"="7";"value"="qqq"}; +{"key"="527";"subkey"="4";"value"="bbb"}; +{"key"="761";"subkey"="6";"value"="ccc"}; +{"key"="911";"subkey"="2";"value"="kkk"}; diff --git a/yql/essentials/tests/sql/suites/window/input3.txt b/yql/essentials/tests/sql/suites/window/input3.txt new file mode 100644 index 0000000000..b125936b3c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input3.txt @@ -0,0 +1,9 @@ +{"key"="23";"subkey"="1";"value"="aaa"}; +{"key"="41";"subkey"="2";"value"="ddd"}; +{"key"="54";"subkey"="3";"value"="ggg"}; +{"key"="25";"subkey"="1";"value"="bbb"}; +{"key"="41";"subkey"="2";"value"="eee"}; +{"key"="25";"subkey"="1";"value"="ccc"}; +{"key"="55";"subkey"="3";"value"="hhh"}; +{"key"="41";"subkey"="2";"value"="fff"}; +{"key"="56";"subkey"="3";"value"="iii"}; diff --git a/yql/essentials/tests/sql/suites/window/input4.txt b/yql/essentials/tests/sql/suites/window/input4.txt new file mode 100644 index 0000000000..65f33616b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input4.txt @@ -0,0 +1,9 @@ +{"key"="0";"subkey"="10";"value"="FOO"}; +{"key"="0";"subkey"="40";"value"="FOO"}; +{"key"="1";"subkey"="10";"value"="FOO"}; +{"key"="1";"subkey"="20";"value"="BAR"}; +{"key"="1";"subkey"="40";"value"="BAR"}; +{"key"="1";"subkey"="50";"value"="WAT"}; +{"key"="2";"subkey"="40";"value"="WAT"}; +{"key"="2";"subkey"="50";"value"="FOO"}; +{"key"="2";"subkey"="60";"value"="BAR"}; diff --git a/yql/essentials/tests/sql/suites/window/input5.txt b/yql/essentials/tests/sql/suites/window/input5.txt new file mode 100644 index 0000000000..f90c1dbacc --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input5.txt @@ -0,0 +1,30 @@ +{"key"="1";"subkey"="3";"value"="01"}; +{"key"="1";"subkey"="5";"value"="02"}; +{"key"="1";"subkey"="1";"value"="03"}; +{"key"="1";"subkey"="1";"value"="04"}; +{"key"="1";"subkey"="3";"value"="05"}; +{"key"="1";"subkey"="8";"value"="06"}; +{"key"="1";"subkey"="7";"value"="07"}; +{"key"="1";"subkey"="4";"value"="08"}; +{"key"="1";"subkey"="6";"value"="09"}; +{"key"="1";"subkey"="2";"value"="10"}; +{"key"="2";"subkey"="3";"value"="11"}; +{"key"="2";"subkey"="5";"value"="12"}; +{"key"="2";"subkey"="1";"value"="13"}; +{"key"="2";"subkey"="1";"value"="14"}; +{"key"="2";"subkey"="3";"value"="15"}; +{"key"="2";"subkey"="8";"value"="16"}; +{"key"="2";"subkey"="7";"value"="17"}; +{"key"="2";"subkey"="4";"value"="18"}; +{"key"="2";"subkey"="6";"value"="19"}; +{"key"="2";"subkey"="2";"value"="20"}; +{"key"="3";"subkey"="3";"value"="21"}; +{"key"="3";"subkey"="5";"value"="22"}; +{"key"="3";"subkey"="1";"value"="23"}; +{"key"="3";"subkey"="1";"value"="24"}; +{"key"="3";"subkey"="3";"value"="25"}; +{"key"="3";"subkey"="8";"value"="26"}; +{"key"="3";"subkey"="7";"value"="27"}; +{"key"="3";"subkey"="4";"value"="28"}; +{"key"="3";"subkey"="6";"value"="29"}; +{"key"="3";"subkey"="2";"value"="30"}; diff --git a/yql/essentials/tests/sql/suites/window/input_intersect.txt b/yql/essentials/tests/sql/suites/window/input_intersect.txt new file mode 100644 index 0000000000..42bcf2179e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input_intersect.txt @@ -0,0 +1,14 @@ +{"key"="075";"subkey"="911";"value"="abc"}; +{"key"="911";"subkey"="1";"value"="kkk"}; +{"key"="023";"subkey"="527";"value"="aaa"}; +{"key"="527";"subkey"="023";"value"="bbb"}; +{"key"="037";"subkey"="075";"value"="ddd"}; +{"key"="761";"subkey"="911";"value"="ccc"}; +{"key"="200";"subkey"="075";"value"="qqq"}; +{"key"="150";"subkey"="075";"value"="zzz"}; +{"key"="023";"subkey"="911";"value"="vca"}; +{"key"="527";"subkey"="150";"value"="oef"}; +{"key"="037";"subkey"="761";"value"="vdf"}; +{"key"="761";"subkey"="037";"value"="aet"}; +{"key"="200";"subkey"="150";"value"="fdb"}; +{"key"="150";"subkey"="037";"value"="bfs"}; diff --git a/yql/essentials/tests/sql/suites/window/input_intersect_with_holes.txt b/yql/essentials/tests/sql/suites/window/input_intersect_with_holes.txt new file mode 100644 index 0000000000..e010220a24 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input_intersect_with_holes.txt @@ -0,0 +1,14 @@ +{"key"="075";"subkey"="null";"value"="abc"}; +{"key"="911";"subkey"="1";"value"="kkk"}; +{"key"="023";"subkey"="null";"value"="aaa"}; +{"key"="527";"subkey"="023";"value"="bbb"}; +{"key"="037";"subkey"="075";"value"="ddd"}; +{"key"="761";"subkey"="911";"value"="ccc"}; +{"key"="200";"subkey"="322";"value"="qqq"}; +{"key"="150";"subkey"="null";"value"="zzz"}; +{"key"="023";"subkey"="null";"value"="vca"}; +{"key"="527";"subkey"="null";"value"="oef"}; +{"key"="037";"subkey"="761";"value"="vdf"}; +{"key"="761";"subkey"="null";"value"="aet"}; +{"key"="200";"subkey"="150";"value"="fdb"}; +{"key"="150";"subkey"="null";"value"="bfs"}; diff --git a/yql/essentials/tests/sql/suites/window/input_optkey1.txt b/yql/essentials/tests/sql/suites/window/input_optkey1.txt new file mode 100644 index 0000000000..c525b5bd8e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input_optkey1.txt @@ -0,0 +1,5 @@ +{"key"=1; "optkey"=1; value="1" }; +{"key"=2; "optkey"=2; value="2" }; +{"key"=3; "optkey"=3; value="3" }; +{"key"=4; "optkey"=4; value="4" }; +{"key"=5; "optkey"=#; value="null"}; diff --git a/yql/essentials/tests/sql/suites/window/input_optkey1.txt.attr b/yql/essentials/tests/sql/suites/window/input_optkey1.txt.attr new file mode 100644 index 0000000000..1ca6ee6155 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input_optkey1.txt.attr @@ -0,0 +1,7 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"Int32"]]; + ["optkey";["OptionalType";["DataType";"Int32"]]]; + ["value";["DataType";"String"]]] + ]; +}} diff --git a/yql/essentials/tests/sql/suites/window/input_optkey2.txt b/yql/essentials/tests/sql/suites/window/input_optkey2.txt new file mode 100644 index 0000000000..2c9f6066e7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input_optkey2.txt @@ -0,0 +1,5 @@ +{"key"=#; "subkey"=1u; value="000" }; +{"key"=1; "subkey"=2u; value="aaa" }; +{"key"=1; "subkey"=2u; value="bbb" }; +{"key"=1; "subkey"=2u; value="ccc" }; +{"key"=1; "subkey"=2u; value="ddd" }; diff --git a/yql/essentials/tests/sql/suites/window/input_optkey2.txt.attr b/yql/essentials/tests/sql/suites/window/input_optkey2.txt.attr new file mode 100644 index 0000000000..14e8d693aa --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input_optkey2.txt.attr @@ -0,0 +1,7 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["OptionalType";["DataType";"Int32"]]]; + ["subkey";["DataType";"Uint32"]]; + ["value";["DataType";"String"]]] + ]; +}} diff --git a/yql/essentials/tests/sql/suites/window/input_tutorial_users.txt b/yql/essentials/tests/sql/suites/window/input_tutorial_users.txt new file mode 100644 index 0000000000..4a18a0dd29 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/input_tutorial_users.txt @@ -0,0 +1,12 @@ +{"key"="15";"subkey"="213";"value"="Anya"}; +{"key"="25";"subkey"="225";"value"="Petr"}; +{"key"="17";"subkey"="1";"value"="Masha"}; +{"key"="5";"subkey"="225";"value"="Alena"}; +{"key"="23";"subkey"="2";"value"="Irina"}; +{"key"="13";"subkey"="21";"value"="Inna"}; +{"key"="33";"subkey"="125";"value"="Ivan"}; +{"key"="45";"subkey"="225";"value"="Asya"}; +{"key"="27";"subkey"="125";"value"="German"}; +{"key"="41";"subkey"="225";"value"="Olya"}; +{"key"="35";"subkey"="2";"value"="Slava"}; +{"key"="56";"subkey"="2";"value"="Elena"}; diff --git a/yql/essentials/tests/sql/suites/window/lagging/aggregations.cfg b/yql/essentials/tests/sql/suites/window/lagging/aggregations.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/lagging/aggregations.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/lagging/aggregations.sql b/yql/essentials/tests/sql/suites/window/lagging/aggregations.sql new file mode 100644 index 0000000000..bd38e584e8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/lagging/aggregations.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/lagging/aggregations_leadlag.cfg b/yql/essentials/tests/sql/suites/window/lagging/aggregations_leadlag.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/lagging/aggregations_leadlag.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/lagging/aggregations_leadlag.sql b/yql/essentials/tests/sql/suites/window/lagging/aggregations_leadlag.sql new file mode 100644 index 0000000000..f87bf21f0e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/lagging/aggregations_leadlag.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/leading/aggregations.cfg b/yql/essentials/tests/sql/suites/window/leading/aggregations.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/leading/aggregations.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/leading/aggregations.sql b/yql/essentials/tests/sql/suites/window/leading/aggregations.sql new file mode 100644 index 0000000000..9e2fb4090d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/leading/aggregations.sql @@ -0,0 +1,16 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + COUNT(*) over w1 as count1, + ListSort(AGGREGATE_LIST_DISTINCT(subkey) over w1) as agglist_distinct1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/leading/aggregations_leadlag.cfg b/yql/essentials/tests/sql/suites/window/leading/aggregations_leadlag.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/leading/aggregations_leadlag.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/leading/aggregations_leadlag.sql b/yql/essentials/tests/sql/suites/window/leading/aggregations_leadlag.sql new file mode 100644 index 0000000000..23f6c5ae87 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/leading/aggregations_leadlag.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + LEAD(value || value, 3) over w1 as dvalue_lead1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + LAG(cast(value as uint32)) over w2 as value_lag2, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 3 FOLLOWING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/lib1.sql.txt b/yql/essentials/tests/sql/suites/window/lib1.sql.txt new file mode 100644 index 0000000000..be9eb62cb7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/lib1.sql.txt @@ -0,0 +1,5 @@ +DEFINE SUBQUERY $subq() AS + SELECT key, subkey, value, DENSE_RANK() OVER w AS r FROM plato.Input WINDOW w AS (PARTITION BY key ORDER BY subkey); +END DEFINE; + +EXPORT $subq; diff --git a/yql/essentials/tests/sql/suites/window/mixed/aggregations.cfg b/yql/essentials/tests/sql/suites/window/mixed/aggregations.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/mixed/aggregations.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/mixed/aggregations.sql b/yql/essentials/tests/sql/suites/window/mixed/aggregations.sql new file mode 100644 index 0000000000..d54d034301 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/mixed/aggregations.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ + +SELECT + value, + + SUM(unwrap(cast(subkey as uint32))) over w1 as sum1, + AGGREGATE_LIST(subkey) over w1 as agglist1, + + SUM(cast(subkey as uint32)) over w2 as sum2, + AGGREGATE_LIST(subkey) over w2 as agglist2, + +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w2 as (PARTITION BY key ORDER BY value ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) +ORDER BY value; diff --git a/yql/essentials/tests/sql/suites/window/multiaggr_subq.sql.txt b/yql/essentials/tests/sql/suites/window/multiaggr_subq.sql.txt new file mode 100644 index 0000000000..0c1d4f8b40 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/multiaggr_subq.sql.txt @@ -0,0 +1,11 @@ +import agg_factory symbols $factory; +use plato; + +define subquery $multiaggr_win() as + select + MULTI_AGGREGATE_BY(AsStruct(subkey as a,value as b), $factory) over w as ma, + ROW_NUMBER() over w as rn + from Input window w as (order by key); +end define; + +export $multiaggr_win; diff --git a/yql/essentials/tests/sql/suites/window/null_type.sql b/yql/essentials/tests/sql/suites/window/null_type.sql new file mode 100644 index 0000000000..ea743b19aa --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/null_type.sql @@ -0,0 +1,25 @@ +/* syntax version 1 */ +/* postgres can not */ +select + min(x) over w, + count(x) over w, + count(*) over w, + aggregate_list_distinct(x) over w, + aggregate_list(x) over w, + bool_and(x) over w +from ( + select null as x union all select Null as x +) +window w as (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); + +select + min(x) over w, + count(x) over w, + count(*) over w, + aggregate_list_distinct(x) over w, + aggregate_list(x) over w, + bool_and(x) over w +from ( + select null as x union all select Null as x +) +window w as (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING); diff --git a/yql/essentials/tests/sql/suites/window/p_int32.json b/yql/essentials/tests/sql/suites/window/p_int32.json new file mode 100644 index 0000000000..18d17df026 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/p_int32.json @@ -0,0 +1 @@ +"3" diff --git a/yql/essentials/tests/sql/suites/window/presort_window_order_by_table.sql b/yql/essentials/tests/sql/suites/window/presort_window_order_by_table.sql new file mode 100644 index 0000000000..a635674af9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/presort_window_order_by_table.sql @@ -0,0 +1,16 @@ +/* postgres can not */ +use plato; + +$list = AsList( + AsList(3,1), + AsList(1,1), + AsList(1), + ); + +insert into @foo +select x from (select $list as x) +flatten by x; +commit; +select x,row_number() over w as r from @foo +window w as (order by x asc); + diff --git a/yql/essentials/tests/sql/suites/window/presort_window_partition_by_mem.sql b/yql/essentials/tests/sql/suites/window/presort_window_partition_by_mem.sql new file mode 100644 index 0000000000..6f5b6dc5ae --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/presort_window_partition_by_mem.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +SELECT + key, row_number() over w +FROM (SELECT AsList("a") as key, "z" as value) +WINDOW w AS (partition by key order by value); diff --git a/yql/essentials/tests/sql/suites/window/presort_window_partition_by_table.sql b/yql/essentials/tests/sql/suites/window/presort_window_partition_by_table.sql new file mode 100644 index 0000000000..402496130b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/presort_window_partition_by_table.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +use plato; +SELECT + key, row_number() over w +FROM (SELECT AsList(key) as key, value from Input) +WINDOW w AS (partition by key order by value) +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/window/rank/nulls.sql b/yql/essentials/tests/sql/suites/window/rank/nulls.sql new file mode 100644 index 0000000000..7feafa67ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/nulls.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA warning("disable", "4520"); +PRAGMA AnsiRankForNullableKeys; + +SELECT + key, + RANK() over w1 as r1, + DENSE_RANK() over w1 as r2, +FROM AS_TABLE([<|key:1|>, <|key:null|>, <|key:null|>, <|key:1|>, <|key:2|>]) +WINDOW + w1 as (ORDER BY key ROWS BETWEEN UNBOUNDED PRECEDING AND 5 PRECEDING) +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/window/rank/nulls_legacy.sql b/yql/essentials/tests/sql/suites/window/rank/nulls_legacy.sql new file mode 100644 index 0000000000..a9db1c3400 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/nulls_legacy.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA warning("disable", "4520"); +PRAGMA DisableAnsiRankForNullableKeys; + +SELECT + key, + RANK() over w1 as r1, + DENSE_RANK() over w1 as r2, +FROM AS_TABLE([<|key:1|>, <|key:null|>, <|key:null|>, <|key:1|>, <|key:2|>]) +WINDOW + w1 as (ORDER BY key ROWS BETWEEN UNBOUNDED PRECEDING AND 5 PRECEDING) +ORDER BY key; diff --git a/yql/essentials/tests/sql/suites/window/rank/opt.cfg b/yql/essentials/tests/sql/suites/window/rank/opt.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/opt.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/rank/opt.sql b/yql/essentials/tests/sql/suites/window/rank/opt.sql new file mode 100644 index 0000000000..3260ffb6ea --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/opt.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA warning("disable", "4520"); +PRAGMA AnsiRankForNullableKeys; + +SELECT + key, + subkey, + RANK() over w1 as r1, + DENSE_RANK() over w1 as r2, + RANK(subkey) over w1 as r3, + DENSE_RANK(subkey) over w1 as r4, + + RANK() over w2 as r5, + DENSE_RANK() over w2 as r6, + RANK(subkey || subkey) over w2 as r7, + DENSE_RANK(subkey || subkey) over w2 as r8, +FROM (SELECT cast(key as uint32) as key, subkey, value FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY subkey ROWS BETWEEN UNBOUNDED PRECEDING AND 5 PRECEDING), + w2 as ( ORDER BY key, subkey ROWS BETWEEN 3 FOLLOWING AND 2 FOLLOWING) +ORDER BY key,subkey; diff --git a/yql/essentials/tests/sql/suites/window/rank/plain.cfg b/yql/essentials/tests/sql/suites/window/rank/plain.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/plain.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/rank/plain.sql b/yql/essentials/tests/sql/suites/window/rank/plain.sql new file mode 100644 index 0000000000..dee09aefe6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/plain.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA warning("disable", "4520"); +PRAGMA AnsiRankForNullableKeys; + +SELECT + key, + subkey, + RANK() over w1 as r1, + DENSE_RANK() over w1 as r2, + RANK(subkey) over w1 as r3, + DENSE_RANK(subkey) over w1 as r4, + + RANK() over w2 as r5, + DENSE_RANK() over w2 as r6, + RANK(subkey || subkey) over w2 as r7, + DENSE_RANK(subkey || subkey) over w2 as r8, +FROM (SELECT * FROM plato.Input WHERE key = '1') +WINDOW + w1 as (PARTITION BY key ORDER BY subkey ROWS BETWEEN UNBOUNDED PRECEDING AND 5 PRECEDING), + w2 as ( ORDER BY key, subkey ROWS BETWEEN 3 FOLLOWING AND 2 FOLLOWING) +ORDER BY key,subkey; diff --git a/yql/essentials/tests/sql/suites/window/rank/unordered.cfg b/yql/essentials/tests/sql/suites/window/rank/unordered.cfg new file mode 100644 index 0000000000..000848a05d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/unordered.cfg @@ -0,0 +1 @@ +in Input input5.txt diff --git a/yql/essentials/tests/sql/suites/window/rank/unordered.sql b/yql/essentials/tests/sql/suites/window/rank/unordered.sql new file mode 100644 index 0000000000..c8213f505e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/rank/unordered.sql @@ -0,0 +1,20 @@ +/* syntax version 1 */ +/* postgres can not */ + +PRAGMA warning("disable", "4520"); +PRAGMA warning("disable", "4521"); +PRAGMA AnsiRankForNullableKeys; + +SELECT + key, + subkey, + RANK() over w1 as r1, + DENSE_RANK() over w1 as r2, + + RANK() over w2 as r3, + DENSE_RANK() over w2 as r4, +FROM plato.Input +WINDOW + w1 as (PARTITION BY key ROWS BETWEEN UNBOUNDED PRECEDING AND 5 PRECEDING), + w2 as ( ROWS BETWEEN 3 FOLLOWING AND 2 FOLLOWING) +ORDER BY key,subkey; diff --git a/yql/essentials/tests/sql/suites/window/row_number_no_part_from_subq.sql b/yql/essentials/tests/sql/suites/window/row_number_no_part_from_subq.sql new file mode 100644 index 0000000000..1b767deceb --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/row_number_no_part_from_subq.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +USE plato; + +SELECT key, ROW_NUMBER() OVER w AS row_num +FROM (select * from Input where key != "020") +WINDOW w AS (); diff --git a/yql/essentials/tests/sql/suites/window/row_number_no_part_multi_input.sql b/yql/essentials/tests/sql/suites/window/row_number_no_part_multi_input.sql new file mode 100644 index 0000000000..df8eebf750 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/row_number_no_part_multi_input.sql @@ -0,0 +1,25 @@ +/* postgres can not */ +USE plato; + +insert into @foo select * from Input; + +commit; + +$input = ( + select * from Input where key != "020" + union all + select * from @foo + union all + select * from Input +); + +$output = SELECT key, ROW_NUMBER() OVER w AS row_num +FROM $input +WINDOW w AS (); + +select + min(key) as min_key, + count(distinct row_num) as dist_rn, + min(row_num) as min_rn, + max(row_num) as max_rn, +from $output; diff --git a/yql/essentials/tests/sql/suites/window/row_number_to_map.sql b/yql/essentials/tests/sql/suites/window/row_number_to_map.sql new file mode 100644 index 0000000000..914b36e574 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/row_number_to_map.sql @@ -0,0 +1,18 @@ +/* postgres can not */ +/* syntax version 1 */ + +USE plato; + +SELECT key, subkey, + ROW_NUMBER() OVER w1 AS rn1, + ROW_NUMBER() OVER w2 AS rn2, + COUNT(*) OVER w2 AS w2_cnt, + ROW_NUMBER() OVER w3 AS rn3, + ROW_NUMBER() OVER w4 AS rn4, +FROM Input +WINDOW + w1 AS (), + w2 AS (ORDER BY subkey ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w3 AS (ORDER BY subkey DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), + w4 AS (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) +ORDER BY subkey; diff --git a/yql/essentials/tests/sql/suites/window/row_number_to_map_multiple.sql b/yql/essentials/tests/sql/suites/window/row_number_to_map_multiple.sql new file mode 100644 index 0000000000..979699b5af --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/row_number_to_map_multiple.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* syntax version 1 */ + +USE plato; + +SELECT key, subkey, + ROW_NUMBER() OVER w1 AS rn1, + ROW_NUMBER() OVER w2 AS rn2, + ROW_NUMBER() OVER w3 AS rn3, +FROM Input +WINDOW + w1 AS (), + w2 AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w3 AS (ORDER BY subkey DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) +ORDER BY subkey; diff --git a/yql/essentials/tests/sql/suites/window/row_number_to_map_noncompact.sql b/yql/essentials/tests/sql/suites/window/row_number_to_map_noncompact.sql new file mode 100644 index 0000000000..7d870be944 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/row_number_to_map_noncompact.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* syntax version 1 */ + +USE plato; + +SELECT + ROW_NUMBER() OVER w AS rn, + COUNT(*) OVER w AS cnt, +FROM Input +WINDOW + w AS () +ORDER BY rn; diff --git a/yql/essentials/tests/sql/suites/window/session1.txt b/yql/essentials/tests/sql/suites/window/session1.txt new file mode 100644 index 0000000000..632c7ed4f7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/session1.txt @@ -0,0 +1,26 @@ +{"user"=#; "ts"=#; payload=1 }; +{"user"="u1"; "ts"=#; payload=2 }; + +{"user"=#; "ts"=#; payload=3 }; +{"user"="u1"; "ts"=#; payload=4 }; + +{"user"=#; "ts"=1; payload=5 }; +{"user"="u1"; "ts"=2; payload=6 }; + +{"user"=#; "ts"=2; payload=7 }; +{"user"="u1"; "ts"=3; payload=8 }; + +{"user"=#; "ts"=3; payload=9 }; +{"user"="u1"; "ts"=4; payload=10 }; + +{"user"=#; "ts"=10; payload=11 }; +{"user"="u1"; "ts"=11; payload=12 }; + +{"user"=#; "ts"=21; payload=13 }; +{"user"="u1"; "ts"=22; payload=14 }; + +{"user"=#; "ts"=31; payload=15 }; +{"user"="u1"; "ts"=32; payload=16 }; + +{"user"=#; "ts"=50; payload=17 }; +{"user"="u1"; "ts"=51; payload=18 }; diff --git a/yql/essentials/tests/sql/suites/window/session1.txt.attr b/yql/essentials/tests/sql/suites/window/session1.txt.attr new file mode 100644 index 0000000000..0df1a044c1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/session1.txt.attr @@ -0,0 +1,7 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["user";["OptionalType";["DataType";"String"]]]; + ["ts";["OptionalType";["DataType";"Int32"]]]; + ["payload";["DataType";"Int32"]]] + ]; +}} diff --git a/yql/essentials/tests/sql/suites/window/table_funcs_spec_win_func.sqlx b/yql/essentials/tests/sql/suites/window/table_funcs_spec_win_func.sqlx new file mode 100644 index 0000000000..41e8728051 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/table_funcs_spec_win_func.sqlx @@ -0,0 +1,10 @@ +/* postgres can not */ +use plato; + +insert into Output +select + key, + max(TablePath()) over w as table_rec +from Input +window w as (order by key) +; diff --git a/yql/essentials/tests/sql/suites/window/table_funcs_spec_with_win_func.sqlx b/yql/essentials/tests/sql/suites/window/table_funcs_spec_with_win_func.sqlx new file mode 100644 index 0000000000..ab63366f15 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/table_funcs_spec_with_win_func.sqlx @@ -0,0 +1,6 @@ +/* postgres can not */ +select + key, TablePath() as tab_path, ROW_NUMBER() over w1 +from plato.Input +window w1 as (order by key) +order by key; diff --git a/yql/essentials/tests/sql/suites/window/udaf_no_merge.sql b/yql/essentials/tests/sql/suites/window/udaf_no_merge.sql new file mode 100644 index 0000000000..b9fe5fb2e2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/udaf_no_merge.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* syntax version 1 */ +$f = AggregationFactory( + "UDAF", + ($item, $_) -> ($item), + ($state, $item, $_) -> ($state), + null, + ($state) -> ($state) +); + +select aggregate_by(x,$f) over (order by x) from (values (1),(2),(3)) as a(x);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/udaf_window.cfg b/yql/essentials/tests/sql/suites/window/udaf_window.cfg new file mode 100644 index 0000000000..289c4251bb --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/udaf_window.cfg @@ -0,0 +1,2 @@ +in Input input.txt +udf python3_udf diff --git a/yql/essentials/tests/sql/suites/window/udaf_window.sql b/yql/essentials/tests/sql/suites/window/udaf_window.sql new file mode 100644 index 0000000000..34f9f56c6a --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/udaf_window.sql @@ -0,0 +1,51 @@ +/* postgres can not */ +/* syntax version 1 */ +$script = @@ +import heapq +import json + +N_SMALLEST = 3 + +def create(item): + return [item] + +def add(state, item): + heapq.heappush(state, item) + return heapq.nsmallest(N_SMALLEST, state) + +def merge(state_a, state_b): + merged = heapq.merge(state_a, state_b) + return heapq.nsmallest(N_SMALLEST, merged) + +def get_result(state): + result = heapq.nsmallest(N_SMALLEST, state) + return '%d smallest items: %s' % ( + N_SMALLEST, + ', '.join(map(str, result)) + ) + +def serialize(state): + return json.dumps(state) + +def deserialize(serialized): + return json.loads(serialized) +@@; + +$create = Python3::create(Callable<(Double)->Resource<Python3>>, $script); +$add = Python3::add(Callable<(Resource<Python3>,Double)->Resource<Python3>>, $script); +$merge = Python3::merge(Callable<(Resource<Python3>,Resource<Python3>)->Resource<Python3>>, $script); +$get_result = Python3::get_result(Callable<(Resource<Python3>)->String>, $script); +$serialize = Python3::serialize(Callable<(Resource<Python3>)->String>, $script); +$deserialize = Python3::deserialize(Callable<(String)->Resource<Python3>>, $script); + +SELECT UDAF( + CAST(key AS Double), + $create, + $add, + $merge, + $get_result, + $serialize, + $deserialize +) OVER w +FROM plato.Input +WINDOW w AS (ORDER by value); diff --git a/yql/essentials/tests/sql/suites/window/udaf_with_def_value.sql b/yql/essentials/tests/sql/suites/window/udaf_with_def_value.sql new file mode 100644 index 0000000000..a68fdd16a3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/udaf_with_def_value.sql @@ -0,0 +1,65 @@ +pragma warning("disable", "4520"); + +$my_table = +SELECT 1 AS id, 1 AS ts, 4 AS value1 +UNION ALL +SELECT 2 AS id, 1 AS ts, NULL AS value1 +UNION ALL +SELECT 1 AS id, 2 AS ts, 4 AS value1 +UNION ALL +SELECT 3 AS id, 2 AS ts, 40 AS value1 +UNION ALL +SELECT 3 AS id, 5 AS ts, 2 AS value1 +UNION ALL +SELECT 3 AS id, 10 AS ts, 40 AS value1 +; + +$cnt_create = ($_item, $_parent) -> { return 1ul }; +$cnt_add = ($state, $_item, $_parent) -> { return 1ul + $state }; +$cnt_merge = ($state1, $state2) -> { return $state1 + $state2 }; +$cnt_get_result = ($state) -> { return $state }; +$cnt_serialize = ($state) -> { return $state }; +$cnt_deserialize = ($state) -> { return $state }; +-- non-trivial default value +$cnt_default = 0.0; + +$cnt_udaf_factory = AggregationFactory( + "UDAF", + $cnt_create, + $cnt_add, + $cnt_merge, + $cnt_get_result, + $cnt_serialize, + $cnt_deserialize, + $cnt_default +); + + +SELECT + id + , ts + , value1 + , AGGREGATE_BY(value1, $cnt_udaf_factory) OVER lagging AS lagging_opt + , AGGREGATE_BY(value1, $cnt_udaf_factory) OVER generic AS generic_opt + + , AGGREGATE_BY(ts, $cnt_udaf_factory) OVER lagging AS lagging + , AGGREGATE_BY(ts, $cnt_udaf_factory) OVER generic AS generic + + , AGGREGATE_BY(value1, $cnt_udaf_factory) OVER empty AS empty_opt + , AGGREGATE_BY(ts, $cnt_udaf_factory) OVER empty AS empty + +FROM $my_table +WINDOW lagging AS ( + ORDER BY ts, id + ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING +) +, generic AS ( + ORDER BY ts, id + ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING +) +, empty AS ( + ORDER BY ts, id + ROWS BETWEEN 10 FOLLOWING AND 1 FOLLOWING +) +ORDER BY ts, id; + diff --git a/yql/essentials/tests/sql/suites/window/win_aggregate_check01.sqlx b/yql/essentials/tests/sql/suites/window/win_aggregate_check01.sqlx new file mode 100644 index 0000000000..ffc03289d4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_aggregate_check01.sqlx @@ -0,0 +1,24 @@ +/* postgres can not */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + prefix, + region, + region + 2 as region_2, + age, -- use direct value in aggregation impossible + avg(age) as avg_age, + sum(age) as sum_age, + sum(avg(age)) over w1 as sum_by_avg_age, + lag(region) over w1 as prev_region, + some(prefix) over w1 as prefix_list, + lag(prefix) over w1 as prev_prefix_list, + lag(some(name)) over w1 as prev_region_list, + 'test' +from $data +group by region, SUBSTRING(name,0,1) as prefix +window w1 as (partition by region order by avg(age) desc) +order by region, avg_age desc +; diff --git a/yql/essentials/tests/sql/suites/window/win_aggregate_check02.sqlx b/yql/essentials/tests/sql/suites/window/win_aggregate_check02.sqlx new file mode 100644 index 0000000000..46ee384a24 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_aggregate_check02.sqlx @@ -0,0 +1,23 @@ +/* postgres can not */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + prefix, + region, + region + 2 as region_2, + avg(age) as avg_age, + sum(age) as sum_age, + sum(avg(age)) over w1 as sum_by_avg_age, + lag(region) over w1 as prev_region, + some(prefix) over w1 as prefix_list, + lag(prefix) over w1 as prev_prefix_list, + lag(some(name)) over w1 as prev_region_list, + 'test' +from $data +group by region, SUBSTRING(name,0,1) as prefix +window w1 as (partition by region order by age desc) -- use unaggregaded age in order by in win specification +order by region, avg_age desc +; diff --git a/yql/essentials/tests/sql/suites/window/win_by_all_aggregate.cfg b/yql/essentials/tests/sql/suites/window/win_by_all_aggregate.cfg new file mode 100644 index 0000000000..0dba2231fb --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_by_all_aggregate.cfg @@ -0,0 +1,4 @@ +in Input input_tutorial_users.txt +udf histogram_udf +udf math_udf +udf stat_udf diff --git a/yql/essentials/tests/sql/suites/window/win_by_all_aggregate.sql b/yql/essentials/tests/sql/suites/window/win_by_all_aggregate.sql new file mode 100644 index 0000000000..d144fc095d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_by_all_aggregate.sql @@ -0,0 +1,38 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +-- insert into Output +select + region, + name, + sum(age) over w1 as sum, + min(age) over w1 as min, + max(age) over w1 as max, + count(age) over w1 as count, + count(*) over w1 as count_all, + count_if(age>20) over w1 as count_if, + some(age) over w1 as some, + bit_and(age) over w1 as bit_and, + bit_or(age) over w1 as bit_or, + bit_xor(age) over w1 as bit_xor, + bool_and(age>20) over w1 as bool_and, + bool_or(age>20) over w1 as bool_or, + avg(age) over w1 as avg, + aggr_list(age) over w1 as `list`, + min_by(age, name) over w1 as min_by, + max_by(age, name) over w1 as max_by, + nanvl(variance(age) over w1, -999.0) as variance, + nanvl(stddev(age) over w1, -999.0) as stddev, + nanvl(populationvariance(age) over w1, -999.0) as popvar, + nanvl(stddevpopulation(age) over w1, -999.0) as popstddev, + histogram(age) over w1 as hist, + median(age) over w1 as median, + percentile(age, 0.9) over w1 as perc90, + aggregate_by(age, aggregation_factory("count")) over w1 as aggby +from $data +window w1 as (partition by region order by name desc) +order by region, name desc +; diff --git a/yql/essentials/tests/sql/suites/window/win_by_all_avg_interval.sql b/yql/essentials/tests/sql/suites/window/win_by_all_avg_interval.sql new file mode 100644 index 0000000000..c2ff089f9e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_by_all_avg_interval.sql @@ -0,0 +1,19 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +-- insert into Output +$data2 = (select + region, + name, + avg(CAST(age as Interval)) over w1 as avg_age, +from $data +window w1 as (partition by region order by name desc) +); + +discard select + EnsureType(avg_age, Interval?) as avg_age +from $data2 +; diff --git a/yql/essentials/tests/sql/suites/window/win_by_all_percentile_interval.sql b/yql/essentials/tests/sql/suites/window/win_by_all_percentile_interval.sql new file mode 100644 index 0000000000..b7c1cf8a79 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_by_all_percentile_interval.sql @@ -0,0 +1,36 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +$zero = unwrap( cast(0 as Interval) ); + +-- safely cast data to get rid of optionals after cast +$prepared = select + cast(key as Interval) ?? $zero as age + , cast(subkey as uint32) as region + , value as name + from Input; + +-- we want to check both optional<interval> and plain interval +$data = (select + age + , just(age) as age_opt + , region + , name + from $prepared); + +$data2 = (select + region, + name, + percentile(age, 0.8) over w1 as age_p80, + percentile(age_opt, 0.8) over w1 as age_opt_p80, +from $data +window w1 as (partition by region order by name desc) +); + +select + EnsureType(age_p80, Interval) as age_p80 + , EnsureType(age_opt_p80, Interval?) as age_opt_p80 +from $data2 +; + diff --git a/yql/essentials/tests/sql/suites/window/win_by_simple.sql b/yql/essentials/tests/sql/suites/window/win_by_simple.sql new file mode 100644 index 0000000000..4a34e08cea --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_by_simple.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +SELECT + key, row_number() over w +FROM (SELECT "a" as key, "z" as value) +WINDOW w AS (partition by key order by value); diff --git a/yql/essentials/tests/sql/suites/window/win_expr_bounds.cfg b/yql/essentials/tests/sql/suites/window/win_expr_bounds.cfg new file mode 100644 index 0000000000..10325bfb63 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_expr_bounds.cfg @@ -0,0 +1,2 @@ +in Input4 input4.txt +param $begin p_int32.json diff --git a/yql/essentials/tests/sql/suites/window/win_expr_bounds.sql b/yql/essentials/tests/sql/suites/window/win_expr_bounds.sql new file mode 100644 index 0000000000..64e7387bc6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_expr_bounds.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +declare $begin as Int32; + +select + key, subkey, + COUNT(*) over w as cnt +from Input4 +window + w as (order by key, subkey rows between $begin preceding and 1 + 1 following) +order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/window/win_extract_members.sql b/yql/essentials/tests/sql/suites/window/win_extract_members.sql new file mode 100644 index 0000000000..7785cf9eb9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_extract_members.sql @@ -0,0 +1,21 @@ +/* postgres can not */ +use plato; + +$foo = ( + select key, subkey, value, + sum(cast(subkey as uint32)) over w as sks + from Input + window w as (partition by key order by subkey) +); + +$bar = ( + select key, subkey, + sum(cast(subkey as uint32)) over w as sks, + avg(cast(subkey as uint32)) over w as ska + from Input4 + window w as (partition by key order by subkey) +); + +select key,subkey, value from $foo order by key, subkey; +select key,ska from $bar order by key,ska; + diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func.sql new file mode 100644 index 0000000000..245bbdf5b3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +select + subkey, + 2 * sum(cast(key as uint32)) over w1 as dbl_sum, + count(key) over w1 as c, + min(key) over w1 as mink, + max(key) over w1 as maxk +from plato.Input window w1 as (partition by subkey order by key) +order by subkey, c; diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part.sql new file mode 100644 index 0000000000..6d15ae0b4b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +select + 2 * sum(cast(key as uint32)) over w1 as dbl_sum, + count(key) over w1 as c, + min(key) over w1 as mink, + max(key) over w1 as maxk +from plato.Input window w1 as (order by key desc) +order by c; diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part_sorted.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part_sorted.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part_sorted.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part_sorted.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part_sorted.sql new file mode 100644 index 0000000000..34f8c138f6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_no_part_sorted.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +select + 2 * sum(cast(key as uint32)) over w1 as dbl_sum, + count(key) over w1 as c, + min(key) over w1 as mink, + max(key) over w1 as maxk +from plato.Input +window w1 as (order by key) +order by c; diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort.sql new file mode 100644 index 0000000000..821da3fefd --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +select + subkey, + sum(cast(key as uint32)) over w1 as s, + count(key) over w1 as c, + min(key) over w1 as mink, + max(key) over w1 as maxk +from plato.Input window w1 as (partition by subkey order by key) +order by subkey, c; diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort_desc.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort_desc.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort_desc.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort_desc.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort_desc.sql new file mode 100644 index 0000000000..3553711d4d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_4func_sort_desc.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +select + subkey, + sum(cast(key as uint32)) over w1 as s, + count(key) over w1 as c, + min(key) over w1 as mink, + max(key) over w1 as maxk +from plato.Input window w1 as (partition by subkey order by key desc) +order by subkey, c; diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_hist.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_hist.cfg new file mode 100644 index 0000000000..79b8cf294f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_hist.cfg @@ -0,0 +1,2 @@ +in Input input_intersect.txt +udf histogram_udf diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_hist.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_hist.sql new file mode 100644 index 0000000000..c7c506e390 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_hist.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +select subkey, HISTOGRAM(cast(key as uint32) % 10, 2.) over w1 as hh, count(key) over w1 as c, min(key) over w1 as mink, max(key) over w1 as maxk +from plato.Input +window w1 as (partition by subkey order by key) +order by subkey, c; diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_stat.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_stat.cfg new file mode 100644 index 0000000000..b0a6e1e6b6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_stat.cfg @@ -0,0 +1,2 @@ +in Input input.txt +udf math_udf diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_stat.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_stat.sql new file mode 100644 index 0000000000..7d7ed2bb52 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_stat.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +select + key, subkey, + nanvl(correlation(cast(key as double), cast(subkey as double)) over w, NULL) as corr, + nanvl(covariance(cast(key as double), cast(subkey as double)) over w, -9.9) as covar, + hll(value, 18) over w as hll +from plato.Input +window w as (order by subkey); diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all.cfg new file mode 100644 index 0000000000..144f0dd840 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all.cfg @@ -0,0 +1 @@ +in Input input2.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all.sql new file mode 100644 index 0000000000..976046bcc8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all.sql @@ -0,0 +1,6 @@ +pragma SimpleColumns; +use plato; + +$q = (select CAST(key as Int32) as key, CAST(subkey as Int32) as subkey, value from Input); + +select t.*, sum(subkey) over w as subkey_sum, sum(key) over w from $q as t window w as (partition by key order by value) order by key,subkey; diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all_no_simple_columns.cfg b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all_no_simple_columns.cfg new file mode 100644 index 0000000000..144f0dd840 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all_no_simple_columns.cfg @@ -0,0 +1 @@ +in Input input2.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all_no_simple_columns.sql b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all_no_simple_columns.sql new file mode 100644 index 0000000000..d790cfea62 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_aggr_with_qualified_all_no_simple_columns.sql @@ -0,0 +1,7 @@ +pragma DisableSimpleColumns; + +use plato; + +$q = (select CAST(key as Int32) as key, CAST(subkey as Int32) as subkey, value from Input); + +select t.*, sum(subkey) over w as subkey_sum, sum(key) over w from $q as t window w as (partition by key order by value) order by `t.key`,`t.subkey`; diff --git a/yql/essentials/tests/sql/suites/window/win_func_auto_arg.sql b/yql/essentials/tests/sql/suites/window/win_func_auto_arg.sql new file mode 100644 index 0000000000..4dea9c50b1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_auto_arg.sql @@ -0,0 +1,17 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; +PRAGMA DisableAnsiRankForNullableKeys; + +SELECT + RANK() over w as rank_noarg, + DENSE_RANK() over w as dense_rank_noarg, + RANK(cast(subkey as uint32) / 10 % 2) over w as rank, + DENSE_RANK(cast(subkey as uint32) / 10 % 2) over w as dense_rank, + zz.* +FROM + Input4 as zz +WINDOW + w as (PARTITION BY key ORDER BY subkey) +ORDER BY key, subkey, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_auto_arg_selective_rank.sql b/yql/essentials/tests/sql/suites/window/win_func_auto_arg_selective_rank.sql new file mode 100644 index 0000000000..aae50e65f4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_auto_arg_selective_rank.sql @@ -0,0 +1,18 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; +PRAGMA DisableSimpleColumns; +PRAGMA DisableAnsiRankForNullableKeys; + +--INSERT INTO Output +SELECT + RANK() over w as rank_noarg, + DENSE_RANK() over w as dense_rank_noarg, + RANK(AsTuple(key, value)) over w as rank, + DENSE_RANK(AsTuple(key, value)) over w as dense_rank, + zz.* +FROM + Input4 as zz +WINDOW + w as (ORDER BY key, subkey, value) +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_auto_arg_two_sort.sql b/yql/essentials/tests/sql/suites/window/win_func_auto_arg_two_sort.sql new file mode 100644 index 0000000000..67f6c8cc5f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_auto_arg_two_sort.sql @@ -0,0 +1,18 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; +PRAGMA DisableAnsiRankForNullableKeys; + +--INSERT INTO Output +SELECT + RANK() over w as rank_noarg, + DENSE_RANK() over w as dense_rank_noarg, + RANK(subkey) over w as rank, + DENSE_RANK(subkey) over w as dense_rank, + zz.* +FROM + Input4 as zz +WINDOW + w as (PARTITION BY key ORDER BY subkey, value) +ORDER BY key, subkey, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_cume_dist.sql b/yql/essentials/tests/sql/suites/window/win_func_cume_dist.sql new file mode 100644 index 0000000000..864cfcc6ea --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_cume_dist.sql @@ -0,0 +1,7 @@ +select +r,x, +cume_dist() over w, +from (select * from (values (1,3),(2,null),(3,4),(4,5)) as a(r,x)) as z +window w as (order by r) +order by r + diff --git a/yql/essentials/tests/sql/suites/window/win_func_cume_dist_ansi.sql b/yql/essentials/tests/sql/suites/window/win_func_cume_dist_ansi.sql new file mode 100644 index 0000000000..fd609fd046 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_cume_dist_ansi.sql @@ -0,0 +1,4 @@ +pragma AnsiCurrentRow; +SELECT cume_dist() over w FROM (VALUES (4),(5),(5),(6)) as a(x) +window w as (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING); + diff --git a/yql/essentials/tests/sql/suites/window/win_func_first_last.cfg b/yql/essentials/tests/sql/suites/window/win_func_first_last.cfg new file mode 100644 index 0000000000..1af92ac7be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_first_last.cfg @@ -0,0 +1 @@ +in Input input_intersect_with_holes.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_first_last.sql b/yql/essentials/tests/sql/suites/window/win_func_first_last.sql new file mode 100644 index 0000000000..1e0c82e8fd --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_first_last.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +SELECT + key, + FIRST_VALUE(cast(subkey as uint32)) RESPECT NULLS OVER w1, + FIRST_VALUE(cast(subkey as uint32)) IGNORE NULLS OVER w1, + LAST_VALUE(cast(subkey as uint32)) OVER w1, + LAST_VALUE(cast(subkey as uint32)) IGNORE NULLS OVER w1, + subkey +FROM plato.Input +WINDOW w1 as (ORDER BY key desc, subkey); diff --git a/yql/essentials/tests/sql/suites/window/win_func_first_last_over_nonopt.sql b/yql/essentials/tests/sql/suites/window/win_func_first_last_over_nonopt.sql new file mode 100644 index 0000000000..9303079983 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_first_last_over_nonopt.sql @@ -0,0 +1,19 @@ +/* postgres can not */ +/* syntax version 1 */ +SELECT + key, + + FIRST_VALUE(key) RESPECT NULLS OVER w1, + FIRST_VALUE(key) IGNORE NULLS OVER w1, + LAST_VALUE(key) OVER w1, + LAST_VALUE(key) IGNORE NULLS OVER w1, + + FIRST_VALUE(null) RESPECT NULLS OVER w1, + FIRST_VALUE(null) IGNORE NULLS OVER w1, + LAST_VALUE(null) OVER w1, + LAST_VALUE(null) IGNORE NULLS OVER w1, + + subkey +FROM plato.Input +WINDOW w1 as (ORDER BY subkey ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +ORDER BY subkey; diff --git a/yql/essentials/tests/sql/suites/window/win_func_first_last_rev.cfg b/yql/essentials/tests/sql/suites/window/win_func_first_last_rev.cfg new file mode 100644 index 0000000000..1af92ac7be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_first_last_rev.cfg @@ -0,0 +1 @@ +in Input input_intersect_with_holes.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_first_last_rev.sql b/yql/essentials/tests/sql/suites/window/win_func_first_last_rev.sql new file mode 100644 index 0000000000..ce137c76f3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_first_last_rev.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +SELECT + key, + FIRST_VALUE(cast(subkey as uint32)) OVER w1, + FIRST_VALUE(cast(subkey as uint32)) IGNORE NULLS OVER w1, + LAST_VALUE(cast(subkey as uint32)) RESPECT NULLS OVER w1, + LAST_VALUE(cast(subkey as uint32)) IGNORE NULLS OVER w1, + subkey +FROM plato.Input +WINDOW w1 as (ORDER BY key asc, subkey); diff --git a/yql/essentials/tests/sql/suites/window/win_func_first_last_with_part.cfg b/yql/essentials/tests/sql/suites/window/win_func_first_last_with_part.cfg new file mode 100644 index 0000000000..1af92ac7be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_first_last_with_part.cfg @@ -0,0 +1 @@ +in Input input_intersect_with_holes.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_first_last_with_part.sql b/yql/essentials/tests/sql/suites/window/win_func_first_last_with_part.sql new file mode 100644 index 0000000000..84de1048c6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_first_last_with_part.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +$input=(select cast(key as int32) / 100 as key_hundred, cast(key as int32) as key, cast(subkey as int32) as subkey, value from plato.Input); + +SELECT + key_hundred, + key, + FIRST_VALUE(cast(subkey as uint32)) RESPECT NULLS OVER w1 as first_res_null, + FIRST_VALUE(cast(subkey as uint32)) IGNORE NULLS OVER w1 as first_esc_null, + LAST_VALUE(cast(subkey as uint32)) OVER w1 as last_res_null, + LAST_VALUE(cast(subkey as uint32)) IGNORE NULLS OVER w1 as last_esc_null, + subkey +FROM $input +WINDOW w1 as (PARTITION BY key_hundred ORDER BY key) +ORDER BY key_hundred, key, subkey; diff --git a/yql/essentials/tests/sql/suites/window/win_func_in_lib.cfg b/yql/essentials/tests/sql/suites/window/win_func_in_lib.cfg new file mode 100644 index 0000000000..b6318845f7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_in_lib.cfg @@ -0,0 +1,2 @@ +file lib1.sql lib1.sql.txt +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_in_lib.sql b/yql/essentials/tests/sql/suites/window/win_func_in_lib.sql new file mode 100644 index 0000000000..3c6dd9719c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_in_lib.sql @@ -0,0 +1,6 @@ +USE plato; +PRAGMA library('lib1.sql'); + +IMPORT lib1 symbols $subq; + +SELECT * FROM $subq(); diff --git a/yql/essentials/tests/sql/suites/window/win_func_into_udf.cfg b/yql/essentials/tests/sql/suites/window/win_func_into_udf.cfg new file mode 100644 index 0000000000..061efc4f73 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_into_udf.cfg @@ -0,0 +1,2 @@ +in Input input4.txt +udf math_udf diff --git a/yql/essentials/tests/sql/suites/window/win_func_into_udf.sql b/yql/essentials/tests/sql/suites/window/win_func_into_udf.sql new file mode 100644 index 0000000000..45083bc46d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_into_udf.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +SELECT + key, Math::Sqrt(CAST(row_number() over w as double)) as sq +FROM plato.Input +WINDOW w AS (partition by key order by subkey) +ORDER BY key, sq; diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_opt.cfg b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_opt.cfg new file mode 100644 index 0000000000..f0840473ba --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_opt.cfg @@ -0,0 +1,2 @@ +in Input input.txt +in InputOpt input_optkey1.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_opt.sql b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_opt.sql new file mode 100644 index 0000000000..192103f030 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_opt.sql @@ -0,0 +1,23 @@ +/* postgres can not */ +use plato; + +select + subkey, + lag(Just(subkey)) over w as opt_lag, + lead(Just(subkey)) over w as opt_lead, + lag(subkey, 0) over w as lag0, + lead(subkey, 0) over w as lead0 +from Input window w as () +order by subkey; + + +select + key, + lag(optkey) over w as opt_lag, + lead(Just(optkey)) over w as opt_lead, + lag(Just(optkey), 0) over w as lag0, + lead(optkey, 0) over w as lead0 +from InputOpt window w as () +order by key; + +select lead(null) over w from (select 1 as key) window w as (); diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm.cfg b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm.sql b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm.sql new file mode 100644 index 0000000000..4400f070f2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm.sql @@ -0,0 +1,21 @@ +/* postgres can not */ +use plato; + +$input=(select cast(key as int32) as key, cast(subkey as int32) as subkey, value from Input); + +--insert into Output +select + lead(key, 3) over w as keyL, + lead(key, 2) over w as keyM, + lead(key, 1) over w as keyN, + key as keyO, + lag(key, 1) over w as key_1, + lag(key, 2) over w as key_2, + lag(key, 3) over w as key_3, + lag(key, 4) over w as key_4, + lag(key, 5) over w as key_5, + value +from $input +window w as (order by key, value) +order by keyO, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part.cfg b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part.sql b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part.sql new file mode 100644 index 0000000000..509cd93f05 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part.sql @@ -0,0 +1,22 @@ +/* postgres can not */ +use plato; + +$input=(select cast(key as int32) / 100 as key_hundred, cast(key as int32) as key, cast(subkey as int32) as subkey, value from Input); + +--insert into Output +select + key_hundred as a_part, + lead(key, 3) over w as keyL, + lead(key, 2) over w as keyM, + lead(key, 1) over w as keyN, + key as keyO, + lag(key, 1) over w as key_1, + lag(key, 2) over w as key_2, + lag(key, 3) over w as key_3, + lag(key, 4) over w as key_4, + lag(key, 5) over w as key_5, + value +from $input +window w as (partition by key_hundred order by key, value) +order by a_part, keyO, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part_other.cfg b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part_other.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part_other.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part_other.sql b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part_other.sql new file mode 100644 index 0000000000..1da77dd086 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_lead_lag_worm_with_part_other.sql @@ -0,0 +1,22 @@ +/* postgres can not */ +use plato; + +$input=(select cast(key as int32) % 4 as key_quad, cast(key as int32) as key, cast(subkey as int32) as subkey, value from Input); + +--insert into Output +select + key_quad as a_part, + lead(key, 3) over w as keyL, + lead(key, 2) over w as keyM, + lead(key, 1) over w as keyN, + key as keyO, + lag(key, 1) over w as key_1, + lag(key, 2) over w as key_2, + lag(key, 3) over w as key_3, + lag(key, 4) over w as key_4, + lag(key, 5) over w as key_5, + value +from $input +window w as (partition by key_quad order by key, value) +order by a_part, keyO, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_mutable_resource.sql b/yql/essentials/tests/sql/suites/window/win_func_mutable_resource.sql new file mode 100644 index 0000000000..74892a5a56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_mutable_resource.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +select median(x) over w,median(x) over w from ( +select x, 0 as y from (select AsList(1,2,3,4,5,6,7,8,9,10) as x) flatten by x +) +window w as (order by y) diff --git a/yql/essentials/tests/sql/suites/window/win_func_nth_value.sql b/yql/essentials/tests/sql/suites/window/win_func_nth_value.sql new file mode 100644 index 0000000000..a8e99c6201 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_nth_value.sql @@ -0,0 +1,14 @@ +select +r,x, +nth_value(x,1) over w as nr1, +nth_value(x,1) ignore nulls over w as ni1, +nth_value(x,2) over w as nr2, +nth_value(x,2) ignore nulls over w as ni2, +nth_value(x,3) over w as nr3, +nth_value(x,3) ignore nulls over w as ni3, +nth_value(x,4) over w as nr4, +nth_value(x,4) ignore nulls over w as ni4 +from (values (1,3),(2,null),(3,4),(4,5)) as a(r,x) +window w as (order by r) +order by r + diff --git a/yql/essentials/tests/sql/suites/window/win_func_ntile.sql b/yql/essentials/tests/sql/suites/window/win_func_ntile.sql new file mode 100644 index 0000000000..86b622e239 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_ntile.sql @@ -0,0 +1,7 @@ +select +r,x, +ntile(3) over w, +from (select * from (values (1,3),(2,null),(3,4),(4,5)) as a(r,x)) as z +window w as (order by r) +order by r + diff --git a/yql/essentials/tests/sql/suites/window/win_func_on_cloned_source.sql b/yql/essentials/tests/sql/suites/window/win_func_on_cloned_source.sql new file mode 100644 index 0000000000..a4cd401589 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_on_cloned_source.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +USE plato; + +$sub_raws = (SELECT ROW_NUMBER() OVER trivialWindow as RowNum +FROM Input WINDOW trivialWindow As () +); + +--INSERT INTO Output +SELECT + * +FROM $sub_raws; + +SELECT + * +FROM $sub_raws; diff --git a/yql/essentials/tests/sql/suites/window/win_func_order_by_udf_empty_rank.cfg b/yql/essentials/tests/sql/suites/window/win_func_order_by_udf_empty_rank.cfg new file mode 100644 index 0000000000..144f0dd840 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_order_by_udf_empty_rank.cfg @@ -0,0 +1 @@ +in Input input2.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_order_by_udf_empty_rank.sql b/yql/essentials/tests/sql/suites/window/win_func_order_by_udf_empty_rank.sql new file mode 100644 index 0000000000..6508a8c827 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_order_by_udf_empty_rank.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ +PRAGMA DisableAnsiRankForNullableKeys; + +USE plato; + +SELECT + key, + RANK() over w as ix, + subkey, + String::Base64Encode(subkey) as subkey_enc +FROM Input +WINDOW w AS ( + PARTITION BY key + ORDER BY String::Base64Encode(subkey) DESC +) +ORDER BY key, ix; diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by.cfg b/yql/essentials/tests/sql/suites/window/win_func_over_group_by.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by.sql b/yql/essentials/tests/sql/suites/window/win_func_over_group_by.sql new file mode 100644 index 0000000000..2766549b6e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by.sql @@ -0,0 +1,17 @@ +/* postgres can not */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + prefix, + region, + avg(age) as avg_age, + sum(avg(age)) over w1 as sum_by_avg_age +from $data +group by region, SUBSTRING(name,0,1) as prefix +-- how to use single avg_age? +window w1 as (partition by region order by avg(age)) +order by region, avg_age +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_compl.cfg b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_compl.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_compl.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_compl.sql b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_compl.sql new file mode 100644 index 0000000000..fda60ad550 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_compl.sql @@ -0,0 +1,25 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + prefix, + region, + region + 2 as region_2, + --age, + avg(age) as avg_age, + sum(age) as sum_age, + sum(avg(age)) over w1 as sum_by_avg_age, + lag(region) over w1 as prev_region, + lag(aggr_list(region)) over w1 as prev_region_list, + 'test' +from $data +group by region, SUBSTRING(name,0,1) as prefix +-- how to use single avg_age? +window w1 as (partition by region order by avg(age) desc) +--window w1 as (order by avg(age) desc) +order by region, avg_age desc +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names.cfg b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names.cfg new file mode 100644 index 0000000000..582479bcde --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names.cfg @@ -0,0 +1 @@ +in Input input_tutorial_users.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names.sql b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names.sql new file mode 100644 index 0000000000..d6ec520dae --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names.sql @@ -0,0 +1,27 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + prefix, + region, + region + 2 as region_2, + --age, + avg(age) as avg_age, + sum(age) as sum_age, + sum(avg(age)) over w1 as sum_by_avg_age, + lag(region) over w1 as prev_region, + aggr_list(prefix) over w1 as prefix_list, + lag(prefix) over w1 as prev_prefix_list, + lag(aggr_list(name)) over w1 as prev_region_list, + 'test' +from $data +group by region, SUBSTRING(name,0,1) as prefix +-- how to use single avg_age? +window w1 as (partition by region order by avg(age) desc, prefix) +--window w1 as (order by avg(age) desc) +order by region, avg_age desc, prefix +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names_order_prefix.cfg b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names_order_prefix.cfg new file mode 100644 index 0000000000..582479bcde --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names_order_prefix.cfg @@ -0,0 +1 @@ +in Input input_tutorial_users.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names_order_prefix.sql b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names_order_prefix.sql new file mode 100644 index 0000000000..3050750f9b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_list_names_order_prefix.sql @@ -0,0 +1,24 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + prefix, + region, + region + 2 as region_2, + avg(age) as avg_age, + sum(age) as sum_age, + sum(avg(age)) over w1 as sum_by_avg_age, + lag(region) over w1 as prev_region, + aggr_list(prefix) over w1 as prefix_list, + lag(prefix) over w1 as prev_prefix_list, + lag(agg_list(name)) over w1 as prev_region_list, + 'test' +from $data +group by region, SUBSTRING(name,0,1) as prefix +window w1 as (partition by region order by prefix desc) +order by region desc, prefix, sum_by_avg_age +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_with_having.sqlx b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_with_having.sqlx new file mode 100644 index 0000000000..de7ab56543 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_with_having.sqlx @@ -0,0 +1,17 @@ +/* postgres can not */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +insert into Output +select + prefix, + region, + avg(age) as avg_age, + sum(avg(age)) over w1 as sum_by_avg_age +from $data +group by region, SUBSTRING(name,0,1) as prefix +having lag(avg(age)) over w1 > 30 +window w1 as (partition by region order by avg(age)) +order by region, avg_age +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_over_group_by_with_where.sqlx b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_with_where.sqlx new file mode 100644 index 0000000000..27da455931 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_over_group_by_with_where.sqlx @@ -0,0 +1,16 @@ +/* postgres can not */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +insert into Output +select + prefix, + region, + sum(avg(age)) over w1 as sum_by_avg_age +from $data +where lag(avg(age), 2) over w1 > 30 +group by region, SUBSTRING(name,0,1) as prefix +window w1 as (partition by region order by avg(age)) +order by region, avg_age +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_part_by_expr.cfg b/yql/essentials/tests/sql/suites/window/win_func_part_by_expr.cfg new file mode 100644 index 0000000000..582479bcde --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_part_by_expr.cfg @@ -0,0 +1 @@ +in Input input_tutorial_users.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_part_by_expr.sql b/yql/essentials/tests/sql/suites/window/win_func_part_by_expr.sql new file mode 100644 index 0000000000..f101052580 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_part_by_expr.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +use plato; + +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from Input); + +--insert into Output +select + prefix, + region, + name, + sum(age) over w1 as sum1 +from $data +window w1 as (partition by SUBSTRING(name,0,1) as prefix order by name) +order by prefix, region, name; diff --git a/yql/essentials/tests/sql/suites/window/win_func_part_by_expr_new.sql b/yql/essentials/tests/sql/suites/window/win_func_part_by_expr_new.sql new file mode 100644 index 0000000000..cec51e2e40 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_part_by_expr_new.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* syntax version 1 */ +-- YQL-1977 +use plato; + +--insert into Output +select key_mod, aggr_list(value) over w, aggr_list(subkey) over w +from Input window w as (partition by cast(key as uint32) % 10 as key_mod order by subkey) +order by key_mod, column1; diff --git a/yql/essentials/tests/sql/suites/window/win_func_percent_rank.sql b/yql/essentials/tests/sql/suites/window/win_func_percent_rank.sql new file mode 100644 index 0000000000..f249d901e3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_percent_rank.sql @@ -0,0 +1,8 @@ +select +r,x, +percent_rank() over w, +percent_rank(x) over w, +from (select * from (values (1,null),(2,3),(3,4),(4,4)) as a(r,x)) as z +window w as (order by r) +order by r + diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_all.cfg b/yql/essentials/tests/sql/suites/window/win_func_rank_by_all.cfg new file mode 100644 index 0000000000..1af92ac7be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_all.cfg @@ -0,0 +1 @@ +in Input input_intersect_with_holes.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_all.sql b/yql/essentials/tests/sql/suites/window/win_func_rank_by_all.sql new file mode 100644 index 0000000000..963a93391f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_all.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; +PRAGMA DisableAnsiRankForNullableKeys; + +$input=(select cast(key as int32) ?? 0 as key, cast(subkey as int32) as subkey, value from Input); + +SELECT + rank(key) over w1 as rank_key, + dense_rank(key) over w1 as dense_rank_key, + key +FROM $input +WINDOW w1 as (ORDER BY key); diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_all.cfg b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_all.cfg new file mode 100644 index 0000000000..1af92ac7be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_all.cfg @@ -0,0 +1 @@ +in Input input_intersect_with_holes.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_all.sql b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_all.sql new file mode 100644 index 0000000000..fd589fcba2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_all.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; +PRAGMA DisableAnsiRankForNullableKeys; + +$input=(select cast(key as int32) as key, cast(subkey as int32) as subkey, value from Input); + +SELECT + rank(key) over w1 as rank_key, + dense_rank(key) over w1 as dense_rank_key, + key +FROM $input +WINDOW w1 as (ORDER BY key); diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_part.cfg b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_part.cfg new file mode 100644 index 0000000000..1af92ac7be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_part.cfg @@ -0,0 +1 @@ +in Input input_intersect_with_holes.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_part.sql b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_part.sql new file mode 100644 index 0000000000..72e506b183 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_opt_part.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; +PRAGMA DisableAnsiRankForNullableKeys; + +$input=(select cast(key as int32) % 4 as key_quad, cast(key as int32) as key, cast(subkey as int32) as subkey, value from Input); + +SELECT + rank(key) over w1 as rank_key, + dense_rank(key) over w1 as dense_rank_key, + key_quad, + key +FROM $input +WINDOW w1 as (PARTITION BY key_quad ORDER BY key) +ORDER BY rank_key, dense_rank_key, key_quad; diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_part.cfg b/yql/essentials/tests/sql/suites/window/win_func_rank_by_part.cfg new file mode 100644 index 0000000000..1af92ac7be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_part.cfg @@ -0,0 +1 @@ +in Input input_intersect_with_holes.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_by_part.sql b/yql/essentials/tests/sql/suites/window/win_func_rank_by_part.sql new file mode 100644 index 0000000000..6b94e82863 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_by_part.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; +PRAGMA DisableAnsiRankForNullableKeys; + +$input=(select cast(key as int32) % 4 as key_quad, cast(key as int32) ?? 0 as key, cast(subkey as int32) as subkey, value from Input); + +SELECT + rank(key) over w1 as rank_key, + dense_rank(key) over w1 as dense_rank_key, + key_quad, + key +FROM $input +WINDOW w1 as (PARTITION BY key_quad ORDER BY key) +ORDER BY rank_key, dense_rank_key, key_quad; diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_with_order_by_aggr_key.cfg b/yql/essentials/tests/sql/suites/window/win_func_rank_with_order_by_aggr_key.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_with_order_by_aggr_key.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_rank_with_order_by_aggr_key.sql b/yql/essentials/tests/sql/suites/window/win_func_rank_with_order_by_aggr_key.sql new file mode 100644 index 0000000000..5153e2cca7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_rank_with_order_by_aggr_key.sql @@ -0,0 +1,9 @@ +use plato; + +select + key, + RANK() over w +from Input +group by key +WINDOW w as (order by key) +order by key; diff --git a/yql/essentials/tests/sql/suites/window/win_func_spec_with_part.cfg b/yql/essentials/tests/sql/suites/window/win_func_spec_with_part.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_spec_with_part.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_spec_with_part.sql b/yql/essentials/tests/sql/suites/window/win_func_spec_with_part.sql new file mode 100644 index 0000000000..c4ccbc43be --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_spec_with_part.sql @@ -0,0 +1,14 @@ +/* postgres can not */ +$input=(select cast(key as int32) / 100 as key_hundred, cast(key as int32) as key, cast(subkey as int32) as subkey, value from plato.Input); + +select + key_hundred, + key, + (key - lag(key, 1) over w) as key_diff, + (subkey - lag(subkey, 1) over w) as subkey_diff, + row_number() over w as row, + value +from $input +window w as (partition by key_hundred order by key, value) +order by key_hundred, key, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_special.cfg b/yql/essentials/tests/sql/suites/window/win_func_special.cfg new file mode 100644 index 0000000000..3df7813790 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_special.cfg @@ -0,0 +1 @@ +in Input input_intersect.txt diff --git a/yql/essentials/tests/sql/suites/window/win_func_special.sql b/yql/essentials/tests/sql/suites/window/win_func_special.sql new file mode 100644 index 0000000000..1dd68979b3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_special.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +$input=(select cast(key as int32) as key, cast(subkey as int32) as subkey, value from plato.Input); + +select + key, + (key - lag(key, 1) over w) as key_diff, + (subkey - lag(subkey, 1) over w) as subkey_diff, + row_number() over w as row, + value +from $input +window w as (order by key, subkey, value); diff --git a/yql/essentials/tests/sql/suites/window/win_func_with_group_by.sqlx b/yql/essentials/tests/sql/suites/window/win_func_with_group_by.sqlx new file mode 100644 index 0000000000..4d4d238324 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_with_group_by.sqlx @@ -0,0 +1,12 @@ +/* postgres can not */ +USE plato; + +--INSERT INTO Output +SELECT + RANK(), -- unable to use window function with GROUP BY + key +FROM + Input as zz +GROUP BY + key +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_with_struct_access.sql b/yql/essentials/tests/sql/suites/window/win_func_with_struct_access.sql new file mode 100644 index 0000000000..47c0a0001e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_with_struct_access.sql @@ -0,0 +1,21 @@ +/* postgres can not */ +USE plato; + +$input=(SELECT + Cast(key as int32) / 100 as key_hundred, + AsStruct( + Cast(key as int32) as key, + Cast(subkey as int32) as subkey + ) as `struct`, + value +FROM Input as inSrc); + +--INSERT INTO Output +SELECT + key_hundred as a_part, + `struct`.key - lead(`struct`.key, 1) over w as keyDiff, + value +FROM $input as outSrc +WINDOW w as (partition by key_hundred ORDER BY `struct`.key, value) +ORDER by a_part, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_func_with_struct_access_full_access.sql b/yql/essentials/tests/sql/suites/window/win_func_with_struct_access_full_access.sql new file mode 100644 index 0000000000..49642a734e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_func_with_struct_access_full_access.sql @@ -0,0 +1,21 @@ +/* postgres can not */ +USE plato; + +$input=(SELECT + Cast(key as int32) / 100 as key_hundred, + AsStruct( + Cast(key as int32) as key, + Cast(subkey as int32) as subkey + ) as `struct`, + value +FROM Input as inSrc); + +--INSERT INTO Output +SELECT + key_hundred as a_part, + `struct`.key - lead(outSrc.`struct`.key, 1) over w as keyDiff, + value +FROM $input as outSrc +WINDOW w as (partition by key_hundred ORDER BY `struct`.key, value) +ORDER by a_part, value +; diff --git a/yql/essentials/tests/sql/suites/window/win_fuse_window.sql b/yql/essentials/tests/sql/suites/window/win_fuse_window.sql new file mode 100644 index 0000000000..644031aeaf --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_fuse_window.sql @@ -0,0 +1,25 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; +$data = (select unwrap(cast(key as uint32)) as key, unwrap(cast(subkey as uint32)) as subkey, value, value || value as unused from Input4); + +insert into @data +select * from $data; + +commit; + +select + key, subkey, + FIRST_VALUE(value) over w1 as w1_first_value, + SUM(subkey) over w2 as w2_sum_subkey, + LAST_VALUE(value) over w3 as w3_last_value, + SUM(key) over w4 as w4_sum_key, + LEAD(value) over w5 as w5_next_value, +from @data +window + w1 as (partition by subkey, key order by value), + w2 as (order by key,subkey rows unbounded preceding), + w3 as (partition by key, subkey order by value rows unbounded preceding), -- = w1 + w4 as (order by key,subkey rows between unbounded preceding and current row), -- = w2 + w5 as (partition by subkey, key order by value rows between unbounded preceding and current row) -- = w1 +order by key, subkey; diff --git a/yql/essentials/tests/sql/suites/window/win_group_peephole.sql b/yql/essentials/tests/sql/suites/window/win_group_peephole.sql new file mode 100644 index 0000000000..7997aa0940 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_group_peephole.sql @@ -0,0 +1,19 @@ +/* syntax version 1 */ +/* postgres can not */ + + +$t = SELECT 'john' as name, 42 as age; + +SELECT + SUM(age) OVER w0 AS sumAge, + LEAD(age,1) OVER w1 AS nextAge, + LAG(age,1) OVER w1 AS prevAge, + RANK() OVER w0 as rank, + DENSE_RANK() OVER w0 as dense_rank, + ROW_NUMBER() OVER w1 as row_number, +FROM $t AS u +WINDOW + w0 AS (ORDER BY name), + w1 AS () +; + diff --git a/yql/essentials/tests/sql/suites/window/win_inline_spec.sql b/yql/essentials/tests/sql/suites/window/win_inline_spec.sql new file mode 100644 index 0000000000..b881a07689 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_inline_spec.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +select + key, + max(key) over (order by key) as running_max, + lead(key) over (order by key rows unbounded preceding) as next_key, + aggregate_list(key) over w as keys, +from plato.Input +window w as (order by key rows between unbounded preceding and current row) +order by key; diff --git a/yql/essentials/tests/sql/suites/window/win_lead_in_mem.sql b/yql/essentials/tests/sql/suites/window/win_lead_in_mem.sql new file mode 100644 index 0000000000..ae78304cbd --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_lead_in_mem.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +USE plato; + +$list = (select item FROM (select AsList('foo', 'bar', 'baz', 'quux', 'bat') as `list`) FLATTEN BY `list` as item); + +--INSERT INTO Output +SELECT + item, YQL::Concat('+', Lead(item, 1) over w), YQL::Concat("++", Lead(item,2) over w) +FROM $list +WINDOW w as (); diff --git a/yql/essentials/tests/sql/suites/window/win_multiaggr.sql b/yql/essentials/tests/sql/suites/window/win_multiaggr.sql new file mode 100644 index 0000000000..4d641e8a53 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_multiaggr.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +use plato; + +select MULTI_AGGREGATE_BY(AsStruct(subkey as a,value as b), AGGREGATION_FACTORY("count")) over w, +MULTI_AGGREGATE_BY(AsStruct(subkey as a,value as b), AGGREGATION_FACTORY("max")) over w +from Input window w as (order by key); diff --git a/yql/essentials/tests/sql/suites/window/win_multiaggr_library.cfg b/yql/essentials/tests/sql/suites/window/win_multiaggr_library.cfg new file mode 100644 index 0000000000..f71ef4179c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_multiaggr_library.cfg @@ -0,0 +1,3 @@ +in Input input.txt +file agg_factory.sql agg_factory.sql.txt +file multiaggr_subq.sql multiaggr_subq.sql.txt diff --git a/yql/essentials/tests/sql/suites/window/win_multiaggr_library.sql b/yql/essentials/tests/sql/suites/window/win_multiaggr_library.sql new file mode 100644 index 0000000000..a9f599906c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_multiaggr_library.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +pragma library('multiaggr_subq.sql'); +pragma library('agg_factory.sql'); + +import multiaggr_subq symbols $multiaggr_win; + +select * from $multiaggr_win() order by rn; diff --git a/yql/essentials/tests/sql/suites/window/win_multiaggr_list.sql b/yql/essentials/tests/sql/suites/window/win_multiaggr_list.sql new file mode 100644 index 0000000000..5eeb21148c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_multiaggr_list.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +use plato; + +select MULTI_AGGREGATE_BY(AsList(subkey,value), AGGREGATION_FACTORY("count")) over w, +MULTI_AGGREGATE_BY(AsList(subkey,value), AGGREGATION_FACTORY("max")) over w +from Input window w as (order by key); diff --git a/yql/essentials/tests/sql/suites/window/win_multiaggr_tuple.sql b/yql/essentials/tests/sql/suites/window/win_multiaggr_tuple.sql new file mode 100644 index 0000000000..2384757bfb --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_multiaggr_tuple.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +use plato; + +select MULTI_AGGREGATE_BY(AsTuple(subkey,value), AGGREGATION_FACTORY("count")) over w, +MULTI_AGGREGATE_BY(AsTuple(subkey,value), AGGREGATION_FACTORY("max")) over w +from Input window w as (order by key); diff --git a/yql/essentials/tests/sql/suites/window/win_over_few_partitions.cfg b/yql/essentials/tests/sql/suites/window/win_over_few_partitions.cfg new file mode 100644 index 0000000000..582479bcde --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_over_few_partitions.cfg @@ -0,0 +1 @@ +in Input input_tutorial_users.txt diff --git a/yql/essentials/tests/sql/suites/window/win_over_few_partitions.sql b/yql/essentials/tests/sql/suites/window/win_over_few_partitions.sql new file mode 100644 index 0000000000..20f59940d0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_over_few_partitions.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +$data = (select + cast(key as uint32) as age, + cast(key as uint32)/10 as age_decade, + cast(subkey as uint32) as region, + value as name +from plato.Input); + +select + region, age, name, sum(age) over w1 as sum1, sum(age) over w2 as sum2 +from $data +window + w1 as (partition by region order by name), + w2 as (partition by age_decade order by name) +order by region, age; diff --git a/yql/essentials/tests/sql/suites/window/win_over_few_partitions_other.cfg b/yql/essentials/tests/sql/suites/window/win_over_few_partitions_other.cfg new file mode 100644 index 0000000000..582479bcde --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_over_few_partitions_other.cfg @@ -0,0 +1 @@ +in Input input_tutorial_users.txt diff --git a/yql/essentials/tests/sql/suites/window/win_over_few_partitions_other.sql b/yql/essentials/tests/sql/suites/window/win_over_few_partitions_other.sql new file mode 100644 index 0000000000..19bd3fe37b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_over_few_partitions_other.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +$input = (select cast(key as uint32) as key, cast(subkey as uint32) as subkey, value from plato.Input); + +select + subkey, + sum(subkey) over w2 AS x, + 2 * sum(key) over w1 as dbl_sum, + count(key) over w1 as c, + min(key) over w1 as mink, + max(key) over w1 as maxk +from $input +window + w1 as (partition by subkey order by key % 3, key), + w2 as (partition by key order by subkey) +order by subkey, x, dbl_sum; diff --git a/yql/essentials/tests/sql/suites/window/win_over_joined.cfg b/yql/essentials/tests/sql/suites/window/win_over_joined.cfg new file mode 100644 index 0000000000..b90cccc68d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_over_joined.cfg @@ -0,0 +1,3 @@ +in Input input.txt +in Input4 input4.txt + diff --git a/yql/essentials/tests/sql/suites/window/win_over_joined.sql b/yql/essentials/tests/sql/suites/window/win_over_joined.sql new file mode 100644 index 0000000000..77a93d6e2c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_over_joined.sql @@ -0,0 +1,13 @@ +SELECT + r.id, + ROW_NUMBER() OVER w AS rank +FROM ( + SELECT 0 AS id +) AS r +JOIN ( + SELECT 0 AS id +) AS m +ON r.id = m.id +WINDOW w AS ( + PARTITION BY r.id +) diff --git a/yql/essentials/tests/sql/suites/window/win_peephole.sql b/yql/essentials/tests/sql/suites/window/win_peephole.sql new file mode 100644 index 0000000000..94c054edac --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_peephole.sql @@ -0,0 +1,18 @@ +/* syntax version 1 */ +/* postgres can not */ + + +$t = SELECT 'john' as name, 42 as age; + +SELECT + SUM(age) OVER w0 AS sumAge, + LEAD(age,1) OVER w0 AS nextAge, + LAG(age,1) OVER w0 AS prevAge, + RANK() OVER w0 as rank, + DENSE_RANK() OVER w0 as dense_rank, + ROW_NUMBER() OVER w0 as row_number, +FROM $t AS u +WINDOW + w0 AS (ORDER BY name) +; + diff --git a/yql/essentials/tests/sql/suites/window/win_peephole_double_usage.sql b/yql/essentials/tests/sql/suites/window/win_peephole_double_usage.sql new file mode 100644 index 0000000000..3ace3346d6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_peephole_double_usage.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ + +$input = select * from as_table([<|key:1|>, <|key:1|>]); + +$src = select + key, + MIN(key) over w as curr_min +from $input +window w as (order by key); + +select * from $src +union all +select * from $src; diff --git a/yql/essentials/tests/sql/suites/window/win_with_as_table.sql b/yql/essentials/tests/sql/suites/window/win_with_as_table.sql new file mode 100644 index 0000000000..bcd5b5a3f8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_with_as_table.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +select k, + SUM(k) over w1 as s1, + SUM(k) over w2 as s2 +from as_table(AsList(AsStruct(1 as k), AsStruct(2 as k))) +window w1 as (order by k), w2 as (order by k desc) +order by k; diff --git a/yql/essentials/tests/sql/suites/window/win_with_cur_row.cfg b/yql/essentials/tests/sql/suites/window/win_with_cur_row.cfg new file mode 100644 index 0000000000..582479bcde --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_with_cur_row.cfg @@ -0,0 +1 @@ +in Input input_tutorial_users.txt diff --git a/yql/essentials/tests/sql/suites/window/win_with_cur_row.sql b/yql/essentials/tests/sql/suites/window/win_with_cur_row.sql new file mode 100644 index 0000000000..15c59c9159 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_with_cur_row.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +$data = (select cast(key as uint32) as age, cast(subkey as uint32) as region, value as name from plato.Input); + +select + region, name, sum(age) over w1 as sum1 +from $data +window w1 as (partition by region order by name) +order by region, name; diff --git a/yql/essentials/tests/sql/suites/window/yql-14179.sql b/yql/essentials/tests/sql/suites/window/yql-14179.sql new file mode 100644 index 0000000000..79fc1bcd39 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/yql-14179.sql @@ -0,0 +1,7 @@ +select + x, + aggregate_list(x) over w as lst, +from (values (1),(2),(3)) as a(x) +window + w as (rows between 0 preceding and 0 preceding) +order by x; diff --git a/yql/essentials/tests/sql/suites/window/yql-14277.sql b/yql/essentials/tests/sql/suites/window/yql-14277.sql new file mode 100644 index 0000000000..937dfd3d18 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/yql-14277.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ +$data = [ + <|id:1, time:1, value:'a'|>, + <|id:1, time:2, value:null|>, + <|id:1, time:3, value:null|>, + <|id:1, time:4, value:'b'|>, + <|id:1, time:5, value:null|>, + <|id:2, time:1, value:'c'|>, + <|id:2, time:2, value:'d'|>, + <|id:2, time:3, value:null|>, +]; + +select + a.*, + count(value) over w1 as w1, + max(value) over w2 as w2, +from + as_table($data) as a +window w1 as (order by time, id), + w2 as (partition by id) +order by id, time; diff --git a/yql/essentials/tests/sql/suites/window/yql-14479.sql b/yql/essentials/tests/sql/suites/window/yql-14479.sql new file mode 100644 index 0000000000..bb0de0cb7f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/yql-14479.sql @@ -0,0 +1,8 @@ +/* custom check: len(yt_res_yson[0]['Write'][0]['Data']) < 4 */ +/* ignore plan diff */ +USE plato; + +SELECT a.*, row_number() OVER (PARTITION BY key, subkey) as rn, TableName() as tn +FROM Input as a +TABLESAMPLE BERNOULLI(10.0) REPEATABLE(1) +; diff --git a/yql/essentials/tests/sql/suites/window/yql-14738.sql b/yql/essentials/tests/sql/suites/window/yql-14738.sql new file mode 100644 index 0000000000..b295b092c2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/yql-14738.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* syntax version 1 */ +USE plato; + +SELECT + t.*, + COUNT(*) OVER (PARTITION BY key || "1") as c1, + COUNT(*) OVER (PARTITION BY key || "2") as c2, + COUNT(*) OVER w as c3, +FROM Input as t +WINDOW w AS (PARTITION BY key || "3") +ORDER BY subkey; diff --git a/yql/essentials/tests/sql/suites/window/yql-15636.sql b/yql/essentials/tests/sql/suites/window/yql-15636.sql new file mode 100644 index 0000000000..b9efdb2c56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/yql-15636.sql @@ -0,0 +1,37 @@ +/* syntax version 1 */ +/* postgres can not */ +use plato; + +insert into @tmp +select + Just('foo') as driver_license_pd_id, + 'bar' as order_id, + '1' as user_phone_pd_id, + '2' as utc_order_dttm; +commit; + + + +SELECT + driver_license_pd_id, + user_phone_pd_id, + utc_order_dttm, + order_id, + + LEAD( + <|"order_id": order_id, "order_dttm": utc_order_dttm|>, + 1 + ) OVER ( + PARTITION BY user_phone_pd_id + ORDER BY utc_order_dttm + ) AS next_user_order, + + LEAD( + <|"order_id": order_id|>, + 1 + ) OVER ( + PARTITION BY driver_license_pd_id + ORDER BY utc_order_dttm + ) AS next_driver_order, + +FROM @tmp; diff --git a/yql/essentials/tests/sql/suites/window/yql-18879.sql b/yql/essentials/tests/sql/suites/window/yql-18879.sql new file mode 100644 index 0000000000..3778686fd9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/yql-18879.sql @@ -0,0 +1,29 @@ +/* syntax version 1 */ +/* postgres can not */ + +$t1 = AsList( + AsStruct(100 AS itemid, 20 AS duration, 2 AS start_shows, 1 AS day), + AsStruct(1001 AS itemid, 10 AS duration, 2 AS start_shows, 1 AS day), + AsStruct(134 AS itemid, 25 AS duration, 1 AS start_shows, 1 AS day), + AsStruct(123 AS itemid, 24 AS duration, 1 AS start_shows, 1 AS day), + AsStruct(23 AS itemid, 30 AS duration, 1 AS start_shows, 2 AS day), + AsStruct(23 AS itemid, 30 AS duration, 1 AS start_shows, 2 AS day), + AsStruct(1 AS itemid, 45 AS duration, 1 AS start_shows, 2 AS day), + AsStruct(30 AS itemid, 63 AS duration, 1 AS start_shows, 2 AS day), + AsStruct(53 AS itemid, 1000 AS duration, 0 AS start_shows, 2 AS day), +); + +SELECT + PERCENT_RANK() OVER (PARTITION BY day ORDER BY start_shows DESC) AS col1, + SUM(start_shows) OVER (PARTITION BY day) AS col2 +FROM AS_TABLE($t1); + +SELECT + CUME_DIST() OVER (PARTITION BY day ORDER BY start_shows DESC) AS col1, + SUM(start_shows) OVER (PARTITION BY day) AS col2 +FROM AS_TABLE($t1); + +SELECT + NTILE(2) OVER (PARTITION BY day ORDER BY start_shows DESC) AS col1, + SUM(start_shows) OVER (PARTITION BY day) AS col2 +FROM AS_TABLE($t1); |