Van egy lekérdezésünk, ami összeszámolja adott kliens esetén, hogy melyik státuszban hányszor volt. Így néz ki:
SELECT client_id,status,COUNT(status) FROM cli_s GROUP BY status,client_id ORDER BY client_id,status;
Ez ad egy ilyen táblázatot:
+-----------+--------+---------------+ | client_id | status | count(status) | +-----------+--------+---------------+ | 155 | 0 | 69 | | 155 | 10 | 8 | | 155 | 30 | 2 | | 155 | 40 | 2 | | 1100 | 0 | 158 | | 1100 | 10 | 17 | | 1100 | 30 | 16 | +-----------+--------+---------------+
Ez helyett szeretnék valami tömörebbet, valahogy így:
+-----------+----------+-----------+-----------+-----------+-----------+ | client_id | count(0) | count(10) | count(20) | count(30) | count(40) | +-----------+----------+-----------+-----------+-----------+-----------+ | 155 | 69 | 8 | 0 | 2 | 2 | | 1100 | 158 | 17 | 0 | 16 | 0 | +-----------+----------+-----------+-----------+-----------+-----------+
Ok, awk-val pillanatok alatt össze lehet dobni az átalakítást, de talán az SQL is tud ilyet.
A status csak a 0,10,20,30,40 számok valamelyike lehet.
- 763 megtekintés
Hozzászólások
Egyrészt a mező értékei is lehetnek subqueryk, pl.
select client_id,
(select count from cli_s s0 where m.client_id = s0.client_id AND s0.status = 0) as "count(0)",
(select count from cli_s s10 where m.client_id = s10.client_id AND s1.status = 10) as "count(10)",
(select count from cli_s s20 where m.client_id = s20.client_id AND s2.status = 20) as "count(20)",
(select count from cli_s s30 where m.client_id = s30.client_id AND s3.status = 30) as "count(30)",
(select count from cli_s s40 where m.client_id = s40.client_id AND s4.status = 40) as "count(40)"
from
(select distinct client_id from cli_s) m
Másik opció, hogy a subqueryd beteszed egy CTE-be (vagy külön egy temp táblába, ha rengeteg az adat) és azt joinolgatod össze.
WITH sums (client_id,status, cnt) as (
SELECT client_id,status,COUNT(status) cnt FROM cli_s GROUP BY status,client_id
)
SELECT client_id,
s0.cnt AS "count(0)",
s10.cnt AS "count(10)",
s20.cnt AS "count(20)",
s30.cnt AS "count(30)",
s40.cnt AS "count(40)"
from
(select distinct client_id from cli_s) m
LEFT JOIN sums s0 ON s0.status = 0 AND s0.client_id = m.client_id
LEFT JOIN sums s10 ON s10.status = 10 AND s0.client_id = m.client_id
LEFT JOIN sums s20 ON s20.status = 20 AND s0.client_id = m.client_id
LEFT JOIN sums s30 ON s30.status = 30 AND s0.client_id = m.client_id
LEFT JOIN sums s40 ON s40.status = 40 AND s0.client_id = m.client_id
Lehet, hogy Window functionnal is lehetne valamit trükközni, de most este van, hogy megnézzem.
Fejből írtam, PostgreSQL-re, escapelést majd átírod a neked megfelelőre. Első nem feltétlen lesz gyors, de ha nem kell sok adatra, jó lehet. Ha sok adatra kell, akkor inkább a második vagy akár temp tábla + index rá.
Egyéb megjegyzések, 15+ év tapasztalata alapján: remélem nem ezek a mezőnevek vannak a rendes rendszerben, mert elég meh. Mi az a cli_s? s valami rövidítés? cli az client vagy command line? stb. Hogy a következő ember ne tudja, hogy mi az? A status meg szintén szerintem nem szerencsés, hogy nincs ott, hogy miféle status (ha majd lesz 50 status meződ a rendszerben, öröm lesz queryt írni és mindig aliaszolni) és egyébként se annyira jó szerintem az, hogy egy integer value a status, ahelyett, hogy egy enum lenne vagy legalább varcharban rendes szöveg. Csináltam én is ilyet, Ameddig nem trilliócsilliárd rekord van és nem ez a szűk keresztmetszet, csak önszopatás.
- A hozzászóláshoz be kell jelentkezni
Ezt többféleképp is csinálhatod:
pl. a legegyszerűbb, hogy ha csak client_id-re group-olsz, és a count(statusz) helyett ezt írod:
sum(case status when 0 then 1 else 0 end) as status0,
sum(case status when 10 then 1 else 0 end) as status10,
stb...
(A count(statusz)-t is benne hagynám esetleg ellenőrzésnek, és kivenném, ha rendben van minden.)
Össze is join-olhatsz 5 lekérdezést, ami mindegyik a megfelelő státuszra szűr, és count-al számol,
De a mai adatbázis kezelőkben tuti van rá valami modern okosság, amiket nem tudok fejből, pl pivot, crosstab funkciók.
- A hozzászóláshoz be kell jelentkezni
sub
- A hozzászóláshoz be kell jelentkezni
Szvsz sorból oszlopot csinálni pont az a felhasználás, amire az SQL nem való. Hacsak a felhasználó nem direktben futtatja a select-et, akkor van valamilyen kliens oldali programnyelv, amiben ezt meg lehet csinálni.
- A hozzászóláshoz be kell jelentkezni
Tulajdonképp csak egy fapados report lenne. Kb.: echo "select ..." | mysql --table dbname | mail -s "report" xxx@yyy.hu
- A hozzászóláshoz be kell jelentkezni
már miért nem? erre van a pivot több sqlben is
~ubuntu, raspbian, os x~
- A hozzászóláshoz be kell jelentkezni
A 'több SQL-ben is' az pont ugyanaz, mint amit én mondtam: utólag rácsavarozott, koncepcióidegen feature gyártóspecifikus bővítményekkel.
- A hozzászóláshoz be kell jelentkezni
Milyen adatbázisról beszélünk? Mert van ahol ez egyszerűen megoldott, pl. plsql pivot.
- A hozzászóláshoz be kell jelentkezni
mysql
- A hozzászóláshoz be kell jelentkezni
SELECT
client_id,
SUM(DECODE(status, 0, 1, 0)) count_0,
SUM(DECODE(status, 10, 1, 0)) count_10,
SUM(DECODE(status, 20, 1, 0)) count_20,
SUM(DECODE(status, 30, 1, 0)) count_30,
SUM(DECODE(status, 40, 1, 0)) count_40,
SUM(DECODE(status, 50, 1, 0)) count_50
FROM cli_s
GROUP BY client_id
ORDER BY client_id
Ez valószínűleg minden ANSI SQL-en működni fog.
SELECT *
FROM (
SELECT client_id, status
FROM cli_s)
PIVOT (
COUNT(*) FOR status IN (0 AS count_0, 10 AS count_10, 20 AS count_20, 30 AS count_30, 40 AS count_40, 50 AS count_50)
)
Ez pedig ugyan az errefelé közutálatnak örvendő cég által gyártott rdbms-ben (11g-től).
- A hozzászóláshoz be kell jelentkezni
Szerintem az Oraclet alapvetően nem a featuresetje miatt szokták utálni :)
- A hozzászóláshoz be kell jelentkezni
Nem csak azért :)
BlackY
"Gyakran hasznos ugyanis, ha számlálni tudjuk, hányszor futott le már egy végtelenciklus." (haroldking)
- A hozzászóláshoz be kell jelentkezni
Köszi. Kicsit át kellet írni, de ez az ötlet vált be.
SUM(IF(status=30,1,0)) AS 'count_30'
Sőt, még az IF is kispórolható:
SELECT client_id,
SUM(status=0) count_0,
SUM(status=10) count_10,
SUM(status=20) count_20,
SUM(status=30) count_30,
SUM(status=40) count_40
FROM cli_s
GROUP BY client_id
ORDER BY client_id
- A hozzászóláshoz be kell jelentkezni
Igen, ez ugyanaz, mint a case when-es.
- A hozzászóláshoz be kell jelentkezni
Ezt javaslom mindenkinek, hogy nézze meg:
https://www.slideshare.net/MarkusWinand/modern-sql
Van benne pár érdekes dolog, pedig ez sem mai prezentáció. (Vagy legalábbis nekem volt.)
Ami most kellett, azt úgy látom, a 72-es, 73-as slide-on szerepel is.
- A hozzászóláshoz be kell jelentkezni