雅典娜得到百分位数的边界

Question

每个用户都有以下模型预测随时间变化的模式：

model_id版本模型_a模型_a<1>[/tr><1><1><1><1>

时间戳	user_id	预测
2022-06-22 05:29:36.344	1	1	[0.1226]
2022-06-22 05:29:41.307	1	[0.932]
	1	模型a
2022-06-22 05:29:43.511	2	模型_a	[0.0226]
2022-06-22 05:29:43.870	2	模型_a	[0.132]
	2	模型a

您可以尝试的一件事是通过使用第一个CTE:的max_by聚合将windows函数更改为普通组

-- sample data
WITH dataset (timestamp, user_id, model_id, version, prediction) AS (
VALUES (timestamp '2022-06-22 05:29:36.344', 1, 'model_a', '1', array[0.1226]),
(timestamp '2022-06-22 05:29:41.307', 1, 'model_a', '1', array[0.932]),
(timestamp '2022-06-22 05:29:43.511', 2, 'model_a', '1', array[0.0226]),
(timestamp '2022-06-22 05:29:43.870', 2, 'model_a', '1', array[0.132])
)
-- sample query
select user_id, 
max_by(round(prediction[1],5), timestamp) last_prediction
FROM dataset
where date(timestamp) BETWEEN date('2022-06-21') AND date('2022-08-09')
AND version = '1' AND model_id = 'model_a'
GROUP BY user_id;

输出：

user_id	last_prediction
1	0.9320
2	0.1320