Oracle lekérdezések specialitásai és tovább fejlesztési lehetőségek

Kell egy SQL lekérdezés célja, hogy megtalálja azokat a felhasználókat, akik:

Legalább két vásárlást végeztek.
Az első és második vásárlás legfeljebb 7 nap különbséggel történt.
A második vásárlás után nem történt több vásárlás.

Általános megoldás SQL-ben:

---sql

WITH purchases AS (
    SELECT 
        user_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
    FROM orders
),
first_two AS (
    SELECT 
        user_id,
        MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
        MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
    FROM purchases
    GROUP BY user_id
    HAVING COUNT(*) >= 2
)
SELECT user_id
FROM first_two
WHERE DATEDIFF(day, first_purchase, second_purchase) <= 7
  AND NOT EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.user_id = first_two.user_id 
        AND o.order_date > second_purchase
  );

Rövid magyarázat:

purchases CTE: Számozza a vásárlásokat időrendben minden felhasználónál.
first_two CTE: Kiválasztja az első és második vásárlás dátumát, csak azoktól, akiknek legalább két vásárlásuk volt.
Fő lekérdezés: Szűr, hogy a két vásárlás között legfeljebb 7 nap teljen el, és ne legyen harmadik vásárlás a második után.

Oracle adatbázisban néhány dolgot máshogy kell írni:

DATEDIFF(day, a, b) nem létezik, helyette: b - a
A ROW_NUMBER() és LEAD() ugyanúgy működik.
NOT EXISTS is támogatott.

Íme az átalakított SQL Oracle-re:

Oracle-kompatibilis lekérdezés:

---sql

WITH purchases AS (
    SELECT 
        user_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
    FROM orders
),
first_two AS (
    SELECT 
        user_id,
        MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
        MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
    FROM purchases
    GROUP BY user_id
    HAVING COUNT(*) >= 2
)
SELECT user_id
FROM first_two
WHERE (second_purchase - first_purchase) <= 7
  AND NOT EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.user_id = first_two.user_id 
        AND o.order_date > second_purchase
  );

Megjegyzések:

Oracle-ben a dátumok kivonása napokban adja meg a különbséget (DATE - DATE = szám napban).
A HAVING COUNT(*) >= 2 feltételezhető, hogy a purchases CTE-ben csak két sorra van szükség, de Oracle-ben ezt így is hagyhatjuk.

🔧 1. Paraméterezhető lekérdezés Oracle-ben

Oracle-ben a bind paraméterek használhatók pl. PL/SQL blokkokban, vagy alkalmazásból való hívás esetén (pl. :max_days_between_purchases). Itt egy példalekérdezés max_days_between_purchases paraméterrel:

---sql

-- Paraméterezett Oracle SQL
-- Feltételezve, hogy a :max_days_between_purchases paraméter kívülről érkezik (pl. PL/SQL vagy alkalmazásból)

WITH purchases AS (
    SELECT 
        user_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
    FROM orders
),
first_two AS (
    SELECT 
        user_id,
        MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
        MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
    FROM purchases
    GROUP BY user_id
    HAVING COUNT(*) >= 2
)
SELECT user_id
FROM first_two
WHERE (second_purchase - first_purchase) <= :max_days_between_purchases
  AND NOT EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.user_id = first_two.user_id 
        AND o.order_date > second_purchase
  );

✨ 2. Oracle-specifikus finomítási lehetőségek

✅ A. Használj `TRUNC()`-ot dátumok összehasonlításához

Ha csak a dátum része érdekel, nem az időpont is, akkor célszerű TRUNC()-ot használni:

---sql

WHERE TRUNC(second_purchase) - TRUNC(first_purchase) <= :max_days_between_purchases

✅ B. Használj `WITH` + `FETCH FIRST` Oracle 12c-től felfelé

Ha például szeretnél csak az első 1000 eredményt, akkor Oracle 12c+ verzióban használhatsz:

---sql

SELECT user_id
FROM first_two
WHERE ...
FETCH FIRST 1000 ROWS ONLY;

✅ C. `CAST(... AS DATE)` – ha valahol szám vagy szöveg formátumban van dátum

---sql

CAST(order_date AS DATE)

✅ D. PL/SQL block példa paraméterrel

---sql
VARIABLE max_days NUMBER
EXEC :max_days := 7;

