Top50 Datenbankabfragen

Nachdem du alle Tabellen deiner Datenbank miteinander verbunden hast, ist es Zeit, ein paar Queries zu schreiben. Wobei – ein paar ist ja was für Anfänger. Du bist schon ein Profi, also wirst du 50(!) Abfragen für deine Datenbank schreiben müssen. Und das sind nur die wichtigsten.

Datenbankabfragen

1. Produktliste für das Schaufenster holen

Query gibt alle aktiven Produkte mit ihrem Hauptpreis und Bild zurück, damit sie auf der Startseite und im Katalog angezeigt werden können. So kannst du das Schaufenster schnell aufbauen und die Produktinfos aktuell halten.

2. Produktsuche nach Stichwort

Ermöglicht Usern, interessante Produkte per Treffer im Namen oder in der Beschreibung zu finden. Das ist ein wichtiger Teil der User-Funktionalität für die schnelle Katalogsuche.

3. Produktkarte per ID

Gibt erweiterte Infos zu einem bestimmten Produkt zurück, inkl. Brand und Kategorie. Wird für die Detailseite des Produkts benötigt.

4. Liste der Produktvarianten

Zeigt alle verfügbaren Varianten (SKU) eines Produkts: Größen, Farben, Lagerbestand, Preise. Wird für die Auswahl der gewünschten Modifikation auf der Produktseite genutzt.

5. Produktbilder-Galerie

Für die vollständige Darstellung der Produktkarte sind alle Fotos wichtig. Query gibt sie mit Angabe des Hauptbilds zurück.

6. Durchschnittsbewertung und Anzahl der Bewertungen für ein Produkt

Wird genutzt, um die Bewertung und die Anzahl der Reviews anzuzeigen – wichtig für Reputation und Vertrauen der Käufer.

7. Detaillierte Liste der Produktbewertungen

Für den Bewertungsbereich in der Produktkarte: Rating, Text, Autor und Datum der Bewertung. Hilft neuen Käufern bei der Kaufentscheidung.

8. Fragen und Antworten zum Produkt

Query für das Holen von Fragen mit Antworten zu jedem Produkt – wichtig für den FAQ-Block auf der Produktseite.

9. Produktkategorien mit Hierarchie

Ermöglicht die Visualisierung der Katalogstruktur, Aufbau eines Navigationsbaums für Filter und Menü.

10. Produkte nach Kategorie und Unterkategorien

Hilft, alle Produkte aus der gewählten Kategorie oder deren "Kind"-Kategorien (Verschachtelungsebene) anzuzeigen.

11. Liste der Brands

Für Filter nach Brands, Brand-Listing und Landingpages.

12. Beliebte Tags und ihre Produktanzahl

Analysiert die meistgenutzten Tags für Trend-Produkte und Tag-Clouds.

13. Preisänderungshistorie für ein Produkt

Für Analytics und Anzeige der Preisdynamik (alt/neu, Aktionen).

14. Statusänderungshistorie eines Produkts

Ermöglicht das Nachverfolgen des Produkt-Lebenszyklus, Grund für das Verschwinden aus dem Schaufenster oder Rückgabe.

15. Suche nach Zertifikaten und Lizenzen

Kritisch für professionelle Käufer und B2B-Segment (Qualität und Legalität der Produkte).

16. Daten zu Produktlieferanten

Wichtig für Administration, Qualitätskontrolle und Kontakt zu Lieferanten.

17. Produktbestand nach Lager

Kontrolle und Erfassung aktueller Bestände pro Lager. Notwendig für Logistiksystem und Vermeidung von "out of stock".

18. Produkte mit Bestand unter Schwelle

Automatisierung der Lagerauffüllung, Vermeidung von Umsatzverlusten durch fehlende Produkte.

19. Warenbewegung im Lager (Audit)

Tracking aller Warenbewegungen im gewählten Zeitraum: Zugänge, Abgänge und Korrekturen – wichtig für Inventur und Verlustprävention.

