☰

» »

SQL query za datume, ki se ne prekrivajo

SQL query za datume, ki se ne prekrivajo

puncka ::

Živjo,

recimo, da imamo naslednji polji v tabeli:
- od (npr. 1.1.2013)
- do (npr. 31.1.2013)

Kako bi našla vse zapise, ki se ne prekrivajo (glej primer)?

Primer 1 - zapisa se ne prekrivata
1) 1.1.2013 - 31.1.2013
2) 1.2.2013 - 28.2.2013

Primer 2 - zapisa se prekrivata
1) 1.1.2013 - 31.1.2013
2) 15.1.2013 - 15.2.2013

Hvala za kakršenkoli nasvet :)

Mesar ::

Recimo... http://salman-w.blogspot.com/2012/06/sq...
Your turn to burn!

puncka ::

To ni ravno to, kar sem iskala. Ali obstaja način, da bi izmed npr. 1000-ih zapisov našla tiste, ki se ne prekrivajo? Torej nimam konkretnega datuma, ki bi ga lahko vnesla v where pogoj. Kot nekakšna for zanka, ki bi se sprehodila čez vse zapise ...

black ice ::

Kaj takšnega mogoče (nisem preverjal).
http://stackoverflow.com/questions/3038...

puncka ::

Tale zadnji združuje različna obdobja po nekem določenem parametru. Ni to to ..

Mesar ::

Zakaj pa rabiš to? Ni druge rešitve? Ta je namreč izredno nepraktična, ker če hočeš to v SQL stavku delat nimaš druge kot vsak datum primerjat z vsakim pa potem delat seznam podvojenih... pri 1000 zapisih bodo že hudi problemi s hitrostjo takšnega querya.
Your turn to burn!

krneki0001 ::

Če je 1000 zapisov problem, kakšno patetično bazo pa potem imaš? Pri nas so tabele s po 500 miljoni podatkov (ker jih redno arhiviramo, drugače bi šle številke v miljarde) pa ni nekih probemov s performansami tudi če pregleduješ s queryjem celoten table space. Baza je pa DB2 od IBM-a.
Asrock X99 Extreme 4 | Intel E5-2683V4 ES | 64GB DDR4 2400MHz ECC |
Samsung 250GB M.2 | Asus 1070 TI | 850W Antec | LC Tank Buster

Mesar ::

krneki0001 je izjavil:

Če je 1000 zapisov problem, kakšno patetično bazo pa potem imaš? Pri nas so tabele s po 500 miljoni podatkov (ker jih redno arhiviramo, drugače bi šle številke v miljarde) pa ni nekih probemov s performansami tudi če pregleduješ s queryjem celoten table space. Baza je pa DB2 od IBM-a.


Ja super. Zdaj pa še poskusi kaj takšnega z MySQLom, pa upoštevaj, da glede na ostale poste gre za nekoga ki se uči/študira in to dela po možnosti na svojem PCju (ne nekih hudih serverskih mašinah).

In pa ... tukaj ni govora iti čez table space pa iskat neki noter ali pa vrnit par vrstic, ki ustrezajo pogojem ampak primerjat zapise vsakega z vsakim pa ob tem delat seznam, hkrati pa moreš to delati še čez dve polji na enkrat (začetni čas in končni čas).

Pa lahko imaš za tako stvar samo eno preprosto tabelo z (id, dateTimeStart, dateTimeEnd) pri čemer maš primary index na ID ter navaden index na dateTimeStart in dateTimeEnd, pa bo z relativno malo podatkov še vedno hudo počasno vse skupaj.
Your turn to burn!

Zgodovina sprememb…

  • spremenil: Mesar ()

WarpedGone ::

Kako bi našla vse zapise, ki se ne prekrivajo (glej primer)?

1. v sql dodaš "window" funkcijo, ki v posamezni vrstici prikaže končni datum v prejšnji vrstici,če sortiraš po začetnem datumu
3. vse skup zapreš v subselect in v zunanjem selectu napišeš simpl pogoj

