当前位置： 文江博客话题详情

snowflake中的start_time和末端之间的分时持续时间

发布于 2025-02-13 04:11:43 字数 1242 浏览 0 评论 0原文

我有一个具有以下格式的表。

user_idstart_timeend_time
aaa0012020-04-04 09：09：04：27.0002020-04-04 09：09：08：34.000，

我需要通过添加一个分钟的插槽作为新列，以使每一分钟以我的新列来划分start_time和end_time之间的持续时间有一个独特的行。 The result I want to see like this:

USER_IDSTART_TIMEEND_TIMEMINUTE_SLOT
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:54.0002020-04-04 09:05:27.000
AAA0012020-04-04 09：04：27.0002020-04-04 09：08：54.0002020-04-04 09:06:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:54.0002020-04-04 09:07:27.000
AAA0012020-04-04 09:04 ：27.0002020-04-04 09：08：54.0002020-04-04 09：08：27.000
AAA0012020-04-04 09：04：04：27.0002020-04-04-04 09：08：54.0002020-04-04-04-09：09：09：27.000

如果有人可以告诉我这是可能的，并且如何在雪花中做到这一点，这将不胜感激，谢谢！

原文

I have a table with following format.

USER_IDSTART_TIMEEND_TIME
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.000

I need to split the duration between START_TIME and END_TIME by adding a one minute slots as a new column such that for each minute i have a unique row. The result I want to see like this:

USER_IDSTART_TIMEEND_TIMEMINUTE_SLOT
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:54.0002020-04-04 09:05:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:54.0002020-04-04 09:06:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:54.0002020-04-04 09:07:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:54.0002020-04-04 09:08:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:54.0002020-04-04 09:09:27.000

If anyone can tell me is this possible and how to do this in snowflake, that would be much appreciated, thanks!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

暖心男生 2025-02-20 04:11:43

使用以下数据 -

with data(USER_ID,START_TIME,END_TIME) as
(select * from values
('AAA001','2020-04-04 09:04:27.000'::timestamp,'2020-04-04 09:08:34.000'::timestamp),
('AAA002','2020-04-04 09:04:27.000'::timestamp,'2020-04-04 09:08:34.000'::timestamp)
),

第一种方法，如@Dave共享的一种方法，并具有限制子句（使用大生成器值） -

rn_cte as (
select row_number () over (partition by user_id order by 0) rn,user_id,
start_time,
end_time,
timeadd(minute,
rn,
start_time) new_time
from data,
table(generator(rowcount=>150))
qualify rn <= TIMESTAMPDIFF(minute, start_time,end_time)
)select user_id, start_time, end_time, new_time
from rn_cte;

或者，由于限制了给出恒定值，以获取虚拟行并避免额外的行生成> Generator 子句。

select user_id,
start_time,
end_time,
timeadd(minute, index, start_time) minute_slot from data,
table(split_to_table(repeat(',',TIMESTAMPDIFF(minute, start_time,end_time)-1),','));

两者都给出以下输出

-USER_IDstart_timeend_timenew_time
aaa0012020-04-04 09：04：27.0002020-04-04-04 09：08：34.000 2020-04-04-09：09：05：27.000 aaa001 2020-04-04-04-09：04：09：04：09：04：09:04：09:04
：09: 04： 27.0002020-04-04 09：08：34.0002020-04-04 09:06:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:07:27.000
AAA0012020-04-04 09:04 ：27.0002020-04-04 09：08：34.0002020-04-04 09:08:27.000
AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:05:27.000
AAA0022020-04-04 09:04 ：27.0002020-04-04 09：08：34.0002020-04-04 09：06：27.000
AAA0022020-04-04 09：04：27.000 2020-04-04 09：08：08：34.0002020-04-04-09：09：07：27.000 AAAAA002 2020-2020-2020-04-04-04-09：04-04-09：04：04-04-04-04-04-04-04-09：04：04：04：04：04：04：
04 ：04 ：04： ：27.0002020-04-04 09：08：34.0002020-04-04 09：08：27.000

With following data -

with data(USER_ID,START_TIME,END_TIME) as
(select * from values
('AAA001','2020-04-04 09:04:27.000'::timestamp,'2020-04-04 09:08:34.000'::timestamp),
('AAA002','2020-04-04 09:04:27.000'::timestamp,'2020-04-04 09:08:34.000'::timestamp)
),

First approach, as the one shared by @Dave, with qualify clause to limit (use large generator value) -

rn_cte as (
select row_number () over (partition by user_id order by 0) rn,user_id,
start_time,
end_time,
timeadd(minute,
rn,
start_time) new_time
from data,
table(generator(rowcount=>150))
qualify rn <= TIMESTAMPDIFF(minute, start_time,end_time)
)select user_id, start_time, end_time, new_time
from rn_cte;

Or, another one to get dummy rows and avoiding extra row generation due to limitation of giving constant value in generator clause.

select user_id,
start_time,
end_time,
timeadd(minute, index, start_time) minute_slot from data,
table(split_to_table(repeat(',',TIMESTAMPDIFF(minute, start_time,end_time)-1),','));

Both giving following output -

USER_IDSTART_TIMEEND_TIMENEW_TIME
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:05:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:06:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:07:27.000
AAA0012020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:08:27.000
AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:05:27.000
AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:06:27.000
AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:07:27.000
AAA0022020-04-04 09:04:27.0002020-04-04 09:08:34.0002020-04-04 09:08:27.000
回复 原文
定格我的天空 2025-02-20 04:11:43

假设您想在start_time和end_time之间保持微小的插槽，并假设end_time -start_time＆lt; = 24小时：

with 
  t0 as
  (select
    'AAA001' as user_id,
    '2020-04-04 09:04:27.000' as start_time,
    '2020-04-04 09:08:34.000' as end_time),
  t1 as
  (select row_number() over(order by 0) as i
   from table(generator(rowcount => 3600))) -- 3600 minutes in 24 hours
select
  t0.user_id,
  t0.start_time,
  t0.end_time,
  timeadd("minutes", i, t0.start_time) as minute_slot
from t1 cross join t0
where minute_slot <= end_time

基于一个想法，请 this”> this答案

Assuming you want to keep the minute slots between the start_time and end_time and assuming that the end_time - start_time <= 24 hours:

with 
  t0 as
  (select
    'AAA001' as user_id,
    '2020-04-04 09:04:27.000' as start_time,
    '2020-04-04 09:08:34.000' as end_time),
  t1 as
  (select row_number() over(order by 0) as i
   from table(generator(rowcount => 3600))) -- 3600 minutes in 24 hours
select
  t0.user_id,
  t0.start_time,
  t0.end_time,
  timeadd("minutes", i, t0.start_time) as minute_slot
from t1 cross join t0
where minute_slot <= end_time

Based on an idea at this answer

回复 原文
~没有更多了~

关于作者

德意的啸

暂无简介

文章
评论
613 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文