20. Logistik der Umlagerungen zwischen Lagern

Ermöglicht die Einsicht in die Historie und den Status interner Warenbewegungen zwischen Logistikzentren.

21. Versand: Methoden und Tarife

Für die Berechnung der Versandkosten und Info für den User beim Checkout.

22. Bestellhistorie des Users

Wichtigster Teil des User-Accounts – alle getätigten Bestellungen, deren Status und Summe.

23. Bestelldetails mit Positionen

Ermöglicht das Holen der kompletten Bestellstruktur – Inhalt, Preise, Menge – für Frontend oder Support.

24. Bestellreport nach Zeitraum und Status

Analytics und Reporting zu Verkäufen, gibt Bestellungen nach Zeitraum und gewünschtem Status zurück (z.B. "abgeschlossen").

25. "Verlassene" Warenkörbe

Analytics für Marketer: Warenkörbe, bei denen der User nicht bestellt hat – potenziell für Retargeting.

26. Bestseller

Analytics für den Block "Bestseller" und Marketing-Collections: Welche Produkte werden am häufigsten gekauft.

27. Verkäufe pro Tag (für Charts)

Report zum Tagesumsatz – Basis für Business-Dynamik-Analyse und Charting.

28. Liste der Rückgaben

Zeigt Rückgaben aller Bestellungen mit Grund und Status, hilft bei der Analyse der Rückgabegründe.

29. Liste der Stornierungen

Kontrolle von Verlusten und Stornogründen: Zeigt Stornos mit Grund, wer storniert hat und wann.

30. Bestellungen, die auf Versand warten

Für Lager und Versand – Bestellungen, die gepackt und verschickt werden müssen, mit Versanddetails.

31. Durchschnittlicher Warenkorbwert

Der "Average Order Value" – eine Schlüsselmetrik zur Bewertung von Marketing und Sortiment.

32. Bestellungen mit Promocode-Einsatz

Analytics zur Effektivität von Aktionen: Welche Promocodes wurden wie oft genutzt.

33. Nutzung von Rabatten nach Kategorien und Brands

Ermöglicht die Bewertung, welche Aktionen funktionieren, und das Monitoring der Rabattbeliebtheit nach Kategorie und Brand.

34. Eingesetzte Promocodes und ihre User

Kontrolle der Promocode-Nutzung, Erkennung von Anomalien und Missbrauch.

35. Zahlungshistorie zur Bestellung

Für Support und Buchhaltung: Zeigt alle Zahlungstransaktionen, deren Status und verwendete Zahlungsmethoden.

36. Bestellungen mit Rückerstattung

Für Rückgabe-Analyse, Buchhaltungsreports und Betrugsprävention.

37. User-Wallet-Balance und Transaktionshistorie

Kontrolle und Anzeige von Bonus- oder Cashback-Guthaben des Users, Historie der Bewegungen.

38. User-Support-Tickets

Ermöglicht dem User, seine Anfragen und deren Status zu sehen.

39. SLA-Analytics zu Support-Tickets

Analysiert die durchschnittliche Antwort- und Lösungszeit je Priorität – wichtig für SLA-Kontrolle.

40. Nachrichten zum Support-Ticket

Ermöglicht die Ansicht der gesamten Kommunikation zum gewählten Ticket – wichtig für User und Support.

41. Aktive FAQs nach Kategorien

Zeigt häufig gestellte Fragen für die Knowledge Base des Kunden, hilft Support-Last zu reduzieren.

42. Aktive Marketing-Kampagnen und Banner

Für die Anzeige aktueller Werbeangebote auf der Seite.

43. Ausgewählte Produkte auf der Startseite

Für den "Favoriten"-Block: Produkte, die auf der Startseite hervorgehoben werden sollen.

44. A/B-Test-Historie

Analyse durchgeführter Experimente zur Optimierung von UX und Marketing.