npr:
SELECT *
  FROM (SELECT t.*,
               LAG(t.DATUM_DO, 1, 0) OVER (ORDER BY t.DATUM_OD) AS PREJSNJI_DATUM_DO
          FROM tabela t
       ) sub
WHERE sub.PREJSNJI_DATUM_DO < sub.DATUM_OD
Zbogom in hvala za vse ribe

WarpedGone ::

Druga varianta je, da podatke pretopiš iz načina OD / DO v novo tabelo kjer maš hranjene posamezne datume tega intervala nato pa tuki pogrupiraš različne datume in vrneš zapise kjer je natanko en različen datum.

npr vrstice 
[A 1.1.2013 3.1.2013]
[B 2.1.2013 5.1.2013]
[C 6.1.2013 7.1.2013]

pretvoriš v vrstice
[A 1.1.2013]
[A 2.1.2013]
[A 3.1.2013]
[B 2.1.2013]
[B 3.1.2013]
[B 4.1.2013]
[B 5.1.2013]
[C 6.1.2013]
[C 6.1.2013]


iz tega ven lahko dobiš ključe zapisov ki obstajajo samo v enem intervalu:
SELECT *
  FROM tabela t
 WHERE (SELECT DATUM FROM tabela_dnevi GROUP BY DATUM HAVING COUNT(DISTINCT id) = 1) BETWEEN DATUM_OD AND DATUM_DO


Variacija druge variante je, da namesto fizične tabele "tabela_dnevi" ustrezne zapise zgeneriraš v subselectu.
Zbogom in hvala za vse ribe

Zgodovina sprememb…

krneki0001 ::

Mesar je izjavil:

krneki0001 je izjavil:

Če je 1000 zapisov problem, kakšno patetično bazo pa potem imaš? Pri nas so tabele s po 500 miljoni podatkov (ker jih redno arhiviramo, drugače bi šle številke v miljarde) pa ni nekih probemov s performansami tudi če pregleduješ s queryjem celoten table space. Baza je pa DB2 od IBM-a.


Ja super. Zdaj pa še poskusi kaj takšnega z MySQLom, pa upoštevaj, da glede na ostale poste gre za nekoga ki se uči/študira in to dela po možnosti na svojem PCju (ne nekih hudih serverskih mašinah).

In pa ... tukaj ni govora iti čez table space pa iskat neki noter ali pa vrnit par vrstic, ki ustrezajo pogojem ampak primerjat zapise vsakega z vsakim pa ob tem delat seznam, hkrati pa moreš to delati še čez dve polji na enkrat (začetni čas in končni čas).

Pa lahko imaš za tako stvar samo eno preprosto tabelo z (id, dateTimeStart, dateTimeEnd) pri čemer maš primary index na ID ter navaden index na dateTimeStart in dateTimeEnd, pa bo z relativno malo podatkov še vedno hudo počasno vse skupaj.



To da moraš vsak zapis z vsakim primerjat je koliko 1000 vrstic X 1000 primerjav? en maljon poizvedb - spustiš zvečer, pa bo do jutra naredil na atom procesorju, če imaš vsaj C2D procesor nima dve uri kej delat. Če pa na kakem I procesorju z SSD diskom delaš, pa bo naredil instantno.
Asrock X99 Extreme 4 | Intel E5-2683V4 ES | 64GB DDR4 2400MHz ECC |
Samsung 250GB M.2 | Asus 1070 TI | 850W Antec | LC Tank Buster

Zgodovina sprememb…

galu ::

Najpreprosteje (kar se tiče znanja kode) ampak najbrž tudi najpočasneje (za CPU) je pač običajno "rudarjenje".

Strni si datum v integer (danes bi bil npr. zacetek = 20130705, jutri pa konec = 20130706), potem pa primerjaš s for loopom, da je zacetek i++ objekta manjši ali enak i objektu, IN, da je konec i++ objekta večji ali enak od začetka i objekta.


