---

Kasper Johansen wrote:
> Hej gruppe.
>
> Er det muligt, med en SQL-sætning, at trække bestemte data ud fra
en tabel
> og lægge nogle af disse tal sammen (eller få en procent del tilbage
mht til
> et felt).
>
> Tænker noget lign:
>
> SELECT
> *
>
> FROM
> users,
> tal
>
> WHERE
> tal.uid = users.id &&
> tal.value = "5" = "80%"
>
>
> Altså trække de brugere ud som har skrevet 5 i value i 80% af
tilfældene?
>
> Er der en nem eller svær måde at gøre dette på?

Den var sjov !

Jeg har forsimplet opstillingen lidt, idet det for den grundlæggende
problemstilling er irrelevant om du arbejder på et join eller en
enkelt tabel.

Problem: Find de id i tabel t, der i (netop) 80% af tilfældende har
value=5.

Løsning:

select id
from t t1
group by id
having count(*) * 0,8 =
(select id, count(*)
from t t2
where t1.id = t2.id
and value = 5
group by id)

Hvis "netop 80%" skal ændres til "mindst 80%" rettes "* 0,8 ="
naturligvis blot til "* 0,8 <=".

Det er den slags, der gør dk.edb.database sjov at læse.

Advarsel: Overstående er ikke testet, og der i særdeleshed ingen
garanti for at det virker på dit DBMS.


Venlig hilsen
Kristian

---

On Thu, 16 Sep 2004 12:44:10 -0700, Kristian Damm Jensen wrote:

> Løsning:
>
> select id
> from t t1
> group by id
> having count(*) * 0,8 =
> (select id, count(*)
> from t t2
> where t1.id = t2.id
> and value = 5
> group by id)

Nu benytter du et (korreleret) subquery. Hvis man "må" det (der er jo
tale om en MySQL-database uden versionsangivelse), så må jeg også
foreslå følgende join-baserede løsning, fordi det er min erfaring, at
korrelerede subqueries er meget tunge:

SELECT * FROM
(
SELECT username,COUNT(answer) AS num5s
FROM answers WHERE answer=5
GROUP BY username,answer
) as l
NATURAL JOIN
(
SELECT username,COUNT(*) AS totalanswers
FROM answers
GROUP BY username
) AS r
WHERE (num5s::float/totalanswers)>=.8;

I ovenstående antager jeg en tabel med resultater fra spørgeskemaer:

CREATE TABLE answers (
formno INTEGER NOT NULL,
username VARCHAR(20) NOT NULL,
answer INTEGER NOT NULL CHECK(answer>0 AND answer<6),
PRIMARY KEY(formno,username)
);

- Og så tolker jeg i øvrigt opgaven som at finde alle brugere, der har
svaret 5 i mindst 80% af svarene.

Hvis DBMS'et ikke understøtter NATURAL JOINs, kan det omskrives til et
mindre klart udtryk, der benytter et INNER JOIN.

- Og så er "num5s::float" i øvrigt en PostgreSQL-only syntaks for at
cast'e til float, så jeg gennemtvinger den rette form for division. Igen
kan der oversættes til en anden SQL-variant efter behov.

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

Troels Arvin <troels@arvin.dk> wrote in message news:<pan.2004.09.16.20.35.28.138187@arvin.dk>...

<snip>

> der er jo tale om en MySQL-database uden versionsangivelse

UPS. Det havde jeg ikke lige set. Til gengæld fik jeg efterfølgende
testet min query i Sybase og kan se, at den virker. Omend den for min
tabel, der indeholder mange millioner rækker, var noget tung.

<snip>

> så må jeg også foreslå følgende join-baserede løsning

<snip løsning>

Elegantere, bestemt.

De to løsninger baserer jo i øvrigt sig på samme grund idé, nemlig at
lave én query, der finder det samlede antal pr. id, og en anden der
finder de id'er der opfylder et specifikt kriterium. Disse to queries
sammenlignes dernæst. Hvis man ikke har adgang til sub-queries, er det
ikke noget stort problem at konvertere til en fladere struktur ved at
bruge views eller temporære tabeller e.l.

- Og hvis jeg skulle foretage en sådan konvertering vill jeg nok tage
udgangspunkt i Troels' version.

<snip>

VH
Kristian

---

On Fri, 17 Sep 2004 13:10:34 -0700, Kristian Damm Jensen wrote:

> Til gengæld fik jeg efterfølgende
> testet min query i Sybase og kan se, at den virker. Omend den for min
> tabel, der indeholder mange millioner rækker, var noget tung.

