时间戳

首页 > 解决方案 > Oracle - SQL query to bin the data from one table to another based on a column value?

sql - Oracle - SQL query to bin the data from one table to another based on a column value?

问题描述

What SQL query to use to generate Table2 from Table1(tables shown below)?

Table2 needs to bin the timestamps from Table1 into start and end times according to the Table1.Status field. As a result Table2.Status should toggle between 0 and 1 in every row.

Table2.StartTime = 1st value of Table1.TimeStamp whenever Table1.Status changes from 1 to 0 or viceversa.

Table2.EndTime = (1st value of Table1.TimeStamp of Table1.Status next change) - 1millisecond

The first record in Table1 can start from any Status - 1 or 0.

Table1

TimeStamp                               Status
11-NOV-11 12.45.45.445000000 AM           1
11-NOV-11 12.46.45.445000000 AM           1
11-NOV-11 12.47.45.445000000 AM           1
11-NOV-11 12.48.45.445000000 AM           0
11-NOV-11 12.49.45.445000000 AM           0
11-NOV-11 12.50.45.445000000 AM           1
11-NOV-11 12.51.45.445000000 AM           0
11-NOV-11 12.52.45.445000000 AM           0
11-NOV-11 12.53.45.445000000 AM           1
...

Table2

StartTime                                        EndTime                     Status
11-NOV-11 12.45.45.445000000 AM      11-NOV-11 12.48.45.444000000 AM            1
11-NOV-11 12.48.45.445000000 AM      11-NOV-11 12.50.45.444000000 AM            0
11-NOV-11 12.50.45.445000000 AM      11-NOV-11 12.51.45.444000000 AM            1
11-NOV-11 12.51.45.445000000 AM      11-NOV-11 12.53.45.444000000 AM            0
...

Any help would be appreciated.

标签： sqloracle

解决方案

select min(t2.TimeStamp) Start_time
  , lead(min(t2.TimeStamp), 1)over(order by min(t2.TimeStamp))  - INTERVAL '0.001' SECOND End_time
  , t2.status
from (
  select t1.TimeStamp TimeStamp, STATUS, group_tag, first_value(group_tag ignore nulls)over(order by t1.TimeStamp ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) group_tag_gap_filling
  from (
      select t.TimeStamp, Status, case when lead(status, 1, 2)over(order by t.TimeStamp) != status then row_number()over(order by t.TimeStamp)end group_tag
      from Table1 t
    )t1
  )t2
group by t2.group_tag_gap_filling, t2.status
;

First, in inline view "t1", I create temporary groups Then Secondly, I fill in the gap for all temporary groups Then I extract what is need using min aggregation function, and lead analytic function, and I extract 0.001 second from end_time

推荐阅读