How to to average the counts between parts in SQL

Question

I have a bunch of sales ids with associated counts divided into four different groups/parts using ntile funtion and table data looks something like this:

existing Data of sales

I would like to write a sql to balance the all four groups to have average counts. if you observe the data, group four having two sales ids with huge volume that should be distributed to other groups as shown below :

Required data of sales

SELECT TABLEA.SALESID,TABLEA.COUNT,ntile(4) OVER(ORDER BY null) AS PART NUMBER FROM TABLEA;

Answer 1

For a fixed number of buckets, you can use a MODEL clause:

select sales_id,
       cnt,
       part_number,
       bucket
FROM (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY cnt DESC) AS rn
  FROM   table_name t
)
MODEL
  DIMENSION BY (rn)
  MEASURES (
    sales_id,
    cnt,
    part_number,
    0 AS bucket,
    0 AS b1,
    0 AS b2,
    0 AS b3,
    0 AS b4
  )
  RULES AUTOMATIC ORDER (
    bucket[1] = 1,
    b1[1] = cnt[1],
    b2[1] = 0,
    b3[1] = 0,
    b4[1] = 0,
    bucket[rn>1] = CASE LEAST(b1[cv()-1], b2[cv()-1], b3[cv()-1], b4[cv()-1])
                   WHEN b1[cv()-1] THEN 1
                   WHEN b2[cv()-1] THEN 2
                   WHEN b3[cv()-1] THEN 3
                   WHEN b4[cv()-1] THEN 4
                   END,
    b1[rn>1] = b1[cv()-1] + CASE bucket[cv()] WHEN 1 THEN cnt[cv()] ELSE 0 END,
    b2[rn>1] = b2[cv()-1] + CASE bucket[cv()] WHEN 2 THEN cnt[cv()] ELSE 0 END,
    b3[rn>1] = b3[cv()-1] + CASE bucket[cv()] WHEN 3 THEN cnt[cv()] ELSE 0 END,
    b4[rn>1] = b4[cv()-1] + CASE bucket[cv()] WHEN 4 THEN cnt[cv()] ELSE 0 END
);

Which, for the sample data:

CREATE TABLE table_name (sales_id, cnt, part_number) AS
SELECT  1, 5000, 4 FROM DUAL UNION ALL
SELECT  2, 4000, 4 FROM DUAL UNION ALL
SELECT  3, 3000, 3 FROM DUAL UNION ALL
SELECT  4, 2000, 3 FROM DUAL UNION ALL
SELECT  5, 1000, 3 FROM DUAL UNION ALL
SELECT  6,  500, 2 FROM DUAL UNION ALL
SELECT  7,  400, 2 FROM DUAL UNION ALL
SELECT  8,  300, 2 FROM DUAL UNION ALL
SELECT  9,  200, 1 FROM DUAL UNION ALL
SELECT 10,  100, 1 FROM DUAL UNION ALL
SELECT 11,   10, 1 FROM DUAL;

Outputs:

SALES_ID	CNT	PART_NUMBER	BUCKET
1	5000	4	1
2	4000	4	2
3	3000	3	3
4	2000	3	4
5	1000	3	4
6	500	2	3
7	400	2	4
8	300	2	4
9	200	1	3
10	100	1	3
11	10	1	4

fiddle