एसक्यूएल, संख्याओं की सहायक तालिका

कुछ प्रकार के एसक्यूएल प्रश्नों के लिए, संख्याओं की एक सहायक तालिका बहुत उपयोगी हो सकती है। इसे एक पंक्ति के रूप में बनाया जा सकता है जैसा कि आपको किसी विशेष कार्य के लिए या उपयोगकर्ता परिभाषित फ़ंक्शन के रूप में आवश्यक है जो प्रत्येक क्वेरी में आवश्यक पंक्तियों की संख्या देता है।

इस तरह के एक समारोह को बनाने का सबसे अच्छा तरीका क्या है?

0
उदाहरण के लिए ऐसी तालिका भरने के लिए।
जोड़ा लेखक vzczc, स्रोत
क्या आप समझा सकते हैं कि संख्याओं से पहले भरने वाली तालिका का उपयोग करने के बजाय आप ऐसा क्यों करेंगे?
जोड़ा लेखक jammus, स्रोत
सभी डीबीए और/या तृतीय पक्ष ऐप्स स्थायी स्थायी तालिका को जोड़ने की अनुमति नहीं देंगे।
जोड़ा लेखक JeffO, स्रोत

7 उत्तर

संख्या तालिका उत्पन्न करने के लिए SQL Server 2016 + का उपयोग करके आप OPENJSON का उपयोग कर सकते हैं:

-- range from 0 to @max - 1
DECLARE @max INT = 40000;

SELECT rn = CAST([key] AS INT) 
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']'));

LiveDemo


Idea taken from संख्याओं की श्रृंखला उत्पन्न करने के लिए हम OPENJSON का उपयोग कैसे कर सकते हैं?

0
जोड़ा
अच्छा लगा। मुझे लगता है कि अगर कोई SQL सर्वर के XQuery में position() पूरी तरह से समर्थित था, तो इस तरह एक्सएमएल का उपयोग इसी तरह किया जा सकता था।
जोड़ा लेखक Andriy M, स्रोत

यह दृश्य सुपर तेज़ है और इसमें सभी सकारात्मक int मान शामिल हैं।

CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
    WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
    , Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
    , Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
    , Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
    , Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
    , Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
    SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
    FROM Int32
GO
0
जोड़ा
दृश्य में SCHEMABINDING जोड़ने के लिए कोई विशेष कारण?
जोड़ा लेखक ca9163d9, स्रोत
'SCHEMABINDING के साथ' जोड़ना प्रश्नों को तेज़ी से बना सकता है। यह अनुकूलक को यह जानने में मदद करता है कि कोई डेटा एक्सेस नहीं किया जाता है। ( blogs.msdn.com/b/sqlprogrammability/ देखें संग्रह/2006/05/12/& hellip; )
जोड़ा लेखक Anthony Faull, स्रोत
0 अक्सर उपयोगी होता है। और मैं शायद अंतिम कॉलम को int में परिवर्तित कर दूंगा। आपको यह भी पता होना चाहिए कि मूल रूप से विधि स्वीकृत उत्तर ( 0 के बिना या int में रूपांतरण) के नाम से Itzik के क्रॉस जॉइन सीटीई विधि के नाम से शामिल है
जोड़ा लेखक Andriy M, स्रोत
मुझे आश्चर्य है कि अगर @ एंथनीफूल कुछ मापों के साथ इसे वापस कर सकता है।
जोड़ा लेखक Rbjz, स्रोत

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

0
जोड़ा
आईओ लगभग हमेशा सीपीयू से सस्ता होगा, खासकर जब यह तालिका छोटी होगी और शायद पहले से ही बुडरफ़ूल में होगी।
जोड़ा लेखक mrdenny, स्रोत
मुझे लगता है कि यह आपकी स्थिति पर निर्भर करता है। दो बेहतरीन प्रदर्शन विकल्पों के बीच, आप आईओ और सीपीयू लागतों के बीच व्यापार कर सकते हैं, जो आपके लिए अधिक महंगा है।
जोड़ा लेखक Rbjz, स्रोत
@mrdenny I/O हमेशा रास्ता CPU से अधिक महंगा और धीमा है। हाल के वर्षों में एसएसडी ने कुछ हद तक बदल दिया है, लेकिन ज्यादातर उत्पादन आर्किटेक्चर में उन एसएसडी के पास उनके और सीपीयू के बीच एक नेटवर्क लिंक है। एकमात्र डेटाबेस जो मैं देखता हूं कि वास्तव में सीपीयू बाध्य हैं अनचाहे ओआरएम-केवल ऐप्स या भारी मशीन सीखना चल रहा है।
जोड़ा लेखक rmalayter, स्रोत

