1

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.

hubson bropa
  • 2,662
  • 2
  • 29
  • 34
Kuusum
  • 11
  • 1
  • 2

3 Answers3

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,135
  • 12
  • 26
  • 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
  • 2
    @hubson: `SAMPLE` only works on the table, not on an inline view. – Jeffrey Kemp Jan 23 '13 at 08:27
2

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,662
  • 2
  • 29
  • 34
-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
  • 6,191
  • 35
  • 44
  • 70
Guillermo B. De Jesús Castillo
  • 1