SELECT ...
WHERE (second_purchase - first_purchase) <= :max_days;

✅ E. Indexhasználat optimalizálása

Ha az orders táblán sok rekord van:

Legyen index user_id, order_date sorrendben (kompozit index).
Használhatsz materializált view-t, ha ez gyakran fut.

Egy Oracle PL/SQL tárolt eljárásnak, amely:

Bemeneti paraméterként fogadja a napok számát (pl. p_max_days).
Lefuttatja a lekérdezést.
Az eredményt kurzorral adja vissza (hogy az alkalmazás vagy más PL/SQL blokk tudja kezelni).

✅ Tárolt eljárás kurzorral – Oracle PL/SQL

--- sql
CREATE OR REPLACE PROCEDURE get_loyal_users (
    p_max_days IN NUMBER,
    p_result OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_result FOR
        WITH purchases AS (
            SELECT 
                user_id,
                order_date,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
            FROM orders
        ),
        first_two AS (
            SELECT 
                user_id,
                MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
                MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
            FROM purchases
            GROUP BY user_id
            HAVING COUNT(*) >= 2
        )
        SELECT user_id
        FROM first_two
        WHERE TRUNC(second_purchase) - TRUNC(first_purchase) <= p_max_days
          AND NOT EXISTS (
              SELECT 1 
              FROM orders o 
              WHERE o.user_id = first_two.user_id 
                AND o.order_date > second_purchase
          );
END get_loyal_users;
/

📦 Hogyan használd?

---sql
-- Bindelj ki egy változót
VARIABLE rc REFCURSOR

-- Hívd meg az eljárást (pl. 7 napos különbséggel)
EXEC get_loyal_users(7, :rc)

-- Nézd meg az eredményt
PRINT rc

💡 Tipp: Lehetséges továbbfejlesztések

Bemeneti időintervallumként DATE paramétert is adhatsz hozzá (pl. p_from_date, p_to_date).
Visszaadhatsz más mezőket is (pl. vásárlás dátumai).
Lefuthat automatizáltan, ütemezve (pl. DBMS_SCHEDULER segítségével).

🧩 1. Bővített eljárás – Hiba- és naplókezeléssel

Itt egy továbbfejlesztett változat:

✅ Tárolt eljárás naplózással és hibakezeléssel

---sql

CREATE OR REPLACE PROCEDURE get_loyal_users (
    p_max_days     IN  NUMBER,
    p_result       OUT SYS_REFCURSOR
)
AS
    v_start_time   TIMESTAMP := SYSTIMESTAMP;
    v_log_msg      VARCHAR2(4000);
BEGIN
    -- Naplózás: kezdés
    v_log_msg := 'Lekérdezés indul: ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS') || 
                 ', max napok: ' || p_max_days;
    INSERT INTO process_log (log_time, message)
    VALUES (SYSTIMESTAMP, v_log_msg);
    COMMIT;

    -- Lekérdezés futtatása
    OPEN p_result FOR
        WITH purchases AS (
            SELECT 
                user_id,
                order_date,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
            FROM orders
        ),
        first_two AS (
            SELECT 
                user_id,
                MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
                MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
            FROM purchases
            GROUP BY user_id
            HAVING COUNT(*) >= 2
        )
        SELECT user_id
        FROM first_two
        WHERE TRUNC(second_purchase) - TRUNC(first_purchase) <= p_max_days
          AND NOT EXISTS (
              SELECT 1 
              FROM orders o 
              WHERE o.user_id = first_two.user_id 
                AND o.order_date > second_purchase
          );

    -- Naplózás: sikeres befejezés
    INSERT INTO process_log (log_time, message)
    VALUES (SYSTIMESTAMP, 'Lekérdezés sikeresen lefutott.');
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        -- Naplózás hiba esetén
        INSERT INTO process_log (log_time, message)
        VALUES (SYSTIMESTAMP, 'Hiba: ' || SQLERRM);
        COMMIT;
        -- Hibát újradobja, ha szükséges
        RAISE;
END get_loyal_users;
/

📋 2. `process_log` naplótábla (ha még nincs)

---sql

CREATE TABLE process_log (
    log_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    log_time  TIMESTAMP DEFAULT SYSTIMESTAMP,
    message   VARCHAR2(4000)
);

📈 3. Egyszerű riportgenerálás (opcionálisan Excel-be, külső eszközből)

