Getting a dynamic Sample

Database
Enthusiast

Getting a dynamic Sample

I am attempting  to create a population based on percentages stored in a table. The table structure is -

employee_id dept allocation_percentage
1 english 100
2 math 39.33259
2 science 60.66741
3 math 100
4 science 100
5 science 100
6 science 41.28975
6 history 58.71025
7 english 69.46117
7 math 30.53883
8 english 69.46117
8 math 30.53883

I want to write a sql which can select a random sample of employees distributed as per following logic -

1.First identify all the employees that share the same departments. Like 7 and 8 in above example. BOth have math and english.

2. For each of these groups randomly distribute them as per allocation_percentage into different departments. So out of all  the employees who took english and math 69.46 % should be assigned to english and 30.53 to math.

Any help is greatly appreciated. Thank you so much in advance.

Regards,

San




6 REPLIES
Senior Apprentice

Re: Getting a dynamic Sample

Hi San,

some questions:

Are the shared departments known in advance or should the query find them?

Are there multiple combinations?

Can there be more than two shared departments?

Is the allocation_percentage the same for all employee sharing the same departments?

Do the percentages always add up to 100?

How many rows are in that table?

And finally, what are you actually trying to do? :-)

Enthusiast

Re: Getting a dynamic Sample

Thanks for your help Dieter. I was literally waiting for your reply :)

So basically each department is claiming that they should be responsible for the employee since the employee works for more than one department. So I want to assign each employee to one department each. Now there can be many combinations of the departments.

There can be more than 2 shared departments. The percentages always add up to 100.

The allocation % for the same combination will always remain same.

So the way I was trying to do is -

1. I created a column concatenating all the departments for one employee.

2. Then I am doing a group by on that concatenated column.

3. My problem is - I am unable to allocate employees as per the percentages. Like  out of all  the employees who took english and math 69.46 % should be assigned to english and 30.53 to math from above example.

Senior Apprentice

Re: Getting a dynamic Sample

You can assign a percentage based on a PERCENT_RANK * 100. If it needs to be statistically random you must use a Derived Table to create a RANDOM(1,2000000000) number to ORDER BY it within the PERCENT_RANK. 

Is this related to your other post on recursion?

Enthusiast

Re: Getting a dynamic Sample

I was able to get the recursive things working from other post - to get a column with concatenated departments.

I was trying something like this to get the data -

WITH RECURSIVE rec_emps(emp_id, dept_id, ALL_depts)

AS   (    

select distinct emp_id, dept_id, ALL_depts from vt_alloc3 qualify row_number() OVER (PARTITION BY all_depts order by  emp_id) = 1

union all

(

select  a.* from  (

select distinct vt_alloc3.emp_id, vt_alloc3.dept_id, vt_alloc3.ALL_dept from vt_alloc3 WHERE ALLOC_PCT <> 100

qualify row_number() OVER (PARTITION BY vt_alloc3.ALL_dept, vt_alloc3.dept_id order by  vt_alloc3.emp_id) <= (( vt_alloc3.alloc_pct * vt_alloc3.cnt_cust ) /100)

  ) a  where

  vt_alloc3.emp_id <> a.emp_id

)

 )

SELECT * from rec_emps order by  emp_id,all_dept 

Enthusiast

Re: Getting a dynamic Sample

my cnt_cust is the total number of customers per each combination of departments.

Enthusiast

Re: Getting a dynamic Sample

Also, can you please send me a sample query on the approach you suggested? Thank you so much!!