Untitled

-- Czyszczenie tabel, jeśli istnieją
IF OBJECT_ID('Produkty', 'U') IS NOT NULL
    DROP TABLE Produkty;
IF OBJECT_ID('Klienci', 'U') IS NOT NULL
    DROP TABLE Klienci;
IF OBJECT_ID('Sprzedaz', 'U') IS NOT NULL
    DROP TABLE Sprzedaz;
IF OBJECT_ID('fakt_sprzedazy', 'U') IS NOT NULL
    DROP TABLE fakt_sprzedazy;
IF OBJECT_ID('wymiar_produkty', 'U') IS NOT NULL
    DROP TABLE wymiar_produkty;
IF OBJECT_ID('wymiar_klienci', 'U') IS NOT NULL
    DROP TABLE wymiar_klienci;
IF OBJECT_ID('wymiar_data', 'U') IS NOT NULL
    DROP TABLE wymiar_data;
IF OBJECT_ID('wymiar_produkty_scd2', 'U') IS NOT NULL
    DROP TABLE wymiar_produkty_scd2;

-- Tworzenie tabel
CREATE TABLE Produkty (
    id INT PRIMARY KEY,
    nazwa VARCHAR(100),
    kategoria VARCHAR(50),
    cena FLOAT
);

CREATE TABLE Klienci (
    id INT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50),
    email VARCHAR(100),
	miasto VARCHAR(100)
);

CREATE TABLE Sprzedaz (
    id INT PRIMARY KEY,
    data_sprzedazy DATE,
    id_produktu INT,
    id_klienta INT,
    ilosc INT,
    FOREIGN KEY (id_produktu) REFERENCES Produkty(id),
    FOREIGN KEY (id_klienta) REFERENCES Klienci(id)
	);

CREATE TABLE wymiar_data (
    data DATE PRIMARY KEY,
    rok INT,
    miesiac INT,
    dzien INT
);

CREATE TABLE wymiar_produkty (
    id INT PRIMARY KEY,
    nazwa VARCHAR(100),
    kategoria VARCHAR(50),
    cena FLOAT
);

CREATE TABLE wymiar_klienci (
    id INT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(50),
    email VARCHAR(100)
);


CREATE TABLE fakt_sprzedazy (
    id INT IDENTITY(1,1) PRIMARY KEY, 
    data_sprzedazy DATE,
    id_produktu INT,
    id_klienta INT,
    kwota FLOAT,
    FOREIGN KEY (data_sprzedazy) REFERENCES wymiar_data(data),
    FOREIGN KEY (id_produktu) REFERENCES wymiar_produkty(id),
    FOREIGN KEY (id_klienta) REFERENCES wymiar_klienci(id)
);


DECLARE @startDate DATE = '2024-01-01';
DECLARE @endDate DATE = '2024-12-31';

WHILE @startDate <= @endDate
BEGIN
    INSERT INTO wymiar_data (data, rok, miesiac, dzien)
    VALUES (
        @startDate,
        YEAR(@startDate),
        MONTH(@startDate),
        DAY(@startDate)
    );
    SET @startDate = DATEADD(DAY, 1, @startDate);
END;