Da si pa to pripraviš, bi pa jaz naredil tako, da vsak termin dobi svojo "dimenzijo" v arrayu. Napolnjen bi bil tako: $array[i][$object]

Edit: Aja, vidim (glede na naslov), da hočeš tole že vse v SQL bazi narediti...
Tako to gre.

Zgodovina sprememb…

  • spremenil: galu ()

krneki0001 ::

Sej komot naredi nov view v SQL-u in substringa cifre iz datuma, ter jih sestavi skupaj kot si ti napisal in se potem v naslednjem sql-u po tem view-u sprašuje in primerja podatke.

CREATE OR REPLACE VIEW_NOVI AS
SELECT SUBSTR(DATUM1, .....
Asrock X99 Extreme 4 | Intel E5-2683V4 ES | 64GB DDR4 2400MHz ECC |
Samsung 250GB M.2 | Asus 1070 TI | 850W Antec | LC Tank Buster

jernejl :: 

select * from tabela t1 where not exists (
   select * from tabela t2 where (t2.datum_od <= t1.datum_od and t2.datum_do >= t1.datum_do) or ...
)


Intervali se lahko med sabo prekrivajo na različne načine. Zgoraj v podpoizvedbi je ena varianta, treba pa je še nekoliko dopolniti where pogoj.

Zgodovina sprememb…

  • spremenil: jernejl ()

jernejl ::

Paziti je treba tudi na primer, ko podpoizvedba opravlja primerjavo vrstice same s seboj. Tukaj npr. pomaga, če ima tabela kak primarni ključ, ali pa uporabiti kakšen drug "trik".

Mesar ::

krneki0001 je izjavil:

Mesar je izjavil:

krneki0001 je izjavil:

Če je 1000 zapisov problem, kakšno patetično bazo pa potem imaš? Pri nas so tabele s po 500 miljoni podatkov (ker jih redno arhiviramo, drugače bi šle številke v miljarde) pa ni nekih probemov s performansami tudi če pregleduješ s queryjem celoten table space. Baza je pa DB2 od IBM-a.


Ja super. Zdaj pa še poskusi kaj takšnega z MySQLom, pa upoštevaj, da glede na ostale poste gre za nekoga ki se uči/študira in to dela po možnosti na svojem PCju (ne nekih hudih serverskih mašinah).

In pa ... tukaj ni govora iti čez table space pa iskat neki noter ali pa vrnit par vrstic, ki ustrezajo pogojem ampak primerjat zapise vsakega z vsakim pa ob tem delat seznam, hkrati pa moreš to delati še čez dve polji na enkrat (začetni čas in končni čas).

Pa lahko imaš za tako stvar samo eno preprosto tabelo z (id, dateTimeStart, dateTimeEnd) pri čemer maš primary index na ID ter navaden index na dateTimeStart in dateTimeEnd, pa bo z relativno malo podatkov še vedno hudo počasno vse skupaj.



To da moraš vsak zapis z vsakim primerjat je koliko 1000 vrstic X 1000 primerjav? en maljon poizvedb - spustiš zvečer, pa bo do jutra naredil na atom procesorju, če imaš vsaj C2D procesor nima dve uri kej delat. Če pa na kakem I procesorju z SSD diskom delaš, pa bo naredil instantno.


Za katero praktično aplikacijo pa je sprejemljivo, da čaka eno uro, da se query izvrši? Razen kakih jobov, ki jih sistem izvaja sam... to da boš pred prikazom nekih podatkov vsakič čakal 15+ minut pa je nesprejemljivo. Jaz take zadeve nebi upal prodati nikomur, še manj pa se podpisati pod njo... ker je to bolj na nivoju indijskega programerja, ki dela za 3$/uro.
Your turn to burn!

krneki0001 ::

One man band ali računalničarji na majhnih sistemih ne morejo razumeti in ne bodo razumel, da se obdelave lahko izvajajo tudi po par dni zaradi mase podatkov. V večjih sistemih z kakimi IBM-ovimi DB2 sistemi, kjer gre za miljarde podatkov in delaš nad tem statistiko (delam take stvari na mesečnem nivoju in na dnevnem), obdelave delajo tudi po par dni, od tega dela samo nabor podatkov s kurzorjem traja več ur. Samo za primerjavo; IBM-ova IDAA (sistem prav namenjen statistikam in obdelavam velikih mas podatkov) pohitri SQL za maximalno 1900 krat, pa statistika dela 3 do 4 ure z optimitziranim SQL-om in gre nabor po indexu.
To če SQL dela eno uro na offline sistemu (ponoči, ko ni dostopov) je čisto normalna zadeva pri takih zadevah.

Če gre za enkratnop zadevo, se pa spusti taka obdelava med vikendom, ko je baza nezasedena.

Drugo pa je, če je to ponovljiva zadeva in jo spuščaš vsakodnevno ali večkrat na dan, pa da je podatkov malo. Ampak 1000 vrstic je praktično nič. Sam sem delal na PC-ju P4 2.66GHz z mySQL in tabelo s 64.000 vrsticami in okoli 8.000 preverjanj za vsako vrstico (torej 64.000 * 8.000 ponovitev), pa je to delalo točno 1 uro in 6 minut (smo štopali, ker smo delali neko primerjavo in smo morali narest na koncu poročilo) - nekaj smo morali v podatkih poiskat, primerjat in narest update, če je bilo pravilno ujemanje.
Tako da z današnjimi večjedrnimi procesorji bi moralo biti 1000 vrstic in 1000 preverjanj za vsako vrstico narejeno praktično online.
Asrock X99 Extreme 4 | Intel E5-2683V4 ES | 64GB DDR4 2400MHz ECC |
Samsung 250GB M.2 | Asus 1070 TI | 850W Antec | LC Tank Buster

WarpedGone ::

@jernejl
Če si odgovarjal na moje primere ... bolje jih poglej.
Zbogom in hvala za vse ribe

Mesar ::

krneki0001 je izjavil:

One man band ali računalničarji na majhnih sistemih ne morejo razumeti in ne bodo razumel, da se obdelave lahko izvajajo tudi po par dni zaradi mase podatkov. V večjih sistemih z kakimi IBM-ovimi DB2 sistemi, kjer gre za miljarde podatkov in delaš nad tem statistiko (delam take stvari na mesečnem nivoju in na dnevnem), obdelave delajo tudi po par dni, od tega dela samo nabor podatkov s kurzorjem traja več ur. Samo za primerjavo; IBM-ova IDAA (sistem prav namenjen statistikam in obdelavam velikih mas podatkov) pohitri SQL za maximalno 1900 krat, pa statistika dela 3 do 4 ure z optimitziranim SQL-om in gre nabor po indexu.
To če SQL dela eno uro na offline sistemu (ponoči, ko ni dostopov) je čisto normalna zadeva pri takih zadevah.

Če gre za enkratnop zadevo, se pa spusti taka obdelava med vikendom, ko je baza nezasedena.

Drugo pa je, če je to ponovljiva zadeva in jo spuščaš vsakodnevno ali večkrat na dan, pa da je podatkov malo. Ampak 1000 vrstic je praktično nič. Sam sem delal na PC-ju P4 2.66GHz z mySQL in tabelo s 64.000 vrsticami in okoli 8.000 preverjanj za vsako vrstico (torej 64.000 * 8.000 ponovitev), pa je to delalo točno 1 uro in 6 minut (smo štopali, ker smo delali neko primerjavo in smo morali narest na koncu poročilo) - nekaj smo morali v podatkih poiskat, primerjat in narest update, če je bilo pravilno ujemanje.
Tako da z današnjimi večjedrnimi procesorji bi moralo biti 1000 vrstic in 1000 preverjanj za vsako vrstico narejeno praktično online.


No in če govorimo o preprosti aplikaciji ali pa spletni strani, tem podatkom je običajno potrebno dodati še kak podatek iz druge tabele, pa ko je malo več podatkov ORDER pa LIMIT za strani, sortiranje. In če potem končni uporabnik preko nekega vmesnika pregleduje recimo kak seznam, ki zahteva tak query, verjetno ni sprejemljivo, da vsakič ko gre na naslednjo stran čaka več kot par sekund, da se mu naloži (pa že to je dosti) ali pač? Bog ne, daj da potem hočeš še searchat po teh podatkih, ker bil si upal trditi, da ni praktično, da more nekdo pregledat 500 izpisov, da najde želenega. Za uporabno in odzivno aplikacijo pa bi se po tvoje morali vsi queryi izvršit v koliko časa? Pol ure? :)) Da greš vmes na tri pire pa se še strezniš... preden lahko narediš naslednji korak.
Your turn to burn!