संपादित करें: नीचे कॉनराड की टिप्पणी देखें।

जेफ मॉडेन का जवाब बहुत अच्छा है ... लेकिन मुझे पोस्टग्रेस पर लगता है कि जब तक आप E32 पंक्ति को हटा नहीं देते हैं तब Itzik विधि विफल हो जाती है।

Postgres (40ms बनाम 100ms) पर थोड़ा तेज़ एक और तरीका है जिसे मैंने यहां पोस्टग्रेज़ के लिए अनुकूलित:

WITH 
    E00 (N) AS ( 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
    E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
    E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
    E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b 
        LIMIT 11000  -- end record  11,000 good for 30 yrs dates
    ), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
    Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)

SELECT N
FROM Tally

चूंकि मैं SQL सर्वर से पोस्टग्रेज़ दुनिया में जा रहा हूं, हो सकता है कि उस प्लेटफ़ॉर्म पर टैली टेबल करने का बेहतर तरीका हो ... INTEGER ()? अनुक्रम ()?

0
जोड़ा
हो सकता है कि [पोस्टग्रेस] पर टैली टेबल करने का बेहतर तरीका हो गया हो हाँ आपने किया gener_series
जोड़ा लेखक Conrad Frix, स्रोत
धन्यवाद कॉनराड ... रूकी त्रुटि।
जोड़ा लेखक Ruskin, स्रोत

हे ... माफ करना, मैं एक पुरानी पोस्ट का जवाब दे रहा हूं। और, हाँ, मुझे जवाब देना पड़ा क्योंकि इस धागे पर सबसे लोकप्रिय उत्तर (उस समय, 14 अलग-अलग तरीकों के लिंक के साथ रिकर्सिव सीटीई का जवाब) है, ummm ... प्रदर्शन सबसे अच्छा चुनौतीपूर्ण है।

सबसे पहले, 14 अलग-अलग समाधानों वाला आलेख फ्लाई पर नंबर/टैली टेबल बनाने के विभिन्न तरीकों को देखने के लिए ठीक है, लेकिन जैसा कि लेख में और उद्धृत धागे में बताया गया है, वहां बहुत महत्वपूर्ण है बोली ...

"दक्षता के संबंध में सुझाव और   प्रदर्शन अक्सर व्यक्तिपरक होते हैं।   चाहे कोई प्रश्न कैसे हो रहा हो   इस्तेमाल किया, भौतिक कार्यान्वयन   एक क्वेरी की दक्षता निर्धारित करता है।   इसलिए, पर भरोसा करने के बजाय   पक्षपातपूर्ण दिशानिर्देश, यह जरूरी है   कि आप क्वेरी का परीक्षण करते हैं और निर्धारित करते हैं   कौन सा बेहतर प्रदर्शन करता है। "

विडंबना यह है कि इस आलेख में कई व्यक्तिपरक बयान और "पक्षपातपूर्ण दिशानिर्देश" शामिल हैं जैसे "एक पुनरावर्ती सीटीई एक सूची सूची बना सकता है सुंदर कुशलतापूर्वक " और "यह < इटज़िक बेन-जनरल द्वारा पोस्ट न्यूज़ ग्रुप पोस्टिंग से WHILE लूप का उपयोग करने के लिए मजबूत> एक कुशल विधि (जो मुझे यकीन है कि वह केवल तुलना उद्देश्यों के लिए पोस्ट किया गया है)। चलो लोग ... बस इट्ज़िक के अच्छे नाम का जिक्र करने से वास्तव में उस भयानक विधि का उपयोग करके कुछ खराब स्लॉब का कारण बन सकता है। लेखक को अभ्यास करना चाहिए कि वह किस तरह से उपदेश देता है और इस तरह के हास्यास्पद रूप से गलत बयान देने से पहले थोड़ा प्रदर्शन परीक्षण करना चाहिए, विशेष रूप से किसी भी स्केलेबिलिटी के सामने।

वास्तव में कुछ परीक्षण करने से पहले किसी भी कोड को क्या करता है या कोई "पसंद" के बारे में कोई व्यक्तिपरक दावे करने से पहले, यहां कुछ कोड है जिसके साथ आप अपना परीक्षण कर सकते हैं। एसपीआईडी ​​के लिए सेटअप प्रोफाइलर जो आप परीक्षण चला रहे हैं और इसे अपने लिए जांचें ... बस अपने "पसंदीदा" नंबर के लिए 1000000 नंबर की "खोज 'नहीं' बदलें" और देखें ...

--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
   WITH Tally (N) AS 
        ( 
         SELECT 1 UNION ALL 
         SELECT 1 + N FROM Tally WHERE N < 1000000 
        ) 
 SELECT N 
   INTO #Tally1 
   FROM Tally 
 OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
 CREATE TABLE #Tally2 (N INT);
    SET NOCOUNT ON;
