...

2.1 COUNT DISTINCT

 
performance optimization practice(38)

Find the number of customers who place an order in each month:
SQL

SELECT count(DISTINCT CustomerID) num,year(OrderDate) years, 
    month(OrderDate) months 
FROM Orders
WHERE OrderDate>='2022-01-01' and EmployeeID=5
GROUP BY year(OrderDate), month(OrderDate)
ORDER BY years,months

SPL

A
1 >st=date(“2022-01-01”), start=days@o(st)
2 =file(“Orders.ctx”).open().cursor(OrderDate,CustomerID;OrderDate>=start && EmployeeID==5)
3 =A2.groups(year(OrderDate):years,month(OrderDate):months;icount(CustomerID):num)

A3 Use icount function to calculate the number of distinct members.

performance optimization practice(38)
 
 •  141 View  •