CROSS APPLY code example

Example 1: userdefined function cross apply all days into the future sql

USE Northwind	
GO

SELECT o1.OrderID, o1.OrderDate, ca.OrderID AS NextOrder, 
		ca.OrderDate AS NextOrderDate, CustomerID, 
			DATEDIFF(DAY, o1.OrderDate,ca.OrderDate) DaysToNextOrder
FROM Orders AS o1
	CROSS APPLY
			(SELECT TOP 1 o.OrderDate, o.OrderID
			 FROM Orders AS o 
                         WHERE o.customerID = o1.customerID
				AND o.OrderID > o1.OrderID
			 ORDER BY OrderID) AS ca

ORDER BY CustomerID, o1.OrderID

Example 2: Apply functions to results of SQL queries.

# Apply functions to results of SQL queries.

results = spark.sql(
  "SELECT * FROM people")
names = results.map(lambda p: p.name)