Valitse SQL Server Data Between Two Date (2023)

Tekijä:Hadi Fadlallah|Päivitetty: 24.4.2023 |Kommentit (2)| Aiheeseen liittyvä:Lisää>TSQL

Ongelma

Monet SQL-kehittäjät ja -käyttäjät kohtaavat haasteita, kun he etsivät tehokkaasti tietoja tietyltä ajanjaksolta, etenkin kun käsitellään osioituja ja sirpaloituja taulukoita. Tässä SQL-opetusohjelmassa tarkastellaan eri tapoja, joilla voit kirjoittaa SQL:n palauttamaan tietoja ajanjaksojen välillä ja kuinka parantaa suorituskykyä, kun tiedot on osioitu.

Ratkaisu

Tietokantojen kanssa työskennellessä on tavallista kohdata skenaarioita, joissa sinun on etsittävä tietoja tietyltä ajanjaksolta. Onneksi Microsoft SQL Server tarjoaa useita menetelmiä tämäntyyppisten hakujen suorittamiseen T-SQL:llä. Tämä SQL-opetusohjelma havainnollistaa joitain yleisimmistä tekniikoista kahden päivämääräarvon välillä etsimiseen SQL:ssä, mukaan lukien BETWEEN-operaattorin, suurempi kuin (>) ja pienempi kuin (<) -operaattoreiden sekä DATEPART()-funktion käyttäminen. Se tarjoaa myös vinkkejä tehokkaaseen päivämäärien etsimiseen osioiduissa ja jaetuissa taulukoissa, mikä voi auttaa parantamaan suorituskykyä.

Haku päivämäärien välillä käyttämällä SQL-aritmeettisia operaattoreita

Yksi tapa etsiä kahden päivämääräarvon välillä SQL-käskyssä on käyttää aritmeettisia operaattoreita (suurempi kuin ja pienempi kuin operaattorit). Nämä operaattorit mahdollistavat halutun ajanjakson alkamis- ja lopetuspäivän määrittämisen. Esimerkiksi seuraavaa SQL-komentoa voidaan käyttää tietueiden hakemiseen välillä 2009-01-01 ja 2009-12-31.

Huomaa, että CreationDate-sarake sisältää myös ajan, joten alla oleva kysely saa tietoja vain 2009-12-31 keskiyöhön asti. Muita viimeisen päivän tietoja ei sisällytetä.

SELECT * FROM [StackOverflow2010].[dbo].[Posts] WHERE CreationDate >= '2009-01-01' AND CreationDate <= '2009-12-31';

Haku päivämäärien välillä SQL BETWEEN -operaattorin avulla

Toinen tapa etsiä kahden päivämääräarvon välillä SQL:ssä on käyttää BETWEEN-operaattoria. BETWEEN-operaattori suodattaa tulokset tietyllä alueella, mukaan lukien aloitus- ja loppuarvot. Esimerkiksi seuraavaa kyselyä voidaan käyttää kaikkien tietueiden etsimiseen välillä 2009-01-01 ja 2009-12-31.

Huomaa, että CreationDate-sarake sisältää myös ajan, joten alla oleva kysely saa tietoja vain 2009-12-31 keskiyöhön asti. Muita viimeisen päivän tietoja ei sisällytetä.

SELECT * FROM [StackOverflow2010].[dbo].[Posts] WHERE CreationDate BETWEEN '2009-01-01' JA '2009-12-31';

Haku päivämäärien välillä käyttämällä päivämäärätoimintoja

On muitakin tapoja etsiä kahden päivämääräarvon välillä SQL:ssä. Yksi menetelmä sisältää DATEPART-funktion käyttämisen tiettyjen päivämääräosien, kuten vuoden, kuukauden tai päivän, poimimiseen päivämääräsarakkeesta. Esimerkiksi seuraavaa kyselyä käytetään etsimään kaikki tietueet, joissa DateColumn-sarakkeen vuosi on 2009:

SELECT * FROM [StackOverflow2010].[dbo].[Viestit] WHERE DATEPART(vuosi, Luontipäivä) = 2009;

Vastaavasti voit käyttää DATEPARTia muiden päivämääräosien, kuten kuukauden tai päivän, poimimiseen ja vertailuun. Vaikka tämä menetelmä voi olla vähemmän intuitiivinen kuin aritmeettinen operaattori tai BETWEEN-operaattori, se voi olla hyödyllinen tietyissä skenaarioissa, erityisesti poimittaessa ja verrattaessa tiettyjä päivämääräosia.

Aritmeettisten operaattorien vs. BETWEEN vs. päivämääräfunktioiden vertailu

Alla olevassa kuvakaappauksessa voimme huomata, että kolme annettua menetelmää palauttavat saman tietuemäärän. Varmistaaksemme, että kaikki koko vuoden tiedot on valittu, muutimme kyselyitä hieman alla olevan kuvan mukaisesti varmistaaksemme, että sisällytämme koko vuoden viimeisen päivän.

