दिलचस्प पोस्ट
आप Windows Vista पर अपने विज़ुअल स्टूडियो कैश को कैसे साफ़ करते हैं? एंड्रॉइड होमस्क्रीन का विकास करना यदि वे regexp का उपयोग नहीं कर रहे हैं, तो HTML पार्स कैसे काम करता है? Angularjs $ q.all ऐरे # मैप के लिए कॉलबैक के रूप में स्ट्रिंग # ट्रिम का उपयोग नहीं किया जा सकता स्विफ्ट में एक सरणी के तत्वों द्वारा समूह कैसे बनाया जाए रेल संघ के तरीके कैसे काम करते हैं? लूप का उपयोग किए बिना गिनती में सरणी के अनुसार समूह कैसे करें LINQ से SQL जहां क्लॉज वैकल्पिक मानदंड मैं एक सूची <T> को एक डाटासेट में कैसे बदलूं? मैं रिमोट जीआईटी रिपॉजिटरी की केवल एक शाखा कैसे प्राप्त करूं? संस्करण संख्या तुलना कैसे निर्धारित करें कि एक JSON ऑब्जेक्ट संशोधित नहीं किया गया है? IIS / ASP.NET के सभी उपयोगकर्ता खाते क्या हैं और वे अलग-अलग कैसे हैं? क्या XPath में "if-then-else" कथन है?

यादृच्छिक पंक्तियों PostgreSQL चुनने का सर्वोत्तम तरीका

मैं PostgreSQL में पंक्तियों के एक यादृच्छिक चयन करना चाहता हूं, मैंने यह कोशिश की:

select * from table where random() < 0.01; 

लेकिन कुछ अन्य यह सुझाते हैं:

 select * from table order by random() limit 1000; 

मेरे पास 500 मिलियन पंक्तियों के साथ एक बहुत बड़ी तालिका है, मैं इसे तेजी से करना चाहता हूं

कौन सा दृष्टिकोण बेहतर है? क्या अंतर हैं? यादृच्छिक पंक्तियों का चयन करने का सबसे अच्छा तरीका क्या है?

Solutions Collecting From Web of "यादृच्छिक पंक्तियों PostgreSQL चुनने का सर्वोत्तम तरीका"

आपके विनिर्देशों को देखते हुए (टिप्पणियों में अतिरिक्त जानकारी),

  • आपके पास एक संख्यात्मक आईडी स्तंभ (पूर्णांक संख्या) केवल कुछ (या मामूली कुछ) अंतराल के साथ है
  • जाहिर है कोई या कुछ लिखने के संचालन
  • आपका आईडी स्तंभ अनुक्रमित होना चाहिए! एक प्राथमिक कुंजी अच्छी तरह से काम करती है

नीचे दिए गए क्वेरी को बड़ी तालिका के अनुक्रमिक स्कैन की आवश्यकता नहीं है, केवल एक इंडेक्स स्कैन।

सबसे पहले, मुख्य क्वेरी के लिए अनुमान प्राप्त करें:

 SELECT count(*) AS ct -- optional , min(id) AS min_id , max(id) AS max_id , max(id) - min(id) AS id_span FROM big; 

केवल संभवतः महंगा हिस्सा count(*) (विशाल टेबल के लिए) विनिर्देशों से ऊपर दिए गए, आपको इसकी आवश्यकता नहीं है। अनुमान के मुताबिक सिर्फ ठीक नहीं होगा, लगभग कोई भी कीमत पर उपलब्ध नहीं है ( विस्तृत विवरण यहां ):

 SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass; 

