Autor Zpráva
Petr Ká
Profil
Ahoj,

potřeboval bych poradit s dotazem pro vhodný výběr reklamy. Bohužel SqlFidle tento dotaz nezpracuje, tak to aspon zkusim.

Prvně popíšu co nyní mám:
- sub-selectem si vyberu ID velikosti z tabulky ADS_SIZES (největší možnou reklamu dle dané proměnné - na příkladu je 640x120px, která zároveň existuje v tabulce ADS)
- dále si teda z ADS vyberu jen ty reklamy, které mají danou velikost (pozor, tady znova používám absolutně stejné podmíny - jde to optimalizovat???)
- Podle náhodně vygenerovaného čísla (0 - 100 - na příkladu je 21) se rozhodnu, zda si vyberu prvních 15 dle nejlepsí nabídky (BID), nebo nejvyššího rozpočtu na kampaň (FLOOR(BALANCE/BID) AS usages) a nebo zbytek náhodně (aby se zobrazovali i ostatní)
- Těhle 15 kusů poté seřadím náhodně a vezmi si jen jeden, který zobrazím

Co potřebuji?
1) Pokud to jde, dotaz zoptimalizovat - nebráním se i kompletnímu přepracování, ale nenapadá mě nic lepšího. Testováno na cca 4000 záznamech a v pohodě
2) Přidat vazbu do tabulky ADS_TARGETING, kde budou řádky AD_ID[vazba na ADS.ID], TYPE [enum "GEO","HOURS_FROM","HOUR_TO"] a VALUE (hodnota TYPE [např. GEO=CZ a HOURS_FROM=18 a HOURS_TO=21 znamená, že se reklama zobrazí jen v ČR v časech mezi 18-21 hodinou. Pokud nebude záznam např. GEO, tak nefiltrovat]).
Lámu si s tím hlavu a fakt nevím :-)

SQL: TADY
DB SCHEMA: TADY

EDIT: Adminum se omlouvam za tisíc úprav, ale když jsem dal do CODE celý SQL dotaz a do druhého CODE SQL SCHEMA, tak se to celé rozbouralo :)


EDTI2: Jestě jsem nedodal, že ADS a ADS_TARGETING je 1:N... Ale to je ze schema asi patrné :)
Petr Ká
Profil
EDIT3: Je možné, že by to fungovalo TAKHLE ?

Stejně se mi tam nelíbí ten milion podselectů a opakující se podmínky
TomášK
Profil
ORDER BY RAND() může ten dotaz zpomalit i několikanásobně, navíc to zřejmě není úplně náhodné.

Edit: koukám, že je jen na 15 záznamech, tam je to asi jedno
Edit2: je to i uvnitř dotazu
Petr Ká
Profil
TomášK:
Nad těma sloupcema mám index, dočetl jsem se, že to nějak zásadně nezpomaluje. Spíš mě mrazí po zádech z těch podmínek a milionu subselectů...
BTW: otestováno a funkční, kdyby náhodou někdy něco takovýho potřeboval.
TomášK
Profil
V tom odkazu, který jsi poslal jsou dva příklady - jeden trvá 300 ms, druhý 1200 ms. To, co používáš, je analogické tomu 1200 ms, neuniká-li mi něco.
Petr Ká
Profil
TomášK:
Jo, taky jsem si toho pak všiml, už jsem byl hodně unavenej... Ostatně zpět k tomu dotazu, napadá Tě, jak by jej šlo zjednodušit?
CZechBoY
Profil
Ten CASE bych asi vyhodil, šlo by to přepsat pomocí OR.
a.end_date IS NULL OR a.end_date >= NOW()
analogicky dole.
Nevím jestli to nějak zrychlí dotaz, ale určitě zjednoduší.
Petr Ká
Profil
CZechBoY:
Nemyslím, ze by to byla nejaka zatez, ve finale to provede to samé, ale rád bych se zbavil ted opakujících se JOINů atd
TomášK
Profil
Nevidím, jak se zbavit těch JOINů bez analytických funkcí, které do MySQL ještě nedorazily. S tabulkou ads_targeting se špatně pracuje, prootože není vhodně navržená (Entity–attribute–value). Rozdělíš tu tabulku podle enumu a pak v každém selectu vybíráš jednu konkrétní hodnotu a přetypováváš. Změnil bych ji, asi na tři tabulky.
Petr Ká
Profil
TomášK:
Nyní to mám pořešené tak, že jsem to rozdelil na 2 dotazy. Prvně si vytáhnu ty targeting ID a až pak pokládám ten dotaz. Tedy zbavil jsem se jednoho subselectu s 3mi joiny.
Kajman
Profil
Petr Ká:

1) Pokud je to v pohodě, tak bych opakování neřešil pokud to v dotaze databázi pomůže.

2) Asi bych zkusil dělat 3x left join na porušení omezení a ve where pak vybral ty, kde se provázání nepovedlo (tedy není žádná podmínka, nebo je splněna)

