[MS SQL] waardes ophalen die niet in m'n range tabel zitten

[Russel88]

Hoi,

Ik zit met een query in m'n maag.
Het is meer, ik kan voor deze situatie geen query bedenken.

Ik heb een tabel waar o.a. 2 integer kolommen in staan. Daarin wordt een From To mechanisme gebruikt.
Nu wil ik alle getallen hebben tussen de 100 en de 1000 die niet in de From To range vallen.

Ik kan dit oplossen door een domme cursor te gebruiken, of gebruik te maken van temp tables.
Echter wil ik 1 query hebben die ik in een view kan gebruiken. Dus valt een cursor en temp table af. En volgens mij kan dat veel handiger met ingebouwde functies van MS Sql.

Ik weet alleen niet hoe
Kan iemand mij verder helpen hiermee?

Alvast bedankt

[Exception]

Kun je eens posten hoe die tabel eruit ziet? Want zoals ik het nu begrijp kun je dit met BETWEEN oplossen?

[Russel88]

RangeTable(Van int, Tot int)
Met b.v. de volgende waardes:
100 - 200
201 - 368
370 - 600
601 - 888
890 - 1000

Resultaat van m'n query moet dus zijn:
369
889

[Verwijderd]

http://stackoverflow.com/...-missing-sequence-numbers
Hier staan wat ideeën.

[Russel88]

Verwijderd schreef op vrijdag 15 maart 2013 @ 11:26:
http://stackoverflow.com/...-missing-sequence-numbers
Hier staan wat ideeën.

Thanks.
Maar in de thread worden temp tables geadviseerd. Dit wil ik liever vermijden en hoopte ik dat MS SQL wat handige functies heeft die ik niet ken en me hierbij kan helpen.

[NMe]

Lijkt me in elk geval erg sterk. SQL is vooral bedoeld om data op te halen die bestaat in een tabel en jij wil juist data ophalen die niet bestaat in een tabel, en als klap op de vuurpijl gaat het niet alleen om waardes in die tabel maar ook de waardes die tussen twee "arbitraire" velden liggen. Als daar iets in mogelijk is zonder een temp table zou me dat heel erg verbazen.

[Russel88]

NMe schreef op vrijdag 15 maart 2013 @ 11:37:
Lijkt me in elk geval erg sterk. SQL is vooral bedoeld om data op te halen die bestaat in een tabel en jij wil juist data ophalen die niet bestaat in een tabel, en als klap op de vuurpijl gaat het niet alleen om waardes in die tabel maar ook de waardes die tussen twee "arbitraire" velden liggen. Als daar iets in mogelijk is zonder een temp table zou me dat heel erg verbazen.

Hier was ik al bang voor.
Bedankt in ieder geval voor de reacties.

[ Voor 3% gewijzigd door Russel88 op 15-03-2013 11:47 ]

[nescafe]

Als je het direct in een view wilt gebruiken, zou je een (table valued) user defined function kunnen maken.

Afhankelijk van wat je verdere wensen zijn kun je die een hele range laten genereren en op basis van een outer join (joinconditie BETWEEN en wherestatement IS NULL) de missende getallen tevoorschijn halen.

[Verwijderd]

Russel88 schreef op vrijdag 15 maart 2013 @ 11:46:

Hier was ik al bang voor.
Bedankt in ieder geval voor de reacties.

Volgende vraag dan. Waarom heb je dit binnen een view nodig? Sowieso kun je dit niet als foreign key gebruiken, want zinloos, er zal nooit een link zijn tussen deze view en andere tabellen. Volgens mij kun je er op databaseniveau weinig mee en is dit veelal een presentatie-issue.

[P_de_B]

SQL:

--testtabel maken
CREATE TABLE Ranges (id int, van int, tot int)
INSERT INTO Ranges (id, van, tot) values (1, 0, 100)
INSERT INTO Ranges (id, van, tot) values (1, 100, 200)
INSERT INTO Ranges (id, van, tot) values (1, 201, 368)
INSERT INTO Ranges (id, van, tot) values (1, 370, 600)
INSERT INTO Ranges (id, van, tot) values (1, 601, 888)
INSERT INTO Ranges (id, van, tot) values (1, 890, 1000)


--we gebruiken een tabel Tally gevuld met alle nummers die je wilt checken
CREATE TABLE Tally (num int)

--vullen van de tabel
DECLARE @i int = 1
WHILE @i < 1000
BEGIN
    INSERT INTO Tally (num) values (@i)
    SET @i = @i + 1
END

--gebruik nu de tabel om de missende nummers te vinden
SELECT t.num 
FROM Tally t
LEFT OUTER Join Ranges R ON t.num BETWEEN r.van AND r.tot
WHERE r.id IS NULL

Kijk bijvoorbeeld eens hier voor meer info over 'Tally' tabellen

[ Voor 8% gewijzigd door P_de_B op 15-03-2013 11:55 ]

[Russel88]

