sql - गतिविधि डेटा के आधार पर आईडी कॉलम बनाएं




oracle oracle11g (2)

मेरे पास एक टेबल EVENTS

USER  EVENT_TS             EVENT_TYPE
abc   2016-01-01 08:00:00  Login
abc   2016-01-01 08:25:00  Stuff
abc   2016-01-01 10:00:00  Stuff
abc   2016-01-01 14:00:00  Login
xyz   2015-12-31 18:00:00  Login
xyz   2016-01-01 08:00:00  Logout

मुझे क्या करना है प्रत्येक उपयोगकर्ता के लिए गतिविधि के प्रत्येक अवधि के लिए session फ़ील्ड का उत्पादन करना है। इसके अतिरिक्त, यदि उपयोगकर्ता p_timeout बराबर या उससे अधिक अवधि के लिए बेकार है (इस मामले में 1 घंटे) तो एक नया सत्र अगले गतिविधि पर शुरू होता है उपयोगकर्ता हमेशा साफ तरीके से लॉग आउट नहीं करते हैं, इसलिए लॉगआउट वहां मौजूद नहीं है ...

टिप्पणियाँ:

लॉगआउट हमेशा सत्र समाप्त कर देता है
लॉगआउट या लॉगिन होने की आवश्यकता नहीं है (क्योंकि सॉफ्टवेयर)
लॉगिन हमेशा एक नया सत्र होता है

आउटपुट की तरह

USER  EVENT_TS             EVENT_TYPE  SESSION
abc   2016-01-01 08:00:00  Login       1
abc   2016-01-01 08:25:00  Stuff       1
abc   2016-01-01 10:00:00  Stuff       2
abc   2016-01-01 14:00:00  Login       3
xyz   2015-12-31 18:00:00  Login       1
xyz   2016-01-01 08:00:00  Logout      1

यह कैसे प्राप्त करने पर कोई विचार?


पूर्णता के लिए (Oracle 12 या उससे ऊपर के उपयोगकर्ताओं के लिए), यहां MATCH_RECOGNIZE का उपयोग कर एक समाधान है:

select usr, event_ts, event_type, sess
from   events
match_recognize(
  partition by usr
  order by event_ts
  measures match_number() as sess
  all rows per match
  pattern (strt follow*)
  define follow as    event_type = 'Logout'
                   or (     event_type != 'Login'
                        and prev(event_type) != 'Logout'
                        and event_ts < prev(event_ts) + 1/24
                      )
)
;

यहाँ मैं एक असामान्य मामले को कवर करता हूं: Logout इवेंट के बाद एक Logout ईवेंट। ऐसे मामलों में, मैं सभी लगातार Logout मानता हूं, चाहे कितने और कितनी दूर समय में हो, उसी सत्र से संबंधित हो। (यदि ऐसे मामलों की गारंटी है कि डेटा में नहीं होने चाहिए, तो बेहतर होगा।)

कृपया मुझे अपने अन्य उत्तर (ओरेकल 11 और उसके बाद के संस्करण के लिए) में एक नोट भी देखें जो usr लिए पहली बार एक Logout होने की संभावना (यदि इनपुट डेटा में भी संभव है) की संभावना के बारे में है।


मुझे लगता है कि यह आपके लिए क्या आवश्यक हो सकता है मैंने इनपुट में "यूजर" को "यूजर" बदल दिया है, और आउटपुट में "सत्र" को "सत्र" में बदल दिया है - मैं ऑब्जेक्ट नामों के लिए आरक्षित ओरेकल शब्द का उपयोग नहीं करता।

नोट : जैसा कि बेनिस्ट नीचे बताया गया है, मेरा समाधान पहले सत्र में 0 की एक सत्र संख्या असाइन करेगा, अगर यह Logout इवेंट (या शीर्ष पर Logout उत्तराधिकार) है। यदि स्थिति में यह स्थिति हो सकती है, और अगर वांछित व्यवहार उस स्थिति में भी 1 पर सत्र शुरू करने के लिए है, तो flag की परिभाषा को tweaked किया जाना चाहिए - उदाहरण के लिए, जब lag(event_ts) over (partition by usr order by event_ts) is null flag = 1 बनाकर lag(event_ts) over (partition by usr order by event_ts) is null ठीक है।

सौभाग्य!

with
     events ( usr, event_ts, event_type ) as (
       select 'abc', to_timestamp('2016-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Login' from dual union all
       select 'abc', to_timestamp('2016-01-01 08:25:00', 'yyyy-mm-dd hh24:mi:ss'), 'Stuff' from dual union all
       select 'abc', to_timestamp('2016-01-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Stuff' from dual union all
       select 'abc', to_timestamp('2016-01-01 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Login' from dual union all
       select 'xyz', to_timestamp('2015-12-31 18:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Login' from dual union all
       select 'xyz', to_timestamp('2016-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Logout' from dual
     ),
     start_of_sess ( usr, event_ts, event_type, flag ) as (
       select usr, event_ts, event_type,
              case when event_type != 'Logout' 
                    and
                        (    event_ts >= lag(event_ts) over (partition by usr 
                                                             order by event_ts) + 1/24 
                          or event_type = 'Login'
                          or lag(event_type) over (partition by usr 
                                                   order by event_ts) = 'Logout'
                        )
                   then 1 end
       from   events
     )
select usr, event_ts, event_type,
       count(flag) over (partition by usr order by event_ts) as sess
from   start_of_sess
; 

आउटपुट (टाइमस्टैम्प मेरे वर्तमान NLS_TIMESTAMP_FORMAT सेटिंग का उपयोग करते हैं):

USR EVENT_TS                          EVENT_TYPE   SESS
--- --------------------------------- ---------- ------
abc 01-JAN-2016 08.00.00.000000000 AM Login           1
abc 01-JAN-2016 08.25.00.000000000 AM Stuff           1
abc 01-JAN-2016 10.00.00.000000000 AM Stuff           2
abc 01-JAN-2016 02.00.00.000000000 PM Login           3
xyz 31-DEC-2015 06.00.00.000000000 PM Login           1
xyz 01-JAN-2016 08.00.00.000000000 AM Logout          1

 6 rows selected     




oracle11g