testdata/workloads/functional-query/queries/QueryTest/analytic-fns-tpcds.test

====
---- QUERY
# Analytic function with no partition
select i_item_sk, i_current_price,
   SUM (i_current_price)
  OVER (ORDER BY i_item_sk) running_total
from item
order by
  i_brand,
  i_item_sk
limit 10
---- RESULTS
2,1.12,28.14
24,2.61,213.93
36,91.42,501.66
54,37.84,730.17
110,1.31,1167.33
168,6.73,1719.08
190,0.56,1859.34
200,3.46,1911.81
372,8.87,3602.84
404,2.52,3873.45
---- TYPES
BIGINT, DECIMAL, DECIMAL
====
---- QUERY
# Same as above, but with partition
select i_item_sk, i_brand, i_current_price,
   SUM (i_current_price)
  OVER (partition by i_brand ORDER BY i_item_sk) running_total
from item
order by
  i_brand,
  i_item_sk
limit 10
---- RESULTS
2,'amalgamalg #1',1.12,1.12
24,'amalgamalg #1',2.61,3.73
36,'amalgamalg #1',91.42,95.15
54,'amalgamalg #1',37.84,132.99
110,'amalgamalg #1',1.31,134.30
168,'amalgamalg #1',6.73,141.03
190,'amalgamalg #1',0.56,141.59
200,'amalgamalg #1',3.46,145.05
372,'amalgamalg #1',8.87,153.92
404,'amalgamalg #1',2.52,156.44
---- TYPES
BIGINT, STRING, DECIMAL, DECIMAL
====
---- QUERY
# Same as above, but the order by column in the analytic function has repeating values
select i_item_sk, i_brand, i_current_price,
   SUM (i_current_price)
  OVER (partition by i_brand ORDER BY i_current_price) running_total
from item
order by
  i_brand,
  i_item_sk
limit 10
---- RESULTS
2,'amalgamalg #1',1.12,17.76
24,'amalgamalg #1',2.61,111.23
36,'amalgamalg #1',91.42,1967.39
54,'amalgamalg #1',37.84,1091.13
110,'amalgamalg #1',1.31,29.10
168,'amalgamalg #1',6.73,497.90
190,'amalgamalg #1',0.56,5.73
200,'amalgamalg #1',3.46,150.20
372,'amalgamalg #1',8.87,726.45
404,'amalgamalg #1',2.52,103.45
---- TYPES
BIGINT, STRING, DECIMAL, DECIMAL
====
---- QUERY
# Nested analytic functions
select i_item_sk, i_brand, running_total,
   SUM (running_total)
  OVER (partition by i_manufact_id ORDER BY running_total) running_total2
from (
  select i_item_sk, i_brand, i_current_price, i_manufact_id,
     SUM (i_current_price)
    OVER (partition by i_brand ORDER BY i_current_price) running_total
  from item
  order by
    i_brand,
    i_item_sk
) sub
order by
  i_item_sk,
  i_brand desc
limit 10
---- RESULTS
1,'exportischolar #2',988.61,5055.15
2,'amalgamalg #1',17.76,92.01
3,'brandbrand #4',37.69,243.96
4,'importoexporti #1',26.69,82.85
5,'importoimporto #2',243.76,1132.66
6,'exportiimporto #1',10.73,38.84
7,'amalgexporti #2',850.23,6619.62
8,'exportiexporti #1',100.41,514.95
9,'edu packamalg #2',292.44,1274.12
10,'namelessunivamalg #11',16.46,51.79
---- TYPES
BIGINT, STRING, DECIMAL, DECIMAL
====
---- QUERY
# 2 analytic functions on different partition and order by columns
select i_item_sk, i_brand, i_current_price, i_manufact_id,
   SUM (i_current_price)
  OVER (partition by i_brand ORDER BY i_current_price) running_total,
   MAX (i_current_price)
  OVER (partition by i_manufact_id ORDER BY i_item_sk) running_max
from item
order by
  i_brand,
  i_item_sk
