SHARE
TWEET

Untitled

a guest Jun 17th, 2019 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select
  2.     t.*
  3.     , LAG(t.activity,1) over w as activity_0
  4. from (
  5.             -- первая запись
  6.             select
  7.                 csh.object_id as categories_id
  8.                 , STR_TO_DATE('1900-01-01', '%Y-%m-%d') as date_from
  9.                 , IF(csh.status_id IN (0,3,11,15,16,17,20,21,23,24,25,26,27,28,35,36,37,38,39,40,41,42,43), 0, 1) as activity
  10.             from (select object_id
  11.                              , min(categories_status_history_id) as categories_status_history_id
  12.                         from categories_status_history
  13.                         group by object_id) as csh_1
  14.             join categories_status_history as csh on csh_1.object_id = csh.object_id and csh_1.categories_status_history_id = csh.categories_status_history_id
  15.             where csh.object_id = 81856
  16.            
  17.             union all
  18.            
  19.             -- остальные записи
  20.             select
  21.                 csh.object_id as categories_id
  22.                 , date(csh.date_added) as date_from
  23.                 , MAX(IF(csh.status_id IN (0,3,11,15,16,17,20,21,23,24,25,26,27,28,35,36,37,38,39,40,41,42,43), 0, 1) ) AS activity
  24.             from categories_status_history as csh
  25.             where csh.object_id = 81856
  26.             group by csh.object_id
  27.                 , date(csh.date_added)
  28.         ) as t
  29. window w as (partition by t.categories_id order by t.date_from)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top