Har du mulighed for at benchmark'e med den join-baserede løsning?

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

Troels Arvin <troels@arvin.dk> wrote in message news:<pan.2004.09.19.18.17.22.579283@arvin.dk>...
> On Fri, 17 Sep 2004 13:10:34 -0700, Kristian Damm Jensen wrote:
>
> > Til gengæld fik jeg efterfølgende
> > testet min query i Sybase og kan se, at den virker. Omend den for min
> > tabel, der indeholder mange millioner rækker, var noget tung.
>
> Har du mulighed for at benchmark'e med den join-baserede løsning?

Jeg skal prøve. Jeg vender tilbage senere.

VH
Kristian

---

damm@ofir.dk (Kristian Damm Jensen) wrote in message news:<2c9e2992.0409200435.5f1a7a03@posting.google.com>...
> Troels Arvin <troels@arvin.dk> wrote in message news:<pan.2004.09.19.18.17.22.579283@arvin.dk>...
> > On Fri, 17 Sep 2004 13:10:34 -0700, Kristian Damm Jensen wrote:
> >
> > > Til gengæld fik jeg efterfølgende
> > > testet min query i Sybase og kan se, at den virker. Omend den for min
> > > tabel, der indeholder mange millioner rækker, var noget tung.
> >
> > Har du mulighed for at benchmark'e med den join-baserede løsning?
>
> Jeg skal prøve. Jeg vender tilbage senere.

Jeg har kørt følgende (anonymiseret, så jeg ikke anklages for at
offentliggøre min arbejdsgivers produktionsdata )

select count(*) from T
go

select id
from T t1
group by id
having count(*) * 0.8 <=
   (select count(*)
   from T t2
   where t1.id = t2.id
   and vaerdi = 'N')
go

select t1.id
from
   (select id, count(*) as antal
   from T where vaerdi = 'N'
   group by id) as t1
join
   (select id, count(*) as total
   from T
   group by id) as t2
on t1.id = t2.id
where convert(float, antal)/total >= 0.8
go

Der er 3454412 rækker i T, af hvilke 87264 opfylder kriteriet.

For den første select rapporterer Sybase
cpu time: 51200 ms
SQLServer time: 66756
og for den anden
cpu time: 63500 ms
SQLServer time: 123783

Det ser altså ud til at prisen for at skulle producere to
mellemresultater er større end prisen for at skulle producere et
(lille) mellemresultat for hver id.

VH
Kristian

---

On Mon, 20 Sep 2004 13:38:05 -0700, Kristian Damm Jensen wrote (subj. "Re:
=?ISO-8859-1?Q?MySQL:_L=E6gge_sammen,_tr=E6kke_fra_og_dividere=3F?="):

> Det ser altså ud til at prisen for at skulle producere to
> mellemresultater er større end prisen for at skulle producere et (lille)
> mellemresultat for hver id.

Interesting; tak.

Det kunne være interessant at prøve at køre i andre
SQL-implementationer.

Stadig må svartider over 50 sekunder siges at høre til blandt de
længere queries. Gad vist, om det i MSSQL kan betale sig at gemme
delresultater i temporære tabeller, og så join'e.

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

Troels Arvin <troels@arvin.dk> wrote in message news:<pan.2004.09.20.20.52.58.680320@arvin.dk>...
> On Mon, 20 Sep 2004 13:38:05 -0700, Kristian Damm Jensen wrote (subj. "Re:
> =?ISO-8859-1?Q?MySQL:_L=E6gge_sammen,_tr=E6kke_fra_og_dividere=3F?="):
>
> > Det ser altså ud til at prisen for at skulle producere to
> > mellemresultater er større end prisen for at skulle producere et (lille)
> > mellemresultat for hver id.
>
> Interesting; tak.
>
> Det kunne være interessant at prøve at køre i andre
> SQL-implementationer.
>
> Stadig må svartider over 50 sekunder siges at høre til blandt de
> længere queries. Gad vist, om det i MSSQL kan betale sig at gemme
> delresultater i temporære tabeller, og så join'e.

Mit umiddelbare gæt er: Nej. Kun hvis det samme delresultat skal
bruges flere gange kan det muligvis være en fordel. Eller hvis man kan
producere en generaliseret version, så man ved et udtræk reelt danner
flere forskellige delresultater.

I alle andre tilfælde tror jeg det vil være mere effektivt at lade
databasen om at foretage øvelsen.

VH
Kristian

---

On Mon, 20 Sep 2004 22:52:59 +0200, Troels Arvin wrote:

> Det kunne være interessant at prøve at køre i andre
> SQL-implementationer.

I PostgreSQL 7.4, på min laptop, "answers"-tabellen har 750000 rækker,
resultatsættet har 1041 rækker:

Tre forskellige metoder:

a) Korreleret subquery:
SELECT username FROM answers
GROUP BY username HAVING COUNT(*)*0.8<=(
SELECT COUNT(*) FROM answers AS a2
WHERE answers.username=a2.username
AND
answer=5
GROUP BY username
);

b) Join af underresultater:
SELECT username FROM
(
SELECT username,COUNT(answer) AS num5s
FROM answers WHERE answer=5
GROUP BY username,answer
) as l
NATURAL JOIN
(
SELECT username,COUNT(*) AS totalanswers
FROM answers
GROUP BY username
) AS r
WHERE (num5s::float/totalanswers)>=.8;