INSERT INTO Produkty (id, nazwa, kategoria, cena)
VALUES
    (1, 'Laptop Gamingowy', 'Elektronika', 3500.00),
    (2, 'Smartfon Premium', 'Elektronika', 2200.00),
    (3, 'Tablet Graficzny', 'Elektronika', 1200.00),
    (4, 'Drukarka Laserowa', 'Elektronika', 500.00),
    (5, 'Głośnik Bluetooth', 'Elektronika', 250.00),
    (6, 'Słuchawki Bezprzewodowe', 'Elektronika', 180.00),
    (7, 'Monitor Ultrawide', 'Elektronika', 1500.00),
    (8, 'Mysz Gamingowa', 'Elektronika', 80.00),
    (9, 'Klawiatura Mechaniczna', 'Elektronika', 160.00),
    (10, 'Konsola do gier', 'Elektronika', 2000.00),
    (11, 'Telewizor 4K', 'Elektronika', 3000.00),
    (12, 'Aparat fotograficzny', 'Elektronika', 4000.00),
    (13, 'Skuter Elektryczny', 'Pojazdy', 4000.00),
    (14, 'Rower Górski', 'Pojazdy', 2800.00),
    (15, 'Hulajnoga Elektryczna', 'Pojazdy', 600.00),
    (16, 'Deskorolka Elektryczna', 'Pojazdy', 800.00),
    (17, 'Kask Rowerowy', 'Pojazdy', 150.00),
    (18, 'Samochód osobowy', 'Pojazdy', 50000.00),
    (19, 'Motocykl', 'Pojazdy', 25000.00),
    (20, 'Kurtka Zimowa', 'Odzież', 350.00),
    (21, 'Buty Trekkingowe', 'Odzież', 200.00),
    (22, 'Koszula Elegancka', 'Odzież', 120.00),
    (23, 'Spodnie Jeansowe', 'Odzież', 180.00),
    (24, 'Sukienka Letnia', 'Odzież', 220.00),
    (25, 'Sweter', 'Odzież', 150.00),
    (26, 'Bluza', 'Odzież', 100.00),
    (27, 'Powieść Fantasy', 'Książki', 45.00),
    (28, 'Thriller', 'Książki', 35.00),
    (29, 'Kryminał', 'Książki', 40.00),
    (30, 'Biografia', 'Książki', 50.00),
    (31, 'Poradnik', 'Książki', 60.00),
    (32, 'Komiks', 'Książki', 30.00),
    (33, 'Podręcznik', 'Książki', 80.00);


INSERT INTO Klienci (id, imie, nazwisko, email, miasto)
VALUES
    (1, 'Jan', 'Kowalski', 'jan.kowalski@gmail.com', 'Warszawa'),
    (2, 'Anna', 'Nowak', 'anna.nowak@wp.pl', 'Kraków'),
    (3, 'Paweł', 'Wiśniewski', 'pawel.wisniewski@onet.pl', 'Gdańsk'),
    (4, 'Ewa', 'Wójcik', 'ewa.wojcik@gmail.com', 'Poznań'),
    (5, 'Marek', 'Kowalczyk', 'marek.kowalczyk@wp.pl', 'Wrocław'),
    (6, 'Agnieszka', 'Kamińska', 'agnieszka.kaminska@onet.pl', 'Łódź'),
    (7, 'Tomasz', 'Lewandowski', 'tomasz.lewandowski@gmail.com', 'Warszawa'),
    (8, 'Karolina', 'Nowicka', 'karolina.nowicka@wp.pl', 'Kraków'),
    (9, 'Robert', 'Zieliński', 'robert.zielinski@onet.pl', 'Gdańsk'),
    (10, 'Marta', 'Szymańska', 'marta.szymanska@gmail.com', 'Poznań'),
    (11, 'Katarzyna', 'Woźniak', 'katarzyna.wozniak@gmail.com', 'Warszawa'),
    (12, 'Piotr', 'Nowicki', 'piotr.nowicki@wp.pl', 'Kraków'),
    (13, 'Andrzej', 'Kwiatkowski', 'andrzej.kwiatkowski@onet.pl', 'Gdańsk'),
    (14, 'Małgorzata', 'Lewandowska', 'malgorzata.lewandowska@gmail.com', 'Poznań'),
    (15, 'Krzysztof', 'Jankowski', 'krzysztof.jankowski@wp.pl', 'Wrocław'),
    (16, 'Magdalena', 'Woźniak', 'magdalena.wozniak@example.com', 'Wrocław'),
    (17, 'Michał', 'Majewski', 'michal.majewski@example.com', 'Toruń'),
    (18, 'Joanna', 'Rutkowska', 'joanna.rutkowska@example.com', 'Wrocław'),
    (19, 'Marcin', 'Sadowski', 'marcin.sadowski@example.com', 'Toruń'),
    (20, 'Katarzyna', 'Wysocka', 'katarzyna.wysocka@example.com', 'Wrocław'),
    (21, 'Bartłomiej', 'Zieliński', 'bartlomiej.zielinski@example.com', 'Toruń'),
    (22, 'Izabela', 'Jankowska', 'izabela.jankowska@example.com', 'Toruń'),
    (23, 'Sebastian', 'Zawadzki', 'sebastian.zawadzki@example.com', 'Wrocław'),
    (24, 'Dominika', 'Sawicka', 'dominika.sawicka@example.com', 'Toruń'),
    (25, 'Wojciech', 'Borkowski', 'wojciech.borkowski@example.com', 'Toruń'),
    (26, 'Patrycja', 'Lis', 'patrycja.lis@example.com', 'Wrocław'),
    (27, 'Łukasz', 'Adamski', 'lukasz.adamski@example.com', 'Toruń'),
    (28, 'Aleksandra', 'Król', 'aleksandra.krol@example.com', 'Wrocław'),
    (29, 'Mateusz', 'Czarnecki', 'mateusz.czarnecki@example.com', 'Toruń'),
    (30, 'Monika', 'Gajewska', 'monika.gajewska@example.com', 'Toruń');

