英文:
Getting maximum metric value over a period of latest 90 days only
问题
我想找到一种方法，在过去90天内的最大度量值作为一列添加。
SELECT id1,
id2,
date,
metric,
MAX(metric) OVER(PARTITION BY id1, id2 ORDER BY date ROWS BETWEEN 90 PRECEDING AND CURRENT ROW) max_l90
FROM table
我在考虑这样做，但它不起作用。我还尝试过查看窗口函数，但似乎不能在其中插入条件。
有人有想法吗？
英文:
I would like to find a way to add the maximum of a metrics on the last 90 days as a columns.
SELECT id1,
id2,
date,
metric,
MAX(metric) OVER(PARTITION BY id1, id2 WHERE date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)) max_l90
FROM table
I was wondering about doing something like that but it does not works. I also tried to have a look at window functions but it seems that we can't insert conditions in it.
Does any one have an idea ?
答案1
得分: 0
你可以通过在 MAX 函数内嵌套一个 CASE 表达式来以条件方式执行：
SELECT id1,
id2,
date,
metric,
MAX(CASE WHEN date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
THEN metric
END) OVER(PARTITION BY id1, id2) max_l90
FROM table
英文:
You can do it in a conditional way by embedding a CASE expression inside the MAX function:
SELECT id1,
id2,
date,
metric,
MAX(CASE WHEN date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
THEN metric
END) OVER(PARTITION BY id1, id2) max_l90
FROM table
答案2
得分: 0
您可以使用子查询来筛选最近90天的数据，然后使用MAX函数来获取每个id1和id2的指标的最大值。
SELECT id1, id2, date, metric,
(SELECT MAX(metric)
FROM table t2
WHERE t2.id1 = t1.id1 AND t2.id2 = t1.id2 AND t2.date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
) AS max_l90
FROM table t1;
英文:
<p style="margin: 0;">You can use a subquery to filter the data for the last 90 days and then use the MAX function to get the maximum value of the metric for each id1 and id2</p>
SELECT id1, id2, date, metric,
(SELECT MAX(metric)
FROM table t2
WHERE t2.id1 = t1.id1 AND t2.id2 = t1.id2 AND t2.date >= DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY)
) AS max_l90
FROM table t1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论