Temat: Baza danych problem z jednym zapytaniem

Witam mam problem z jednym zapytaniem:

/*
Lista 10 uslug przynoszacych najwieksze wplywy
operatorom w ciagu ostatnich 5 lat kalendarzowych
id uslugi nazwauslugi kwota wplywow
lista obejmuje tylko te uslugi ktore sa dostepne
u min 5 operatorow i skorzystalo z nich min 1000 klientow(roznych)
*/

Schemat bazy danych :
https://www.dropbox.com/s/c90fdixgbxwn423/Physical.jpg
[obrazek]
Koceptual:
https://www.dropbox.com/s/v8etnf9a57bpao5/Billing.jpg




Poprzednie zapytania poszły mam problem z tym ostatnim nie wiem jak podejść
Wczesniejsze rozwiazywalem w ten sposob moze nieoptymalnie ale nie jestem ekspertem dopiero sie ucze

/*

ZAPYTANIE 3

Najbardziej popularne modele telefonu (Idmodelu, NazwaModelu, NazwaMarki,

Liczba wystąpień) w poprzednim roku kalendarzowym(Umowa.Data_start)



*/

Select  TEL_MODEL.IDMODELU,TEL_MODEL.NAZWAMODELU,TEL_MODEL.NAZWAMARKI, COUNT(U.IDMODELU) AS LW

FROM UMOWA AS U 

INNER JOIN (SELECT T.IDMARKI, T.IDMODELU,T.IDTELEFONU, M.NAZWAMODELU, MAR.NAZWAMARKI

                FROM TELEFON AS T

                INNER JOIN MODEL AS M 

                ON M.IDMODELU = T.IDMODELU AND M.IDMARKI = T. IDMARKI

                INNER JOIN MARKA AS MAR

                ON MAR.IDMARKI = T.IDMARKI

                ) as TEL_MODEL ON U.IDTELEFONU = TEL_MODEL.IDTELEFONU

WHERE DATEDIFF(year,U.DATA_START,now()) >= 1

GROUP BY TEL_MODEL.IDMODELU,TEL_MODEL.NAZWAMODELU,TEL_MODEL.NAZWAMARKI

ORDER BY LW DESC





/*

    Zapytanie 2

Lista klientow (idklienta, nazwisko,imie,) oraz laczna kwota wystawionych rachunkow

za ostatni rok kalendarzowy - tylko tych klientow ktorych kwota uslug przekracza 

1000 PZL - liste posortowac wg kwoty rachunu malejaca

Dane z encji Uslugi wykonane kazdegi 1 miesiaca sa przenoszone do encji historiauslug





*/



SELECT K_U_T.ID_KLIENTA, K_U_T.Nazwisko, SUM(US_K.CENA) AS S

FROM( SELECT K.ID_KLIENTA,K.NAZWISKO,T.IDTELEFONU

        FROM KLIENT AS K

        INNER JOIN UMOWA AS U ON U.ID_KLIENTA = K.ID_KLIENTA

        INNER JOIN TELEFON AS T ON T.IDTELEFONU = U.IDTELEFONU

) AS K_U_T

INNER JOIN ( SELECT U.CENA, HU_UW.IDTELEFONU

                FROM( SELECT HU.IDUSLUGI, HU.IDRACHUNKU, HU.IDTELEFONU

                        FROM HISTORIAUSLUG AS HU

                        UNION ALL

                      SELECT UW.IDUSLUGI, UW.IDRACHUNKU, UW.IDTELEFONU

                        FROM USLUGIWYKONANE AS UW

                )AS HU_UW

                    INNER JOIN USLUGA AS U ON HU_UW.IDUSLUGI = U.IDUSLUGI

                    INNER JOIN RACHUNEK AS R ON HU_UW.IDRACHUNKU = R.IDRACHUNKU

                    INNER JOIN JEDNOSTKAMIARY AS JM ON JM.IDJM = U.IDJM

                    WHERE JM.NAZWAJM = 'PLZ' AND Year(R.datawystawienia) = Year(NOW()) -1

) AS US_K ON K_U_T.IDTELEFONU = US_K.IDTELEFONU

  GROUP BY K_U_T.ID_KLIENTA, K_U_T.NAZWISKO

  HAVING S > 1000

  ORDER BY S DESC



              