जब तक ct id_span तुलना में बहुत कम नहीं है , क्वेरी अन्य तरीकों से मात देगा।

 WITH params AS ( SELECT 1 AS min_id -- minimum id <= current min id , 5100000 AS id_span -- rounded up. (max_id - min_id + buffer) ) SELECT * FROM ( SELECT p.min_id + trunc(random() * p.id_span)::integer AS id FROM params p ,generate_series(1, 1100) g -- 1000 + buffer GROUP BY 1 -- trim duplicates ) r JOIN big USING (id) LIMIT 1000; -- trim surplus 
  • id स्थान में यादृच्छिक संख्या उत्पन्न करें। आपके पास "कुछ अंतराल" हैं, इसलिए पुनः प्राप्त करने के लिए पंक्तियों की संख्या में 10% (आसानी से रिक्त स्थान को कवर करने के लिए पर्याप्त) जोड़ें।

  • प्रत्येक id मौके से कई बार उठाया जा सकता है (हालांकि बड़ी आईडी स्थान के साथ बहुत संभावना नहीं है), इसलिए उत्पन्न संख्याओं का समूह (या DISTINCT उपयोग करें)।

  • बड़ी तालिका में id जुड़ें यह जगह में सूचकांक के साथ बहुत तेजी से होना चाहिए

  • अंत में ट्रिम अधिशेष id है जो दुश्मनों और अंतरालों द्वारा नहीं खाया गया है। हर पंक्ति में चुना जाना पूरी तरह बराबर है

लघु संस्करण

आप इस क्वेरी को आसान बना सकते हैं। उपरोक्त प्रश्न में सीटीई सिर्फ शैक्षिक उद्देश्यों के लिए है:

 SELECT * FROM ( SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id FROM generate_series(1, 1100) g ) r JOIN big USING (id) LIMIT 1000; 

आरसीटीई के साथ परिशोधित करें

खासकर यदि आप अंतराल और अनुमानों के बारे में इतनी निश्चित नहीं हैं

 WITH RECURSIVE random_pick AS ( SELECT * FROM ( SELECT 1 + trunc(random() * 5100000)::int AS id FROM generate_series(1, 1030) -- 1000 + few percent - adapt to your needs LIMIT 1030 -- hint for query planner ) r JOIN big b USING (id) -- eliminate miss UNION -- eliminate dupe SELECT b.* FROM ( SELECT 1 + trunc(random() * 5100000)::int AS id FROM random_pick r -- plus 3 percent - adapt to your needs LIMIT 999 -- less than 1000, hint for query planner ) r JOIN big b USING (id) -- eliminate miss ) SELECT * FROM random_pick LIMIT 1000; -- actual limit 

हम आधार क्वेरी में एक छोटे अधिशेष के साथ काम कर सकते हैं यदि बहुत अधिक अंतराल हैं तो हमें पहले चलने की पर्याप्त पंक्तियां नहीं मिल रही हैं, आरसीटीई पुनरावर्ती कार्यकाल के साथ फिर से जारी है। हमें अब भी आईडी स्पेस में अपेक्षाकृत कम अंतराल की आवश्यकता है या सीमा समाप्त होने से पहले रिकर्सन ड्राई हो सकता है – या हमें बड़े बफर के साथ शुरू करना होगा जो प्रदर्शन को अनुकूलित करने के उद्देश्य को खारिज करते हैं।

यूनिट द्वारा आरसीटीई में डुप्लिकेट हटा दिए जाते हैं

हमारे पास पर्याप्त पंक्तियां होने के कारण बाह्य LIMIT सीटीई बंद हो जाती है

यह क्वेरी ध्यानपूर्वक उपलब्ध सूचकांक का उपयोग करने के लिए तैयार है, वास्तव में यादृच्छिक पंक्तियां उत्पन्न करती है और जब तक हम सीमा को पूरा नहीं करते हैं तब तक नहीं रोकते (जब तक कि रिकर्सन सूख नहीं चलता)। अगर आप इसे फिर से लिखना चाहते हैं तो यहां बहुत सारे नुकसान हैं।

फ़ंक्शन में लपेटें