krneki0001 ::

To je že res, ampak ti ne boš šel iskat datume in delat primerjave na neki strani v html-ju in majhni bazi. To se dela v offline načinu med servisiranjem.
Za aplikacijo bi pa to sprogramiral, da aplikacija to enkrat naredi, ne pa da dela vsakič.

Sicer pa ne vem zakaj bi moral uporabnik gledati 500 zapisov, da najde želenega. To pomeni, da so že kriteriji za nabor napačni in je slabo vse skupaj zasnovano in koncept ni pravi. Na ekran itak dobi maximalno nekih 30 zapisov (pri nas 22 na en ekran, pa že to je veliko).

Če te že zanima, pravilno konceptirana aplikacija pri nas ima nekje 0.03 sekunde na poizvedbo. Query za vpis 3.7 miljona podatkov - vsaka vrstica ima nekih 64 polje, ki se spreminjajo (če že obstaja naredi update, drugače pa insertira podatke na novo) traja nekih 7 minut.
Imamo res hitre zadeve in tudi optimizacije in reorg se dela dnevno v nočnem času, ko so baze manj obremenjene, tako da imamo vsak dan vse optimizirano in pripravljeno za nov "boj".
Asrock X99 Extreme 4 | Intel E5-2683V4 ES | 64GB DDR4 2400MHz ECC |
Samsung 250GB M.2 | Asus 1070 TI | 850W Antec | LC Tank Buster