Ha PL/SQL-ből simán szeretnéd szövegként:

---sql
SELECT user_id, 
       TO_CHAR(first_purchase, 'YYYY-MM-DD') AS first_purchase, 
       TO_CHAR(second_purchase, 'YYYY-MM-DD') AS second_purchase
FROM (
    -- ugyanaz a WITH lekérdezés ide bemásolható
)

Vagy:

Ha Excel riportot szeretnél, azt érdemes Oracle BI, APEX, UTL_FILE, vagy akár Python/PowerShell alkalmazással kinyerni.
Kérheted az eredményt CSV-be mentve PL/SQL-ből is UTL_FILE-el (ha szeretnéd, írok rá példát).

💬 További lehetőségek

📨 Email küldése hiba esetén (UTL_SMTP)
📂 CSV export fájlba (UTL_FILE)
📆 Ütemezés (DBMS_SCHEDULER)
📊 APEX interfész ennek megjelenítésére

✅ 1. CSV export PL/SQL-ből (`UTL_FILE`)

A. Előkészítés – fájlírás engedélyezése Oracle-ben

Hozz létre egy Oracle DIRECTORY objektumot:

---sql

CREATE OR REPLACE DIRECTORY export_dir AS '/u01/app/oracle/export';
-- Ezt az elérési utat a szerverhez igazítsd!

Adj hozzá jogosultságot:

---sql

GRANT READ, WRITE ON DIRECTORY export_dir TO your_user;

B. PL/SQL eljárás CSV exporthoz

---sql

CREATE OR REPLACE PROCEDURE export_loyal_users_csv (
    p_max_days   IN NUMBER,
    p_filename   IN VARCHAR2
)
AS
    v_file       UTL_FILE.FILE_TYPE;
BEGIN
    v_file := UTL_FILE.FOPEN('EXPORT_DIR', p_filename, 'W');

    UTL_FILE.PUT_LINE(v_file, 'user_id,first_purchase,second_purchase');

    FOR rec IN (
        WITH purchases AS (
            SELECT 
                user_id,
                order_date,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
            FROM orders
        ),
        first_two AS (
            SELECT 
                user_id,
                MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
                MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
            FROM purchases
            GROUP BY user_id
            HAVING COUNT(*) >= 2
        )
        SELECT user_id, first_purchase, second_purchase
        FROM first_two
        WHERE TRUNC(second_purchase) - TRUNC(first_purchase) <= p_max_days
          AND NOT EXISTS (
              SELECT 1 
              FROM orders o 
              WHERE o.user_id = first_two.user_id 
                AND o.order_date > second_purchase
          )
    ) LOOP
        UTL_FILE.PUT_LINE(v_file, 
            rec.user_id || ',' || 
            TO_CHAR(rec.first_purchase, 'YYYY-MM-DD') || ',' || 
            TO_CHAR(rec.second_purchase, 'YYYY-MM-DD'));
    END LOOP;

    UTL_FILE.FCLOSE(v_file);

    INSERT INTO process_log (log_time, message)
    VALUES (SYSTIMESTAMP, 'Sikeres CSV export: ' || p_filename);
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO process_log (log_time, message)
        VALUES (SYSTIMESTAMP, 'Hiba CSV export során: ' || SQLERRM);
        COMMIT;
        IF UTL_FILE.IS_OPEN(v_file) THEN
            UTL_FILE.FCLOSE(v_file);
        END IF;
        RAISE;
END export_loyal_users_csv;
/

🗓️ 2. Ütemezés Oracle-ben (`DBMS_SCHEDULER`)

Példa: minden nap hajnal 2-kor futtassuk le az exportot:

---sql

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'daily_loyal_user_export',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN export_loyal_users_csv(7, ''loyal_users.csv''); END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0',
        enabled         => TRUE
    );
END;
/

🐍 3. Python megoldás Oracle-adatbázishoz

Követelmények:

Telepítsd a cx_Oracle csomagot:

---bash

pip install cx_Oracle

Példa Python szkript – CSV export

---python

import cx_Oracle
import csv

# Kapcsolat beállítás
conn = cx_Oracle.connect("username", "password", "hostname:port/service_name")
cursor = conn.cursor()