V tabulce ADS_TARGETING zvažte více sloupců pro value podle typu (value_string, value_number, value_time). Případně ji nedělat univerzální a mít zemi a časy jako sloupce - pak stačí jeden left join na hledání porušení.
CZechBoY
Profil
A nemůžeš prostě tu podmínku where se subselectem přepsat jako join? Nebo použít MAX? Mně připadá, že dělá úplně to stejný dvakrát zbytečně...
Nešlo by to takhle?
SELECT final.*
FROM (
        SELECT
            MAX(s.x + s.y), s.id,
            a.id, a.is_image, a.data, a.pricing, a.bid, a.balance, a.user_id,
            s.x, s.y,
            FLOOR(a.balance / a.bid) AS usages, 21 AS rand_number
        FROM ads a
        INNER JOIN ads_sizes s ON s.id      = a.size_id
        INNER JOIN users u     ON a.user_id = u.user_id
        WHERE
            a.adult        = 0         AND
            a.bid         <= a.balance AND
            u.active       = 1         AND
            u.confirmed    = 1         AND
            a.active       = 1         AND
            a.start_date  <= NOW()     AND
            CASE
                WHEN a.end_date IS NOT NULL
                    THEN a.end_date >= NOW()
                ELSE TRUE
            END AND
        ORDER BY
            CASE
                WHEN rand_number >  66 THEN bid
                WHEN rand_number >= 33 THEN usages
                ELSE RAND()
            END
            DESC
        LIMIT 15
) AS final
ORDER BY RAND()
LIMIT 1
Petr Ká
Profil
CZechBoY:
Takhle to nelze, protože musím vybrat prvně nejvíce vyhovující velikost reklamy - která zároveň má nějaké reklamy splnujici podminky, proto 2x stejne podminky...
CZechBoY
Profil
A když bys ty 2 podmínky dal do WHERE?
Petr Ká
Profil
Kajman:
k ad 2) Nemyslíme oba to samé, což mám?
CASE WHEN COUNT(b.id)>0 THEN (SELECT COUNT(bb.id) FROM ads_targeting bb WHERE bb.ad_id=a.id AND bb.type='GEO' AND bb.value='EN') ELSE -1 END AS geo_match
.
.

WHERE sub_final.geo_match <> 0 AND sub_final.hour_from_match <> 0 AND sub_final.hour_to_match <> 0



EDIT: Strukturu je možné ještě přepracovat, jedná se o nový projekt, rád se nechám poučit :)
Kajman
Profil
Měl jsem na mysli kratší zápis bez poddotazu
...
left join ads_targeting bb WHERE bb.ad_id=aa.id AND bb.type='GEO' AND bb.value!='EN'

..
where bb.ad_id is null

Jestli bude rychlejší, je potřeba vyzkoušet. Ono != nebývá rychlé, ale třeba na ty časy se negace na rychlost neprojeví a kontrolují se omezení jen pro vybrazné inzeráty (jsou aktivní atp.)

Edit. ale pokud by k jednomu inzerátu byly dvě GEO hodnoty - třeba CZ a SK, tak by se při tomto řešení nezobrazil nikde
Petr Ká
Profil
Kajman:
Edit. ale pokud by k jednomu inzerátu byly dvě GEO hodnoty - třeba CZ a SK, tak by se při tomto řešení nezobrazil nikde
To je právě ten důvod, proč to mám takhle... Inzerent si bude moct zvolit oblast (nebo více). Proto ty hodnoty -1 (nefiltrováno), 0 (filtrováno, ale nevyhovuje) a COUNT( > 0 vyhovuje geocílení)
Petr Ká
Profil
Takže jsem to kompletně přepsal a na 1000 dotazů je průměr doby zpracování 0.00339 sekundy (tabulka ADS má 4500 záznamů).

