SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-15
Examples of SAMPLE
•
In this example, both the tables are sampled first and then joined. This query
computes the average salary and the average sale amount generated from a
random 10 percent of all the sales people and 20 percent of all the sales
transactions.
SELECT AVG(salary), AVG(amount)
FROM ( SELECT salary, empid
FROM salesperson
SAMPLE RANDOM 10 PERCENT ) AS S,
( SELECT amount, empid
FROM sales
SAMPLE RANDOM 20 PERCENT ) AS T
WHERE S.empid = T.empid;
(EXPR) (EXPR)
--------- ---------
45000.00 31000.00
--- 1 row(s) selected.
•
This example illustrates oversampling. This query retrieves 150 percent of the
sales transactions where the amount exceeds $1000. The result contains every
row at least once, and 50 percent of the rows, picked randomly, occur twice.
SELECT *
FROM sales
WHERE amount > 1000
SAMPLE RANDOM 150 PERCENT;
EMPID PRODUCT REGION AMOUNT
----- -------------------- ------ -----------
1 PCGOLD, 30MB E 30000.00
23 PCDIAMOND, 60MB W 40000.00
23 PCDIAMOND, 60MB W 40000.00
29 GRAPHICPRINTER, M1 N 11000.00
32 GRAPHICPRINTER, M2 S 15000.00
32 GRAPHICPRINTER, M2 S 15000.00
... ... ... ...
--- 88 row(s) selected.