Code-Bibliothek — Capypad · Code Zeile für Zeile lesen

SQL · ExperteSnippet

Bedingte Aggregation für Data Pivoting

Bedingte Aggregation verwendet CASE-Anweisungen innerhalb von Aggregatfunktionen, um Daten von Zeilen in Spalten umzuwandeln (Pivoting). Diese Technik ist hocheffizient für die Erstellung von Berichten, die mehrere Kennzahlen aus einem einzigen Tabellendurchlauf erfordern.

snippet.sql

sql

1

2

3

4

5

6

SELECT 
  EXTRACT(YEAR FROM order_date) as year,
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) as revenue_shipped,
  SUM(CASE WHEN status = 'returned' THEN amount ELSE 0 END) as loss_returned
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

queriesbestpractices

SQL · ExperteSnippet

Rekursive Common Table Expressions (CTEs)

Rekursive CTEs werden verwendet, um hierarchische Datenstrukturen wie Organigramme oder Stücklisten zu durchlaufen. Sie bestehen aus einem Anker-Element und einem rekursiven Element, die durch UNION ALL verbunden sind.

snippet.sql

sql

1

2

3

4

5

6

7

8

9

10

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

syntaxqueries

SQL · ExperteSnippet

Transaktions-Isolationsstufen und Integrität

Isolationsstufen definieren, wie die Integrität von Transaktionen für andere Benutzer und Systeme sichtbar ist. SERIALIZABLE ist die höchste Stufe und verhindert Dirty Reads, Non-Repeatable Reads und Phantom Reads durch Simulation einer sequentiellen Ausführung.

snippet.sql

sql

1

2

3

4

5

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 102;
COMMIT;

transactionssecurity

SQL · ExperteSnippet

Fensterfunktionen für deterministisches Ranking

Fensterfunktionen führen Berechnungen über eine Menge von Tabellenzeilen durch, die mit der aktuellen Zeile in Beziehung stehen. Im Gegensatz zu Aggregatfunktionen gruppieren sie Zeilen nicht in einer einzigen Ergebniszeile, wodurch die ursprünglichen Datendetails erhalten bleiben.

snippet.sql

sql

1

2

3

4

5

6

SELECT 
  product_id, 
  category, 
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
FROM products;

syntaxfunctions

SQL · ExperteSnippet

Korrelierte Unterabfragen für vergleichende Analysen

Eine korrelierte Unterabfrage bezieht sich auf Spalten der äußeren Abfrage. Dieses Beispiel identifiziert Mitarbeiter, die mehr als das Durchschnittsgehalt in ihrer jeweiligen Abteilung verdienen, was erfordert, dass die Unterabfrage für jede von der äußeren Anweisung ausgewertete Zeile ausgeführt wird.

snippet.sql

sql

1

2

3

4

5

6

7

SELECT e1.employee_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

queriesperformance

SQL · ExperteSnippet

Relationale Division mittels doppelter Negation

Die relationale Division wird verwendet, um Entitäten zu identifizieren, die mit jedem Datensatz einer Zielmenge verknüpft sind. Da SQL keinen nativen 'DIVIDE'-Operator besitzt, nutzen wir die doppelte Negation mit NOT EXISTS, um Projekte zu finden, für die es 'keinen Skill gibt, der nicht in diesem Projekt vorhanden ist'.

snippet.sql

sql

1

2

3

4

5

6

7

8

9

10

11

12

SELECT DISTINCT p.id
FROM Projects p
WHERE NOT EXISTS (
    SELECT s.id
    FROM Skills s
    WHERE NOT EXISTS (
        SELECT 1
        FROM Project_Skills ps
        WHERE ps.project_id = p.id
        AND ps.skill_id = s.id
    )
);

queriesperformance

SQL · ExperteSnippet

Logik zur Erkennung zeitlicher Überlappungen

Die Identifizierung überlappender Intervalle ist eine häufige Experten-Herausforderung. Die Logik (StartA < EndeB AND StartB < EndeA) ist der mathematisch optimale Weg, um jede Schnittmenge zwischen zwei Zeiträumen zu erkennen und komplexe verschachtelte CASE-Anweisungen zu vermeiden.

snippet.sql

sql

1

2

3

4

5

6

SELECT a.id, b.id
FROM Bookings a
JOIN Bookings b ON a.room_id = b.room_id
  AND a.id < b.id
WHERE a.start_time < b.end_time
  AND b.start_time < a.end_time;

queriesbestpractices

SQL · ExperteSnippet

Datenintegrität mittels WITH CHECK OPTION

Die Klausel WITH CHECK OPTION in einer View verhindert, dass Benutzer Zeilen einfügen oder aktualisieren, die durch die View selbst nicht mehr sichtbar wären. Dies erzwingt Geschäftslogik auf Schema-Ebene und stellt die Datenkonsistenz für aktualisierbare Views sicher.

snippet.sql

sql

1

2

3

4

5

CREATE VIEW Active_Staff AS
SELECT id, name, status
FROM Employees
WHERE status = 'ACTIVE'
WITH CHECK OPTION;

securitysyntax