最近需要计算不同业务最近几单成交价的平均价。
个人水平有限,如果用Group By,只能计算每业务所有单的平均价,因为时间不确定,也不能在where中加时间条件。查了一下MySql的说明,发现有一些窗口函数或者可以帮上忙。
第一步:每条计录按时间加上序号
SELECT _service,_price,_date,
ROW_NUMBER() OVER (PARTITION BY _service ORDER BY _date desc) AS row_num
FROM `order` t1
“ROW_NUMBER() OVER (PARTITION BY _service ORDER BY _date desc) AS row_num”的作用是生成一个序号,order by子句指定按时间顺序。结果如下:
这个Select里面直接用where或having筛选row_num是不行的,因为这个字段是结果集出来以后才加上去的,执行顺序比where或having都要晚。
第二步:再套一层select...group by
将上面查询的结果当作源表,再套一层select就可以用where和having筛选row_num了。
SELECT _service,AVG(_price) AS price_avg
FROM (SELECT _service,_price,_date,
ROW_NUMBER() OVER (PARTITION BY _service ORDER BY _date) AS row_num
FROM `order` t1) t2
WHERE row_num<=10
GROUP BY _service
参考官网文档说明:MySQL :: MySQL 8.0 Reference Manual :: 12.20.2 Window Function Concepts and Syntax
本人水平有限,如果大家有更好的办法,诚请告知。如果上面的代码对大家有参考作用,也是很好的事。