limit 10
---- RESULTS
2,'amalgamalg #1',1.12,294,17.76,1.12
24,'amalgamalg #1',2.61,260,111.23,3.73
36,'amalgamalg #1',91.42,553,1967.39,91.42
54,'amalgamalg #1',37.84,865,1091.13,37.84
110,'amalgamalg #1',1.31,253,29.10,1.31
168,'amalgamalg #1',6.73,310,497.90,8.19
190,'amalgamalg #1',0.56,46,5.73,3.84
200,'amalgamalg #1',3.46,40,150.20,3.46
372,'amalgamalg #1',8.87,652,726.45,8.87
404,'amalgamalg #1',2.52,260,103.45,3.73
---- TYPES
BIGINT, STRING, DECIMAL, INT, DECIMAL, DECIMAL
====
---- QUERY
# Union all in the FROM subquery
select i_item_sk, i_brand, i_current_price,
   SUM (i_current_price)
  OVER (partition by i_brand ORDER BY i_item_sk) running_total
from (
  select * from item
  union all
  select * from item
) sub
order by
  i_brand,
  i_item_sk
limit 10
---- RESULTS
2,'amalgamalg #1',1.12,2.24
2,'amalgamalg #1',1.12,2.24
24,'amalgamalg #1',2.61,7.46
24,'amalgamalg #1',2.61,7.46
36,'amalgamalg #1',91.42,190.30
36,'amalgamalg #1',91.42,190.30
54,'amalgamalg #1',37.84,265.98
54,'amalgamalg #1',37.84,265.98
110,'amalgamalg #1',1.31,268.60
110,'amalgamalg #1',1.31,268.60
---- TYPES
BIGINT, STRING, DECIMAL, DECIMAL
====
---- QUERY
# Aggregate the column generated by the analytic function
select max(running_total)
from (
  select i_item_sk, i_current_price,
     SUM (i_current_price)
    OVER (partition by i_brand ORDER BY i_current_price) running_total
  from item
  order by
    i_brand,
    i_item_sk
) sub
---- RESULTS
2815.26
---- TYPES
DECIMAL
====
---- QUERY
# Regression test for IMPALA-7403 that reproduces a memory management bug.
# The plan has two analytic operators with no intervening sort. A low NDV
# sort key seems to help with reproducing the bug.
SELECT c_birth_country, a, b, count(*)
FROM (
  SELECT c_birth_country,
         LAG(c_birth_country) OVER (ORDER BY c_birth_country) a,
         MIN(c_birth_country) OVER (ORDER BY c_birth_country) b
  FROM customer
) v
GROUP BY c_birth_country, a, b
ORDER BY count(*), c_birth_country, a, b
LIMIT 20
---- RESULTS
'AFGHANISTAN','NULL','AFGHANISTAN',1
'ALAND ISLANDS','AFGHANISTAN','AFGHANISTAN',1
'ALBANIA','ALAND ISLANDS','AFGHANISTAN',1
'ALGERIA','ALBANIA','AFGHANISTAN',1
'AMERICAN SAMOA','ALGERIA','AFGHANISTAN',1
'ANDORRA','AMERICAN SAMOA','AFGHANISTAN',1
'ANGOLA','ANDORRA','AFGHANISTAN',1
'ANGUILLA','ANGOLA','AFGHANISTAN',1
'ANTARCTICA','ANGUILLA','AFGHANISTAN',1
'ANTIGUA AND BARBUDA','ANTARCTICA','AFGHANISTAN',1
'ARGENTINA','ANTIGUA AND BARBUDA','AFGHANISTAN',1
'ARMENIA','ARGENTINA','AFGHANISTAN',1
'ARUBA','ARMENIA','AFGHANISTAN',1
'AUSTRALIA','ARUBA','AFGHANISTAN',1
'AUSTRIA','AUSTRALIA','AFGHANISTAN',1
'AZERBAIJAN','AUSTRIA','AFGHANISTAN',1
'BAHAMAS','AZERBAIJAN','AFGHANISTAN',1
'BAHRAIN','BAHAMAS','AFGHANISTAN',1
'BANGLADESH','BAHRAIN','AFGHANISTAN',1
'BARBADOS','BANGLADESH','AFGHANISTAN',1
---- TYPES
STRING,STRING,STRING,BIGINT
====