Huomaa: alla olevassa ensimmäisessä kyselylauseessa, jos jokin CreationDate sattuu olemaan täsmälleen 2010-01-01 keskiyöllä, ne sisällytetään laskelmiin. Siksi on tärkeää ymmärtää käsittelemäsi tiedot.

Myös arvioidut toteutussuunnitelmat ovat hyvin samanlaisia; ne kaikki käyttävät indeksiä CreationDate-sarakkeessa:

Huomaa, että päivämäärätoiminto DATEPART suorittaa hakemistoskannauksen, kun taas kaksi muuta menetelmää suorittavat indeksihaun. Tämä johtuu siitä, että SQL Serverissä käytettäessä toimintoja (tässä tapauksessa DATEPART) kaikki tiedot on arvioitava ehdon suhteen, eikä tämä hyödytä luotujen indeksien käyttöä.

Etsitään suuria ositettuja taulukoita päivämäärien välillä

Kun haetaan kahden päivämääräarvon välillä taulukossaositettukäyttämällä päivämääräarvoa, on tärkeää hyödyntää osiointijärjestelmää. Tämä minimoi skannattavan tiedon määrän. Yksi tapa tehdä tämä on käyttää tiettyä osiota poistamalla tarpeettomat osiot, mikä tarkoittaa, että tunnistetaan ja tarkistetaan vain osiot, jotka sisältävät tietoja halutulla ajanjaksolla. Tämä voi johtaa merkittäviin suorituskyvyn parannuksiin, erityisesti suurissa pöydissä.

Voit suorittaa osion poistamisen sisällyttämällä osiointisarakkeen ja hakuehdot WHERE-lauseeseen.

Oletetaan esimerkiksi, että CreationDate-sarakkeessa on taulukon osioitu päivämäärän mukaan. Haluat löytää kaikki tietueet ajalta 2009-01-01 ja 2009-12-31. Voit käyttää tällaista kyselyä:

SELECT * FROM [StackOverflow2010].[dbo].[Posts] WHERE CreationDate >= '2009-01-01' AND CreationDate <= '2009-12-31';

Katsotaanpa realistisempaa esimerkkiä. Olen työskennellyt valtavan osioidun taulukon kanssa vuosia. Suurimman osan ajasta päivämääräsarakkeita ei käytetä osioavaimena, ja sen sijaan käytetään päivämääräosaa.

Esimerkiksi vaikka tapahtumapäivä/aika-sarake voisi olla taulukossa, vuoden päivä (1-365) -saraketta käytetään osioavaimena. Siinä tapauksessa alla olevan kyselyn käyttäminen päivämääräsarakkeen yli hakemiseen voi heikentää tehokkuutta. ,varsinkin kun datan koko on valtava eikä meillä ole mahdollisuutta luoda kaikkea tarvittavaaindeksi.

Tässä esimerkissä tarkastelemme tietoja tietyn päivämäärän ja kellonajan välillä.

SELECT * FROM [StackOverflow2010].[dbo].[Posts]WHERE CreationDate >= '2009-01-01 05:00:00' AND CreationDate <= '2009-01-05 09:00:00'

SQL Server tarjoaa useita tapoja hallita, mitkä osiot haetaan tietoarvojen perusteella. Alla olevassa esimerkissä CreationDayofYear-sarake on osioavain, ja nämä arvot ovat 1-365 jokaiselle vuoden päivälle. Kyselyssä valitsemme tiedot osioista 1, 2, 3, 4 ja 5 vuoden 5 ensimmäiseltä päivältä ja myös rajoittavat tiedot 1. ja 5. päivälle.

SELECT * FROM [StackOverflow2010].[dbo].[Posts]WHERE CreationDate >= '2009-01-01 05:00:00' AND $PARTITION.pfDayOfTheYear(CreationDayofYear) IN (1); UNION ALL SELECT * FROM [StackOverflow2010].[dbo].[Posts]WHERE $PARTITION.pfDayOfTheYear(CreationDayofYear) IN (2,3,4); UNION ALL SELECT * FROM [StackOverflow2010].[dbo].[Posts]WHERE CreationDate <= '2009-01-05 09:00:00' JA $PARTITION.pfDayOfTheYear(CreationDayofYear) IN (5);

Tässä esimerkissä $PARTITION.pfDayOfTheYear palauttaa osionnumeron CreationDayofYear-sarakkeen arvon perusteella. IN-operaattori määrittää haettavat osiot. Määrittämällä haettavat osiot voit varmistaa, että vain asiaankuuluvat osiot tarkistetaan koko taulukon tarkistamisen sijaan. Lisäksi käyttämällä yllä olevaa syntaksia autamme kyselyä optimoimaan CreationDate-sarakkeen arvon, kun olemme varmoja, että koko osio on määritetyllä ajanjaksolla. Huomautus: rajaosioille (1 ja 5) lisäämme tarvittavan suodattimen ajan sisällyttämiseksi CreationDate-sarakkeeseen, mutta tätä ei tarvita keskellä (2,3,4).

