Erstellen Sie eine Zusammenfassungszeile für Daten über mehrere Tabellen hinweg

Ich versuche, eine SQL-Abfrage zu schreiben, um eine Zusammenfassungszeile für die Aktionen zu generieren, die von einem bestimmten Benutzer in einem bestimmten Zeitraum ausgeführt werden. Ich habe die folgende relevante Tabellenstruktur:

Benutzer

• ID
• Team

audit_periods (kann verarbeitet werden, Versand, Pause, etc.)

• Benutzer-ID
• period_type (kann "processing", "shipping" usw. sein - momentan nicht normalisiert)
• gestarteter_at
• finished_at (kann für die aktuelle Periode null sein, daher die Logik um die Zeiten herum)

audit_tasks

• audit_period_id
• audit_task_type_id
• created_at
• punkten

audit_task_types

• Name ("scan", "place_in_pallet", usw.)
• score (scheint redundant zu sein, aber wir müssen den Punktestand beibehalten, den die audit_task zum Zeitpunkt der Ausführung erhalten hat, da sich die audit_task_type-Punktzahl später ändern kann)

Für jeden Benutzer für einen bestimmten Zeitraum möchte ich etwas wie die folgende Datenzeile erstellen:

users.id users.email time_spent_processing time_spent_shipping ... Anzahl_von_Scans Anzahl_Paletten

was berechnet würde, indem für jeden Benutzer herausgefunden wird:

• Welche audit_periods fallen zumindest teilweise in das gewünschte Fenster? (Verwendet start_at und final_at.)
• Wie lange hat ein Benutzer in jeder Art von audit_period verbracht? (Sollte sich die Gruppe nach audit_periods.period_type beteiligen, würde ich mir das vorstellen.)
• Welche audit_tasks fallen in das gewünschte Fenster? (Verwendet created_at - noch nicht im Code unten.)
• Wie viele von jedem Typ von audit_task hat ein Benutzer während des Fensters ausgeführt? (Joins zu "audit_task_type" und schließt wahrscheinlich eine Gruppe mit "audit_task_types.name" ein.)
• Wie viele Punkte wurden während des Zeitraums gesammelt? (Summiert die Bewertungen aller audit_tasks im Fenster.)

Ich habe alle SQL-Tricks, die ich kenne (nicht viele), ausgeschöpft und habe etwa folgendes gefunden:

Dies scheint jedoch funktional gleichbedeutend mit der Auswahl aller Prüfungsaufgaben zu sein. Ich habe auch einige Unterabfragen ausprobiert, aber ohne Erfolg. Genauer gesagt, erzeugt dies etwas wie (weniger wichtige Spalten überspringend):

wenn ich will:

Ich kann einfach alle audit_tasks für einen bestimmten Benutzer abrufen und sie in Code zusammenfassen, aber ich werde vielleicht Hunderttausende von audit_tasks über einen bestimmten Zeitraum abrufen, also muss es in SQL gemacht werden.

Nur um klar zu sein: Ich suche nach einer Abfrage, um eine Zeile pro Benutzer zu generieren, die zusammenfassende Daten enthält, die in den anderen drei Tabellen gesammelt wurden. Also möchte ich für jeden Benutzer wissen, wie viel Zeit er in jeder Art von Audit-Periode (3600 Sekunden Verarbeitung, 3200 Sekunden Versand usw.) verbracht hat und wie viele von jeder Audit-Aufgabe er ausgeführt hat (5 Scans, 10 Items) Palette usw.).

Ich denke, ich habe die Elemente einer Lösung, ich habe nur Schwierigkeiten, sie zusammenzufügen. Ich weiß genau, wie ich das in Ruby / Java / etc erreichen würde, aber ich glaube nicht, dass ich SQL gut genug verstehe, um zu wissen, welches Werkzeug mir fehlt. Brauche ich eine temporäre Tabelle? Eine Union? Irgendein anderes Konstrukt vollständig?

Jede Hilfe wird sehr geschätzt, und ich kann klären, ob das oben genannte Unsinn ist.