SQL新手。我正试图获得一些周比一周的趋势来比较各种指标点。这就是我到目前为止所拥有的。运行时很糟糕,因为它遍历了整个表4次,而这只针对过去的4周。是否有任何方法可以改进这一过程,并获得过去几周(而不仅仅是过去4周(的指标?
编辑:这是MySQL。
样本数据:
时间戳 | 度量命中次数 | 度量总计度量值 | |
---|---|---|---|
2022-09-20 06:50:01.332000 | 4 | 1 | |
2022-08-31 08:49:59.086000 | 2 | 3 | 0.6666 |
2022-08-09 04:50:12430000 | 1 | 2 | 0.5 |
这应该会让您更接近。我将让您添加更多功能(如周开始和结束日期(
select floor((daysold - DATEPART(WEEKDAY, GETDATE()::date)) / 7) as weeksold
, sum(metric_hits) as metric_hits_sum
, sum(metric_total) as metrics_total_sum
, avg(metric_value) as metric_value_avg
from (
SELECT datediff(day, [timestamp], GETDATE()::date) as daysold
, metric_hits
, metric_total
, metric_value
from metric_events
where [timestamp] < DATEADD(DAY, -DATEPART(WEEKDAY, GETDATE()::date), GETDATE()::date)
) q
group by floor((daysold - DATEPART(WEEKDAY, GETDATE()::date)) / 7)
order by 1