SQL सर्वर तालिका से n यादृच्छिक पंक्तियों का चयन करें




sql-server random (12)

मुझे इसमें लगभग 50,000 पंक्तियों के साथ एक SQL सर्वर तालिका मिली है। मैं यादृच्छिक रूप से उन पंक्तियों में से 5,000 का चयन करना चाहता हूं। मैंने एक जटिल तरीके से सोचा है, एक "यादृच्छिक संख्या" कॉलम के साथ एक temp तालिका बना रहा है, उसमें मेरी तालिका की प्रतिलिपि बना रहा है, अस्थायी तालिका के माध्यम से लूपिंग और प्रत्येक पंक्ति को RAND() साथ अद्यतन कर रहा है, और फिर उस तालिका से चयन करना जहां यादृच्छिक है संख्या कॉलम <0.1। यदि संभव हो तो एक कथन में, मैं इसे करने का एक आसान तरीका ढूंढ रहा हूं।

यह आलेख NEWID() फ़ंक्शन का उपयोग करने का सुझाव देता है। यह आशाजनक लग रहा है, लेकिन मैं नहीं देख सकता कि मैं पंक्तियों का एक निश्चित प्रतिशत कैसे विश्वसनीय रूप से चुन सकता हूं।

कोई भी इससे पहले कभी करता है? कोई विचार?


MySQL में आप यह कर सकते हैं:

SELECT `PRIMARY_KEY`, rand() FROM table ORDER BY rand() LIMIT 5000;

आपकी जरूरतों के आधार पर, TABLESAMPLE आपको लगभग यादृच्छिक और बेहतर प्रदर्शन के रूप में प्राप्त करेगा। यह एमएस एसक्यूएल सर्वर 2005 और बाद में उपलब्ध है।

TABLESAMPLE यादृच्छिक पंक्तियों के बजाय यादृच्छिक पृष्ठों से डेटा वापस कर देगा और इसलिए डीओएस डेटा को पुनर्प्राप्त नहीं करेगा कि वह वापस नहीं आएगा।

मैंने परीक्षण की एक बहुत बड़ी मेज पर

select top 1 percent * from [tablename] order by newid()

20 मिनट से अधिक समय लिया।

select * from [tablename] tablesample(1 percent)

2 मिनट लग गए।

प्रदर्शन TABLESAMPLE में छोटे नमूने पर भी सुधार होगा जबकि यह नए newid() साथ नहीं होगा।

कृपया ध्यान रखें कि यह newid() विधि के रूप में यादृच्छिक नहीं है लेकिन आपको एक सभ्य नमूना देगा।

एमएसडीएन पेज देखें।


यदि आप (ओपी के विपरीत) को विशिष्ट संख्या में रिकॉर्ड्स की आवश्यकता होती है (जो चेक्सम दृष्टिकोण को मुश्किल बनाता है) और टैबलेटैप्ले की तुलना में अधिक यादृच्छिक नमूना चाहते हैं, और चेक्सम की तुलना में बेहतर गति चाहते हैं, तो आप विलय के साथ कर सकते हैं टैबलेट और न्यूआईडी () विधियों, इस तरह:

DECLARE @sampleCount int = 50
SET STATISTICS TIME ON

SELECT TOP (@sampleCount) * 
FROM [yourtable] TABLESAMPLE(10 PERCENT)
ORDER BY NEWID()

SET STATISTICS TIME OFF

मेरे मामले में यह यादृच्छिकता के बीच सबसे सरल समझौता है (यह वास्तव में नहीं है, मुझे पता है) और गति। सारणी के प्रतिशत (या पंक्तियों) को उचित रूप से भिन्न करें - प्रतिशत जितना अधिक होगा, नमूना अधिक यादृच्छिक होगा, लेकिन गति में एक रैखिक गिरावट की उम्मीद है। (ध्यान दें कि TABLESAMPLE एक चर स्वीकार नहीं करेगा)


यह मेरे लिए काम करता है:

SELECT * FROM table_name
ORDER BY RANDOM()
LIMIT [number]

इसे इस्तेमाल करे:

SELECT TOP 10 Field1, ..., FieldN
FROM Table1
ORDER BY NEWID()

मैं इसे सबक्वायरी में इस्तेमाल कर रहा था और यह मुझे subquery में एक ही पंक्ति वापस कर दिया

 SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

तो मैं जहां में मूल तालिका चर सहित शामिल हल किया

SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              Where Mytable.ID>0
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

ध्यान दें कि कहां स्थित है


यह प्रारंभिक बीज विचार और एक चेकसम का संयोजन है, जो मुझे न्यूडिड () की लागत के बिना ठीक से यादृच्छिक परिणाम देने के लिए देखता है:

SELECT TOP [number] 
FROM table_name
ORDER BY RAND(CHECKSUM(*) * RAND())

नया काम () / आदेश काम करेगा, लेकिन बड़े परिणाम सेट के लिए बहुत महंगा होगा क्योंकि इसे प्रत्येक पंक्ति के लिए एक आईडी उत्पन्न करना है, और फिर उन्हें सॉर्ट करना है।

TABLESAMPLE () एक प्रदर्शन दृष्टिकोण से अच्छा है, लेकिन आप परिणामों का झुकाव प्राप्त करेंगे (एक पृष्ठ पर सभी पंक्तियां वापस कर दी जाएंगी)।

बेहतर प्रदर्शन करने वाले वास्तविक यादृच्छिक नमूने के लिए, पंक्तियों को यादृच्छिक रूप से फ़िल्टर करना सबसे अच्छा तरीका है। मुझे SQL सर्वर पुस्तकें ऑनलाइन आलेख सीमित परिणाम परिणामों में निम्न कोड नमूना मिला TABLESAMPLE का उपयोग करके सेट :

