PFZ.nl - Databases Forum

Verbruik per maand --> verschil tussen waarden uit 2 rijen berekenen

Sun, 05 Feb 2012 11:04:24 +0000

Goedemiddag,

Ik wil het gasverbruik per maand gaan berekenen. Ik ga er (voorlopig) vanuit dat er slechts 1x per maand een stand wordt ingevoerd, maar dat kan later altijd nog ooit veranderen. Dus bv. op 1 maart wordt de beginstand van maart ingevoerd (= eindstand februari), op 1 april wordt de beginstand van april ingevoerd (= eindstand maart) etc. etc. Dit zou de volgende tabel kunnen opleveren:

1
2
3
4
5
6
7

+----+------------+-------+
| ID | Datum      | Stand |
+----+------------+-------+
|  1 | 2011-03-01 | 23175 |
|  2 | 2011-04-01 | 23312 |
|  3 | 2011-05-01 | 23389 |
+----+------------+-------+

Nu wil ik het verbruik per maand gaan bepalen. Dat lukt me (uiteraard) wel met PHP, maar kan ik dat ook met SQL doen? Mijn gevoel zegt dat dat namelijk sneller zou zijn. Voor het verbruik in april moet dat dan ID3(Stand) - ID2(Stand) zijn... maar hoe kan dat dan eventueel? Uiteraard moet dat bv. voor een heel jaar per maand gebeuren.

Daarnaast nog een vraag:
Is het slim om de tabel op deze manier vorm te geven? Of kan ik beter gewoon de stand invoeren (zoals hierboven) maar ook een extra kolom maken met de eindstand van de vorige maand? Het kan namelijk bijv. ook gebeuren dat de gasmeter wordt vervangen en die dus weer op 0 begint. Dat gaat bij het bovenstaande een probleem opleveren lijkt me. Ik kan namelijk nergens een uitzondering invoeren waaruit blijkt dat er een nieuwe meter is geplaatst. Als ik ook de vorige stand opneem, dan kan dat denk ik wel:

Begin = beginstand van bv. 1 april,Eind = eindstand van 31 maart. Normaal gesproken hetzelfde, tenzij er een meterwissel is geweest

1
2
3
4
5
6
7
8

+----+------------+-------+-------+
| ID | Datum      | Begin | Eind  | 
+----+------------+-------+-------+
|  1 | 2011-03-01 | 23175 | 23175 |
|  2 | 2011-04-01 | 23312 | 23312 |
|  3 | 2011-05-01 |     0 | 23389 |
|  4 | 2011-06-01 |    12 |    12 |
+----+------------+-------+-------+

(Overigens kan ik ook - als de eindwaarde van de maand hetzelfde is als de startwaarde van de nieuwe maand (geen meter vervangen) - NULL (niks) invoeren, dat maakt opzich niet bar veel uit, het vraagt om een andere controle.)

btw data type

Sat, 04 Feb 2012 19:24:23 +0000

wat voor soort veld in mysql zouden jullie btw gebruiken?

ik dacht aan decimal maar heb nu tinyint

Nieuwe kolom database: informatie toevoegen!

Sat, 04 Feb 2012 10:20:56 +0000

Goedemorgen heren,

Enkele jaren geleden heb ik een (amateuristische) website gemaakt en daar moet ik nu een kleine aanpassing in maken, echter ben ik het programmeren totaal verleerd. Vandaar dat ik jullie hulp inschakel, hopelijk is er iemand die mij uit de brand wil helpen!

Ik heb een database van plm. 1000 rijen en nu heb ik een extra kolom ("afbeelding") toegevoegd aan elke rij. Om nu handmatig 1000 keer informatie toe te voegen aan elke nieuwe kolom "afbeelding" is onbegonnen werk. Derhalve ben ik op zoek naar een gemakkelijkere oplossing in de vorm van een PHP script. Het gaat om het volgende:

