Neoview SQL Reference Manual (R2.5)
--- 88 row(s) selected.
• The BALANCE option enables stratified sampling. Retrieve the age and salary of 1000 sales
people such that 50 percent of the result are male and 50 percent female.
SELECT age, sex, salary
FROM salesperson
SAMPLE FIRST
BALANCE WHEN sex = 'male' THEN 15 ROWS
WHEN sex = 'female' THEN 15 ROWS
END
ORDER BY age;
AGE SEX SALARY
----------- ------ -----------
22 male 28000.00
22 male 90000.00
22 female 136000.00
22 male 37000.40
... ... ...
--- 30 row(s) selected.
• Retrieve all sales records with the amount exceeding $10000 and a random sample of 10
percent of the remaining records:
SELECT *
FROM sales
SAMPLE RANDOM
BALANCE WHEN amount > 10000 THEN 100 PERCENT
ELSE 10 PERCENT
END;
EMPID PRODUCT REGION AMOUNT
----- -------------------- ------ -----------
1 PCGOLD, 30MB E 30000.00
23 PCDIAMOND, 60MB W 40000.00
29 GRAPHICPRINTER, M1 N 11000.00
32 GRAPHICPRINTER, M2 S 15000.00
... ... ... ...
228 MONITORCOLOR, M2 N 10500.00
... ... ... ...
--- 32 row(s) selected.
• This query shows an example of stratified sampling where the conditions are not mutually
exclusive:
SELECT *
FROM sales
SAMPLE RANDOM
BALANCE WHEN amount > 10000 THEN 100 PERCENT
WHEN product = 'PCGOLD, 30MB' THEN 25 PERCENT
WHEN region = 'W' THEN 40 PERCENT
ELSE 10 PERCENT
END;
EMPID PRODUCT REGION AMOUNT
----- -------------------- ------ -----------
1 PCGOLD, 30MB E 30000.00
23 PCDIAMOND, 60MB W 40000.00
29 GRAPHICPRINTER, M1 N 11000.00
32 GRAPHICPRINTER, M2 S 15000.00
324 SQL Clauses