DECLARE @i INT = 1;
WHILE @i <= 500 -- Zwiększamy liczbę transakcji do 500
BEGIN
    INSERT INTO Sprzedaz (id, data_sprzedazy, id_produktu, id_klienta, ilosc)
    VALUES (
        @i,
        DATEADD(DAY, -ROUND(RAND() * 365, 0), GETDATE()), 
        ROUND(RAND() * 32 + 1, 0),                      
        ROUND(RAND() * 14 + 1, 0),                       
        ROUND(RAND() * 5 + 1, 0)                       
    );
    SET @i = @i + 1;
END;

INSERT INTO wymiar_produkty (id, nazwa, kategoria, cena)
SELECT id, nazwa, kategoria, cena
FROM Produkty;

INSERT INTO wymiar_klienci (id, imie, nazwisko, email)
SELECT id, imie, nazwisko, email
FROM Klienci;

-- ETL dla tabeli faktów z agregacją
-- Dodanie brakujących dat do wymiar_data
INSERT INTO wymiar_data (data, rok, miesiac, dzien)
SELECT DISTINCT s.data_sprzedazy, YEAR(s.data_sprzedazy), MONTH(s.data_sprzedazy), DAY(s.data_sprzedazy)
FROM Sprzedaz s
WHERE NOT EXISTS (
    SELECT 1 FROM wymiar_data d WHERE d.data = s.data_sprzedazy
);

-- Wstawienie danych do fakt_sprzedazy
INSERT INTO fakt_sprzedazy (data_sprzedazy, id_produktu, id_klienta, kwota)
SELECT 
    s.data_sprzedazy,
    s.id_produktu,
    s.id_klienta,
    SUM(s.ilosc * p.cena) AS kwota
FROM Sprzedaz s
JOIN Produkty p ON s.id_produktu = p.id
GROUP BY s.data_sprzedazy, s.id_produktu, s.id_klienta;

-- Obsługa Delty Danych

-- Aktualizowanie istniejących wierszy w tabeli fakt_sprzedazy
UPDATE fakt_sprzedazy
SET data_sprzedazy = '2024-05-01', id_produktu = 1, id_klienta = 1, kwota = 1103.00
WHERE id = 2503;

UPDATE fakt_sprzedazy
SET data_sprzedazy = '2024-05-02', id_produktu = 2, id_klienta = 2, kwota = 20.00
WHERE id = 2502;