DECLARE @Index INT;
    SET @Index = 1;
  WHILE @Index <= 1000000 
  BEGIN 
         INSERT #Tally2 (N) 
         VALUES (@Index);
            SET @Index = @Index + 1;
    END;
GO
--===== Traditional CROSS JOIN table method
 SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   INTO #Tally3
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT N
   INTO #Tally4
   FROM cteTally
  WHERE N <= 1000000;
GO
--===== Housekeeping
   DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO

जबकि हम इसमें हैं, यहां 100, 1000, 10000, 100000, और 1000000 के मानों के लिए SQL प्रोफाइलर से प्राप्त संख्याएं हैं ...

SPID TextData                                 Dur(ms) CPU   Reads   Writes
---- ---------------------------------------- ------- ----- ------- ------
  51 --===== Test for 100 rows ==============       8     0       0      0
  51 --===== Traditional RECURSIVE CTE method      16     0     868      0
  51 --===== Traditional WHILE LOOP method CR      73    16     175      2
  51 --===== Traditional CROSS JOIN table met      11     0      80      0
  51 --===== Itzik's CROSS JOINED CTE method        6     0      63      0
  51 --===== Housekeeping   DROP TABLE #Tally      35    31     401      0

  51 --===== Test for 1000 rows =============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method      47    47    8074      0
  51 --===== Traditional WHILE LOOP method CR      80    78    1085      0
  51 --===== Traditional CROSS JOIN table met       5     0      98      0
  51 --===== Itzik's CROSS JOINED CTE method        2     0      83      0
  51 --===== Housekeeping   DROP TABLE #Tally       6    15     426      0

  51 --===== Test for 10000 rows ============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method     434   344   80230     10
  51 --===== Traditional WHILE LOOP method CR     671   563   10240      9
  51 --===== Traditional CROSS JOIN table met      25    31     302     15
  51 --===== Itzik's CROSS JOINED CTE method       24     0     192     15
  51 --===== Housekeeping   DROP TABLE #Tally       7    15     531      0

  51 --===== Test for 100000 rows ===========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method    4143  3813  800260    154
  51 --===== Traditional WHILE LOOP method CR    5820  5547  101380    161
  51 --===== Traditional CROSS JOIN table met     160   140     479    211
  51 --===== Itzik's CROSS JOINED CTE method      153   141     276    204
  51 --===== Housekeeping   DROP TABLE #Tally      10    15     761      0

  51 --===== Test for 1000000 rows ==========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method   41349 37437 8001048   1601
  51 --===== Traditional WHILE LOOP method CR   59138 56141 1012785   1682
  51 --===== Traditional CROSS JOIN table met    1224  1219    2429   2101
  51 --===== Itzik's CROSS JOINED CTE method     1448  1328    1217   2095
  51 --===== Housekeeping   DROP TABLE #Tally       8     0     415      0

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

एक साइड बार के रूप में, श्री डेनी बिल्कुल सही जगह पर है ... सही ढंग से आकार के स्थायी नंबर या टैली टेबल ज्यादातर चीज़ों के लिए जाने का तरीका है। सही ढंग से आकार का क्या मतलब है? खैर, अधिकांश लोग तारीख बनाने या VARCHAR (8000) पर विभाजन करने के लिए टैली टेबल का उपयोग करते हैं। यदि आप "एन" पर सही क्लस्टर इंडेक्स के साथ 11,000 पंक्ति टैली तालिका बनाते हैं, तो आपके पास 30 से अधिक वर्षों की तिथियां बनाने के लिए पर्याप्त पंक्तियां होंगी (मैं बंधक के साथ काम करता हूं, इसलिए 30 साल मेरे लिए एक महत्वपूर्ण संख्या है ) और निश्चित रूप से एक VARCHAR (8000) विभाजन को संभालने के लिए पर्याप्त है। "सही आकार" इतना महत्वपूर्ण क्यों है? यदि टैली टेबल का बहुत उपयोग किया जाता है, तो यह आसानी से कैश में फिट बैठता है जो स्मृति पर बहुत अधिक दबाव के बिना तेज रफ्तार से बनाता है।

Last but not least, every one knows that if you create a permanent Tally table, it doesn't much matter which method you use to build it because 1) it's only going to be made once and 2) if it's something like an 11,000 row table, all of the methods are going to run "good enough". So why all the indigination on my part about which method to use???

