时间戳

首页 > 解决方案 > 在给定日期范围（日历）内具有开始日期和结束日期的活跃工单的每日计数

google-bigquery - 在给定日期范围（日历）内具有开始日期和结束日期的活跃工单的每日计数

问题描述

我想计算有效票，我有每张票的开始和结束日期。我需要两个日期之间的每日总数（日历）

这是票表（MINITABLE）

Ticket              Created                Closed
MAS-10772905    2020-04-20T14:00:00 
MAS-11542813    2020-07-28T16:00:00    2020-07-30T10:00:00
MAS-11850014    2020-09-04T04:00:00 
MAS-13926615    2021-02-26T02:00:00 
MAS-11960295    2020-09-16T10:00:00    2020-09-16T06:00:00
MAS-12301712    2020-10-27T23:00:00    2021-01-02T04:00:00
MAS-11850014    2020-09-04T23:00:00 
MAS-12361774    2020-11-02T08:00:00 
MAS-13141083    2021-01-02T11:00:00 
MAS-13144525    2021-01-02T14:00:00 
...

这就是我想要得到的

Start       Active Tickets
2020-01-01    10
2020-01-02    8
2020-01-03    14
2020-01-04    13
2020-01-05    11
2020-01-06    16
...

我尝试通过此查询执行此操作，但不工作......它给了我以下失败消息：“如果没有来自连接双方的字段相等的条件，则无法使用 LEFT OUTER JOIN。”

SELECT start, COUNT(DISTINCT Ticket) AS Actived
FROM   (SELECT 
        CAST(CONCAT(DATE(calendar)," ", TIME(00,00,00)) AS DATETIME) AS start,
        CAST(CONCAT(DATE(calendar)," ", TIME(22,00,00)) AS DATETIME) AS ending
        FROM 
        UNNEST(GENERATE_DATE_ARRAY('2020-01-01', (CURRENT_DATE()), INTERVAL 1 DAY)) AS calendar
        ORDER BY start ASC) calendar

LEFT JOIN MINITABLE ON (start BETWEEN Created AND Closed) OR 
                      (ending BETWEEN Created AND Closed) OR 
                      (Created >= start AND Closed <= ending) OR 
                      (Created <= start AND Closed >= ending)
GROUP BY start
ORDER BY start ASC

任何帮助将非常感激！非常感谢你 ：）

标签： google-bigquery

解决方案

考虑下面

select date(timestamp_trunc(hour, day)) day, 
  count(distinct Ticket) Active_Tickets
from `project.dataset.minitable`,
unnest(generate_timestamp_array(timestamp(created), ifnull(current_timestamp(), timestamp(closed)), interval 1 hour)) hour
group by day

首先在这里-您将每个条目行拆分/扩展为相应的每小时行-与创建和关闭之间的小时数一样多的行（如果关闭为空，则将其替换为当前时间戳）。然后您只需按天分组并计算不同的门票 - 就这么简单

推荐阅读