# Paraméterezett SQL
sql = """
WITH purchases AS (
    SELECT user_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
    FROM orders
),
first_two AS (
    SELECT user_id,
           MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
           MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
    FROM purchases
    GROUP BY user_id
    HAVING COUNT(*) >= 2
)
SELECT user_id, first_purchase, second_purchase
FROM first_two
WHERE TRUNC(second_purchase) - TRUNC(first_purchase) <= :max_days
  AND NOT EXISTS (
      SELECT 1 
      FROM orders o 
      WHERE o.user_id = first_two.user_id 
        AND o.order_date > second_purchase
)
"""

# Paraméter érték
max_days = 7

cursor.execute(sql, {'max_days': max_days})

# CSV-be mentés
with open('loyal_users_python.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow([desc[0] for desc in cursor.description])  # fejléc
    for row in cursor:
        writer.writerow(row)

cursor.close()
conn.close()

print("Sikeres export Pythonból.")

📨 1. Email küldés Oracle-ből (`UTL_SMTP`)

Oracle beépített csomagjával (UTL_SMTP) tudunk egyszerű szöveges emailt küldeni, de a fájlmelléklet (CSV) bonyolultabb, MIME kódolást igényel.

A. Egyszerű szöveges email (riport link vagy kivonat)

---sql

DECLARE
    l_mailhost VARCHAR2(64) := 'smtp.yourserver.com'; -- SMTP szerver neve vagy IP
    l_conn     UTL_SMTP.connection;
    l_sender   VARCHAR2(64) := 'noreply@yourdomain.com';
    l_recipient VARCHAR2(64) := 'user@example.com';
BEGIN
    l_conn := UTL_SMTP.open_connection(l_mailhost, 25);
    UTL_SMTP.helo(l_conn, l_mailhost);
    UTL_SMTP.mail(l_conn, l_sender);
    UTL_SMTP.rcpt(l_conn, l_recipient);
    UTL_SMTP.open_data(l_conn);
    UTL_SMTP.write_data(l_conn, 'From: ' || l_sender || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_conn, 'To: ' || l_recipient || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_conn, 'Subject: Riport: Loyal Users' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_conn, UTL_TCP.crlf ||
        'A loyal_users.csv fájl sikeresen elkészült a szerveren.' || UTL_TCP.crlf ||
        'Futtatás dátuma: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || UTL_TCP.crlf);
    UTL_SMTP.close_data(l_conn);
    UTL_SMTP.quit(l_conn);
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO process_log (log_time, message)
        VALUES (SYSTIMESTAMP, 'Email küldési hiba: ' || SQLERRM);
        COMMIT;
END;

👉 Megjegyzés: ha fájlt is szeretnél csatolni Oracle-ből, ahhoz Base64 és MIME fejlécek kézi kezelése szükséges – érdemesebb Pythonból küldeni.

🐍 2. Email küldés Pythonból (CSV melléklettel)

Ez sokkal egyszerűbb, jól kezelhető SMTP-vel, és tökéletes email-riport automatizálásra.

A. Telepítsd (ha még nincs):

---bash

pip install secure-smtplib

B. Példa: email küldés CSV melléklettel

---python

import smtplib
from email.message import EmailMessage
import ssl

# Email beállítások
sender_email = "noreply@yourdomain.com"
receiver_email = "user@example.com"
subject = "Loyal Users Report"
body = "Csatolva található a legfrissebb loyal_users riport."

# SMTP szerver beállítások
smtp_server = "smtp.yourdomain.com"
smtp_port = 587
smtp_user = "noreply@yourdomain.com"
smtp_password = "yourpassword"

# Email üzenet felépítése
msg = EmailMessage()
msg['From'] = sender_email
msg['To'] = receiver_email
msg['Subject'] = subject
msg.set_content(body)

# CSV melléklet hozzáadása
filename = 'loyal_users_python.csv'
with open(filename, 'rb') as f:
    file_data = f.read()
    msg.add_attachment(file_data, maintype='text', subtype='csv', filename=filename)

# Email küldés
context = ssl.create_default_context()
with smtplib.SMTP(smtp_server, smtp_port) as server:
    server.starttls(context=context)
    server.login(smtp_user, smtp_password)
    server.send_message(msg)

print("Email sikeresen elküldve.")



Lépések összevonása :
Átadja az Oracle-lekérdezést 
Elmenti az eredményt CSV fájlba 
Emailben elküldi a riportot 
Visszajelez az adatbázisba egy process_log tábla segítségével



📦 Struktúra áttekintés
Lépés Funkció
🔍 1. Oracle kapcsolódás és lekérdezés
📁 2. CSV fájl létrehozása
📬 3. Email küldése melléklettel
📝 4. Naplóbejegyzés mentése az Oracle-be

✅ Komplett Python szkript 
 

💡 A működéshez szükséges: 
 
Oracle DB elérés (username, password, service) 
SMTP adatok (felhasználónév, jelszó) 
cx_Oracle és email modul 
Létrehozott process_log tábla Oracle-ben 
---python

import cx_Oracle
import csv
import smtplib
from email.message import EmailMessage
import ssl
from datetime import datetime

# ==== Beállítások ====

# Oracle DB
db_username = "your_user"
db_password = "your_password"
db_dsn = "hostname:port/service_name"

# Email
smtp_server = "smtp.yourdomain.com"
smtp_port = 587
smtp_user = "noreply@yourdomain.com"
smtp_password = "yourpassword"
recipient_email = "user@example.com"

# Fájl
csv_filename = "loyal_users_python.csv"

# Paraméter
max_days = 7

try:
    # ==== 1. Oracle kapcsolat ====
    conn = cx_Oracle.connect(db_username, db_password, db_dsn)
    cursor = conn.cursor()

    sql = """
    WITH purchases AS (
        SELECT user_id, order_date,
               ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
        FROM orders
    ),
    first_two AS (
        SELECT user_id,
               MAX(CASE WHEN rn = 1 THEN order_date END) AS first_purchase,
               MAX(CASE WHEN rn = 2 THEN order_date END) AS second_purchase
        FROM purchases
        GROUP BY user_id
        HAVING COUNT(*) >= 2
    )
    SELECT user_id, first_purchase, second_purchase
    FROM first_two
    WHERE TRUNC(second_purchase) - TRUNC(first_purchase) <= :max_days
      AND NOT EXISTS (
          SELECT 1 
          FROM orders o 
          WHERE o.user_id = first_two.user_id 
            AND o.order_date > second_purchase
    )
    """

    cursor.execute(sql, {'max_days': max_days})
    rows = cursor.fetchall()
    headers = [desc[0] for desc in cursor.description]

    # ==== 2. CSV fájl írás ====
    with open(csv_filename, 'w', newline='') as f:
        writer = csv.writer(f)
        writer.writerow(headers)
        writer.writerows(rows)

    # ==== 3. Email küldés ====
    msg = EmailMessage()
    msg['From'] = smtp_user
    msg['To'] = recipient_email
    msg['Subject'] = f"Loyal Users Riport - {datetime.now().strftime('%Y-%m-%d %H:%M')}"
    msg.set_content(f"A riport generálása sikeres volt. Mellékelve a CSV fájl.\nMax napok: {max_days}")

    with open(csv_filename, 'rb') as f:
        msg.add_attachment(f.read(), maintype='text', subtype='csv', filename=csv_filename)

    context = ssl.create_default_context()
    with smtplib.SMTP(smtp_server, smtp_port) as server:
        server.starttls(context=context)
        server.login(smtp_user, smtp_password)
        server.send_message(msg)

    # ==== 4. Naplózás adatbázisba ====
    log_msg = f"Sikeres riport és email küldés: {csv_filename}"
    cursor.execute("INSERT INTO process_log (log_time, message) VALUES (SYSTIMESTAMP, :msg)", {'msg': log_msg})
    conn.commit()

    print("✔️ Sikeres riport készítés, emailküldés és naplózás.")

except Exception as e:
    # Hibát is naplózzuk Oracle-be
    try:
        error_msg = f"Hiba a riport folyamatban: {str(e)}"
        cursor.execute("INSERT INTO process_log (log_time, message) VALUES (SYSTIMESTAMP, :msg)", {'msg': error_msg})
        conn.commit()
    except:
        print("❌ Hiba naplózás közben is")
    print(f"❌ Hiba történt: {e}")

finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()


📋 process_log tábla
Ha még nem hoztad létre:
sql

CREATE TABLE process_log (
    log_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    log_time  TIMESTAMP DEFAULT SYSTIMESTAMP,
    message   VARCHAR2(4000)
);

IT, BI, DWH, DM, AI