🔘 👰🏼 🌿 समय के साथ समूहबद्ध सीरियल पोस्ट 💰 🙍🏿 🙏

शुभ दोपहर, हब्र!

परियोजना में, मुझे निम्नलिखित कार्य का सामना करना पड़ा: तस्वीरों का एक समाचार फ़ीड है, जिसे उपयोगकर्ता केवल एक फोटो पर पोस्ट कर सकते हैं, और उन्हें एक गैलरी के रूप में एक साथ प्रदर्शित करने की आवश्यकता है। दूसरे शब्दों में, सभी चयन पंक्तियों को तार्किक रूप से प्रत्येक लेखक के लिए कई "टाइम विंडो" में संयोजित किया जाना चाहिए और प्रदर्शित करते समय इसका उपयोग करना चाहिए।

यह निम्न पदों को एक-एक करके समूहीकृत करने का प्रयास करता है, लेकिन यह उपयुक्त नहीं है: यदि दो उपयोगकर्ता समानांतर और धीरे-धीरे सैकड़ों फ़ोटो अपलोड करते हैं और अपलोड करते हैं, तो उन्हें एक-एक करके फीड में जोड़ा जाता है, और पोस्ट देखने पर, यह वैकल्पिक रूप से अप्रिय होगा।

MySQL में एक समाधान के लिए

समस्या का बयान

तुरंत एक आरक्षण करें कि चयन के दौरान पदों को समूहीकृत करना गलत है: यह बहुत ही वांछनीय है कि छवियों के समूह स्थिर रहें और कुछ भी कहीं से भी गिर न जाए। इस प्रकार, प्रत्येक पोस्ट को स्पष्ट रूप से गैलरी द्वारा प्रस्तुत "समूह" के कुछ प्रकार से संबंधित होना चाहिए।

समाधान एक रामबाण नहीं है, लेकिन कार्यों का एक चक्र है जहां वास्तव में यह दृष्टिकोण काम में आ सकता है।

पहले, आइए प्रयोगों के लिए एक तालिका बनाएं:

CREATE TABLE `feed`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `tm` INT UNSIGNED NOT NULL COMMENT 'timestamp', `user_id` INT UNSIGNED NOT NULL COMMENT 'author id', `image` VARCHAR(255) NOT NULL COMMENT 'posted image filename', `group` INT UNSIGNED NULL DEFAULT NULL COMMENT 'post group', PRIMARY KEY(`id`), INDEX(`user_id`), INDEX(`tm`,`group`) );

`feed` पदों की एक सूची है। प्रत्येक पोस्ट में tm जोड़ने का समय होता है, लेखक user_id लिए एक लिंक, स्वयं चित्र, और हम एक विशेष group कॉलम भी जोड़ते हैं, जो हमें गैलरी में छवियों को समूह बनाने की अनुमति देता है। नई प्रविष्टि जोड़ते समय, group=NULL ।

विकल्प गलत है

पहले ऐसा लगता है: हम सबसे नए पोस्ट का चयन करते हैं, फिर हम एक घंटे के भीतर एक ही उपयोगकर्ता के पदों का चयन करते हैं, और उन्हें सभी group= id-- असाइन करते हैं। केवल इस मामले में, प्रत्येक पोस्ट, यह पता चला है, केवल अपने समूह का होगा। नहीं, उपयुक्त नहीं :)

समूह

पहले आपको पदों की अस्थायी निकटता के लिए मानदंड निर्धारित करने की आवश्यकता है:

 SET @granularity:=60*60;

इसलिए, एक घंटे के भीतर सभी पोस्ट एक गैलरी में समूहीकृत की जाती हैं।

अगला, हम निम्नलिखित तार्किक कदम बनाते हैं: प्रत्येक पोस्ट को समूह के लिए "नींव" बनने दें:

 SELECT `g`.`id` AS `group` FROM `feed` `g`;

और इस तरह के एक समूह में "आधार" (समय अंतर एक घंटे के भीतर है) से प्रति घंटा त्रिज्या में लाइनें शामिल होंगी:

 SELECT `g`.`id` AS `group`, `f`.* FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` )

इसलिए, अब प्रत्येक पंक्ति में मूल बातें के लिए कई उम्मीदवार हैं। चयन मानदंड: हम "आधार" एक पद के रूप में चयन करते हैं जिसमें प्रति घंटा त्रिज्या में सबसे बड़ी संख्या होती है।
अनावश्यक रूप से गणना के साथ MySQL को तनाव न देने के लिए, त्रिज्या के बजाय हम कसौटी का उपयोग करते हैं `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` : तो व्यापक रचना के साथ आधार का सबसे बड़ा `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` होगा :

 SELECT MAX(`g`.`id`) AS `group`, `f`.* FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` ) GROUP BY `f`.`id`

इसलिए, हमने सभी पंक्तियों को चुना और उनमें से प्रत्येक के लिए एक समूह बनाया।