45. Produkt-View-Historie eines bestimmten Users

Zeigt "Du hast angesehen" oder wird für personalisierte Empfehlungen genutzt.

46. Beliebte Suchanfragen der User

Analyse der User-Nachfrage, hilft bei der Optimierung von Suche und Vorschlägen.

47. Analytics zu Traffic-Quellen

Ermöglicht die Bewertung, welche Werbekanäle Traffic und Conversions bringen.

48. User-Retention nach Kohorten

Schlüsselmetrik zur Bewertung von Loyalität und Wiederkäufen.

49. News/Artikel für die Startseite

Für die Anzeige von News und Blog-Artikeln, um User-Engagement zu steigern.

50. Aktive Seiten der Website und zugehörige Content-Blöcke

Für die Prüfung der Content-Integrität der Seite, CMS-Funktion und Anzeige von Daten auf den Seiten.

Indizes hinzufügen

Queries sind natürlich nice, aber nur, wenn sie auch schnell laufen. Deshalb solltest du ein paar Indizes zu deiner DB hinzufügen. Du solltest zu den wichtigsten Tabellen deines Projekts 40 Indizes hinzufügen, um die Query-Performance und die Wartbarkeit zu verbessern.

1. Index auf product.product(status)

Fast alle Produktabfragen werden nach Status gefiltert (z.B. aktive Produkte für Schaufenster, Suche usw.). Der Index beschleunigt die Auswahl von Produkten mit bestimmtem Status und minimiert das Scannen der ganzen Tabelle.

2. Index auf product.variant(product_id, is_active)

Abfragen zu Produktvarianten (SKU) und fürs Schaufenster nutzen die Filterung nach Produkt und Aktivität der Variante. Dieser zusammengesetzte Index ermöglicht die optimale Auswahl aller aktiven Varianten eines Produkts.

3. Index auf product.image(product_id, is_main DESC)

Für das Holen des Hauptbilds eines Produkts (oder der ganzen Liste) wird nach Produkt gefiltert und nach "Hauptbild"-Flag sortiert. Der Index beschleunigt solche Abfragen und sorgt für schnelle Datenlieferung für Galerien.

4. Index auf product.product(name text_pattern_ops)

Für schnelle Produktsuche nach Stichwort im Namen via ILIKE '%...%'. Spezieller Index auf name text_pattern_ops verbessert die Substring-Suche, besonders bei großen Datenmengen.

5. Index auf product.product(description gin_trgm_ops)

Analog zu Punkt 4 – Suche in der Produktbeschreibung (ILIKE oder Fulltext). GIN-Index mit Trigrammen beschleunigt Filterung nach Textfeldern.

6. Index auf product.product(category_id)

Oft wird nach Kategorie oder nach direkten/Kind-Kategorien gefiltert (siehe Katalogfilter-Queries). Der Index ermöglicht schnelles Finden aller Produkte einer Kategorie.

7. Index auf product.category(parent_id)

Für den Aufbau der Kategorie-Hierarchie und Visualisierung des Navigationsbaums werden oft Abfragen nach parent_id gemacht. Der Index beschleunigt diese rekursiven Hierarchie-Queries.

8. Index auf product.review(product_id)

Alle Zugriffe auf Produktbewertungen werden nach product_id gefiltert (sowohl für Durchschnittsrating als auch für die Review-Liste). Index auf diesem Feld macht Aggregation und Auswahl der Reviews deutlich schneller.

9. Index auf product.review(product_id, created_at DESC)

Für das schnelle Holen der letzten Produktbewertungen (ORDER BY createdat DESC), besonders zusammen mit Filterung nach productid, hilft ein zusammengesetzter Index.

10. Index auf product.question(product_id, created_at DESC)

Beliebte Abfrage nach Antworten zu einem bestimmten Produkt, sortiert nach Erstellungszeit. Index deckt beide Bedingungen ab und beschleunigt die Q&A-Sektion in der Produktkarte.