-- Dodawanie nowych wierszy do tabeli fakt_sprzedazy
IF NOT EXISTS (SELECT * FROM fakt_sprzedazy WHERE id = 4)
BEGIN
    INSERT INTO fakt_sprzedazy (data_sprzedazy, id_produktu, id_klienta, kwota) -- Usunięto id z listy kolumn
    VALUES ('2024-05-04', 3, 3, 90.00);
END;

SELECT * FROM fakt_sprzedazy;


-- Implementacja SCD2 dla tabeli wymiarów
IF OBJECT_ID('wymiar_produkty_scd2', 'U') IS NOT NULL
    DROP TABLE wymiar_produkty_scd2;
CREATE TABLE wymiar_produkty_scd2 (
    id INT,
    nazwa VARCHAR(100),
    kategoria VARCHAR(50),
    cena FLOAT,
    data_start DATE,
    data_end DATE,
    PRIMARY KEY (id, data_start)
);

-- Ładowanie danych do tabeli SCD2

TRUNCATE TABLE wymiar_produkty_scd2
INSERT INTO wymiar_produkty_scd2 (id, nazwa, kategoria, cena, data_start, data_end)
SELECT id, nazwa, kategoria, cena, '2024-01-01', '2024-12-31'
FROM wymiar_produkty;

SELECT * FROM wymiar_produkty_scd2

-- Deklaracja zmiennej @currentDate
DECLARE @currentDate DATE = GETDATE();

-- Zamknięcie starego rekordu
UPDATE wymiar_produkty_scd2
SET data_end = @currentDate
WHERE id = 1 AND data_end = '2024-12-31';

SELECT * FROM wymiar_produkty_scd2

-- Wstawienie nowego rekordu
TRUNCATE TABLE wymiar_produkty_scd2
INSERT INTO wymiar_produkty_scd2 (id, nazwa, kategoria, cena, data_start, data_end)
VALUES (1, 'Monitor', 'Elektronika', 275.00, @currentDate, '2024-10-01');


--Select
SELECT * FROM wymiar_produkty_scd2
--GROUPBY
SELECT 
    wk.id AS KlientID,
    wk.imie + ' ' + wk.nazwisko AS Klient,
    AVG(fs.kwota) AS SredniaWartoscZamowienia
FROM 
    fakt_sprzedazy fs
JOIN 
    wymiar_klienci wk ON fs.id_klienta = wk.id
GROUP BY 
    wk.id, wk.imie, wk.nazwisko;
--JOINY
SELECT wymiar_klienci.imie, wymiar_klienci.nazwisko, wymiar_produkty.nazwa, wymiar_produkty.cena, fakt_sprzedazy.data_sprzedazy FROM fakt_sprzedazy
LEFT JOIN wymiar_klienci ON fakt_sprzedazy.id_klienta = wymiar_klienci.id
LEFT JOIN wymiar_produkty ON fakt_sprzedazy.id_produktu = wymiar_produkty.id
WHERE fakt_sprzedazy.data_sprzedazy >= '2024-05-30';
--ORDER
SELECT 
    wp.id AS ProduktID,
    wp.nazwa AS Produkt,
    COUNT(fs.id) AS LiczbaZamowien,
    SUM(fs.kwota) AS SumaKwot
FROM 
    fakt_sprzedazy fs
JOIN 
    wymiar_produkty wp ON fs.id_produktu = wp.id
GROUP BY 
    wp.id, wp.nazwa
ORDER BY 
    SumaKwot DESC;
--PARTITION + funkcja AVG
SELECT 
    kategoria,
    AVG(cena) OVER (PARTITION BY kategoria) AS SredniaCena
FROM 
    wymiar_produkty;
--HAVING
SELECT 
    wp.kategoria,
    COUNT(fs.id) AS LiczbaZamowien,
    SUM(fs.kwota) AS SumaKwot
FROM 
    fakt_sprzedazy fs
JOIN 
    wymiar_produkty wp ON fs.id_produktu = wp.id
GROUP BY 
    wp.kategoria
HAVING 
    SUM(fs.kwota) > 1000;