विभिन्न मापदंडों के साथ दोहराया उपयोग के लिए:

 CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000, _gaps real = 1.03) RETURNS SETOF big AS $func$ DECLARE _surplus int := _limit * _gaps; _estimate int := ( -- get current estimate from system SELECT c.reltuples * _gaps FROM pg_class c WHERE c.oid = 'big'::regclass); BEGIN RETURN QUERY WITH RECURSIVE random_pick AS ( SELECT * FROM ( SELECT 1 + trunc(random() * _estimate)::int FROM generate_series(1, _surplus) g LIMIT _surplus -- hint for query planner ) r (id) JOIN big USING (id) -- eliminate misses UNION -- eliminate dupes SELECT * FROM ( SELECT 1 + trunc(random() * _estimate)::int FROM random_pick -- just to make it recursive LIMIT _limit -- hint for query planner ) r (id) JOIN big USING (id) -- eliminate misses ) SELECT * FROM random_pick LIMIT _limit; END $func$ LANGUAGE plpgsql VOLATILE ROWS 1000; 

कॉल करें:

 SELECT * FROM f_random_sample(); SELECT * FROM f_random_sample(500, 1.05); 

आप किसी भी सारणी के लिए काम करने के लिए यह जेनेरिक भी बना सकते हैं: पीके कॉलम और तालिका को बहुरूपिक प्रकार के रूप में रखें और उपयोग करें का उपयोग करें … लेकिन यह इस प्रश्न के दायरे से परे है। देख:

  • विभिन्न SELECT क्वेरीज़ के आउटपुट को वापस करने के लिए पीएल / पीजीएसयूएल फ़ंक्शन रिफैक्टर करें

संभावित विकल्प

अगर आपकी आवश्यकता दोहराई गई कॉल के लिए समान सेट की अनुमति देती है (और हम दोहराए जाने वाले कॉल के बारे में बात कर रहे हैं) तो मैं एक भौतिक दृश्य पर विचार करेगा। एक बार उपरोक्त क्वेरी निष्पादित करें और एक तालिका में परिणाम लिखें। उपयोगकर्ताओं को बिजली की गति पर एक अर्ध यादृच्छिक चयन मिलता है। अंतराल या अपने चयन की घटनाओं पर अपने यादृच्छिक चयन को ताज़ा करें

पोस्टग्रेस 9.5 TABLESAMPLE SYSTEM (n) परिचय TABLESAMPLE SYSTEM (n)

यह बहुत तेज है , लेकिन परिणाम बिल्कुल यादृच्छिक नहीं है । नियम पुस्तिका:

SYSTEM पद्धति BERNOULLI विधि से काफी तेज है, जब छोटे नमूना प्रतिशत निर्दिष्ट किए जाते हैं, लेकिन यह क्लस्टरिंग प्रभावों के परिणामस्वरूप तालिका का एक कम-यादृच्छिक नमूना वापस कर सकता है।

और लौट की गई पंक्तियों की संख्या गहराई से भिन्न हो सकती है हमारे उदाहरण के लिए, लगभग 1000 पंक्तियों को प्राप्त करने के लिए, कोशिश करें:

 SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0); 

जहां n एक प्रतिशत है नियम पुस्तिका:

BERNOULLI और SYSTEM नमूना पद्धति प्रत्येक एक एकल तर्क स्वीकार करते हैं जो नमूना के लिए अंश का अंश है, 0 और 100 के बीच प्रतिशत के रूप में व्यक्त किया गया है। यह तर्क किसी भी real मूल्यवान अभिव्यक्ति हो सकता है

बोल्ड जोर खान

सम्बंधित:

  • PostgreSQL में किसी तालिका की पंक्ति संख्या को खोजने के लिए तेज़ तरीका

या अतिरिक्त मॉड्यूल tsm_system_rows को ठीक से अनुरोधित पंक्तियों की संख्या प्राप्त करने के लिए स्थापित करें (यदि पर्याप्त है) और अधिक सुविधाजनक सिंटैक्स के लिए अनुमति दें:

 SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000); 

विवरण के लिए इवान का जवाब देखें

लेकिन यह अभी भी बिल्कुल यादृच्छिक नहीं है

आप दोनों का उपयोग करके निष्पादन योजना की जांच और तुलना कर सकते हैं

 EXPLAIN select * from table where random() < 0.01; EXPLAIN select * from table order by random() limit 1000; 

