ugrás a tartalomhoz

MySQL lekérdezés máshogy

Termes · 2015. Feb. 13. (P), 15.44
Sziasztok!

A következőt találtam ki:

Adott egy iksz tábla szolgáltatásokkal pl: dátum,név,vásárlás

| id | nev | datum | vasarlas |
| 1 | Okos | 2015-01-01 | igen |
| 2 | Peter | 2015-01-05 | igen |
....
Arra lenne szükségem, hogy ki az az egyén (id, aki kéthetente legalább egyszer vásárolt egy éven keresztül. Tehát, nem 27x egy évben, hanem MINDEN második héten van legalább egy rekordja.

Ez lenne és nem tudok rájönni, hogy hogyan lehetne minél kevesebb lekérdezésből megoldani.

Köszönöm a segítséget.

Üdv:T
‹ Mysql backup egy nagy tábla vs. sok ugyanolyan kicsi ›
 ■
1

Kérdés

Hidvégi Gábor · 2015. Feb. 13. (P), 15.54
Mi az, amit eddig sikerült kifőzni?
2

hosszú sorok...

Termes · 2015. Feb. 13. (P), 16.01
Egyesével futtatom a lekérdezéseket és ellenőrzöm, hogy az adott lekérdezés hozott-e eredményt. Ha hozott kilistázom és az összes eredményt csoportosítom azonosító alapján. Így nagy nehezen kiugrik az a pár ember, aki minden második héten legalább egyszer vásárol.
3

Mivel MySQL-ben közvetlenül

Hidvégi Gábor · 2015. Feb. 13. (P), 16.05
Mivel MySQL-ben közvetlenül nem nagyon lehet az egyik rekordból a másikra hivatkozni (talán csak tárolt eljárásokban kurzor segítségével), ezért szerveroldalon lehet elvégezni bizonyos ellenőrzéseket. Ha ez lassú, érdemes EXPLAIN-nel megnézni, hol a szűk keresztmetszet, és indexelni.
4

esetleg...

Termes · 2015. Feb. 13. (P), 16.07
Esetleg tömbökkel nem lehetne megoldani?
5

Mit?

Hidvégi Gábor · 2015. Feb. 13. (P), 16.22
Mit?
6

az..

Termes · 2015. Feb. 13. (P), 16.29
Gondoltam... ;) ha a lekérdezések eredményeit, ami mondjuk az azonosítók lennének, tömbösítenénk, akkor kapnánk 27 tömböt. Ebből lehetne kiválasztani azokat az azonosítókat, amik mind az összesben szerepel és azonosító alapján csoportba rendezni....vagy hülyeség?
7

Attól függ, mit kérdezel le :

Hidvégi Gábor · 2015. Feb. 13. (P), 17.02
Attól függ, mit kérdezel le : ) De érzésem szerint rossz úton jársz.

Próbáljuk meg lebontani a feladatot részekre: hogyan tudod eldönteni egy illetőről, hogy az adott időintervallumban (egy évben) vásárolt-e kéthetenként? Milyen lekérdezéseket futtatnál le, és a kapott adatok alapján mit ellenőriznél le?
13

ez az..

Termes · 2015. Feb. 17. (K), 12.26
pont ez lenne nekem a megoldás...

A lekérdezésekben azon személyek azonosítóit listáznám, akik vásároltak legalább egyszer az adott héten. Ez kb 27 lekérdezés lenne. Idáig megy is :) Itt kellene megvizsgálni, hogy kik azok, akik mind a 27 lekérdezésben szerepelnek. Ez már meghaladja a tudásomat sajnos és nem is tudom, hogy hogyan tudnám tutorialokból kibogozni.
8

plusz mező

szabo.b.gabor · 2015. Feb. 14. (Szo), 05.52
Kell egy plusz mező, amiben eltárolod az illető előző vásárlása óta eltelt időt. Én integerben tárolnék másodperceket.

Lehet külön tábla, működhet trigger alapján.

GROUP BY, MAX

Oszt jónapotkívánok.
14

csak minta

Termes · 2015. Feb. 17. (K), 12.31
Az eredeti tábla sokkal összetettebb, csak a példa kedvéért van így lebutítva.

A problémám, hogy már 3. éve töltődik az adatbázis, tehát visszamenőleges megoldást kellene találni.

Köszi.
16

Érdekes feladat és én is

kuka · 2015. Feb. 17. (K), 12.49
Érdekes feladat és én is szerencsét próbálnék. De sajnos nagyon nincs időm adatbázist gyártani hozzá. Esetleg megtehetnéd, hogy valahova feltöltesz egy lebutított dumpot (lényegesen kevesebb mint 3 év, fölösleges oszlopok eltüntetve, személyes jellegű adatok véletlenszerűre cserélve), mert szerintem akadna próbálkozó rajtam kívül is.
21

nem akarok..

