ugrás a tartalomhoz

SQL nehéz lekérés

Radon · 2014. Jan. 16. (Cs), 19.11
A felhasználók között szeretnék úgy keresni, hogy azokat találja, akikkel közösek a céljaink.
Táblák:
- users (id,name)
- goals (célok) (-id,name)
- user_goals (id, user_id,goal_id)

Felveszünk felhasználókat, és célokat, a felhasználók pedig jelölgetnek maguknak célokat. Mindenkinek különböző fajta és mennyiségű céljai vannak.
Azokat keresem, akiknek hasonló (legalább 1 megfelelés) céljai vannak, mint nekem.
‹ #1064 - You have an error in your SQL syntax Kaszkádolt mező ›
 ■
1

MySQL?

Vilmos · 2014. Jan. 16. (Cs), 20.35
Egy felhasználóhoz több cél tartozhat. Szóval 1-több kapcsolatról beszélünk. Attól függ a lekérdezés, hogy a felhasználó azonosítóján kívül mire van még szükséged.

Ha a felhasználó felől nézzük, akkor összekapcsolandó két tábla. Az eredmény azonban szorzat lenne, ezért az egyedi eseteket szűrni kell a "distinct" függvénnyel:
SELECT DISTINCT( users.id ) FROM users,goals
WHERE users.id = goals.user_id

Ha van találat, akkor legalább egy célja van a felhasználónak.

Ez a másik minta arra lenne példa, ha egyrészt elég csak egy-egy "id", másrészt célok száma szerint is szűrni akarsz ( mondjuk az érdekes akinek több mint 1 célja van ):
SELECT * FROM (
  SELECT user_id, count( goal_id ) AS darab FROM user_goals
  GROUP BY user_id ) AS tmp
WHERE darab > 1

Valamiért a MySQL-nél nem lehet közvetlenül lekérdezni az ilyesmit. Összetett "select" kell hozzá.
2

Jo

Radon · 2014. Jan. 16. (Cs), 21.38
Ez egy eleg jo lekérés, megjegyzem, jol fog jönni. De nekem nem azok kellenek, akiknek több célja van, hanem azok, akiknek ugyan olyan céljai vannak, mint nekem.
Tehát ha vannak 1 , 2, 3, 4, 5, 6, 7, 8 as célok.
Nekem van 3, 4, 5, ös cél, akkor olyat keresek,
Akinek van legalább 3as, 4es, vagy 5 ös célja,
Vagy max mind a 8 db célja, akkor is megtalálja, mert benne az én 3 fajta célom.
Amolyan legkisebb közös többszörös keresése.
Ja, és usersből elég az id. A while ciklusban majd ujrakérem a többi adatot.
6

Nagyon remélem, hogy abban a

bamegakapa · 2014. Jan. 16. (Cs), 23.27
Nagyon remélem, hogy abban a bizonyos while ciklusban nem megannyi lekérést akarsz még futtatni (júzerenként egyet), mert az iszonyatos erőforráspazarlás lenne.
8

jaj

Radon · 2014. Jan. 17. (P), 00.28
Hát sajnos elég gyakran alkalmazom.
pl a felhasználónak csak a nevére csináltam 1 függvényt, és
ahol user_id van, csak beszúrom ezt a függvényt és átláthatóbb.
Sokszor ez ciklusba esik.
Akkor ezek szerint ezt nem kellene:)
3

Erre gondoltál?

mahoo · 2014. Jan. 16. (Cs), 22.25
Erre gondoltál?
SELECT u.name FROM users u INNER JOIN user_goals ug ON u.id = ug.user_id WHERE goal_id IN (sajat_goal_id_1,sajat_goal_id_2, stb.) AND user_id <> sajat_id
4

Mindjárt kipróbálom, ez mit

Radon · 2014. Jan. 16. (Cs), 22.40
Mindjárt kipróbálom,
ez mit jelent: user_id <> sajat_id ?
Ezt jelenti: user_id != sajat_id ?
5

igen

mahoo · 2014. Jan. 16. (Cs), 22.48
igen
7

Kicsit továbbgondolva:SELECT

bamegakapa · 2014. Jan. 16. (Cs), 23.42
Kicsit továbbgondolva:
SELECT u.*
    FROM users u 
    JOIN user_goals ug ON u.id = ug.user_id
    WHERE goal_id IN (SELECT goal_id FROM user_goals WHERE user_id = 1) 
        AND user_id <> 1
    GROUP BY user_id
SQLFiddle

