Can I use Top-K projections against views and with where clauses?
I need to support top k queries by a key that gets passed into my query. All the examples I see for Top K projections don't have any conditional logic and the query that I have does. Can I use Top K or live aggregate projections for this case? Will it work against a view or only the table?
My query looks like this:
CREATE PROJECTION rep_agg AS SELECT
t1.EFF_DATE AS EFF_DATE,
t1.COMPENSABLE_FLAG AS COMPENSABLE_FLAG,
t1.AVAIL_IN_SAMI_IND AS AVAIL_IN_SAMI_IND,
t1.TXN_AMOUNT AS TXN_AMOUNT,
t1.COMPENSABLE_SALES_AMOUNT AS COMPENSABLE_SALES_AMOUNT,
t1.FUND_CODE AS FUND_CODE,
t1.COMPOSITE_CODE AS COMPOSITE_CODE,
t1.HOC_HEART_BEAT_ID AS HOC_HEART_BEAT_ID,
t1.CURRENT_DATE_FLAG AS CURRENT_DATE_FLAG,
t1.CURRENT_MONTH_FLAG AS CURRENT_MONTH_FLAG,
t1.CURRENT_YEAR_FLAG AS CURRENT_YEAR_FLAG,
t1.PRIOR_YEAR_FLAG AS PRIOR_YEAR_FLAG,
t1.RTL_REGION_NUMBER AS RTL_REGION_NUMBER
FROM
poc_db."public".REP_TRANSACTIONS_AGGREGATE_temp_V1 t1
WHERE
t1.FUND_CODE = '00220'
AND t1.RTL_REGION_NUMBER = '112'
AND t1.CURRENT_YEAR_FLAG = 'Y'
AND t1.AVAIL_IN_SAMI_IND = 'Y'
AND t1.TRANSACTION_CLASS_MAJOR_DESC IN ('Exchange In',
'Purchase')
ORDER BY
COMPENSABLE_SALES_AMOUNT DESC LIMIT 100;
Comments
Hi Ryan,
top-k projections or live aggregate projections work only with tables. They cannot be created against views and regarding join predicate in the WHERE clause, only foreign-key = primary-key equijoins between the large and smaller tables are allowed and No other predicates are allowed. I hope this clarifies your doubts.
For more information, please refer to the documentation http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEPROJECTIONLiveAggregate.htm%3FTocPath%3DSQL%2520Reference%2520Manual|SQL%2520Statements|_____43
-Regards,
Sruthi