एक बड़ी तालिका 1 पर एक त्वरित परीक्षण दिखाता है, कि पहले ORDER BY पूर्ण तालिका में क्रमबद्ध होता है और फिर पहले 1000 आइटम उठाता है। बड़ी टेबल को छांटने के लिए न केवल उस तालिका को पढ़ता है बल्कि अस्थायी फ़ाइलों को पढ़ने और लिखना भी शामिल है। where random() < 0.1 केवल एक बार पूर्ण तालिका को स्कैन करता है

बड़ी तालिकाओं के लिए यह संभवतः आप नहीं चाहते हैं क्योंकि एक भी पूरी तालिका स्कैन लंबे समय तक ले सकती है।

एक तीसरा प्रस्ताव होगा

 select * from table where random() < 0.01 limit 1000; 

यह एक तालिका स्कैन रोकता है जैसे ही 1000 पंक्तियां मिलती हैं और इसलिए जल्द ही रिटर्न मिलता है। बेशक यह बगैर यादृच्छिकता को थोड़ा नीचे देता है, लेकिन शायद यह आपके मामले में काफी अच्छा है।

संपादित करें: इस विचार के अलावा, आप इस के लिए पहले से ही पूछे जाने वाले प्रश्नों की जांच कर सकते हैं क्वेरी का प्रयोग [postgresql] random बहुत कुछ हिट देता है

  • पोस्टग्रेज़ में त्वरित यादृच्छिक पंक्ति चयन
  • पोस्टग्रे एसक्यूएल तालिका से यादृच्छिक डेटा पंक्तियां कैसे प्राप्त करें?
  • पोस्टग्रेज़: तालिका से यादृच्छिक प्रविष्टियां प्राप्त करें – बहुत धीमी गति से

और डेपेज़ के लिंक वाले कई अन्य तरीकों की रूपरेखा:


1 "बड़े" के रूप में "पूर्ण तालिका मेमोरी में फिट नहीं होगी"

postgresql आदेश यादृच्छिक () द्वारा, यादृच्छिक क्रम में पंक्तियां चुनें:

 select your_columns from your_table ORDER BY random() 

पोस्टग्रेस्क्ल क्रम को यादृच्छिक () द्वारा एक विशिष्ट के साथ:

 select * from (select distinct your_columns from your_table) table_alias ORDER BY random() 

यादृच्छिक सीमा से पोस्टग्रेस्केल क्रम एक पंक्ति:

 select your_columns from your_table ORDER BY random() limit 1 

ऑर्डर बाय वाला एक धीमे वाला होगा

select * from table where random() < 0.01; रिकार्ड द्वारा रिकॉर्ड चला जाता है, और बेतरतीब ढंग से फ़िल्टर करने का निर्णय करता है या नहीं यह O(N) होने जा रहा है क्योंकि इसे केवल प्रत्येक रिकॉर्ड को एक बार जांचना होगा।

select * from table order by random() limit 1000; पूरी तालिका को सॉर्ट करने जा रहा है, तो पहले 1000 उठाओ। पर्दे के पीछे किसी भी जादू जादू के अलावा, O(N * log N) द्वारा ऑर्डर किया जाता है।

random() < 0.01 एक के नकारात्मक पक्ष यह है कि आपको आउटपुट रिकॉर्ड्स की एक चर संख्या मिलेगी।


ध्यान दें, यादृच्छिक रूप से सॉर्टिंग से डेटा के सेट को फेरबदल करने का एक बेहतर तरीका है: फिशर-येट्स शफल , जो O(N) में चलता है। एसक्यूएल में फेरबदल को लागू करना काफी चुनौती है, हालांकि

PostgreSQL 9.5 के साथ शुरू, एक नया सिंटैक्स एक तालिका से यादृच्छिक तत्वों को प्राप्त करने के लिए समर्पित है:

 SELECT * FROM mytable TABLESAMPLE SYSTEM (5); 

यह उदाहरण आपको mytable से 5% तत्व देगा।

