SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-15
Examples of SAMPLE
For this query execution, the number of rows returned by the embedded query is
limited by the total number of rows in the SALESPERSON table. Therefore, it is
possible that no rows satisfy the search condition in the WHERE clause.
•
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.