Ik heb een kolom "code" en een kolom "afbeelding" (zie: http://i40.tinypic.com/dwdpir.jpg). Ik wil de laatste 3 letters van de kolom "code" gebruiken voor de kolom "afbeelding". Dus de kolom "afbeelding" van code S.FCROC 560 moet de waarde 560.jpg krijgen, code S.FCROC 847 moet afbeelding 847.jpg krijgen etc. Kunnen jullie mij hiermee helpen? Alvast hartelijk dank.

Groeten,
Wesley

Many to many relation met dubbelewhere clause

Sat, 04 Feb 2012 09:25:56 +0000

Ik heb een many to many relationship tussen books en authors, immers een boek kan meerdere auteurs hebben en een auteur meerdere boeken. De koppeltabel heet authors_books.

Het is me al gelukt om te zoeken op boeken die zowel door auteur 'Jan Wolkers' als door 'Herman Koch' geschreven zijn. Onderstaande levert dan ook een goed resultaat op.

1
2
3
4
5
6
7
8

SELECT books.id, books.title
FROM books
RIGHT JOIN authors_books AB1 ON AB1.book_id = books.id
LEFT JOIN authors AS A1 ON A1.id = AB1.author_id
RIGHT JOIN authors_books AB2 ON AB2.book_id = books.id
LEFT JOIN authors AS A2 ON A2.id = AB2.author_id
WHERE A1.name = 'Jan Wolkers'
AND A2.name ='Herman Koch'

Nu ben ik echter op zoek naar alle boeken die wel door 'Jan Wolkers' zijn geschreven, maar waar 'Herman Koch' niet aan mee heeft geschreven. Hoe krijg ik dit voor elkaar? Onderstaande werkt niet (is ook logisch).

1
2
3
4
5
6
7
8

SELECT books.id, books.title
FROM books
RIGHT JOIN authors_books AB1 ON AB1.book_id = books.id
LEFT JOIN authors AS A1 ON A1.id = AB1.author_id
RIGHT JOIN authors_books AB2 ON AB2.book_id = books.id
LEFT JOIN authors AS A2 ON A2.id = AB2.author_id
WHERE A1.name = 'Jan Wolkers'
AND A2.name !='Herman Koch'

order by uit een andere tabel

Fri, 03 Feb 2012 17:33:48 +0000

Hallo,

Ik heb een klik script en die heeft de volgende tabellen.

Tabel: ads met de advertentie gegevens, zoals aId, omschrijving, hoeveel geklikt er nog over zijn, enz.

Tabel: ads_clicked met userid, advertentieid, datum&tijd.

Als iemand op een ad klikt, insert hij dus in de tabel ads_clicked het userid + aId en zet hij ook clicked op 1.

Wat ik wil is het volgende:

De ads ophalen en die orderen by niet geklikt.

Dus eerst de ads weergeven die niet geklikt zijn (die niet voorkomen in de tabel ads_clicked) en als laatste de geklikte ads (die wel in de tabel ads_clicked staan).

Ik heb al verschillende dingen geprobeerd met bijv left join, maar dit wil helaas niet helpen.

Bijv:

1
2

SELECT * FROM ads LEFT JOIN ads_clicked ON ads.aId = ads_clicked.aId   
WHERE aClicksLeft1 > 0 OR aClicksLeft2 > 0 OR aClicksLeft3 > 0 AND aApproved > 0 AND aPaused=0 AND aUpgraded=0 ORDER BY ads_clicked.clicked DESC

rekenen binnen select sql

Fri, 03 Feb 2012 14:42:20 +0000

Is het mogelijk om een binnen een select uit een sql een berekening te doen en weer te geven?

Ik heb nl al dat hij de tijd omzet:

TIME_FORMAT((uren.tijd_eind - uren.tijd_begin - uren.tijd_pauze),'%H:%i') AS tijd_totaal,
SUM((TIME_TO_SEC(uren.tijd_eind) - TIME_TO_SEC(uren.tijd_begin) - TIME_TO_SEC(uren.tijd_pauze))/3600) AS totaal,

maar ik wil dan ook dat hij het "totaal" x "uurtarief" doet?

ALTER ENUM TYPE (postgresql)

Fri, 03 Feb 2012 09:35:27 +0000

Ik heb het volgende TYPE:
CREATE TYPE rekening_type AS ENUM ('factuur','herinnering');

Dit wil ik graag veranderen in:
CREATE TYPE rekening_type AS ENUM ('debetfactuur','herinnering','creditfactuur');

Hoe kan ik dit doen?

Zonder de afhankelijke tabellen en views te verwijderen

Mail versturen als bod hoger is

Thu, 02 Feb 2012 15:17:41 +0000

Hi,

Ik ben een beetje aan het rommelen met een site om opdrachten voor anderen te doen. Een soort freelance site.
Nu wil ik als persoon x een bod van 10 euro gedaan heeft en persoon y doet vervolgens een bod van 12 euro dat persoon x een e-mail krijgt met de boodschap dat er een hoger bod geplaatst is.

Mijn gedachte is om uit de tabel de ingegeven waarde van persoon y te vergelijken met alle personen die boden, en dan de biedingen die lager zijn te retourneren en een e-mail te sturen.

Helaas, helaas, ik krijg het niet voor elkaar. Kan iemand mij een opzetje geven, op weg helpen of een voorbeeld geven. Ik breek me er de hele dag al over.

Ik hoor het graag. Dit is wat ik heb:

Functie hoogste bod:

1
2
3
4
5
6
7

        function vind_hoogste_bod($pro_id,$amount)
        {
            global $db;
            $sql= " SELECT * FROM ".BIED_LIJST." AS BL "
                 ." LEFT JOIN ".LEDEN_LIJST." AS LL ON BL.bid_by_user = LL.user_login_id "
                 ." WHERE BL.project_id = '".$pro_id."' AND BL.bied_hoogte > '".$amount."' AND BL.waarschuwing_aan = 1 ";
               $db->query($sql);

waarschuwing_aan plaats ik als aanvink optie bij het plaatsen van de opdracht.

Alleen, hoe krijg ik het nu voor elkaar dat de resultaten van de hoogste_bod functie een e-mail krijgen?

1	$result = $project->vind_hoogste_bod($_POST['project_id'],$_POST['bied_hoogte']);

Zit ik op het goede spoor of denk ik verkeerd? Heeft iemand wellicht een idee?

Ik hoor het graag!

Groet,

Denny

Versiebeheer van teksten - hoe verwerken in de database?

Thu, 02 Feb 2012 08:42:21 +0000

Hi Iedereen,

Binnen ons CMS loop ik tegen een puntje aan m.b.t. de database structuur.
Ik heb binnen een website allerlei modules gemaakt en per module wil ik alle versie bewaren van teksten e.d. Op dit moment heb ik deze functionaliteit gemaakt met de volgende structuur:

- Id: int(11)
- Lang: char(2)
- Versie: datetime()
- Versie_end: datetime()
- Title: varchar(255)

Aan de hand van Versie_end kon je de laatste versie opvragen, want deze versie had NULL bij de betreffende kolom. Nu heb ik gemerkt dat deze werkwijze bij grote klanten ervoor zorgt dat de database flink groeit.

Doordat de database-tabellen flink groeien heb ik het idee dat de website ook traag wordt. Nu ben ik een groot gedeelte van het CMS aan het verbouwen en wil dit punt (m.b.t. traagheid) ook oppakken.

Mijn vraag is eigenlijk hoe ik het beste om kan gaan met versies binnen een tabel. Toch werken met versie_end of een tabel maken (history_*) waarin oude versies worden opgeslagen?

Ik hoor graag van jullie.

select not exists in vraag

Wed, 01 Feb 2012 16:24:40 +0000

Heb een table products die ik op het veld products_id wil vergelijken met de table products_content.
Indien products_id niet voorkomt in de table products_content toon dan p.products_id + p.products_model

Bij mij blijft de query leeg als result.
Uiteraard doe ik wat verkeerd maar wat?

1
2
3
4
5
6
7
8
9

select 
p.products_id,
p.products_model
from 
products p
where 
p.manufacturers_id = 20 and
not EXISTS
(select products_id from  products_content )

Algoritme voor users met zelfde filmsmaak

Wed, 01 Feb 2012 09:05:14 +0000

Ik dacht even dat ik er was (Pg_vincent had me al een eind op weg geholpen), maar ik kom er toch niet uit.

Ik wil het gemiddelde verschil in rating berekenen tussen users die dezelfde films hebben bekeken. Zie hieronder waar ik naar op zoek ben.

Table: users
id
name

Table: ratings
id
user_id
movie_id
rating

Table: movies
id
name

Gewenst resultaat:

1
2
3
4
5
6
7
8
9
10
11

╔═══════════╦═══════════╦════════════════╦══════════════════════╗
║ user_id_1 ║ user_id_2 ║ avg_difference ║ nr_of_similar_movies ║
╠═══════════╬═══════════╬════════════════╬══════════════════════╣
║ 1         ║ 1         ║ 0.0            ║ 58                   ║
║ 1         ║ 2         ║ 1.3            ║ 15                   ║
║ 1         ║ 3         ║ 0.5            ║ 1                    ║
║ 1         ║ 4         ║ 2.5            ║ 3                    ║
║ 2         ║ 1         ║ 0.5            ║ 8                    ║
║ 2         ║ 2         ║ 0.0            ║ 83                   ║
║ ...       ║ ...       ║ ...            ║ ...                  ║
╚═══════════╩═══════════╩════════════════╩══════════════════════╝

Het stukje avg_difference en nr_of_similar_movies lukt volgens mij wel met het onderstaande:

SELECT AVG(ABS(ratings_a.rating - ratings_b.rating)) AS avg_difference COUNT(movies.id) AS nr_of_similar_movies

Ik loop alleen vast op alle combinaties tussen user_1 en user_2.

Mysql Meerdere keren AND en OR

Tue, 31 Jan 2012 22:09:20 +0000

Ik wil het volgende:

Records ophalen uit een tabel wanneer stad een bepaalde waarde heeft of wanneer er voldaan wordt aan 2 tijdspannes. Ik heb de volgende code:

1
2
3
4
5

SELECT * FROM hhs
WHERE stad = '".$_REQUEST['dwa']."'
AND
((dagbegin = ".$nudag." AND tijdbegin =< ".$nutijd.") OR (dageinde = ".$nudag." AND tijdeinde => ".$nutijd."))
ORDER BY dagbegin, tijdbegin

Dit geeft helaas de volgende melding

1	mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Ik heb al lopen klooien met de ( )'s maar ik krijg het niet goed. Zoeken op Mysql AND OR in Google levert ook niets duidelijks op. Wie kan me helpen?

inschakelen/uitschakelen onderdeel

Tue, 31 Jan 2012 21:01:23 +0000

Beste,

via een admin had ik graag bepaalde onderdelen van de site aan of uitgezet, dit via een link:

\n"; } else { echo " \n"; }

1
2
3
4
5
6
7
8

    
if ($panel_status == "0") {
    echo "

\n"; echo "Aan

\n"; echo "Uit

door het klikken op de actieve link had ik graag een query die de status op 1 of 0 zet

Alvast bedankt

met mysql update een bepaalde value weghalen

Tue, 31 Jan 2012 19:29:40 +0000

Hallo,

Ik heb een custom script gedownload (een klik script).
Dit script slaat veel data op in de tabel users.
Ik heb dus een tabel users met de username, voornaam en nog meer gegevens, maar ook een TEXT veld ads_clicked.
Hier slaat hij alle ad_ids op met een scheidingsteken ertussen.

Bijv: 29-31-34-33-32-35-40-44-47-48-

Nu wil ik 1 variable daar uit verwijderen.. (bijv: 44-) via een cronjob zodat leden deze ad elke dag kunnen klikken.

Dit doormiddel van de mysql update functie.

Hoe kan ik dit voor elkaar krijgen?

rekenen met procenten

Sat, 28 Jan 2012 13:55:01 +0000

Hallo,
Ik heb een probleem met het rekenen met procenten
ik heb een kolom ex_btw hier komt een bedrag in te staan exclusief btw en een kolom btw dit kan zijn 19% of 6%
deze twee kolommen wil ik met elkaar vermenigvuldigen zoiets als
Als ik ik kolom ex_btw het bedrag 25 invul en in de kolom btw 19% krijg ik 475 te staan
Dit moet eigenlijk 4,75 zijn
Tevens moet hij dit bedrag optellen bij het getal 25
Dus uiteindelijk moet er komen te staan 29,75
Hoe krijg ik dit voor elkaar.
Alvast bedankt voor de reactie

Hans

MySQL "drop index" versnellen?

Fri, 27 Jan 2012 17:34:37 +0000

Op een tabel van 35GB zijn een groot aantal dubbele en overbodige indexen aangemaakt. Vraag me niet waarom, maar dat is ooit zo gedaan. Nu wil ik met een DROP INDEX de diverse overbodige indexen verwijderen, maar dat levert het volgende resultaat op:
- MySQL gaat een nieuwe datafile aanmaken (betreft MyISAM, dus tabel.MYD) van 35GB
- MySQL gaat een nieuwe indexfile aanmaken, het tabel.MYI bestand, van minmaal 30GB (laatste stand van zaken)
- MySQL schrijft vreselijk langzaam op een razendsnel SAN (800MB per seconde is mogelijk, MySQL doet "pieken" van maximaal 20MB/s)

Is er een mogelijkheid om dit proces een factor 1000 te versnellen? Na drie uur wachten hield ik het voor gezien, maandag is er weer een dag.

Het is duidelijk dat de keuze voor één bestand met alle indexen niet handig is in deze opzet, maar waarom in hemelsnaam een nieuw databestand aanmaken? En hoe kan ik de schrijfsnelheid van MySQL aanzienlijk verbeteren? PostgreSQL en MySQL draaien op exact dezelfde hardware met ieder zijn eigen dedicated SAN, en dezelfde RHEL 5.4 configuratie. Omzetten van MyISAM naar innoDB is geen optie, dat maakt de boel nóg veel langzamer. Voor een datawarehouse is de betrouwbaarheid van innoDB ook niet zo'n issue, er zijn backups beschikbaar voor het geval dat.

PostgreSQL doet er op dezelfde hardware enkele seconden over om een index te droppen van dezelfde omvang, voor grap nog even getest. Het schrijven met PostgreSQL gaat ook met 400/500 MB's, richting het maximaal haalbare op deze hardware.

eerste rij van het cvs bestand laad niet in

Tue, 24 Jan 2012 15:54:35 +0000

Ik probeer (voor het eerst) een cvs bestand in mijn mysql database in te laden alleen de eerste kolom wordt neit meegenomen en als alles is ingeladen in de database dat is de eerst regel gevuld met waarde die ik niet in mijn cvs bestand heb staan.

Hoe kan ik een schoon en compleet cvs bestand overzetten?

Thanxs Betsie

Kan PostgreSQL backup niet importeren

Tue, 24 Jan 2012 13:23:41 +0000

Hallo,

Ik probeer een PostgreSQL databank van de ene Ubuntu-installatie naar de andere te krijgen. De installaties zijn vrijwel identiek (beiden Ubuntu 11.10 met alle software uit de repositories en alle wachtwoorden hetzelfde) dus ik had niet verwacht dat het zo moeilijk zou zijn. Ik wordt echter met allerhande foutmeldingen om de oren geslagen.

Ik heb mbv PGadmin III een backup gedaan van de volledige database met als format-optie "compress". Het resulterende bestand heb ik overgezet en probeer mbv de Restore-optie van PGadminIII de database nu weer in te laden. Hierbij krijg ik echter de volgende melding:

Quote

pg_restore: [archiver] WARNING: invalid creation date in header
pg_restore: [archiver] entry ID -1280 out of range -- perhaps a corrupt TOC

Over een "corrupt TOC" kan ik vrijwel niets vinden en heb dus ook geen idee wat het is.

Ik heb de backup meerdere malen gedaan, elke keer gecheckt of het bestand op de nieuwe machine wel exact evenveel bytes had en ook op de bron machine geprobeerd om het geëxporteerde bestand meteen weer te importeren. Alles lijkt prima in orde en ik weet dus echt niet wat er nou fout is.

Iemand misschien enig idee wat ik fout gedaan zou kunnen hebben?

[EDIT]
Ik heb trouwens ook geprobeerd om een export te doen vanaf de command line in psql mbv pg_dump:

1	pg_dump test_db > /home/kramer65/Desktop/pg_dump

Hierbij krijg ik echter een foutmelding die zegt: syntax error at or near "pg_dump"

Vacatures sorteren op relevantie

Tue, 24 Jan 2012 11:42:31 +0000

Ik heb inmiddels een query die het gewenste resultaat geeft, maar volgens mij gaat dit met 10000+ records straks niet werken. Ik ben dus benieuwd of jullie me kunnen helpen onderstaand verhaal te optimaliseren.

Tabellen:
vacature
- id
- naam

vaardigheden
- id
- naam

vacature_vaardigheden
- id
- vacature
- vaardigheid

Situatie
Ik heb de gegevens van een kandidaat opgehaald die over vaardigheid id 1 en 2 beschikt. Een kandidaat moet over minimaal 50% van de gevraagde vaardigheden beschikken om een vacature als match te kunnen beshouwen.

Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT
  vacature AS vacatureId,
  (SELECT COUNT(vaardigheid) FROM vacature_vaardigheden WHERE vaardigheid IN(1,2) AND vacature = vacatureId) / (COUNT(vaardigheid)) AS vaardighedenMatch

FROM
  `vacature_vaardigheden`

GROUP BY 
  vacature

HAVING
  vaardighedenMatch >= 0.5

ORDER BY 
  vaardighedenMatch DESC

Ik ben benieuwd of iemand me hiermee kan helpen. Alvast bedankt!

Group By, Order By en LIMIT combineren

Mon, 23 Jan 2012 19:18:28 +0000

Hi Forumgenoten,

Het is inmiddels 20.00u geweest en ik heb nog steeds mijn MySQL probleem van deze ochtend niet opgelost, wat hulp is meer dan welkom .

Ik heb een berichten tabel met daarin ~3 miljoen records. De structuur van deze tabel is als volgt:

1
2
3
4
5
6
7
8
9

bericht
-------
id - het auto increment id nummer van het bericht
van - het lid id van de afzender
naar - het lid id van de ontvanger
datum - datum waarop het bericht verzonden is in unix timestamp formaat (berichten kunnen in de toekomst worden verzonden)
bericht - de daadwerkelijke inhoud van het bericht
gelezen - 0/1: of de ontvanger het bericht al heeft gelezen
verwijderd - 0/1: of de ontvanger het bericht al heeft verwijderd

Uit deze tabel wil ik graag de unieke combinaties van "van" en "naar" ophalen gesorteerd op de datum als DESC, de query die ik hiervoor gebruik is:

1
2
3

SELECT id, van, naar, FROM_UNIXTIME(datum) AS datum, bericht, gelezen FROM bericht ";
WHERE (naar = ".$ingelogd." OR van = ".$ingelogd.") AND datum <= ".$tijd." AND verwijderd = 0 
GROUP BY van, naar ORDER BY datum DESC LIMIT ".$limit1.", ".$aantalResults1;

Helaas geeft deze query niet de resultaten die ik verwacht en waar ik ook geen logica in kan ontdekken. Op pagina 1 (LIMIT 0, 10) zie ik unieke combinaties maar meestal niet het meest recente bericht van deze combinaties.

Vervolgens op pagina 2 (LIMIT 10, 10) zijn zelfs de combinaties tussen van en naar niet meer uniek.

Ik maak gebruik van Mysql Versie 5.1.41 (op mijn laptop waarop ik dit bouw). De uiteindelijke productie omgeving heeft Mysql versie 5.0.77. Er bestaat hiermee een risico dat de query zich op de productie omgeving weer anders gaat gedragen i.v.m. een Mysql-bug: http://bugs.mysql.com/bug.php?id=32202.

Wat kan ik het beste doen om toch het gewenste resultaat te krijgen ?

Groeten,
Dirk