11. Index auf product.answer(question_id, created_at)

Für die Suche nach Antworten auf Produktfragen braucht man schnellen Zugriff per Fremdschlüssel question_id, oft mit Sortierung nach Datum. Dieser Index minimiert die Verzögerung beim Generieren von Q&A.

12. Index auf product.price_history(variant_id, changed_at DESC)

Preisänderungshistorie wird schnell nach Produktvariante und letzten Änderungen geholt. Solcher Index beschleunigt Analytics-Queries zu Preisdynamik und "alt/neu Preis".

13. Index auf product.status_history(product_id, changed_at DESC)

Abfrage der Statusänderungshistorie eines Produkts mit Sortierung nach Zeit ist gefragt für Audit und Lebenszyklus-Kontrolle. Zusammengesetzter Index beschleunigt solche Queries deutlich.

14. Index auf product.certificate(product_id)

Suche nach Produktzertifikaten per id – typische Operation für B2B und zertifizierte Schaufenster. Index beschleunigt solche Checks.

15. Index auf product.license(product_id)

Für die Suche nach Produktlizenzen, besonders bei Filterung nach Lizenztyp.

16. Index auf product.product_tag(tag_id)

Häufige Query – alle Produkte zu einem bestimmten Tag holen (und umgekehrt). Index ermöglicht schnelles Verknüpfen von Produkten und Tags für Tag-Clouds oder Filter.

17. Index auf product.product_tag(product_id)

Ermöglicht schnelles Ermitteln, welche Tags an ein Produkt gebunden sind, beschleunigt Tag-Auswahl.

18. Index auf logistics.inventory(product_id, warehouse_id)

Für sofortigen Zugriff auf Produktbestand im Lager (oder für Berechnung über alle Lager) – kritisch für Logistik, Stock-Level-Check und Echtzeit-Schaufenster.

19. Index auf logistics.inventory(variant_id)

Für Bestandsführung nach Produktvariante (Farbe/Größe) und für durchgehende Reports.

20. Index auf logistics.stock_level(product_id, warehouse_id)

Schneller Check des Mindestbestands für ein Produkt im Lager (z.B. für Auto-Bestellung oder Low-Level-Alarm). Solcher Index wird für Vergleich mit inventory gebraucht.

21. Index auf logistics.inventory_movement(product_id, changed_at DESC)

Ermöglicht das schnelle Holen der Warenbewegungshistorie (Audit) für die letzten Zeiträume – nützlich für Fehlerprävention, Verlustanalyse und Lieferkontrolle.

22. Index auf logistics.transfer(product_id, requested_at DESC)

Für Analyse der Logistik von Umlagerungen zwischen Lagern, Filterung nach Produkt und Sortierung nach Anfragedatum.

23. Index auf logistics.shipping_rate(shipping_method_id, destination_zone)

Bei der Versandkostenberechnung wird oft nach Versandart-id und Zielzone ausgewählt. Index beschleunigt die Berechnung für den User beim Checkout.

24. Index auf "order".order(user_id, placed_at DESC)

Alle Zugriffe auf die Bestellhistorie des Users nutzen Filter nach user_id und Sortierung nach Bestelldatum. Zusammengesetzter Index sorgt für schnelle History-Ausgabe im User-Account.

25. Index auf "order".order(status, placed_at)

Für Analytics und Reports zu Bestellungen nach Zeitraum sowie Suche nach Status (z.B. "in Bearbeitung"/"abgeschlossen").

26. Index auf "order".order_item(order_id)

Holen aller Bestellpositionen per order_id – eine der häufigsten Operationen für Bestelldetails.

27. Index auf "order".order_item(product_id)

Sales-Analytics und Produktstatistik brauchen schnelle Auswahl von Bestellpositionen nach Produkt-id.

28. Index auf "order".return(order_id)