Termes · 2015. Feb. 18. (Sze), 10.42
Isten ments, hogy bárkit is megdolgoztassak.... nem ez a célom, hogy helyettem megcsináljátok, ezért sem az eredeti tábla alapján kértem segítséget. Csupán a metodika / logika megtalálásában szeretném, ha segítenétek, azután majd próbálkozok megint... ;)
24

Ne állj a programozó és a

bamegakapa · 2015. Feb. 18. (Sze), 11.17
Ne állj a programozó és a prédának kiszemelt probléma közé ;).
25

:D

szabo.b.gabor · 2015. Feb. 18. (Sze), 12.17
ezjó!
17

Ha van egy szkripted, akkor

szabo.b.gabor · 2015. Feb. 17. (K), 13.17
Ha van egy szkripted, akkor az lefut 3 évre is gond nélkül gondolom én.
23

persze

Termes · 2015. Feb. 18. (Sze), 11.00
Igen persze, de ha plusz táblákkal akarnám megoldani, arra vonatkozott az előbbi irományom. Azokkal kell dolgozni, ami jelenleg rendelkezésre áll. Köszi.
12

Szia! Az is egy megoldás,

tisch.david · 2015. Feb. 17. (K), 10.22
Szia!

Az is egy megoldás, hogy

SELECT T1.* FROM TABLE T1
  JOIN TABLE T2 ON T2.ID = T1.ID AND T3.DATUM >= '2015-01-08' AND T3.DATUM <= '2015-01-14'
  JOIN ...
WHERE T1.DATUM >= '2015-01-01' AND T1.DATUM <= '2015-01-07'
És úgy állítod össze a lekérdezést, hogy a dátum határok mindig az adott év 1. hétfőtől induljanak. Nagyon fapados, de szerintem tökélesen működne.

Üdv:

Dávid
9

Egy lekérdezésből megvan.

Sanyiii · 2015. Feb. 16. (H), 10.44
Egy lekérdezésből megvan. Kábé:

SELECT
  t1.nev,
  /* Vásárlások száma (kivéve első vásárlás): */
  COUNT(t1.id) AS darab1,
  /* Ebből folyamatos vásárlások száma: */
  SUM(IF(
    (SELECT
      MAX(t2.datum)
    FROM
      tabla AS t2
    WHERE
      t2.nev = t1.nev
      AND t2.datum < t1.datum
      AND t2.datum >= DATE_SUB(t1.datum, INTERVAL 2 WEEK)) IS NOT NULL,
    1,
    0
  )) AS darab2,
  /* Az első vásárlás adata is kell: */
  (
    SELECT
      MIN(t3.datum)
    FROM
      tabla AS t3
    WHERE
      t3.nev = t1.nev
  ) AS min_datum,
  SUBSTRING_INDEX(GROUP_CONCAT(t1.datum ORDER BY t1.datum DESC), ',', 1) AS max_datum
FROM
  tabla AS t1
WHERE
  /* Második vásárlástól nézzük: */
  t1.datum > (
    SELECT
      MIN(t3.datum)
    FROM
      tabla AS t3
    WHERE
      t3.nev = t1.nev
  )
  /* IDE TOVÁBBI FELTÉTELEK JÖHETNEK, PL. IDŐSZAKRA VISZGÁLÁS STB. */
GROUP BY
  t1.nev
HAVING
  /* A vásárlások száma és a folyamatos vásárlások száma megegyezik: */
  darab1 = darab2
  /* Legalább 1 év eltelt: */
  AND DATE_ADD(min_datum, INTERVAL 1 YEAR) <= max_datum
10

Ahogy nézem, lehetne

Sanyiii · 2015. Feb. 16. (H), 10.54
Ahogy nézem, lehetne gyorsítani is, de azt már old meg magad:)
15

igazam volt...

Termes · 2015. Feb. 17. (K), 12.38
Igazam volt, ez már tényleg magas nekem....

Köszi.
18

Ne legy kishitu, ez csak sql,

Sanyiii · 2015. Feb. 17. (K), 15.39
Ne legy kishitu, ez csak sql, jatek, mint egy lego.
22

:)

Termes · 2015. Feb. 18. (Sze), 10.46
Köszi, de mivel én ezeket sosem tanultam, csak autodidakta módon, ezért elég sok hiányosságom van. Mindig csak azt tanultam meg, ami éppen kellett, kigugliztam és addig próbálkoztam, amíg megértettem ( valamennyire ) a működését az éppen aktuális operátornak, vagy másnak...

Ez nem kishitűség...önismeret :))
26

