通过时间戳转换表的日期卷积--如何
通过时间戳转换表的日期卷积--如何
提问于 2018-06-28 01:38:47
我们需要从过去到现在的每一天都与一系列带有时间戳的布尔设备转换进行比较。最终的输出应该是一个表，它有一个date:device_id条目，表示它在线的每一天(否则没有该日期的条目)。
以下是单个设备的转换表示例：
要生成卷积日历，请执行以下操作：
calendar AS (
SELECT day
FROM UNNEST (GENERATE_DATE_ARRAY('2011-05-15', CURRENT_DATE())) AS day
),然后，为了至少生成一个只包含转换事件之后的转换日期的表，以便随后可以对它们进行排名并选择最新的(这里是交叉连接--讨厌！)：
joined_with_cal AS (
SELECT
cal.day as online_date,
otr.when_changed,
otr.device_id,
otr.is_online,
otr.rank_by_date
FROM
calendar AS cal
CROSS JOIN
ordered_transitions otr
WHERE
cal.day >= DATE(otr.when_changed)
),然后，尝试按时间戳对分区中的最新记录进行排名和选择的代码(when_changed或ranked_by_date --似乎都不起作用)：
SELECT
online_date,
when_changed,
device_id,
is_online,
rank_by_date,
FROM (
SELECT
online_date,
when_changed,
device_id,
is_online,
rank_by_date,
RANK() OVER (PARTITION BY device_id ORDER BY rank_by_date ASC) as final_rank
FROM
joined_with_cal
)
WHERE
final_rank = 1 AND
-- online_date < '2017-08-01' AND
device_id = 419609
ORDER BY
online_date,
when_changed,
device_id然而，这是不起作用的，显然是丑陋的。
有人能给出一个正确的、优雅的解决方案吗？
提前感谢！
回答 1
Stack Overflow用户
发布于 2018-06-29 05:05:38
@Mikhail:谢谢你的关注，很抱歉我的解释不是很清楚。
在与一位同事讨论后，我最终使用了一个似乎有效的自连接：
trans_as_range_not_first AS (
SELECT
t1.device_id,
t1.rank_by_when,
t2.when_changed as online_start,
t1.when_changed as online_stop,
t1.account_id,
t1.account_name,
t1.server_type
FROM
ordered_trans AS t1 -- lower in rank index, later in time
LEFT JOIN
ordered_trans AS t2 -- greater in rank index, earlier in time
ON
t1.device_id = t2.device_id AND
t1.rank_by_when+1 = t2.rank_by_when -- current and next row
WHERE
t1.is_online = 0 AND t2.is_online = 1
GROUP BY
device_id,
rank_by_when,
online_start,
online_stop,
account_id,
account_name,
server_type
),页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51068573
复制相关文章
相似问题