इस ब्लॉग पोस्ट पर अधिक स्पष्टीकरण देखें: http://www.postgresql.org/docs/current/static/sql-select.html

यदि आप केवल एक पंक्ति चाहते हैं, तो आप count से प्राप्त एक गणना offset उपयोग कर सकते हैं।

 select * from table_name limit 1 offset floor(random() * (select count(*) from table_name)); 

यह एक निर्णय है जो मेरे लिए काम करता है मुझे लगता है कि यह समझना और निष्पादित करना बहुत सरल है।

 SELECT field_1, field_2, field_2, random() as ordering FROM big_table WHERE some_conditions ORDER BY ordering LIMIT 1000; 

आर्मविन ब्रैंडस्टेटर द्वारा उल्लिखित भौतिक दृश्य "संभावित विकल्प" का एक भिन्नता संभव है।

उदाहरण के लिए कहें, कि आप यादृच्छिक मूल्यों में डुप्लिकेट नहीं चाहते हैं जो लौट आए हैं इसलिए आपको प्राथमिक तालिका में बुलियन मूल्य सेट करना होगा जिसमें आपके (गैर-यादृच्छिक) मानों का सेट होगा।

मान लें कि यह इनपुट तालिका है:

 id_values id | used ----+-------- 1 | FALSE 2 | FALSE 3 | FALSE 4 | FALSE 5 | FALSE ... 

आवश्यकता के अनुसार ID_VALUES तालिका को ID_VALUES फिर, जैसा कि एर्विन द्वारा वर्णित है, एक भौतिक दृश्य बनाते हैं जो एक बार ID_VALUES तालिका को यादृच्छिक बना देता है:

 CREATE MATERIALIZED VIEW id_values_randomized AS SELECT id FROM id_values ORDER BY random(); 

ध्यान दें कि भौतिक दृश्य में उपयोग किए गए स्तंभ शामिल नहीं हैं, क्योंकि यह जल्दी-ठीक-ठीक हो जाएगा न ही दृश्य को अन्य कॉलमों को शामिल करने की आवश्यकता है जो id_values तालिका में हो सकते हैं।

प्राप्त करने के लिए (और "उपभोग") क्रम में, id_values ​​पर UPDATE-RETURNING का उपयोग करें, id_values से id_values_randomized चयन करके, और केवल प्रासंगिक संभावनाओं को प्राप्त करने के लिए वांछित मानदंडों को लागू करना। उदाहरण के लिए:

 UPDATE id_values SET used = TRUE WHERE id_values.id IN (SELECT i.id FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id WHERE (NOT i.used) LIMIT 5) RETURNING id; 

आवश्यकतानुसार LIMIT को बदलें – यदि आपको केवल एक समय में एक यादृच्छिक मूल्य की आवश्यकता है, तो LIMIT को 1 बदलें

id_values पर उचित अनुक्रमित के साथ, मुझे विश्वास है कि अपडेट-रिटर्न को बहुत कम लोड के साथ शीघ्रता से कार्यान्वित करना चाहिए। यह यादृच्छिक मूल्यों को एक डेटाबेस राउंड-ट्रिप के साथ देता है "पात्र" पंक्तियों के मानदंड जितना जरूरी हो उतना जटिल हो सकता है। नई पंक्तियों को किसी भी समय id_values तालिका में जोड़ा जा सकता है, और जैसे ही भौतिक दृश्य को ताज़ा किया जाता है (जो संभवत: किसी ऑफ-पीक समय पर चलाया जा सकता है) के रूप में वे आवेदन के लिए सुलभ हो जाएंगे भौतिक दृश्य के निर्माण और रीफ्रेश धीमा हो जाएगा, लेकिन id_values टेबल में नए आईडी जोड़े जाने पर केवल इसे निष्पादित करने की id_values है।

 select * from table order by random() limit 1000; 

यदि आपको पता है कि आप कितनी पंक्तियां चाहते हैं, तो tsm_system_rows

tsm_system_rows

