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




oracle oracle11g (4)

मेरे पास एक टेबल 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

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


Answers

पूर्णता के लिए (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 होने की संभावना (यदि इनपुट डेटा में भी संभव है) की संभावना के बारे में है।


मुझे लगता है कि यह चाल करेगा:

WITH EVENTS AS (SELECT 'abc' usr, to_date('2016-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'login' event_type FROM dual UNION ALL
                SELECT 'abc' usr, to_date('2016-01-01 08:25:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'Stuff' event_type FROM dual UNION ALL
                SELECT 'abc' usr, to_date('2016-01-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'Stuff' event_type FROM dual UNION ALL
                SELECT 'abc' usr, to_date('2016-01-01 14:00:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'login' event_type FROM dual UNION ALL
                SELECT 'xyz' usr, to_date('2015-12-31 18:00:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'login' event_type FROM dual UNION ALL
                SELECT 'xyz' usr, to_date('2016-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'Logout' event_type FROM dual UNION ALL
                SELECT 'def' usr, to_date('2016-01-01 08:00:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'Logout' event_type FROM dual UNION ALL
                SELECT 'def' usr, to_date('2016-01-01 08:15:00', 'yyyy-mm-dd hh24:mi:ss') event_ts, 'Logout' event_type FROM dual)
SELECT usr,
       event_ts,
       event_type,
       SUM(counter) OVER (PARTITION BY usr ORDER BY event_ts) session_id
FROM   (SELECT usr,
               event_ts,
               event_type,
               CASE WHEN LAG(event_type, 1, 'Logout') OVER (PARTITION BY usr ORDER BY event_ts) = 'Logout' THEN 1
                    WHEN event_type = 'Logout' THEN 0
                    WHEN event_ts - LAG(event_ts) OVER (PARTITION BY usr ORDER BY event_ts) > 1/24 THEN 1
                    WHEN event_type = 'login' THEN 1
                    ELSE 0
               END counter
        FROM   EVENTS);

USR EVENT_TS            EVENT_TYPE SESSION_ID
--- ------------------- ---------- ----------
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
def 2016-01-01 08:00:00 Logout              1
def 2016-01-01 08:15:00 Logout              2
xyz 2015-12-31 18:00:00 login               1
xyz 2016-01-01 08:00:00 Logout              1

यह समाधान तर्क-संक्षिप्त सर्किटिंग पर निर्भर करता है जो कि CASE अभिव्यक्ति में होता है और तथ्य यह है कि event_type शून्य नहीं है। यह भी मानता है कि एक पंक्ति में एकाधिक लॉगआउट को अलग सत्र के रूप में गिना जाता है:

  1. अगर पिछली पंक्ति एक लॉगआउट पंक्ति थी (और अगर कोई पिछली पंक्ति नहीं है - यानी सेट में पहली पंक्ति के लिए - यह मानते हैं कि कोई लॉगआउट पंक्ति मौजूद थी), तो हम काउंटर को एक से बढ़ाना चाहते हैं (लॉगआउट सत्र समाप्त कर देते हैं, इसलिए लॉगआउट के बाद हमारे पास हमेशा एक नया सत्र होता है।)
  2. यदि मौजूदा पंक्ति एक लॉगआउट है, तो यह मौजूदा सत्र समाप्त कर देता है इसलिए, काउंटर में वृद्धि नहीं की जानी चाहिए।
  3. यदि मौजूदा पंक्ति का समय पिछले पंक्ति से एक घंटे से अधिक है, तो काउंटर को एक से बढ़ाएं
  4. यदि मौजूदा पंक्ति एक लॉगिन पंक्ति है, तो यह एक नया सत्र है, इसलिए काउंटर को एक से बढ़ाएं
  5. किसी अन्य मामले के लिए, हम काउंटर में वृद्धि नहीं करते हैं।

एक बार जब हमने ऐसा किया है, काउंटर पर चलने वाला कुल करने की बात सिर्फ एक बात है


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

नोट : जैसा कि बेनिस्ट नीचे बताया गया है, मेरा समाधान पहले सत्र में 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     

डबल कोट्स का उपयोग करने का प्रयास करें यदि यह एक चर चर है:

DEFINE stupidvar = "'stupidvarcontent'";

या

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata  
FROM stupidtable  
WHERE stupidcolumn = '&stupidvar'

upd:

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 25 17:13:26 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn od/[email protected]
Connected.
SQL> define var = "'FL-208'";
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = 'FL-208'

CODE
---------------
FL-208

SQL> define var = 'FL-208';
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = FL-208
select code from product where code = FL-208
                                      *
ERROR at line 1:
ORA-06553: PLS-221: 'FL' is not a procedure or is undefined




sql oracle oracle11g