Nem tudom ez mennyire optimális, kicsit megkoptak az SQL szkilljeim az utóbbi időben :). Megfelelő indexekkel jónak kéne lennie.
9

nem jo.

Radon · 2014. Jan. 17. (P), 00.39
Ez a lekérésem:
"SELECT DISTINCT users.id
FROM users 
INNER JOIN achi_members ON
users.id = achi_members.user_id 
WHERE achi_members.achi_id IN 
(SELECT DISTINCT achi_id FROM achi_members WHERE user_id=".$_SESSION['user_id'].") 
AND users.id!=".$_SESSION['user_id'];
Az én céljaim: 6,7,8,9,12,13,14,15,22,23
A user célja: 16

Nincs egyezés, mégis kidobja az azonosítóját.
10

én postgresql-t használok de ez szabvány sql utasításokkal megol

wias · 2014. Jan. 17. (P), 05.57
A goal táblára nincs szükség pusztán a user_goals táblában azokat a user_id-kat kell lekérdezni amelyeknél szerepel a saját felhasználódhoz hozzárendelt goal_id, majd
az itt megkapott user_id-kat lekérdezed a users táblából, kizárva természetesen a saját
felhasználódat (nem tudom ez szükséges-e), ezt persze a users tábla where záradékában is meg lehet tenni.


select users.id,users.name from users
where users.id in (select user_id from user_goals where goal_id in
(select goal_id from user_goals where user_id = a_te_felhasznalo_id_d)
and user_goals.user_id <> a_te_felhasznalo_id_d)
11

Elkelne egy kis pontosítás ...

Vilmos · 2014. Jan. 17. (P), 09.35
A nyitó kérdésben azt írod, legalább egy megfeleltetés legyen a célok között, a 2. hozzászólásban pedig azt, hogy az én céljaim legyenek részhalmazai bárki máséinak.
A két fogalmazás között jelentős különbség van.
Melyiket akarod lekérdezni, egy tetszőleges elemszámú metszetet, vagy teljes részhalmazt?

Utóbbi az egyszerűbb.
Kezdetnek itt ez a lekérdezés, többet ad vissza mint kell, viszont ellenőrzésre pont jó:
SELECT * FROM user_goals,
            ( SELECT * FROM user_goals WHERE user_id = 'ÉN') AS tmp
WHERE user_goals.user_id <> 'ÉN' and user_goals.goal_id = tmp.goal_id
ORDER BY user_goals.user_id, user_goals.goal_id

Bővítve, már csak a keresettek azonosítóját kapod meg:
SELECT DISTINCT( user_goals.user_id ) FROM user_goals,
            ( SELECT * FROM user_goals WHERE user_id = 'ÉN') AS tmp
WHERE user_goals.user_id <> 'ÉN' and user_goals.goal_id = tmp.goal_id
ORDER BY user_goals.user_id

Természetesen nincs olyan táblám mint neked, hasonlón azért kipróbáltam a mintákat.
12

én céljaim legyenek részhalmazai bárki máséinak.

Radon · 2014. Jan. 17. (P), 09.54
én céljaim legyenek részhalmazai bárki máséinak.

Nagyon jól megfogalmaztad, ezt akartam írni:)
Köszi szépen mindegyik verzió végre működik. Mindet kipróbáltam.

A lekéréssel csak az a gond, hogy ha én 1 gyakori célt bejelölök, ami megvan mindenki másnak, a lekérés igazábol nem ér semmit.
Ha még lehetne bonyolítani a lekérést, azt hogy tudom lekérdezni, hogy nem legalább 1 célom legyen részhalmaza bárki másnak, hanem a legtöbb.
Nyilván arra az emberre vagyok kiváncsi, akivel a legtöbb közös célunk van.
Az már tényleg lehetetlen, hogy ráadásul eszerint rakja sorrendbe:)
14

Jogos

Vilmos · 2014. Jan. 17. (P), 10.53
Elírtam a hozzászólást, az első eset az egyszerűbb. Vagyis legalább 1 közös célú felhasználókat találtam meg.

A teljes részhalmazra még nincs ötletem.
13

kb