मॉड्यूल तालिका नमूनाकरण पद्धति SYSTEM_ROWS प्रदान करता है, जिसे किसी SELECT कमांड के TABLESAMPLE खंड में उपयोग किया जा सकता है।

यह तालिका नमूना पद्धति एक एकल पूर्णांक तर्क को स्वीकार करती है जो पढ़ने के लिए अधिकतम पंक्तियों की संख्या है। परिणामस्वरूप नमूना हमेशा उस पंक्ति को ठीक से रखेगा, जब तक तालिका में पर्याप्त पंक्तियां न हों, तब तक उस स्थिति में पूरी तालिका का चयन किया जाता है। अंतर्निहित सिस्टम नमूना पद्धति की तरह, SYSTEM_ROWS ब्लॉक-स्तरीय नमूना करता है, ताकि नमूना पूरी तरह से यादृच्छिक न हो, लेकिन क्लस्टरिंग प्रभाव के अधीन हो, खासकर यदि केवल कुछ पंक्तियों का अनुरोध किया गया हो।

पहले एक्सटेंशन को स्थापित करें

 CREATE EXTENSION tsm_system_rows; 

फिर आपकी क्वेरी,

 SELECT * FROM table TABLESAMPLE SYSTEM_ROWS(1000); 

सोचा कि एसक्यूएल मोड में पकड़े जाने के बाद, मुझे एहसास हुआ कि, मेरे उपयोग के मामले में, निम्नलिखित विधि को काम करना चाहिए।

मेरे उद्देश्य के लिए, मैं "पिछले महीने से" एक विशिष्ट संख्या (लगभग 10-20) यादृच्छिक तत्वों का चयन करना चाहता हूं (संभवत: 300-400 तत्वों का उपसंच – एक संख्या जो बढ़ी हुई सर्वर गतिविधि के साथ नहीं बढ़ेगी)। मिकाल का समाधान लगभग बिल के बिलकुल ठीक है लेकिन ऐसा प्रतीत होता है कि आप WHERE क्लॉज के बाद TABLESAMPLE उपयोग नहीं कर सकते।

यहां मैंने जिस समाधान पर पहुंचे:

सबसे पहले, मैंने एक सरल क्वेरी चला दी:

SELECT id FROM table WHERE "timestamp" > now()::Date - 30

एक बार परिणामों को मेरे कार्यक्रम में वापस कर दिए जाने के बाद, मैंने आईडी के एक यादृच्छिक नमूने का चयन किया तो मैं बस भाग गया:

SELECT * FROM table WHERE id IN (1,2,3) (जहां (1,2,3) मेरा बेतरतीब ढंग से चयनित नमूना है)।

मुझे पता है कि यह कड़ाई से PostgreSQL समाधान नहीं है, लेकिन यह अच्छा और सरल है और, यदि आप स्केलिंग की कमी को ध्यान में रखते हैं, तो ठीक काम करना चाहिए। उम्मीद है कि यह किसी ऐसी स्थिति में किसी के लिए सही फिट होगा।

टाइप serial साथ r नामक एक कॉलम जोड़ें। सूचकांक r

मान लें कि हमारे पास 200,000 पंक्तियां हैं, हम एक यादृच्छिक संख्या n उत्पन्न करने जा रहे हैं, जहां 0 < n <= 200, 000

r > n साथ पंक्तियों का चयन करें, उन्हें ASC सॉर्ट करें और सबसे छोटा एक चुनें।

कोड:

 select * from YOUR_TABLE where r > ( select ( select reltuples::bigint AS estimate from pg_class where oid = 'public.YOUR_TABLE'::regclass) * random() ) order by r asc limit(1); 

कोड स्वयं-व्याख्यात्मक है बीच में सबक्वेरी का उपयोग जल्दी से https://stackoverflow.com/a/7945274/1271094 से तालिका पंक्ति गणनाओं का अनुमान लगाने के लिए किया जाता है

आवेदन स्तर में आपको फिर से बयान निष्पादित करने की आवश्यकता है यदि n > पंक्तियों की संख्या या कई पंक्तियों को चुनना होगा।