Výsledné SQL:
SELECT tmp.* FROM (
    SELECT 
           ad.id AS ad_id, ad.is_image,ad.adult, ad.target_url, ad.data,
           IFNULL(adt_geo.value,'NO_LIMIT') AS geo_limit_val, 
           IFNULL(adt_hour_start.value,'NO_LIMIT') AS hour_from_limit_val, 
           IFNULL(adt_hour_to.value,'NO_LIMIT') AS hour_to_limit_val, 
           ads.x AS size_x,ads.y AS size_y, (ABS(600*2 - ads.x*2)+ABS(500 - ads.y)) AS size_diff,
           ad.pricing,ad.bid, ROUND(ad.bid/max_bid.mbid*100/20)*20 AS bid_percent,max_bid.mbid,ad.balance, FLOOR(ad.balance/ad.bid) AS usages, 
           add_rnd.random
    FROM ads ad
      LEFT JOIN ads_targeting adt_geo        ON adt_geo.ad_id=ad.id AND adt_geo.type = 'GEO'
      LEFT JOIN ads_targeting adt_hour_start ON adt_hour_start.ad_id=ad.id AND adt_hour_start.type = 'HOURS_FROM'
      LEFT JOIN ads_targeting adt_hour_to    ON adt_hour_to.ad_id=ad.id AND adt_hour_to.type = 'HOURS_TO'
      INNER JOIN ads_sizes ads               ON ads.id=ad.size_id
      INNER JOIN users u                     ON ad.user_id=ad.user_id AND u.active=1 AND u.confirmed=1
      INNER JOIN (SELECT ROUND(RAND()*100) AS random) add_rnd
      INNER JOIN (SELECT MAX(bid) AS mbid FROM ads)   max_bid
    WHERE 
          ad.active = 1 AND
          ad.bid > 0 AND
          ad.balance > 0 AND
          ad.start_date <= NOW() AND
          ( ad.end_date IS NULL OR ad.end_date >= NOW() ) AND
          ads.x <= 600 AND 
          ads.y <= 500 
          
    GROUP BY 
          ad.id, adt_geo.id, adt_hour_start.id, adt_hour_to.id

    HAVING
          ( geo_limit_val = 'NO_LIMIT' OR geo_limit_val = 'CZ' ) AND
          ( hour_from_limit_val = 'NO_LIMIT' OR hour_from_limit_val >= HOUR(NOW()) ) AND
          ( hour_to_limit_val = 'NO_LIMIT' OR hour_to_limit_val >= HOUR(NOW()) ) 
    
    ORDER BY  
          size_diff ASC,      
          CASE WHEN add_rnd.random > 66 THEN ABS(size_diff-bid_percent)
            WHEN add_rnd.random >= 33 THEN ABS(size_diff-usages)
            ELSE RAND()
          END ASC
    LIMIT 30
) AS tmp 
ORDER BY RAND()
LIMIT 1
Kajman
Profil
Petr Ká:
A jak se zadává do tabulek, když má být reklama zobrazována od 23:00 do 01:00?

Myslím, že to nebude fungovat korektně (ať to jsou dva řádky, nebo čtyři (23,23:59:9,0,1)). Myslím, že by byla lepší tabulka se sloupci (geo, from, to). Nevidím výhodu to ukládat omezení jako nezávislé řádky. Pokud se tam přidá nový typ omezení, tak stejně musíte upravit vyhledávací dotazy, proto mi nepřijde horší řešení přidat případně sloupec.

Ještě mě napadá, že by mohli zákazníci raději omezovat čas podle času klienta a ne serveru. V případě omezení na straně serveru se o to více může hodit mít závislé omezení v jednom řádku
CZ   23   23:59:59
CZ    0    1
US    9   11
null 22   null
CH   null null

A v současném řešení mi přijde, že zvyšováním počtu splněných podmínek se u reklamy zvyšuje šance, že bude vybrána v těch ostatních (<33).
Petr Ká
Profil
Kajman:
A jak se zadává do tabulek, když má být reklama zobrazována od 23:00 do 01:00?
To je dobrá poznámka... teď to vlastně je tak, ze se zada jen jeden intarval od-do, ale pres pulnoc to neresi... ajaj...

Nevidím výhodu to ukládat omezení jako nezávislé řádky
A jak omezím geolokaci třeba na 10 států tedy? Leda pro každý stát nastavit / nenastavit časový interval... Ale zase, co když těch intervalů bude vícero ?

Pokud se tam přidá nový typ omezení
Tam už mám v plánu ještě kategorizaci webů dle zaměření

Ještě mě napadá, že by mohli zákazníci raději omezovat čas podle času klienta
Opět dobrá poznámka

Otázkou je, co teď. Překopat to?
Kajman
Profil
Asi bych udělal tu tabulku omezeni se všemi sloupci, co to mohou omezovat. Pokud bude hodnota null, tak se neomezuje. Jinak musí platit všechny nenullové omezení. Stačí když omezení bude vyhovovat jeden řádek. Pro deset států tedy bude např. 10 řádků, kde bude vyplněné jen geo a další omezení budou null.

V dotaze by pak šlo něco jako

left join ads_targeting ex on ex.ad_id=ad.id
left join ads_targeting ok on ex.adt_id=ok.adt_id
                              and (ok.geo is null or ok.geo='UK')
                              and (ok.lang is null or ok.lang='en')
                              and (ok.hour_start_server is null or ok.hour_start_server<=hour(now()))
                              and (ok.hour_to_server is null or ok.hour_to_server>=hour(now()))
...
where (ex.adt_id is null or ok.adt_id is not null) # neni vubec zadna, nebo je alespon jedna splnena
...
group by ad.id ...


(adt_id je primární klíč ads_targeting)
Petr Ká
Profil
Kajman:
To zní hezky, zítra to otestuju, ted musim pryc a napisu sem, jak to dopadlo na datech ;) Dekuju!

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: