set hive.cbo.enable=false;

drop table over10k_n14;

create table over10k_n14(
           t tinyint,
           si smallint,
           i int,
           b bigint,
           f float,
           d double,
           bo boolean,
           s string,
           ts timestamp,
           `dec` decimal(4,2),
           bin binary)
       row format delimited
       fields terminated by '|';

load data local inpath '../../data/files/over10k' into table over10k_n14;

 EXPLAIN
  SELECT fv
    FROM (SELECT distinct first_value(t) OVER ( PARTITION BY si ORDER BY i ) AS fv
            FROM over10k_n14) sq
ORDER BY fv
   LIMIT 10;

  SELECT fv
    FROM (SELECT distinct first_value(t) OVER ( PARTITION BY si ORDER BY i ) AS fv
            FROM over10k_n14) sq
ORDER BY fv 
   LIMIT 10;

 EXPLAIN
  SELECT lv
    FROM (SELECT distinct last_value(i) OVER ( PARTITION BY si ORDER BY i ) AS lv
            FROM over10k_n14) sq
ORDER BY lv
   LIMIT 10;

  SELECT lv
    FROM (SELECT distinct last_value(i) OVER ( PARTITION BY si ORDER BY i ) AS lv
            FROM over10k_n14) sq
ORDER BY lv
   LIMIT 10;

 EXPLAIN
  SELECT lv, fv
    FROM (SELECT distinct last_value(i) OVER ( PARTITION BY si ORDER BY i ) AS lv,
                          first_value(t) OVER ( PARTITION BY si ORDER BY i ) AS fv
            FROM over10k_n14) sq
ORDER BY lv, fv
   LIMIT 50;

  SELECT lv, fv 
    FROM (SELECT distinct last_value(i) OVER ( PARTITION BY si ORDER BY i ) AS lv,
                          first_value(t) OVER ( PARTITION BY si ORDER BY i ) AS fv
            FROM over10k_n14) sq
ORDER BY lv, fv
   LIMIT 50;

explain
select si, max(f) mf, rank() over ( partition by si order by mf ) r
FROM over10k_n14
GROUP BY si
HAVING max(f) > 0
ORDER BY si, r
limit 50;

select si, max(f) mf, rank() over ( partition by si order by mf ) r
FROM over10k_n14
GROUP BY si
HAVING max(f) > 0
ORDER BY si, r
limit 50;

explain
select distinct si, rank() over ( partition by si order by i ) r
FROM over10k_n14
ORDER BY si, r
limit 50;

select distinct si, rank() over ( partition by si order by i ) r
FROM over10k_n14
ORDER BY si, r
limit 50;