Fixed Size Random Sample based on current query

Question

I would like to random sample 150 WORK ORDERS based on my current query and I added a SAMPLE (3), which is sampling 3%. But every time I run the sample query the number of work order changes. Is there a way I can just say sample 150 work orders?

This is my current query:

SELECT  
WORKORDER.WONUM,
WORKORDER.PARENT,
  WORKORDER.STATUS,
  TO_CHAR(WORKORDER.REPORTDATE, 'DD-MON-YY') AS REPORTDATE,
  TO_CHAR(WORKORDER.ACTSTART, 'DD-MON-YY')   AS ACTSTART,
  TO_CHAR(WORKORDER.ACTFINISH, 'DD-MON-YY')  AS ACTFINISH,
  WORKORDER.HASCHILDREN,
  WORKORDER.ACTLABCOST,
  WORKORDER.ACTMATCOST,
  WORKORDER.ACTTOOLCOST,
  WORKORDER.WOACCEPTSCHARGES,
  WORKORDER.EXT_JOBCODE,
  WORKORDER.WORKTYPE,
  WORKORDER.DESCRIPTION,
  WORKORDER.ACTSERVCOST,
  WORKORDER.EXT_DISTWORKTYPE,
  WORKORDER.LOCATION,
  LOCATIONS.EXT_OFFICE,
  LOCATIONS.EXT_STATECODE,
  WORKORDER.OWNERGROUP,
  CASE
    WHEN LOCATIONS.EXT_SRV_POLYGON IN ('BOF', 'CDA', 'COL', 'DAV', 'GOS', 'KEL', 'KLF', 'LAG', 'LEC', 'MED', 'PUM', 'RIT', 'ROS', 'SAN', 'SPO')
    THEN
      CASE
        WHEN WORKORDER.EXT_DISTWORKTYPE IN ('EC', 'ES', 'ET')
        THEN 'WRONG POLYGON'
        ELSE 'GAS'
      END
    WHEN LOCATIONS.EXT_SRV_POLYGON IN ('CDC', 'COC', 'DAC', 'DPC', 'GRC', 'KEC', 'LCC', 'OTC', 'PAC', 'SAC', 'SPC')
    THEN
      CASE
        WHEN WORKORDER.EXT_DISTWORKTYPE IN ('GC', 'GS', 'GT')
        THEN 'WRONG POLYGON'
        ELSE 'ELECTRIC'
      END
    ELSE 'MISSING'
  END                                           AS TYPE,
  TO_CHAR(WORKORDER.SCHEDSTART, 'DD-MON-YY')    AS SCHEDSTART,
  TO_CHAR(WORKORDER.SCHEDFINISH, 'DD-MON-YY')   AS SCHEDFINISH,
  TO_CHAR(WORKORDER.TARGCOMPDATE, 'DD-MON-YY')  AS TARGCOMPDATE,
  TO_CHAR(WORKORDER.TARGSTARTDATE, 'DD-MON-YY') AS TARGSTARTDATE,
  WORKORDER.REPORTEDBY
FROM WORKORDER
INNER JOIN LOCATIONS
ON WORKORDER.LOCATION   = LOCATIONS.LOCATION
WHERE ((WORKORDER.EXT_JOBCODE NOT LIKE 'A%') AND (WORKORDER.EXT_JOBCODE NOT LIKE 'B%') OR (WORKORDER.EXT_JOBCODE IS NULL))
AND WORKORDER.STATUS IN ('COMP', 'CLOSE') --COMMENT OUT FOR BLANKET WORKORDERS
--AND WORKORDER.WONUM LIKE 'B%' --FOR BLANKET WORKORDERS
AND WORKORDER.ACTFINISH > '01-FEB-15'--WORKORDER COMPLETED OR CLOSED INCLUDING WOS FROM CONVERSION THAT WERE OPEN / COMMENT OUT FOR BLANKET WOS
AND WORKORDER.SITEID = 'OPS'
--AND WORKORDER.EXT_DISTWORKTYPE IN ('EC','GC') --Only enable this line if I am running report for Lamont's request
--AND WORKORDER.ACTLABCOST != '0' --USED FOR TROUBLESHOOTING TO SEE LABORCOSTS ONLY
ORDER BY WORKORDER.WONUM;
--AND WORKORDER.EXT_JOBCODE NOT IN ('K008','K009','I006','I007','I008')--Per Rodeny not to worry about taking out these job codes since they are still being handled by gas and electric construction
--AND TO_CHAR(WORKORDER.ACTFINISH,'MM') =  TO_CHAR(SYSDATE,'MM')-1
--AND TO_CHAR(WORKORDER.ACTFINISH, 'YY') = TO_CH`enter code here`AR(SYSDATE,'YY')

Alex Poole · Accepted Answer · 2015-08-05 16:46:58Z

You can't do that with the sample clause:

This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table.

To get an exact number you could use a subquery with an analytic function added to the select list, but otherwise with your existing query:

SELECT * -- list the columns to avoid the dummy RN appearing
FROM (
  SELECT  
    WORKORDER.WONUM,
    WORKORDER.PARENT,
    ...
    WORKORDER.REPORTEDBY,
    -- randomly ordered
    ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) AS RN
  FROM WORKORDER
  ...
)
WHERE RN <= 150
ORDER BY WONUM;

Which is less convenient than just using sample, but more explicit. From 12c you could use the fetch clause, ordering by random instead of your work order number; but you'd still need a subquery to then order the resulting 150 rows by the work order number.

Collectives™ on Stack Overflow

Fixed Size Random Sample based on current query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related