Split a Table in SQL in 50/50 per Market

user_11362241 0
Viewed 168

I have following table:

Column1 Column2
13       US
15       UK
94       ES
309      ES
39       US

And so on...

I want to split the table in 50/50 but so that per market (UK, US, ES...) is approximately the same number of entries in both tables. That means if there are currently 50 rows with the market US, then both tables should now have 25 entries.

snowflake-cloud-data-platform sql
2 Answers

Using ROW_NUMBER(), and selecting the first half, we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Column2 ORDER BY Column2) rn,
              COUNT(*) OVER (PARTITION BY Column2) cnt
    FROM yourTable
)

SELECT Column1, Column2
FROM cte
WHERE 1.0*rn / cnt < 0.5;
user_18632290 
WITH CTE(Column1, Column2)AS
(

  SELECT 13,       'US' UNION ALL
  SELECT 15 ,      'UK' UNION ALL
  SELECT 94 ,      'ES' UNION ALL
  SELECT 309,      'ES' UNION ALL
  SELECT 39 ,      'US' UNION ALL
  SELECT 67,       'US' UNION ALL
  SELECT 99,       'US' 
)
SELECT C.Column1,C.Column2,
NTILE(2)OVER(PARTITION BY C.COLUMN2 ORDER BY C.COLUMN1)SPLITT
FROM CTE AS C
ORDER BY C.COLUMN2

Could you please try, if the above is suitable for you. Splitting is done by NTILE-function

user_145355170
Related