Forum PHP.pl

Witam po raz pierwszy
Niestety poległem w prostej sprawie, jak mi się przynajmniej wydawało..:
Mam następującą tabelę zakupu2014:


|id |indeks |nazwa_towaru |jm |il0osc |cena_zakupu |nazwa_kontrahenta |data_realizacji|
|0 |121 |aaa |kg |12 |12.1 |hh |2014-01-24|
|1 |121 |aaa |kg |12 |13.1 |hh |2014-01-23|
|2 |122 |bbb |szt |12 |14.1 |hh |2014-01-24|
|3 |121 |aaa |kg |12 |10.1 |hh |2014-01-01|
|4 |122 |bbb |szt|12 |9.1 |hh |2014-01-10|
|5 |122 |bbb |szt|12 |1.1 |hh |2014-01-12|

potrzebuje osiągnąc taki wynik jak daje:
Select indeks,cena_zakupu,avg(cena_zakupu),min(cena_zakupu)
from zakupy2014
group by indeks

ale zamiast np avg ostatnią cenę zakupu, przedostatnia cenę zakupu i przedprzedostatnia cenę zakupu -w jednym rekordzie jak to jest właśnie w tym zapytaniu
Oczywiście biorę po uwagę wariant ze zbudowaniem osobnej tabeli z takimi wynikami.
Podkreślam, że dla niektórych indeksów może być tylko ostatnia cena_zakupu.
Ma ktoś pomysł jak to rozwiązać?
Pozdrawiam

Jedyne rozwiązanie, jakie mi przychodzi do głowy, to leftjoin z samym sobą, ale to na pewno nie będzie bardzo wydajne.

[SQL] pobierz, plaintext 
SELECT tabelka.*, tabelka_1.cena AS cena_1, tabelka_2.cena AS cena2, tabelka_3.cena AS cena_3 FROM tabelka
LEFT JOIN tabelka tabelka_1 ON tabelka.id=(tabelka.id+1)
LEFT JOIN tabelka tabelka_2 ON tabelka.id=(tabelka.id+2)
LEFT JOIN tabelka tabelka_3 ON tabelka.id=(tabelka.id+3)
WHERE <warunki>
[SQL] pobierz, plaintext 

Powyższego nie sprawdziłem w praktyce, więc może coś działać źle, ale generalnie idea jest poprawna. Tyle, że powyższy przypadek będzie miał problem, jeśli coś usuniesz z tabeli, wówczas trzeba by go jeszcze bardziej skomplikować.



Chyba, że potrzebujesz tą średnią + ostatnie 3 wyniki, to wówczas po prostu zrób 2 zapytania, powinno to działać szybciej, niż na tych joinach.

Dzięki za podpowiedź ale dalej nie wiem jak skonstrowac warunki do tego, żeby było poprawnie. Piszesz, że można to rozwiązać przez 2 zapytania i szybciej to działa..przy ilości rekordów sięgających nawet 200 000 moze być to istotne. Mógłbyś rozwinąc ten temat.
Pozdrawiam

No, te 2 zapytania to najprościej, jak się da.
Twoje zapytanie, które już masz, i drugie jeszcze prostsze, czyli

[SQL] pobierz, plaintext 
SELECT <co_ci_trzeba> FROM tabela ORDER BY id DESC LIMIT 3
[SQL] pobierz, plaintext 

Chyba, że źle zrozumiałem, o co ci chodzi

[SQL] pobierz, plaintext 
SELECT <co_ci_trzeba> FROM tabela ORDER BY id DESC LIMIT 3
[SQL] pobierz, plaintext 

To zapytanie zwróci informacje o trzech wierszach największym id. Wcale nie muszą to być trzy ostatnie zakupy.

Same zapytanie jest niepoprawne więc warunki w nim nic nie pomogą.

Swoją drogą to tabela też nie jest poprawna - np. możesz dopisać towar raz w litrach, a przy kolejnym zakupie w kg. Chyba, że jest to celowe. Jeśli nie to poczytaj o postaciach normalnych.

