I'm trying to take a sample from a insurance claims database.
For example 20% random, sample from 1 million claims data where provider type is '25' and year is '2012'. Data is in sqldeveloper. I am a statistician with basic SQL knowledge.
Asked
Active
Viewed 7,074 times
1
-
And what exactly is your question? – That Brazilian Guy Jan 18 '13 at 22:04
-
check http://stackoverflow.com/questions/733652/select-a-random-sample-of-results-from-an-oracle-query – hubson bropa Jan 18 '13 at 22:07
3 Answers
3
You can use SAMPLE to get a random set of rows from a table.
SELECT *
FROM claim SAMPLE(20)
WHERE type ='25'
AND year = 2012;
Alen Oblak
- 3,285
- 13
- 27
-
Thank you Alen for your reply. sample() works fine when I do not use the where clause. But it will not bring random sample once I specify provider type and year. – Kuusum Jan 22 '13 at 14:12
-
I have hunch this is because it is applying filters after sample is taken. to get around this do the filtering in a inner query then sample on that: ie SELECT * FROM (SELECT * FROM WHERE ...) SAMPLE(20). syntax is rusty so that may not work as wrtten but should express the idea – hubson bropa Jan 22 '13 at 19:09
-
3@hubson: `SAMPLE` only works on the table, not on an inline view. – Jeffrey Kemp Jan 23 '13 at 08:27
1
SQL has a SAMPLE command built in. Example:
SELECT * FROM emp SAMPLE(25)
means each row in emp has a 25% chance of being included in the resulting set. NOTE: this does not mean that exactly 25% of the rows are necessarily selected
this blog was a quick read on more details on sampling
hubson bropa
- 2,720
- 2
- 29
- 35
-
@hubson_bropa. No SQL which is an ISO standard has a TABLESAMPLE clause with this syntax : TABLESAMPLE
( percent_value ) [ REPEATABLE ( seed_value ) ] *with* – SQLpro Dec 12 '22 at 13:58::= BERNOULLI | SYSTEM. What you says is PL/SQL which is very far to the standard...
-2
With this you get a single line of a sample that is shown random.
SELECT * FROM TABLE@ SAMPLE(10)
FETCH NEXT 1 ROWS ONLY
Jeremy Caney
- 7,102
- 69
- 48
- 77