Toisaalta, jos suoritamme seuraavan SQL-kyselyn:

SELECT * FROM [StackOverflow2010].[dbo].[Posts]WHERE CreationDate >= '2009-01-01 05:00:00' AND CreationDate <= '2009-01-05 09:00:00' JA $OSIO. pfDayOfTheYear(CreationDayofYear) IN (1,2,3,4,5);

Kyselyn optimoija ei ole tietoinen seuraavista seikoista:

• Osiossa 1Luontipäivä <= '2009-01-05 09:00:00'suodatin on merkityksetön.
• Osiossa 5Luontipäivä >= '2009-01-01 05:00:00'suodatin on merkityksetön.
• Osioissa 2, 3 ja 4 CreationDate-sarakkeen suodattaminen on merkityksetöntä.

Tämän lähestymistavan on parannettava suorituskykyä työskennellessään valtavien osioitujen taulukoiden kanssa, joissa meillä ei ole mahdollisuutta luoda kaikkia tarvittavia ei-klusteroituja indeksejä. Huomaa, että kyselyn jakaminen voi heikentää suorituskykyä pienillä tai keskikokoisilla tauluilla (jopa muutama gigatavu).Suorituskyvyn lisäys riippuu myös siitä, kuinka osiot on jaettu tietovarastoon.

Haku päivämäärien välillä jaetuista taulukoista

Kun haet kahden päivämääräarvon välillä kohdassa asirpaloitutietokanta, on välttämätöntä ottaa huomioon sirpaleavain ja se, kuinka kyselyt rakennetaan reititystaulukon avulla. Jos sirpaleavain on päivän tai kuukauden arvo, useisiin sirpaleisiin ulottuvat kyselyt voivat olla pitkiä ja resursseja vaativia. Yksi tapa lieventää tätä ongelmaa on käyttää reititystaulukkoa, joka yhdistää kyselyt sopivaan sirpaleeseen querieddate-alueen perusteella. Reititystaulukko tulee optimoida minimoimaan kyselyjen tarvitsevien sirpaleiden määrä ja samalla varmistaa, että kaikki asiaankuuluvat tiedot haetaan.

Kahden päivämääräarvon välisen haun optimoimiseksi edelleen sirpaloidussa tietokannassa on tärkeää varmistaa, että indeksit on määritetty oikein. Erityisesti indeksit tulisi osioida siten, että ne ovat linjassa sirpaleavaimen kanssa, jotta yhteen sirpaleeseen reititetyt kyselyt voidaan suorittaa tehokkaasti. Lisäksi kyselyn optimointitekniikoita, kuten rinnakkaisuutta ja välimuistia, voidaan käyttää parantamaan suorituskykyä entisestään.

Johtopäätös

Yhteenvetona voidaan todeta, että haku kahden päivämääräarvon välillä SQL:ssä voidaan suorittaa useilla eri menetelmillä, kuten aritmeettisilla operaattoreilla, BETWEEN-operaattorilla ja DATEPART-funktiolla. Jotta varmistetaan, että vain oleellisia tietoja kysytään käsiteltäessä osioituja ja sirpaloituja taulukoita, kyselyiden optimointiin tulisi käyttää karsintaa, kuten reititystaulukoita ja sirpaleellisia suodattimia. Lisäksi on tärkeää määrittää indeksit ja osiot oikein, jotta ne mukautuvat sirpaleavaimeen ja optimoidaan kyselyn suorituskykyä. Ottamalla nämä tekniikat käyttöön kehittäjät voivat etsiä tehokkaasti kahden päivämäärän välillä SQL-tietokannoista ja hallita tehokkaasti suuria tietomääriä.

Seuraavat vaiheet

• Saat lisätietoja osioiduista taulukoista SQL Serverissä seuraavista vinkeistä:
  • Taulukon luominen vaakasuoralla osiolla SQL Serverissä
  • SQL-palvelimen osiointivinkkejä, opetusohjelmia, verkkoseminaareja ja paljon muuta
• Saat lisätietoja tietokannan jakamisesta seuraavasta artikkelista:
  • Tietokannan jakamisen edut ja haitat suorituskykyä ja ylläpitoa varten
• Saat lisätietoja tietotyypeistä ja päivämääräfunktioista seuraavista vinkeistä:
  • Päivämäärän ja ajan muunnokset SQL Serverin avulla
  • Päivämäärä- ja aikatietotyyppien (DateTime, DateTime2, TimeStamp) käyttäminen SQL Serverissä
  • Kuinka saada nykyinen päivämäärä SQL Serverissä GETDATE:n avulla
  • SQL Server DATEADD -toiminto
  • SQL Server DATEDIFF -funktio
  • SQL Muunna päivämääräksi VVVV-KK-PP

Kirjailijasta

Artikkeli Päivitetty viimeksi: 24.4.2023