yql/essentials/tests/sql/suites/window/distinct_over_window.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;