sql - 如何在 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并从 id 中max获取总数count，即最小值、最小值和最大值之间的范围以及最大值。目前，我只能获得过去一天的数据。

期望的输出：

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。

谢谢！

标签： sqlcountsumlooker

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

子查询过滤日期范围，按审阅者和日期聚合，并计算记录数以及最小和最大日期之间的差异（以小时为单位）。然后，外部查询由审阅者聚合并进行最终计算。

计算日期差异的实际函数因数据库而异；timestampdiff()MySQL 支持 - 其他引擎都有替代方案。