szabo.b.gabor · 2014. Jan. 17. (P), 10.06
alap verzió, szerintem ez jó kellene legyen
SELECT DISTINCT ug2.id
FROM user_goals AS ug1 INNER JOIN user_goals AS ug2
ON ug1.user_id = sajat_user_id AND 
ug1.goal_id = ug2.goal_id AND 
ug1.user_id <> ug2.user_id
kicsit fejlettebb, elvileg csinálható valami ilyesmi is
SELECT ug2.id,COUNT(*) AS hasonlosag
FROM user_goals AS ug1 INNER JOIN user_goals AS ug2
ON ug1.user_id = sajat_user_id AND 
ug1.goal_id = ug2.goal_id AND 
ug1.user_id <> ug2.user_id
GROUP BY ug2.id
ORDER BY hasonlosag DESC
azt nem értem, hogy az alulvonások miért nem látszanak..

SELECT DISTINCT ug2.id
FROM user_goals AS ug1 INNER JOIN user_goals AS ug2
ON ug1.user_id = sajat_user_id AND
ug1.goal_id = ug2.goal_id AND
ug1.user_id <> ug2.user_id
15

Ez már valami hasonló ...

Vilmos · 2014. Jan. 17. (P), 11.08
Amennyiben mégsem kell csak és kizárólag a teljes egyezés, akkor kiszűrheted találatok száma szerint is a megfelelőket.
Ahogy "szabo.b.gabor" utalt rá, a "COUNT()" függvény segíthet neked.

SELECT user_goals.user_id, COUNT( user_goals.user_id )
FROM user_goals,
   ( SELECT * FROM user_goals WHERE user_id = 'ÉN') AS tmp
WHERE user_goals.user_id <> 'ÉN' and user_goals.goal_id = tmp.goal_id
GROUP BY user_goals.user_id
16

miért kell beágyazott

szabo.b.gabor · 2014. Jan. 17. (P), 11.35
miért kell beágyazott lekérdezés?
17

Miért beágyazott?

Vilmos · 2014. Jan. 17. (P), 15.09
Biztos lehet másképp, de így átlátom.

Személyes preferencia, vagy korlát, ahogy tetszik:

- Ugyanazon tábla elemeivel végzünk műveletet, olyan módon mintha két tábláról lenne szó. A beágyazott "SELECT" a második táblát szimulálja.

- A "JOIN" alapú szintaxis számomra idegen, ilyet csak legvégső esetben írok le. Mondjuk akkor, ha kapcsolatból kimaradó elemeket keresek a "LEFT JOIN"-nal.
18

nem hiszem, hogy teljesítmény

szabo.b.gabor · 2014. Jan. 17. (P), 16.00
nem hiszem, hogy teljesítmény szempontjából jobb a beágyazott lekérdezés..
19

Hit

Vilmos · 2014. Jan. 17. (P), 16.23
Az már a kérdező gondja.

Bár, ha foglalkozni akarsz ezzel, vagy van valami tapasztalatod, akkor írd meg.
Hit alapon nem érdemes, az nem bizonyíték alapú konstrukció.
20

Beágyazott lekérdezés

Hidvégi Gábor · 2014. Jan. 17. (P), 16.39
MySQL-ben valóban érdemes kerülni (minél több adat van a táblában, annál inkább), postgresql-ben úgy vettem észre eddig, hogy nincs akkora veszteség.
21

nem teljesen jó.

Radon · 2014. Jan. 17. (P), 20.05
Nem teljesen jó, mert a COUNT( user_goals.user_id )
csak az abban a táblában lévő, userhez tartozó rekordok számát adja vissza, de az nem azt jelenti, hogy azok egyeznek is az én céljaimmal.
tehát a count visszaadja, hogy 1 ik usernak 3 célja van, de abból csak 1 a közös, még akinek csak 2 célja van, lehet hogy abból mind2 egyezik velem, tehát elé kellene, hogy kerüljön.
23

Két lekérdezés

Vilmos · 2014. Jan. 18. (Szo), 10.20
Valóban, részleges egyezést is visszaad a lekérdezés.
Azt javaslom, két lekérdezéssel oldd meg a problémádat.

Elsőnek tudd meg hány célja van a kiválasztott felhasználónak ("ÉN"). Ez hamar lefut, nem sokat ront a futási időn.
SELECT COUNT( goals_id ) AS celok_szama
FROM user_goals WHERE user_id = 'ÉN'
GROUP BY user_id


Másodszorra, sorba rendezett találati listát kérj le. Ez nem optimális valószínűleg, de működik. Ha kiadtad az utasítást, akkor csak azokat a sorokat vedd figyelembe, ahol a "talalt_celok" = "celok_szama"
SELECT user_goals.user_id, COUNT( user_goals.user_id ) AS talalt_celok
FROM user_goals,
   ( SELECT * FROM user_goals WHERE user_id = 'ÉN') AS tmp
WHERE user_goals.user_id <> 'ÉN' and user_goals.goal_id = tmp.goal_id
GROUP BY user_goals.user_id
ORDER BY talalat DESC
22

Metszetek

complex857 · 2014. Jan. 17. (P), 23.57
Megpróbálom kicsit körüljárni a feltett kérdést, illetve az eddigiek során felmerült pontosítások, ötletek körét is (egy kicsit hosszadalmasra sikerült).
Ha jól értem a kezdőkérdést, illetve az azóta folyó diskurzust, téged az érdekel, hogy kik azok akik a lehető legnagyobb átfedésben vannak a useredhez tartozó "goal_id" listával.

Ezt alapvetően a user_goals tábla önmagával vett descartes-szorzatából ki lehet számolni a következőképpen (nagyjából a 13-as hozzászólásban található lekérdezésnek felel meg):

SELECT                
  t2.user_id AS other_user_id,
  count(t2.goal_id) as matchcount
FROM
  user_goals t1 
JOIN 
  user_goals t2 ON t1.goal_id = t2.goal_id
WHERE
  t1.user_id <> t2.user_id and
  t1.user_id = 1
GROUP BY
  t1.user_id, t2.user_id
ORDER BY
  matchcount desc
Az eredmény nagyjából a következőképp fest majd (SQLFiddle):
TO_USER_IDMATCHCOUNT
22
41
31

Egy kis magyarázat a dolog elemeiről:
Az összesítés lelke a user_goals tábla saját magához csatolása a goal_id-k mentén, ezzel egy olyan relációt hozunk éltre aminek soraiban szerepel 1 goal és 2 user ID.

FROM
  user_goals t1 
JOIN 
  user_goals t2 ON t1.goal_id = t2.goal_id
Az így kapott sorokból először kidobáljuk azokat amikben a user-ek saját magukhoz lettek párosítva ...

WHERE
  t1.user_id <> t2.user_id and
Mivel minket csak egyetlen user szempontjából érdekel az eredmény (a mienkéből) csak azokkal a sorokkal haladunk tovább amik rólunk szólnak, vagyis a reláció userei között felbukkanunk (a példában a 1es user ID vagyunk mi), mivel a reláció szimmetrikus elég ha a két user id oszlopból csak egyre adunk meg szűrést:

WHERE
....
  and t1.user_id = 1
Az így kapott sorok most minden létező goal_id + valaki más user_id-ja sort látjuk, már csak összesíteni kell őket a másik user ID-ja alapján, hogy az egyező goal-ok megszámlálhatóak legyenek, illetve sorba állítani, hogy sok közös céllal rendelkezők legyenek elöl, mégiscsak ők izgalmasak:

GROUP BY
  t1.user_id, t2.user_id
ORDER BY
  matchcount desc

Na most ez a lekérdezés egy kellően nagy adatbázison alapvetően nem lesz nagyon gyors user_id illetve goal_id oszlopokra egyaránt érdemes lehet indexet tenni. Egyik megfontolandó továbblépési pont, hogy az összes variáció előre kiszámolására általánosítani a lekérdezést és adatok változásakor csak adott user-hez tartozó dolgokat újraszámolni. Valami ilyesmit képzelnék el:

SELECT                
  t1.user_id AS from_user_id,
  t2.user_id AS to_user_id,
  count(t2.goal_id) as matchcount
FROM
  user_goals t1 
JOIN 
  user_goals t2 ON t1.goal_id = t2.goal_id
WHERE
  t1.user_id <> t2.user_id and
  t1.user_id > t2.user_id
GROUP BY
  t1.user_id, t2.user_id
HAVING 
  matchcount > 0
Ez az összes összepárosítható usert ki fogja dobni az adott pár egyezési számával.
Az egyetlen új elem ami talán magyarázatra szorulhat az a második feltétel a WHERE -ben:

WHERE
...
  t1.user_id > t2.user_id
Erre itt azért van szükség mivel a reláció szimmetrikus, az eredmény tartalmazná mind az A user -> B user mind a
B user -> A user párokat. Viszont ha rendezést adunk meg a két user ID között akkor csak ezek közül az egyik lehet igaz. Cserébe persze lekérdezéskor egy UNION segítségére lesz szükségünk, mivel keresett user ID-ja lehet mindkét oldalon.

