如何在SQL/LookerML中从一组具有各自数据点的天数中度量平均计数

Question

我有下表：

id | decided_at        | reviewer
1    2020-08-10 13:00   john
2    2020-08-10 14:00   john
3    2020-08-10 16:00   john
4    2020-08-12 14:00   jane
5    2020-08-12 17:00   jane
6    2020-08-12 17:50   jane
7    2020-08-12 19:00   jane

我想要做的是得到min和max之间每天的差异，并从id中得到总count，即min、min和max之间的范围以及最大值。目前，我只能得到过去一天的数据。

期望产出：

Date       | Time(h) | Count | reviewer
2020-08-10   3         3       john
2020-08-12   5         4       jane

从这里，我想得到平均显示这个数据在过去x的天数。

如果今天是第13天，过滤过去2天(48小时)的输出：

reviewer | reviews/hour
jane        5/4 = 1.25

例2:如果今天是第13天，在过去3天(48小时)进行过滤。

reviewer | reviews/hour
john       3/3 = 1
jane       5/4 = 1.25

理想情况下，如果在没有使用派生表的LookML 中这样做是可能的，那么最好这样做。否则，SQL中的解决方案将是很好的，我可以尝试转换为LookerML。

谢谢!

Answer 1

在SQL中，一种解决方案是使用两个级别的聚合：

select reviewer, sum(cnt) / sum(diff_h) review_per_hour
from (
    select
        reviewer,
        date(decided_at) decided_date,
        count(*) cnt,
        timestampdiff(hour, min(decided_at), max(decided_at)) time_h
    from mytable
    where decided_at >= current_date - interval 2 day
    group by reviewer, date(decided_at)
) t
group by reviewer

子查询对日期范围进行筛选，由审阅者和日聚合，并计算记录数以及最小日期和最大日期之间的差额(小时)。然后，外部查询由审阅者聚合并进行最终计算。

计算日期差异的实际函数因数据库而异；MySQL支持MySQL-其他引擎都有其他的选择。