Ha érdekel az sql (végülis

Sanyiii · 2015. Feb. 19. (Cs), 16.40
Ha érdekel az sql (végülis különben nem tettél fel volna ilyen kérdést), akkor vedd meg az SQL fejtörők könyvet. Pont olyan jellegű, mint egy fórum, van egy feladvány (kérdés), és ad a szerző rá 1-2 megoldást, elmagyarázva, mit miért. Egy feladvány és a válasz rá 2 oldal, tehát nem terheled szét magad tőle, és mégis mindig valami újat mutat. Az sql tényleg nem valami nehéz dolog, pár oldal után is kisujjadban lesz az egész.
27

Nekem megvan

zzrek · 2015. Feb. 19. (Cs), 18.05
Nekem megvan ez a könyv, eladom, ha érdekel valakit.
11

Én abból indultam ki, hogy ha

spapp · 2015. Feb. 16. (H), 10.54
Én abból indultam ki, hogy ha kéthetente egyszer kell vásárolni, akkor az vagy minden páros, vagy minden páratlan hét kell legyen, tehát azt kell meghatározni, hogy hány páros vagy páratlan héten történik vásárlás.

Tábla:

CREATE TABLE `tbl` (
  `id` int(11) DEFAULT NULL,
  `d` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Lekérdezés:

select tbl3.id, tbl3.w2 as 'páros vagy páratlan', count(tbl3.w2) as 'vásárlások száma'
from (
	select tbl2.id, mod(w,2) as w2 
	from (
		select distinct id, week(d) as w 
		from tbl
	) as tbl2
) as tbl3
group by tbl3.id, tbl3.w2
Eredmény:

id | páros vagy páratlan | vásárlások száma
1 | 0 | 2
1 | 1 | 26
2 | 0 | 1
19

Egész osztás

vbence · 2015. Feb. 17. (K), 21.01
Ugyebár a naptárban minden hétnek van egy száma. A te naptáradban minden két hétnek lesz egy száma, egy évben 26 Kéthét lesz.

Minden dátumból megkapod hogy melyik Kéthét alá tartozik, ha lesztod 14-gyel. Picit bővebben: veszed a dátum UNIX_TIMESTAMP reprezentációját, és leosztod 14 * 24 * 60 * 60 nal - a Unix timestamp másodperc alapú és ennyi másodperc van 14 napban.

Akikre igaz az állítás azoknak minden Kéthét le lesz fedve, vagyis 26 különböző ilyen rekordot produkálnak: COUNT(DISTINCT (datum DIV 1209600)) = 26

A való életben egyetlen SQL helyett egy segédtáblába gyűjteném a vásárolásokat, aminek mezői a Kéthetet reprezentáló szám és a User id lenne. A két mezőre kombinációjára unique kulccsal. Ebbe INSERT IGNORE SELECT ... utasítással beletölteném az összes rekordot (így egyetlen rekord testesítené meg, hogy egy User vásárolt-e az adott Kéthétben).

Innen már lehet:
SELECT user_id, COUNT(*) AS cnt GROUP BY user_id HAVING cnt >= 26;
20

Fiddle

Endyl · 2015. Feb. 18. (Sze), 00.31
Csináltam hozzá egy fiddle-t, ha valaki akarná tesztelni a megoldását. Az adatokban Bill vásárlásai között nem telik el 13-nál több nap, John esetében viszont van egy 14 napnál nagyobb kihagyás.

Én így fordítanám le a probléma vélt megfogalmazását MySQL-re:
SELECT
	p.name,
	MAX(
		DATEDIFF(
			IFNULL(
				(SELECT
					p2.pdate
				FROM
					purchase p2
				WHERE
					p2.pdate > p.pdate AND
					p2.name = p.name
				ORDER BY
					p2.pdate ASC
				LIMIT 1),
				'2014-12-31'),
			p.pdate)
		) as nextPurchaseDiff,
	MAX(
		DATEDIFF(
			p.pdate,
			IFNULL(
				(SELECT
					p2.pdate
				FROM
					purchase p2
				WHERE
					p2.pdate < p.pdate AND
					p2.name = p.name
				ORDER BY
					p2.pdate DESC
				LIMIT 1),
				'2014-01-01')
			)
		) as prevPurchaseDiff
FROM
	purchase p
WHERE
	'2014-01-01' <= p.pdate AND
	p.pdate <= '2014-12-31'
GROUP BY
	p.name
HAVING
	nextPurchaseDiff <= 14 AND
	prevPurchaseDiff <= 14
ORDER BY
	p.name ASC;
Így megkapjuk, hogy 2014-ben kinek telt el legfeljebb 14 nap két egymást követő vásárlása között (ellenőrizve az intervallum elejét és végét mint első és utolsó elméletbeli vásárlás).
Az egyenlő bánásmód nevében a subselecteket is célszerű lehet szűrni az intervallumra, mert két 2014. december 30-án vásárló közül az, aki azóta nem vásárolt, az bekerül a listába, míg aki rá 15 napra vásárolt legközelebb az pedig nem.
Valahogy talán ki lehet trükközni, hogy csak egy subselect legyen benne, de már nem fog hozzá az agyam.