Sample Clause after Where condition in Oracle

1
votes

I have a table in Oracle where I wanted to get 10% sample records after some conditions applied. I searched in Google but I am not getting correct algorithm to use SAMPLE clause after WHERE clause in Oracle.

I explained my requirement with an example below. Can you please suggest how to write sql query? My attemps are not working.

Table Name: ACCOMMODATION

Table Records:

ACCOM_ID    ACCOM_TYPE
--------    ----------
1       HOTEL
2       HOTEL
3       HOTEL
4       HOTEL
5       HOUSE
6       HOUSE
7       CRUISE

Here I wanted to get 50% sample records after where condition applied. That is,

SELECT * FROM (SELECT * FROM ACCOMMODATION WHERE ACCOM_TYPE = 'HOTEL') T SAMPLE(50);

or

SELECT * FROM (SELECT * FROM ACCOMMODATION WHERE ACCOM_TYPE = 'HOUSE') T SAMPLE(50);

But above queries are not working, fails with error ORA-00933: SQL command not properly ended . If I use like below, it is not correctly returning results to me. I want only 2 random hotel records but it sometimes return 2, sometimes 3, sometimes 4. 

select * from accommodation sample(50) where accom_type = 'HOTEL';

Please refer sql fiddle example HERE.

Edit: This is the sample table. Real table I use have plenty of records so unfortunately I am not able to order by dbms_random.value() as It takes very very long time to finish.

3
sqloracleoracle11g
If I am understanding your requirement; you want to get random 50% of your records in whatever sort? If you have 6 records, you want a random 3 records to be displaying? - Isaiah3015
I want 50% records after where condition applied. Eg: 50% Hotel in Accommodation, 50% House in Accommodation. - Abdul Hameed

3 Answers

1
votes

This should work, but it won't be fast for a big table:

select * FROM (
  select * from accommodation 
  where accom_type = 'HOTEL'
  order by dbms_random.value
)
WHERE rownum <= 0.5 * (
  SELECT count(*) FROM accommodation where accom_type = 'HOTEL'
)
;

Demo: http://sqlfiddle.com/#!4/6bf8b/13

1
votes

here is a sample code on given table to get the results as per your need. I am fetching 80% of rows from each ACCOM_TYPE.

  SELECT *
    FROM (  SELECT a.*,
                   ROW_NUMBER () OVER (PARTITION BY ACCOM_TYPE ORDER BY ACCOM_ID)
                      AS pos
              FROM ACCOMMODATION a
          ORDER BY ACCOM_TYPE, pos) t
   WHERE pos <= (SELECT ROUND ( (COUNT (*) * 80) / 100)
                   FROM ACCOMMODATION
                  WHERE ACCOM_TYPE = t.ACCOM_TYPE)
ORDER BY ACCOM_TYPE, DBMS_RANDOM.VALUE;

In inline view query find out the row_number as pos column alias, then in main query filter the rows base on count of 80%(this you can change accordingly) of total rows for each ACCOM_TYPE.

Finally for random results use DBMS_RANDOM.VALUE in order by clause.

Hope this helps.

0
votes

One way is to count the number of rows that you have for HOTEL and HOUSE and then use that number to divide /2. This query will count the number of rows for HOUSE and use the rownum number you get from the subquery

Select * from Accommodation
Where ACCOM_TYPE = 'HOTEL'
and rownum <= (Select count(accom_id)/2 as HotelCount from ACCOMMODATION 
WHERE ACCOM_TYPE = 'HOTEL'
group by accom_type)
UNION
Select * from Accommodation
Where ACCOM_TYPE = 'HOUSE'
and rownum <= (Select count(accom_id)/2 as HotelCount from ACCOMMODATION 
WHERE ACCOM_TYPE = 'HOUSE'
group by accom_type)