开封
乾学院 6 号会员
566 浏览  • 
     ...

3.7 组内第一条 / 最后一条

 
SPL性能优化基础练习(38)

SQL

WITH m AS(
  SELECT *, row_number() OVER(PARTITION BY year(OrderDate),month(OrderDate) 
     ORDER BY OrderDate) r 
  FROM Orders
  WHERE OrderDate>='2021-01-01')
SELECT * 
FROM m 
WHERE r=1

SPL

A
1 >st=date(“2021-01-01”), start=days@o(st)
2 =file(“Orders_Time.ctx”).open().cursor@x(;OrderDate>=start)
3 =A2.groups@o(month@y(OrderDate):monthes;top(1,0)).conj(#2)
4 =A3.run(OrderDate=date@o(OrderDate))

A3 取每组第一条记录。top(1,0) 第一个参数表示取几条，第二个参数表示按这个参数排序取排在前面的 n 个；这里第一个参数用 1，表示只取一条记录；第二个参数用常数表示不用排序，所以最后就是分组后从原序集合中取第 1 条记录

SQL

WITH m AS(
    SELECT *,year(OrderDate) years,month(OrderDate) months, 
      row_number() OVER(PARTITION BY year(OrderDate),month(OrderDate) 
        ORDER BY OrderDate) r 
    FROM Orders
    WHERE OrderDate>='2021-01-01'),
    m1 AS(SELECT years,months,max(r) r FROM m GROUP BY years,months)
SELECT * 
FROM m,m1
WHERE m.r=m1.r and m.years=m1.years and m.months=m1.months

SPL

A
1 >st=date(“2021-01-01”), start=days@o(st)
2 =file(“Orders_Time.ctx”).open().cursor@x(;OrderDate>=start)
3 =A2.groups@o(month@y(OrderDate):monthes;top(-1,0)).conj(#2)
4 =A3.run(OrderDate=date@o(OrderDate))

A3 取每组最后一条记录。top(-1,0) 第一个参数用负数，表示按第二个参数排序后从末尾取 n 个

SQL

WITH m AS(
    SELECT *, row_number() OVER(PARTITION BY CustomerID ORDER BY OrderDate) r 
    FROM Orders
    WHERE OrderDate>='2021-01-01')
SELECT month(OrderDate) AS Months,count(*) Num
FROM m 
WHERE r=1
GROUP BY month(OrderDate)

SPL

A
1 >st=date(“2021-01-01”), start=days@o(st)
2 =file(“Orders_Account.ctx”).open().cursor@x(;OrderDate>=start)
3 =A2.group@s(CustomerID;top(1,0)).conj(#2)
4 =A3.groups(month(OrderDate):Months;count(1):Num)

还可以用 group@1() 来写

A
3 =A2.group@1(CustomerID)

SQL

WITH m AS(
    SELECT *,row_number() OVER(PARTITION BY CustomerID ORDER BY OrderDate) r 
    FROM Orders
    WHERE OrderDate>='2022-01-01'),
    m1 AS(SELECT CustomerID,max(r) r FROM m GROUP BY CustomerID)
SELECT month(m.OrderDate) AS Months,count(*) Num
FROM m,m1
WHERE m.r=m1.r and m.CustomerID=m1.CustomerID
GROUP BY month(m.OrderDate)

SPL

A
1 >st=date(“2022-01-01”), start=days@o(st)
2 =file(“Orders_Account.ctx”).open().cursor@x(;OrderDate>=start)
3 =A2.group@s(CustomerID;top(-1,0)).conj(#2)
4 =A3.groups(month(OrderDate):Months;count(1):Num)

 

SPL性能优化基础练习(38)