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

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-11
Examples of SAMPLE
Cluster sampling can be done only on a base table, not on intermediate results.
Cluster sampling is generally faster than sampling individual rows because fewer
blocks are read from disk. In random row and periodic sampling, the entire result table
being sampled is read, and each row in the table is processed. In cluster sampling,
only the disk blocks that are part of the result table are read and processed. Therefore,
if the sampling percentage is small, the performance advantage of cluster sampling
over other sampling methods can be dramatic.
For more information, see the SQL/MX Query Guide.
Examples of SAMPLE
Within SQLCI, suppose that the data-mining tables SALESPER, SALES, and DEPT
have been created as:
CREATE TABLE $db.mining.salesper
( empid NUMERIC (4) UNSIGNED NOT NULL
,dnum NUMERIC (4) UNSIGNED NOT NULL
,salary NUMERIC (8,2) UNSIGNED
,age INTEGER
,sex CHAR (6)
,PRIMARY KEY (empid) );
CREATE TABLE $db.mining.sales
( empid NUMERIC (4) UNSIGNED NOT NULL
,product VARCHAR (20)
,region CHAR (4)
,amount NUMERIC (9,2) UNSIGNED
,PRIMARY KEY (empid) );
CREATE TABLE $db.mining.dept
( dnum NUMERIC (4) UNSIGNED NOT NULL
,name VARCHAR (20)
,PRIMARY KEY (dnum) );
Within MXCI, the ANSI alias name has been mapped as:
CREATE SQLMP ALIAS db.mining.salesperson $db.mining.salesper;
CREATE SQLMP ALIAS db.mining.sales $db.mining.sales;
CREATE SQLMP ALIAS db.mining.department $db.mining.dept;
Suppose, too, that sample data is inserted into this database similar to the data in the
sample database.
•
Return the SALARY of the youngest 50 sales people:
SELECT salary
FROM salesperson
SAMPLE FIRST 50 ROWS SORT BY age;
SALARY
-----------
90000.00
90000.00
28000.00