c) Join af temporære tabeller (svarer antagelig til
join af materialized views i DBMSer hvor
sådanne understøttes):
CREATE TEMPORARY TABLE l AS
SELECT username,COUNT(answer) AS num5s
FROM answers
WHERE answer=5
GROUP BY username,answer;
CREATE TEMPORARY TABLE r AS
SELECT username,COUNT(*) AS totalanswers
FROM answers
GROUP BY username;
SELECT username
FROM l NATURAL JOIN r
WHERE (num5s::float/totalanswers)>=.8;

Svartider, gennemsnit over tre kørsler, hvor ingen af kørselstiderne
skildte sig ud i absurd grad:

a/Korreleret subquery: 31 sek
b/Join af underresultater: 23 sek
c/Join af temporære tabeller: 24 sek

Den brugte tabel:
http://troels.arvin.dk/tests/sql/answer-stats/answers.sql.gz

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

On Tue, 21 Sep 2004 15:04:12 +0200, Troels Arvin wrote:

> I PostgreSQL 7.4, på min laptop, "answers"-tabellen har 750000 rækker,
> resultatsættet har 1041 rækker:

[...]

På en Oracle 10 på en relativt kraftig maskine:

Korreleret subquery: 4 sekunder.

Join af delresultater (uden brug af temporære tabeller):
Lidt under 2 sekunder.

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

Troels Arvin <troels@arvin.dk> wrote in message news:<pan.2004.09.21.13.04.11.703055@arvin.dk>...
> On Mon, 20 Sep 2004 22:52:59 +0200, Troels Arvin wrote:
>
> > Det kunne være interessant at prøve at køre i andre
> > SQL-implementationer.
>
> I PostgreSQL 7.4, på min laptop, "answers"-tabellen har 750000 rækker,
> resultatsættet har 1041 rækker:
>
> Tre forskellige metoder:
>
> a) Korreleret subquery:
> SELECT username FROM answers
> GROUP BY username HAVING COUNT(*)*0.8<=(
> SELECT COUNT(*) FROM answers AS a2
> WHERE answers.username=a2.username
> AND
> answer=5
> GROUP BY username
> );

Hm. Gør det nogen forskel, hvis du udelader group by fra subquerien?

Den er faktisk overflødig, hvilket gik op for mig, da jeg lavede min
egen test.

VH
Kristian

P.S. Undskyld, hvis dette er postet to gange. Google meldte
server-error, og jeg har ikke en chance for at se om min artikel kom
igennem før om 4-9 timer

---

On Tue, 21 Sep 2004 11:26:02 -0700, Kristian Damm Jensen wrote:

> Gør det nogen forskel, hvis du udelader group by fra subquerien?
>
> Den er faktisk overflødig, hvilket gik op for mig, da jeg lavede min egen
> test.

Ja, den er da overflødig. Og fjernelse af den gør en forskel:
På PostgreSQL kører løsningen med et korreleret subquery nu kun 7%
længere, mod før 35%.

Jeg har også lige prøvet (den forbedrede) korreleret-subquery
vs. join'ede subqueries på DB2. (Fandt endelig ud af, hvorledes man
importerer større datamængder ind i en DB2). Dér kører den korrelerede
metode lidt over dobbelt så længe som join-af-subqueries metoden.

Bladrede i øvrigt lidt i
http://books.elsevier.com/mk/default.asp?isbn=1558607536
Dér skriver de en del om, at man bør overveje at omskrive korrelerede
subqueries; deres forslag går primært på benyttelse af temporære
tabeller.

--
Greetings from Troels Arvin, Copenhagen, Denmark