SPLIT BY Clause

Explodes records into groups of records using values in one or more columns.

 

Syntax

 

SPLIT BY expression[, expression ...]

 

The SPLIT BY clause has these parts:

 

Part

Description

expression

The expression used for splitting. Must contain a call to one of the split functions or be a query.

 

Remarks

 

Use the SPLIT BY clause to replace each record with a group of records using one or more splitting expressions. Each splitting expression must be a query or must contain a call to one of the special split functions used to convert a single value into a set of values.

 

When the SPLIT BY clause includes more than one splitting expression, the records are first split with the first expression, then the result is split with the second expression, and so on in left-to-right order.

 

The effect of the SPLIT BY clause is the reverse of the GROUP BY clause, in that the GROUP BY clause collapses groups of records into single records, and the SPLIT BY clause explodes single records into groups of records.

 

It is possible to use both the SPLIT BY clause and the GROUP BY clause in the same query. In this case, the SPLIT BY clause will be applied before the GROUP BY clause.

 

To filter the records returned by the SPLIT BY clause, use the LEAVING clause.

 

Examples

 

This example uses the SPLIT BY clause and a query to join each area in drawing D to each point in the same drawing it contains (one could also do this using the INNER JOIN operator):

 

SELECT [ID] AS [AID], [PID] FROM [D] AS [DA]

WHERE IsArea([ID]) SPLIT BY
(SELECT [DP].[ID] AS [PID] FROM [D] AS [DP]
WHERE IsPoint([DP].[ID]) AND Contains([DA].[ID], [DP].[ID]));

 

This example uses the SPLIT BY clause to output each branch of each object in drawing D as a separate geom:

 

SELECT [ID], [P] FROM [D]

SPLIT BY Branches([ID]) AS [P];

 

This example uses the SPLIT BY clause to create the convex hull of each area in drawing D and to output the coordinates in the hulls as separate geoms:

 

SELECT [ID], [P] FROM [D]
WHERE IsArea([ID])

SPLIT BY Coords(ConvexHull([ID])) AS [P];

 

This example uses the SPLIT BY clause to create a Voronoi diagram of all points in drawing D and output a Voronoi area for each point:

 

SELECT [P] FROM (SELECT Voronoi(AllCoords([ID]))

AS [V] FROM [D]
WHERE IsPoint([ID]))

SPLIT BY Branches([V]) AS [P];

 

This example is similar to the above, but uses the INNER JOIN operator to only output a Voronoi area for each point the ID of which exceeds 5:

 

SELECT [ID], [P] FROM
(SELECT [P] FROM

(SELECT Voronoi(AllCoords([ID])) AS [V] FROM [D]
WHERE IsPoint([ID]))

SPLIT BY Branches([V]) AS [P])
INNER JOIN [D] ON Contains([P], [ID])

WHERE IsPoint([ID]) AND [ID] > 5;

 

This example uses the SPLIT BY clause and the GROUP BY clause to create a table of all possible values of X in all coordinates in the drawing D, along with their frequencies:

 

SELECT CentroidX([P]), Count(*) FROM [D]
SPLIT BY Coords([ID]) AS [P]

GROUP BY CentroidX([P]);

 

 

See Also

 

GROUP BY Clause

LEAVING Clause