如何获得比原始表更高的行输出的SQL输出？ - 事件的时间表

发布于 2025-02-10 01:33:09 字数 836 浏览 4 评论 0原文

我有一个表如下：

event_num    occurs_at_time     length
1            0                  10
2            10                 3
3            20                 10
4            30                 5

预期输出：

start_time     length      event_type
0              10          Occurrence
10             3           Occurrence
13             7           Free Time
20             10          Occurrence
30             5           Occurrence

我很难弄清楚如何为空闲时间在select>选择语句中创建新行。 免费时间每当下一个行之间的差异之间发生事件，即0。

例如，在event_num 2和event_num 3，20-10-3 = 7之间10 + 3 = 13将是start_time。

我尝试使用lag（）和lead（） window> case 条款以比较下一行，但我不是确保如何在中间创建一个新行。

原文

I have a table as follows:

event_num    occurs_at_time     length
1            0                  10
2            10                 3
3            20                 10
4            30                 5

Intended output:

start_time     length      event_type
0              10          Occurrence
10             3           Occurrence
13             7           Free Time
20             10          Occurrence
30             5           Occurrence

I'm having a hard time figuring out how to create a new row for Free Time in a SELECT statement. Free Time events occur whenever the difference between the next row occurs_at_time and previous row length + occurs_at_time is > 0.

For instance, between event_num 2 and event_num 3, 20 - 10 - 3 = 7 is the length and 10 + 3 = 13 will be the start_time.

I tried using LAG() and LEAD() window functions with CASE WHEN clauses to compare the next and previous rows, but I'm not sure how I can create a new row in the middle.

分享到QQ

分享到微博

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

发布评论

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

茶花眉 2025-02-17 01:33:09

请使用-USTUPS

select * from (
  select occurs_at_time as start_time, 
    length, 
    'Occurrence' as event_type
  from your_table
  union all
  select occurs_at_time + length as start_time, 
    lead(occurs_at_time) over(order by occurs_at_time) - occurs_at_time - length as length, 
    'Free Time' event_type
  from your_table
)
where length > 0

如果应用于您的问题中的示例数据，

Use below

select * from (
  select occurs_at_time as start_time, 
    length, 
    'Occurrence' as event_type
  from your_table
  union all
  select occurs_at_time + length as start_time, 
    lead(occurs_at_time) over(order by occurs_at_time) - occurs_at_time - length as length, 
    'Free Time' event_type
  from your_table
)
where length > 0

if applied to sample data in your question - output is