Pagalbos su SQL

Sveiki,

turiu tokią SQL:

SELECT COUNT(*) AS total, CONCAT(YEAR(sms_date), '/', WEEK(sms_date)) AS week_numb FROM smsPay WHERE sms_date >= DATE_SUB(NOW(), INTERVAL 12 WEEK) GROUP BY week_numb ORDER BY week_numb ASC

Neįsivaizduoju, kaip padaryti, kad jeigu tą savaitę nėra jokio įrašo, tai total išvestų 0. Dabar jeigu tą savaitę nieko nėra, tai visai nieko ir negrąžina - tiesiog praleidžia...

Taip atrodo ką mysql gražina dabar:

total week_numb

1 2013/50

1 2013/51

1 2013/52

2 2014/0

5 2014/2

3 2014/5

1 2014/9

O man reikia, kad gražintų 12 pastarųjų savaičių maždaug taip:

total week_numb

<...>

5 2014/2

0 2014/3

0 2014/4

3 2014/5

0 2014/6

0 2014/7

0 2014/8

1 2014/9

Ačiū.

Kiek žinau, tokias problemas sprendžia susidarius atskirą lentelę (gali pabandyti paieškoti internete „SQL calendar table“). Toje lentelėje išsaugai tau visas galimas reikšmes: visus "YYYY/W" nuo minimalios datos iki maksimalios. Pavadinkim šitą lentelę calendar.

Tada tiesiog kai gauni savo duomenis, juos dar LEFT/RIGHT JOIN'ini su calendar lentele, patikrini reikšmes, kurios lygios NULL ir joms priskiri 0 su IFNULL() funkcija.

Nelabai suprantu kaip įdėti LEFT JOIN į tą mano užklausą. Susikūriau lentelę Calendar su YYYY/W reikšmėmis, bet kaip tą LEFT JOIN ON padaryti tai nedašunta, nes SMS lentelėje turiu tik datą ir id...

Jeigu paimam tą tavo ilgą užklausą ir susikuriam virtualią lentelę iš jos, tai turėtų atrodyti daugmaž taip:

SELECT calendar.week_numb, IFNULL(total, 0) AS total FROM calendar LEFT JOIN ({tavo ilga užklausa eina čia}) AS smsPayData ON calendar.week_numb = smsPayData.week_numb WHERE calendar.week_numb >= MIN(smsPayData.week_numb) AND calendar.week_numb <= MAX(smsPayData.week_numb)

Idėja daugmaž tokia, tik labai tikėtina, kad kažkur klaidų įvėliau (pavyzdžiui, nežinau ar korektiška rašyti „IFNULL(total, 0) AS total“). Klaidas, tikiuosi, sugebėsi išsitaisyti pats.

Dabar su WHERE problemos... smsPayData.week_numb yra varchar(7) [2014/10] ir neina palygint su daugiau/mažiau...

Tada gal geriau saugoti kalendoriaus lentelėj SQL datą iš kiekvienos savaitės tam tikros dienos (tarkim, kiekvieno pirmadienio). Tada prieš pateikdamas į LEFT JOIN galėsi atsifiltruoti reikiamas ir susidaryti savaičių string'us.

SELECT calendar.week_numb, IFNULL(total, 0) AS total FROM (SELECT CONCAT({parametrai}) as week_numb FROM calensdar WHERE date >= DATE_SUB(NOW(), INTERVAL 12 WEEK) AND date <= NOW()) AS calendar LEFT JOIN ({tavo ilga užklausa eina čia}) AS smsPayData ON calendar.week_numb = smsPayData.week_numb

Dabar iš calendar lentelės paims tik tas datas, kurios reikalingos tavo intervalui.

Tada gal geriau saugoti kalendoriaus lentelėj SQL datą iš kiekvienos savaitės tam tikros dienos (tarkim, kiekvieno pirmadienio). Tada prieš pateikdamas į LEFT JOIN galėsi atsifiltruoti reikiamas ir susidaryti savaičių string'us.

 

SELECT calendar.week_numb, IFNULL(total, 0) AS total FROM (SELECT CONCAT({parametrai}) as week_numb FROM calensdar WHERE date >= DATE_SUB(NOW(), INTERVAL 12 WEEK) AND date <= NOW()) AS calendar LEFT JOIN ({tavo ilga užklausa eina čia}) AS smsPayData ON calendar.week_numb = smsPayData.week_numb

 

Dabar iš calendar lentelės paims tik tas datas, kurios reikalingos tavo intervalui.

Vistiek nesigauna... Mistika man čia su tokia užklausa :D pasiklystu aš joje...

---

Edit:

ohhh... Pagaliau susitvarkiau.

Jeigu kam nors ateity prireiks kažko panašaus, tai mano užklausa gavosi tokia:

SELECT 
calendar.week_numb, 
IFNULL(total, 0) AS total 
FROM (
SELECT 
	CONCAT(YEAR(dt), '/', WEEK(dt)) as week_numb,
	dt
FROM calendar 
WHERE dt >= DATE_SUB(NOW(), INTERVAL 12 WEEK) AND dt <= NOW()
) AS calendar 
LEFT JOIN (
SELECT 
	COUNT(*) AS total, 
	CONCAT(YEAR(sms_date), '/', WEEK(sms_date)) AS week_numb 
FROM newspapersPurchases 
WHERE sms_date >= DATE_SUB(NOW(), INTERVAL 12 WEEK) 
GROUP BY week_numb 
ORDER BY week_numb ASC
) AS smsPayData ON calendar.week_numb = smsPayData.week_numb
GROUP BY calendar.week_numb
ORDER BY calendar.dt

calendar lentelę kūriau pagal šią nuorodą: Mano nuoroda

smsPayData.week_numb

skaicius saugai, o nustates varchar, pakeisk i tam tinkama formata

skaicius saugai, o nustates varchar, pakeisk i tam tinkama formata

week_numb dabar duombazėje nėra saugomas, jį su užklausa pasidarau ;)

CONCAT(YEAR(dt), '/', WEEK(dt)) as week_numb

ir

CONCAT(YEAR(sms_date), '/', WEEK(sms_date)) AS week_numb