SQL, tabel auxiliar de numere

Pentru anumite tipuri de interogări sql, o tabelă auxiliară de numere poate fi foarte utilă. Acesta poate fi creat ca un tabel cu câte rânduri aveți nevoie pentru o anumită sarcină sau ca funcție definită de utilizator care returnează numărul de rânduri necesare în fiecare interogare.

Care este modalitatea optimă de a crea o astfel de funcție?

0
fr hi bn
Pentru a completa un astfel de tabel, de exemplu.
adăugat autor vzczc, sursa
Puteți explica de ce ați face acest lucru mai degrabă decât să utilizați o masă pre-umplută cu numere?
adăugat autor jammus, sursa
Nu toate aplicațiile DBA și/sau terțe părți vor permite adăugarea unei mese permanente.
adăugat autor JeffO, sursa

7 răspunsuri

Folosind SQL Server 2016 + pentru a genera tabele de numere ai putea folosi 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 Cum putem folosi OPENJSON pentru a genera o serie de numere?

0
adăugat
Frumos. Cred că ar fi putut fi folosit în mod similar XML, dacă position() a fost pe deplin suportat în XQuery de la SQL Server.
adăugat autor Andriy M, sursa

Această vizualizare este super rapidă și conține toate valorile pozitive 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
adăugat
Orice motiv special pentru a adăuga WITH SCHEMABINDING în vizualizare?
adăugat autor ca9163d9, sursa
Adăugarea cu "SCHEMABINDING" poate face ca interogările să fie mai rapide. Ajută optimizatorul să știe că nu există date accesate. (Consultați blogs.msdn.com/b/sqlprogrammability/ arhiva/2006/05/12/& hellip; )
adăugat autor Anthony Faull, sursa
0 este adesea utilă. Și aș fi transformat probabil coloana finală în int . De asemenea, ar trebui să știți că metoda este inclusă în răspunsul acceptat (fără 0 sau conversia în int ) prin metoda Metoda CTE CROSS JOINED .
adăugat autor Andriy M, sursa
Mă întreb dacă @AnthonyFaull poate susține acest lucru cu unele măsurători.
adăugat autor Rbjz, sursa

Funcția cea mai optimă ar fi utilizarea unei tabele în locul unei funcții. Utilizarea unei funcții determină încărcarea CPU suplimentară pentru a crea valorile pentru datele returnate, mai ales dacă valorile returnate acoperă un interval foarte mare.

0
adăugat
IO va fi aproape întotdeauna mai ieftin decât CPU, mai ales că acest tabel ar fi mic și probabil deja în budferpool.
adăugat autor mrdenny, sursa
Cred că depinde de situația ta. Între cele două opțiuni cu cele mai bune performanțe, puteți face schimburi între costurile IO și CPU, în funcție de ceea ce este mai scump pentru dvs.
adăugat autor Rbjz, sursa
@mrdenny I/O este întotdeauna mod mai scump și mai lent decât CPU. SSD-urile au schimbat acest lucru într-o oarecare măsură în ultimii ani, însă în majoritatea arhitecturilor de producție aceste SSD-uri au o legătură de rețea între ele și procesoarele. Singurele baze de date pe care le văd că sunt cu adevărat legate de CPU sunt difuzarea unor aplicații nefolosite numai de ORM sau învățarea mașinilor grele.
adăugat autor rmalayter, sursa

editați: consultați comentariul lui Conrad de mai jos.

Răspunsul lui Jeff Moden este grozav ... dar găsesc pe Postgres că metoda Itzik eșuează dacă nu elimini rândul E32.

Puțin mai rapid pe postgres (40ms vs 100ms) este o altă metodă pe care am găsit-o la aici adaptat pentru postgres:

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

Pe măsură ce mă mut din SQL Server în lumea Postgres, s-ar putea să fi ratat o modalitate mai bună de a face mese de corespondență pe acea platformă ... INTEGER ()? SECVENŢĂ()?

0
adăugat
s-ar putea să fi ratat o modalitate mai bună de a face tabele de corespondență pe postgres Da, ați făcut generate_series
adăugat autor Conrad Frix, sursa
mulțumesc Conrad ... eroare de începător.
adăugat autor Ruskin, sursa

Heh ... îmi pare rău că răspund atât de târziu la un post vechi. Si, da, a trebuit sa raspund, pentru ca cel mai popular raspuns (in acel moment, raspunsul recursiv CTE cu legatura cu 14 metode diferite) pe acest thread este, ummm ... performanta contestata in cel mai bun caz.

În primul rând, articolul cu cele 14 soluții diferite este bine pentru a vedea diferitele metode de creare a unui tabel Numbers/Tally în zbor, dar după cum sa subliniat în articol și în firul citat, este foarte important important citat...

"sugestii privind eficiența și   performanțele sunt deseori subiective.   Indiferent de modul în care este o interogare   utilizate, implementarea fizică   determină eficiența unei interogări.   Prin urmare, mai degrabă decât să se bazeze pe   orientări părtinitoare, este imperativ   să testați interogarea și să determinați   care se comportă mai bine. "