Czy moje sposoby podejcie jest wlasciwe ??
Uzywac moge tylko SQL-92Ten post został edytowany przez Autora dnia 03.01.14 o godzinie 22:31

Temat: Baza danych problem z jednym zapytaniem

Masz może skrypt do tej bazy i przykładowe dane testowe? Chętnie bym Ci pomógł w optymalnej konstrukcji zapytań, ale nie che mi się za bardzo klepać SQL-a by stworzyć ową bazkę.

Pozdrawiam

Temat: Baza danych problem z jednym zapytaniem

Mogę w PowerDesignerze wygenerować BD oraz jakies test data jak oczywisccie PD prawidlowo to zrobi bo czest o jakos tak potem ze leca inserty a wyskakuje primary key not found for .... dla jakiejs zwiazanej tabeli , z kluczami ma problem jak sie uda to ci podesle
Tutaj mam tylko mysql i nietestowane przez mnie bo uzywam Sybase

     DROP TABLE IF EXISTS GRUPAO;



    DROP TABLE IF EXISTS HISTORIAUSLUG;



    DROP TABLE IF EXISTS JEDNOSTKAMIARY;



    DROP TABLE IF EXISTS KLIENT;



    DROP TABLE IF EXISTS MARKA;



    DROP TABLE IF EXISTS MODEL;



    DROP TABLE IF EXISTS OPERATOR;



    DROP TABLE IF EXISTS RACHUNEK;



    DROP TABLE IF EXISTS TELEFON;



    DROP TABLE IF EXISTS UMOWA;



    DROP TABLE IF EXISTS USLUGA;



    DROP TABLE IF EXISTS USLUGIWYKONANE;



    CREATE TABLE GRUPAO

    (

    IDGRUPYO INT NOT NULL,

    NAZWAGRUPYO CHAR(50),

    PRIMARY KEY (IDGRUPYO)

    );



    CREATE TABLE HISTORIAUSLUG

    (

    IDUSLUGI INT NOT NULL,

    IDMARKI INT NOT NULL,

    IDMODELU INT NOT NULL,

    IDTELEFONU INT NOT NULL,

    IDRACHUNKU INT NOT NULL,

    CZASSTART TIMESTAMP,

    CZASKONIEC TIMESTAMP,

    PRIMARY KEY (IDMARKI, IDMODELU, IDUSLUGI, IDTELEFONU, IDRACHUNKU)

    );



    CREATE TABLE JEDNOSTKAMIARY

    (

    IDJM INT NOT NULL,

    JED_IDJM INT,

    NAZWAJM CHAR(30),

    SYMBOLJM CHAR(5),

    PRZELICZNIK NUMERIC(15,2),

    PRIMARY KEY (IDJM)

    );



    CREATE TABLE KLIENT

    (

    IDKLIENTA INT NOT NULL,

    NAZWISKO CHAR(30),

    PRIMARY KEY (IDKLIENTA)

    );



    CREATE TABLE MARKA

    (

    IDMARKI INT NOT NULL,

    NAZWAMARKI CHAR(30),

    PRIMARY KEY (IDMARKI)

    );



    CREATE TABLE MODEL

    (

    IDMARKI INT NOT NULL,

    IDMODELU INT NOT NULL,

    PRIMARY KEY (IDMARKI, IDMODELU)

    );



    CREATE TABLE OPERATOR

    (

    IDGRUPYO INT NOT NULL,

    IDOPERATORA INT NOT NULL,

    NAZWAOPERATORA CHAR(30),

    NIP CHAR(20),

    PRIMARY KEY (IDGRUPYO, IDOPERATORA)

    );



    CREATE TABLE RACHUNEK

    (

    IDRACHUNKU INT NOT NULL,

    DATAWYSTAWIENIA DATE,

    DATAPLATNOSC DATE,

    PRIMARY KEY (IDRACHUNKU)

    );



    CREATE TABLE TELEFON

    (

    IDMARKI INT NOT NULL,

    IDMODELU INT NOT NULL,

    IDTELEFONU INT NOT NULL,

    KOLOR CHAR(20),

    NRTELEFONU CHAR(20),

    PRIMARY KEY (IDMARKI, IDMODELU, IDTELEFONU)

    );



    CREATE TABLE UMOWA

    (

    IDKLIENTA INT NOT NULL,

    IDGRUPYO INT NOT NULL,

    IDOPERATORA INT NOT NULL,

    IDMARKI INT NOT NULL,

    IDMODELU INT NOT NULL,

    IDTELEFONU INT NOT NULL,

    NRUMOWY CHAR(20),

    DATASTART DATE,

    DATAKONIEC DATE,

    UWAGI CHAR(100),

    WAZNA BOOL,

    PRIMARY KEY (IDMARKI, IDMODELU, IDGRUPYO, IDKLIENTA, IDOPERATORA, IDTELEFONU)

    );



    CREATE TABLE USLUGA

    (

    IDUSLUGI INT NOT NULL,

    IDJM INT NOT NULL,

    NAZWAUSLUGI CHAR(30),

    CENA NUMERIC(15,2),

    VAT NUMERIC(7,2),

    PRIMARY KEY (IDUSLUGI)

    );



    CREATE TABLE USLUGIWYKONANE

    (

    IDMARKI INT NOT NULL,

    IDMODELU INT NOT NULL,

    IDTELEFONU INT NOT NULL,

    IDUSLUGI INT NOT NULL,

    IDRACHUNKU INT NOT NULL,

    CZASSTART TIMESTAMP,

    CZASKONIEC TIMESTAMP,

    PRIMARY KEY (IDMARKI, IDMODELU, IDTELEFONU, IDUSLUGI, IDRACHUNKU)

    );



    ALTER TABLE HISTORIAUSLUG ADD CONSTRAINT FK_HISTORIAUSLUG FOREIGN KEY (IDUSLUGI)

    REFERENCES USLUGA (IDUSLUGI) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE HISTORIAUSLUG ADD CONSTRAINT FK_HISTORIAUSLUG2 FOREIGN KEY (IDMARKI, IDMODELU, IDTELEFONU)

    REFERENCES TELEFON (IDMARKI, IDMODELU, IDTELEFONU) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE HISTORIAUSLUG ADD CONSTRAINT FK_HISTORIAUSLUG3 FOREIGN KEY (IDRACHUNKU)

    REFERENCES RACHUNEK (IDRACHUNKU) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE JEDNOSTKAMIARY ADD CONSTRAINT FKJMPOCHODNA FOREIGN KEY (JED_IDJM)

    REFERENCES JEDNOSTKAMIARY (IDJM) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE MODEL ADD CONSTRAINT FKMARKAMODEL FOREIGN KEY (IDMARKI)

    REFERENCES MARKA (IDMARKI) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE OPERATOR ADD CONSTRAINT FKGROOPERATOR FOREIGN KEY (IDGRUPYO)

    REFERENCES GRUPAO (IDGRUPYO) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE TELEFON ADD CONSTRAINT FKMODELTELEFON FOREIGN KEY (IDMARKI, IDMODELU)

    REFERENCES MODEL (IDMARKI, IDMODELU) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE UMOWA ADD CONSTRAINT FKUMOWA FOREIGN KEY (IDKLIENTA)

    REFERENCES KLIENT (ID_KLIENTA) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE UMOWA ADD CONSTRAINT FK_UMOWA2 FOREIGN KEY (IDGRUPYO, IDOPERATORA)

    REFERENCES OPERATOR (IDGRUPYO, IDOPERATORA) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE UMOWA ADD CONSTRAINT FK_UMOWA3 FOREIGN KEY (IDMARKI, IDMODELU, IDTELEFONU)

    REFERENCES TELEFON (IDMARKI, IDMODELU, IDTELEFONU) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE USLUGA ADD CONSTRAINT FKJMUSLUGA FOREIGN KEY (IDJM)

    REFERENCES JEDNOSTKAMIARY (IDJM) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE USLUGIWYKONANE ADD CONSTRAINT FK_USLUGIWYKONANE FOREIGN KEY (IDMARKI, IDMODELU, IDTELEFONU)

    REFERENCES TELEFON (IDMARKI, IDMODELU, IDTELEFONU) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE USLUGIWYKONANE ADD CONSTRAINT FK_USLUGIWYKONANE2 FOREIGN KEY (IDUSLUGI)

    REFERENCES USLUGA (IDUSLUGI) ON DELETE RESTRICT ON UPDATE RESTRICT;



    ALTER TABLE USLUGIWYKONANE ADD CONSTRAINT FK_USLUGIWYKONANE3 FOREIGN KEY (IDRACHUNKU)

    REFERENCES RACHUNEK (IDRACHUNKU) ON DELETE RESTRICT ON UPDATE RESTRICT;