यदि आप वास्तव में व्यक्तिगत पंक्तियों का एक यादृच्छिक नमूना चाहते हैं, तो तालिकाओं का उपयोग करने के बजाय, पंक्तियों को यादृच्छिक रूप से फ़िल्टर करने के लिए अपनी क्वेरी को संशोधित करें। उदाहरण के लिए, निम्न क्वेरी SalesID की पंक्तियों में से लगभग एक प्रतिशत लौटने के लिए NEWID फ़ंक्शन का उपयोग करती है। SalesDrderDetail तालिका:

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float)
              / CAST (0x7fffffff AS int)

SalesOrderID कॉलम को चेक्सम अभिव्यक्ति में शामिल किया गया है ताकि प्रति-पंक्ति आधार पर नमूना प्राप्त करने के लिए NEWID () प्रति पंक्ति एक बार मूल्यांकन करे। अभिव्यक्ति सीएएसटी (चेक्सम (न्यूआईडी (), सेल्स ऑर्डर आईडी) और 0x7fffffff एएस फ्लोट / सीएएसटी (0x7fffffff एएस इंट) 0 और 1 के बीच एक यादृच्छिक फ्लोट मान का मूल्यांकन करता है।

1,000,000 पंक्तियों वाली तालिका के खिलाफ दौड़ते समय, मेरे परिणाम यहां दिए गए हैं:

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

यदि आप टैबलेटमैले का उपयोग कर दूर हो सकते हैं, तो यह आपको सर्वश्रेष्ठ प्रदर्शन देगा। अन्यथा newid () / फ़िल्टर विधि का उपयोग करें। यदि आपके पास एक बड़ा परिणाम सेट है तो नया () / ऑर्डर अंतिम उपाय होना चाहिए।


बस यादृच्छिक संख्या से तालिका को ऑर्डर करें और TOP का उपयोग करके पहली 5,000 पंक्तियां प्राप्त करें।

SELECT TOP 5000 * FROM [Table] ORDER BY newid();

अद्यतन करें

बस कोशिश की और एक नया newid() कॉल पर्याप्त है - सभी कास्ट और सभी गणित की कोई ज़रूरत नहीं है।


इस लिंक में ऑर्डरबी (NEWID ()) और 1, 7 और 13 लाख पंक्तियों वाले तालिकाओं के लिए अन्य विधियों के बीच एक दिलचस्प तुलना है।

अक्सर, जब चर्चा समूहों में यादृच्छिक पंक्तियों का चयन करने के बारे में प्रश्न पूछे जाते हैं, तो NEWID क्वेरी प्रस्तावित होती है; यह सरल है और छोटे टेबल के लिए बहुत अच्छी तरह से काम करता है।

SELECT TOP 10 PERCENT *
  FROM Table1
  ORDER BY NEWID()

हालांकि, जब आप बड़ी टेबल के लिए इसका उपयोग करते हैं तो NEWID क्वेरी में बड़ी कमी होती है। खंड द्वारा ऑर्डर तालिका में सभी पंक्तियों को tempdb डेटाबेस में कॉपी करने का कारण बनता है, जहां उन्हें सॉर्ट किया जाता है। इससे दो समस्याएं आती हैं:

  1. सॉर्टिंग ऑपरेशन आमतौर पर इसके साथ जुड़ी एक उच्च लागत है। छंटनी बहुत सारी डिस्क I / O का उपयोग कर सकती है और लंबे समय तक चल सकती है।
  2. सबसे बुरी स्थिति परिदृश्य में, tempdb अंतरिक्ष से बाहर चला सकता है। सबसे अच्छे मामले परिदृश्य में, tempdb डिस्क स्थान की एक बड़ी मात्रा ले सकता है जिसे बिना किसी मैन्युअल सिकंक कमांड के पुनः प्राप्त किया जाएगा।

आपको जो चाहिए वह यादृच्छिक रूप से पंक्तियों का चयन करने का एक तरीका है जो tempdb का उपयोग नहीं करेगा और तालिका जितनी बड़ी हो जाएगी उतनी धीमी नहीं होगी। यहां एक नया विचार है कि यह कैसे करें:

SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

इस क्वेरी के पीछे मूल विचार यह है कि हम तालिका में प्रत्येक पंक्ति के लिए 0 और 99 के बीच एक यादृच्छिक संख्या उत्पन्न करना चाहते हैं, और फिर उन सभी पंक्तियों का चयन करें जिनकी यादृच्छिक संख्या निर्दिष्ट प्रतिशत के मान से कम है। इस उदाहरण में, हम पंक्तियों में से लगभग 10 प्रतिशत यादृच्छिक रूप से चुने गए हैं; इसलिए, हम उन सभी पंक्तियों को चुनते हैं जिनकी यादृच्छिक संख्या 10 से कम है।

कृपया एमएसडीएन में पूरा लेख पढ़ें।


ऐसा लगता है कि नया () क्लॉज में उपयोग नहीं किया जा सकता है, इसलिए इस समाधान को आंतरिक क्वेरी की आवश्यकता होती है:

SELECT *
FROM (
    SELECT *, ABS(CHECKSUM(NEWID())) AS Rnd
    FROM MyTable
) vw
WHERE Rnd % 100 < 10        --10%

select top 10 percent * from [yourtable] order by newid()

बड़ी तालिकाओं से संबंधित "शुद्ध कचरा" टिप्पणी के जवाब में: आप प्रदर्शन को बेहतर बनाने के लिए ऐसा कर सकते हैं।

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

इसकी लागत मूल्यों का मुख्य स्कैन और अतिरिक्त लागत होगी, जो एक छोटे से प्रतिशत चयन के साथ एक बड़ी तालिका पर उचित होना चाहिए।





random