Bedankt voor alle antwoorden.
Ik heb een aantal ideeen samengevoegd.
Een table valued functie:

SQL:

CREATE    FUNCTION DummyRangeValues (@from as int, @to as int)  
RETURNS @myTable table 
(
    Number int
)
AS  
Begin
    Declare @i int = @from
    
    While @i < @to
    Begin
        Insert into @myTable(Number)
        Values (@i)
        Set @i = @i + 1
    End

    Return
End; 

En daarna m'n view als volgt geschreven:

SQL:

Select d.Number
From DummyRangeValues(100, 999) d
Left outer Join Range r on d.Number Between r.FromVal AND r.ToVal
Where r.ID is null

[P_de_B]

Waarom maak je niet een echte tabel DummyRangesValues? Nu wordt iedere keer die tabel opnieuw opgebouwd.

[Russel88]

P_de_B schreef op vrijdag 15 maart 2013 @ 12:49:
Waarom maak je niet een echte tabel DummyRangesValues? Nu wordt iedere keer die tabel opnieuw opgebouwd.

Flexibiliteit.
Willen ze in de toekomst een extra controle van 100 tm 500, dan hoef ik geen nieuwe tabel aan te maken.

[P_de_B]

Dan zou ik nog die tabel aanmaken en in de view where d.Number BETWEEN 100 AND 999 gebruiken. Het is denk ik zonde om die tabel steeds opnieuw aan te maken, maar dat is ook afhankelijk van hoe vaak deze view wordt aangeroepen. Als dat 2x per dag is, zit het denk ik niemand in de weg. Iedere minuut wordt al vervelender.

[KopjeThee]

Russel88 schreef op vrijdag 15 maart 2013 @ 11:20:
RangeTable(Van int, Tot int)
Met b.v. de volgende waardes:
100 - 200
201 - 368
370 - 600
601 - 888
890 - 1000

Resultaat van m'n query moet dus zijn:
369
889

Geen idee of dit werkt, niet uitgeprobeerd en ik ben niet echt een SQL kenner. Maar die tabel met zichzelf joinen. Dan natuurlijk proberen aan de rechterkant (r) telkens de rij te zetten die direct volgt op de range van de linkerkant (l). Zodat je de "tot" van de linkerkant (l) kunt vergelijken met de "van" van de rechterkant (r), en daar ontbrekende ranges uit kunt maken.

SQL:

create view OntbrekendeRanges as
select
  l.tot + 1 as ontbrekend_van,
  r.van - 1 as ontbrekend_tot
from RangeTable as l, RangeTable as r
where l.tot - r.van > 1
group by l.tot
having l.tot - r.van = min(l.tot - r.van)

[ Voor 3% gewijzigd door KopjeThee op 15-03-2013 21:18 ]

[RobIII]

Ik zou dit hebben gedaan (gebaseerd op P_de_B's Range testtabel, regel 1-8 dus):

SQL:

Declare @start int, @end int
Select @start=min(van), @end=max(tot) from ranges;
With NumberSequence( Number ) as (
    Select @start as Number union all
    Select Number + 1
    From NumberSequence
    Where Number < @end
)
Select Number
From NumberSequence
Where Not exists(Select 1 From Ranges Where Number Between van and tot)
Option (MaxRecursion 0)

369
889

Deze gaat uit van de hoogste "tot" en de laagste "van" (regel 2) om te bepalen waartussen de ontbrekende getallen gezocht moeten worden; je kunt natuurlijk ook gewoon @start en @end 'hardcoden'.
Vervolgens wordt een sequence (met 'interval' van 1) gegenereerd en daarna wordt met een not exists (leest 't makkelijkst) gekeken welke getallen ontbreken.

Geen cursors, geen temp/tally tables, geen while lussen. Maar hou er wel rekening mee dat een 'tally table' bij grote(re) aantallen dan nu (0-1000) waarschijnlijk beter gaan performen.

Regel 10/11 kun je ook schrijven als:

SQL:

Left outer join Ranges on Number between van and tot
Where id is null

Zie hier voor meer info.

[ Voor 52% gewijzigd door RobIII op 15-03-2013 22:10 ]

[pedorus]

Interessant, maar vooralsnog wint de tally table:

quote: http://www.littlekendra.com/2011/01/04/tallytables/

I can’t really think of a situation in which I’ll use the recursive CTE for this specific issue.

[DamadmOO]

De tally table zal altijd winnen. De enige CTE die er dicht bij in de buurt komt is die van Itzik:

SQL:

  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
  FROM cteTally
  WHERE N <= 1000000;
GO

Mijn persoonlijk voorkeur is om gewoon een Tally tabel te hebben. Maak deze gewoon groot genoeg zodat deze in de toekomst niet meteen te klein is. Zoveel opslag kost het namelijk niet. Er zijn erg veel problemen die met behulp van een Tally tabel een stuk makkelijker opgelost kunnen worden, ook al is er met de komst van SQL 2012 en window functions ook een boel mogelijk zonder extra tabel.