Tutaj test data
http://pastebin.com/gix03FXK o ile zatybi to narzedzie ma problem z generowanie czasami

Temat: Baza danych problem z jednym zapytaniem

brak auto_increment przy primary key

Temat: Baza danych problem z jednym zapytaniem

Rozumiem że to jakieś zadanie szkolne, wiec zamiast podać Ci na tacy gotowe rozwiązanie spróbuję nakierować Cię na ścieżkę jak tego typu zadania rozwiązać.

/*
Lista 10 uslug przynoszacych najwieksze wplywy
operatorom w ciagu ostatnich 5 lat kalendarzowych
id uslugi nazwauslugi kwota wplywow
lista obejmuje tylko te uslugi ktore sa dostepne
u min 5 operatorow i skorzystalo z nich min 1000 klientow(roznych)
*/

Najważniejsze a przy dużych bazach danych dające największe korzyści w efektywności wykonywania zapytań jest jak najszybsze maksymalne ograniczenie ilości wierzy (danych na których ma zostać wykonane zapytanie)

1) Logiczne jest więc że w 1 kolejności powinniśmy wybrać usługi dostępne minimalnie u 5 operatorów oraz takich z których skorzystało minimalnie 1000 klientów, brak pełnej specyfikacji pozwala ci dowolnie stworzyć zapytanie licząc 1000 w sumie różnych klientów, lub tylko takich gdzie liczba 1000 jest przekroczona dla każdego z operatorów
Wbrew pozorom ta 2 wersja się może okazać prostsza
Tu nas interesuje tylko i wyłącznie nazwa usługi

2) teraz liczymy na podstawie historii sumę wszystkich dochodów dla wybranych wcześniej usług (za ostatnie 5 lat !)

3) z wyniku wybieramy listę pierwszych 10 na przykład poleceniem (select top 10 .....)

Jest też możliwe podejście którego głównym celem jest przejrzystość rozwiązania
Ale podkreślam aczkolwiek zapytanie jest bardzo czytelne nie wolno go stosować do wielkich baz danych (zabudowane optymalizacje zapytań w bazach danych) nie poradzą sobie dobrze z takim zadaniem.
Budujemy jedno duże zapytanie uwzględniające wymagane dane oraz SUM(cena) DOCHOD, COUNT(DISTINCT IDOperatora) ILOSC_OPERATOROW, COUNT(DISTINCT ID_KLIENTA) ILOSC_KLIENTOW
Nalezy pamietac o ustawieniu w where warunku wybierającego wiersze za ostatnie 5 lat, i użyć odpowiednich kolumn w poleceniu GROUP BY

Jak już to mamy wystarczy przy użyciu having wybrać odpowiednie wiersze.Ten post został edytowany przez Autora dnia 09.01.14 o godzinie 16:31