Verknüpfung von Rückgaben mit Bestellungen wird für Support und Rückgabe-Analytics genutzt. Index beschleunigt die Suche nach Rückgaben per Bestellnummer.

29. Index auf "order".cancellation(order_id)

Analog zu Rückgaben – beschleunigt das Finden von Stornos für Analytics und Support.

30. Index auf "order".cart(user_id, updated_at DESC)

Für die Suche nach den letzten Warenkörben eines Users (z.B. "verlassene" Warenkörbe) ist ein Index nach user_id mit Sortierung nach letztem Update praktisch.

31. Index auf payment.payment_transaction(order_id)

Die meisten Abfragen zur Zahlungshistorie filtern nach Bestellung. Index sorgt für sofortigen Zugriff auf die Transaktionen der Bestellung.

32. Index auf payment.refund(transaction_id)

Ermöglicht effizientes Finden von Rückerstattungen zu einer bestimmten Transaktion für Support, Reporting und Betrugsprävention.

33. Index auf payment.wallet(user_id)

Schneller Zugriff auf das User-Wallet für Balance-Check und Transaktionshistorie.

34. Index auf payment.wallet_transaction(wallet_id, created_at DESC)

Auswahl der Wallet-Transaktionen des Users mit Sortierung nach Datum (z.B. Anzeige der Transaktionshistorie).

35. Index auf support.support_ticket(user_id, created_at DESC)

Historie der Support-Anfragen eines Users (Account/Client-Service). Zusammengesetzter Index optimiert solche Abfragen.

36. Index auf support.ticket_message(ticket_id, sent_at)

Für die Anzeige der gesamten Kommunikation zum Ticket ist ein Index nach Ticket und Datum praktisch – beschleunigt die Sortierung der Nachrichten nach Zeit.

37. Index auf support.ticket_sla_tracking(ticket_id)

Für SLA-Analytics und Kontrolle pro Ticket, schneller Zugriff auf SLA-Daten durch Index nach ticket_id.

38. Index auf marketing.promo_usage(user_id, used_at DESC)

Für Analyse der User-Aktivität bei Promocodes (Analytics und Missbrauchsschutz) braucht man schnelle Suche nach user_id mit Sortierung nach Zeit.

39. Index auf analytics.product_view(user_id, viewed_at DESC)

Speicherung und Analyse der Produkt-View-Historie des Users (Personalisierung, Empfehlungen) braucht schnellen Zugriff nach user_id mit Sortierung nach View-Zeit.

40. Index auf analytics.search_query_log(query_text)

Beliebte Suchanfragen und deren Häufigkeit – ein zentrales Analytics-Tool für die Suche. Index beschleunigt Aggregationen und Zählungen nach Suchtext.

Hinweis

Für Textsuche mit ILIKE empfiehlt sich der Einsatz von GIN-Indizes mit pg_trgm-Extension, die für Substring- und Fuzzy-Suche effizient sind. Für große Tabellen mit Aggregation oder Sortierung nach Datum empfiehlt sich ein DESC-Index auf dem Datum – das beschleunigt die Auswahl der letzten Einträge.

Es macht Sinn, Indizes nach realen Ausführungsplänen und Laststatistiken zu konfigurieren, aber die oben genannten Indizes decken die wichtigsten Produktionsszenarien für unseren Marktplatz ab.

Funktionen hinzufügen

Noch nicht müde? Dann lass uns noch ein paar Funktionen schreiben, um das Schreiben unserer aktuellen und zukünftigen Queries zu vereinfachen. Sozusagen für schnellere Umsetzung der Key-Queries, weniger Code-Duplikate in der App und Zentralisierung der Business-Logik auf DB-Seite.

1. Produktsuche nach Stichwort inkl. Tags und Brands

Wozu:

Die normale Suche nach Name und Beschreibung ist limitiert. Oft muss man auch nach Tags und Brands suchen. Eine universelle Funktion zentralisiert die erweiterte Suchlogik, reduziert Code-Duplikate und vereinfacht die Frontend-Integration.

