如何通过SQL查询从Clickhouse获得当天的p95和当天最后7天的p95？
如何通过SQL查询从Clickhouse获得当天的p95和当天最后7天的p95？
提问于 2022-03-11 05:57:19
现在，我的代码是：
select
toStartOfDay(time_stamp) as day,
quantileExact(0.95)(duration) as p95,
'' as day7_p95 --what code?
from
duration_all
where
time_stamp >= '2022-03-03 00:00:00' and
time_stamp <= '2022-03-09 00:00:00'
group by toStartOfDay(time_stamp)
order by toStartOfDay(time_stamp)产出如下：
但是我只能获得每天的p95，我不知道如何修改上面的SQL以获得每天将近7天的p95。
回答 1
Stack Overflow用户
发布于 2022-03-11 21:59:58
SELECT
day,
finalizeAggregation(0.95)(state) AS p95,
quantileExactMerge(0.95)(state) OVER (ORDER BY day ASC Rows BETWEEN 7 PRECEDING AND CURRENT ROW) AS day7_p95
FROM
(
SELECT
toStartOfDay(time_stamp) AS day,
quantileExactState(0.95)(val) AS state
FROM
(
SELECT
now() - (number * 3600) AS time_stamp,
(rand() % 33333) / 10000 AS val
FROM numbers(500)
)
GROUP BY day
ORDER BY day ASC
)
Query id: ad35a361-730e-4860-99ce-35e88a076a45
┌─────────────────day─┬────p95─┬─day7_p95─┐
│ 2022-02-19 00:00:00 │ 3.2733 │ 3.2733 │
│ 2022-02-20 00:00:00 │ 3.1988 │ 3.2079 │
│ 2022-02-21 00:00:00 │ 3.0383 │ 3.1988 │
│ 2022-02-22 00:00:00 │ 2.9919 │ 3.1988 │
│ 2022-02-23 00:00:00 │ 3.1512 │ 3.1988 │
│ 2022-02-24 00:00:00 │ 3.0554 │ 3.1512 │
│ 2022-02-25 00:00:00 │ 2.9619 │ 3.1362 │
│ 2022-02-26 00:00:00 │ 3.3009 │ 3.1783 │
│ 2022-02-27 00:00:00 │ 2.9855 │ 3.1512 │
│ 2022-02-28 00:00:00 │ 3.0934 │ 3.1266 │
│ 2022-03-01 00:00:00 │ 3.2075 │ 3.174 │
│ 2022-03-02 00:00:00 │ 3.1238 │ 3.174 │
│ 2022-03-03 00:00:00 │ 3.319 │ 3.1783 │
│ 2022-03-04 00:00:00 │ 2.9233 │ 3.1783 │
│ 2022-03-05 00:00:00 │ 3.1928 │ 3.1857 │
│ 2022-03-06 00:00:00 │ 3.1941 │ 3.1857 │
│ 2022-03-07 00:00:00 │ 3.0741 │ 3.1857 │
│ 2022-03-08 00:00:00 │ 3.1022 │ 3.1857 │
│ 2022-03-09 00:00:00 │ 2.9859 │ 3.1238 │
│ 2022-03-10 00:00:00 │ 3.1898 │ 3.1446 │
│ 2022-03-11 00:00:00 │ 2.7417 │ 3.1222 │
│ 2022-03-12 00:00:00 │ 3.2549 │ 3.1898 │
└─────────────────────┴────────┴──────────┘页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71434197
复制相关文章
相似问题
腾讯云开发者