जवाब यह है कि कुछ गरीब लड़के/लड़की जो किसी भी बेहतर तरीके से नहीं जानते हैं और सिर्फ अपनी नौकरी पाने की जरूरत है, वे रिकर्सिव सीटीई विधि की तरह कुछ देख सकते हैं और इसे इमारत के मुकाबले ज्यादा बड़े और अक्सर इस्तेमाल करने के लिए इस्तेमाल करने का फैसला कर सकते हैं एक स्थायी टैली तालिका और मैं उन लोगों की सुरक्षा करने के लिए प्रयास कर रहा हूं, सर्वर उनके कोड चलते हैं, और वह कंपनी जो उन सर्वरों पर डेटा का मालिक है । हाँ ... यह एक बड़ा सौदा है। यह हर किसी के लिए भी होना चाहिए। "पर्याप्त पर्याप्त" की बजाय चीजों को करने का सही तरीका सिखाएं। किसी पोस्ट या पुस्तक से कुछ पोस्ट करने या उपयोग करने से पहले कुछ परीक्षण करें ... वास्तव में, आप जो जीवन बचाते हैं, वह वास्तव में हो सकता है, खासकर यदि आपको लगता है कि एक रिकर्सिव सीटीई इस तरह के कुछ के लिए जाने का तरीका है। ;-)

सुनने के लिए धन्यवाद...

0
जोड़ा
+1 सबसे उपयोगी उत्तर।
जोड़ा लेखक Martin Smith, स्रोत
मैं वास्तव में वास्तव में चाहता हूं कि अधिक लोगों को सामाजिक उत्तरदायित्व की भावना हो। ने कहा है कि किसी अन्य कारण के लिए किसी को एक नंबर तालिका को पॉप्युलेट करने की आवश्यकता होगी, अगर किसी कारण से, ऐसा लगता है < कोड> चयन करें w/ पहचान सीटीई से तेज़ है
जोड़ा लेखक Andre Figueiredo, स्रोत
बहुत दयालु प्रतिक्रिया के लिए धन्यवाद, आंद्रे।
जोड़ा लेखक Jeff Moden, स्रोत

This article gives 14 different possible solutions with discussion of each. The important point is that:

दक्षता के संबंध में सुझाव और   प्रदर्शन अक्सर व्यक्तिपरक होते हैं।   चाहे कोई प्रश्न कैसे हो रहा हो   इस्तेमाल किया, भौतिक कार्यान्वयन   एक क्वेरी की दक्षता निर्धारित करता है।   इसलिए, पर भरोसा करने के बजाय   पक्षपातपूर्ण दिशानिर्देश, यह जरूरी है   कि आप क्वेरी का परीक्षण करते हैं और निर्धारित करते हैं   कौन सा बेहतर प्रदर्शन करता है।

मुझे व्यक्तिगत रूप से पसंद आया:

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
0
जोड़ा
स्वीकृत उत्तर से गलत साबित हुआ? यह 'इष्टतम' नहीं है, हालांकि यह सुंदर दिखता है।
जोड़ा लेखक Rbjz, स्रोत

अभी भी बहुत बाद में, मैं थोड़ा अलग 'पारंपरिक' सीटीई योगदान देना चाहता हूं (पंक्तियों की मात्रा प्राप्त करने के लिए बेस टेबल को स्पर्श नहीं करता है):

--===== Hans CROSS JOINED CTE method
WITH Numbers_CTE (Digit)
AS
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT HundredThousand.Digit * 100000 + TenThousand.Digit * 10000 + Thousand.Digit * 1000 + Hundred.Digit * 100 + Ten.Digit * 10 + One.Digit AS Number
INTO #Tally5
FROM Numbers_CTE AS One CROSS JOIN Numbers_CTE AS Ten CROSS JOIN Numbers_CTE AS Hundred CROSS JOIN Numbers_CTE AS Thousand CROSS JOIN Numbers_CTE AS TenThousand CROSS JOIN Numbers_CTE AS HundredThousand

यह सीटीई इटज़िक के सीटीई के बाद और अधिक पढ़ता है लेकिन पारंपरिक सीटीई से कम है। हालांकि, यह लगातार अन्य प्रश्नों के बाद कम WRITES निष्पादित करता है। जैसा कि आप जानते हैं, लेखन लगातार काफी महंगा होते हैं तो पढ़ता है।

अवधि कोर (MAXDOP) पर भारी निर्भर करती है, लेकिन, मेरे 8core पर लगातार लगातार (एमएस में कम अवधि) निष्पादित करती है, फिर अन्य प्रश्न।

मै इस्तेमाल कर रहा हूँ:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
May 14 2014 18:34:29 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

विंडोज सर्वर 2012 आर 2, 32 जीबी, ज़ीऑन एक्स 3450 @ 2.67 गीगा, 4 कोर एचटी सक्षम।

0
जोड़ा