अतिरिक्त बाधाओं के साथ लगातार टाइमस्टैम्प में सबसे बड़ा अंतर प्राप्त करें? - एसक्यूएल, ओरेकल

मेरे पास एक टेबल है items जो चलती वस्तुओं का प्रतिनिधित्व करता है। दूसरों के बीच, इस तालिका में पीके है nameटाइमस्टैम्प creation_time जिसमें प्रवेश और स्ट्रिंग के निर्माण का समय शामिल है type जो या तो हो सकता है start, move या end साथ में move प्रविष्टियों के बीच में आ रहा है start तथा end

तालिका का एक हिस्सा इस तरह दिख सकता है:

name creation_time       type
_________________________________
i1   2017-03-01 10:00:01 start
i1   2017-03-01 10:00:02 move
i1   2017-03-01 10:00:08 move
i1   2017-03-01 10:00:10 end
i1   2017-03-01 10:00:31 start
i1   2017-03-01 10:00:33 move
i1   2017-03-01 10:00:37 end

जो मैं प्राप्त करना चाहता हूं वह लगातार टाइमस्टैम्प का सबसे बड़ा अंतर है जहां दूसरा टाइमस्टैम्प का प्रकार नहीं है start (मुझे ए के बीच के समय की परवाह नहीं है end और ए start)।

पर आधारित यह SO सवाल है मैं निम्नलिखित क्वेरी के साथ आया हूं जो अभी तक प्रविष्टियों के प्रकार को ध्यान में रखता नहीं है:

select name, creation, next_creation, (next_creation-creation) difference from (
select name, creation, (
select min(creation) from items i2
where i2.name=i.name
and i2.creation > i.creation
) as next_creation
from items i
)
where (next_creation-creation) is not null
order by difference desc

मेरी समस्या यह है कि मैं नहीं जानता कि कैसे ठीक से किया जाएअब प्रकार के लिए फ़िल्टर करें। मेरी समझ के लिए, मुझे निम्नतम क्वेरी से टाइप प्राप्त करना होगा, क्योंकि मुझे दूसरी प्रविष्टि के प्रकार की परवाह है, और फिर जोड़ें and type<>"start" बाहरीतम क्वेरी के खंड के लिए - लेकिन मुझे subquery से दो मान नहीं मिल सकते हैं, है ना?

संपादित करें: मेरे द्वारा अपेक्षित परिणाम इस तरह दिखेगा:

name creation            next_creation        difference action
i1   2017-03-01 10:00:02 2017-03-01 10:00:08  6s         move
i1   2017-03-01 10:00:33 2017-03-01 10:00:37  4s         end
[...]

जैसा कि आप देखते हैं, पहले के बीच के समय के लिए कोई प्रविष्टि नहीं है end और दूसरा start, वह है जो टाइप के लिए फ़िल्टर करके पूरा करना चाहता है।

उदाहरण के लिए, पहली और बहुत ही अंतिम प्रविष्टि के बीच के समय के लिए कोई प्रविष्टि नहीं है क्योंकि मुझे लगातार प्रविष्टियों के बीच अंतर चाहिए।

उत्तर:

जवाब के लिए 2 № 1

इसके संपादन से पहले प्रश्न का उत्तर देने के लिए:

उपयोग LAG विश्लेषणात्मक समारोह:

SELECT name,
MAX( time_difference ) AS max_time_difference
FROM   (
SELECT name,
type,
creation_time
- LAG( creation_time ) OVER ( PARTITION BY name ORDER BY creation_time )
AS time_difference
FROM   items
)
WHERE type != "start"
GROUP BY name;

अपडेट किया गया

स्टार्ट / एंड आइटम्स के प्रत्येक समूह के लिए इसे प्राप्त करने के लिए (जो प्रतीत होता है कि आउटपुट दिखा रहा है - क्योंकि इसमें कई पंक्तियां हैं):

SELECT name,
creation_time,
next_creation_time,
next_creation_time - creation_time AS difference,
type
FROM   (
SELECT i.*,
ROW_NUMBER() OVER (
PARTITION BY name, start_group
ORDER BY next_creation_time - creation_time DESC, creation_time ASC
) AS rn
FROM   (
SELECT name,
type,
creation_time,
LEAD( creation_time ) OVER ( PARTITION BY name ORDER BY creation_time )
AS next_creation_time,
SUM( CASE type WHEN "start" THEN 1 END )
OVER( PARTITION BY name ORDER BY creation_time )
AS start_group
FROM   items
) i
WHERE type != "end"
)
WHERE rn = 1;

जवाब के लिए 0 № 2

मेरी समझ के अनुसार। यह सबक्वायरी में कॉलम "प्रकार" को शामिल करना होगा जिसे इनलाइन व्यू में फ़िल्टर के रूप में उपयोग किया जा सकता है। आशा करता हूँ की ये काम करेगा।

SELECT name,
creation,
next_creation,
(next_creation-creation) difference
FROM
(SELECT name,
creation,
(SELECT MIN(creation)
FROM items i2
WHERE i2.name   =i.name
AND I2.CREATION > I.CREATION
) AS NEXT_CREATION,
type
FROM items i
)
WHERE (NEXT_CREATION-CREATION) IS NOT NULL
and type                       <> "start"
ORDER BY difference DESC;

जवाब के लिए 0 № 3

यदि आपको प्रत्येक नाम मान के लिए अधिकतम अंतर की आवश्यकता है, तो ऐसा कुछ काम करना चाहिए:

SELECT i1.NAME, MAX(i2.creation_time - i1.creation_time) difference
FROM items i1
INNER JOIN items i2 ON i1.name = i2.name AND i1.creation_time <= i2.creation_time
WHERE "start" NOT IN (i1.type, i2.type)
GROUP BY i1.name;

संबंधित सवाल
सबसे लोकप्रिय