Блог

Я пытаюсь использовать оператор select для создания представления, транспонируя таблицу с датой и временем в таблицу с записями в каждой строке, время начала и окончания, когда последовательные значения по времени (раздел по станциям) в поле «запись» не равно 0.

Вот пример исходной таблицы.

И как это должно выглядеть после транспонирования.

Кто-нибудь может помочь?

Вы можете использовать conditional_change_event аналитическую функцию для создания специального идентификатора группировки, чтобы разделить их в простом запросе:

 select row_number() over () unique_id, 
       station, 
       min(datetime) startdate, 
       max(datetime) enddate
from  (
  select t.*, CONDITIONAL_CHANGE_EVENT(decode(recording,0,0,1)) 
              over (partition by station order by datetime) chg
  from mytable t
) x
where recording > 0
group by station, chg
order by 1, 2
  

Декодирование заключается только в настройке ваших островов и пробелов (где пробелы записываются <= 0, а острова записываются> 0). Затем событие изменения для этого сгенерирует новый идентификатор для группировки. Также обратите внимание, что я группируюсь по событию изменения, даже если оно не является частью выходных данных.

ROW_NUMBER() лучше всего подходит для разделения. Затем вы можете выполнить самосоединение для секционированных таблиц, чтобы увидеть, превышает ли разница между временами более пяти минут. Я думаю, что лучшим решением является разделение на скользящую сумму разницы во временных метках, смещенную на 5 минут на основе вашего шаблона. Если пять минут не являются регулярным шаблоном, то, вероятно, существует обобщенный подход, который можно использовать с нулями.

Решение, написанное в виде CTE ниже для упрощения создания представления (хотя это медленный просмотр).

 WITH partitioned as (
    SELECT datetime, station, recording, 
    ROW_NUMBER() OVER(PARTITION BY station
    ORDER BY datetime ASC) rn
    FROM table --Not sure what the tablename is
    WHERE recording != 0),
diffed as (
    SELECT a.datetime, a.station, 
    DATEDIFF(mi,ISNULL(b.datetime,a.datetime),a.datetime)-5) Difference
    --The ISNULL logic is for when a.datetime is the beginning of the block,
    --we want a 0
    FROM partitioned a
    LEFT JOIN partitioned b on a.rn = b.rn   1 and a.station=b.station
    GROUP BY a.datetime,a.station),
cumulative as (
    SELECT a.datetime, a.station, SUM(b.difference) offset_grouping
    FROM diff a
    LEFT JOIN diff b on a.datetime >= b.datetime and a.station = b.station ),
ordered as (SELECT datetime,station, 
    ROW_NUMBER() OVER(PARTITION BY station,offset_grouping ORDER BY datetime asc) starter, 
    ROW_NUMBER() OVER(PARTITION BY station,offset_grouping ORDER BY datetime desc) ender
    FROM cumulative)
SELECT ROW_NUMBER() OVER(ORDER BY a.datetime) unique_id,a.station,a.datetime startdate, b.datetime enddate
FROM ordered a
JOIN ordered b on a.starter = b.ender and a.station=b.station and a.starter=1
  

Это единственное решение, которое я могу придумать, но опять же, оно медленное, в зависимости от объема имеющихся у вас данных.