Блог

Мне нужен совет по SQL здесь…

У меня есть таблица с объектом (называемым «entityid»), обновленной меткой времени и статусом этого объекта. Теперь я хочу отслеживать, как часто этот объект был установлен пользователем «неактивным». Но это должно быть только макс. 1 раз неактивен в день. Если статус до этого также был неактивным, он не должен учитываться!

Итак, вот небольшой пример, который я подготовил в Excel, чтобы показать, где маркер должен появляться, а где нет:

Есть ли у вас какие-либо советы, как я могу решить это с помощью SQL? (В настоящее время мы работаем с Redshift -> PostgreSQL).

Если я правильно понимаю, вы можете использовать оконные функции. Это возвращает первое «неактивное» значение за каждый день:

 select t.*,
       (content_status = 'inactive' and
        row_number() over (partition by entityid, updated_at::date, content_status) = 1
       ) as needed_marker            
from t;
  

Если я правильно понимаю, вы можете использовать оконные функции. Это возвращает первое «неактивное» значение за каждый день:

 select t.*,
       (content_status = 'inactive' and
        row_number() over (partition by entityid, updated_at::date, content_status order by lastmodifiedtimestamp) = 1
       ) as needed_marker            
from t;
  

Примечание: я не уверен, что updated_at это просто дата. Если это так, то логика больше похожа:

 select t.*,
       (content_status = 'inactive' and
        row_number() over (partition by entityid, updated_at, content_status order by lastmodifiedtimestamp) = 1
       ) as needed_marker            
from t;
  

Редактировать:

Если вы хотите, чтобы при первом изменении статуса с активного на неактивный, затем:

 select t.*,
       (content_status = 'inactive' and
        num_actives = 1 and
        prev_status = 'active'
       ) as needed_marker            
from (select t.*,
             sum(case when status = 'active' then 1 else 0 end) over (partition by entityid, updated_at order by lastmodifiedtimestamp) as num_actives,
             lag(content_status) over (partition by entityid, updated_at lastmodifiedtimestamp) as prev_status
      from t
     ) t;
  

На самом деле, подзапрос не нужен:

 select t.*,
       (content_status = 'inactive' and
        sum(case when status = 'active' then 1 else 0 end) over (partition by entityid, updated_at order by lastmodifiedtimestamp) = 1 and
        lag(content_status) over (partition by entityid, updated_at lastmodifiedtimestamp) = 'active'
       ) as needed_marker 
from t;