Sample Data Collection

---- Start

We often encounter this situation and want to see several data records in a table, such as 10 and 20. In DB2, we can write as follows:

Select * from <table_name> fetch first 10 rows only; 

I don't know if you noticed it. No matter how many times you execute the preceding statement, the result set remains unchanged. So what should I do if I want to query 10 records randomly? You can use the following SQL:

Select * from <table_name> order by rand () Fetch first 10 rows only; 

The above is the simplest way to collect sample data. In DB2, more professional is to use tablesample to collect sample data. So why should we collect sample data? The main reason is that grouping statistics on massive data is time-consuming and laborious. At this time, we can collect sample data and then collect statistics on the sample data to predict the overall trend.

I. Syntax

Select... from <br/> <Table-Name> tablesample [Bernoulli | System] (percent) repeatable (Num) <br/> where... 

Ii. Example

Select * <br/> from staff tablesample Bernoulli (8) repeatable (586) <br/> order by ID; </P> <p> -- Description <br/> 8% of the sample data is extracted from the staff table using the Bernoulli sampling method, repeatable indicates that the same result is returned when the same statement is executed multiple times.

Iii. Sampling Method

1. Bernoulli (Row-level bernuoli sampling): It checks each row, with high accuracy but poor performance.

2. System (system page-level sampling): It checks each data page (a data page contains several rows), high performance, but poor accuracy.

--- For more information, see:DB2 SQL

----Statement: indicate the source for reprinting.

---- Last updated on 2010.2.2

---- Written by shangbo on 2009.9.25

---- End