Złożone zapytanie SQL Opcje

[markuz]

Witam,

Mam problem z dosyć złożonym zapytaniem SQL. Posiadam 3 tabele:

[SQL] pobierz, plaintext 
CREATE TABLE `lg_campaigns` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `created_at` DATETIME NOT NULL,
    `status` TINYINT(1) DEFAULT 0,
    `name` VARCHAR(100) NOT NULL,
    `campaign_type` TINYINT(1) NOT NULL,
    `content_type` TINYINT(1) NOT NULL,
    `url` VARCHAR(300) DEFAULT NULL,
    `html` TEXT DEFAULT NULL,
    PRIMARY KEY(`id`)
);
 
CREATE TABLE `lg_campaigns_geo_targets` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `campaign_id` INT NOT NULL,
    `country_id` INT NOT NULL,
    `unique` TINYINT(1) NOT NULL,
    `min_bid` FLOAT NOT NULL,
    `max_bid` FLOAT NOT NULL,
    `budget` FLOAT NOT NULL,
    `daily_budget` FLOAT NOT NULL DEFAULT 0,
    PRIMARY KEY(`id`)
);
 
CREATE TABLE `lg_campaigns_views` (
    `campaigns_geo_target_id` INT NOT NULL,
    `bid` FLOAT NOT NULL,
    `ip` VARCHAR(20) NOT NULL,
    `created_at` DATETIME NOT NULL,
    FOREIGN KEY (`campaigns_geo_target_id`) REFERENCES `lg_campaigns_geo_targets` (`id`)
);
[SQL] pobierz, plaintext 

Gdzie:
lg_campaigns - Kampanie reklamowe
lg_campaigns_geo_targets - Dostępne kraje dla danej kampanii reklamowej
lg_campaigns_views - Odsłony dla danego kraju z danej kampanii reklamowej

Chcę pobrać:
Kampanię reklamową (lg_campaigns) (Tylko 1 - LIMIT 1)
Gdzie:
lg_campaigns.status = 1 (Aktywna kampania)
lg_campaigns_geo_targets.country_id = X (Kraj użytkownika który wyświetla reklame)
Jeżeli lg_campaigns_geo_targets.daily_budget > 0 (Kampania posiada dziennie ograniczenie budżetu do wydania)
- Pobierana jest suma wydanych dzisiaj środków (lg_campaigns_views.bid) dla danego kraju kampanii (lg_campaigns_geo_targets)
Jeżeli ruch dla danego kraju kampanii jest unikalny (lg_campaigns_geo_targets.unique = 1) wtedy:
- Pobierana jest suma dzisiejszych wyświetleń (lg_campaigns_views) dla danego IP (lg_campaigns_views.ip) (Suma musi być = 0 - tzn. dzisiaj żaden użytkownik z tym IP nie wyświetlił tej reklamy).
- Sortowanie malejąco według lg_campaigns_geo_targets.max_bid (Czyli ta kampania która ma największą ofertę zostaje pobrana)

Problem tkwi głównie w tym:

[SQL] pobierz, plaintext 
            AND lg_campaigns_geo_targets.daily_budget < 
                IF(lg_campaigns_geo_targets.daily_budget > 0,
                    (SELECT SUM(lg_campaigns_views.bid) WHERE lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id FROM lg_campaigns_views),
                    1
                )
[SQL] pobierz, plaintext 

Nie wiem jak połączyć pobieranie sumy wydanych środków skoro nie wiem jeszcze jaką kampanie skrypt wybierze..


Ma ktoś pomysł jak to zrobić 1 zapytaniem?

Takie zapytanie też nie chce działać:

[SQL] pobierz, plaintext 
            SELECT lg_campaigns.*, (SELECT SUM(lg_campaigns_views.bid) FROM lg_campaigns_views WHERE lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id) AS `today_spend_budget` 
            FROM lg_campaigns 
            JOIN lg_campaigns_geo_targets ON lg_campaigns.id = lg_campaigns_geo_targets.campaign_id
            LEFT JOIN lg_campaigns_views ON lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id
            WHERE lg_campaigns.STATUS = 1
            AND lg_campaigns_geo_targets.country_id = 1
            AND IF(lg_campaigns_geo_targets.daily_budget > 0, `today_spend_budget` < lg_campaigns_geo_targets.daily_budget , TRUE)
            LIMIT 1
[SQL] pobierz, plaintext 

Błąd: Unknown column 'today_spend_budget' in 'where clause'
Czemu tak jest skoro zaznaczyłem `today_spend_budget` jako sumę wszystkich ofert (bid) (Pomijając już datę).

PS. nie mogłem modyfikować posta ze względu na jakiś błąd z takiem SQL. (Podmieniło czysty tekst na HTML przy kolorowaniu składni).

Ten post edytował markuz 25.03.2014, 09:49:25

[Crozin]

W klauzuli WHERE nie masz dostępu do kolumny today_spend_budget. Musisz przenieść ten warunek do klauzuli HAVING.

Do poczytania:
1. http://stackoverflow.com/questions/2905292/where-vs-having
2. http://dev.mysql.com/doc/refman/5.0/en/gro...extensions.html
3. https://dev.mysql.com/doc/refman/5.0/en/select.html - fragmenty dot. HAVING

[mmmmmmm]

[SQL] pobierz, plaintext 
(SELECT SUM(lg_campaigns_views.bid) WHERE lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id FROM lg_campaigns_views)
[SQL] pobierz, plaintext 

WTF??

[SQL] pobierz, plaintext 
(SELECT SUM(lg_campaigns_views.bid) FROM lg_campaigns_views WHERE lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id )
[SQL] pobierz, plaintext 

[markuz]

[SQL] pobierz, plaintext 
SELECT `lg_campaigns`.*,
`lg_campaigns_geo_targets`.`daily_budget`,
`lg_campaigns_geo_targets`.`unique`,
 
(SELECT SUM(`lg_campaigns_views`.`bid`) 
FROM `lg_campaigns_views` 
WHERE `lg_campaigns_views`.`campaigns_geo_target_id` = `lg_campaigns_geo_targets`.`id` 
AND `lg_campaigns_views`.`created_at` > DAY(CURRENT_TIMESTAMP)
) AS `today_budget`,
 
(SELECT COUNT(*) 
FROM `lg_campaigns_views` 
WHERE `lg_campaigns_views`.`ip` = '192.168.1.16'
AND `lg_campaigns_views`.`campaigns_geo_target_id` = `lg_campaigns_geo_targets`.`id`
AND `lg_campaigns_views`.`created_at` > DAY(CURRENT_TIMESTAMP)
) AS `today_views`
 
FROM `lg_campaigns`
 
JOIN `lg_campaigns_geo_targets` ON (`lg_campaigns_geo_targets`.`campaign_id` = `lg_campaigns`.`id`)
 
WHERE `lg_campaigns`.`status` = 1
AND `lg_campaigns_geo_targets`.`country_id` = 1
 
HAVING IF(`lg_campaigns_geo_targets`.`daily_budget` > 0, `lg_campaigns_geo_targets`.`daily_budget` > `today_budget`, TRUE)
AND IF(`lg_campaigns_geo_targets`.`unique` = 1, `today_views` = 0, TRUE)
 
ORDER BY `lg_campaigns_geo_targets`.`max_bid` DESC
 
LIMIT 1
[SQL] pobierz, plaintext 

Dzięki za podpowiedź.
Skleiłem coś takiego, wydaje mi się, że działa. Jednak mam jeszcze za mało danych w tabelach aby poprawnie to zapytanie przetestować.
Gdyby ktoś chciał się podzielić opinia na temat powyższego zapytania chętnie jej wysłucham