În mod ironic, articolul în sine conține multe afirmații subiective și "orientări părtinitoare", cum ar fi "un CTE recursiv poate genera un număr destul de eficient " și o metodă eficientă de a folosi buclă WHILE de la postarea de grup de știri de către Itzik Ben-Gen " (despre care sunt sigur că a postat doar pentru scopuri comparative). Oameni buni ... Doar menționând numele bun al lui Itzik ar putea să-i ducă pe niște sloboși săraci să folosească de fapt metoda oribilă. Autorul ar trebui să practice ceea ce predică el și ar trebui să facă un test de performanță puțin înainte de a face astfel de declarații ridicol de incorecte, în special în fața oricărei scalabilități.

Cu gândul de a face unele teste înainte de a face orice afirmații subiective despre ceea ce face orice cod sau ceea ce cineva "îi place", iată câteva coduri pe care le puteți face propriile teste cu. Configurați profilul pentru SPID-ul pe care îl executați și verificați-l singur ... faceți doar un "Search'n'Replace" al numărului 1000000 pentru numărul "preferat" și vedeți ...

--===== 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

În timp ce suntem la el, iată numerele pe care le obțin de la SQL Profiler pentru valorile de 100, 1000, 10000, 100000 și 1000000 ...

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

După cum puteți vedea, metoda CTE recursivă este a doua cea mai rea doar pentru Loop-ul de timp și CPU și are de 8 ori presiunea de memorie sub formă de citiri logice decât Loop-ul . Este vorba despre RBAR pe steroizi și ar trebui să fie evitată, cu orice preț, pentru orice calcule cu un singur rând, la fel cum ar trebui să fie evitată o buclă. Există locuri în care recursivitatea este destul de valoroasă, dar nu este una dintre ele .

Ca o bară laterală, domnul Denny este absolut la fața locului ... o tabelă numerică corectă Numbers sau Tally este calea de a merge pentru majoritatea lucrurilor. Ce înseamnă dimensiunea corectă? Ei bine, cei mai mulți oameni folosesc un tabel Tally pentru a genera date sau pentru a face splituri pe VARCHAR (8000). Dacă creați un tabel Tally de 11.000 de rânduri cu indexul cluster corect pe "N", veți avea suficiente rânduri pentru a crea date valabile mai mult de 30 de ani (lucrez cu ipoteci un pic echitabil, astfel încât 30 de ani sunt un număr important pentru mine ) și cu siguranță suficient pentru a face față unui split VARCHAR (8000). De ce este "dimensionarea corectă" atât de importantă? Dacă masa Tally este folosită foarte mult, ea se potrivește cu ușurință în memoria cache, ceea ce o face rapid, fără prea multă presiune asupra memoriei.

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???

Răspunsul este că un tip sărac care nu știe mai bine și are nevoie doar de a-și face treaba poate să vadă ceva asemănător metodei Recursive CTE și să decidă să o folosească pentru ceva mult mai mare și mult mai frecvent folosit decât clădirea un tabel permanent Tally și încerc să protejez acei oameni, serverele pe care le rulează codul și compania care deține datele de pe serverele respective . Da ... este o afacere atât de mare. Ar trebui să fie și pentru toți ceilalți. Învățați modul corect de a face lucrurile în loc de "suficient de bun". Faceți niște teste înainte de a posta sau de a folosi ceva dintr-un post sau o carte ... viața pe care o economisiți poate, de fapt, să fie a ta, mai ales dacă credeți că o CTE recursivă este calea de a merge pentru ceva de genul asta. ;-)

Vă mulțumim că ați ascultat ...

0
adăugat
+1 Cel mai util răspuns.
adăugat autor Martin Smith, sursa
Chiar îmi doresc ca mai mulți oameni să aibă sentimentul de responsabilitate socială. Au spus că și în afară ar fi nevoie de o dată pentru a popula un tabel Numbers pentru toate tipurile de lucruri, dacă este nevoie dintr-un motiv, se pare < codul> SELECT INTO w/ IDENTITY este mai rapid decât CTE .
adăugat autor Andre Figueiredo, sursa
Mulțumesc pentru feedback foarte bun, Andre.
adăugat autor Jeff Moden, sursa

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

sugestii privind eficiența și   performanțele sunt deseori subiective.   Indiferent de modul în care este o interogare   utilizate, implementarea fizică   determină eficiența unei interogări.   Prin urmare, mai degrabă decât să se bazeze pe   orientări părtinitoare, este imperativ   să testați interogarea și să determinați   care se comportă mai bine.

Mi-a placut personal:

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
0
adăugat
Dovedită greșită de răspunsul acceptat? Nu este "optim", deși arată frumos.
adăugat autor Rbjz, sursa

Încă mult mai târziu, aș vrea să contribuie cu un CTE "tradițional" ușor diferit (nu atinge tabelele de bază pentru a obține volumul de rânduri):

--===== 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

Acest CTE efectuează mai multe READ-uri decât CTE-ul lui Itzik, dar mai puțin CTE tradițional. Cu toate acestea, acesta efectuează în mod consecvent mai puține Scripturi, apoi celelalte interogări. După cum știți, înregistrările sunt în mod consistent mult mai scumpe decât Citește.

Durata depinde foarte mult de numărul de nuclee (MAXDOP), dar, pe 8core, funcționează mai repede (mai puțină durată în ms), apoi celelalte interogări.

Eu folosesc:

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: )

pe Windows Server 2012 R2, 32 GB, Xeon X3450 @ 2.67Ghz, 4 fire HT activat.

0
adăugat