Jeśli zależy ci na wydajności to trzy ostatnie ceny możesz przechowywać w osobnej tabeli. Ich aktualizacje najwygodniej obsłużyć bezpośrednio w bazie danych, za pomocą wyzwalaczy.

[SQL] pobierz, plaintext 
CREATE INDEX indeks
ON zakupy2014 (indeks);
 
CREATE TABLE `ostatnie_ceny` (
  `indeks` int(11) NOT NULL,
  `cena_ost` decimal(8,2) NOT NULL,
  `cena_przedost` decimal(8,2) DEFAULT NULL,
  `cena_przedprzedost` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`indeks`),
  FOREIGN KEY (`indeks`) REFERENCES `zakupy2014` (`indeks`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
 
DROP TRIGGER IF EXISTS ostatnie_ceny;
DELIMITER //
 
CREATE TRIGGER ostatnie_ceny AFTER INSERT ON zakupy2014 FOR EACH ROW
BEGIN
    DECLARE v_cena_przedost, v_cena_przedprzedost DECIMAL(8,2) DEFAULT NULL;
 
    SELECT cena_ost, cena_przedost INTO v_cena_przedost, v_cena_przedprzedost
    FROM ostatnie_ceny
    WHERE indeks = NEW.indeks;
 
    REPLACE ostatnie_ceny (indeks, cena_ost, cena_przedost, cena_przedprzedost)
    VALUE (NEW.indeks, NEW.cena_zakupu, v_cena_przedost, v_cena_przedprzedost);
END//
DELIMITER ;
[SQL] pobierz, plaintext 

Trzeba wziąć pod uwagę, że dane w tabeli mogą być zaktualizowane lub usunięte więc i dla tych zdarzeń trzeba napisać wyzwalacze. Jeśli masz pewnośc, że nie będzie żadnych aktualizacji lub kasowania wtedy to co jest wystarczy.

Dzięki bo już zastanawiałem sie nad pivot table żeby zrobic. Mam jednak problem:
CREATE TABLE `ostatnie_ceny` (
`indeks` int(11) NOT NULL,
`cena_ost` decimal(8,2) NOT NULL,
`cena_przedost` decimal(8,2) DEFAULT NULL,
`cena_przedprzedost` decimal(8,2) DEFAULT NULL,
PRIMARY KEY (`indeks`),
FOREIGN KEY (`indeks`) REFERENCES `zakupy2014` (`indeks`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
MySQL zwrócił komunikat: Dokumentacja

#1005 - Can't create table 'carboceny.ostatnie_ceny' (errno: 150) (Szczegóły…)

Co może być nie tak?

Nie wiem czy to istotne, ale w tabeli kolumne indeks musialem zmienic na varchar bo niestety kolumna ta może posiadać znaki wymieszane z cyframi:/ Co do budowy bazy to budowa jest importowana z pewnego "magicznego" systemu (skazana jest na jego ułomności) i nie będzie ulegała zmianie czy usuwaniu rekordów. Jako ciekawostka dodam, że poszczególne jm moga być litr i l, ale to temat na kiedy indziej.
BTW Teraz zauważyłem, że wątek ująłem nie w tym dziale co powinienem za co przepraszam.

Kolumna indeks musi być w obu tabelach tego samego typu. Poza tym na tą w tabeli zakupy2014 musi być nałożony indeks.

[SQL] pobierz, plaintext 
CREATE INDEX indeks
ON zakupy2014 (indeks);
[SQL] pobierz, plaintext 

No tak myslalem, wiec ten wariant tez sprawdzilem jak tylko ten bład mi wyskoczył. Po uruchomieniu kodu, indeksy założyła ta procedura przez ciebie napisana. Zatrzymuje sie na create table..



CREATE TABLE `ostatnie_ceny` (
`indeks` varchar(15) NOT NULL,
`cena_ost` decimal(8,2) NOT NULL,
`cena_przedost` decimal(8,2) DEFAULT NULL,
`cena_przedprzedost` decimal(8,2) DEFAULT NULL,
PRIMARY KEY (`indeks`),
FOREIGN KEY (`indeks`) REFERENCES `zakupy2014` (`indeks`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
MySQL zwrócił komunikat: Dokumentacja

#1005 - Can't create table 'carboceny.ostatnie_ceny' (errno: 150) (Szczegóły…)

Kod błędu ewidentnie mówi, że jest problem z kluczem obcym. Napisz co zwróci zapytanie.

[SQL] pobierz, plaintext 
SHOW CREATE TABLE zakupy2014
[SQL] pobierz, plaintext 

Takie cuś
Table
Create Table
zakupy2014
CREATE TABLE `zakupy2014` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`indeks` varchar(15) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
`nazwa_towaru` text CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
`jm` tinytext CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
`ilosc` float NOT NULL,
`cena_zakupu` float NOT NULL,
`nazwa_kontrahenta` tinytext CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
`data_realizacji` date NOT NULL,
PRIMARY KEY (`id`),
KEY `indeks` (`indeks`)
) ENGINE=InnoDB AUTO_INCREMENT=11539 DEFAULT CHARSET=latin1

Więc jednak kolumny indeks w obu tabelach nie są tego samego typu. Popraw to i wtedy zadziała.

[quote name='diodadioda' date='17.06.2014, 18:24:14 ' post='1111477']
CREATE TABLE `ostatnie_ceny` (
`indeks` varchar(15) NOT NULL,

no w kodzie co uruchamiam jest varchar(15)

CREATE TABLE `ostatnie_ceny` (
`indeks` varchar(15) NOT NULL,
`cena_ost` decimal(8,2) NOT NULL,
`cena_przedost` decimal(8,2) DEFAULT NULL,
`cena_przedprzedost` decimal(8,2) DEFAULT NULL,
PRIMARY KEY (`indeks`),
FOREIGN KEY (`indeks`) REFERENCES `zakupy2014` (`indeks`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

DROP TRIGGER IF EXISTS ostatnie_ceny;
DELIMITER //

CREATE TRIGGER ostatnie_ceny AFTER INSERT ON zakupy2014 FOR EACH ROW
BEGIN
DECLARE v_cena_przedost, v_cena_przedprzedost DECIMAL(8,2) DEFAULT NULL;

SELECT cena_ost, cena_przedost INTO v_cena_przedost, v_cena_przedprzedost
FROM ostatnie_ceny
WHERE indeks = NEW.indeks;

REPLACE ostatnie_ceny (indeks, cena_ost, cena_przedost, cena_przedprzedost)
VALUE (NEW.indeks, NEW.cena_zakupu, v_cena_przedost, v_cena_przedprzedost);
END//
DELIMITER ;

Chyba ze coś przeoczylem..moze musi byc inny typ zmiennej, ale jaki co by też znaki obsłużył?

Przepraszam ...
Teraz powstaly indeksy, wyzwalacze i ..pusta tabela:

CREATE INDEX indeks
ON zakupy2014 (indeks);# MySQL zwrócił pusty wynik (zero wierszy).

CREATE TABLE `ostatnie_ceny` (
`indeks` varchar(15) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
`cena_ost` decimal(8,2) NOT NULL,
`cena_przedost` decimal(8,2) DEFAULT NULL,
`cena_przedprzedost` decimal(8,2) DEFAULT NULL,
PRIMARY KEY (`indeks`),
FOREIGN KEY (`indeks`) REFERENCES `zakupy2014` (`indeks`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;# MySQL zwrócił pusty wynik (zero wierszy).


DROP TRIGGER IF EXISTS ostatnie_ceny;# MySQL zwrócił pusty wynik (zero wierszy).

DELIMITER //

CREATE TRIGGER ostatnie_ceny AFTER INSERT ON zakupy2014 FOR EACH ROW
BEGIN
DECLARE v_cena_przedost, v_cena_przedprzedost DECIMAL(8,2) DEFAULT NULL;

SELECT cena_ost, cena_przedost INTO v_cena_przedost, v_cena_przedprzedost
FROM ostatnie_ceny
WHERE indeks = NEW.indeks;

REPLACE ostatnie_ceny (indeks, cena_ost, cena_przedost, cena_przedprzedos[...]

No super Teraz już wiem dlaczego nie mogę usuwać wierszy..
Wielkie Dzieki, Szacun za wiedzę.
Wreszcie sie wyśpie