Search code examples
sqlkognitio-wx2kognitiowx2

How to efficiently write keying logic for full range of BIGINT


I have a column flowing in with the full range of integers. Whilst generating keys on the INT column, I need to push the 0's and negatives down (ordered DESC) and the positive keys to stay at the top (ordered ASC/DESC). I came up with two approaches but I am not sure which is the best

Approach 1: Tidier but unsure about scalability

WITH t1(c1) AS
(
SELECT 11 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT -1 UNION ALL
SELECT -11 
)
SELECT c1, ROW_NUMBER() OVER (ORDER BY 
                             CASE WHEN c1 <=0 THEN c1 END DESC , 
                             CASE WHEN c1 >0 THEN c1 END ASC) keys
FROM t1 ;

Approach 2: Scalable

WITH t1(c1) AS
(
SELECT 11 UNION ALL
SELECT 2 UNION ALL
SELECT 0 UNION ALL
SELECT -1 UNION ALL
SELECT -11 
)
SELECT c1, ROW_NUMBER() OVER (ORDER BY c1 ASC) keys
FROM t1 
WHERE c1 >0
UNION ALL 
-- Need to include Max keys from previous step in actual implementation    
SELECT c1, 
       maxkeys + ROW_NUMBER() OVER (ORDER BY c1 DESC) keys 
FROM t1 
WHERE c1 <= 0

;

Please suggest any other better alternatives for scalability (BIGINT range).


Solution

  • After a round of performance testing, we found option 1 was more elegant and optimum solution in this case. Also, this snippet is getting generated for numerous tables dynamically, it was easier to configure and turn on and off.