How to do a PERCENT_RANK in KINESIS ANALYTICS

Question

I am trying to learn how to do simple analysis such as calculating the percentile rank of price volatility in real-time with Kinesis Analytics ,

I calculate the price volatility of each Ticker over 1 minute sliding window and then try to percent rank for all ticker symbols and take the top 25% as selected tickers, however I am getting Error in the percent_rank step, Not sure its because of me not being a Pro in SQL or its just a kinesis related error, Please share your solution. Thanks in advance


CREATE OR REPLACE STREAM "TEMP_STREAM" (
                            ticker_symbol VARCHAR(10), 
                            Change double,
                            Price double);

CREATE OR REPLACE STREAM "RAW_STREAM" (
                            ticker_symbol VARCHAR(10), 
                            price_avg double,
                            price_volatility double);
                            
                            
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" (
                            ticker_symbol VARCHAR(10), 
                            price_avg double,
                            price_volatility double,
                            pr_rank double);
                          
CREATE OR REPLACE PUMP "STREAM_PUMP" AS 
   INSERT INTO "TEMP_STREAM"
      SELECT STREAM ticker_symbol,Change,Price  
      FROM "SOURCE_SQL_STREAM_001"  ;
      
      

CREATE OR REPLACE PUMP "STREAM_PUMP" AS 
   INSERT INTO "RAW_STREAM"
      SELECT STREAM ticker_symbol,
                    AVG(Price) over W1 as price_avg,
                    STDDEV_SAMP(Price) over W1 as price_volatility
      FROM "TEMP_STREAM" 
       WINDOW W1 AS (
              PARTITION BY ticker_symbol 
              RANGE INTERVAL '1' MINUTE PRECEDING) ORDER BY ROWTIME,price_volatility ASC;
              
CREATE OR REPLACE PUMP "OUTPUT_PUMP" AS 
   INSERT INTO "DESTINATION_SQL_STREAM"
      SELECT STREAM ticker_symbol,
                    price_avg, price_volatility,
                     PERCENT_RANK() OVER(ORDER BY ROWTIME,price_volatility ASC) AS pr_rank
                  FROM "RAW_STREAM";

There was an issue updating your application. Error message:  Failed SQL command: CREATE OR REPLACE PUMP "OUTPUT_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM" SELECT STREAM ticker_symbol, price_avg, price_volatility, PERCENT_RANK() OVER(ORDER BY ROWTIME,price_volatility ASC) AS pr_rank FROM "RAW_STREAM". SQL error message: From line 5, column 51 to line 5, column 57: ROWTIME pseudo-column must be in SELECT or ORDER BY clause

How to do a PERCENT_RANK in KINESIS ANALYTICS

0 Answers0