CODEWITHSUNDEEP
sqlrandomnetezza
Lorena
Lorena

Reputation: 3

Random selection in Netezza

I need help with a random selection in Netezza.

I have for example 4 customers and each one has some options for a variable.

Customer | Variable

1    |     4
1    |     5
1    |     6
2    |     10
2    |     12
2    |     16
3    |     1
3    |     2 
3    |     3
3    |     6
4    |     5
4    |     8

And I need to select only one option for each Customer using a random selection. Example of solution:

Customer | Variable

1    |     5
2    |     10
3    |     3
4    |     8

Thanks!

Upvotes: 0

Views: 3573

Answers (1)

NzGuy
NzGuy

Reputation: 397

IBM Netezza offers inbuilt random() function Random number functions which you can use to derive the desired output. Below solution tested on NPS Release 7.2.1.0

Test data :

create table TEST 
(
Customer int,
Variable int

)
distribute on random;

insert into TEST values(1,4);
insert into TEST values(1,5);
insert into TEST values(1,6);
insert into TEST values(2,10);
insert into TEST values(2,12);
insert into TEST values(2,16);
insert into TEST values(3,1);
insert into TEST values(3,2 );
insert into TEST values(3,3);
insert into TEST values(3,6);
insert into TEST values(4,5);
insert into TEST values(4,8);

Solution:

    SELECT CUSTOMER
        ,max(VARIABLE) AS VARIABLE
    FROM (
        SELECT CUSTOMER 
            ,first_value(VARIABLE) OVER (
                PARTITION BY CUSTOMER ORDER BY random()
                ) AS VARIABLE

        FROM TEST
        ) a

GROUP BY CUSTOMER
ORDER BY 1;

Output iteration 1:

CUSTOMER VARIABLE
1           5
2           16
3           2
4           8

Output iteration 2:

CUSTOMER VARIABLE
1           6
2           10
3           1
4           5

Upvotes: 1

Related Questions