Egy másik talán fontosabb probléma a módszerrel, hogy nem tud különbséget tenni goal és goal között. A hozzászólások között felmerült, hogy vannak goal-ok amiket "mindenki bejelöl" ezeket célszerű volna kevésbé értékesnek jelölni mint azokat amikre csak páran iratkoznak fel. Ehez maguknak a goal-oknak kellene valamilyen súlyozást adni, majd összesítéskor hozzácsatolva a (goal_id, t1.user_id, t2.user_id) sorokhoz a COUNT() által visszaadott értéket módosítani. Például ritkább goal-ok nagyobb pontszámosak mint gyakoriak, pontszámok SUM() + COUNT() -ja adja az egyezőségi pontszámot. Ez esetben persze a korábban felvetett előre kiszámolást nehéz hatékonyan végezni mivel ha valaki felvesz egy új goalt magához az potenciálisan mindenki mást is érinthet, értelmetlenné válik előre kiszámolni bármit is.

A korábbi példát kiegészítve egy score mezővel a goal táblán (SQLFiddle):

SELECT                
  t2.user_id AS other_user_id,
  sum(g.score) + count(t2.goal_id) as score,
  sum(g.score) as scoresum,
  count(t2.goal_id) as matchcount,
  group_concat(t2.goal_id) as goals
FROM
  user_goals t1 
JOIN 
  user_goals t2 ON t1.goal_id = t2.goal_id
JOIN
  goals g ON t2.goal_id = g.id
WHERE
  t1.user_id <> t2.user_id and
  t1.user_id = 1
GROUP BY
  t1.user_id, t2.user_id
ORDER BY
  score desc
Az eredmény így fest, a szemléletesség kedvéért kiemeltem a részeredményeket is külön oszlopokba:
OTHER_USER_IDSCORESCORESUMMATCHCOUNTGOALS
46513
24221,2
32111

Látható, hogy 2-es ID-jú user két goal-ban is osztozik velünk, de azok együtt se olyan izgalmasak mint 4 ID-jú user akivel egy 5 pontos goal-on van egyezés.

Ez az egész persze csak a "Recommender systems" nevű jéghegy csúcsa, érdemes a témakör további felderítésének kiindulópontjaként ajánlom a wikipedia egy, talán ide vonatkozó szócikkét: Collaborative filtering.
24

Ez igen

Hidvégi Gábor · 2014. Jan. 18. (Szo), 10.22
Rád mindig lehet számítani.
25

szép

Radon · 2014. Jan. 18. (Szo), 11.22
Na ez brutál.
Az meg, hogy scoret adjak a célokhoz, hát sosem gondoltam volna ilyet.. Jó tudni erről, bár gyakorlati szerepe itt nem lesz, mivel ha még csak 1-2 célom van, úgysem keresek egyező embereket, ha pedig 10-15 célom lesz, akkor önmagában érdekes 8-10 egyezőség.

Eddig ezek működtek, nagyon jó, megvan egy sor user_id-m.
De ebből hogy lesz user_name listám?
Ha ezt az egészet beteszem egy
WHERE users.id IN ()-be, akkor a matchcount-ot elvesztem.
Vagy olyat tudnék, hogy array_push-al tömbbe rakom, foreach-el körbejárom és a ciklusban kérdem le a neveket.
Szóval hogy lehetne a user_neveket a matchcount alapj'n sorba rakni?:)
26

JOINs all the way down

complex857 · 2014. Jan. 18. (Szo), 19.41
Alapvetően elég sokféleképpen oda lehet varázsolni az adatokat user_id mellé, de szerintem legegyszerűbb ha ezt is oda JOIN-olod (SQLFiddle):

SELECT
  t2.user_id AS other_user_id,
  u.name as user_name,
  count(t2.goal_id) as matchcount
FROM
  user_goals t1
JOIN
  user_goals t2 ON t1.goal_id = t2.goal_id
JOIN
  users u ON u.id = t2.user_id
WHERE
  t1.user_id <> t2.user_id and
  t1.user_id = 1
GROUP BY
  t1.user_id, t2.user_id
ORDER BY
  matchcount desc
Az egyetlen újdonság a 2. JOIN amivel elérhetővé válnak users tábla adatai a SELECT-ben.
27

kösz

Radon · 2014. Jan. 18. (Szo), 23.19
Kösz szépen úgy néz ki működik.
Kösz mindenkinek aki segített, az összes hozzászólásból tanultam valamit,
külön köszi complex, hogy magyarázatot is írtál.
Ez a Collaborative filtering is nagyon tetszik.
Ebben a weboldalban még nem csalódtam.:)