SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—544517-008
7-13
Examples of SAMPLE
•
Return the SALARY of the youngest five sales people, skip the next 15 rows, and
repeat this process until there are no more rows in the intermediate result table.
Note that you cannot specify periodic sampling with the sample size larger than the
period.
SELECT salary
FROM salesperson
SAMPLE PERIODIC 5 ROWS EVERY 20 ROWS SORT BY age;
SALARY
-----------
90000.00
90000.00
28000.00
27000.12
136000.00
36000.00
...
--- 17 row(s) selected.
In this example, there are 62 rows in the SALESPERSON table. For each set of 20
rows, the first five rows are selected. The last set consists of two rows, both of
which are selected.
•
Compute the average salary of a random 10 percent of the sales people. You will
get a different result each time you run this query because it is based on a random
sample.
SELECT AVG(salary)
FROM salesperson
SAMPLE RANDOM 10 PERCENT;
(EXPR)
--------------------
61928.57
--- 1 row(s) selected.
•
Compute the average salary of a random 10 percent of the sales people using
cluster sampling where each cluster is 4 blocks. You will get a different result each
time you run this query because it is based on a random sample.
SELECT AVG(salary)
FROM salesperson
SAMPLE RANDOM 10 PERCENT CLUSTERS OF 4 BLOCKS;
(EXPR)
---------------------
50219.524
--- 1 row(s) selected.