Print this page
Segment a Data Extension into equal parts
Knowledge Article Number | 000213707 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Description | It's possible to segment a Data Extension into a number of equal parts such as a 50/50 or 33/33/33 split of subscribers. One way to do this is using a combination of a Query Activity and the SQL ntile function, outlined below. |
||||||||||
Resolution | The Query Activity retrieves the Data Extension information that matches your criteria and include that information in a Data Extension. The "ntile" function divides an ordered data set into segments. The number of segments in the query is dependent on the number of parts desired. This method can be used for any number of parts. Query Activity and nTile function
select * from ( select *, ntile({integer_expression}) over({partition_by}) as tile_nr FROM [{source data extension name}] ) x where x.tile_nr = {order_by_clause}
Example:The example below splits the data extension into thirds where the source data extension is called original_de and the data will be segmented by email.select * from ( select *, ntile(3) over(order by email) as tile_nr FROM [original_de] ) x where x.tile_nr = 1 select * from ( select *, ntile(3) over(order by email) as tile_nr FROM [original_de] ) x where x.tile_nr = 2 select * from ( select *, ntile(3) over(order by email) as tile_nr FROM [original_de] ) x where x.tile_nr = 3 |