Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Złożone zapytanie SQL
Forum PHP.pl > Forum > Bazy danych > MySQL
markuz
Witam,

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

  1. CREATE TABLE `lg_campaigns` (
  2. `id` INT NOT NULL AUTO_INCREMENT,
  3. `user_id` INT NOT NULL,
  4. `created_at` DATETIME NOT NULL,
  5. `status` TINYINT(1) DEFAULT 0,
  6. `name` VARCHAR(100) NOT NULL,
  7. `campaign_type` TINYINT(1) NOT NULL,
  8. `content_type` TINYINT(1) NOT NULL,
  9. `url` VARCHAR(300) DEFAULT NULL,
  10. `html` TEXT DEFAULT NULL,
  11. PRIMARY KEY(`id`)
  12. );
  13.  
  14. CREATE TABLE `lg_campaigns_geo_targets` (
  15. `id` INT NOT NULL AUTO_INCREMENT,
  16. `campaign_id` INT NOT NULL,
  17. `country_id` INT NOT NULL,
  18. `unique` TINYINT(1) NOT NULL,
  19. `min_bid` FLOAT NOT NULL,
  20. `max_bid` FLOAT NOT NULL,
  21. `budget` FLOAT NOT NULL,
  22. `daily_budget` FLOAT NOT NULL DEFAULT 0,
  23. PRIMARY KEY(`id`)
  24. );
  25.  
  26. CREATE TABLE `lg_campaigns_views` (
  27. `campaigns_geo_target_id` INT NOT NULL,
  28. `bid` FLOAT NOT NULL,
  29. `ip` VARCHAR(20) NOT NULL,
  30. `created_at` DATETIME NOT NULL,
  31. FOREIGN KEY (`campaigns_geo_target_id`) REFERENCES `lg_campaigns_geo_targets` (`id`)
  32. );


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:
  1. AND lg_campaigns_geo_targets.daily_budget <
  2. IF(lg_campaigns_geo_targets.daily_budget > 0,
  3. (SELECT SUM(lg_campaigns_views.bid) WHERE lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id FROM lg_campaigns_views),
  4. 1
  5. )


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? smile.gif

Takie zapytanie też nie chce działać:
  1. 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`
  2. FROM lg_campaigns
  3. JOIN lg_campaigns_geo_targets ON lg_campaigns.id = lg_campaigns_geo_targets.campaign_id
  4. LEFT JOIN lg_campaigns_views ON lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id
  5. WHERE lg_campaigns.STATUS = 1
  6. AND lg_campaigns_geo_targets.country_id = 1
  7. AND IF(lg_campaigns_geo_targets.daily_budget > 0, `today_spend_budget` < lg_campaigns_geo_targets.daily_budget , TRUE)
  8. LIMIT 1

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).
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
  1. (SELECT SUM(lg_campaigns_views.bid) WHERE lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id FROM lg_campaigns_views)

WTF??
  1. (SELECT SUM(lg_campaigns_views.bid) FROM lg_campaigns_views WHERE lg_campaigns_geo_targets.id = lg_campaigns_views.campaigns_geo_target_id )
markuz
  1. SELECT `lg_campaigns`.*,
  2. `lg_campaigns_geo_targets`.`daily_budget`,
  3. `lg_campaigns_geo_targets`.`unique`,
  4.  
  5. (SELECT SUM(`lg_campaigns_views`.`bid`)
  6. FROM `lg_campaigns_views`
  7. WHERE `lg_campaigns_views`.`campaigns_geo_target_id` = `lg_campaigns_geo_targets`.`id`
  8. AND `lg_campaigns_views`.`created_at` > DAY(CURRENT_TIMESTAMP)
  9. ) AS `today_budget`,
  10.  
  11. (SELECT COUNT(*)
  12. FROM `lg_campaigns_views`
  13. WHERE `lg_campaigns_views`.`ip` = '192.168.1.16'
  14. AND `lg_campaigns_views`.`campaigns_geo_target_id` = `lg_campaigns_geo_targets`.`id`
  15. AND `lg_campaigns_views`.`created_at` > DAY(CURRENT_TIMESTAMP)
  16. ) AS `today_views`
  17.  
  18. FROM `lg_campaigns`
  19.  
  20. JOIN `lg_campaigns_geo_targets` ON (`lg_campaigns_geo_targets`.`campaign_id` = `lg_campaigns`.`id`)
  21.  
  22. WHERE `lg_campaigns`.`status` = 1
  23. AND `lg_campaigns_geo_targets`.`country_id` = 1
  24.  
  25. HAVING IF(`lg_campaigns_geo_targets`.`daily_budget` > 0, `lg_campaigns_geo_targets`.`daily_budget` > `today_budget`, TRUE)
  26. AND IF(`lg_campaigns_geo_targets`.`unique` = 1, `today_views` = 0, TRUE)
  27.  
  28. ORDER BY `lg_campaigns_geo_targets`.`max_bid` DESC
  29.  
  30. LIMIT 1


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 smile.gif
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.