Zgodovina sprememb…

puncka ::

Sem obupala nad primerom :) in različno pogrupirala zapise ter na uč pogledala. Je bilo veliko hitreje kot, če bi se še naprej ukvarjala s tem. Hvala za pomoč.

omnimint ::

puncka je izjavil:

Sem obupala nad primerom :) in različno pogrupirala zapise ter na uč pogledala. Je bilo veliko hitreje kot, če bi se še naprej ukvarjala s tem. Hvala za pomoč.

To je za moje pojme najslabše kar lahko narediš. Očitno si s tem izgubila že kar nekaj časa in se ne boš nič navadila, če sedaj pustiš. Poleg tega imaš podatke lepo strukturirane, mašina lahko naredi točno, človek zgreši. V najslabšem primeru, če se ti zdijo navedene rešitve preveč komplicirane, lahko uporabiš kurzorje, kjer je logika podobna klasičnemu programiranju, greš skozi vse zapise in ni hudič, da ne bi naredilo: cursors
To je moje mnenje, vsak naredi po svoje... Aja, nisi napisala, zakaj si obupala:)

Vredno ogleda ...

TemaSporočilaOglediZadnje sporočilo
TemaSporočilaOglediZadnje sporočilo
»

[SQL] Pohitritev izpisa

Oddelek: Programiranje		252622 (1521) kuall
»

program za pretvorbo številk v besede

Oddelek: Pomoč in nasveti		233722 (2843) šebenik
»

S.p. kot študent

Oddelek: Loža		163953 (3193) fosil
»

[SQL] Unikatni izpisi

Oddelek: Programiranje		212054 (1441) 111111111111
»

mysql if stavek

Oddelek: Programiranje		91195 (809) MrStein

Več podobnih tem