2. Komplette Produktkarte per ID holen (alle Daten für die Karte)

Wozu:

Im Frontend wird oft direkt alle Info zum Produkt gebraucht: Hauptfelder, Brand, Kategorie, Bilder, Tags, Attribute, Durchschnittsrating und Review-Anzahl. Die Funktion liefert die komplette Produktkarte in einem Call und spart DB-Zugriffe.

3. Kategoriehierarchie mit Verschachtelung holen

Wozu:

Der Aufbau des Kategoriebaums (oder Pfads) wird für Schaufenster, Filter und Breadcrumbs gebraucht. Statt rekursiver Queries im Client-Code gibt die Funktion die ganze Hierarchie auf einmal zurück.

4. Durchschnitts-, Minimal- und Maximalpreis pro Kategorie berechnen

Wozu:

Für Katalogfilter und Analytics ist es praktisch, aggregierte Statistik zu Produkten in einer Kategorie zu bekommen: Preisspanne, Durchschnitt. Die Funktion spart wiederholte Subqueries.

5. Check und automatische Berechnung des Produktbestands über alle Lager

Wozu:

Ermöglicht das sofortige Ermitteln des Gesamtbestands eines Produkts (und jeder Variante) – praktisch für Schaufenster, Lager und Logistik. Zentralisiert die Berechnung und verhindert doppelte Business-Logik.

6. Bestellhistorie des Users mit Details holen

Wozu:

Funktion gibt die Bestellliste des Users zurück, inkl. Bestellpositionen, Summen, Status – so kann das Frontend die History in einem Call holen und direkt das User-Account bauen.

7. Durchschnittsbewertung eines Users als Verkäufer/Käufer holen

Wozu:

Für die Anzeige von Vertrauen und Reputation eines Users auf der Plattform ist sein Durchschnittsrating als Verkäufer oder Käufer wichtig. Die Funktion macht die Aggregation.

8. Promocode-Nutzung durch User (Validator mit allen Bedingungen)

Wozu:

Die gesamte Business-Logik zur Prüfung und Einlösung des Promocodes (Aktivität, Limits, Datum usw.) ist in einer Funktion zentralisiert. Das vereinfacht die App-Logik und schützt vor Fehlern durch doppelte Bedingungen.

9. Universelle Funktion zum Loggen von User-Events

Wozu:

Für durchgehende Analytics und Audit reduziert zentrales Event-Logging Code-Duplikate und das Risiko von Datenverlust zu User-Aktionen.

10. Funktion zum Holen des Bonus-Wallet-Balances und der Gesamtsumme der Gutschriften

Wozu:

Ein Call liefert direkt den aktuellen User-Balance und die Gesamtsumme der Gutschriften im Wallet. Praktisch für Dashboards und reduziert die Zahl der SQL-Queries.

11. Universelle Funktion zum Statuswechsel einer Bestellung mit Logging

Wozu:

Ändert den Bestellstatus, fügt einen Eintrag ins Status-Log hinzu und minimiert Fehler beim Statuswechsel in verschiedenen App-Teilen.

12. Alle Nachrichten eines Support-Dialogs holen (Ticket + alle Messages)

Wozu:

Funktion gibt die gesamte Kommunikation zum Ticket zurück, inkl. Ticketdetails und jeder Nachricht. Das erleichtert den Aufbau der Ticket-History im Frontend.

13. Check auf User-Existenz per E-Mail oder Telefon

Wozu:

Wird für Registrierung und Passwort-Reset genutzt, verhindert doppelte Logik im Front- und Backend.

Hinweis

Dieses Funktionsset deckt die wichtigsten Business-Szenarien ab, macht die Arbeit mit Daten bequemer, optimiert die Logik und beschleunigt Frontend-Entwicklung und Integrationen. Hoffe, es hat dir gefallen :)

Dateien mit der Lösung