sql - BigQuery:如何将分钟归因于个别日子
问题描述
我在 BigQuery 中有一个具有以下架构的表:
deviceId int,
state int,
started timestamp,
duration minutes
此处的一行表示此设备在接下来的几分钟内从 start 开始处于此状态。这里的分钟可能超过几天。
现在我想知道这个 deviceId 每天有多少分钟处于这种状态。所以上表中的一行可以表示为以下列表:
deviceId int,
state int,
date date,
duration minutes
这是我正在玩的一些示例表:
WITH `temp.test` AS (
SELECT 1 id, 1 state, TIMESTAMP('2018-08-17 10:40:00') ts, 120 minutes UNION ALL
SELECT 1, 2, '2018-08-17 12:40:00', 120 UNION ALL
SELECT 1, 1, '2018-08-17 14:40:00', 560 UNION ALL
SELECT 2, 1, '2018-08-17 09:00:00', 180 UNION ALL
SELECT 2, 2, '2018-08-17 12:00:00', 2940
)
对于最后一行,其持续时间为 2940 分钟,即 49 小时(2 天零 1 小时),因此需要将其转换为多行。我想要从这个例子中得到的输出如下:
1, 1, 2018-08-17, 120
1, 2, 2018-08-17, 120
1, 1, 2018-08-17, 560
2, 1, 2018-08-17, 180
2, 2, 2018-08-17, 720
2, 2, 2018-08-17, 1440
2, 2, 2018-08-17, 780
有没有办法在 SQL 中实现这一点,或者我应该编写一个 UDF?
解决方案
以下示例适用于 BigQuery 标准 SQL
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 id, 1 state, TIMESTAMP('2018-08-17 10:40:00') ts, 120 minutes UNION ALL
SELECT 1, 2, '2018-08-17 12:40:00', 120 UNION ALL
SELECT 1, 1, '2018-08-17 14:40:00', 560 UNION ALL
SELECT 2, 1, '2018-08-17 09:00:00', 180 UNION ALL
SELECT 2, 2, '2018-08-17 12:00:00', 2940
)
SELECT
id, state, day,
CASE
WHEN day = first_day AND day = last_day THEN minutes
WHEN day = first_day THEN 24*60 - TIMESTAMP_DIFF(ts, TIMESTAMP(first_day), MINUTE)
WHEN day = last_day THEN TIMESTAMP_DIFF(TIMESTAMP_ADD(ts, INTERVAL minutes MINUTE), TIMESTAMP(last_day), MINUTE)
ELSE 24*60
END duration
FROM `project.dataset.table`,
UNNEST(GENERATE_DATE_ARRAY(DATE(ts), DATE(TIMESTAMP_ADD(ts, INTERVAL minutes - 1 MINUTE)), INTERVAL 1 DAY)) day,
UNNEST([STRUCT<first_day DATE, last_day DATE>(DATE(ts), DATE(TIMESTAMP_ADD(ts, INTERVAL minutes MINUTE)))]) key
结果:
Row id state day duration
1 1 1 2018-08-17 120
2 1 2 2018-08-17 120
3 1 1 2018-08-17 560
4 2 1 2018-08-17 180
5 2 2 2018-08-17 720
6 2 2 2018-08-18 1440
7 2 2 2018-08-19 780
您唯一需要调整的(我认为)是在下面 使用minutes - 1
vsminutes
TIMESTAMP_ADD(ts, INTERVAL minutes - 1 MINUTE)
这是为了处理期末到一天开始的情况 - 当天的持续时间为 0(零)
推荐阅读
- asp.net-mvc - 是否可以在 ASP.NET MVC Core 中使用 Serilog 登录内部视图?
- ruby - Ruby:调用定义的函数
- javascript - 当没有可显示的内容时,如何使 React Router 呈现 NotFound?
- kubernetes - ConfigMap 和 Secret 是否在节点级别进行管理?
- java - 填充数组列表。爪哇
- scheduling - 如何在 cplex 中查找进程的完成时间
- node.js - 我如何找到一个 mongoDB 文档和第二个基于其 id 作为第一个属性的第二个(如内部连接)
- c++ - 如何使用 CMake 安装依赖项?
- javascript - 我想创建一个介于 100 和 1000 之间的随机数,但我不想重复该数字,例如 101 或 111 或 909?
- javascript - 如何将初始化的接口从父接口转换回子接口