मैं ध्यान देता हूं कि कोई बारीकियां है। इस तरह के टेप को प्रदर्शित करते हुए, हम अब `group` DESC क्रमबद्ध करेंगे। फिर, यदि उपरोक्त कोड में MAX() फ़ंक्शन का उपयोग किया जाता है, तो टेप को सॉर्ट करने में नवीनतम "ग्रुप" (नवीनतम अपडेट प्राप्त) बहुत ऊपर तक जाएगा।
इस व्यवहार को आसानी से बदला जा सकता है: फिर हमें लगातार समूह मिलते हैं, जैसे कि तत्व एक से दूसरे में नहीं जा सकते हैं: इसके लिए यह MIN() फ़ंक्शन का उपयोग करने के लिए पर्याप्त है: आधार तब हमेशा सबसे पुराना पोस्ट बन जाता है, और समूह को केवल अन्य महत्वपूर्ण फ़ोटो के साथ पूरक किया जा सकता है:

 SELECT MIN(`g`.`id`) AS `group`, `f`.* FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm` AND `g`.`tm`+@granularity ) GROUP BY `f`.`id`

अब हमें इस क्वेरी के परिणामों के आधार पर तालिका को अपडेट करने की आवश्यकता है: `group` कॉलम का मान सेट करें। MySQL उस तालिका को अद्यतन करने की अनुमति नहीं देता है जिससे वह एक UPDATE क्वेरी में पढ़ी जाती है, इसलिए हमें पहले अपने चयन को एक अस्थायी तालिका में स्थानांतरित करना होगा:

 CREATE TEMPORARY TABLE `_feedg` SELECT MAX(`g`.`id`) AS `group`, `f`.`id` FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` ) WHERE `f`.`group` IS NULL OR `f`.`tm` >= (UNIX_TIMESTAMP()-2*@granularity) GROUP BY `f`.`id`;

दिखाई देने वाले WHERE ध्यान दें: इसका उपयोग अनुकूलन के लिए किया जाता है ताकि रीग्रुपिंग केवल नवीनतम रिकॉर्ड के बीच, तालिका के शीर्ष पर किया जाए।

अब, अस्थायी तालिका का उपयोग करके, आप मूल को अपडेट कर सकते हैं:

 UPDATE `feed` `f` CROSS JOIN `_feedg` `g` USING(`id`) SET `f`.`group` = `g`.`group`;

प्रत्येक सम्मिलन के बाद अनुरोध को निष्पादित करने की सलाह दी जाती है, लेकिन अनुकूलन के लिए, आप इसे क्राउन द्वारा कर सकते हैं, आउटपुट एल्गोरिदम पर कुछ काम छोड़ सकते हैं।

UPD: जैसा कि मेल्किज ने सुझाव दिया था - वास्तव में, यदि आप पढ़े गए अनुरोध को एक JOIN के बजाय एक उपकुंजी बना सकते हैं, तो आप अपडेट कर सकते हैं। फिर हम पूरी तरह से CREATE TEMPORARY TABLE हटा देते हैं, और UPDATE क्वेरी इस तरह दिखाई देगी:

 UPDATE `feed` `f` CROSS JOIN ( SELECT MAX(`g`.`id`) AS `group`, `f`.`id` FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`) WHERE `f`.`group` IS NULL OR `f`.`tm` >= (UNIX_TIMESTAMP()-2*@granularity) GROUP BY `f`.`id` ) `g` USING(`id`) SET `f`.`group` = `g`.`group`;

नमूना

अब, ऐसी तालिका से सही ढंग से कैसे चयन करें?

यदि `group` सभी लाइनों के लिए `group` , तो

 SELECT * FROM `feed` ORDER BY `group` DESC, `tm` DESC;

हालांकि, इस घटना में कि उपरोक्त अनुरोध मुकुट पर चलाया जाता है और इसलिए, हमारे पास कुछ लाइनें हैं जिनके लिए group=NULL , कुछ आउटपुट तर्क स्क्रिप्ट रेंडरर को सौंपा जाना चाहिए, और चयन इस तरह किया जाना चाहिए:

 SELECT * FROM `feed` ORDER BY `group` IS NULL, `group` DESC, `tm` DESC;

संदर्भ

मेरा प्रश्न स्टैकओवरफ़्लो पर है: स्टैकओवरफ़्लो: उपयोगकर्ता फीड में सीरियल पोस्ट को समूहीकृत करना । यहां आप प्रशंसा कर सकते हैं कि "टाइम विंडो" का उपयोग करके ओरेकल में यह कैसे किया जाता है।

SQLfiddle, चारों ओर खेलने: SQLfiddle

आशा है कि मैं मददगार रहा हूँ।

समय के साथ समूहबद्ध सीरियल पोस्ट

समस्या का बयान

विकल्प गलत है

समूह

नमूना

संदर्भ

More articles: