topic How to retrieve a statistic sample from a database table in Application Development and Automation Discussions

How to retrieve a statistic sample from a database table

Former Member — Thu, 21 Apr 2011 09:09:06 GMT

Hi experts,

Let's say I have a 100.000 rows table and I want to take a representative statistical sample from that table.

Say, it should be 5% (or 5000 rows).

Question is: How to ensure that the sample is representative?

This is not guaranteed by UP TO N ROWS because the database selects without having an order by or WHERE starting reading the first db blocks until it reaches the limit of 5000 rows.

SELECT * UP TO 5000 rows into table .... from ZSAMPLE.

This means I miss mostly of the records in the table, hence the sample is not representative.

Do you ever encounter such a problem?

Re: How to retrieve a statistic sample from a database table

Former Member — Thu, 21 Apr 2011 09:15:52 GMT

never encountered such a requirement. And i really wonder for what this may be good.

Computers are so fast nowadays that i do not have any idea why you dont go for all the data instead of viewing just that "representative" ones.

Sorry i couldnt help you out here, but still i´m quite interested what this is about.

Maybe we can get you into other ideas once we know for what exactly you are doing this.

Re: How to retrieve a statistic sample from a database table

Former Member — Thu, 21 Apr 2011 09:18:00 GMT

yes it's not representative.

You should insert more statistical conditions, such as the date (maybe the year), the type of record, the status, the amount...

but it depends of the table.

Surely you have to make a list of these fields and make a select balanced on their values.

example considering only the year.

loop at year. "range build on 5 years

select fields up to 1000 rows

from table

appending table i_tab

where year...

endloop.

Re: How to retrieve a statistic sample from a database table

Former Member — Thu, 21 Apr 2011 11:56:01 GMT

You could SELECT (with package size if desired) into an internal table. Then:

Select * from table into internal table........

package size 10000. "not really necessary for 100000 row table

*Initialize a counter of type i.

lv_counter = 0.

loop at the internal table.

lv_counter = lv_counter + 1.

if lv_counter lt 20. "1 in 20 = 5%

delete internal table. "drop this row.

elseif lv_counter eq 20.

lv_counter = 0.

endif.

endloop.

....

endselect. "if you used package size.

at the end you would have kept every 1 out of every 20 records from your original table select. Be sure to use a package size (if needed ) that is an exact multiple of 20. To futher randomize the table, you could sort on some field that has random values, etc. before doing the every 20th record retention.

Re: How to retrieve a statistic sample from a database table

Former Member — Thu, 21 Apr 2011 13:12:38 GMT

It depends on what you mean by "representative". if you mean any 5,000 rows, then it doesn't matter how you pick them. If you want a truly random selection, then Breakpoint's solution (while looking good otherwise, won't do.

Rob